Dba_indexes View Performance Analysis _ Database other

Select Case when status= ' unusable ' then '
        alter index ' | | owner| | '. ' | | index_name| | ' Rebuild online compute statistics; '
      When To_number (degree) >1 then
        ' ALTER INDEX/* ' | | degree | | | owner| | '. ' | | index_name| | ' noparallel; '
    End Case from
(SELECT * from dba_indexes where degree<> ' DEFAULT ') a
where status= ' unusable '
or to_ Number (degree) >1 and owner not in
(' SYS ', ' SYSTEM ', ' MANAGER ', ' Wmsys ');

The statement runs quickly, but the logical reading of this statement from the Statspack is as high as 26846. Using set Autotrace to compare execution plans and statistics for the next 9i and 10g, it was found that the cost of the 9i query was very high, while 10g improved somewhat. In Oracle9i, the Optimizer_mode default is choose, so the query data dictionary uses RBO, and oracle10g defaults to All_rows, so the CBO is used.

Sql> select * from V$version; BANNER----------------------------------------------------------------oracle9i Enterprise Edition Release 9.2.0.6.0-64bit Production pl/sql release 9.2.0.6.0-production CORE 9.2.0.6.0 Production TNS for Ibm/aix RISC Syst Em/6000:version 9.2.0.6.0-production nlsrtl Version 9.2.0.6.0-production sql> set Autot trace sql> Select * FR

Om dba_indexes;

1242 rows selected.  Execution Plan----------------------------------------------------------0 SELECT STATEMENT optimizer=choose 1 0  NESTED LOOPS (OUTER) 2 1 NESTED LOOPS (OUTER) 3 2 NESTED LOOPS 4 3 NESTED LOOPS 5 4 NESTED LOOPS (OUTER) 6 5 NESTED LOOPS 7 6 NESTED LOOPS (OUTER) 8 7 NESTED LOOPS 9 8 TABLE AC CESS (Full) ' obj$ ' 8 TABLE ACCESS (by INDEX ROWID) of ' ind$ ' (UNIQUE SCAN) of ' i_ IND1 ' (UNIQUE) 7 TABLE ACCESS (by INDEX ROWID) of ' obj$ ' INdex (unique SCAN) of ' i_obj1 ' (unique) 6 TABLE ACCESS (by INDEX ROWID) of ' obj$ ' (uniqu E SCAN) of ' i_obj1 ' (unique) 5 TABLE ACCESS (CLUSTER) ' user$ ' (unique SCAN) of ' i_user#
 ' (non-unique) 4 TABLE ACCESS (CLUSTER) ' user$ ' (UNIQUE SCAN) of ' i_user# ' (non-unique) 3 table ACCESS (CLUSTER) ' user$ ' (UNIQUE SCAN) of ' i_user# ' (non-unique) 2 table ACC ESS (CLUSTER) ' seg$ ' (UNIQUE SCAN) of ' i_file#_block# ' (non-unique) 1 TABLE ACCESS (CLUSTER) O F ' ts$ ' INDEX (UNIQUE SCAN) of ' i_ts# ' (non-unique) Statistics-----------------------------------------------
   -----------0 Recursive calls 0 db block gets 42924 consistent gets 0 physical reads 0 redo size  98000 Bytes sent via sql*net to client 1558 bytes-received via sql*net from client-sql*net roundtrips To/from Client 0 Sorts (meMory) 0 Sorts (disk) 1242 rows processed
 

Sql>select * from V$version; BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bi pl/sql release 10.2.0.4.0-production CORE 10.2.0.4.0 Production TNS for Ibm/aix RISC System /6000:version 10.2.0.4.0-productio nlsrtl Version 10.2.0.4.0-production 21:32:11 sys@coll>set autot Trace 21:32:1

5 Sys@coll>select * from dba_indexes;

