[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在这里统计做了不好,不能很好的区分软分析以及软软分析.很容易引起歧异.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2640580/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2640580/