Forum Stats

  • 3,840,393 Users
  • 2,262,599 Discussions
  • 7,901,262 Comments

Discussions

Real time SQL Monitor not refreshing

user626688
user626688 Member Posts: 416 Bronze Badge
edited Feb 15, 2022 3:05PM in SQL Developer

Hi, I am using SQL Developer version 21.4.2.018. The Real time SQL Monitor available under Tools Menu does not refresh the running queries even when I have selected "Auto Refresh" at 5.0 s. In older versions of SQL Developer i never faced this issue. However, i am seeing this issue since i started using version 17 onwards. Is there any setting that i need to change so that the Auto Refresh of the running queries will work?

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,708 Employee

    As soon as I enable the refresh period, I see this query running on a regular basis (see the Log panel) - do you?


    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 < 10240        THEN to_char(

      m.physical_read_bytes

      )

                          || 'B'

     WHEN m.physical_read_bytes < 10240 * 1024    THEN to_char(

      round(

      m.physical_read_bytes / 1024

      , 0

      )

      )

                              || 'KB'

     WHEN m.physical_read_bytes < 10240 * 1024 * 1024 THEN to_char(

      round(

      m.physical_read_bytes /(1024 * 1024)

      , 0

      )

      )

                                 || 'MB'

     ELSE to_char(

      round(

      m.physical_read_bytes /(1024 * 1024 * 1024)

      , 0

      )

      )

         || '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 ) < 10000    THEN to_char(

      m.buffer_gets

      )

     WHEN ( m.buffer_gets ) < 10000000  THEN to_char(

      round(

      (m.buffer_gets) / 1000

      , 0

      )

      )

                          || 'K'

     WHEN ( m.buffer_gets ) < 10000000000 THEN to_char(

      round(

      (m.buffer_gets) / 1000000

      , 0

      )

      )

                           || 'M'

     ELSE to_char(

      round(

      (m.buffer_gets) / 1000000000

      , 0

      )

      )

         || '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

  • user626688
    user626688 Member Posts: 416 Bronze Badge

    As soon as i start the SQL Monitor, i can see all the queries running at the SQL Text tab. By the way where do i look for this query? (Log Panel).

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,708 Employee

    View menu, click on the Log entry, then toggle over to 'Statements'

  • user626688
    user626688 Member Posts: 416 Bronze Badge

    Thank you for your help and pointing me to the logging Pane. I see the following recurring error message.

    " SEVERE 731 5043 oracle.dbtools.raptor.metrics.observable.ItemChangeAdapter error thrown by metrics event handler "

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,708 Employee

    Ok, that helps I'll ask our developer what you could be running into.

  • user626688
    user626688 Member Posts: 416 Bronze Badge
  • user626688
    user626688 Member Posts: 416 Bronze Badge

    Hi, Any feedback on this?


    Thanks

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,708 Employee

    Sorry, no. I would open a Service Request with My Oracle Support.

  • user626688
    user626688 Member Posts: 416 Bronze Badge