Oracle Historical Session Information with ASH >10g



In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time:

You can use this 2 views to obtain this information:

v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes
dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour.


v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types:

set linesize 190
col  METRIC_NAME for a100
select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ;


for example, if you are looking for metrics related with connected sessions to database, you can do:

SQL> set linesize 190
col  METRIC_NAME for a100
select METRIC_ID, METRIC_NAME from v$sysmetric_history where lower(METRIC_NAME) like '%session%' group by METRIC_ID, METRIC_NAME ;
SQL> SQL>
 METRIC_ID METRIC_NAME
---------- ----------------------------------------------------------------------------------------------------
      2137 PQ QC Session Count
      2143 Session Count
      2138 PQ Slave Session Count
      2119 Session Limit %
      2147 Average Active Sessions
      2148 Active Serial Sessions
      2149 Active Parallel Sessions

7 rows selected.

and if you want to know number of sessions connected along time, you can use this view if you are looking for information of the last hour selecting a metric_id what you want:
set linesize 190
set pages 200
col METRIC_NAME for a40
col BEGIN_TIME for a20
col END_TIME for a20
COL METRIC_UNIT for a20
alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
select * from v$sysmetric_history where METRIC_ID=2143 order by BEGIN_TIME;



SQL> set linesize 190
SQL> set pages 200
SQL> col METRIC_NAME for a40
SQL> col BEGIN_TIME for a20
SQL> col END_TIME for a20
SQL> COL METRIC_UNIT for a20
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
select BEGIN_TIME, END_TIME, VALUE, METRIC_UNIT  from v$sysmetric_history where METRIC_ID=2143 order by BEGIN_TIME;

Session altered.

SQL>
BEGIN_TIME           END_TIME                  VALUE METRIC_UNIT
-------------------- -------------------- ---------- --------------------
09.10.2017 12:24:09  09.10.2017 12:25:10         357 Sessions
09.10.2017 12:25:10  09.10.2017 12:26:10         358 Sessions
09.10.2017 12:26:10  09.10.2017 12:27:09         355 Sessions
09.10.2017 12:27:09  09.10.2017 12:28:10         356 Sessions
09.10.2017 12:28:10  09.10.2017 12:29:09         355 Sessions
09.10.2017 12:29:09  09.10.2017 12:30:10         355 Sessions
09.10.2017 12:30:10  09.10.2017 12:31:10         355 Sessions
09.10.2017 12:31:10  09.10.2017 12:32:09         355 Sessions
09.10.2017 12:32:09  09.10.2017 12:33:10         355 Sessions
09.10.2017 12:33:10  09.10.2017 12:34:09         356 Sessions
09.10.2017 12:34:09  09.10.2017 12:35:10         356 Sessions
09.10.2017 12:35:10  09.10.2017 12:36:10         355 Sessions


If you want to query long term historical data, you can to do that by using dba_hist_sysmetric_summary view with a few variations: 


set linesize 190
set pages 200
col METRIC_NAME for a40
col BEGIN_TIME for a20
col END_TIME for a20
COL METRIC_UNIT for a20
select INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT from dba_hist_sysmetric_summary where  METRIC_ID=2143 and INSTANCE_NUMBER=1 order by SNAP_ID;

Due dba_hist_sysmetric_summary contains summary data for intervals of an entire hour, it has an AVERAGE and a MAXVALUE column instead a simply VALUE column...


SQL> set linesize 190
SQL> set pages 200
SQL> col METRIC_NAME for a40
col BEGIN_TIME for a20
SQL> SQL> col END_TIME for a20
SQL> COL METRIC_UNIT for a20
SQL> select INSTANCE_NUMBER, BEGIN_TIME, END_TIME, METRIC_NAME, AVERAGE, MAXVAL, METRIC_UNIT from dba_hist_sysmetric_summary where  METRIC_ID=2143 and INSTANCE_NUMBER=1 order by SNAP_ID;

INSTANCE_NUMBER BEGIN_TIME           END_TIME             METRIC_NAME                                 AVERAGE     MAXVAL METRIC_UNIT
--------------- -------------------- -------------------- ---------------------------------------- ---------- ---------- --------------------
              1 29.08.2017 22:59:53  29.08.2017 23:59:53  Session Count                                324,85        327 Sessions
              1 29.08.2017 23:59:53  30.08.2017 00:59:53  Session Count                                   325        327 Sessions
              1 30.08.2017 00:59:53  30.08.2017 01:59:53  Session Count                            323,833333        327 Sessions
              1 30.08.2017 01:59:53  30.08.2017 02:59:54  Session Count                                 324,3        328 Sessions
              1 30.08.2017 02:59:54  30.08.2017 03:59:53  Session Count                            324,516667        329 Sessions
              1 30.08.2017 03:59:53  30.08.2017 04:59:53  Session Count                            322,533333        327 Sessions
              1 30.08.2017 04:59:53  30.08.2017 05:59:54  Session Count                            322,483333        326 Sessions
              1 30.08.2017 05:59:54  30.08.2017 06:59:54  Session Count                            324,766667        328 Sessions
              1 30.08.2017 06:59:54  30.08.2017 07:59:53  Session Count                            324,983333        327 Sessions
              1 30.08.2017 07:59:53  30.08.2017 08:59:54  Session Count                                 326,1        329 Sessions
              1 30.08.2017 08:59:54  30.08.2017 09:59:54  Session Count                            328,166667        332 Sessions
              1 30.08.2017 09:59:54  30.08.2017 10:59:54  Session Count                                326,85        335 Sessions
              1 30.08.2017 10:59:54  30.08.2017 11:59:54  Session Count                            325,116667        329 Sessions
              1 30.08.2017 11:59:54  30.08.2017 12:59:54  Session Count                            325,966667        330 Sessions
              1 30.08.2017 12:59:54  30.08.2017 13:59:54  Session Count                                 325,8        330 Sessions
....................................................................................................................................................
....................................................................................................................................................
              1 09.10.2017 07:00:09  09.10.2017 08:00:09  Session Count                            351,983333        357 Sessions
              1 09.10.2017 08:00:09  09.10.2017 09:00:09  Session Count                                351,85        358 Sessions
              1 09.10.2017 09:00:09  09.10.2017 10:00:09  Session Count                            352,566667        358 Sessions
              1 09.10.2017 10:00:09  09.10.2017 11:00:10  Session Count                                 356,3        359 Sessions
              1 09.10.2017 11:00:10  09.10.2017 12:00:09  Session Count                                356,55        362 Sessions
dba_hist_sysmetric_summary contains data for all database instances, so if you have a RAC select correct INSTANCE_NUMBER.

Usually, i copy all of this data in a spreadsheet and make a graphic with it. This way you see all data with a glance and maybe send a report to your boss and get a salary increase .. .)


Comments

  1. Anonymous5 October 2021 at 12:29

    wish you have told how to make graph , i could have also got increase :-) , thanks lot

    ReplyDelete

Post a Comment

Popular posts from this blog

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more