SQL performance issue determination
- From: Rich <richa03@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Apr 2010 15:28:31 -0700
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:
