Топ-100
 
CerebroSQL

Active Session History (ASH)

 

Oracle session longops


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

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

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

  • there are statistics on tables

CerebroSQL - oracle session longops.jpg

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

-> Request to retrieve data <-

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 "SQL text" field, the text of the query being executed is displayed, as well as the name of the table whose blocks are being scanned at a given time and the progress of execution in absolute values.

When switching to the "SQL plan" tab, the query execution plan is displayed

-> Request to retrieve data <-

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

-----

Control buttons

  • Refresh data in the "Session list"

  • Resize the "Session list" grid to fit the data  in it

 

Oracle ASH viewer

The history of active sessions in the context of expectation classes speaks about 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 operating mode, supports Standard edition

  • Data from views  diagnostics pack

 

Oracle SQL monitor

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

The request is considered long 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 monitored requests

-> Request to retrieve data <-

select * from v$sql_monitor order by sql_exec_start desc 

-----

Report generation and application of recommendations

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