Re: SQL performance issue determination
- From: Rich <richa03@xxxxxxxxx>
- To: Tanel Poder <tanel@xxxxxxxxxx>
- Date: Wed, 21 Apr 2010 08:38:56 -0700
Thanks, Tanel.
We are using the plan_hash_value for comparison.
The SQL statement is small:
SELECT COUNT(*) FROM HSR_CORRELATION_INFO WHERE CORR_SOURCE_ID_CRC = :B8 AND
CORR_VALUE_TYPE_CRC = :B7 AND CORR_VALUE_CRC = :B6 AND CORR_SOURCE_ID = :B5
AND CORR_VALUE_TYPE = :B4 AND CORR_VALUE = :B3 AND CONV_KEY = :B2 AND
TRAN_KEY = :B1 AND ROWNUM <= 1
Some testing on how the plan_hash_value is computed is at:
http://oracle-randolf.blogspot.com/2009/07/planhashvalue-how-equal-and-stable-are_26.html
I was trying to see if we could determine what predicate values were causing
this statement to perform poorly (and assuming that the plan was not
changing due to the same plan_hash_value).
In this case I assumed that, since the statement is small and it's
(observed) execution plan was fairly simple:
OP
------------------------------
SELECT STATEMENT
SORT AGGREGATE
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
it would have the same hash value - maybe not.
The index is a non-unique index on TRAN_KEY.
I think some (small number of) executions of this statement find large
numbers of non-unique TRAN_KEY values and thus perform a large amount of
[L]IO against the table causing the longer executions.
Maybe there is a better way to go about this?
Thoughts?
Thanks,
Rich
On Tue, Apr 20, 2010 at 11:07 PM, Tanel Poder <tanel@xxxxxxxxxx> wrote:
> You can get the current bind variable values from an errorstack which you
> can get with oradebug. You need to run it when the problem is ongoing:
>
>
> http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output#TOC-Reading-the-current-bind-variable-v
>
> How do you compare whether execution plan has changed or not?
>
> The plan_hash_value is not perfect. It is computed only from a part of
> execution plan, for example if the predicate section (predicate application
> order etc) changes, then the plan_hash_value still stays the same. So you
> might actually have a different execution plan, but the plan_hash_value
> stays the same.
>
> In 11g, Oracle has solved this problem via adding current active execution
> plan rowsource ID column into ASH, before 11g (or if you don't have diag
> pack licenses) you need to get clever and use OS tools or measure against
> which buffers most of the LIOs are done etc.
>
> --
> Tanel Poder
> http://tech.e2sn.com
> http://blog.tanelpoder.com
>
>
>
>
> On Wed, Apr 21, 2010 at 6:28 AM, Rich <richa03@xxxxxxxxx> wrote:
>
>> 10.2.0.4 RHEL x86_64
>>
>> Hi all,
>> This is production.
>>
>> I'm trying to "catch" an intermittently poorly performing SQL statement.
>>
>> Sometimes it's performance is very good, sometimes very bad.
>>
>> I suspect the performance swing is due to skew in table data and I'm
>> trying to prove it.
>>
>> During a poor performance run of this SQL, I'd like to be able to
>> determine what the bind variables and the execution stats are.
>>
>> I can't set statistics_level to anything higher than typical (can't set it
>> to all) at the instance level.
>> Setting this to all enables "plan execution statistics" as well as "timed
>> os statistics" both of which introduce performance issues in this
>> environment.
>> See Tanel Poder's explanation of how execution statistics are gathered on
>> this platform at:
>>
>> http://blog.tanelpoder.com/2008/06/15/advanced-oracle-troubleshooting-guide-part-6-understanding-oracle-execution-plans-with-os_explain/
>>
>> We have a fairly large number of sessions which would potentially process
>> this SQL (over 500) and these sessions are very performance intensive.
>> I can't set statistics_level to anything higher than typical in all of
>> these sessions nor gather 10046 traces on these sessions due to the same
>> performance issues noted above.
>>
>>
>> I know the parameter _rowsource_execution_statistics will produce the
>> execution stats for just a session without also gathering os statistics.
>> This has helped in a couple of other issues, however, I can't set that in
>> all sessions.
>> Even setting _rowsource_execution_statistics to TRUE in all of these
>> sessions creates more performance issues.
>>
>> Is there any way to set this parameter (or something like it) for a single
>> SQL statement?
>>
>> Also, is there any way to get the bind variables for each run of the SQL
>> from Oracle without impeding performance [much]?
>>
>> Thanks in advance,
>> Rich
>>
>
>
Other related posts:
