Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: More selective means slower?

RE: More selective means slower?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 20 Jan 2005 15:03:26 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87C07@irvmbxw02> 





 I know it's a privilege thing, but here's an example of what I was trying to say. Note the ORA-1039 error for the user with the DBA role.  This error is caused by the use of X$KTFBUE in the underlying tables. 
SQL> --- with DBA role: unable to do explain plan on dba_extents
SQL> connect system


Entrez le mot de passe : *****


Connecté.


SQL> select * from v$version ;


BANNER





Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production


CORE    9.2.0.3.0       Production


TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production



SQL> set autotrace traceonly explain


SQL> select * from dba_extents ;


Plan d'exécution




ERROR:



ORA-01039: privilèges insuffisants pour les objets sous-jacents de la vue
SP2-0612: Erreur lors de la génération de l'état AUTOTRACE EXPLAIN



SQL> --- CONNECT AS SYSDBA: now able to do explain plan on dba_extents

SQL> connect sys/****************************** as sysdba


Connecté.


SQL> set autotrace traceonly explain


SQL> select * from dba_extents ;


Plan d'exécution




   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=2 Bytes=364)
   1    0   VIEW OF 'DBA_EXTENTS' (Cost=26 Card=2 Bytes=364)

   2    1     UNION-ALL
   3    2       NESTED LOOPS (Cost=5 Card=1 Bytes=299)
   4    3         NESTED LOOPS (Cost=4 Card=1 Bytes=260)
   5    4           VIEW OF 'SYS_DBA_SEGS' (Cost=2 Card=1 Bytes=169)
   6    5             UNION-ALL
   7    6               NESTED LOOPS (Cost=748 Card=1 Bytes=306)
   8    7                 NESTED LOOPS (Cost=747 Card=1 Bytes=276)
   9    8                   NESTED LOOPS (Cost=746 Card=1 Bytes=203)
  10    9                     MERGE JOIN (Cost=745 Card=1 Bytes=160)
  11   10                       SORT (JOIN) (Cost=20 Card=7 Bytes=637)
  12   11                         NESTED LOOPS (Cost=19 Card=7 Bytes=637)
  13   12                           TABLE ACCESS (FULL) OF 'SEG$' (Cost=17 Card=40 Bytes=2600)
  14   12                           INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
  15   10                       SORT (JOIN) (Cost=725 Card=24595 Bytes=1697055)
  16   15                         VIEW OF 'SYS_OBJECTS' (Cost=153 Card=24595 Bytes=1697055)
  17   16                           UNION-ALL
  18   17                             TABLE ACCESS (FULL) OF 'TAB$' (Cost=17 Card=41 Bytes=2665)
  19   17                             TABLE ACCESS (FULL) OF 'TABPART$' (Cost=17 Card=4072 Bytes=211744)
  20   17                             TABLE ACCESS (FULL) OF 'CLU$' (Cost=17 Card=4072 Bytes=211744)
  21   17                             TABLE ACCESS (FULL) OF 'IND$' (Cost=17 Card=41 Bytes=2665)
  22   17                             TABLE ACCESS (FULL) OF 'INDPART$' (Cost=17 Card=4072 Bytes=211744)
  23   17                             TABLE ACCESS (FULL) OF 'LOB$' (Cost=17 Card=81 Bytes=5265)
  24   17                             TABLE ACCESS (FULL) OF 'TABSUBPART$' (Cost=17 Card=4072 Bytes=211744)
  25   17                             TABLE ACCESS (FULL) OF 'INDSUBPART$' (Cost=17 Card=4072 Bytes=211744)
  26   17                             TABLE ACCESS (FULL) OF 'LOBFRAG$' (Cost=17 Card=4072 Bytes=219888)
  27    9                     TABLE ACCESS (CLUSTER) OF 'TS$' (Cost=2 Card=1 Bytes=43)
  28   27                       INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  29    8                   TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=2 Card=1 Bytes=73)
  30   29                     INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  31    7                 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=2 Card=1 Bytes=30)
  32   31                   INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  33    6               NESTED LOOPS (Cost=24 Card=1 Bytes=246)
  34   33                 NESTED LOOPS (Cost=23 Card=1 Bytes=216)
  35   34                   NESTED LOOPS (Cost=22 Card=1 Bytes=173)
  36   35                     NESTED LOOPS (Cost=19 Card=6 Bytes=570)
  37   36                       TABLE ACCESS (FULL) OF 'UNDO$' (Cost=17 Card=11 Bytes=759)
  38   36                       INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
  39   35                     TABLE ACCESS (CLUSTER) OF 'SEG$' (Cost=2 Card=1 Bytes=78)
  40   39                       INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
  41   34                   TABLE ACCESS (CLUSTER) OF 'TS$' (Cost=2 Card=1 Bytes=43)
  42   41                     INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  43   33                 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=2 Card=1 Bytes=30)
  44   43                   INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  45    6               NESTED LOOPS (Cost=21 Card=1 Bytes=190)
  46   45                 NESTED LOOPS (Cost=20 Card=1 Bytes=160)
  47   46                   NESTED LOOPS (Cost=19 Card=1 Bytes=121)
  48   47                     TABLE ACCESS (FULL) OF 'SEG$' (Cost=17 Card=1 Bytes=78)
  49   47                     TABLE ACCESS (CLUSTER) OF 'TS$' (Cost=2 Card=1 Bytes=43)
  50   49                       INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  51   46                   TABLE ACCESS (BY INDEX ROWID) OF 'FILE$' (Cost=2 Card=1 Bytes=39)
  52   51                     INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
  53   45                 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=2 Card=1 Bytes=30)
  54   53                   INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  55    4           TABLE ACCESS (CLUSTER) OF 'UET$' (Cost=2 Card=1 Bytes=91)
  56   55             INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
  57    3         TABLE ACCESS (BY INDEX ROWID) OF 'FILE$' (Cost=2 Card=1 Bytes=39)
  58   57           INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
  59    2       NESTED LOOPS (Cost=21 Card=1 Bytes=299)
  60   59         NESTED LOOPS (Cost=19 Card=1 Bytes=260)
  61   60           VIEW OF 'SYS_DBA_SEGS' (Cost=2 Card=1 Bytes=169)
  62   61             UNION-ALL
  63   62               NESTED LOOPS (Cost=770 Card=1 Bytes=306)
  64   63                 NESTED LOOPS (Cost=769 Card=1 Bytes=276)
  65   64                   NESTED LOOPS (Cost=768 Card=2 Bytes=406)
  66   65                     MERGE JOIN (Cost=767 Card=2 Bytes=320)
  67   66                       SORT (JOIN) (Cost=42 Card=744 Bytes=67704)
  68   67                         NESTED LOOPS (Cost=19 Card=744 Bytes=67704)
  69   68                           TABLE ACCESS (FULL) OF 'SEG$' (Cost=17 Card=3966 Bytes=257790)
  70   68                           INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
  71   66                       SORT (JOIN) (Cost=725 Card=24595 Bytes=1697055)
  72   71                         VIEW OF 'SYS_OBJECTS' (Cost=153 Card=24595 Bytes=1697055)
  73   72                           UNION-ALL
  74   73                             TABLE ACCESS (FULL) OF 'TAB$' (Cost=17 Card=41 Bytes=2665)
  75   73                             TABLE ACCESS (FULL) OF 'TABPART$' (Cost=17 Card=4072 Bytes=211744)
  76   73                             TABLE ACCESS (FULL) OF 'CLU$' (Cost=17 Card=4072 Bytes=211744)
  77   73                             TABLE ACCESS (FULL) OF 'IND$' (Cost=17 Card=41 Bytes=2665)
  78   73                             TABLE ACCESS (FULL) OF 'INDPART$' (Cost=17 Card=4072 Bytes=211744)
  79   73                             TABLE ACCESS (FULL) OF 'LOB$' (Cost=17 Card=81 Bytes=5265)
  80   73                             TABLE ACCESS (FULL) OF 'TABSUBPART$' (Cost=17 Card=4072 Bytes=211744)
  81   73                             TABLE ACCESS (FULL) OF 'INDSUBPART$' (Cost=17 Card=4072 Bytes=211744)
  82   73                             TABLE ACCESS (FULL) OF 'LOBFRAG$' (Cost=17 Card=4072 Bytes=219888)
  83   65                     TABLE ACCESS (CLUSTER) OF 'TS$' (Cost=2 Card=1 Bytes=43)
  84   83                       INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  85   64                   TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=2 Card=1 Bytes=73)
  86   85                     INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  87   63                 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=2 Card=1 Bytes=30)
  88   87                   INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  89   62               NESTED LOOPS (Cost=24 Card=1 Bytes=246)
  90   89                 NESTED LOOPS (Cost=23 Card=1 Bytes=216)
  91   90                   NESTED LOOPS (Cost=22 Card=1 Bytes=173)
  92   91                     NESTED LOOPS (Cost=19 Card=6 Bytes=570)
  93   92                       TABLE ACCESS (FULL) OF 'UNDO$' (Cost=17 Card=11 Bytes=759)
  94   92                       INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
  95   91                     TABLE ACCESS (CLUSTER) OF 'SEG$' (Cost=2 Card=1 Bytes=78)
  96   95                       INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
  97   90                   TABLE ACCESS (CLUSTER) OF 'TS$' (Cost=2 Card=1 Bytes=43)
  98   97                     INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  99   89                 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=2 Card=1 Bytes=30)
 100   99                   INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
 101   62               NESTED LOOPS (Cost=21 Card=1 Bytes=190)
 102  101                 NESTED LOOPS (Cost=20 Card=1 Bytes=160)
 103  102                   NESTED LOOPS (Cost=19 Card=1 Bytes=121)
 104  103                     TABLE ACCESS (FULL) OF 'SEG$' (Cost=17 Card=1 Bytes=78)
 105  103                     TABLE ACCESS (CLUSTER) OF 'TS$' (Cost=2 Card=1 Bytes=43)
 106  105                       INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
 107  102                   TABLE ACCESS (BY INDEX ROWID) OF 'FILE$' (Cost=2 Card=1 Bytes=39)
 108  107                     INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
 109  101                 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=2 Card=1 Bytes=30)
 110  109                   INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
 111   60           FIXED TABLE (FIXED INDEX) OF 'X$KTFBUE (ind:1)' (Cost=17 Card=4 Bytes=364)
 112   59         TABLE ACCESS (BY INDEX ROWID) OF 'FILE$' (Cost=2 Card=1 Bytes=39)
 113  112           INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)





SQL> 






--



http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 20 2005 - 18:06:34 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US