How to compare two awr snapshot periods in a single file?

Sometimes User may say, my DB was good yesterday the same period but today it is very slow.

At these times, we may face a scenario where we need to compare DB performance of specific time period of two different dates.

There are 2 awr related sql scripts which may come handy in this case.

· awrddrpt.sql

· awrddrpi.sql

The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

To generate an AWR Compare Periods report:

1. At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

2. Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

3. Specify the number of days for which you want to list snapshot Ids in the first time period.

Enter value for num_days: 2

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

4. Specify a beginning and ending snapshot ID for the first time period:

Enter value for begin_snap: 2

Enter value for end_snap: 3

In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.

5. Specify the number of days for which you want to list snapshot Ids in the second time period.

Enter value for num_days2: 1

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

6. Specify a beginning and ending snapshot ID for the second time period:

Enter value for begin_snap2: 26

Enter value for end_snap2: 27

In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.

7. Enter a report name, or accept the default report name:

Enter value for report_name:

Using the report name awrdiff_1_102_1_126.txt

In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated.

Sample comparison report :

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

How to find history of SGA/PAGA usage

For resource planning it is vital to know how the memory usage is changing during night/day  time. 

Most often the database load varies over time.  If you know the hourly usage of the SGA / PGA , you can better plan your resources or tune your instance.

You need to have diagnostic pack for your database to get this information.

Here is a query getting hourly  sga/pga memory usages of an Oracle instance. 

select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,

trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo 
   from DBA_HIST_SGASTAT 
  group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo 
    from DBA_HIST_PGASTAT where name = 'total PGA allocated' 
   group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn 
where sn.snap_id=sga.snap_id
  and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
  and sn.snap_id=pga.snap_id
  and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;

Sample Output

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

INSTANCE_NUMBER        SGA        PGA        TOT TIME
--------------- ---------- ---------- ---------- ---------
              1      69.75       .278     70.028 17-DEC-14
              1     69.751       .277     70.028 17-DEC-14
              1     69.753       .276     70.029 17-DEC-14
              1     69.764       .257     70.021 17-DEC-14
              1     69.798       .304     70.102 17-DEC-14
              1     69.798       .301     70.099 17-DEC-14
              1     69.798       .303     70.101 17-DEC-14
              1     69.798       .304     70.102 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.799       .307     70.106 17-DEC-14
              1       69.8       .309     70.109 17-DEC-14
              1       69.8       .308     70.108 17-DEC-14
              1     69.805       .304     70.109 16-DEC-14
              1     69.806       .304      70.11 16-DEC-14
              1     69.791       .301     70.092 16-DEC-14
              1     69.793       .292     70.085 16-DEC-14
              1     69.793        .29     70.083 16-DEC-14
 

Measuring average tablespace growth per day based on previous 7 day statistics.

Run this query and see..This query will consider the sysdate -7 days and makes the avg growth per day calculations for the tablespaces.


SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
  SELECT a.days, a.tsname, used_size_mb
  , used_size_mb - LAG (used_size_mb,1)  OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM (
      SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
       ,ts.tsname
       ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
      FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
       ,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
      WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
       AND ts.tsname = dt.tablespace_name  AND sp.begin_interval_time > sysdate-7
      GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
      ORDER BY ts.tsname, days
  ) A
) b GROUP BY b.tsname ORDER BY b.tsname
/

Sample output:


TABLESPACE_NAME                CUR_USED_SIZE_MB AVG_INCREAS_MB
------------------------------ ---------------- --------------
GWMONITOR                                 19.44              0
MEDIADMIN                                 74.56              0
MYVRNUSER                                  4.06              0
NPIDATA                                  5954.5              0
NSIDATA                                35847.67          27.43
NSIDATA01                             422923.03         266.93
NSIDATA03                                   .06              0
QUARTZ                                    42.75              0
SMRDATA                                10418.44              0
SYSAUX                                 14261.13           8.49
SYSTEM                                 10651.25          18.29
UNDOTBS1                                1109.88         -67.29
UNDOTBS2                                1383.88         -35.14
USERS                                     67.63              0
VPX                                     8399.31           1.65