ITPub博客

SQL> select count(*) from test;  COUNT(*)----------  28835328Elapsed: 00:00:09.48SQL> select count(*) from test1;  COUNT(*)----------  28833280Elapsed: 00:00:09.20SQL> select id from test minus select id from test1;2048 rows selected.Execution Plan-----------...

1.Clustering Factor is a measure of the orderedness of an index in comparison to the table that is based upon  It is used as an indicator for computing the estimated cost of the table lookup following an index access.2.The Clustering Factor records the number of data blocks that will be acces...

可以在system级别,session级别，statement级别设定优化器[@more@]system alter system set optimizer_mode=all_rows(first_rows)session alter session set optimizer_mode=all_rows(first_rows)statement select /*+first_rows(10)*/ select /*+all_rows(10)*/ 1)SQL> select /*+rule*/ count(*) from tt; 2)select /*+full(tt)*/ * from tt where object_id=10; 3)select /*+index(tt tt_index)*/ * from tt; 4)select /*+no_index(tt tt_index)*

11gR2中，有两种压缩，一种是普通的压缩compress,另一种是compress for oltp.普通的compress对后面的insert进来的数据似乎压缩不大compress for oltp对后面insert进来的数据压缩比例比较明显SQL> select * from v$ve...

oracle提供了很多工具来查找最低效的SQL，但是，本质上就是通过如下SQL语句，从相关系统视图中获取最低效的SQL： select rownum as rank, a.* from (select PARSING_SCHEMA_NAME RUN_USER, --执行用户 SQL_FULLTEXT, --SQL文本,全，带格式 sql_text, --SQL文本前面部分 elapsed_Time , --单位是微秒，语句执行完的总时间，包括CPU时间和等待时间 cpu_time , --CPU时间，语句运行的时间 elapsed_Time - cpu_time wait_time, trunc((elapsed_Time - cpu_time)*100/elapsed_Time,2) "wait_time_per%",--等待时间所占

select ksppinm "Parameter Name", ksppstvl "Value",ksppstdf "Default"from x$ksppi x, x$ksppcv ywhere x.indx = y.indxand ksppinm like '/_%trace%' escape '/';Tracing by Enabling EventsAlter s...

(1)data dictionary cache hit ratio     select * from v$rowcache     select sum(gets)/(sum(gets)+sum(getmisses)) from v$rowcache where gets+getmi...

select (select value from v$sysstat where name='physical reads') phy ,(select value from v$sysstat where name='db block gets') get,(select value from v$sysstat where name='consistent g...

select a.name,b.XACTS,d.Sid,d.USERNAME,e.SQL_TEXT from v$rollname a,v$rollstat b,v$transaction c,v$session d ,v$sqltext ewhere a.usn=b.USN and b.USN=c.XIDUSN and c.ADDR=d.TADDR and d.sql_id=e.sql_...

Normal 0 0 2 false false false MicrosoftInternetExplorer4