-
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
-
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|