Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

AWR report analysis

Status
Not open for further replies.

cottoncrown

Programmer
Joined
Mar 30, 2009
Messages
1
Location
LU
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
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.
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top