Realtime monitoring of sql query using v$sql_plan_monitor

 V$SQL_PLAN_MONITOR displays real time plan level monitoring statistics for the currently running sql queries.. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. 
--- sql_is from v$sql_monitor
 
SELECT sql_id FROM   v$sql_monitor;
 
SQL_ID
-------
00tr6c6tngp4x

SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;
 


 
       SID SQL_ID        STATUS      LINE OPERATION                       ROWS
---------- ------------- ---------- ----- ------------------------- ----------
        82 00tr6c6tngp4x EXECUTING      0 INSERT STATEMENT                   0
                                        1 LOAD TABLE CONVENTIONAL            0
                                        2 HASH UNIQUE                        0
                                        3 HASH JOIN ANTI                     0
                                        4 NESTED LOOPS ANTI                  0
                                        5 STATISTICS COLLECTOR      4293890583
                                        6 HASH JOIN                 4293890583
                                        7 NESTED LOOPS                       1
                                        8 STATISTICS COLLECTOR         1565871
                                        9 MERGE JOIN CARTESIAN         1565871
                                       10 INDEX RANGE SCAN                5381
                                       11 BUFFER SORT                  1565871
                                       12 INDEX RANGE SCAN                 291
                                       13 INDEX RANGE SCAN                   0
                                       14 INDEX FAST FULL SCAN         1860196
                                       15 INDEX RANGE SCAN                  69
                                       16 INDEX RANGE SCAN                   0
 
17 rows selected.

Comments

Post a Comment

Popular posts from this blog

Oracle Architecture SGA

Image
 Architecture :   Basic Memory Structures Oracle Database includes several memory areas, each of which contains multiple subcomponents. The basic memory structures associated with Oracle Database include: ·          System global area (SGA) The SGA is a group of shared memory structures, known as SGA components , that contain data and control information for one Oracle Database instance. All server and background processes share the SGA. Examples of data stored in the SGA include cached data blocks and shared SQL areas. ·          Program global area (PGA) A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. Oracle Database creates the PGA when an Oracle process starts.One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA . Database ...
Read more

FRA Usage and Administration

Image
 In Oracle database, the Flash Recovery Area or FRA is a location on disk where the database can create and manage several kinds of backup and recovery-related files. Main file types are archivelog, flashback log, backups, as well as mirrors for your control files and redo log files. All files in the FRA are Oracle-managed files. Using a Flash Recovery Area simplifies the administration of your database by automatically retaining them for as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed, because the space is needed for another backup and recovery-related purpose. Checking the current usage  You can check the configuration by looking at two parameters.The parameters are db_receovery_file_dest and second one db_recovery_file_dest_size .  db_receovery_file_dest point to location where the files will be located in Server . FOr normal single Databases this will be any directory for RAC this will b...
Read more

Format Date in Oracle Database - NLS_DATE_FORMAT

Image
 When you connect to the Oracle database, anything that has the DATE datatype, is displayed in the default format of DD-MON-YY, and no time component.   There are multiple ways to see the time component of a DATE column, in sqlplus. You can either alter your session to set the date format, or you can use a function in your select statement to alter the format of the column. alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS' ; select sysdate from dual;   SYSDATE ------------------- 12/01/2022 20:19:06   select to_char(sysdate, 'DD-MON-YY HH24:MI:SS' ) from dual;   TO_CHAR(SYSDATE,'D ------------------ 12-JAN-22 20:19:50   How about RMAN? How can you see the date and time a backup completed or started? By default you only see the same format as in sqlplus: DD-MON-YY. This format is not always enough. You try an alter session command in RMAN (same as above), and it doesn’t work. Instead, what is the solution? You must set the NLS_DA...
Read more