FAQ
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.

Search Discussions

  • Jorgensen, Finn at May 5, 2011 at 2:18 pm
    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_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
  • Yechiel Adar at May 6, 2011 at 6:26 am
    #2 - just do:
    spool awr_report.html
    Select * from table(dbms.workload_repository_html(parameters));

    parameters:

    1) Dbid from dba_hist_database_instance.
    2) Instance number from dba_hist_database_instance.
    3) Starting snapshot from dba_hist_snapshot.
    4) Ending snapshot from dba_hist_snapshot.
    5) 0.

    and view it with a browser.

    Yechiel Adar
    Israel
    On 05/05/2011 04:57, Ram Raman wrote:
    #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.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 5, '11 at 1:57a
activeMay 6, '11 at 6:26a
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase