Dba_indexes view Performance Analysis

 
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 casefrom (select * From dba_indexes where degree <> 'default') awhere status = 'unusable' or to_number (degree)> 1and owner not in ('sys ', 'system', 'manager', 'wmsys ');
 

The statement runs quickly, but it is found from statspack that the logic read of this statement is as high as 26846 at a time. UseSet autotraceAfter comparing the execution plans and statistics of 9i and 10g, we found that the cost of 9i querying this view is very high, while 10G has improved. In Oracle9i, optimizer_mode is choose by default, so RBO is used for data dictionary query, while Oracle10g is all_rows by default, so CBO is used.

SQL> select * from V $ version; banner ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.6.0-64bit productionpl/SQL release 9.2.0.6.0-productioncore 9.2.0.6.0 productiontns for IBM/AIX RISC system/6000: version 9.2.0.6.0-productionnlsrtl version 9.2.0.6.0-productionsql> set autot tracesql> select * From dba_indexes; 1242 rows selected. execution Plan execution 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 Access (full) of 'obj $'10 8 Table Access (by index rowid) of 'ind $'11 10 index (unique scan) of 'I _ ind1' (unique) 12 7 Table Access (by index rowid) of 'obj $'13 12 index (unique scan) of 'I _ obj1' (unique) 14 6 Table Access (by index rowid) of 'obj $'15 14 index (unique scan) of 'I _ obj1' (unique) 16 5 Table Access (cluster) of 'User $' 17 16 index (unique scan) of 'I _ user #' (NON-UNIQUE) 18 4 Table Access (cluster) of 'User $'19 18 index (unique scan) of' I _ user # '(NON-UNIQUE) 20 3 Table Access (cluster) of 'User $'21 20 index (unique scan) of' I _ user' (NON-UNIQUE) 22 2 Table Access (cluster) of 'seg $'23 22 index (unique scan) of 'I _ file # _ block #' (NON-UNIQUE) 24 1 Table Access (cluster) of 'ts $'25 24 index (unique scan) of 'I _ TS #' (NON-UNIQUE) statistics defaults 0 recursive cballs 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 84 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 1242 rows processed

SQL> select * from V $ version; banner implements Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64 bipl/SQL release 10.2.0.4.0-productioncore 10.2.0.4.0 productiontns for IBM/AIX RISC system/6000: version 10.2.0.4.0-productionlsrtl version 10.2.0.4.0-production21: 32: 11 sys @ Coll> set autot trace21: 32: 15 sys @ Coll> select * From dba_indexes; 1162 rows selected. execution Plan -------------------------------------------------------- plan hash value: 3901056803 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | minute | 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 | identified 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 defaults 0 recursive cballs 0 dB block 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 79 SQL * Net roundtrips to/from client 1 sorts (memory) 0 sorts (Disk) 1162 rows processedselect/* + rule */* From dba_indexes; 1162 rows selected. elapsed: 00:00:00. 55 execution plan ---------------------------------------------------------- plan hash value: 2107813288 bytes | ID | operation | Name | percent | 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 ------rule based optimizer used (consider using CBO) statistics defaults 0 recursive cballs 0 dB block gets 25254 consistent gets 26 physical reads 0 redo size 93977 bytes sent via SQL * Net to client 1339 bytes received via SQL * Net from client 79 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 1162 rows processed