AWR report analysis
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: AWR report analysis

  1. #1
    Join Date
    May 2005
    Posts
    7

    AWR report analysis

    Hello All

    I have the following problem:

    When I create an awr report within two snapshots say 24139 and 24140 , I get from the part of "SQL ordered by Elapsed Time" the following information:

    SQL ordered by Elapsed Time DB/Inst: DEVC/DEVC Snaps: 24139-24140
    -> Resources reported for PL/SQL code includes the resources used by all SQL
    statements called by the code.
    -> % Total DB Time is the Elapsed Time of the SQL statement divided
    into the Total Database Time multiplied by 100


    OUTPUT
    ------------------------------------------------------------------
    Elapsed CPU Elap per % Total
    Time (s) Time (s) Executions Exec (s) DB Time SQL Id
    ---------- ---------- ------------ ---------- ------- -------------
    39 39 2 19.7 14.4 dccxnr2d9b3xd


    So far so good.
    The problem is that I want to retrieve the same information from the DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY for the same snapshot interval I get:


    exec dbms_sqltune.create_sqlset ('MYSQLSET2');

    DECLARE
    cur DBMS_SQLTUNE.SQLSET_CURSOR;
    BEGIN

    OPEN cur FOR
    SELECT VALUE(P)
    FROM table(
    DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(24139,24140)) P;

    DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MYSQLSET2',
    populate_cursor => cur);
    END;
    /


    When I query the information I realize that nothing matchs with the AWR report (for example , the same sql_id as different number of executions)

    SQL> SELECT sql_id,elapsed_time,cpu_time,executions, round(((elapsed_time/1000000)/decode(executions, 0, 1, executions))
    2 'MYSQLSET2', -- sqlset_name
    3 NULL, -- basic_filter
    4 NULL, -- object_filter
    5 NULL, -- ranking_measure1
    6 NULL, -- ranking_measure2
    7 NULL, -- ranking_measure3
    8 NULL, -- result_percentage
    9 NULL) -- result_limit
    10 )
    11 where sql_id ='dccxnr2d9b3xd'
    12 order by 2 desc;

    SQL_ID ELAPSED_TIME CPU_TIME EXECUTIONS RATIO
    ------------- ------------ ---------- ---------- ----------
    dccxnr2d9b3xd 63592931 62241266 3 21.2


    Does anyone have an ideia why the values are different while query the same repository?
    I need to retrieve the correct information and I don't know which of the sources I should rely.
    Thank you in advance for any help

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Cool Snapshots are ... Snapshots!

    Data in the repository are SNAPSHOTS. The correct value would be computed as snapshot (n) minus snapshot (n-1).
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2005
    Posts
    7
    LKBrwn,
    Thank you very much for your answer.
    I completely agree with you.
    The problem is that, as far as I understand, the procedure DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(24139,24140)) is computing the values in the same way as the AWR report. Otherwise, why does it takes as arguments two snapshots?

  4. #4
    Join Date
    May 2005
    Posts
    7
    I will try to formulate again the question:

    I have the following problem:

    I need to retrieve information about query performance (not as a report but as a query output to be used in OS scripts)
    The problem is that I really don't know where to rely upon the information that I retrieve from two different sources. Let me explain:

    When I create an AWR report within two snapshots say 24139 and 24140 , I get from the part of "SQL ordered by Elapsed Time" the following information:

    SQL ordered by Elapsed Time DB/Inst: DEVC/DEVC Snaps: 24139-24140
    -> Resources reported for PL/SQL code includes the resources used by all SQL
    statements called by the code.
    -> % Total DB Time is the Elapsed Time of the SQL statement divided
    into the Total Database Time multiplied by 100

    OUTPUT (for sql_id = dccxnr2d9b3xd)

    Elapsed
    Time(s)
    39

    CPU
    Time(s)
    39

    Executions
    2

    Elap per %
    Exec (s)
    19.7

    Total
    DB Time
    14.4

    SQL Id
    dccxnr2d9b3xd

    So far so good.
    Querying the I get the conclusion that AWR report in fact is computing the delta values of the snapshots ( snap(n) - snap(n-1) ), for example for executions number:

    select snap_id, executions_total from dba_hist_sqlstat
    where sql_id ='dccxnr2d9b3xd';

    SNAP_ID EXECUTIONS_TOTAL
    ---------- ----------------
    24139 1
    24140 3

    The problem is that when I want to retrieve the same information from the DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY whithin the same snapshot interval as follow:

    DECLARE
    cur DBMS_SQLTUNE.SQLSET_CURSOR;
    BEGIN

    OPEN cur FOR
    SELECT VALUE(P)
    FROM table(
    DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(24139,24140)) P;

    DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MYSQLSET2',
    populate_cursor => cur);
    END;
    /

    SELECT sql_id,elapsed_time,cpu_time,executions, round(((elapsed_time/1000000)/decode(executions, 0, 1, executions)),1) as ratio FROM TABLE (DBMS_SQLTUNE.select_sqlset (
    'MYSQLSET2', -- sqlset_name
    NULL, -- basic_filter
    NULL, -- object_filter
    NULL, -- ranking_measure1
    NULL, -- ranking_measure2
    NULL, -- ranking_measure3
    NULL, -- result_percentage
    NULL) -- result_limit
    )
    where sql_id ='dccxnr2d9b3xd'
    order by 2 desc;

    The result is the follow:

    SQL_ID /ELAPSED_TIME /CPU_TIME /EXECUTIONS /RATIO
    dccxnr2d9b3xd / 63592931 / 62241266 / 3 / 21.2

    When I query the previous sqlset_name I get 3 as the number of executions for sql_id ='dccxnr2d9b3xd' withing the same snapshot interval.
    Due this, I wondering myself if this function (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(snap_id1, spnap_id2)) ought compute the values in the same delta way as the AWR report.


    Can anyone please give me an explanation/interpretation about the difference in both results?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width