View Oracle Sessions Information

View Oracle Sessions Information

=========================
Check Current Situation
=========================

SELECT name, value 
  FROM V$PARAMETER 
WHERE name like '%session%' OR name like '%process%'

NAME                           VALUE
------------------------------ ---------------------
processes                      1000
sessions                       1528
session_cached_cursors         100
job_queue_processes            1000
session_max_open_files         10

=========================
See Resource Limit
=========================
SELECT current_utilization, limit_value 
  FROM V$RESOURCE_LIMIT 
 WHERE resource_name='sessions';

CURRENT_UTILIZATION LIMIT_VALUE
------------------- -----------
                 61        1528

=========================
See History Utilization
=========================
 
SELECT resource_name, 
       current_utilization, 
       max_utilization,
       limit_value
  FROM V$RESOURCE_LIMIT
 WHERE resource_name in ('sessions', 'processes');

RESOURCE_NAME       CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------------- ------------------- --------------- -----------
processes                            54              98        1000
sessions                             62             123        1528  

=========================
See Long Running Sessions
=========================
SELECT * 
  FROM V$SESSION_LONGOPS


=========================
See Sessions Historical Data
=========================

To See historical data on sessions:
Query DBA_HIST_ACTIVE_SESS_HISTORY or V$ACTIVE_SESSION_HISTORY

Get list of all sessions 
SELECT *
 FROM V$ACTIVE_SESSION_HISTORY
 WHERE sample_time BETWEEN TO_DATE('20151119 09:00:00','YYYYMMDD HH24:MI:SS') 
   AND TO_DATE('20151119 10:00:00','YYYYMMDD HH24:MI:SS')
   AND session_type <> 'BACKGROUND' 



Get sql text of the sessions

SELECT * FROM DBA_HIST_SQLTEXT HIST_SQLTEXT 

 WHERE sql_id IN 

(

SELECT sql_id

  FROM V$ACTIVE_SESSION_HISTORY

 WHERE sample_time BETWEEN TO_DATE('20151119 09:00:00','YYYYMMDD HH24:MI:SS') 

   AND TO_DATE('20151119 10:00:00','YYYYMMDD HH24:MI:SS')

   AND session_type <> 'BACKGROUND'   

   AND sql_id is not NULL

)

Find top SQL statements from last hour
SET LONG 2000;
SELECT ALL_USERS.username AS user_name,
       V$SQL.module,
       V$SQL.sql_text,
       V$SQL.sql_fulltext,
       NVL(TOP_SQLS.sql_id,'NULL') AS sql_id,
       ROUND(pct_load,1) AS pct_load
FROM(
    SELECT user_id,
           sql_id, 
           count(*),
           count(*)*100/SUM(count(*)) OVER() AS PCT_LOAD
     FROM V$ACTIVE_SESSION_HISTORY     
    WHERE sample_time > sysdate - 1/24
    GROUP BY user_id, sql_id
    )TOP_SQLS,
     V$SQL,
     ALL_USERS
WHERE V$SQL.sql_id(+) = TOP_SQLS.sql_id
  AND ALL_USERS.user_id(+) = TOP_SQLS.user_id
  AND pct_load > 1
  AND username <> 'SYS'
ORDER BY pct_load DESC;

DONT FORGET TO COMMENT !!