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

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.

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