Help - Search - Members - Calendar
Full Version: DB time percentage from dba_sqlstat
Oracle DBA Forums > Oracle > Oracle Forum
Mowgli
Hi,
In this select I would like to get db time percentage for every sql_id as I can see in AWR report.
How can I do this?

select
sql.sql_id ,
sql.executions_delta ,
round(sql.elapsed_time_delta/1000000) elapsed_sec,
round(sql.elapsed_time_delta/1000000)/sql.executions_delta elapsed_exec,
round(sql.cpu_time_delta/1000000) cpu_sec,
round(sql.cpu_time_delta/1000000)/sql.executions_delta cpu_exec,
sql.buffer_gets_delta ,
sql.disk_reads_delta ,
sql.iowait_delta ,
sql.apwait_delta ,
sql.ccwait_delta
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.snap_id between 69041 and 69043
and sql.elapsed_time_delta=0
and sql.cpu_time_delta=0
burleson
Hi Mowgli,

>> I would like to get db time percentage for every sql_id as I can see in AWR report.

Sorry, this does not make sense!

In AWR. DB time is the amount of elapsed time spent performing Database user-level calls.

The db time percentage for a SQL statement indicates the cumulative time that is spent processing user requests, so the db time percentage is a system metric.

For a SQL statement, the db time does not really apply:

CODE
Statistic Name                     Time (s) Percent of Total DB Time

---------------------------------- -------- ------------------------
DB time                                           169
sql execute elapsed time                   156                       93
DB CPU                                           153                       90
PL/SQL execution elapsed time            77                       46
background cpu time                          53                       31
parse time elapsed                               6                        4
hard parse elapsed time                       4                        3
connection management call elapsed     0                        0


Normally, DB time is displayed in the v$sess_time_model and v$sys_time_model views.

However, you probably want this, from dba_hist_sys_time_model:


CODE
select . . . .  
from
   dba_hist_sys_time_model,
   dba_hist_sqlstat
where
   stat_name = 'db time'
and . . .;


***************************************
For historical SQL, any SQL ID has execution statistics in the dba_hist_sqlstat table . . .


CODE
Elapsed      CPU                  Elap per  % Total

  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id

---------- ---------- ------------ ---------- ------- -------------
     4,504         78            2     2251.9    61.9 6zmdns6h6xm5p

Module: SQL*Plus

DECLARE
feeval NUMBER;
BEGIN
  FS_LSQ.sp_funds_val(Feeval,'1003');
  dbms_output.put_line('Feeval = '||TO_CHAR(Feeval));
END;

     3,434         62           74       46.4    47.2 dahmxun9ngx14

Module: SQL*Plus

SELECT NVL(SUM(PAYMENTS.AMT) * :B4 ,0) FROM PAYMENTS, TRANSACTIONS WHERE ( (TRAN
SACTIONS.TRANSKEY = PAYMENTS.TRANSKEY) AND (TRANSACTIONS.CLIENTKEY = :B3 ) AND (
PAYMENTS.TYPE_ <> 7) AND PAYMENTS.AMT>0 AND (PAYMENTS.COLSTATUS = 2) AND (PAYMEN
TS.POSTDATE=:B2 +:B1 ) )


The DB time Oracle metric is the amount of elapsed time (in microseconds) spent performing Database user-level calls, and db time is a value that Oracle calculates to indicate the cumulative time that is spent processing user requests . . .

If you want to download a pre-tested working script to display details for SQL in AWR, see here:

http://www.dba-oracle.com/oracle_scripts.htm

Good Luck!
Mowgli
From AWR report in "SQL ordered by Elapsed Time" section you can find:
CODE

Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
1,574 409 6 262.29 42.29 gzf5n0z44hjfc UPDATE...


how can I find "% Total DB Time " value from DBA_HIST_* views for a specific sql_id?
I hope I have been more clear this time.
Mowgli
Is it possble to to it?
burleson
Hi

>> % Total DB Time

This is the elapsed_time for the SQL (from dba_hist_sqlstat) divided into the total system "db time" (probably from dba_hist_sys_time_model and/or other AWR tables) . . .

See dba_hist_sys_time_model where stat_name = "db time" . . .

Something like this, I don't remember the exact code . . .

CODE
select . . . .  
from
   dba_hist_sys_time_model,
   dba_hist_sqlstat
   dba_hist_snapshot
where
   stat_name = 'db time'
and
   snapshot_id = xxx
and
. . .;


Like I said, you can download scripts that do this already:

http://www.dba-oracle.com/oracle_scripts.htm

Good Luck!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.