1162 rows selected. Execution Plan----------------------------------------------------------The plan hash value:3901056803--------------- -------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |  ----------------------------------------------------------------------------------------------
| 0 |    SELECT STATEMENT | |  1164 |  330k| 237 (3) | 00:00:03 | |* 1 |    HASH JOIN Right OUTER | |  1164 |  330k| 237 (3) |  00:00:03 | |  2 | TABLE ACCESS Full        |   ts$ |  9 |   189 | 4 (0) |  00:00:01 | |* 3 |    HASH JOIN Right OUTER | |  1164 |  306k| 232 (3) |  00:00:03 | |  4 | TABLE ACCESS Full | seg$ |  2635 |  102k| 18 (0) |  00:00:01 | |* 5 |    HASH JOIN | |  1164 |  261k| 214 (3) |  00:00:03 | |   6 | TABLE ACCESS Full |  user$ |  35 |   560 | 2 (0) |   00:00:01 | |* 7 |    HASH JOIN | |  1164 |  243k| 211 (3) |  00:00:03 | |   8 | TABLE ACCESS Full |  user$ |  35 |   560 | 2 (0) |   00:00:01 | |* 9 |    HASH JOIN Right OUTER | |  1164 |  225k| 208 (2) | 00:00:03 | |    10 | TABLE ACCESS Full |  user$ |  35 |   560 | 2 (0) |    00:00:01 | |* 11 |    HASH JOIN | |  1164 |  206k| 206 (2) |    00:00:03 | |* 12 |    HASH JOIN OUTER | |  1164 |  172k| 174 (2) | 00:00:03 | |     13 |    MERGE JOIN | |  1164 |  142k| 142 (2) |     00:00:02 | |* 14 | TABLE ACCESS by INDEX rowid| ind$ |  1164 |  104k| 109 (0) | 00:00:02 | |      15 | INDEX Full SCAN     | I_ind1 |    1164 |   | 2 (0) |     00:00:01 | |* 16 |    SORT JOIN | |  10589 |  341k| 33 (7) |      00:00:01 | |* 17 | TABLE ACCESS Full | obj$ |  10589 |  341k| 32 (4) | 00:00:01 | |     18 | TABLE ACCESS Full | obj$ |  10592 |  279k| 31 (0) | 00:00:01 | |    19 | TABLE ACCESS Full | obj$ |  10592 |  310k| 31 (0) |
00:00:01 | ----------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------1-access ("I".) ts# "=" TS "." ts# "(+)) 3-access (" I ".") file# "=" S "." file# "(+) and" I ". block# "=" S "." block# "(+) and" I ". ts# "=" S "." ts# "(+)) 5-access (" IO ".) owner# "=" IU "." user# ") 7-access (" U ".") user# "=" O "." owner# ") 9-access (" ITO "). owner# "=" ITU "." user# "(+)) 11-access (" I ".") bo# "=" IO "." obj# ") 12-access (" I "). indmethod# "= ITO". " obj# "(+)) 14-filter (Bitand (" I "). FLAGS ", 4096) =0) 16-access (" O ".) obj# "=" I "." Obj# ") FIlter ("O".) obj# "=" I "." obj# ") 17-filter (Bitand (" O "). FLAGS ", 128) =0) Statistics----------------------------------------------------------0 recursive calls 0 db blo CK gets 876 consistent gets 0 physical reads 0 Redo size 92582 bytes sent via sql*net to client 1339 
    Bytes received via sql*net from client sql*net roundtrips to/from client 1 sorts (memory) 0 sorts (disk)

1162 rows processed Select/*+ rule */* from dba_indexes;

1162 rows selected. elapsed:00:00:00.55 Execution Plan----------------------------------------------------------The plan hash value: 2107813288--------------------------------------------------------------| Id | Operation |
Name |  --------------------------------------------------------------
| 0 |        SELECT STATEMENT |  |
| 1 |        NESTED LOOPS OUTER |  |
|  2 |        NESTED LOOPS OUTER |  |
|  3 |        NESTED LOOPS |  |
|   4 |  NESTED LOOPS        |  |
|   5 |        NESTED LOOPS OUTER |  |
|    6 |        NESTED LOOPS |  |
|    7 |        NESTED LOOPS OUTER |  |
|     8 |        NESTED LOOPS |     | |* 9 | TABLE ACCESS Full |     obj$ | |* 10 | TABLE ACCESS by INDEX rowid|      ind$ | |* 11 | INDEX UNIQUE SCAN | I_ind1 | |     12 | TABLE ACCESS by INDEX ROWID |     obj$ | |* 13 | INDEX UNIQUE SCAN | i_obj1 | |    14 | TABLE ACCESS by INDEX ROWID |     obj$ | |* 15 | INDEX UNIQUE SCAN | i_obj1 | |    16 | TABLE ACCESS CLUSTER |    user$ | |* 17 | INDEX UNIQUE SCAN | i_user# | |   18 | TABLE ACCESS CLUSTER |    user$ | |* 19 | INDEX UNIQUE SCAN | i_user# | |   20 | TABLE ACCESS CLUSTER |   user$ | |* 21 | INDEX UNIQUE SCAN | i_user# | |  22 | TABLE ACCESS CLUSTER |   seg$ | |* 23 | INDEX UNIQUE SCAN | i_file#_block# | |  24 | TABLE ACCESS CLUSTER | ts$ | |* 25 |  INDEX UNIQUE SCAN |
i_ts# | --------------------------------------------------------------predicate information (identified by Operation ID):-- -------------------------------------------------9-filter (Bitand ("O"). FLAGS ", 128) =0) 10-filter (Bitand (" I "). FLAGS ", 4096) =0) 11-access (" O ".) obj# "=" I "." obj# ") 13-access (" I "). indmethod# "= ITO". " obj# "(+)) 15-access (" I ".") bo# "=" IO "." obj# ") 17-access (" ITO "). owner# "=" ITU "." user# "(+)) 19-access (" U ".) user# "=" O "." owner# ") 21-access (" IO "). owner# "=" IU "." user# ") 23-access (" I "). ts# "=" S "." ts# "(+) and" I ". file# "=" S "." file# "(+) and" I ". block# "=" S "." block# "(+)) 25-access (" I ".") ts# "=" TS "." ts# "(+)) Note------The rule based optimizer used (consider using CBO) Statistics-------------------------------------- --------------------0 Recursive calls 0 db block gets 25254 consistent gets physical 0 re Do size 93977 bytes sent via sql*net to client 1339 bytes via receivedEt from client-sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1162 rows processed