RE: slow sql question

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

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