RE: slow sql question

Ram,

Check if the AWR table wrh$_sql_plan contains the SQL ID's from before the 
upgrade. The table has a snap_id column you can use to identify the old plans. 
If you find the SQL in there you can then use dbms_xplan.display_awr to display 
the old plans like this :

select * from table(dbms_xplan.display_awr('&sql_id'));

You may have to input the plan_hash_value as well to get the right plan. Do a 
describe on dbms_xplan to get the full syntax.

There's also a diff_plan_awr function in the package that takes a sql id and 2 
plan hash values and displays the differences so if you can get both the old 
and new plan hash values then that will make it easy for you to find the 
difference.

Thanks,
Finn

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Ram Raman
Sent: Wednesday, May 04, 2011 9:57 PM
To: ORACLE-L
Subject: slow sql question

Hi,

We feel that after our upgrade few SQLs are running slow. We have the awr 
report information before the upgrade for a few months.  We have the option of 
generating reports before the upgrade. But given an SQL, is there a way to see 
how long it took or the plan before the upgrade from the repository without 
having to dig through a bunch of reports? Oracle 10.2.

#2  Is there a way to automate the generation of reports without having to type 
all the information, if we want to generate them for a  period of time.

Thanks.

>>> This e-mail and any attachments are confidential, may contain legal, 
>>> professional or other privileged information, and are intended solely for 
>>> the addressee.  If you are not the intended recipient, do not use the 
>>> information in this e-mail in any way, delete this e-mail and notify the 
>>> sender. CEG-IP1

Other related posts: