Proactive Monitoring of DB Time for better Analysis
In the above graph we see there is a spike , one can generate AWR and look into what went wrong at a particular snap time and correct it before it becomes major problem. This helps to monitor DB 24 X 7 .
Fine the Query below which gets the DB time for the whole week.
col HOST_NAME format A15 trunc
col INSTANCE_NUMBER format 9 heading “I”
col begin_interval_time_N noprint
col end_interval_time_N noprint
select A.SNAP_ID , A.INSTANCE_NUMBER , C.HOST_NAME , MIN(A.begin_interval_time) OVER (partition by A.dbid, A.snap_id) AS begin_interval_time_N , MIN(A.end_interval_time) OVER (partition by A.dbid, A.snap_id) AS end_interval_time_N , (B. VALUE – LAG(B.VALUE,1,0) OVER (ORDER BY A.SNAP_ID))/1000000/60 as DB_TIME from dba_hist_snapshot A , DBA_HIST_SYS_TIME_MODEL B,dba_hist_database_instance C where A.SNAP_ID=B.SNAP_ID and A.DBID=B.DBID And A.INSTANCE_NUMBER=B.INSTANCE_NUMBER and B.STAT_NAME Like ‘DB time’ and A.DBID=C.DBID and A.INSTANCE_NUMBER=C.INSTANCE_NUMBER and A.startup_time = C.startup_time and A.begin_interval_time >= trunc(sysdate) – 7 and A.begin_interval_time <= trunc(sysdate) + 20/(26*60)
Database time is total time spent by user processes either actively working or actively waiting in a database call. To make it clearer, DB Time is the time spent in the database by foreground sessions which includes CPU time, IO time and wait time for a particular snap interval. It excludes idle wait time.
DB Time and Wait Events are directly proportional. The first thing one will be looking while reviewing the AWR is the DB time, if it is more and multiples of elapsed time then one has to jump to the TOP 5 Timed Events sessions to investigate further.Ideally DBAs will be reactively looking at AWR for performance problems, but one has to Proactively look at the AWR reports periodically to see if things are fine or not. But it is very difficult to generate AWR or review AWRs for all the snap intervals of Production database as we will be having 7 X 24 AWR reports for a week (1 hour snap interval).
The following single query will help the DBAs to generate the DB time of all Snap Intervals of a week. Based on the data generated, one can identify the high DB time snaps and generate AWR reports to investigate proactively before it gets into worst. We have automated the query which will generate Graph and mail it to the DBAs who owns the databases using the Ploticaus tool to plot graph in linux. (http://ploticus.sourceforge.net/doc/welcome.html)
In the above graph we see there is a spike , one can generate AWR and look into what went wrong at a particular snap time and correct it before it becomes major problem. This helps to monitor DB 24 X 7 .
Fine the Query below which gets the DB time for the whole week.
col snap_id format A8
col begin_interval_time_N format A30 trunc
col end_interval_time_N format A30 trunccol HOST_NAME format A15 trunc
col INSTANCE_NUMBER format 9 heading “I”
col begin_interval_time_N noprint
col end_interval_time_N noprint
select A.SNAP_ID , A.INSTANCE_NUMBER , C.HOST_NAME , MIN(A.begin_interval_time) OVER (partition by A.dbid, A.snap_id) AS begin_interval_time_N , MIN(A.end_interval_time) OVER (partition by A.dbid, A.snap_id) AS end_interval_time_N , (B. VALUE – LAG(B.VALUE,1,0) OVER (ORDER BY A.SNAP_ID))/1000000/60 as DB_TIME from dba_hist_snapshot A , DBA_HIST_SYS_TIME_MODEL B,dba_hist_database_instance C where A.SNAP_ID=B.SNAP_ID and A.DBID=B.DBID And A.INSTANCE_NUMBER=B.INSTANCE_NUMBER and B.STAT_NAME Like ‘DB time’ and A.DBID=C.DBID and A.INSTANCE_NUMBER=C.INSTANCE_NUMBER and A.startup_time = C.startup_time and A.begin_interval_time >= trunc(sysdate) – 7 and A.begin_interval_time <= trunc(sysdate) + 20/(26*60)
No comments:
Post a Comment