RE: F4 on object type is slow

Thanks for the follow-thru - glad it worked on one of the two :)

From: [email protected] [mailto:[email protected]] On Behalf Of Böschenstein Patrik
Sent: Wednesday, May 16, 2012 4:09 AM
To: [email protected]
Subject: AW: [toad] F4 on object type is slow


Hi Bert

Our DBA's "recalculated the statics of the fixed objects of SYS".
This helped and fixed the problem - but only on Oracle 11.2.

They did it on Oracle 11.1.0.7.0 too, but it did not help on this version.

Patrik

Von: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> Im Auftrag von Bert Scalzo
Gesendet: Dienstag, 8. Mai 2012 15:49
An: [email protected]<mailto:[email protected]>
Betreff: RE: [toad] F4 on object type is slow


It's not a Toad problem per se and it will not be fixed (at least it cannot be done universally) - Toad just happens to expose an issue for your DBA's. You would have the exact same problems using SQL Developer or OEM. It's really a DBA "best practice" change in requirements that says once you manage a database where cost based optimizer is the default - if you have users with tools that make heavy access to the data dictionary then the default automatic stats collection is not sufficient and DBA must create and manage their own jobs to do that more completely and aggressively. So far Oracle has not expressed this clearly enough for the light bulb to click for most DBA's - and hence most are hesitant or downright against it. I can only hope that askTom will tell them so that they will finally listen :(

Bert

From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Patrik
Sent: Tuesday, May 08, 2012 8:42 AM
To: [email protected]<mailto:[email protected]>
Subject: AW: [toad] F4 on object type is slow


Hi Bert

Thank you for this information.

Do you have another idea, what we can do to get rid of the problem?
I can wait for the next beta, but my coworkers have to wait for the next GA.

Thank you very much.

Patrik

Von: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> Im Auftrag von Bert Scalzo
Gesendet: Dienstag, 8. Mai 2012 13:54
An: [email protected]<mailto:[email protected]>
Betreff: RE: [toad] F4 on object type is slow


Thanks for getting the DBA's to check this. The blog was written for 10g but it applies to any release after 9i. Oracle changed from rule based to cost based optimizer. The problem is most DBA's assume or misread the Oracle doc's regarding automatic statistics collection. Everyone wrongfully assumes that all data dictionary tables MUST be auto magically covered when in fact they are not. For example the second command in my example (for X$ objects) does not persist across instance restarts - so there are periods where "auto magic" does not cover one. The only real way in any post 9i database (so 10g, 11g and 12c beta) to have reliable data dictionary statistics is to create something yourself as a DBA (very similar to creating your own roles and not using the old CONNECT, DBA and RESOURCE roles).

But very few DBA's pay attention to this since most apps don't make heavy use of the data dictionary. Only apps like OEM, Toad and SQL Developer bang on the database hard enough to expose the actual problem and most DBA's just ignore it.

From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Patrik
Sent: Tuesday, May 08, 2012 2:21 AM
To: [email protected]<mailto:[email protected]>
Subject: AW: [toad] F4 on object type is slow


Hi Bert

We did both, but it did not help.
Btw: As I said, we have Oracle 11.1.0.7 and the blog refers about 10g.

Thanks,
Patrik

Von: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> Im Auftrag von Bert Scalzo
Gesendet: Montag, 7. Mai 2012 13:48
An: [email protected]<mailto:[email protected]>
Betreff: RE: [toad] F4 on object type is slow


Did you ever get your data dictionary statistics updated as John rightfully asked you about? Hints are generally not a great work around .....

http://www.toadworld.com/Blogs/tabid/67/EntryId/135/Toad-Sometimes-Slow-on-Oracle-10g.aspx

You rally should connect as a "DBA" type privileged user and run these two commands:

*  exec dbms_stats.gather_dictionary_stats;
*  exec dbms_stats.gather_fixed_objects_stats;

From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Patrik
Sent: Sunday, May 06, 2012 11:57 PM
To: [email protected]<mailto:[email protected]>
Subject: AW: [toad] F4 on object type is slow


Hi John

Not in my case: without rule, the user_ views (instead of dba_ views) have the same bad performance.

On the other hand, I found this, which takes 30 seconds (without hint rule, it would take 1 second)

  SELECT /*+ RULE */
        USER OWNER,
         o.object_id,
         o.object_name,
         o.CREATED,
         o.last_ddl_time,
         o.status,
         T.typecode,
         T.ATTRIBUTES,
         T.methods,
         o.object_type,
         i.debuginfo
    FROM sys.user_objects o, sys.user_types T, sys.all_probe_objects i
   WHERE     o.object_type IN ('TYPE', 'TYPE BODY')
         AND T.type_name = o.object_name
         AND o.object_id = i.object_id(+)
         AND o.subobject_name IS NULL
         AND T.type_name = :obj
ORDER BY o.object_type

Patrik

Von: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> Im Auftrag von John Dorlon
Gesendet: Freitag, 4. Mai 2012 17:04
An: [email protected]<mailto:[email protected]>
Betreff: RE: [toad] F4 on object type is slow


Patrik, another thing you can do to speed this up is log is as the owner of the type you want to describe.  Then we'll use the USER_ views instead of DBA_ views.  They are usually faster.

From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Patrik
Sent: Friday, May 04, 2012 8:49 AM
To: [email protected]<mailto:[email protected]>
Subject: AW: [toad] F4 on object type is slow


Hi John

Thank you, I'll inform our DBA's.

Patrik

Von: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> Im Auftrag von John Dorlon
Gesendet: Freitag, 4. Mai 2012 15:22
An: [email protected]<mailto:[email protected]>
Betreff: RE: [toad] F4 on object type is slow


Hi Patrik,

  Yeah, I see we don't put a hint into that query.  I will fix that.

Some thoughts on what you can do now -

  Are statistics on the data dictionary current?
  If you are licensed for it, maybe running it through the Oracle Tuning Advisor (off the optimize menu) would help.

-John



From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Patrik
Sent: Friday, May 04, 2012 8:15 AM
To: [email protected]<mailto:[email protected]>
Subject: [toad] F4 on object type is slow


When I want to see a describe of an oracle object type, it takes 30 seconds.

I found that the following select is slow:

  SELECT M.Type_name,
         M.Method_Name,
         M.Method_No,
         M.Method_Type,
         r.Type_Name,
         r.Method_Name,
         r.Result_Type_Mod,
         r.Result_Type_Owner,
         r.Result_Type_Name
    FROM DBA_TYPE_METHODS M, DBA_METHOD_RESULTS r
   WHERE     (M.type_name = :ObjName)
         AND (M.Owner = :Owner)
         AND (r.Type_name(+) = M.type_name)
         AND (r.method_name(+) = M.method_name)
         AND (r.method_no(+) = M.method_no)
         AND (r.Owner(+) = :Owner)
ORDER BY M.method_name

What can I do to improve this?

Fyi: I tried Hint Rule: it would really improve this statment.
So I added both tables in the oracle hint table (in the Toad options->Oracle->Optimizer Hints), but the hint was not used by the query.

I use Toad 11.5 (it was already slow in older versions of toad) and Oracle 11.1.0.7

Thanks,
Patrik