RE: AW: 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]
From: [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 = :objORDER BY o.object_type Patrik Von: [email protected] [mailto:[email protected]] Im Auftrag von John Dorlon
Gesendet: Freitag, 4. Mai 2012 17:04
An: [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]] On Behalf Of Böschenstein Patrik
Sent: Friday, May 04, 2012 8:49 AM
To: [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]] 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]] On Behalf Of Patrik
Sent: Friday, May 04, 2012 8:15 AM
To: [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