Slow start up query on sys.ALL_USERS

when opening a new connection the following qry is executed and very slow. Is there a way on my side or toad side to make this faster?

– Session: FX_MARC_B@DEV11G
– Timestamp: 16:47:13.792
Select username from sys.ALL_USERS a
where exists (select owner
from sys.ALL_OBJECTS
where owner = a.username
and object_type <> ‘SYNONYM’
and not ((object_type = ‘TABLE’) and (temporary= ‘Y’))
and not ((object_type = ‘INDEX’) and (temporary= ‘Y’)))
order by username;


– Session: FX_MARC_B@DEV11G
– Timestamp: 16:47:29.327

I found this on a google search - Have you tried running GATHER_DICTIONARY_STATS()?

From: Marc bounce-marc_109@toadworld.com

Sent: Tuesday, July 17, 2018 3:10 PM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Slow start up query on sys.ALL_USERS

Slow start up query on sys.ALL_USERS

Thread created by Marc

when opening a new connection the following qry is executed and very slow. Is there a way on my side or toad side to make this faster?

– Session: FX_MARC_B@DEV11G

– Timestamp: 16:47:13.792

Select username from sys.ALL_USERS a

where exists (select owner

from sys.ALL_OBJECTS

where owner = a.username

and object_type <> ‘SYNONYM’

and not ((object_type = ‘TABLE’) and (temporary= ‘Y’))

and not ((object_type = ‘INDEX’) and (temporary= ‘Y’)))

order by username;


– Session: FX_MARC_B@DEV11G

– Timestamp: 16:47:29.327

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta Forum
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

I could do this, but i am not the dba(will talk to them). Any other solutions?

found a workaround, I had the last item chosen in the picture above. Changing to All, the query is much faster.