When opening the schema browser, on a database which has very many tables and database objects, it takes 3-4 minutes to open.
CAUSE 1:
In the SQL Tracker output ** the following additional script is being run when the Schema Browser is opened:
Select user, object_name, created, last_ddl_time from sys.user_objects
Where object_type = 'TABLE'
CAUSE 2:
SQL Tracker output ** shows the following SQL statement taking a long time:
SELECT u.NAME
FROM sys.USER$ u
WHERE u.TYPE# = 1
AND (EXISTS (SELECT owner#
FROM SYS."_CURRENT_EDITION_OBJ" o
WHERE o.OWNER# = u.USER#
AND o.TYPE# <> 5
AND NOT ((o.TYPE# = 2) AND (BITAND(o.flags, 2) = 2))
AND NOT ((o.TYPE# = 1) AND (BITAND(o.flags, 2) = 2)))
OR
EXISTS (select db_link
from dba_db_links l
where l.owner = u.name))
ORDER BY 1
View | Toad Options | Schema Browser | LHS | "Only show users that own objects excluding Synonyms and Temporary Tables" was checked in a previous version of Toad (10.x and below) and the setting was migrated when Toad was upgraded.
RESOLUTION 1:
The extra query is run to populate the created and last DDL time columns on the SB Right Hand Side.
You can skip that statement by going into the filter icon on the Tables tab, and unchecking the option, "Load Date Created & Altered".
RESOLUTION 2:
Option "Only show users that own objects excluding Synonyms and Temporary Tables" no longer exists in 11.x so it will be necessary to amend the Toad.ini manually to reset this option. i.e.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center