Re: SQL profile questions

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

--
http://www.freelists.org/webpage/oracle-l