Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.9K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Real time SQL Monitor not refreshing
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
-
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
-
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).
-
View menu, click on the Log entry, then toggle over to 'Statements'
-
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 "
-
Ok, that helps I'll ask our developer what you could be running into.
-
Thank you!
-
Hi, Any feedback on this?
Thanks
-
Sorry, no. I would open a Service Request with My Oracle Support.
-
Thank you!
