Oracle Scratchpad

May 22, 2014


Filed under: Oracle,trace files,Troubleshooting — Jonathan Lewis @ 1:24 pm BST May 22,2014

Here’s a convenient aid to trouble-shooting that appeared in 11g with its enhancements to setting events. It’s a feature that may help you to work out (among other things) why a given statement seems to have a highly variable performance profile. If you can find the SQL_ID for a parent cursor you can enable tracing for just that cursor whenever it executes, whoever executes it.

rem     Script:         sql_trace_11g.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2102
rem     Last tested:

define m_sql_id = '&1'
define m_sql_id = '9tz4qu4rj9rdp'

alter system set events
        sql_trace[SQL: &m_sql_id ]

pause Press return to stop tracing

alter system set events
        sql_trace[SQL: &m_sql_id ]

This is the whole of a little script I’ve got – the generic &1 is how I normally use it, I’ve just included a specific value (which is the sql_id for “select count(*) from all_objects;” as an example) that starts tracing across the entire system, but only for a given sql_id. On a production system, if I think I really need to do this, I would check the expected frequency of execution for the statement and wait enough time to capture a few occurrences before disabling the trace. Each session generates its own trace file, but if you’ve been sufficiently patient you get a reasonable sampling of the different workloads – and if you’ve captured the bind variables as well, this may give you some clues about why different work loads can appear.

A nice detail about this feature is that if the SQL_ID is for a pl/sql block, all the SQL executing inside the block is traced as part of the trace on the block (and that was particularly helpful the last time I had to make use of the feature); in the sample I’ve given I also found that some recursive SQL – relating to the XMLSCHEMA object types – executed within the view was also traced as the main statement was traced … and that makes it easy to see the effects of SQL statements calling PL/SQL functions that contain SQL statements.

NOTE: the pingback in comment #1 will lead you to a note on how to use a similar method to force Oracle to dump the CBO (10053) trace file for a given sql_id every time it is hard parsed.

Update for 12c [Dec 2017]

Another little detail which I hadn’t considered until a posting on OTN today reminded me of the possibility – if you enable this trace for a statement the resulting trace file will also include the dynamic sampling SQL used by the optimizer in 12c as it tries to optimise the statement.  That’s a real boon if you’re trying to answer the question: “Why does it take so long to get going the first time I run this query?” and all the usual answers don’t apply.

Update Aug 2021

A question on the Oracle developers’ forum about getting the execution plans for all the SQL called by a table() operation reminded me that this is (usually) just a special case of tracing a pl/sql function. For example:

select * from table(dbms_xplan.display_cursor(format => 'outline'));

If you enable tracing for this statement (its SQL_ID is ’04j57fnt6uzg5′) then the trace file will contain all the SQL executed by the display_cursor() function of the dbms_xplan package whenever anyone executes the statement.



  1. […] published a note yesterday about enabling SQL trace system-wide for a single statement – and got a response on twitter from Bertrand Drouvot referencing a […]

    Pingback by 10053 trace | Oracle Scratchpad — May 23, 2014 @ 1:37 pm BST May 23,2014 | Reply

  2. […] SQL trace globally is probably not a viable strategy – but 11g allows you to enable tracing for a specified SQL ID whenever it runs, and that might be a useful […]

    Pingback by Investigating Indexes – 2 – All Things Oracle — January 9, 2017 @ 11:35 am GMT Jan 9,2017 | Reply

  3. […] the library cache before it can be captured in an AWR snapshot then an alternative strategy is to enable system-wide tracing for that statement so that you can capture every execution in a trace […]

    Pingback by add_colored_sql | Oracle Scratchpad — October 19, 2018 @ 3:08 pm BST Oct 19,2018 | Reply

  4. […] write a note a few years ago about enabling sql_trace (and other tracing events) system-wide for a single SQL statement. In the […]

    Pingback by Occurence | Oracle Scratchpad — May 3, 2019 @ 1:34 pm BST May 3,2019 | Reply

  5. […] be reads of undo blocks) – and for repeatable cases enable SQL trace with wait state tracing against suspect queries to see if they show the characteristic mix of direct path reads and repeated db file sequential […]

    Pingback by Troubleshooting | Oracle Scratchpad — November 28, 2020 @ 5:05 pm GMT Nov 28,2020 | Reply

  6. […] further with the feature here’s a less painful way of doing it – enable SQL trace for just the sequence update statement – which in current versions has an SQL_ID of […]

    Pingback by Sequence Accelerator | Oracle Scratchpad — August 6, 2021 @ 11:22 am BST Aug 6,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: