[20170615]执行dbms_sqldiag.dump_trace看执行计划.txt
--//上午在想查看10053执行计划时使用包时出现如下提示:
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> select count(*) from t1 where skew = 73;
COUNT(*)
----------
73
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 41bbtn98pwgyq, child number 0
-------------------------------------
select count(*) from t1 where skew = 73
Plan hash value: 2432955788
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T1_I1 | 86 | 258 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SKEW"=73)
--//使用包dbms_sqldiag.dump_trace最大的好处是无需硬分析,而使用10053要进行硬分析才能获得执行计划.
SCOTT@book> @ &r/10053x 41bbtn98pwgyq 0
BEGIN dbms_sqldiag.dump_trace(p_sql_id=>'41bbtn98pwgyq',p_child_number=>0,p_component=>'Compiler',p_file_id=>'41bbtn98pwgyq'); END;
*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 206
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1185
ORA-06512: at line 1
--//报错.
SYS@book> grant execute on dbms_sqldiag to scott;
Grant succeeded.
--//以sys用户授权后再次执行问题依旧.
SYS@book> revoke execute on dbms_sqldiag from scott;
Revoke succeeded.
--//google,百度发现别人都可以,而且我以前的测试也没有遇到问题.最终我发现只要p_file_id参数第1个字母不是数字就ok了,例如:
SCOTT@book> exec dbms_sqldiag.dump_trace(p_sql_id=>'41bbtn98pwgyq',p_child_number=>0,p_component=>'Compiler',p_file_id=>'a1bbtn98pwgyq');
PL/SQL procedure successfully completed.
SCOTT@book> exec dbms_sqldiag.dump_trace(p_sql_id=>'41bbtn98pwgyq',p_child_number=>0,p_component=>'Compiler',p_file_id=>'11bbtn98pwgyq');
BEGIN dbms_sqldiag.dump_trace(p_sql_id=>'41bbtn98pwgyq',p_child_number=>0,p_component=>'Compiler',p_file_id=>'11bbtn98pwgyq'); END;
*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 206
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1185
ORA-06512: at line 1
SCOTT@book> @ &r/desc_proc sys dbms_sqldiag dump_trace
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED
----- ------------ ----------- -------- -------------------- -------------------- --------- --------- ----------
SYS DBMS_SQLDIAG DUMP_TRACE 1 P_SQL_ID VARCHAR2 IN VARCHAR2 N
2 P_CHILD_NUMBER NUMBER IN NUMBER Y
3 P_COMPONENT VARCHAR2 IN VARCHAR2 Y
4 P_FILE_ID VARCHAR2 IN VARCHAR2 Y
--//P_FILE_ID参数仅仅字符类型,并没有说明第1个参数的字符是数字就不行,oracle真是很奇怪....