dba_hist_snapshot – Make DBA Life Easy

Find Oracle SQL Execution History Details

DBAs are always asked about the database performance issues, which are mostly caused by some SQL queries’ execution plan changes.

This post demonstrates how to get a SQL query performance details like executions, disk/physical read/write, cpu time, elapsed time and waiting time, etc.

With the help of those statistics of a specific SQL query, DBA can easily identify the issue, and remediate it by applying SQL plan baselines.

Subscribe to get access

Read more of this content when you subscribe today.

How to Check Oracle Database Current Number of Sessions / Processes and Maximum Utilization

1)From V$LICENSE View. It shows USER sessions only excluding database system sessions.

SQL> select SESSIONS_CURRENT,SESSIONS_HIGHWATER from v$license;

SESSIONS_CURRENT SESSIONS_HIGHWATER 
---------------- ------------------ 
 771             1346 

SESSIONS_CURRENT  : Current number of concurrent user sessions
SESSIONS_HIGHWATER: Highest number of concurrent user sessions since the instance started.

2) From v$resource_limit view. It shows both users and database system sessions.

SQL> select 
            RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE 
     from 
            v$resource_limit 
     where 
            RESOURCE_NAME in ('processes','sessions');

RESOURCE_NAME  CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------------- ------------------- --------------- ---------------
processes      842                 1414            4040
sessions       865                 1439            6096

CURRENT_UTILIZATION : Number of (resources, locks, or processes) 
                         currently being used.
    MAX_UTILIZATION : Maximum consumption of this resource since 
                     the last instance start-up

3)From AWR.

col STARTT format a20
col FINISHH format a20
col RESOURCE_NAME format a13

SQL>select     
           a.snap_id SNAP_ID,
           to_char(b.begin_interval_time,'YYYYMMDD-HH24:MI:SS')  STARTT,
           to_char(b.end_interval_time,'YYYYMMDD-HH24:MI:SS') FINISHH,
           a.resource_name,
           a.CURRENT_UTILIZATION,
           a.max_utilization ,
           a.INITIAL_ALLOCATION,
           a.LIMIT_VALUE
from       
           DBA_HIST_RESOURCE_LIMIT a, 
           DBA_HIST_SNAPSHOT b
where      
          ( a.resource_name like '%process%' or a.resource_name like '%session%')
and        a.snap_id=b.snap_id
and         a.instance_number=b.instance_number
and         a.instance_number=1
and         b.begin_interval_time > sysdate - 2
order by
         a.snap_id,
         a.resource_name;

