RE: F4 on object type is slow

I've made a change to next beta so the hints are observed in the first query you mentioned.     When that beta comes out, you can turn on the hint for DBA_TYPE_METHODS to get a hint just in that query and not the second one you mentioned.


From: [email protected] [mailto:[email protected]] On Behalf Of Böschenstein Patrik
Sent: Sunday, May 06, 2012 11:57 PM
To: [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 Böschenstein 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