Топ-100
 
CerebroSQL

Active Session History (ASH)

 

Oracle session longops


The window displays information about a session in the Oracle DBMS running for more than 6 seconds.

To collect information about the execution time of requests, 2 conditions must be met:

  • the DBMS TIMED_STATISTICS parameters are enabled, or  SQL_TRACE ( TIMED_STATISTICS=TRUE or  SQL_TRACE=TRUE )

  • there are statistics on the tables

CerebroSQL - oracle session longops.jpg

Session list - a list of sessions running for a long time

select distinct( sl.serial#),       

       'EXECUTE' AS STATUS,       

       SL.TIME_REMAINING||' c.' as REMAIN,       

       SL.ELAPSED_SECONDS||' c.' AS ELAPSED,       

       SL.START_TIME "TIME",       

       SL.SQL_ID,       

       S.SQL_TEXT,       

       se.sid,       

       sl.opname,       

       sl.SOFAR,       

       sl.TOTALWORK,       

       round((sl.SOFAR/sl.TOTALWORK)*100,2) as "%",       

       sl.MESSAGE,       

       se.USERNAME,       

       se.OSUSER,       

       se.MACHINE,       

       se.PROGRAM,       

       SE.EVENT,       

       SE.SECONDS_IN_WAIT 

  from V$SESSION SE, V$SESSION_LONGOPS SL, V$SQL S

 where SE.SID=SL.SID   

   AND SE.SERIAL#=SL.SERIAL#   

   AND SE.SQL_ID=S.SQL_ID   

   AND SE.USERNAME IS NOT NULL   

   AND SL.TIME_REMAINING<>'0' 

group by  sl.serial#,SL.TIME_REMAINING,

    SL.ELAPSED_SECONDS,SL.START_TIME,SL.SQL_ID,

    S.SQL_TEXT, se.sid,  sl.opname, sl.SOFAR,

    sl.TOTALWORK, sl.MESSAGE, se.USERNAME,

    se.OSUSER, se.MACHINE, se.PROGRAM,SE.EVENT, SE.SECONDS_IN_WAIT  order by se.sid,sl.serial# 

When a row is selected, detailed information is displayed in the Details block. In the field "SQL text" - the text of the executed query is displayed, as well as the name of the table whose blocks are being scanned at the current time and the progress in absolute terms.

Switching to the SQL plan tab displays the query execution plan

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>'))

Control buttons

  • Update data in "Session list"

  • Fit "Session list" grid size to data  in it

 

Oracle ASH viewer

The history of active sessions in the context of the classes of expectations indicates the current load in the Oracle DBMS , and also helps to find bottlenecks in the code by evaluating the work of sql queries in the context of expectations

The program implements 2 mechanisms for obtaining data on the history of sessions:

  • Real-time mode - the program collects information about the work of sessions on its own, without referring to the views of the diagnostics pack . Recommended mode of operation, supports Standard edition

  • Data from views  diagnostic pack

 

Oracle SQL monitor

The SQL Monitoring window allows you to monitor the operation of long-running SQL queries, generate recommendations from the built-in query optimizer, and apply recommendations in real time.  

A request is considered long-running and is subject to automatic tracking if it consumes more than 5 seconds of CPU or I/O time.  

Oracle SQL Monitor.jpg

List of requests included in the monitoring

select * from v$sql_monitor order by sql_exec_start desc 

Formation of a report and application of recommendations

In the Query list, select the SQL query to generate the report or insert the SQL_ID of the query into the "Analysis of the query by SQL_ID" field