SNAP_ID    STARTT               FINISHH              RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
---------- -------------------- -------------------- ------------- ------------------- --------------- ---------- ----------
 63141     20171214-10:00:13    20171214-11:00:15    processes     845 1414 4040 4040
 63141     20171214-10:00:13    20171214-11:00:15    sessions      869 1439 6096 6096
 63142     20171214-11:00:15    20171214-12:00:18    processes     847 1414 4040 4040
 63142     20171214-11:00:15    20171214-12:00:18    sessions      871 1439 6096 6096
 63143     20171214-12:00:18    20171214-13:00:21    processes     845 1414 4040 4040
 63143     20171214-12:00:18    20171214-13:00:21    sessions      870 1439 6096 6096
 63144     20171214-13:00:21    20171214-14:00:23    processes     842 1414 4040 4040
 63144     20171214-13:00:21    20171214-14:00:23    sessions      866 1439 6096 6096
 63145     20171214-14:00:23    20171214-15:00:26    processes     843 1414 4040 4040
 63145     20171214-14:00:23    20171214-15:00:26    sessions      869 1439 6096 6096
 63146     20171214-15:00:26    20171214-16:00:29    processes     852 1414 4040 4040
 63146     20171214-15:00:26    20171214-16:00:29    sessions      879 1439 6096 6096
 63147     20171214-16:00:29    20171214-17:00:32    processes     849 1414 4040 4040
 63147     20171214-16:00:29    20171214-17:00:32    sessions      873 1439 6096 6096
 63148     20171214-17:00:32    20171214-18:00:35    processes     848 1414 4040 4040
 63148     20171214-17:00:32    20171214-18:00:35    sessions      872 1439 6096 6096
 63149     20171214-18:00:35    20171214-19:00:02    processes     840 1414 4040 4040
 63149     20171214-18:00:35    20171214-19:00:02    sessions      864 1439 6096 6096
 63150     20171214-19:00:02    20171214-20:00:05    processes     841 1414 4040 4040
 63150     20171214-19:00:02    20171214-20:00:05    sessions      865 1439 6096 6096
 63151     20171214-20:00:05    20171214-21:00:08    processes     845 1414 4040 4040
 63151     20171214-20:00:05    20171214-21:00:08    sessions      871 1439 6096 6096
 63152     20171214-21:00:08    20171214-22:00:12    processes     850 1414 4040 4040
 63152     20171214-21:00:08    20171214-22:00:12    sessions      875 1439 6096 6096
 63153     20171214-22:00:12    20171214-23:00:15    processes     854 1414 4040 4040
 63153     20171214-22:00:12    20171214-23:00:15    sessions      880 1439 6096 6096
 63154     20171214-23:00:15    20171215-00:00:17    processes     849 1414 4040 4040
 63154     20171214-23:00:15    20171215-00:00:17    sessions      873 1439 6096 6096
 63155     20171215-00:00:17    20171215-01:00:21    processes     851 1414 4040 4040
 63155     20171215-00:00:17    20171215-01:00:21    sessions      877 1439 6096 6096
 63156     20171215-01:00:21    20171215-02:00:24    processes     837 1414 4040 4040
 63156     20171215-01:00:21    20171215-02:00:24    sessions      861 1439 6096 6096
 63157     20171215-02:00:24    20171215-03:00:27    processes     847 1414 4040 4040
 63157     20171215-02:00:24    20171215-03:00:27    sessions      872 1439 6096 6096
 63158     20171215-03:00:27    20171215-04:00:01    processes     846 1414 4040 4040
 63158     20171215-03:00:27    20171215-04:00:01    sessions      872 1439 6096 6096
 63159     20171215-04:00:01    20171215-05:00:04    processes     841 1414 4040 4040
 63159     20171215-04:00:01    20171215-05:00:04    sessions      865 1439 6096 6096
 63160     20171215-05:00:04    20171215-06:00:06    processes     839 1414 4040 4040
 63160     20171215-05:00:04    20171215-06:00:06    sessions      864 1439 6096 6096
 63161     20171215-06:00:06    20171215-07:00:09    processes     847 1414 4040 4040
 63161     20171215-06:00:06    20171215-07:00:09    sessions      871 1439 6096 6096
 63162     20171215-07:00:09    20171215-08:00:12    processes     843 1414 4040 4040
 63162     20171215-07:00:09    20171215-08:00:12    sessions      866 1439 6096 6096
 63163     20171215-08:00:12    20171215-09:00:14    processes     852 1414 4040 4040
 63163     20171215-08:00:12    20171215-09:00:14    sessions      878 1439 6096 6096

46 rows selected.

4) From OEM Repository.

 col COLUMN_LABEL format a30
 col MINIMUM format 99.99
 col MAXIMUM format 99.99
 col AVERAGE format 99.99

SQL>select 
           to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL, 
           MINIMUM,MAXIMUM,AVERAGE 
    from   MGMT$METRIC_DAILY 
   where   TARGET_NAME='RACTEST' 
     and   COLUMN_LABEL in ('Session Limit Usage (%)') 
   order by 
           ROLLUP_TIMESTAMP,
           COLUMN_LABEL;

