experimenting with dbms_sqldiag.dump_trace
From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 2 Oct 2013 09:51:29 -0500
Message-ID: <CAPZQniUWJ1XQLhtZ_GMswn8O92svq2eMR2Zw9zW2jNBi8qAyQg_at_mail.gmail.com>
Good day, listers,
At a recent OakTable session with the glowing Scotsman Doug Burns (*grin*), I was introduced to dbms_sqldiag.dump_trace (who further gave credit back to Maria Colgan and Greg Rahn). Since this is *STILL* undocumented, I am curious why certain components can be explicitly traced, while others cannot. For instance (from a OSEE 11.2.0.2, 11.2.0.4 or 12.1.0.1 database):
Date: Wed, 2 Oct 2013 09:51:29 -0500
Message-ID: <CAPZQniUWJ1XQLhtZ_GMswn8O92svq2eMR2Zw9zW2jNBi8qAyQg_at_mail.gmail.com>
Good day, listers,
At a recent OakTable session with the glowing Scotsman Doug Burns (*grin*), I was introduced to dbms_sqldiag.dump_trace (who further gave credit back to Maria Colgan and Greg Rahn). Since this is *STILL* undocumented, I am curious why certain components can be explicitly traced, while others cannot. For instance (from a OSEE 11.2.0.2, 11.2.0.4 or 12.1.0.1 database):
SQL > exec dbms_sqldiag.dump_trace ('fq0whgg02000r',0,'APA','APA_trace'); BEGIN dbms_sqldiag.dump_trace ('fq0whgg02000r',0,'APA','APA_trace'); END;
*
ERROR at line 1:
ORA-20001: Invalid value for component parameter ORA-06512: at "SYS.DBMS_SQLDIAG", line 1190 ORA-06512: at line 1
SQL > exec dbms_sqldiag.dump_trace
('fq0whgg02000r',0,'Semantic','Semantic_trace');
BEGIN dbms_sqldiag.dump_trace
('fq0whgg02000r',0,'Semantic','Semantic_trace'); END;
*
ERROR at line 1:
ORA-20001: Invalid value for component parameter ORA-06512: at "SYS.DBMS_SQLDIAG", line 1190 ORA-06512: at line 1
SQL > exec dbms_sqldiag.dump_trace
('fq0whgg02000r',0,'Optimizer','Optimizer_trace');
PL/SQL procedure successfully completed.
SQL > exec dbms_sqldiag.dump_trace
('fq0whgg02000r',0,'Compiler','Compiler_trace');
PL/SQL procedure successfully completed.
-- Charles Schultz -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 02 2013 - 16:51:29 CEST