# Database Monitor takes ages to load and to capture new values

**URL:** <https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [January 19, 2017, 12:28am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956 "2017-01-19T00:28:52Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![dirk.mika](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@dirk.mika](https://forums.toadworld.com/u/dirk.mika)\
**Post date:** [January 19, 2017, 12:28am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/1 "2017-01-19T00:28:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [January 24, 2017, 3:10am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/2 "2017-01-24T03:10:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [January 24, 2017, 6:39am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/3 "2017-01-24T06:39:14Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [January 25, 2017, 5:35am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/4 "2017-01-25T05:35:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dirk.mika](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@dirk.mika](https://forums.toadworld.com/u/dirk.mika)\
**Post date:** [January 25, 2017, 6:05am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/5 "2017-01-25T06:05:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [January 25, 2017, 6:22am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/6 "2017-01-25T06:22:29Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![dirk.mika](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@dirk.mika](https://forums.toadworld.com/u/dirk.mika)\
**Post date:** [January 25, 2017, 10:38pm UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/7 "2017-01-25T22:38:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dirk.mika](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@dirk.mika](https://forums.toadworld.com/u/dirk.mika)\
**Post date:** [January 25, 2017, 10:57pm UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/8 "2017-01-25T22:57:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dirk.mika](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@dirk.mika](https://forums.toadworld.com/u/dirk.mika)\
**Post date:** [January 26, 2017, 12:17am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/9 "2017-01-26T00:17:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [January 26, 2017, 5:45am UTC](https://forums.toadworld.com/t/database-monitor-takes-ages-to-load-and-to-capture-new-values/38956/10 "2017-01-26T05:45:17Z")

</div>

Hi Dirk,

I’m glad you got to the bottom of it (and that it wasn’t a Toad problem!)

-John