Dba Hist Sysstat

Version: 10gR2
Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3192.htm

Column Datatype Description
SNAP_ID NUMBER Unique snapshot ID
DBID NUMBER Database ID for the snapshot
INSTANCE_NUMBER NUMBER Instance number for the snapshot
STAT_ID NUMBER Statistic identifier
STAT_NAME VARCHAR2(64) Statistic name
VALUE NUMBER Statistic value

Example 1: Check open cursor

select snap_id,value from dba_hist_sysstat where stat_name = 'opened cursors current';

Example 2: Show physical reads

Reference:
http://www.ludovicocaldara.net/dba/tag/dba_hist_sysstat/

More than often I’m asked to investigate “what happened yesterday when performance problems appeared”.

Sometimes I have the Enterprise Manager DB Console licensed, sometimes not. Sometimes I have direct SQL*Net access to the database that I may use to produce custom reports with my LAMP self-developed application. But it may happen that only an ssh access is granted to the db server.

That’s why I started to develop some little scripts to plot the trends of database timed statistics.

Let’s see this one:

SQL> @sysstat.sql
Enter a sysstat to search for: physical reads

STAT_ID     STAT_NAME
----------- ------------------------------------------
2263124246 physical reads <-----
4171507801 physical reads cache
297908839 physical reads cache prefetch
2589616721 physical reads direct
2564935310 physical reads direct (lob)
2663793346 physical reads direct temporary tablespace
473165409 physical reads for flashback new
3102888545 physical reads prefetch warmup
531193461 physical reads retry corrupt

9 rows selected.

Enter the desired stat_id: 2263124246 <----- paste value here
Enter the start date (YYYYMMDD) [defaults today] : 20080922 <----- 
Enter the end date date (YYYYMMDD) [defaults today] : 20080922 <-----

STAT_NAME        START    END
---------------- -------- --------
physical reads   20080922 20080922

BEGIN_INTERVAL_TIME           VALORE PLOTTED_VALUE
------------------------- ---------- -------------------------
22-SEP-08 12.00.12.122 AM          0
22-SEP-08 01.00.28.253 AM     120092
22-SEP-08 02.00.05.039 AM      35780
22-SEP-08 03.00.55.595 AM       4792
22-SEP-08 04.00.43.725 AM       4905
22-SEP-08 05.00.31.855 AM       7300
22-SEP-08 06.00.17.017 AM     234596
22-SEP-08 07.00.08.132 AM      24651
22-SEP-08 08.00.50.936 AM     481884
22-SEP-08 09.00.33.488 AM     130201
22-SEP-08 10.00.03.805 AM    1300306 **
22-SEP-08 11.00.07.764 AM     491857
22-SEP-08 12.00.31.548 PM     304702
22-SEP-08 01.01.04.880 PM    1023664 *
22-SEP-08 02.00.17.822 PM    8588180 ************
22-SEP-08 03.00.36.969 PM    2201615 ***
22-SEP-08 04.01.01.397 PM   17237098 *************************
22-SEP-08 05.00.39.262 PM    1606300 **
22-SEP-08 06.00.03.829 PM     451568
22-SEP-08 07.00.31.461 PM     137684
22-SEP-08 08.00.05.966 PM     203803
22-SEP-08 09.00.24.829 PM     536394
22-SEP-08 10.00.12.945 PM   10209783 **************
22-SEP-08 11.00.35.123 PM    6151663 *********

24 rows selected.

Oh! At 4.00 PM we had a lot of physical reads. Nice.

This is the code:

-- display given statistics from DBA_HIST_SYSSTAT
col BEGIN_INTERVAL_TIME FOR a25
SET pages 100 LINES 130
SET verify off term ON
 
accept sysstat prompt 'Enter a sysstat to search for: '
SELECT STAT_ID, STAT_NAME
  FROM DBA_HIST_STAT_NAME
   WHERE LOWER(STAT_NAME) LIKE LOWER('%&sysstat%')
  ORDER BY stat_name;
 
accept stat_id prompt 'Enter the desired stat_id: '
accept startdate prompt 'Start date (YYYYMMDD) [today] : '
accept enddate prompt 'End date date (YYYYMMDD) [today] : '
 
SELECT STAT_NAME,
  nvl('&startdate',to_char(sysdate,'YYYYMMDD')) AS "START",
  nvl('&enddate',to_char(sysdate,'YYYYMMDD')) AS "END"
 FROM DBA_HIST_STAT_NAME
WHERE STAT_ID = &stat_id;
 
SELECT BEGIN_INTERVAL_TIME, VALORE,
  substr( rpad('*',40*round( VALORE/MAX(VALORE)OVER(),2),'*'),1,40) PLOTTED_VALORE
  FROM (
  SELECT s.BEGIN_INTERVAL_TIME BEGIN_INTERVAL_TIME,
    nvl(decode(greatest(VALUE, nvl(lag(VALUE) OVER
      (partition BY s.dbid, s.instance_number, g.stat_name ORDER BY s.snap_id),0)),
    VALUE,
    VALUE - lag(VALUE) OVER
      (partition BY s.dbid, s.instance_number, g.stat_name ORDER BY s.snap_id),VALUE), 0) VALORE
  FROM DBA_HIST_SNAPSHOT s,
          DBA_HIST_SYSSTAT g,
          v$instance i
  WHERE s.SNAP_ID=g.SNAP_ID
  AND g.STAT_ID='&stat_id'
  AND s.BEGIN_INTERVAL_TIME >=
    trunc(to_timestamp(nvl('&startdate',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD'))
  AND s.BEGIN_INTERVAL_TIME < =
   trunc(to_timestamp(nvl('&enddate',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD')+1)
  AND s.instance_number=i.instance_number
  AND s.instance_number=g.instance_number
  ORDER BY 1
);