Plot Oracle historical statistics within SQL*Plus

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

----------- ------------------------------------------

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
Enter the start date (YYYYMMDD) [defaults today] : 20080922
Enter the end date date (YYYYMMDD) [defaults today] : 20080922

---------------- -------- --------
physical reads   20080922 20080922

------------------------- ---------- -------------------------
22-SEP-08 AM          0
22-SEP-08 AM     120092
22-SEP-08 AM      35780
22-SEP-08 AM       4792
22-SEP-08 AM       4905
22-SEP-08 AM       7300
22-SEP-08 AM     234596
22-SEP-08 AM      24651
22-SEP-08 AM     481884
22-SEP-08 AM     130201
22-SEP-08 AM    1300306 **
22-SEP-08 AM     491857
22-SEP-08 PM     304702
22-SEP-08 PM    1023664 *
22-SEP-08 PM    8588180 ************
22-SEP-08 PM    2201615 ***
22-SEP-08 PM   17237098 *************************
22-SEP-08 PM    1606300 **
22-SEP-08 PM     451568
22-SEP-08 PM     137684
22-SEP-08 PM     203803
22-SEP-08 PM     536394
22-SEP-08 PM   10209783 **************
22-SEP-08 PM    6151663 *********

24 rows selected.

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

This is the code:



The following two tabs change content below.


Principal Product Manager at Oracle
Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

11 thoughts on “Plot Oracle historical statistics within SQL*Plus

  1. Thanks. Its great

    What if we want to just to a daily sum and not per snap reading.

    SO I just want to see per day and not per snap ID.

    • Well, in that case you’ll need some aggregation per day…
      Like profs use to say: “the solution of this problem is trivial and is left as an exercise for the reader” 🙂

  2. Ludovico,

    Nice work!!!.

    Please request to validate below SQL can be used for RAC aware script!!

    select instance_number,SNAP_ID, BEGIN_INTERVAL_TIME, VALORE,
    substr( rpad(‘*’,40*round( VALORE/max(VALORE)over(),2),’*’),1,40) PLOTTED_VALORE
    from (
    select s.instance_number instance_number,s.SNAP_ID,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 – lag(VALUE) over
    (partition by s.dbid, s.instance_number, g.stat_name order by s.snap_id),VALUE), 0) VALORE
    gv$instance i
    where s.SNAP_ID=g.SNAP_ID
    and g.STAT_ID=’&stat_id’
    and s.instance_number=i.instance_number
    and s.instance_number=g.instance_number
    order by 1


  3. Pingback: Querying DBA_HIST_SNAPSHOT and DBA_HIST_SYSSTAT « flashdba

  4. Line
    substr( rpad(‘*’,40*round( VALUE/max(VALORE)over(),2),’*’),1,40) PLOTTED_VALORE

    should be:
    substr( rpad(‘*’,40*round( VALORE/max(VALORE)over(),2),’*’),1,40) PLOTTED_VALORE

    Otherwise – very good post! Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.