1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to retrieve SQLs and execution plans from awr history

Discussion in 'SQL PL/SQL' started by anurag_telenor, Dec 23, 2009.

  1. anurag_telenor

    anurag_telenor Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have to collect the past execution plan of specific statement(which runs somewhere around midnight everyday).
    Then I will have to compare the last execution plans of the 3 same statement . I run AWR stats collection every hour and have history upto 30 days.

    I am trying to run the following but it is not showing me even the last 3 executions of specific statement ( identified by .... like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'.......)
    Code (SQL):

    /* Formatted on 12/24/2009 11:47:30 AM (QP5 v5.126.903.23003) */
      SELECT   TO_CHAR (sql_text),
               command,
               snap_id,
               TIMESTAMP
        FROM   (  SELECT   X.SNAP_ID,
                           X.SQL_ID,
                           SQL_TEXT,
                           ROUND (X.ELAPSED_TIME / 1000000 / X.EXECUTIONS_DELTA, 3)
                              AVG_ELAPSED_TIME_SEC,
                           ROUND (X.CPU_TIME / 1000000 / X.EXECUTIONS_DELTA, 3)
                              AVG_CPU_TIME_SEC,
                           X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC,
                           X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC,
                           X.EXECUTIONS_DELTA EXECUTIONS,
                           DECODE (DHST.COMMAND_TYPE,
                                   3,
                                   'Select',
                                   47,
                                   'pl/sql',
                                   2,
                                   'Insert')
                              command,
                           DHSP.TIMESTAMP
                    FROM   DBA_HIST_SQLTEXT DHST,
                           DBA_HIST_SQL_PLAN DHSP,
                           (  SELECT   DHSS.SNAP_ID,
                                       DHSS.SQL_ID SQL_ID,
                                       SUM (DHSS.CPU_TIME_DELTA) CPU_TIME,
                                       SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME,
                                       SUM (DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
                                FROM   DBA_HIST_SQLSTAT DHSS
                               AND   PARSING_SCHEMA_NAME = 'CCDW'
                            GROUP BY   DHSS.SQL_ID, DHSS.SNAP_ID) X
                   WHERE       X.SQL_ID = DHST.SQL_ID
                           AND X.EXECUTIONS_DELTA > 0
                           AND DHSP.SQL_ID = X.SQL_ID
                ORDER BY   AVG_ELAPSED_TIME_SEC DESC)
       WHERE   UPPER (DBMS_LOB.SUBSTR (sql_text, 4000, 1)) LIKE
                  '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'
    GROUP BY   TO_CHAR (sql_text),
               command,
               snap_id,
               TIMESTAMP
    ORDER BY   TIMESTAMP DESC;
     
    Thanks,
    Anurag Vidyarthi
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Off-hand I can tell that you have an error in your inner query

    Code (SQL):
     FROM   DBA_HIST_SQLSTAT DHSS
                               AND   PARSING_SCHEMA_NAME = 'CCDW'
    should be

    Code (SQL):
     FROM   DBA_HIST_SQLSTAT DHSS
                               WHERE   PARSING_SCHEMA_NAME = 'CCDW'
     
  3. anurag_telenor

    anurag_telenor Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Sadik for correcting typo !!!

    Any help on the issue ?

    BR,
    Anurag
     
  4. Aman Jain

    Aman Jain Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    india
    While I'm running this Query after changing the Parsing_schema_name value, I am getting below error :-

    can you please help...

    WHERE UPPER (DBMS_LOB.SUBSTR (sql_text, 4000, 1)) LIKE
    *
    ERROR at line 38:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 1
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is the error I get:

    Code (SQL):
    SQL> /
    GROUP BY   TO_CHAR (sql_text),
               *
    ERROR at line 40:
    ORA-22835: Buffer too small FOR CLOB TO CHAR OR BLOB TO RAW conversion (actual:
    4292, maximum: 4000)


    SQL>
    That is using 12.1.0.2; using 11.2.0.4 I get:

    Code (SQL):
    GROUP BY   TO_CHAR (sql_text),
               *
    ERROR at line 40:
    ORA-22835: Buffer too small FOR CLOB TO CHAR OR BLOB TO RAW conversion (actual:
    4039, maximum: 4000)

     
    If you are using 12.1.0.x you can set max_string_size to extended and get the VARCHAR2 data type expanded to 32767 bytes, the same max that PL/SQL has allowed for a while. If you don't want to start the database in UPGRADE mode you can convert this to PL/SQL so you can have the larger declaration.