Order Bulk Dietz & Watson Landjaeger Sausages

Reports Metrics Load and Response Times Database (10g +)

From 10g added dynamic views and historical information to understand better and faster activity database. Although Statspack and AWR reports have the information, they are based on snapshots as a reference for analyzing a range. Intervals are usually 1 hour (automatic and default in 10g +) and often have to wait for the next snapshot to get an idea of \u200b\u200bthe current activity.
With the new dynamic views can know almost in real time which is the core activity referring to the following dynamic view:

V $ SYSMETRIC
: Metrics more recent and less recent
last minute (one sample every 15 ").

V $ SYSMETRIC_HISTORY

: Last time all samples (choose one sample per 
 minute) . 
 $ SYSMETRIC_SUMMARY 
 
 V: Summary of the activity of the last hour (maximum, minimum, average and standard deviation 
). 
 
 
 stories and views remain part of the information of the dynamic view of the last hour 
 (mmon process is responsible for copy some of the most important information of the view V $ a disc) and externalize result with the following views: 
 
 
 DBA_HIST_SYSMETRIC_HISTORY 
 
 
 DBA_HIST_SYSMETRIC_SUMMARY 
 
  With this information available gives a very detailed idea of \u200b\u200bthe activity and the load profile. Here is a query that uses the view summarized and returns among others, the same data found in the reports Statspack / AWR in the "Load Profile" in the column I tabbed a second:  
  select metric_name,  case (metric_id) 
 Then when to round 2016 (minval/1024/1024, 2) When 2058 
 Then round (minval/1024/1024, 2) 
 else round (minval, 2) end Min, 

case (metric_id) 2016

Then when to round (maxval / 1024/1024, 2)

when 2058 then round(maxval/1024/1024,2)
else round(maxval,2) end Max,

case (metric_id)
when 2016 then round(average/1024/1024,2)
when 2058 then round(average/1024/1024,2)

else round(average,2) end Avg,

   case (metric_id) 
   when 2016 then round(standard_deviation/1024/1024,2) 
   when 2058 then round(standard_deviation/1024/1024,2) 
   else round(standard_deviation,2) end STDDEV, 
   case (metric_id) 
   when 2016 then 'Mbytes Per Second' 
   when 2058 then 'Mbytes Per Second' 
   else metric_unit end metric_unit 
 from v$sysmetric_summary 
 where metric_id in (2003,2026.2004,2006,2016,2018,2030,  
                2044,2046,2058,2071,2075,2081,2123) 
 order by metric_id     
  
  
  

METRIC_NAME MIN MAX AVG STDDEV METRIC_UNIT
---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------------------------------------------------------------

User Transaction Per Sec 0 28.75 24.4 1.64 Transactions Per Second

 Physical Writes Per Sec                                                   0      29.63      21.91       2.25 Writes Per Second 
 Redo Generated Per Sec                                                     0        .06        .05          0 Mbytes Per Second 
 Logons Per Sec                                                            0       1.18        .93        .08 Logons Per Second 
 Logical Reads Per Sec                                                     0    1015.72     592.05      75.19 Reads Per Second 
 Total Parse Count Per Sec                                                 0      52.75      28.29       4.33 Parses Per Second 
 Hard Parse Count Per Sec                                                  0       7.24       1.29        .88 Parses Per Second 
 Network Traffic Volume Per Sec                                            0        .03        .02          0 Mbytes Per Second 
 DB Block Changes Per Sec                  0 339.75 287.39 19.15  Blocks Per Second CPU Usage Per Sec 0 11.27 9.88 51 Second  centiseconds Per User Rollback UndoRec Applied Per Sec 0 3 03 07 Records Per Second 
 Database Time Per Sec 0 72.48 26.67 08.01 Per Second 
 centiseconds 
 
 

previous data are available per transaction if you need it.

Now I will show how to obtain the metric based on percentiles, with ratios and percentages of the last two samples the last minute. The most newest of at most 15 seconds and the oldest is at most 60 seconds.

   
 select metric_name,  round (value, 2) value,  metric_unit 
 from v $ SYSMETRIC 

WHERE metric_name like '% \\%%' escape '\\'
or metric_name like '% Percent%' or metric_name like

'% Ratio%'

 
 
 
 METRIC_NAME METRIC_UNIT 

VALUE ------------------------------------- --------------------------- ---------- ------------- -------------------------------------------------- - Buffer Cache Hit Ratio

95.75% (their - PhyRead) / Achievements
Memory Sorts Ratio 100 % MemSort/(MemSort + DiskSort)

Redo Allocation Hit Ratio 100 % (#Redo - RedoSpaceReq)/#Redo

 User Commits Percentage                                                 100 % (UserCommit/TotalUserTxn) 
 User Rollbacks Percentage                                                 0 % (UserRollback/TotalUserTxn) 
 Cursor Cache Hit Ratio                                               232.71 % CursorCacheHit/SoftParse 
 Execute Without Parse Ratio                                           63.74 % (ExecWOParse/TotalExec) 

Soft Parse Ratio 96.05 % SoftParses/TotalParses

User Calls Ratio 33.48 % UserCalls/AllCalls Host CPU Utilization (%) 4.63 % Busy/(Idle+Busy)

PX downgraded 1 to 25% Per Sec 0 PX Operations Per Second
PX downgraded 25 to 50% Per Sec 0 PX Operations Per Second
PX downgraded 50 to 75% Per Sec 0 PX Operations Per Second
PX downgraded 75 to 99% Per Sec 0 PX Operations Per Second
User Limit % 0 % Sessions/License_Limit Database Wait Time Ratio 42.12 % Wait/DB_Time Database CPU Time Ratio 57.88 % Cpu/DB_Time
Row Cache Hit Ratio 99.75 % Hits/Gets
Row Cache Miss Ratio .25 % Misses/Gets Library Cache Hit Ratio 98.1 % Hits/Pins Library Cache Miss Ratio 1.9 % Misses/Gets
Shared Pool Free % 91.27 % Free/Total
PGA Cache Hit % 99.89 % Bytes/TotalBytes Process Limit % 24.7 % Processes/Limit Session Limit % 16.86 % Sessions/Limit
Streams Pool Usage Percentage 0 % Memory allocated / Size of Streams pool
Buffer Cache Hit Ratio 96.18 % (LogRead - PhyRead)/LogRead
Memory Sorts Ratio 100 % MemSort/(MemSort + DiskSort)
Execute Without Parse Ratio 64.59 % (ExecWOParse/TotalExec)
Soft Parse Ratio 95.72 % SoftParses/TotalParses
Host CPU Utilization (%) 4.39% Busy / (Idle + Busy)
Database CPU Time Ratio 15.8% Cpu / DB_Time Library Cache Hit Ratio 97.64% Hits / Pins Shared Pool Free% 91.28% Free / Total

Another query that I use is more simple and I just returned the overall response time and response time per transaction, both in seconds, so you can quickly analyze and detect if something is happening with the base. I have a reasonable idea of \u200b\u200bthe times for each base and if I see something that triggers me realize looking at only those two values. Below is the query I show how I use and output it.

select end_time,

 round (max (decode (metric_id, 2106, value/100, null)), 4) "SQLRTime" 
 round (max (decode (metric_id, 2109, value/100, null)), 4 ) "IMRT / Trx" from v $ sysmetric_history 
 
 WHERE metric_id in (2106.2109) 
 and end_time> sysdate-10/24/60 
 
 group by order by end_time end_time desc 
 
 
 
 END_TIME SQLRTime IMRT / Trx 
 18/11/2010 12:15:34 PM 0.0013 0.0172 0.0063 
 18/11/2010 12:14:33 pm 18/11/2010 12:13:33 pm 
 0.0843 0.1101 0.0087 
 18/11/2010 12:12:33 pm 
 0.1147 0.0039 0.009 0.1214 18/11/2010 12:11:34 pm 11/18/2010 12:10:34 pm 
 
 11/18/2010 12:09 0.0062 0.1145 : 34 pm 
 0.1102 0.0079 0.0081 0.1167 11/18/2010 12:08:34 pm 11/18/2010 12:07:34 pm 
 
 0.1112 0.0085 0.0078 0.1141 11/18/2010 12:06:34 pm 
 
 
 
 If you would like to see more ancient history or you are building a report summarizing historical and / or grouped by hour, day, week or month can use a historical view (DBA_HIST_xxx).