Re: SQL profile questions
- From: Martin Klier <usn@xxxxxxxxx>
- To: jheinrichdba@xxxxxxxxx
- Date: Sat, 21 Feb 2009 15:43:27 +0100
Hi Jason,
Jason Heinrich schrieb:
> 1. Is there a way to determine if an SQL profile (part of the tuning
> pack) is actually being used? The dba_sql_profiles view shows that the
> profile in question is 'ENABLED', but we had a performance issue with
> the query this morning that shouldn't have happened if the profile was
> working.
I'd be careful about performance "should"s at all. Using a profile
(which is more or less a rewritten query with a set of hints that looked
useful at the time the profile was created) may be perfect one day, and
another morning (due to constellation, data distribution, load, parse
time + bind variable peeking, humidity, rain-or-sunhine, phase of the
moon :) and so much more) it might fail miserably.
If you experience poor statement performance, look into execution plan
(by dbms_xplan.display) if there are any bad or at leas unexpected
issues there. Sometimes bad/old/unappropriate table statistics make the
optimizer use strange/no indexes, or an index has become bad and needs a
rebuild, or for some kind of reason the joins are performed in a
non-optimal way. In combination with an old profile in action, the
response time may skyrocket. So consider a wide field of possibilities
before taking any action - think big.
> 2. The database was restarted last night. Does the profile continue to
> be applied after a restart?
Yes it does.
Regards
Martin Klier
--
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
