AW: AW: F4 on object type is slow

Hi Martin

Hint rule: Oracle does not use the CBO, it takes general access rules.
This is old you should not use this...

Sorry, I do not get what you mean with your second question.

Patrik

Von: [email protected] [mailto:[email protected]] Im Auftrag von Martin Gainty
Gesendet: Montag, 7. Mai 2012 12:32
An: TOAD Yahoogroup
Betreff: RE: AW: [toad] F4 on object type is slow


Good Morning Patrik-

what is RULE in your HINT clause?

i *thought* IN is always rewritten as EXISTS
http://www.dba-oracle.com/sql/t_subquery_not_in_exists.htm?

Martin
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.


________________________________
To: [email protected]<mailto:[email protected]>
From: [email protected]<mailto:[email protected]>
Date: Mon, 7 May 2012 06:56:50 +0200
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