oracle存储过程硬解析,能不能避免sql硬解析

本帖最后由 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)’保持不变。