[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還是很高.

--//昨天看文件,連結:

--//裡面有一段如下:

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在這裡統計做了不好,不能很好的區分軟分析以及軟軟分析.很容易引起歧異.