I like Load profile section of Statspack or AWR reports (who doesn’t). It’s short and gives a brief understanding of what kind of work a database does. But what if you don’t have an access to Statspack or AWR but still want to see something similar? It’s possible to use V$SYSMETRIC to get this numbers for last 60 or 15 seconds. I wanted to write a script to do this for a long time. Here it is.
col short_name format a20 heading 'Load Profile' col per_sec format 999,999,999.9 heading 'Per Second' col per_tx format 999,999,999.9 heading 'Per Transaction' set colsep ' ' select lpad(short_name, 20, ' ') short_name , per_sec , per_tx from (select short_name , max(decode(typ, 1, value)) per_sec , max(decode(typ, 2, value)) per_tx , max(m_rank) m_rank from (select /*+ use_hash(s) */ m.short_name , s.value * coeff value , typ , m_rank from v$sysmetric s, (select 'Database Time Per Sec' metric_name, 'DB Time' short_name, .01 coeff, 1 typ, 1 m_rank from dual union all select 'CPU Usage Per Sec' metric_name, 'DB CPU' short_name, .01 coeff, 1 typ, 2 m_rank from dual union all select 'Redo Generated Per Sec' metric_name, 'Redo size' short_name, 1 coeff, 1 typ, 3 m_rank from dual union all select 'Logical Reads Per Sec' metric_name, 'Logical reads' short_name, 1 coeff, 1 typ, 4 m_rank from dual union all select 'DB Block Changes Per Sec' metric_name, 'Block changes' short_name, 1 coeff, 1 typ, 5 m_rank from dual union all select 'Physical Reads Per Sec' metric_name, 'Physical reads' short_name, 1 coeff, 1 typ, 6 m_rank from dual union all select 'Physical Writes Per Sec' metric_name, 'Physical writes' short_name, 1 coeff, 1 typ, 7 m_rank from dual union all select 'User Calls Per Sec' metric_name, 'User calls' short_name, 1 coeff, 1 typ, 8 m_rank from dual union all select 'Total Parse Count Per Sec' metric_name, 'Parses' short_name, 1 coeff, 1 typ, 9 m_rank from dual union all select 'Hard Parse Count Per Sec' metric_name, 'Hard Parses' short_name, 1 coeff, 1 typ, 10 m_rank from dual union all select 'Logons Per Sec' metric_name, 'Logons' short_name, 1 coeff, 1 typ, 11 m_rank from dual union all select 'Executions Per Sec' metric_name, 'Executes' short_name, 1 coeff, 1 typ, 12 m_rank from dual union all select 'User Rollbacks Per Sec' metric_name, 'Rollbacks' short_name, 1 coeff, 1 typ, 13 m_rank from dual union all select 'User Transaction Per Sec' metric_name, 'Transactions' short_name, 1 coeff, 1 typ, 14 m_rank from dual union all select 'User Rollback UndoRec Applied Per Sec' metric_name, 'Applied urec' short_name, 1 coeff, 1 typ, 15 m_rank from dual union all select 'Redo Generated Per Txn' metric_name, 'Redo size' short_name, 1 coeff, 2 typ, 3 m_rank from dual union all select 'Logical Reads Per Txn' metric_name, 'Logical reads' short_name, 1 coeff, 2 typ, 4 m_rank from dual union all select 'DB Block Changes Per Txn' metric_name, 'Block changes' short_name, 1 coeff, 2 typ, 5 m_rank from dual union all select 'Physical Reads Per Txn' metric_name, 'Physical reads' short_name, 1 coeff, 2 typ, 6 m_rank from dual union all select 'Physical Writes Per Txn' metric_name, 'Physical writes' short_name, 1 coeff, 2 typ, 7 m_rank from dual union all select 'User Calls Per Txn' metric_name, 'User calls' short_name, 1 coeff, 2 typ, 8 m_rank from dual union all select 'Total Parse Count Per Txn' metric_name, 'Parses' short_name, 1 coeff, 2 typ, 9 m_rank from dual union all select 'Hard Parse Count Per Txn' metric_name, 'Hard Parses' short_name, 1 coeff, 2 typ, 10 m_rank from dual union all select 'Logons Per Txn' metric_name, 'Logons' short_name, 1 coeff, 2 typ, 11 m_rank from dual union all select 'Executions Per Txn' metric_name, 'Executes' short_name, 1 coeff, 2 typ, 12 m_rank from dual union all select 'User Rollbacks Per Txn' metric_name, 'Rollbacks' short_name, 1 coeff, 2 typ, 13 m_rank from dual union all select 'User Transaction Per Txn' metric_name, 'Transactions' short_name, 1 coeff, 2 typ, 14 m_rank from dual union all select 'User Rollback Undo Records Applied Per Txn' metric_name, 'Applied urec' short_name, 1 coeff, 2 typ, 15 m_rank from dual) m where m.metric_name = s.metric_name and s.intsize_csec > 5000 and s.intsize_csec < 7000) group by short_name) order by m_rank;
Couple of notes:
- It’s a simple piece of code with most of it being copy-paste to select data that is only required and to sort it the same way as AWR does
- I’ve deliberately supplied a USE_HASH hint to the query because on one DB without a fixed objects statistics (or wrong – not sure) Oracle decided to do a NESTED LOOP with a FULL SCAN of a huge in-memory structure so the query took around a second to execute. Don’t wanna that happen
- It’s possible to utilize PIVOT in the query, but it’ll limit applicability to 11g only – and I want this SQL to be able to run on 10g too
And here is a sample output. Looks familiar π
Load Profile Per Second Per Transaction -------------------- -------------- --------------- DB Time .7 DB CPU .7 Redo size 217,570.3 4,283,958.7 Logical reads 4,177.1 82,248.0 Block changes 1,475.5 29,053.0 Physical reads .1 2.0 Physical writes .4 8.3 User calls .3 6.0 Parses 8.2 161.3 Hard Parses 7.3 144.0 Logons .1 1.3 Executes 722.3 14,222.3 Rollbacks .0 Transactions .1 Applied urec .1 1.0
11 Comments
Hi Timur,
seems very familiar….maybe I’ve seen it in somewhere… π
Ciao
awesome query…one question i had was.. you mentioned this query can be used to get data for last 15 or 60 seconds…
first of all…the current query you have…dose that show data for last 15 or last 60 seconds…
second…if i want to get the data for lets say last 5 or 10 mins…how do i change the query ?
Hi,
plz check the doc
V$SYSMETRIC displays the system metric values captured for the most current time interval for both the long duration (60-second) and short duration (15-second) system metrics.
Regards
GregG
Hi Amy
You can use V$SYSMETRIC_HISTORY but take avarages (as AWR does)
— Kirill Loifman
Thank you this is extremely useful!
Thank you a lot!
You’re welcome.
With your above query inspiration,
I tired for the AWR load profile.
Good, thanks π
The AWR query doesn’t seems to be working..
ERROR at line 56:
ORA-00942: table or view does not exist
Hi, most likely you have no rights to access those tables.
Great script Timur, thanks for time spent. ΡΡΠΏΠ΅Ρ!
you might provide one for the last hour using V$SYSMETRIC_HISTORY
— Kirill Loifman
3 Trackbacks
[…] here’s a link to a really smart piece of SQL from Timur Ahkmadeev which uses v$sysmetric to emulate the output of the statspack/AWR profile over […]
[…] a query from a v$ view.. luckily there’s a script created by Timur Akhmadeev a few months ago https://timurakhmadeev.wordpress.com/2012/02/21/load-profile/ that outputs the same Load Profile section of the AWR report but pulls data from the v$sysmetric […]
[…] Load profile SQL Dashboard V2.1 […]