1):目前可用的段统计信息动态性能视图
DROP TABLE seg_stat PURGE;
CREATE TABLE seg_stat(x INT);
INSERT INTO seg_stat VALUES(1);
INSERT INTO seg_stat VALUES(2);
COMMIT;
SELECT st.statistic_name,
st.VALUE
FROM v$segstat st, dba_objects do
WHERE st.obj# = do.object_id AND
st.dataobj# = do.data_object_id AND
do.owner = upper('scott') AND
do.object_name = upper('seg_stat')
ORDER BY st.VALUE DESC NULLS LAST;
STATISTIC_NAME VALUE
------------------------------ ----------
space allocated 65536
logical reads 48
db block changes 16
physical writes 8
physical write requests 1
physical writes direct 0
optimized physical reads 0
gc cr blocks received 0
gc current blocks received 0
ITL waits 0
row lock waits 0
space used 0
physical reads direct 0
physical read requests 0
physical reads 0
gc buffer busy 0
segment scans 0
buffer busy waits 0
v$segstat的基表是X$KSOLSFTS。
2):未来cbo功能体现
【查看两个隐含参数】
SELECT ksppinm NAME, ksppstvl AS VALUE, ksppdesc AS des
FROM x$ksppcv v, x$ksppi n
WHERE v.indx = n.indx AND
(n.ksppinm LIKE '%_cache_stats_mon%' OR
n.ksppinm LIKE '%_optimizer_cache_stats%');
/*
_optimizer_cache_stats FALSE cost with cache statistics
_cache_stats_monitor FALSE if TRUE, enable cache stats monitoring
*/
【查看dbms_stats包中的stattype选项】
-- stattype - The type of statistics to be deleted
-- ALL - both data and cache statistics will be deleted
-- CACHE - only cache statistics will be deleted
-- force - Ignores the statistics lock on objects and delete
-- the statistics if set to TRUE.
BEGIN
dbms_stats.gather_table_stats(ownname => USER,
tabname => 'seg_stat',
stattype => 'cache'); --收集seg_stat表的缓冲信息
dbms_stats.flush_database_monitoring_info;
END;
/
【查看几个表】
SELECT * FROM sys.tab_stats$;
SELECT * FROM sys.ind_stats$;
SELECT * FROM sys.cache_stats_1$;
SELECT * FROM sys.cache_stats_0$;
【总结】
通过上面的种种表征,我们相信oracle正有一组研发团队致力于将目前段的缓冲
信息情况融入到cbo计算公式中,虽然直到11g都没有看到缓冲信息被考虑到cbo成本
公式中,但是我们相信未来的某个版本会实现这个突破,没准即将发布的12c就会实
现呢。
其实很好理解为什么oracle考虑将缓冲信息情况融入到成本开销公式中,我们知道
传统的成本计算公式对待物理读和逻辑读其实是一样的,它只关心请求的数量;但是
实际上物理读和逻辑读单在消耗cpu上就存在着质的区别,读取一个内存块和从磁盘中
读取一个块肯定是不一样的。
而从tab_stats$和ind_stats$这两个表中我们看到有cachehit这个字段,它解释了
近期针对某个段的内存块命中情况,假设一个段在近期缓冲命中率是99%,则cbo可以
相信物理读取这个段的块只会占整个段大小的%1,所以在评估成本时相比某个时段的
%55 的cachehit,肯定在cost方面会有所下降。
阅读(654) | 评论(0) | 转发(0) |