[20171201]關於explain plan.txt

[20171201]關於explain plan.txt

--//大家都應該知道使用explain plan看執行計劃,有時候顯示的執行計劃不是真實的執行計劃.
--//一般不建議採用explain plan 看執行計劃.
--//toad設計一直有一個小小缺陷,就是掃描sga時找到sql語句查詢執行計劃,
--//它僅僅查詢v$sql_plan child_number =0的是否存在,有一些情況child_number =0是已經無效的執行計劃,甚至不存在的執行計劃.
--//這樣依舊呼叫explain plan.這個問題也存在於SGA TRACE的介面上.我一直希望有一個下拉選單,讓dba選擇對應的child_number.
--//參考連結:http://blog.itpub.net/267265/viewspace-2130781/

--//今天我才發現explain plan每次呼叫實際上cursor是不共享的,有點奇怪,做一個記錄.

1.環境:
SCOTT@book> @ &r/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

SCOTT@book> show parameter cursor_sharing
NAME           TYPE   VALUE
-------------- ------ -------
cursor_sharing string EXACT

2.測試:
SCOTT@book> explain plan for select * from dept where  deptno=10;
Explained.

SCOTT@book> explain plan for select * from dept where  deptno=10;

Explained.

SCOTT@book> explain plan for select * from dept where  deptno=10;

Explained.

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1xmp0v4m617j4, child number 2

explain plan for select * from dept where  deptno=10

NOTE: cannot fetch plan for SQL_ID: 1xmp0v4m617j4, CHILD_NUMBER: 2
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.

--//這樣能很快知道SQL_ID=1xmp0v4m617j4.

SCOTT@book> @ &r/share 1xmp0v4m617j4
SQL_TEXT                       = explain plan for select * from dept where  deptno=10
SQL_ID                         = 1xmp0v4m617j4
ADDRESS                        = 000000007C642498
CHILD_ADDRESS                  = 000000007C4BC188
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>20</ID><reason>Explain Plan
cursor(0)</reason><size>2x1</size><ctxflg_cursor>50</ctxflg_cursor><Literal_Replacement_Enabled>1</Literal_Replacement_Enabled></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>20</ID><reason>Explain Plan
cursor(0)</reason><size>2x1</size><ctxflg_cursor>50</ctxflg_cursor><Literal_Replacement_Enabled>1</Literal_Replacement_Enabled></ChildNode>
--------------------------------------------------
SQL_TEXT                       = explain plan for select * from dept where  deptno=10
SQL_ID                         = 1xmp0v4m617j4
ADDRESS                        = 000000007C642498
CHILD_ADDRESS                  = 000000007C209728
CHILD_NUMBER                   = 1
EXPLAIN_PLAN_CURSOR            = Y
REASON                         = <ChildNode><ChildNumber>1</ChildNumber><ID>20</ID><reason>Explain Plan
cursor(0)</reason><size>2x1</size><ctxflg_cursor>50</ctxflg_cursor><Literal_Replacement_Enabled>1</Literal_Replacement_Enabled></ChildNode><ChildNode><ChildNumber>1</ChildNumber><ID>20</ID><reason>Explain Plan
cursor(0)</reason><size>2x1</size><ctxflg_cursor>50</ctxflg_cursor><Literal_Replacement_Enabled>1</Literal_Replacement_Enabled></ChildNode>
--------------------------------------------------
SQL_TEXT                       = explain plan for select * from dept where  deptno=10
SQL_ID                         = 1xmp0v4m617j4
ADDRESS                        = 000000007C642498
CHILD_ADDRESS                  = 000000007B9C68A0
CHILD_NUMBER                   = 2
EXPLAIN_PLAN_CURSOR            = Y
REASON                         = <ChildNode><ChildNumber>2</ChildNumber><ID>20</ID><reason>Explain Plan cursor(0)</reason><size>2x1</size><ctxflg_cursor>50</ctxflg_cursor><Literal_Replacement_Enabled>1</Literal_Replacement_Enabled></ChildNode>
--------------------------------------------------
PL/SQL procedure successfully completed.

--//我呼叫3次每次都不能共享.

3.換成繫結變數的sql語句,測試看看.

SCOTT@book> explain plan for select * from dept where  deptno=:B0;
Explained.

SCOTT@book> explain plan for select * from dept where  deptno=:B0;
Explained.

SCOTT@book> explain plan for select * from dept where  deptno=:B0;
Explained.

