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:         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 … so that makes it easy to see the effects of SQL statements calling PL/SQL functions that contain SQL statements.

Update for 12c [Dec 2017]

Another little detail which I hadn’t considered until a posting on OTN today reminded me of the problem – 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.


  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

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 )

Google photo

You are commenting using your Google 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.

Powered by