I have a family of databases which is being migrated from 11g to 12c version. For all databases migrated so far we have experienced slowness when using Schema Browser on Toad, particularly the Script tab. Will you please advise how we can at least restore performance level to how it was prior to upgrade? Sometimes it can take as much as 25 seconds to load the DDL for a table. I have enclosed 2 files, each containing the Database Spool for the database on 11g and the other for 12c. My TOAD version is 12.8.0.49.
Did you collect statistics on the SYS schema after the database ugprade? Do so if you did not. It doesn’t hurt to do that again if you aren’t sure. I’ll paste in a script to do that at the end of this reply.
It looks like you are logged as one user (KNL) that does not have the SELECT ANY DICTIONARY or SELECT CATALOG privilege. If you have either of these, Toad will use DBA views instead of ALL views. That should help. If you do have these privileges, go into options and make sure “Check for access to DBA_ views” is checked.
in the schema browser table filter (the funnel icon on the left when looking at tables), if you don’t have any object tables, go into “tables to hide” and check “object tables” . In that case, Toad will skip the query to dba/all/user_object_tables, which tends to be a little bit slow.
on the script tab, click the first toolbar button. Go through each tab under “Script Options” and uncheck whatever you aren’t interested in. This will reduce the number of queries that Toad executes while building the script.
Thank you for the your quick reply. I had already gathered stats but I did it again as well as the additional recommended steps but I still don’t have an acceptable wait when loading the Script tab. For some objects it takes significantly longer to load the data than others. I’ll try to narrow down what is most time consuming and get back to you with more info.
You might want to also update your Toad. We just released version 12.12 a few days ago. I’ve made improvements to some of those queries over the years.
I got exactly the same problem.
I moved to Toad for Oracle 13.1 -> still slow
GRANT SELECT ANY DICTIONARY TO xxx --> still slow
GRANT SELECT_CATALOG_ROLE TO xxx --> still slow
I'm connected to an Oracle 12.2.0.1.0.
I have used Toad for years with Oracle db 12.1 and Toad 12.9 without any problem when opening schema browser.
I can query easily "select count(*) from sys.all_synonyms; " (less than 1 seconds to respnd / 8000 synonyms).
Here is my spool screen output when I tried to launch schema browser ... it tooks around 1 minute the first time, then it takes 10 seconds if I'm reopening it ...
-- Session: PBRABANT@BCPGVAP
-- Timestamp: 15:01:44.775
Select t.table_name, user as owner, t.cluster_name,
t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed, round(t.num_rows) num_rows,
t.initial_extent, t.degree, t.logging, t.cache, t.buffer_pool
, t.monitoring, t.temporary, t.table_type, t.table_type_owner, t.nested
, t.Compression, t.dropped
, t.compress_for, t.flash_cache, t.cell_flash_cache, t.segment_created
, external
, decode(nvl(tablespace_name, 'x') || upper(partitioned) || nvl(iot_type, 'x') || to_char(pct_free), 'xNOx0', 'YES', 'NO') as External
from sys.user_all_tables t
where 1=1
order by table_name;
When you send me a spool SQL, it is better to send the whole thing, so that I can see how long certain queries are taking. For now, I will assume the one you showed me is executing slowly.
Sometimes user_all_tables is slow, but user_tables is fast. You can make Toad query user_tables instead by going into the schema browser filter (the funnel icon on the left), clicking the "Tables to hide" dropdown, then choose "Object Tables".