Title: CA Clarity Tuesday Tip: Oracle Slow SQL in Clarity Only

Discussion created by Shawn_Moore Employee on May 25, 2011
Latest reply on May 27, 2011 by Chris_Hackett
Title: CA Clarity Tuesday Tip: Oracle Slow SQL in Clarity Only

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 5/24/2011

(We are taking a week break from the time slicing series, to briefly go over a topic I've been getting questions on recently. Sorry for the late tip, been a bit busy over the last few days, as I had 2 days of vacation and just became a grandparent! :grin: )

Now for the tip......

In some cases, when you run a query from a query tool like Toad or Aqua, the query runs fast, but from a SQL trace it was previously observed that the query was slow. What’s going on?

In many places Clarity uses bind variables in its queries. The execution plan generated for a query that uses a bind variable vs. one that is hardcoded may be different. This issue can be further obfuscated for queries that have some bind variables and literals, as Oracle will decide whether or not to use an existing execution plan.

If a query is reportedly slow from a sql trace, but you can’t get the same slowness by running it from a query tool, you can use the following steps to get the actual execution plan generated from the past execution of the query.

1) Identify the query id, using an AWR report.

2) If an AWR report is not available, you can embed the start of the query into the following sql statement to find the sql_id.

select sql_id, sql_text from v$sql where sql_text like '%1st part of query here%'

i.e. (for a job query)

select sql_id, sql_text from v$sql where sql_text like '%select job_id, name,%'

3a) Run a sql specific AWR report using the @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql file. This is typically a better option than 3b, because the report is formatted for easy reading.

3b) Alternately, you can look at the execution plan info by putting in newly acquired sql_id into the following query:

select a.sql_id, b.* from v$sql a,v$sql_plan b where
a.sql_id=b.sql_id and
a.sql_id = 'aaaaaaa'

note: aaaaaaa represents the sql id gathered from step 1 or 2.