Home » RDBMS Server » Performance Tuning » AWR report analysis (Oracle Serer, 10g R2 , RH)
AWR report analysis [message #394751] Mon, 30 March 2009 02:34
John Charles
Messages: 5
Registered: January 2004
Junior Member
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

[Updated on: Mon, 30 March 2009 16:52] by Moderator

Report message to a moderator

Previous Topic: Performance tuning
Next Topic: What if not give any ext in datafile........
Goto Forum:
  


Current Time: Sun Dec 04 14:52:09 CST 2016

Total time taken to generate the page: 0.08734 seconds