Oracle DB: Show long running operations from GV$Session_LongOps including the name of the accessed partitions

Sometimes it is interesting to know which partitions are currently accessed by long running scans.
One possible way to get this info is to use the 
GV$Session.Row_Wait_Obj#
to determine the object that is currently accessed by the DB session.
This SQL will select the currently active long ops along with the partition info of the accessed object.
The column 
Object_Belongs_To_Target_Table
checks if the partition info gotten by 
GV$Session.Row_Wait_Obj#
is really valid. 
SELECT l.*, l.Serial# Serial_No,
       o.Object_Type, o.Owner, o.Object_Name, o.SubObject_Name,
       CASE WHEN o.Owner = l.Target_Owner AND o.Object_Name = l.Target_Table_Name THEN 'YES' /* Table is accessed */
       ELSE CASE WHEN (o.Owner, o.Object_Name) IN (SELECT i.Owner, i.Index_Name
                                                   FROM   DBA_Indexes i
                                                   WHERE  i.Table_Owner = l.Target_Owner AND i.Table_Name = l.Target_Table_Name
                                                  ) THEN 'YES' /* Index of table is accessed */
            ELSE 'NO' END /* Object determined by Row_Wait_Obj# is not valid / does not belong to target table */
       END Object_Belongs_To_Target_Table
FROM   (SELECT l.*,
               CASE WHEN l.OpName = 'Sort Output' THEN NULL
               ELSE l.Last_Update_Time + l.Time_Remaining / 86400 END End_Time,
               CASE WHEN INSTR(l.Target, '.') > 0 THEN SUBSTR(l.Target, 1, INSTR(l.Target, '.')-1) END Target_Owner,
               CASE WHEN INSTR(l.Target, '.') > 0 THEN SUBSTR(l.Target, INSTR(l.Target, '.')+1) END Target_Table_Name
        FROM   gv$Session_Longops l
       ) l
LEFT OUTER JOIN gv$Session s ON s.Inst_ID = l.Inst_ID AND s.SID = l.SID AND s.Serial# = l.Serial# AND s.SQL_ID = l.SQL_ID AND s.SQL_Exec_ID = l.SQL_Exec_ID
                             AND s.Row_Wait_Obj# IS NOT NULL AND s.Row_Wait_Obj# != -1 AND l.Time_Remaining > 0
LEFT OUTER JOIN DBA_Objects o ON o.Object_ID = s.Row_Wait_Obj#
WHERE  l.Time_Remaining > 0
ORDER BY l.Start_Time DESC
;
The same logic is also used in Panorama to view long ops with different filters.
There are two views:
The long ops in total (Menu "SGA/PGA-details / SQL-Area / Long operations")
and per considered session
Thanks to Matthias Rogel for the inspiration to add that logic to Panorama.
Location: Dresden, Deutschland

Comments

Post a Comment

Popular posts from this blog

Panorama: Determining candidates for storage reorganization in Oracle-DB

Image
Knowing that Oracle-DB does not automatically release storage below the high water mark it would be necessary to do it yourself. Usually you may scan your database from time to time to identify tables, indexes or lobs where reorganization of these objects can make significant amount of storage in tablespace available for other objects. The PL/SQL package DBMS_SPACE allows consideration of free space in DB blocks for an object. This information can be used to forecast the effect of object reorganization. Unfortunately DBMS_SPACE.Space_Usage takes longer time for evaluation of larger objects, so it is quite expensive to scan a whole schema or system using DBMS_SPACE. Following I'll show how the performance analysis tool Panorama  handles determining candidates for reorganization. To determine candidates for deeper inspection Panorama uses a simple calculation based on avg. row-length, Pct_Free and Ini_Trans to list suspected objects that are worth to reorganize. Sorting th...
Read more

Oracle-DB: Cleanup Unified Audit Trail with dynamic number of rows and oldest timestamp, but limited storage size

For housekeeping of Unified Audit Trail in a recent project there were some objectives: A hard rule that audit records must be kept in all cases for at least x days A hard rule that the stored audit records should not exceed a limited storage size, except the the above age limit requires this A soft objective thet audit records should be stored as long as possible if the storage size limit is not reached The package DBMS_AUDIT_MGMT only provides the method CLEAN_AUDIT_TRAIL with the ability to cut the records to purge at a timestamp limit. This would require to estimate a maximum age of held audit records that ensures also in worst cases that the storage size limit will not be exceeded. In result, outside the worst case of audit volume you will purge the audit records far too early. You could have stored them much longer because there's enough space below the storage limit. The following SQL script accepts all named objectives by ensuring: Audit records younger ...
Read more

Oracle-DB: Estimate network latency of client connections by evaluation of Active Session History

Image
Sometimes it is useful to know about the network latency of client connections to database. Normally this would be done with the established network tools tnsping, ping, traceroute, tcptraceroute etc. from client or DB server side. But what if there is only SQL access to the DB server and no access to the client? This post shows how the network latency can be estimated using the recordings from ASH if the application uses a certain access pattern. The network latency between client and database server can be estimated by the number of number of SQL executions of a single session between two ASH snapshots. If an application executes the same very short running SQL against the DB over and over again in a loop, then: it is a bad architecture approach for the application because the network latency will cause a significant overhead for application performance however, this behavior gives a possibility for a weak estimation of the network latency between client and database ser...
Read more