[20190404]parse call.txt

[20190404]parse call.txt --//经常看awr报表,看到SQL order by Parse calls时,我经常看到parse calls与executions基本一致的情况,我一直不明白问题在那里? --//从awr报表摘抄一段: SQL ordered by Parse Calls Total Parse Calls: 12,516,875  Captured SQL account for 62.1% of Total  Parse Calls Executions  % Total Parses SQL Id        SQL Module  SQL Text  1,927,493   1,928,069            15.40 g7ytdh9mxt1s0 XXXXXX.EXE  select count ( :"SYS_B_0" ) fr...  1,428,336   1,428,567            11.41 f8733rs2f3bng XXXXXX.EXE  SELECT sysdate FROM Dual     415,708     415,743             3.32 fuxpv1hbdp4a8 XXXXXX.EXE  SELECT CSQZ FROM GY_YHCS WHERE  ... --//Parse Calls与Executions基本一致.对于我来讲这段内容我基本跳过不看. --//看到的基本与SQL ordered by Executions显示一致. --//已经设置SESSION_CACHED_CURSORS参数=300,对应的sql语句光标已经缓存.parse calls还是很高. --//昨天看文档,链接: https://docs.oracle.com/database/121/TGDBA/tune_shared_pool.htm#TGDBA601 --//里面有一段如下: Note: Reuse of a cached cursor still registers as a parse, even though it is not a hard parse. --//翻译如下: 缓存游标的重用仍然是一个解析，尽管它不是一个硬解析。 --//也就是在一个回话里面,无论这条语句执行多少次,parse call 次数=executions 次数. --//还是通过例子说明问题: 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: select * from dept where deptno=10; select * from dept where deptno=10; select * from dept where deptno=10; select * from dept where deptno=10; select * from dept where deptno=10; select * from dept where deptno=10; select * from dept where deptno=10; --//执行多次.确定sql_id=4xamnunv51w9j. SCOTT@book> select executions, parse_calls, sql_text from v$sql where sql_id='4xamnunv51w9j'; EXECUTIONS PARSE_CALLS SQL_TEXT ---------- ----------- ------------------------------------------------------------          7           7 select * from dept where deptno=10 --//执行7次,分析调用7次. 3.继续测试: --//采用匿名PL/sql执行呢? declare v_id number; begin     for i in 1 .. 1000 loop         select deptno into v_id from dept where deptno=10;     end loop; end ; / SCOTT@book> select executions, parse_calls, sql_text from v$sql where sql_text='SELECT DEPTNO FROM DEPT WHERE DEPTNO=10'; EXECUTIONS PARSE_CALLS SQL_TEXT ---------- ----------- ------------------------------------------------------------       1000           1 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10 --//PL/SQL会把sql语句格式化,全部转化为大写.可以发现通过pl/sql才能实现分析调用1次. --//不知道其它一些工具可以实现这样的功能. 4.12CR2的sqlplus支持新特性statementcache功能: --//参考链接:http://blog.itpub.net/267265/viewspace-2216326/,重新演示: --//只要使用该版本的sqlplus登录11g数据库,打开这个功能就可以实现: SCOTT@78> set statementcache 100 SCOTT@78> variable c number = 10; select * from dept where deptno = :c ; select * from dept where deptno = :c ; select * from dept where deptno = :c ; select * from dept where deptno = :c ; select * from dept where deptno = :c ; select * from dept where deptno = :c ; select * from dept where deptno = :c ; --//确定sql_id='abzxwsyzmsu8h' SCOTT@78> select executions, parse_calls, sql_text from v$sql where sql_id='abzxwsyzmsu8h'; EXECUTIONS PARSE_CALLS SQL_TEXT ---------- ----------- ------------------------------------------------------------          7           1 select * from dept where deptno = :c 5.顺便说PL/SQL中sql语句的缓存方式不同,第1次执行后就缓存了. declare v_id number; begin     for i in 1 .. 1 loop         select deptno into v_id from dept where deptno=40;     end loop; end ; / --//仅仅执行1次. SCOTT@book> select sql_id,executions, parse_calls, sql_text from v$sql where sql_text='SELECT DEPTNO FROM DEPT WHERE DEPTNO=40'; SQL_ID        EXECUTIONS PARSE_CALLS SQL_TEXT ------------- ---------- ----------- ------------------------------------------------------------ 28zdqa1bza3ad          1           1 SELECT DEPTNO FROM DEPT WHERE DEPTNO=40 --//再次执行上面pl/sql语句. SCOTT@book> select * from v$open_cursor where sql_id='28zdqa1bza3ad'; SADDR            SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE ---------------- --- --------- ---------------- ---------- ------------- --------------------------------------- ------------------- ----------- -------------------- 0000000085EC7D20 295 SCOTT     000000007CED8C70 1475677517 28zdqa1bza3ad SELECT DEPTNO FROM DEPT WHERE DEPTNO=40                                 PL/SQL CURSOR CACHED --//可以发现CURSOR_TYPE='PL/SQL CURSOR CACHED',仅仅执行1次.再次执行以上sql语句. SCOTT@book> select sql_id,executions, parse_calls, sql_text from v$sql where sql_text='SELECT DEPTNO FROM DEPT WHERE DEPTNO=40'; SQL_ID        EXECUTIONS PARSE_CALLS SQL_TEXT ------------- ---------- ----------- ------------------------------------------------------------ 28zdqa1bza3ad          2           2 SELECT DEPTNO FROM DEPT WHERE DEPTNO=40 --//可以发现这样分析调用2次. 6.总结: --//感觉oracle在这里统计做了不好,不能很好的区分软分析以及软软分析.很容易引起歧异.