Great post from James Morle. Consider it required reading (and thinking).
- You know how to turn on the web server/Web Sharing in System Preferences > Sharing
- You know how to get wget (or use curl [part of OS X] to mimic the below commands)
# assuming you already have the web server running and have wget cd /Library/WebServer/Documents wget --mirror http://download.oracle.com/otn_software/emviewers/scripts/flashver.js wget --mirror http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js wget --mirror http://download.oracle.com/otn_software/emviewers/scripts/document.js wget --mirror http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf ln -s download.oracle.com/otn_software otn_software
Now edit /etc/hosts and add
Now when you load an Active SQL Monitor Report it will access those files from your local web server. Don’t forget to undo the /etc/hosts entry once you are back on the Internet. Also, keep in mind that these files may change so re-download them from time to time.
Option 2 – Firefox
Many Oracle DBA’s are probably familiar with what Optimizer trace files are and likely know how to create them. When I say “Optimizer trace” more than likely you think of event 10053, right? SQL code like this probably is familiar then:
alter session set tracefile_identifier='MY_10053'; alter session set events '10053 trace name context forever'; select /* hard parse comment */ * from emp where ename = 'SCOTT'; alter session set events '10053 trace name context off';
In 11g, a new diagnostic events infrastructure was implemented and there are various levels of debug output that you can control for sql compilation. ORADEBUG shows us the hierarchy.
SQL> oradebug doc component SQL_Compiler SQL_Compiler SQL Compiler SQL_Parser SQL Parser (qcs) SQL_Semantic SQL Semantic Analysis (kkm) SQL_Optimizer SQL Optimizer SQL_Transform SQL Transformation (kkq, vop, nso) SQL_MVRW SQL Materialized View Rewrite SQL_VMerge SQL View Merging (kkqvm) SQL_Virtual SQL Virtual Column (qksvc, kkfi) SQL_APA SQL Access Path Analysis (apa) SQL_Costing SQL Cost-based Analysis (kko, kke) SQL_Parallel_Optimization SQL Parallel Optimization (kkopq) SQL_Code_Generator SQL Code Generator (qka, qkn, qke, kkfd, qkx) SQL_Parallel_Compilation SQL Parallel Compilation (kkfd) SQL_Expression_Analysis SQL Expression Analysis (qke) SQL_Plan_Management SQL Plan Managment (kkopm) MPGE MPGE (qksctx)
My personal preference for Optimizer tracing is to stick with the most detailed level, in this case SQL_Compiler vs. just SQL_Optimizer.
Given that, we can do the following in 11g:
alter session set tracefile_identifier='MY_SQL_Compiler_TRACE'; alter session set events 'trace [SQL_Compiler.*]'; select /* hard parse comment */ * from emp where ename = 'SCOTT'; alter session set events 'trace [SQL_Compiler.*] off';
One of the big drawbacks of using the 10053 event or the SQL_Compiler event are that two things need to happen: 1) you have to have the SQL text and 2) a hard parse needs to take place (so there is actually sql compilation). What if you want to get an Optimizer trace file for a statement already executed in your database and is in the cursor cache? Chances are you know how to do #1 & #2 but it’s kind of a pain, right? Even more of a pain if the query is pages of SQL or you don’t have the application schema password, etc.
In 11gR2 (11.2) there was a procedure added to DBMS_SQLDIAG called DUMP_TRACE. The DUMP_TRACE procedure didn’t make the DBMS_SQLDIAG documentation but here is the declaration:
-- $ORACLE_HOME/rdbms/admin/dbmsdiag.sql -------------------------------- dump_trace --------------------------------- -- NAME: -- dump_trace - Dump Optimizer Trace -- -- DESCRIPTION: -- This procedure dumps the optimizer or compiler trace for a give SQL -- statement identified by a SQL ID and an optional child number. -- -- PARAMETERS: -- p_sql_id (IN) - identifier of the statement in the cursor -- cache -- p_child_number (IN) - child number -- p_component (IN) - component name -- Valid values are Optimizer and Compiler -- The default is Optimizer -- p_file_id (IN) - file identifier ------------------------------------------------------------------------------ PROCEDURE dump_trace( p_sql_id IN varchar2, p_child_number IN number DEFAULT 0, p_component IN varchar2 DEFAULT 'Optimizer', p_file_id IN varchar2 DEFAULT null);
As you can see, you can specify either Optimizer or Compiler as the component name which is the equivalent of the SQL_Compiler or SQL_Optimizer events. Conveniently you can use P_FILE_ID to add a trace file identifier to your trace file. The four commands used above can be simplified to just a single call. For example:
SQL> begin 2 dbms_sqldiag.dump_trace(p_sql_id=>'6yf5xywktqsa7', 3 p_child_number=>0, 4 p_component=>'Compiler', 5 p_file_id=>'MY_TRACE_DUMP'); 6 end; 7 / PL/SQL procedure successfully completed.
If we look at the trace file we can see that DBMS_SQLDIAG.DUMP_TRACE added in a comment /* SQL Analyze(1443,0) */ and did the hard parse for us (Thanks!).
Enabling tracing for cur#=9 sqlid=as9bkjstppk0a recursive Parsing cur#=9 sqlid=as9bkjstppk0a len=91 sql=/* SQL Analyze(1443,0) */ select /* hard parse comment */ * from emp where ename = 'SCOTT' End parsing of cur#=9 sqlid=as9bkjstppk0a Semantic Analysis cur#=9 sqlid=as9bkjstppk0a OPTIMIZER INFORMATION ****************************************** ----- Current SQL Statement for this session (sql_id=as9bkjstppk0a) ----- /* SQL Analyze(1443,0) */ select /* hard parse comment */ * from emp where ename = 'SCOTT' ----- PL/SQL Stack ----- ----- PL/SQL Call Stack ----- object line object handle number name 0x16fd3a368 145 package body SYS.DBMS_SQLTUNE_INTERNAL 0x16fd3a368 12085 package body SYS.DBMS_SQLTUNE_INTERNAL 0x18e7fead8 1229 package body SYS.DBMS_SQLDIAG 0x16fdbddd0 1 anonymous block *******************************************
Hopefully you don’t find yourself having to get too many Optimizer Trace Dumps, but if you do and you’re on 11.2, the hard work has been done for you.
Due to a bug in DBMS_ASSERT, you will need to specify a value for P_COMPONENT. If you leave it NULL, it will error like such:
SQL> begin 2 dbms_sqldiag.dump_trace(p_sql_id=>'6yf5xywktqsa7', 3 p_child_number=>0, 4 p_component=>NULL, 5 p_file_id=>'MY_TRACE_DUMP'); 6 end; 7 / begin * ERROR at line 1: ORA-44003: invalid SQL name ORA-06512: at 'SYS.DBMS_ASSERT', line 160 ORA-06512: at 'SYS.DBMS_SQLDIAG', line 1182 ORA-06512: at line 2
If you are looking to know more about the insides of databases (relational algebra, relational design theory, etc.) or machine learning or AI you may want to check out these free online classes from Stanford University. Class begins October 10!
It’s that time again — time to figure out what sessions you will be attending at Oracle OpenWorld 2011. In my slightly biased opinion, session by members of the OakTable Network generally have great technical content and give you the most value for your time. To aid you with your scheduling, I’ve compiled a list of sessions by OakTable members here. Enjoy!
As my loyal readers will know, I have been a big (maybe BIG) fan of the SQL Monitor Report since it’s introduction in 11g. It would not surprise me if I have looked at over 1000 SQL Monitor Reports in the past 4+ years — so I’m pretty familiar with these bad boys. Since I find them so valuable (and many customers are now upgrading to 11g), I’ve decided to do a deep dive into the SQL Monitor Report at both Oracle OpenWorld 2011 in October and the UKOUG in December. I think I have some pretty interesting and educational examples, but for anyone willing to share Active SQL Monitor Reports from their system, I thought I would extend the possibility to have it publicly discussed at either one of these sessions (or even a future blog post). Sound cool? I think so, though I may be slightly biased.
The Rules & Requirements
Here are some rules, requirements, restrictions, etc.:
- The SQL Monitor Report requires Oracle Database 11g and the Oracle Database Tuning Pack.
- By sending me your SQL Monitor Report you implicitly grant permission to me to use it however I want (in my sessions, on my blog, on my refrigerator, etc.).
- If you want to scrub it (remove the SQL Text, rename tables, etc.), feel free, but if you make the report unusable, it will end up in the bit bucket.
- I will only consider SQL Monitor Reports that are of type EM or ACTIVE, not TEXT or HTML or XML.
- I prefer the statement uses Parallel Execution, but will accept serial statements nonetheless.
- Active SQL Monitor Reports can be either saved from the EM/DB Console SQL Monitoring page, or via SQL*Plus (see code below).
- Once you save your Active SQL Monitor Report, validate it is functional from your browser (don’t send me broken stuff).
In order to participate in this once in a lifetime offer, just email the Active SQL Monitor Report file as an attachment to firstname.lastname@example.org. If you are going to be attending my session at either OOW11 or UKOUG11, let me know so if I choose your report I’ll notify you so you can bring your friends, significant other, boss, etc. Thanks in advance!
-- -- script to create an Active SQL Monitor Report given a SQL ID -- 11.2 and newer (EM/ACTIVE types are not in 11.1) -- set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feedback off spool sqlmon_4vbqtp97hwqk8.html select dbms_sqltune.report_sql_monitor(report_level=>'ALL', type=>'EM', sql_id=>'4vbqtp97hwqk8') monitor_report from dual; spool off