本帖最后由 zkbucciarati 于 2012-10-18 16:36 编辑
环境:11g 11.2.0.1.0
项目中cognos生成了一个非常复杂的报表sql,执行计划就有300多行,而且很深的嵌套。
在optimizer_features_enable=11.2.0.1时,第一次执行要5秒,然后要差不多执行6次,每次也要花5秒左右的时间,从第7次开始,执行时间将稳定在0.5秒左右。
将optimizer_features_enable改为10.2.0.5时,第一次执行要9秒,第二次开始,执行时间稳定为0.7秒左右。
通过查看v$sql,v$sqltext,v$sql_shared_cursor这些表发现,在optimizer_features_enable=11.2.0.1时,一条父游标,产生了6个子游标,从第7次查询开始,开始稳定重用第6个子游标。
下面是执行7次之后的查询结果。
v$sqlarea表
SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM VERSION_COUNT LOADED_VERSIONS FETCHES EXECUTIONS PARSE_CALLS USER_IO_WAIT_TIME PLSQL_EXEC_TIME
------------ -------------- ----------- ------------- --------------- ---------- ---------- ----------- ----------------- ---------------
7873962 4256364 4231476 6 6 14 7 7 0 8356
v$sql表
SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM LOADED_VERSIONS FETCHES EXECUTIONS PARSE_CALLS USER_IO_WAIT_TIME PLSQL_EXEC_TIME
------------ -------------- ----------- --------------- ---------- ---------- ----------- ----------------- ---------------
1232818 712932 708784 1 2 1 1 0 1223
1302106 709336 705188 1 2 1 1 0 1194
1338790 708524 704376 1 2 1 1 0 1180
1346942 708524 704376 1 2 1 1 0 1165
1355094 708524 704376 1 2 1 1 0 1223
1367322 708524 704376 1 4 2 2 0 2371也就是说,这条sql的主要瓶颈在paring sql上面,时间主要花在了生成子游标上面,游标能够重用的话,真正取数据是非常快的,由于我们报表中有多条类似的sql,总的执行时间就比较长了。
用set autotrace查看执行计划和统计信息,发现recursive calls这个指标比较异常。第一次执行sql时,recursive calls为13529,之后5次执行,recursive calls都为512,第7次执行开始,recursive calls都为0。
目前解决该问题是暂时将optimizer_features_enable设为10.2.0.5。请教高手两个问题:
1.能不能避免解析sql,也就是将执行计划固定,存储下来,直接执行该计划,避免解析。
2.为什么不同的优化器模式会出现不同的现象。
2012-10-18,添加查看parsing情况的测试。
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as ZBTX_METRIC
首先,获得目标session sid为10,下面是在监测session里面查看parsing情况。
没有执行sql之前
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 1504 parse count (total)
10 380 parse count (hard)
第一次执行sql
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 1702 parse count (total)
10 409 parse count (hard)
第二次执行sql
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 1776 parse count (total)
10 410 parse count (hard)
第三次执行sql
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 1850 parse count (total)
10 411 parse count (hard)
第四次执行sql
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 1924 parse count (total)
10 412 parse count (hard)
第五次执行sql
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 1998 parse count (total)
10 413 parse count (hard)
第六次执行sql
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 2072 parse count (total)
10 414 parse count (hard)
第七次执行sql
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 2073 parse count (total)
10 414 parse count (hard)
第八次执行sql
SQL>
SQL> SELECT s.SID,VALUE,NAME
2 FROM v$sesstat s,v$statname n WHERE s.sid = 10
3 AND s.STATISTIC#=n.STATISTIC# AND n.NAME IN( 'parse count (total)','parse count (hard)');
SID VALUE NAME
---------- ---------- ----------------------------------------------------------------
10 2074 parse count (total)
10 414 parse count (hard)
从第7次执行开始,’parse count (total)‘递增,‘parse count (hard)’保持不变。