In our previous blog post I described how you can use the new diagnostic event infrastructure in Oracle Database 11g to capture an Optimizer trace (10053) for any SQL statement once you have its SQL_ID. The approach I showed using the traditional ‘Alter session set event’ approach and thus required you to execute the SQL statement. What I forgot to mention (and have been scolded for) was that you can also use the new infrastructure to generate an Optimizer trace for any SQL statement in the cursor cache without having to execute it. The DBMS_SQLDIAG package has been extend to include a procedure called DUMP_TRACE. This procedure, just like the event requires only the SQL_ID of the statement you wish to generate an Optimizer trace for but it doesn’t require you to re-execute the statement. The procedure will automatically trigger a hard parse of the statement to generate the trace.
Capturing 10053 trace files continued | Oracle Optimizer Blog
えっ、そんなんできるんですか？
