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,
round((sl.SOFAR/sl.TOTALWORK)*100,2) as "%",
from V$SESSION SE, V$SESSION_LONGOPS SL, V$SQL S
AND SE.USERNAME IS NOT NULL
group by sl.serial#,SL.TIME_REMAINING,
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>'))
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