TO_CHAR(RO COLUMN_LABEL                   MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Session Limit Usage (%)        2.87    6.40    5.22
2017-07-16 Session Limit Usage (%)        6.45   14.57   13.67
2017-07-17 Session Limit Usage (%)        7.96   14.40   14.07
2017-07-18 Session Limit Usage (%)       13.99   14.71   14.34
2017-07-19 Session Limit Usage (%)       14.14   14.94   14.63
2017-07-20 Session Limit Usage (%)       14.55   15.04   14.80
...
..
.
2017-12-04 Session Limit Usage (%)       14.88   15.88   15.47
2017-12-05 Session Limit Usage (%)       14.45   15.16   14.80
2017-12-06 Session Limit Usage (%)       13.99   14.98   14.58
2017-12-07 Session Limit Usage (%)       13.86   14.32   14.09
2017-12-08 Session Limit Usage (%)       13.88   14.37   14.12
2017-12-09 Session Limit Usage (%)       13.80   14.40   14.04
2017-12-10 Session Limit Usage (%)       13.91   14.44   14.06
2017-12-11 Session Limit Usage (%)       13.88   14.35   14.07
2017-12-12 Session Limit Usage (%)       13.96   14.30   14.12
2017-12-13 Session Limit Usage (%)       13.98   14.44   14.18
2017-12-14 Session Limit Usage (%)       14.09   14.42   14.24

--
--

SQL> select 
            to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL, 
            MINIMUM,MAXIMUM,AVERAGE 
       from MGMT$METRIC_DAILY 
      where TARGET_NAME='RACTEST' 
        and COLUMN_LABEL in ('Process Limit Usage (%)') 
   order by ROLLUP_TIMESTAMP,
            COLUMN_LABEL;

TO_CHAR(RO COLUMN_LABEL                   MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Process Limit Usage (%)        3.76    9.16    7.33
2017-07-16 Process Limit Usage (%)        9.21   21.31   20.00
2017-07-17 Process Limit Usage (%)       11.34   21.09   20.60
2017-07-18 Process Limit Usage (%)       20.50   21.49   21.00
2017-07-19 Process Limit Usage (%)       20.74   21.83   21.44
2017-07-20 Process Limit Usage (%)       21.36   22.00   21.71
...
..
.
2017-12-09 Process Limit Usage (%)       20.25   20.99   20.58
2017-12-10 Process Limit Usage (%)       20.40   21.16   20.61
2017-12-11 Process Limit Usage (%)       20.37   21.01   20.63
2017-12-12 Process Limit Usage (%)       20.47   20.97   20.70
2017-12-13 Process Limit Usage (%)       20.52   21.06   20.78
2017-12-14 Process Limit Usage (%)       20.72   21.11   20.89

Tablespace Usage History Report

Useful report to show tablespace daily usage and space usage increasing ratio.

Please make sure you are licensed to run sqls against DBA_HIST_*, like dba_hist_tbspc_space_usage .

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS

NAME                            TYPE     VALUE
------------------------------- -------- -----------------------
control_management_pack_access  string   DIAGNOSTIC+TUNING

The length of the report depends on AWR retention. Please refer to “Change AWR Retention & Interval” for how to change AWR retention.

select THE_DATE,
       TABLESPACE,
       SIZE_IN_GB_TD,
       SIZE_IN_GB_YTD,
       SIZE_IN_GB_TD - SIZE_IN_GB_YTD  SIZE_IN_GB_INCREASED
from ( 
     select 
            to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') THE_DATE,
            tbl.name TABLESPACE,
            round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2) SIZE_IN_GB_TD,
            lag( round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2),1) over ( order by to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') ) SIZE_IN_GB_YTD 
     from 
          dba_hist_snapshot histsnap,
          dba_hist_tbspc_space_usage tblusage,
          v$tablespace tbl,
          v$parameter pmt
    where 
          histsnap.SNAP_ID = tblusage.SNAP_ID
      and tbl.ts# = tblusage.TABLESPACE_ID
      and tbl.name ='&TABLESPACENAME'
      and pmt.name ='db_block_size'
   group by 
          to_char( histsnap.begin_interval_time, 'YYYY-MM-DD'),
          tbl.name,
          pmt.value
 order by 
         to_char( histsnap.begin_interval_time, 'YYYY-MM-DD')
 );

Enter value for tablespacename: USERS
old 17: and tbl.name ='&TABLESPACENAME'
new 17: and tbl.name ='USERS'

THE_DATE   TABLESPACE SIZE_IN_GB_TD SIZE_IN_GB_YTD SIZE_IN_GB..ASED
---------- ---------- ------------- -------------- -----------------
2017-05-10 USERS        4.17
2017-05-11 USERS        4.17         4.17            0
2017-05-12 USERS        4.45         4.17             .28
2017-05-13 USERS        4.55         4.45             .1
2017-05-14 USERS        4.55         4.55            0
2017-05-15 USERS        4.56         4.55             .01
2017-05-16 USERS        4.84         4.56             .28
2017-05-17 USERS        4.96         4.84             .12
2017-05-18 USERS        5.14         4.96             .18
2017-05-19 USERS        5.14         5.14            0

10 rows selected.

Change AWR Retention and Interval

Use “dbms_workload_repository.modify_snapshot_settings” to change AWR retention and interval.

Check current Retention and Interval

RETENTION = 8 days ( 11520 Mins ) ( Default )
INTERVAL = 1 hour ( 60 Mins ) ( Default )

SQL> desc dba_hist_wr_control
 Name          Null?     Type
 ------------- --------- --------------------------
 DBID          NOT NULL NUMBER
 SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION NOT NULL     INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL                VARCHAR2(10)

SQL> select dbid from v$database;

DBID
----------
2920212463

SQL> col SNAP_INTERVAL format a30
SQL> col RETENTION format a30
SQL> select dbid,SNAP_INTERVAL,RETENTION 
       from dba_hist_wr_control;

 DBID      SNAP_INTERVAL       RETENTION
---------- ------------------- ------------------------------
2920212463 +00000 01:00:00.0   +00008 00:00:00.0

SQL> col SNAP_INTERVAL_MINS format a40
SQL> col RETENTION_MINS format a40
SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,
            RETENTION*24*60 RETENTION_MINS 
       from dba_hist_wr_control;

 DBID      SNAP_INTERVAL_MINS            RETENTION_MINS
---------- ----------------------------  -----------------------------
2920212463 +000000060 00:00:00.000000000 +000011520 00:00:00.000000000

Change Retention and Interval

Retention from 8 days ( 11520 Mins ) —-> 31 days ( 44640 Mins )
Interval from 1 hour ( 60 Mins ) —-> 0.5 hour ( 30 Mins )

SQL>exec dbms_workload_repository.modify_snapshot_settings
                                 (retention=>44640,
                                  interval=>30,
                                      dbid=>2920212463);

PL/SQL procedure successfully completed.

Check New Retention and Interval

SQL> select dbid,SNAP_INTERVAL,RETENTION 
       from dba_hist_wr_control;

DBID S     NAP_INTERVAL      RETENTION
---------- ----------------- ---------------------
2920212463 +00000 00:30:00.0 +00031 00:00:00.0

SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,
                     RETENTION*24*60 RETENTION_MINS 
       from dba_hist_wr_control;

 DBID SNAP_INTERVAL_MINS                   RETENTION_MINS
---------- ------------------------------- ----------------------------
2920212463 +000000030 00:00:00.000000000   +000044640 00:00:00.000000000

Check SYSAUX Tablespace Usage

SQL> SELECT occupant_name, space_usage_kbytes/1024 MBS 
       FROM V$SYSAUX_OCCUPANTS 
   order by 2;

OCCUPANT_NAME             MBS
------------------------- ----------
TSM                         0
EXPRESSION_FILTER           0
ULTRASEARCH_DEMO_USER       0
...
..
.
SM/ADVISOR                 69.3125
AUDIT_TABLES              136.1875
SM/OPTSTAT                406.4375
SM/AWR                   1633.3125

31 rows selected.