优选主流主机商
任何主机均需规范使用

深入解析DBA_INDEXES视图:数据库性能优化与索引效率提升技巧

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’);

语句运行的速度很快,但是从statspack中发现这条语句的逻辑读单次高达26846。使用set autotrace比较了下9i和10g的执行计划和统计信息,发现9i查询这个视图的代价非常的高,而10g则有了一定的改善。在Oracle9i中,optimizer_mode默认是CHOOSE,所以查询数据字典使用了RBO,而Oracle10g则默认为ALL_ROWS,所以采用了CBO。

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 System/6000: Version 9.2.0.6.0 – Production
NLSRTL Version 9.2.0.6.0 – Production

SQL> set autot trace
SQL> select * from 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 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
———————————————————-
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
84 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:15 SYS@coll>select * from dba_indexes;

1162 rows selected.

Execution Plan
———————————————————-
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 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 processed

select /*+ rule */* from dba_indexes;

1162 rows selected.

Elapsed: 00:00:00.55

Execution Plan
———————————————————-
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
—–
– rule based optimizer used (consider using cbo)

Statistics
———————————————————-
0 recursive calls
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

未经允许不得转载:搬瓦工中文网 » 深入解析DBA_INDEXES视图:数据库性能优化与索引效率提升技巧