SCOTT@book> explain plan for select * from dept where  deptno=:B0;
Explained.

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  4pd1ywtkkka6p, child number 3

explain plan for select * from dept where  deptno=:B0

NOTE: cannot fetch plan for SQL_ID: 4pd1ywtkkka6p, CHILD_NUMBER: 3
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.

--//問題依舊.不過理由有點點不同.

SCOTT@book> @ &r/share 4pd1ywtkkka6p
SQL_TEXT                       = explain plan for select * from dept where  deptno=:B0
SQL_ID                         = 4pd1ywtkkka6p
ADDRESS                        = 000000007B5F3508
CHILD_ADDRESS                  = 000000007C33DDB0
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>20</ID><reason>Explain Plan cursor(0)</reason><size>2x1</size><ctxflg_cursor>50</ctxflg_cursor><Literal_Replacement_Enabled>1</Literal_Replacement_Enabled></ChildNode>
--------------------------------------------------
SQL_TEXT                       = explain plan for select * from dept where  deptno=:B0
SQL_ID                         = 4pd1ywtkkka6p
ADDRESS                        = 000000007B5F3508
CHILD_ADDRESS                  = 000000007D651B30
CHILD_NUMBER                   = 1
EXPLAIN_PLAN_CURSOR            = Y
REASON                         = <ChildNode><ChildNumber>1</ChildNumber><ID>20</ID><reason>Explain Plan cursor(0)</reason><size>2x1</size><ctxflg_cursor>50</ctxflg_cursor><Literal_Replacement_Enabled>1</Literal_Replacement_Enabled></ChildNode>
--------------------------------------------------
SQL_TEXT                       = explain plan for select * from dept where  deptno=:B0
SQL_ID                         = 4pd1ywtkkka6p
ADDRESS                        = 000000007B5F3508
CHILD_ADDRESS                  = 000000007CE11E78
CHILD_NUMBER                   = 2
EXPLAIN_PLAN_CURSOR            = Y
REASON                         = <ChildNode><ChildNumber>2</ChildNumber><ID>20</ID><reason>Explain Plan cursor(0)</reason><size>2x1</size><ctxflg_cursor>50</ctxflg_cursor><Literal_Replacement_Enabled>1</Literal_Replacement_Enabled></ChildNode>
--------------------------------------------------
SQL_TEXT                       = explain plan for select * from dept where  deptno=:B0
SQL_ID                         = 4pd1ywtkkka6p
ADDRESS                        = 000000007B5F3508
CHILD_ADDRESS                  = 000000007D378AE0
CHILD_NUMBER                   = 3
EXPLAIN_PLAN_CURSOR            = Y
REASON                         =
--------------------------------------------------

PL/SQL procedure successfully completed.

 

4.附上share.sql指令碼:
SET  serveroutput on size  1000000;

DECLARE
   c           NUMBER;
   col_cnt     NUMBER;
   col_rec     DBMS_SQL.desc_tab;
   col_value   VARCHAR2 (4000);
   ret_val     NUMBER;
BEGIN
   c := DBMS_SQL.open_cursor;
   DBMS_SQL.parse
      (c,
       'select q.sql_text, s.*
      from v$sql_shared_cursor s, v$sql q
      where s.sql_id = q.sql_id
          and s.child_number = q.child_number
          and q.sql_id like ''&1''',
       DBMS_SQL.native
      );
   DBMS_SQL.describe_columns (c, col_cnt, col_rec);

   FOR idx IN 1 .. col_cnt
   LOOP
      DBMS_SQL.define_column (c, idx, col_value, 4000);
   END LOOP;

   ret_val := DBMS_SQL.EXECUTE (c);

   WHILE (DBMS_SQL.fetch_rows (c) > 0)
   LOOP
      FOR idx IN 1 .. col_cnt
      LOOP
         DBMS_SQL.COLUMN_VALUE (c, idx, col_value);

         IF col_rec (idx).col_name IN
               ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER',
                'SQL_TEXT','REASON')
         THEN
            DBMS_OUTPUT.put_line (   RPAD (col_rec (idx).col_name, 30)
                                  || ' = '
                                  || col_value
                                 );
         ELSIF col_value = 'Y'
         THEN
            DBMS_OUTPUT.put_line (   RPAD (col_rec (idx).col_name, 30)
                                  || ' = '
                                  || col_value
                                 );
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line
                         ('--------------------------------------------------');
   END LOOP;

   DBMS_SQL.close_cursor (c);
END;
/

SET serveroutput off;