AW: F4 on object type is slow

Hi John

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

Patrik

Von: [email protected] [mailto:[email protected]] Im Auftrag von John Dorlon
Gesendet: Freitag, 4. Mai 2012 15:22
An: [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