Slow schema browser startup with oracle 12c rel2

Toad for Oracle
1

Hi All,

we upgrade from 11g rel2 db to 12c rel2 db
the schema browser on startup takes more then 3 min to open.
I found that this select in the background is the reason for this:
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
from sys.user_all_tables t
where 1=1
order by table_name;

this select brings the table list in the schema browser

I already run the statistics on sys and system schema and on fix objects - not helping

what is your recommendations to improve the loading time?

thanks,
Eitan

2

The Toad version: 13.2

Eitan

3

First, good job on finding the slow query!

USER_ALL_TABLES are slow sometimes.
USER_TABLES is faster.

If you don't mind not seeing object tables in the schema browser, you can exclude them and Toad will switch to the faster USER_TABLES.

Do that like this

image
image804×596 46.1 KB

4

Thanks for the fast reply.

All the developers that are using toad, using tables in most cases as the main objects in use!! - so to exclude tables from the schema browser is not an option for us

p.s" I already done that and the browser is loaded in 3 seconds - but we need the tables!!

Thanks,
Eitan

5

You could try this query in the editor with various optimizer hints and see any of them makes it faster.

For example:
Select /*+ ALL_ROWS */ 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
from sys.user_all_tables t
where 1=1
order by table_name;

Try it with whatever optimizer hint you want.

If you find one that helps, go to Options -> Oracle -> Transactions and add it, like this:

image
image867×606 38.8 KB

6

Another idea, if your Oracle license includes the SQL Tuning Advisor,

Go to Database -> Optimize -> Oracle Tuning advisor.
Add a tuning task, put that query in and run it.
Maybe Oracle will come up with a SQL Profile for it.

7

Hi John,

the hint was the answer for this problem

another solution that is working is to install ver 14 - it runs fast

Thanks,
Eitan

8

I don't think Toad 14 is running a different query but I'm glad to hear that it's faster.