[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;
