When you click on MATERIALIZED VIEWS in SB (Toad 10.1 – Oracle 10g)) and
you have many MViews Toad takes forever to come back.
Before clicking on MViews in SB make sure you go to OPTIONS è HINTS and add a
hint of /*+ RULE */ for DBA_MVIEWS.
DBA_SNAPSHOTS is listed in the list but Toad 10.1 uses DBA_MVIEWS in the SB So
you need to add it. I suggest that For the next go around you include DBA_MVIEWS
in the list with an appropriate hint.
Does anyone else experience this problem? I don’t mind changing
defaults, but I want to confirm that it will benefit the majority. For me,
SB-MViews is loading very quickly against a 10gR2 database.
I’ve had a similar problem lately in SB with the Types tab. It will bring up the
types, but when I click on a type to see its properties, TOAD goes off to la-la
land for about 10 minutes and before returning with what is a relatively small
amount of data from the dictionary. This has forced me to use (gasp!)
SQLDeveloper for my work with my types. A similar operation in SQLDeveloper
returns immediately for the same database and schema leading me to wonder if the
fault is not with the client.
TOAD 9.7.2.5, Oracle 11gR1
John Dorlon said the following on 12/8/2009 10:07 AM:
Does anyone else experience this problem? I don’t mind changing
defaults, but I want to confirm that it will benefit the majority. For me,
SB-MViews is loading very quickly against a 10gR2 database.
It loads quickly when I have a schema with 5 MViews. But when I click on a
schema with 400 Mviews it takes a long time.
Peter, will you enable spool sql to capture the sql statements that toad is
doing, so we can isolate which one is slow?
Hi Erwin,
I’m looking at the query – we’ve got a join in there to
determine if the MV is partitioned or not. I could rewrite it like this:
Select s . mview_name name, decode(t. table_name , null, ‘NO’ , ‘YES’ )
partitioned , s . owner ,
decode( s .updatable, 'N' , 'NO' , 'Y' , 'YES' , null) updatable,
s . last_refresh_date last_refresh , s . refresh_method type, s .
compile_state status
from sys . DBA_MVIEWS s , sys . DBA_PART_TABLES t
where s . owner = T. OWNER (+)
and s . mview_name = t. table_name (+)
and s . owner = :own
will you try that on your schema where it takes a long time and let me know if
it is better? I’d rather change the query than add a hint in 10g or
newer.
Works VERY MUCH better. Even faster than the old with /*+ rule */
This probably won’t help the discussion any, but we have fine performance with
Toad 9.6, Oracle 11, and 150 Mviews that aren’t partitioned.
– jim
Great. I’ll make that change.
User should also read the FAQ on asktoad about slow performance in the schema browser - many times (maybe even the majority) their DBA has not collected statistics properly (or even at all) on the data dictionary under the assumption that Oracle does that for you automatically 
Please do read about data dictionary stats - this can most times eliminate the need for these hints on dictionary queries
Thanks John–GAAA!! Of course it’s working fine this morning when I try it. This
is why I’ve not raised the issue to this point. It used to work fine, then
started this behavior intermittently. Anyway, here’s the SQL from the debug.sql
file:
Does it behave worse when you browse in schemas other than the one you logged in
as?
You mean if I switch the SB window to another schema other than the one logging
in originally? If so, nope.
John Dorlon said the following on 12/8/2009 11:53 AM:
Does it behave worse when you browse in schemas other than the one you
logged in as?
Yes, that’s what I meant. Well, if it acts up again, try the spool sql
thing again and send me those results. At this point I’m not sure where
the problem is.
I wish they would simply try to two commands we suggest on asktoad to see if that fixes it - because many times slow oracle in toad in schema browser and some other screens is DIRECTLY attributed to this - but no reply so ever on my numerous posts - just yours John …