4 Replies Latest reply on Dec 28, 2016 1:47 PM by CiaranOS

    4.2.0.16.356 ORA-12850 from Real Time SQL Monitor

    CiaranOS

      Hi,

       

      I am not ruling out this is a DB config thing, but when I use the Real Time SQL Monitor in EA2 I can consistently generate the following alert log error:

      ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated

       

      Error occurs immedialy when I open a SQL Monitor window. DB is a 2 node RAC cluster 12.1.0.2 with the April PSU.

       

      Any ideas/suggestions?

      I have seen notes: Doc ID 1503358.1 and Doc ID 1945816.1 among others and they do not seem applicable.

       

      Trace File Header:

      *** 2016-12-23 10:50:23.292

      *** SESSION ID:(1526.19319) 2016-12-23 10:50:23.292

      *** CLIENT ID:() 2016-12-23 10:50:23.292

      *** SERVICE NAME:(<service_name>) 2016-12-23 10:50:23.292

      *** MODULE NAME:(SQL Developer) 2016-12-23 10:50:23.292

      *** CLIENT DRIVER:(jdbcthin : 12.2.0.1.0) 2016-12-23 10:50:23.292

      *** ACTION NAME:() 2016-12-23 10:50:23.292

       

      Trace File Entry

      ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated Dump of memory from 0x000000109A1A6E80 to 0x000000109A1A70F7 109A1A6E80 48544957 54434120 5F455649 53534553  [WITH ACTIVE_SESS] 109A1A6E90 5F4E4F49 54534948 2059524F 28205341  [ION_HISTORY AS (] 109A1A6EA0 4C455320 20544345 71732E68 64695F6C  [ SELECT h.sql_id] 109A1A6EB0 2E68202C 5F6C7173 63657865 2C64695F  [, h.sql_exec_id,] 109A1A6EC0 6C766E20 702E6D28 63715F78 2C646973  [ nvl(m.px_qcsid,] 109A1A6ED0 69732E6D 41202964 61622053 735F6573  [m.sid) AS base_s] 109A1A6EE0 202C6469 71732E68 6C705F6C 6C5F6E61  [id, h.sql_plan_l] 109A1A6EF0 5F656E69 202C6469 61732E68 656C706D  [ine_id, h.sample] 109A1A6F00 6D69745F 52462065 67204D4F 51532476  [_time FROM gv$SQ] 109A1A6F10 4F4D5F4C 4F54494E 2C6D2052 24766720  [L_MONITOR m, gv$] 109A1A6F20 49544341 535F4556 49535345 485F4E4F  [ACTIVE_SESSION_H] 109A1A6F30 4F545349 68205952 45485720 6D204552  [ISTORY h WHERE m] 109A1A6F40 6C71732E 2064695F 2E68203D 5F6C7173  [.sql_id = h.sql_] 109A1A6F50 61206469 6D20646E 6C71732E 6578655F  [id and m.sql_exe] 109A1A6F60 64695F63 68203D20 6C71732E 6578655F  [c_id = h.sql_exe] 109A1A6F70 64695F63 202C2920 49544341 485F4556  [c_id ), ACTIVE_H] 109A1A6F80 4F545349 535F5952 53544154 20534120  [ISTORY_STATS AS ] 109A1A6F90 45532028 5443454C 6C717320 2C64695F  [( SELECT sql_id,] 109A1A6FA0 6C717320 6578655F 64695F63 6162202C  [ sql_exec_id, ba] 109A1A6FB0 735F6573 202C6469 5F6C7173 6E616C70  [se_sid, sql_plan] 109A1A6FC0 6E696C5F 64695F65 20534120 6E616C70  [_line_id AS plan] 109A1A6FD0 6E696C5F 64695F65 6F63202C 28746E75  [_line_id, count(] 109A1A6FE0 4120292A 61732053 656C706D 756F635F  [*) AS sample_cou] 109A1A6FF0 202C746E 286E696D 706D6173 745F656C  [nt, min(sample_t] 109A1A7000 29656D69 20534120 5F6E696D 706D6173  [ime) AS min_samp] 109A1A7010 745F656C 2C656D69 78616D20 6D617328  [le_time, max(sam] 109A1A7020 5F656C70 656D6974 53412029 78616D20  [ple_time) AS max] 109A1A7030 6D61735F 5F656C70 656D6974 4F524620  [_sample_time FRO] 109A1A7040 4341204D 45564954 5345535F 4E4F4953  [M ACTIVE_SESSION] 109A1A7050 5349485F 59524F54 4F524720 42205055  [_HISTORY GROUP B] 109A1A7060 71732059 64695F6C 7173202C 78655F6C  [Y sql_id, sql_ex] 109A1A7070 695F6365 62202C64 5F657361 2C646973  [ec_id, base_sid,] 109A1A7080 6C717320 616C705F 696C5F6E 695F656E  [ sql_plan_line_i] 109A1A7090 2C292064 4C515320 4E4F4D5F 524F5449  [d ), SQL_MONITOR] 109A1A70A0 4154535F 41205354 20282053 454C4553  [_STATS AS ( SELE] 109A1A70B0 6D205443 6C71732E 2C64695F 732E6D20  [CT m.sql_id, m.s] 109A1A70C0 655F6C71 5F636578 202C6469 61622E6D  [ql_exec_id, m.ba] 109A1A70D0 735F6573 202C6469 71732E6D 78655F6C  [se_sid, m.sql_ex] 109A1A70E0 735F6365 74726174 2E6D202C 7473616C  [ec_start, m.last] 109A1A70F0 6665725F 00736572                    [_refres.]        
        • 2. Re: 4.2.0.16.356 ORA-12850 from Real Time SQL Monitor
          thatJeffSmith-Oracle

          View - Log - Statements panel

           

          You can see the queries we're running.

           

          The DB wants parallel slaves for the queries but your system is all out. I think.

          • 3. Re: 4.2.0.16.356 ORA-12850 from Real Time SQL Monitor
            BPeaslandDBA

            What is your PARALLEL_MAX_SERVERS parameter set to? When you get this error, query V$PQ_SLAVE to see how many slaves are currently active. Then compare to the max allowed by that parameter. You may be hitting the limit.

             

            Cheers,
            Brian

            • 4. Re: 4.2.0.16.356 ORA-12850 from Real Time SQL Monitor
              CiaranOS

              Thank you gents,

               

              PARALLEL_MAX_SERVERS == 640

               

              When I query V$PQ_SLAVE I get 67 rows each of which has as status 'IDLE'

               

              The Trace file shows that the query that is causing the error begins with: "WITH ACTIVE_SESSION_HISTORY "

              And the two queries SQL Dev is running:

               

               

              1172PROD - - osullivan3627WITH ACTIVE_SESSION_HISTORY AS ( SELECT h.sql_id, h.sql_exec_id, nvl(m.px_qcsid,m.sid) AS base_sid, h.sql_plan_line_id, h.sample_time FROM gv$SQL_MONITOR m, gv$ACTIVE_SESSION_HISTORY h WHERE m.sql_id = h.sql_id and m.sql_exec_id = h.sql_exec_id ), ACTIVE_HISTORY_STATS AS ( SELECT sql_id, sql_exec_id, base_sid, sql_plan_line_id AS plan_line_id, count(*) AS sample_count, min(sample_time) AS min_sample_time, max(sample_time) AS max_sample_time FROM ACTIVE_SESSION_HISTORY GROUP BY sql_id, sql_exec_id, base_sid, sql_plan_line_id ), SQL_MONITOR_STATS AS ( SELECT m.sql_id, m.sql_exec_id, m.base_sid, m.sql_exec_start, m.last_refresh_time, CASE WHEN m.last_refresh_time > m.sql_exec_start THEN round(24*60*60*1000000*(m.last_refresh_time - m.sql_exec_start),0) ELSE m.elapsed_time END AS elapsed_time, m.cpu_time, m.fetches, m.buffer_gets, m.physical_read_requests, m.physical_read_bytes, m.database_time, m.application_wait_time, m.concurrency_wait_time, m.cluster_wait_time, m.user_io_wait_time, m.plsql_exec_time, m.java_exec_time, m.queuing_time FROM (SELECT sql_id, sql_exec_id, nvl(px_qcsid,sid) AS base_sid, min(sql_exec_start) as sql_exec_start, max(last_refresh_time) as last_refresh_time, sum(elapsed_time) AS elapsed_time, sum(cpu_time) AS cpu_time, sum(fetches) AS fetches, sum(buffer_gets) AS buffer_gets, sum(physical_read_requests) AS physical_read_requests, sum(physical_read_bytes) AS physical_read_bytes, sum(cpu_time + application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time + plsql_exec_time + java_exec_time + queuing_time) as database_time, sum(application_wait_time) AS application_wait_time, sum(concurrency_wait_time) AS concurrency_wait_time, sum(cluster_wait_time) AS cluster_wait_time, sum(user_io_wait_time) AS user_io_wait_time, sum(plsql_exec_time) AS plsql_exec_time, sum(java_exec_time) AS java_exec_time, sum(queuing_time) AS queuing_time FROM gv$SQL_MONITOR GROUP BY sql_id, sql_exec_id, nvl(px_qcsid,sid) ) m ), SQL_MONITOR_LIMITS AS ( SELECT max(database_time) as max_database_time, max(elapsed_time) as max_elapsed_time, max(physical_read_requests) AS max_physical_read_requests, max(physical_read_bytes) AS max_physical_read_bytes, max(buffer_gets) AS max_buffer_gets FROM SQL_MONITOR_STATS ), SQL_MONITOR AS ( SELECT m.sql_id, m.sql_exec_id, m.inst_id, m.sid, m.key, m.status, m.user#, m.username, m.session_serial#, m.module, m.action, m.service_name, m.program, m.plsql_object_id, m.first_refresh_time, m.last_refresh_time, m.sql_text, m.sql_exec_start, m.sql_plan_hash_value, m.sql_child_address, m.px_maxdop, s.elapsed_time, s.fetches, s.buffer_gets, s.physical_read_requests, s.physical_read_bytes, s.database_time, s.cpu_time, s.application_wait_time, s.concurrency_wait_time, s.cluster_wait_time, s.user_io_wait_time, s.plsql_exec_time, s.java_exec_time, s.queuing_time FROM gv$SQL_MONITOR m, SQL_MONITOR_STATS s WHERE m.px_qcsid is null and m.sql_id = s.sql_id and m.sql_exec_id = s.sql_exec_id and m.sid = s.base_sid ), SQL_PLAN_MONITOR_STATS AS ( SELECT p.sql_id, p.sql_exec_id, nvl(m.px_qcsid,m.sid) AS base_sid, p.plan_line_id, min(p.first_change_time) AS first_change_time, max(p.last_change_time) AS last_change_time, sum(p.starts) AS starts, sum(p.output_rows) AS output_rows, sum(p.physical_read_requests) AS physical_read_requests, sum(p.physical_read_bytes) AS physical_read_bytes, sum(p.workarea_mem) AS workarea_mem, sum(p.workarea_max_mem) AS workarea_max_mem, sum(p.workarea_tempseg) AS workarea_tempseg, sum(p.workarea_max_tempseg) AS workarea_max_tempseg FROM gv$SQL_MONITOR m, gv$SQL_PLAN_MONITOR p WHERE m.key = p.key GROUP BY p.sql_id, p.sql_exec_id, nvl(m.px_qcsid,m.sid), p.plan_line_id ), SQL_PLAN_MONITOR AS ( SELECT m.sql_id, m.sql_exec_id, m.sid, p.plan_line_id, p.key, p.status, p.first_refresh_time, p.last_refresh_time, p.sql_plan_hash_value, p.sql_child_address, p.plan_parent_id, p.plan_operation, p.plan_options, p.plan_object_name, p.plan_depth, p.plan_cost, p.plan_cardinality, s.first_change_time, s.last_change_time, s.starts, s.output_rows, s.physical_read_requests, s.physical_read_bytes, s.workarea_mem, s.workarea_max_mem, s.workarea_tempseg, s.workarea_max_tempseg, h.sample_count, h.min_sample_time, h.max_sample_time, CASE WHEN h.min_sample_time IS NULL OR h.min_sample_time > s.first_change_time THEN s.first_change_time ELSE CAST(h.min_sample_time AS DATE) END AS first_activity_time, DECODE(s.last_change_time, NULL, p.last_refresh_time, s.last_change_time) AS last_activity_time FROM gv$SQL_MONITOR m, gv$SQL_PLAN_MONITOR p, SQL_PLAN_MONITOR_STATS s, ACTIVE_HISTORY_STATS h WHERE m.px_qcsid is null and m.key = p.key and m.sql_id = s.sql_id and m.sql_exec_id = s.sql_exec_id and p.plan_line_id = s.plan_line_id and m.sid = s.base_sid AND p.plan_line_id = h.plan_line_id(+) AND p.sid = h.base_sid(+) AND p.sql_id = h.sql_id(+) AND p.sql_exec_id = h.sql_exec_id(+) ), SQL_PLAN_MONITOR_LIMITS AS ( SELECT KEY, MIN(FIRST_ACTIVITY_TIME) AS MIN_FIRST_ACTIVITY_TIME, MAX(LAST_ACTIVITY_TIME) AS MAX_LAST_ACTIVITY_TIME, MAX(physical_read_requests) AS max_physical_read_requests, MAX(physical_read_bytes) AS max_physical_read_bytes, SUM(sample_count) as sigma_sample_count FROM SQL_PLAN_MONITOR GROUP BY KEY ) SELECT /*+NO_MONITOR*/ p.SQL_ID, p.PLAN_LINE_ID AS LINE_ID, p.PLAN_PARENT_ID AS PARENT_LINE_ID, CASE WHEN p.status = 'EXECUTING' AND p.max_sample_time IS NOT NULL AND p.max_sample_time >= m.last_refresh_time THEN 'TRUE' WHEN p.status = 'EXECUTING' AND p.max_sample_time IS NULL AND p.LAST_CHANGE_TIME >= m.last_refresh_time THEN 'TRUE' ELSE 'FALSE' END AS EXECUTING , DECODE(p.status, 'QUEUED', 'EXECUTING', 'EXECUTING', 'EXECUTING', 'DONE (ERROR)', 'ERROR', 'DONE') AS STATUS_CODE, p.PLAN_OPERATION || nvl2(p.PLAN_OPTIONS, ' (' || p.PLAN_OPTIONS || ')', '') AS OPERATION, NVL(p.PLAN_OBJECT_NAME, ' ') AS NAME, CASE WHEN p.PLAN_CARDINALITY IS NOT NULL AND p.PLAN_CARDINALITY > 9999 THEN TO_CHAR(ROUND(p.PLAN_CARDINALITY/1000,0)) || 'K' WHEN p.PLAN_CARDINALITY IS NOT NULL THEN TO_CHAR(p.PLAN_CARDINALITY) ELSE ' ' END AS ESTIMATED_ROWS, CASE WHEN p.PLAN_COST IS NOT NULL AND p.PLAN_COST > 9999 THEN TO_CHAR(ROUND(p.PLAN_COST/1000,0)) || 'K' WHEN p.PLAN_COST IS NOT NULL THEN TO_CHAR(p.PLAN_COST) ELSE ' ' END AS COST, 1 AS MAX_LAST_ACTIVITY_TIME_PROP, (l.MAX_LAST_ACTIVITY_TIME - p.LAST_ACTIVITY_TIME) AS END_DURATION, (p.LAST_ACTIVITY_TIME - p.FIRST_ACTIVITY_TIME) AS DURATION, (p.FIRST_ACTIVITY_TIME - l.MIN_FIRST_ACTIVITY_TIME) AS BEGIN_DURATION, (l.MAX_LAST_ACTIVITY_TIME - l.MIN_FIRST_ACTIVITY_TIME) AS TOTAL_DURATION, 0 AS MIN_FIRST_ACTIVITY_TIME_PROP, TO_CHAR(p.FIRST_ACTIVITY_TIME, 'DD-Mon-YYYY HH24:MI:SS') AS FIRST_ACTIVITY_TIME_FORM, TO_CHAR(p.LAST_ACTIVITY_TIME, 'DD-Mon-YYYY HH24:MI:SS') AS LAST_ACTIVITY_TIME_FORM, CASE WHEN p.STARTS IS NOT NULL AND p.STARTS > 9999 THEN TO_CHAR(ROUND(p.STARTS/1000,0)) || 'K' WHEN p.STARTS IS NOT NULL THEN TO_CHAR(p.STARTS) ELSE ' ' END AS EXECUTIONS, CASE WHEN p.OUTPUT_ROWS IS NOT NULL AND p.OUTPUT_ROWS > 9999 THEN TO_CHAR(ROUND(p.OUTPUT_ROWS/1000,0)) || 'K' WHEN p.OUTPUT_ROWS IS NOT NULL THEN TO_CHAR(p.OUTPUT_ROWS) ELSE ' ' END AS ACTUAL_ROWS, CASE WHEN p.WORKAREA_MEM IS NOT NULL AND p.WORKAREA_MEM > 9999 THEN TO_CHAR(ROUND(p.WORKAREA_MEM/1000,0)) || 'KB' WHEN p.WORKAREA_MEM IS NOT NULL THEN TO_CHAR(p.WORKAREA_MEM) || 'B' ELSE ' ' END AS MEMORY, CASE WHEN p.WORKAREA_MAX_MEM IS NOT NULL AND p.WORKAREA_MAX_MEM > 9999 THEN TO_CHAR(ROUND(p.WORKAREA_MAX_MEM/1000,0)) || 'KB' WHEN p.WORKAREA_MAX_MEM IS NOT NULL THEN TO_CHAR(p.WORKAREA_MAX_MEM) || 'B' ELSE ' ' END AS MEMORY_MAX, CASE WHEN p.WORKAREA_TEMPSEG IS NOT NULL AND p.WORKAREA_TEMPSEG > 9999 THEN TO_CHAR(ROUND(p.WORKAREA_TEMPSEG/1000,0)) || 'KB' WHEN p.WORKAREA_TEMPSEG IS NOT NULL THEN TO_CHAR(p.WORKAREA_TEMPSEG) || 'B' ELSE ' ' END AS TEMP, CASE WHEN p.WORKAREA_MAX_TEMPSEG IS NOT NULL AND p.WORKAREA_MAX_TEMPSEG > 9999 THEN TO_CHAR(ROUND(p.WORKAREA_MAX_TEMPSEG/1000,0)) || ' KB' WHEN p.WORKAREA_MAX_TEMPSEG IS NOT NULL THEN TO_CHAR(p.WORKAREA_MAX_TEMPSEG) || 'B' ELSE ' ' END AS TEMP_MAX, p.physical_read_requests AS IO_REQUESTS, CASE WHEN p.physical_read_requests IS NOT NULL AND p.physical_read_requests <> 0 AND p.physical_read_requests > 9999 THEN TO_CHAR(ROUND(p.physical_read_requests/1000,0)) || 'K' WHEN p.physical_read_requests IS NOT NULL AND p.physical_read_requests <> 0 THEN TO_CHAR(p.physical_read_requests) ELSE ' ' END AS IO_REQUESTS_FORM, CASE WHEN p.physical_read_requests IS NOT NULL AND p.physical_read_requests <> 0 AND l.max_physical_read_requests IS NOT NULL AND l.max_physical_read_requests <> 0 THEN p.physical_read_requests/l.max_physical_read_requests ELSE NULL END AS IO_REQUESTS_PROP, p.physical_read_bytes AS IO_BYTES, CASE WHEN p.physical_read_bytes IS NOT NULL AND p.physical_read_bytes <> 0 AND p.physical_read_bytes > 9999 THEN TO_CHAR(ROUND(p.physical_read_bytes/1024,0)) || 'KB' WHEN p.physical_read_bytes IS NOT NULL AND p.physical_read_bytes <> 0 THEN TO_CHAR(p.physical_read_bytes) || 'B' ELSE ' ' END AS IO_BYTES_FORM, CASE WHEN p.physical_read_bytes IS NOT NULL AND p.physical_read_bytes <> 0 AND l.max_physical_read_bytes IS NOT NULL AND l.max_physical_read_bytes <> 0 THEN p.physical_read_bytes/l.max_physical_read_bytes ELSE NULL END AS IO_BYTES_PROP, ROUND(100*p.sample_count/l.sigma_sample_count, 2) AS ACTIVITY_PERCENT, DECODE(ROUND(100*p.sample_count/l.sigma_sample_count, 2), NULL, ' ', TO_CHAR(ROUND(100*p.sample_count/l.sigma_sample_count, 2))) as ACTIVITY_FORM, p.sample_count/l.sigma_sample_count as ACTIVITY_PROP FROM SQL_MONITOR m, SQL_PLAN_MONITOR p, SQL_PLAN_MONITOR_LIMITS l WHERE m.key = p.key AND m.key = l.key AND m.SQL_ID = :sqlId AND m.SQL_EXEC_ID = :sqlExecId AND m.SQL_EXEC_START = :sqlExecStart"sqlId"="", "sqlExecStart"="1970-01-01 00:00:00.0", "sqlExecId"=0
              1171PROD - - osullivan317WITH SQL_MONITOR_STATS AS ( SELECT m.sql_id, m.sql_exec_id, m.base_sid, m.sql_exec_start, m.last_refresh_time, CASE WHEN m.last_refresh_time > m.sql_exec_start THEN round(24*60*60*1000000*(m.last_refresh_time - m.sql_exec_start),0) ELSE m.elapsed_time END AS elapsed_time, m.cpu_time, m.fetches, m.buffer_gets, m.physical_read_requests, m.physical_read_bytes, m.database_time, m.application_wait_time, m.concurrency_wait_time, m.cluster_wait_time, m.user_io_wait_time, m.plsql_exec_time, m.java_exec_time, m.queuing_time FROM (SELECT sql_id, sql_exec_id, nvl(px_qcsid,sid) AS base_sid, min(sql_exec_start) as sql_exec_start, max(last_refresh_time) as last_refresh_time, sum(elapsed_time) AS elapsed_time, sum(cpu_time) AS cpu_time, sum(fetches) AS fetches, sum(buffer_gets) AS buffer_gets, sum(physical_read_requests) AS physical_read_requests, sum(physical_read_bytes) AS physical_read_bytes, sum(cpu_time + application_wait_time + concurrency_wait_time + cluster_wait_time + user_io_wait_time + plsql_exec_time + java_exec_time + queuing_time) as database_time, sum(application_wait_time) AS application_wait_time, sum(concurrency_wait_time) AS concurrency_wait_time, sum(cluster_wait_time) AS cluster_wait_time, sum(user_io_wait_time) AS user_io_wait_time, sum(plsql_exec_time) AS plsql_exec_time, sum(java_exec_time) AS java_exec_time, sum(queuing_time) AS queuing_time FROM gv$SQL_MONITOR GROUP BY sql_id, sql_exec_id, nvl(px_qcsid,sid) ) m ), SQL_MONITOR_LIMITS AS ( SELECT max(database_time) as max_database_time, max(elapsed_time) as max_elapsed_time, max(physical_read_requests) AS max_physical_read_requests, max(physical_read_bytes) AS max_physical_read_bytes, max(buffer_gets) AS max_buffer_gets FROM SQL_MONITOR_STATS ), SQL_MONITOR AS ( SELECT m.sql_id, m.sql_exec_id, m.inst_id, m.sid, m.key, m.status, m.user#, m.username, m.session_serial#, m.module, m.action, m.service_name, m.program, m.plsql_object_id, m.first_refresh_time, m.last_refresh_time, m.sql_text, m.sql_exec_start, m.sql_plan_hash_value, m.sql_child_address, m.px_maxdop, s.elapsed_time, s.fetches, s.buffer_gets, s.physical_read_requests, s.physical_read_bytes, s.database_time, s.cpu_time, s.application_wait_time, s.concurrency_wait_time, s.cluster_wait_time, s.user_io_wait_time, s.plsql_exec_time, s.java_exec_time, s.queuing_time FROM gv$SQL_MONITOR m, SQL_MONITOR_STATS s WHERE m.px_qcsid is null and m.sql_id = s.sql_id and m.sql_exec_id = s.sql_exec_id and m.sid = s.base_sid ) SELECT /*+NO_MONITOR*/ DECODE(m.status, 'QUEUED', 'EXECUTING', 'EXECUTING', 'EXECUTING', 'DONE (ERROR)', 'ERROR', 'DONE') AS STATUS_CODE, m.status AS STATUS, m.elapsed_time/1000 AS ELAPSED_TIME, CASE WHEN m.elapsed_time < 10 THEN '< 0.1 ms' WHEN m.elapsed_time < 1000000 THEN to_char(round(m.elapsed_time/1000,1)) || ' ms' WHEN m.elapsed_time < 60000000 THEN to_char(round(m.elapsed_time/1000000,1)) || ' s' ELSE to_char(round(m.elapsed_time/60000000,1)) || ' m' END AS ELAPSED_TIME_FORM, DECODE(l.max_elapsed_time, 0, 0, m.elapsed_time / l.max_elapsed_time) AS ELAPSED_TIME_PROP, DECODE(m.plsql_object_id, NULL, 'SQL', 'PL/SQL') AS STATEMENT_TYPE, m.sql_id AS SQL_ID, m.sql_plan_hash_value AS SQL_PLAN_HASH_VALUE, RAWTOHEX(m.sql_child_address) AS SQL_CHILD_ADDRESS, NVL(m.username, ' ') AS USERNAME, DECODE(m.px_maxdop, NULL, 'NO', 'YES') AS PARALLEL, DECODE(m.px_maxdop, NULL, ' ', to_char(m.px_maxdop)) AS DOP, m.database_time/1000 AS DATABASE_TIME, DECODE(l.max_database_time, 0, 0, m.database_time / l.max_database_time) AS DATABASE_TIME_PROP, CASE WHEN m.database_time < 1000000 THEN to_char(round(m.database_time/1000,1)) || ' ms' WHEN m.database_time < 60000000 THEN to_char(round(m.database_time/1000000,1)) || ' s' ELSE to_char(round(m.database_time/60000000,1)) || ' m' END AS DATABASE_TIME_FORM, m.cpu_time AS CPU_TIME, CASE WHEN m.cpu_time < 1000000 THEN to_char(round(m.cpu_time/1000,1)) || ' ms' WHEN m.cpu_time < 60000000 THEN to_char(round(m.cpu_time/1000000,1)) || ' s' ELSE to_char(round(m.cpu_time/60000000,1)) || ' m' END AS CPU_TIME_FORM, m.application_wait_time AS APPLICATION_TIME, CASE WHEN m.application_wait_time < 1000000 THEN to_char(round(m.application_wait_time/1000,1)) || ' ms' WHEN m.application_wait_time < 60000000 THEN to_char(round(m.application_wait_time/1000000,1)) || ' s' ELSE to_char(round(m.application_wait_time/60000000,1)) || ' m' END AS APPLICATION_TIME_FORM, m.concurrency_wait_time AS CONCURRENCY_TIME, CASE WHEN m.concurrency_wait_time < 1000000 THEN to_char(round(m.concurrency_wait_time/1000,1)) || ' ms' WHEN m.concurrency_wait_time < 60000000 THEN to_char(round(m.concurrency_wait_time/1000000,1)) || ' s' ELSE to_char(round(m.concurrency_wait_time/60000000,1)) || ' m' END AS CONCURRENCY_TIME_FORM, m.cluster_wait_time AS CLUSTER_TIME, CASE WHEN m.cluster_wait_time < 1000000 THEN to_char(round(m.cluster_wait_time/1000,1)) || ' ms' WHEN m.cluster_wait_time < 60000000 THEN to_char(round(m.cluster_wait_time/1000000,1)) || ' s' ELSE to_char(round(m.cluster_wait_time/60000000,1)) || ' m' END AS CLUSTER_TIME_FORM, m.user_io_wait_time AS USER_IO_TIME, CASE WHEN m.user_io_wait_time < 1000000 THEN to_char(round(m.user_io_wait_time/1000,1)) || ' ms' WHEN m.user_io_wait_time < 60000000 THEN to_char(round(m.user_io_wait_time/1000000,1)) || ' s' ELSE to_char(round(m.user_io_wait_time/60000000,1)) || ' m' END AS USER_IO_TIME_FORM, m.plsql_exec_time AS PLSQL_EXEC_TIME, CASE WHEN m.plsql_exec_time < 1000000 THEN to_char(round(m.plsql_exec_time/1000,1)) || ' ms' WHEN m.plsql_exec_time < 60000000 THEN to_char(round(m.plsql_exec_time/1000000,1)) || ' s' ELSE to_char(round(m.plsql_exec_time/60000000,1)) || ' m' END AS PLSQL_EXEC_TIME_FORM, m.java_exec_time AS JAVA_EXEC_TIME, CASE WHEN m.java_exec_time < 1000000 THEN to_char(round(m.java_exec_time/1000,1)) || ' ms' WHEN m.java_exec_time < 60000000 THEN to_char(round(m.java_exec_time/1000000,1)) || ' s' ELSE to_char(round(m.java_exec_time/60000000,1)) || ' m' END AS JAVA_EXEC_TIME_FORM, m.queuing_time AS QUEUING_TIME, CASE WHEN m.queuing_time < 1000000 THEN to_char(round(m.queuing_time/1000,1)) || ' ms' WHEN m.queuing_time < 60000000 THEN to_char(round(m.queuing_time/1000000,1)) || ' s' ELSE to_char(round(m.queuing_time/60000000,1)) || ' m' END AS QUEUING_TIME_FORM, m.physical_read_requests AS IO_REQUESTS, CASE WHEN m.physical_read_requests < 1000 THEN to_char(m.physical_read_requests) ELSE to_char(round(m.physical_read_requests/1000,1)) || 'K' END AS IO_REQUESTS_FORM, DECODE(l.max_physical_read_requests, 0, 0, m.physical_read_requests / l.max_physical_read_requests) AS IO_REQUESTS_PROP, m.physical_read_bytes AS IO_BYTES, CASE WHEN m.physical_read_bytes < 1000 THEN to_char(m.physical_read_bytes) || 'B' WHEN m.physical_read_bytes < 1000000 THEN to_char(round(m.physical_read_bytes/1000,1)) || 'KB' WHEN m.physical_read_bytes < 1000000000 THEN to_char(round(m.physical_read_bytes/1000000,1)) || 'MB' ELSE to_char(round(m.physical_read_bytes/1000000000,1)) || 'GB' END AS IO_BYTES_FORM, DECODE(l.max_physical_read_bytes, 0, 0, m.physical_read_bytes / l.max_physical_read_bytes) AS IO_BYTES_PROP, m.buffer_gets AS BUFFER_GETS, CASE WHEN (m.buffer_gets) < 1000 THEN to_char(m.buffer_gets) WHEN (m.buffer_gets) < 1000000 THEN to_char(round((m.buffer_gets)/1000,1)) || 'K' WHEN (m.buffer_gets) < 1000000000 THEN to_char(round((m.buffer_gets)/1000000,1)) || 'M' ELSE to_char(round((m.buffer_gets)/1000000000,1)) || 'G' END AS BUFFER_GETS_FORM, DECODE(l.max_buffer_gets, 0, 0, (m.buffer_gets) / l.max_buffer_gets) AS BUFFER_GETS_PROP, m.sql_exec_start AS START_TIME, TO_CHAR(m.sql_exec_start, 'DD-Mon-YYYY HH24:MI:SS') AS START_TIME_FORM, m.last_refresh_time AS END_TIME, TO_CHAR(m.last_refresh_time, 'DD-Mon-YYYY HH24:MI:SS') AS END_TIME_FORM, m.sql_text AS SQL_TEXT, m.sid AS SESSION_ID, m.session_serial# AS SESSION_SERIAL_NO, m.user# AS USER_NO, m.module AS MODULE, m.service_name AS SERVICE_NAME, m.program AS PROGRAM, m.sql_exec_id AS SQL_EXEC_ID, m.sql_exec_start AS SQL_EXEC_START, m.inst_id AS INST_ID FROM SQL_MONITOR m, SQL_MONITOR_LIMITS lnull