RE: Base Line for Performance purpose
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <shivaswamykr@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Jan 2007 12:29:23 -0700
You mentioned ASH, so it sounds like you're on 10g, which keeps all the
history you need in ASH/AWR - you should be able to see there when an
execution plan changed and the resulting performance stats.
If you were on 9i, I'd recommend regular (every hour at least) statspack
snapshots with occasionaly level 6 snapshots to capture the explain
plans as well.
With the explain plan problem you describe below, it sounds like you're
encountering the same problem I battle on a daily basis these days -
bind variable peeking causing explain plans to be shared when they
shouldn't be, i.e. between one execution for a very selective (i.e.
unpopular) value and a later execution for a very unselective/popular
value. The first should use an index and the latter should use FTS, but
Oracle only peeks the first time and then uses the same plan for both
executions. The solution is to force the preferred method with a hint
or stored outline, or break the two executions into two syntactically
different statements so they don't share the same cursor, e.g. with
comments like:
SELECT /*+ popular */ . . .
SELECT /*+ unpopular */ . . .
Or, disable bind variable peeking by setting the hidden parameter.
Regards,
Brandon
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
Other related posts:
