Hi,
I’m using TOAD Version 12.10.0.30 and all of a sudden since yesterday I’m unable to use the database monitor. After selecting the menu entry it takes several minutes (while the application is completely unresponsive) for the monitor to load.
And both an automatic and a manual refresh takes just as long.
I’ve no clue what has caused this. I was able to use the database monitor without any issues till then.
Is there any old data I can clear?
Dirk
Hi Dirk,
I assume that Toad is not a guilty part. Somehow you database stat is now obsolete and do not function properly as it was.
Try to trace session (outside of toad) or spool SQLs and see which SQL it takes most of the time.
Then try to see plan or start that SQL manually outside of Toad and see if it is faster.
Brg
Damir
Sorry for my late reply - Are your database stats up to date?
Try running the following in in Editor or SQL*Plus (they might take a few minutes to complete), then try Database Monitor again.
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
Sometimes it is not so easy to renew db stat…sometimes not at all.
Here is the list of all calls, so go one by one and see where you have the biggest delay.
– Session: xxxx@yyyyy
– Timestamp: 14:31:41.420
BEGIN
BEGIN
SELECT SYSDATE INTO :CurrentTime FROM DUAL;
END;
BEGIN
SELECT SUM(Decode(Type, ‘BACKGROUND’, 1, 0)) system_sessions,
SUM(Decode(Type, ‘BACKGROUND’, 0, Decode(Status, ‘ACTIVE’, 1, 0))) active_users,
SUM(Decode(Type, ‘BACKGROUND’, 0, Decode(Status, ‘ACTIVE’, 0, 1))) inactive_users
INTO :SystemSessions, :ActiveUsers, :InactiveUsers
FROM V$SESSION;
END;
BEGIN
SELECT SUM(gets), SUM(gethits) INTO :Gets, :GetHits
FROM V$LIBRARYCACHE WHERE 1=1
AND namespace = ‘SQL AREA’;
END;
BEGIN
SELECT SUM(DECODE(name, ‘table scans (long tables)’, value, 0))/
(SUM(DECODE(name, ‘table scans (long tables)’, value, 0))+SUM(DECODE(name, ‘table scans (short tables)’, value, 0)))*100 non_indexed_sql,
100-SUM(DECODE(name,‘table scans (long tables)’,value,0))/
(SUM(DECODE(name,‘table scans (long tables)’,value,0))+SUM(DECODE(name,‘table scans (short tables)’,value,0)))*100 indexed_sql
INTO :non_indexed_sql, :indexed_sql
FROM V$SYSSTAT WHERE 1=1
AND ( NAME IN (‘table scans (long tables)’,‘table scans (short tables)’) );
END;
declare
TYPE wait_rec_typ IS RECORD
(wait_type varchar2(100),
time_waited_ms number);
waits_rec wait_rec_typ;
cursor waits is
with wait_categories as (
SELECT CASE
WHEN name LIKE ‘%latch%’ OR name LIKE ‘%mutex%’
OR name LIKE ‘cursor:%’ OR name IN (‘library cache pin’) THEN
‘Latch/mutex’
WHEN name LIKE ‘direct path%temp’ THEN
‘Temp Segment I/O’
WHEN name IN (‘db file sequential read’,
‘log file sync’,
‘buffer busy waits’) THEN name
WHEN name IN (‘free buffer space’, ‘write complete waits’) THEN
‘DBWR waits’
WHEN name LIKE ‘%flashback%’ THEN
‘Flashback log wait’
WHEN name IN (‘gc cr block 2-way’, ‘gc cr block 3-way’) THEN
‘RAC interconnect’
WHEN name LIKE ‘enq:%’ OR name IN (‘row cache lock’) THEN
‘Lock’
WHEN name LIKE ‘log file switch%’ THEN
‘log file switch’
WHEN wait_class LIKE ‘User I/O’ THEN
‘User I/O’
ELSE
wait_class
END AS wait_type, name, wait_class
FROM v$event_name e
WHERE wait_class <> ‘Idle’
)
SELECT wait_type, Round(SUM(time_waited_micro) / 1000) time_waited_ms
FROM v$system_event e, wait_categories c
WHERE 1=1
AND c.name = e.event
GROUP BY wait_type
UNION all
SELECT ‘CPU’, Round(SUM(VALUE) / 1000)
FROM v$sys_time_model
WHERE stat_name IN (‘background cpu time’, ‘DB CPU’);
begin
open waits;
loop
fetch waits into waits_rec;
exit when waits%notfound;
if waits_rec.wait_type = ‘Administrative’ then
:AdminMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Application’ then
:AppMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘CPU’ then
:CPUMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Concurrency’ then
:ConcMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Configuration’ then
:ConfigMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Latch/mutex’ then
:LatchMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Lock’ then
:LockMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Network’ then
:NetworkMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Other’ then
:OtherMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘User I/O’ then
:UserIOMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Scheduler’ then
:SchedulerMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘System I/O’ then
:SystemIOMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘Temp Segment I/O’ then
:TempMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘buffer busy waits’ then
:BBMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘db file sequential read’ then
:DBFSRMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘log file switch’ then
:LFSwitchMS := waits_rec.time_waited_ms;
elsif waits_rec.wait_type = ‘log file sync’ then
:LFSyncMS := waits_rec.time_waited_ms;
end if;
end loop;
close waits;
end;
BEGIN
select sum(decode(name,‘db block changes’,value,0)) block_changes,
sum(decode(name,‘db block gets’,value,0)) current_reads,
sum(decode(name,‘consistent gets’,value,0)) consistent_reads,
sum(decode(name,‘physical reads’,value,0)) datafile_reads,
sum(decode(name,‘physical writes’,value,0)) datafile_writes,
sum(decode(name,‘redo writes’,value,0)) redo_writes,
sum(decode(name,‘parse count (total)’,value,0)) parse_count,
sum(decode(name,‘execute count’,value,0)) execute_count,
sum(decode(name,‘user commits’,value,0)) u_commit,
sum(decode(name,‘user rollbacks’,value,0)) u_rollback
into :block_changes, :current_reads, :consistent_reads, :datafile_reads,
:datafile_writes, :redo_writes, :parse_count, :execute_count, :u_commit, :u_rollback
from v$sysstat
where 1=1
and name in (‘db block changes’,‘db block gets’,‘consistent gets’,
‘physical reads’,‘physical writes’,‘redo writes’,
‘redo writes’,‘parse count (total)’,‘execute count’,
‘user commits’,‘user rollbacks’);
END;
BEGIN
SELECT SUM(gets), SUM(misses)
INTO :LatchGets, :LatchMisses
FROM V$LATCH;
END;
BEGIN
SELECT ROUND(SUM(decode(pool,‘large pool’,bytes/1048576,0)),2) sga_lpool,
ROUND(SUM(decode(pool,NULL,decode(name,‘db_block_buffers’,bytes/1048576,‘buffer_cache’,bytes/1048576,0),0)),2) sga_bufcache,
ROUND(SUM(decode(pool,NULL,decode(name,‘log_buffer’,bytes/1048576,0),0)),2) sga_lbuffer,
ROUND(SUM(decode(pool,NULL,decode(name,‘fixed_sga’,bytes/1048576,0),0)),2) sga_fixed,
ROUND(SUM(decode(pool,‘java pool’,bytes/1048576,0)),2) sga_jpool,
ROUND(SUM(decode(pool,‘shared pool’,decode(name,‘SQLA’,bytes/1048576,0),0)),2) pool_sql_area,
ROUND(SUM(decode(pool,‘shared pool’,decode(name,‘free memory’,bytes/1048576,0),0)),2) pool_free_mem,
ROUND(SUM(decode(pool,‘shared pool’,decode(name,‘library cache’,bytes/1048576,0),0)),2) pool_lib_cache,
ROUND(SUM(decode(pool,‘shared pool’,decode(name,‘row cache’,bytes/1048576,0),0)),2) pool_dict_cache,
ROUND(SUM(decode(pool,‘shared pool’,decode(name,‘library cache’,0,‘row cache’,0,‘free memory’,0,‘SQLA’,0,bytes/1048576),0)),2) pool_misc
INTO :sga_lpool, :sga_bufcache, :sga_lbuffer, :sga_fixed, :sga_jpool, :pool_sql_area,
:pool_free_mem, :pool_lib_cache, :pool_dict_cache, :pool_misc
FROM V$SGASTAT;
END;
BEGIN
SELECT ROUND (SUM (bytes) / (1024 * 1024), 2)
INTO :sga_pool
FROM V$SGASTAT WHERE 1=1
AND (POOL= ‘shared pool’);
END;
END;
:CurrentTime(DATE,IN/OUT)=
:SystemSessions(FLOAT,IN/OUT)=
:ActiveUsers(FLOAT,IN/OUT)=
:InactiveUsers(FLOAT,IN/OUT)=
:Gets(FLOAT,IN/OUT)=
:GetHits(FLOAT,IN/OUT)=
:non_indexed_sql(FLOAT,IN/OUT)=
:indexed_sql(FLOAT,IN/OUT)=
:AdminMS(FLOAT,IN/OUT)=
:AppMS(FLOAT,IN/OUT)=
:CPUMS(FLOAT,IN/OUT)=
:ConcMS(FLOAT,IN/OUT)=
:ConfigMS(FLOAT,IN/OUT)=
:LatchMS(FLOAT,IN/OUT)=
:LockMS(FLOAT,IN/OUT)=
:NetworkMS(FLOAT,IN/OUT)=
:OtherMS(FLOAT,IN/OUT)=
:UserIOMS(FLOAT,IN/OUT)=
:SchedulerMS(FLOAT,IN/OUT)=
:SystemIOMS(FLOAT,IN/OUT)=
:TempMS(FLOAT,IN/OUT)=
:BBMS(FLOAT,IN/OUT)=
:DBFSRMS(FLOAT,IN/OUT)=
:LFSwitchMS(FLOAT,IN/OUT)=
:LFSyncMS(FLOAT,IN/OUT)=
:block_changes(FLOAT,IN/OUT)=
:current_reads(FLOAT,IN/OUT)=
:consistent_reads(FLOAT,IN/OUT)=
:datafile_reads(FLOAT,IN/OUT)=
:datafile_writes(FLOAT,IN/OUT)=
:redo_writes(FLOAT,IN/OUT)=
:parse_count(FLOAT,IN/OUT)=
:execute_count(FLOAT,IN/OUT)=
:u_commit(FLOAT,IN/OUT)=
:u_rollback(FLOAT,IN/OUT)=
:LatchGets(FLOAT,IN/OUT)=
:LatchMisses(FLOAT,IN/OUT)=
:sga_lpool(FLOAT,IN/OUT)=
:sga_bufcache(FLOAT,IN/OUT)=
:sga_lbuffer(FLOAT,IN/OUT)=
:sga_fixed(FLOAT,IN/OUT)=
:sga_jpool(FLOAT,IN/OUT)=
:pool_sql_area(FLOAT,IN/OUT)=
:pool_free_mem(FLOAT,IN/OUT)=
:pool_lib_cache(FLOAT,IN/OUT)=
:pool_dict_cache(FLOAT,IN/OUT)=
:pool_misc(FLOAT,IN/OUT)=
:sga_pool(FLOAT,IN/OUT)=
– Session: xx@yy
– Timestamp: 14:31:50.230
BEGIN
BEGIN
SELECT SYSDATE INTO :CurrentTime FROM DUAL;
END;
BEGIN
SELECT SUM(Decode(Type, ‘BACKGROUND’, 1, 0)) system_sessions,
SUM(Decode(Type, ‘BACKGROUND’, 0, Decode(Status, ‘ACTIVE’, 1, 0))) active_users,
SUM(Decode(Type, ‘BACKGROUND’, 0, Decode(Status, ‘ACTIVE’, 0, 1))) inactive_users
INTO :SystemSessions, :ActiveUsers, :InactiveUsers
FROM V$SESSION;
END;
BEGIN
SELECT SUM(gets), SUM(gethits) INTO :Gets, :GetHits
FROM V$LIBRARYCACHE WHERE 1=1
AND namespace = ‘SQL AREA’;
END;
BEGIN
SELECT SUM(DECODE(name, ‘table scans (long tables)’, value, 0))/
(SUM(DECODE(name, ‘table scans (long tables)’, value, 0))+SUM(DECODE(name, ‘table scans (short tables)’, value, 0)))*100 non_indexed_sql,
100-SUM(DECODE(name,‘table scans (long tables)’,value,0))/
(SUM(DECODE(name,‘table scans (long tables)’,value,0))+SUM(DECODE(name,‘table scans (short tables)’,value,0)))*100 indexed_sql
INTO :non_indexed_sql, :indexed_sql
FROM V$SYSSTAT WHERE 1=1
AND ( NAME IN (‘table scans (long tables)’,‘table scans (short tables)’) );
END;
declare
TYPE wait_rec_typ IS RECORD
(wait_type varchar2(100),
time_waited_ms number);
waits_rec wait_rec_typ;
cursor waits is
with wait_categories as (
SELECT CASE
WHEN name LIKE ‘%latch%’ OR name LIKE ‘%mutex%’
OR name LIKE ‘cursor:%’ OR name IN (‘library cache pin’) THEN
‘Latch/mutex’
WHEN name LIKE ‘direct path%temp’ THEN
‘Temp Segment I/O’
&nbs
Hi John,
thanks for your reply. Unfortunately outdated stats are not the cause. I did check that already.
I’m quite sure it’s not database related in general because it doesn’t matter which database I want to monitor. And my college is able to monitor the very same instance on his machine without any problems. His TOAD is slightly older however.
Getting back to your original question - depending on settings, the Database Monitor might store information as long as Toad is running (you can see for how long in the top-right toolbar button). To clear it, all you need to do is restart Toad.
If that doesn’t do anything, you could resetting Toad to its original settings. To do that, go to Utilities -> Copy User Settings. Check “reset to a clean set to user files from the base installation” and click OK. The old settings folder will be renamed with _bak at the end. For me, this folder is C:\Users\Admin\AppData\Roaming\Dell\Toad for Oracle\12.10 You can always rename it back later if you want (with Toad not running, of course). I don’t think that will solve your problem but maybe it will.
Is everything suddenly slower in Toad or just this window? If everything, maybe something has happened to Oracle client or network settings…
Hi,
a simple restart didn’t help.
I’ll give resetting all settings a try although I didn’t find anything “abnormal” in that directory. I already went through to see if there is anything large which may take time to load.
And no, anything else in Toad works as it did before, nothing to complain about. It’s just the Database Monitor.
Dirk
Hi,
although not expected, starting toad with clean settings did it. And the problem is reproducible. As soon as I use the old settings (by renaming the folder) the problem is back.
But that’s a solution I can live with. I just need to restore the settings by hand. But first I compare both folders with winmerge. Maybe I’m able to find the root cause. Thank’s to all.
Dirk
Hi,
Ok, here’s what I’ve found: It seems to be an issue with my oracle 12.1.0.2 client TNSPING. It just doesn’t finish in time. It reports a result like “OK (20 ms)” and then hangs for the very same amount of time the database monitor did hang.
As soon as I disable TNSPING in the database monitor’s options, it works quick and nicely without any problem.
Dirk
Hi Dirk,
I’m glad you got to the bottom of it (and that it wasn’t a Toad problem!)
-John