1 2 Previous Next 17 Replies Latest reply on Nov 3, 2018 9:29 PM by Blue Bird

    How to use/enable Real Time SQL monitor?

    Blue Bird

      I'm exploring SQL Developer 18.3 and I came to the topic "Real Time SQL Monitor". I watched one of the older Jeffs video about that, but I don't see the same when I open this panel. I see only this:

      while in the video open something like this:

      When I selected this command for the first time I get dialog Tuning pack Required, and that this feature has to licensed. I selected Yes. What else I have to do that this will windows will be alive (enabled)?

       

      Thanks,

      BB

        • 1. Re: How to use/enable Real Time SQL monitor?
          thatJeffSmith-Oracle

          you need access to the packages and views required to use real time sql monitoring

           

          you can see the queries we run in the View > Log > Statements panel

           

          or check out the docs for real time sql monitoring

          • 2. Re: How to use/enable Real Time SQL monitor?
            Blue Bird

            I'm afraid I will need more concrete steps, for somebody who never used this before. I already have access to the packages, if we talk about the same things. In Preferences > Database > Licensing I checked all 3 in a row.

            And I can't find anything useful in the SQL Dev help. Only one paragraph about this:

             

            Below sentence also execute successfully:

             

            Select /* +MONITOR */ *
            From Dept;
            

             

            And I see under View > DBA node "Tuning" with only one item "Real Time SQL Monitor". On right click I see only commands: Open, Refresh and link to above Help. Is that Ok?

             

            What else I have to check or set? Do I need to execute something under SYS or SYSTEM account? I have Oracle XE 11g R2, is maybe that a reason?

            • 3. Re: How to use/enable Real Time SQL monitor?
              thatJeffSmith-Oracle

              We run this query to get the list up top - I bolded the lines of interest. You need SELECT privs on those views.

               

              WITH 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,

                       CASE

                         WHEN m.is_full_sqltext = 'N' THEN m.sql_text || ' ...'

                         ELSE m.sql_text

                       END AS 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 l

               

              Then when you open the report:

               

              WITH 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,

                       CASE

                         WHEN m.is_full_sqltext = 'N' THEN m.sql_text || ' ...'

                         ELSE m.sql_text

                       END AS 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,

                     nvl(

                       TO_CHAR(

                         p.first_activity_time,

                         'DD-Mon-YYYY HH24:MI:SS'

                       ),

                       ' '

                     ) AS first_activity_time_form,

                     nvl(

                       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

               

              If you open this panel, you can see for yourself what we're doing to populate these screens:

              • 4. Re: How to use/enable Real Time SQL monitor?
                Blue Bird

                Jeff, I'm not sure If I understood all these. Should I just copy/paste this whole code in SQL Dev Workshet and run it? I just wanted to see what command offer me, while I study SQL Dev generally.

                1. Does any of 5 M+ users who want to use Real TIme SQL Monitor, have to execute this long snake? I hardly believe that will execute correctly out of the box.
                2. Why this code in not implemented inside SQL Dev already? Or do I have to run on XE, because I don't use full version of Oracle? Or this command is not fully implemented yet?
                3. Under which account I have to run this? SYS or HR?
                4. What 4 green plus sign means in your code?
                5. Why you don't run such similar code in your video for older version? What is the point of the video If I follow it, and I don't get same results or at least similar? It's like something is missing here or is too advance for me at the moment.
                • 5. Re: How to use/enable Real Time SQL monitor?
                  thatJeffSmith-Oracle

                  " I bolded the lines of interest. You need SELECT privs on those views."

                   

                  you need a DBA to grant SELECT privs on the views i bolded in the queries. Or just your DBA for help.

                   

                  1. No, the tool does that for you. You see a blank screen though if you lack the proper database privileges.

                  2. It is.

                  3. Any account which has the correct privs. SYS would def work, but you should never user SYS.

                  4. No idea, i think the OTN site added that, not important anyway

                  5. I think you misunderstand how I'm trying to help you.

                  • 6. Re: How to use/enable Real Time SQL monitor?

                    Should I just copy/paste this whole code in SQL Dev Workshet and run it?

                    You said Sql Dev doesn't run or provide results for that query for you.

                     

                    Now you have the query - so execute it manually and see if you get results.

                    • What 4 green plus sign means in your code?

                    You don't know what an OUTER JOIN is?

                     

                    See the Oracle docs

                    https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52352

                    • 7. Re: How to use/enable Real Time SQL monitor?
                      Blue Bird

                      You bolded next objects: gv$sql_monitor, sql_monitor_stats, gv$sql_plan_monitor, gv$active_session_history, active_session_history views to grant SELECT permission but in my Oracle XE 11g R2, the red ones doesn't exists. If I use Find Database Object to look for this two, it's not found if I search for them under SYS account. I don't have around me any DBA or somebody who are willing to help me about such stuff. It would be much easier to have some coach to guide you and answer you some q's like this.

                       

                      Could be this some kind of bug and it doesn't work because of that? Alas I cannot verify this on some other Oracle server right now if this work the same way. I installed Oracle by default and also SQL Dev and all installed Ok, so where could be a reason that only I would have run some extra statements to activate some command? Why other doesn't have to do that or they didn't try to use this command or they use full version of Oracle where this behave differently?

                       

                      A the end of your code you have two substitution variables (:sqlexecid and :sqlexecstart) for which I don't have values. Did you mean this code is executed when user click this command "Real Time SQL Monitor" or do I have to execute it?

                       

                      Under answer on my 3rd Q you wrote "Any account which has the correct privs. SYS would def work, but you should never user SYS.". Does this mean that I would have to create some other account to run such system stuff? What privileges such user would have to have then? Where I can read more about that good practice?

                      • 8. Re: How to use/enable Real Time SQL monitor?
                        Blue Bird

                        You said Sql Dev doesn't run or provide results for that query for you.

                        I didn't say that. I asked if I have to execute this code.

                         

                        You don't know what an OUTER JOIN is?

                        I learnt that already, and thought on that, but this syntax is ( + ) not only , so I was thinking that this is another mark from Jeff, he forgot to mention. I found out now, thanks to you, that if you write/paste in this editor ( + ) together (without space) you get that plus. I still have to so much to learn. I could live another 100 lives and I would still know very litle about life, not to mention Oracle. So it won't be boring at all, because I can learn something new every single day. I used quotes in this reply too and look more clear when somebody read after you. But you have to learn that too. That I had to asked in another thread.

                        • 9. Re: How to use/enable Real Time SQL monitor?
                          Glen Conway

                          You bolded next objects: gv$sql_monitor,sql_monitor_stats, gv$sql_plan_monitor, gv$active_session_history, active_session_history views to grant SELECT permission but in my Oracle XE 11g R2, the red ones doesn't exists

                          Yet another thing to learn:  Oracle subquery factoring (https://oracle-base.com/articles/misc/with-clause)

                          If you look closely at the SQL statement Jeff pasted in, you will note that sql_monitor_stats and active_session_history are defined locally in the statement in WITH-clauses.

                           

                          Cheers

                          1 person found this helpful
                          • 10. Re: How to use/enable Real Time SQL monitor?

                            The 'GV*' views are to support RAC and EX doesn't support RAC.

                            • 11. Re: How to use/enable Real Time SQL monitor?
                              Glen Conway

                              Good point!  Looks like you have solved it.  Although 11g XE does report the existence of said views and synonyms...

                              Capture.JPG

                              ...the SQL statement used when running Real Time SQL Monitor differs from what Jeff posted and I get only an empty display, just like in the image posted by the OP in the original question.  Here is the SQL statement in 11g XE:

                              WITH 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,
                                      CASE
                                          WHEN m.is_full_sqltext = 'N' THEN m.sql_text || ' ...'
                                          ELSE m.sql_text
                                      END AS 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 l
                              
                              
                              • 12. Re: How to use/enable Real Time SQL monitor?
                                thatJeffSmith-Oracle

                                11g XE doesn't support RTSM.

                                 

                                RTSM requires the diagnostics AND tuning packs - neither of which are available in XE. Or SE1/2.

                                 

                                Jeff

                                1 person found this helpful
                                • 13. Re: How to use/enable Real Time SQL monitor?
                                  Glen Conway

                                  If only the screen shot of the SQL Developer help shown in the OP's second post had explicitly excluded 11g XE.

                                   

                                  If the following post is accurate, apparently that restriction is removed in 18c XE:

                                  Re: Oracle XE 12c / 18c 

                                  assuming SQL Developer supports it.

                                  • 14. Re: How to use/enable Real Time SQL monitor?
                                    thatJeffSmith-Oracle

                                    18c XE also lacks the tuning and diagnostic packs

                                    1 2 Previous Next