1、最近日常检查查看awr报告，发现由一个索引的逻辑读很大
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
BASECONF TBS_DEFALU PK_USER_TO_CHANNEL INDEX 10,318,512 16.95
VIPBILL TBS_DEFALU BIL_COLLECT_BIT TABLE 6,285,648 10.33
BASECONF DATA_04 LAYER_CHANNEL_TO_NOD TABLE 6,042,416 9.93
BASECONF DATA_04 PK3_1 INDEX 3,952,688 6.49
LOGBILL TBS_BAND_D IX_DAILYTABLE_ALL P20080704 INDEX 3,575,488 5.87
2、表结构如下：
-- Create table
create table USER_TO_CHANNEL
(
USER_ID NUMBER(4) not null,
CHANNEL_ID CHAR(4) not null,
constraint PK_USER_TO_CHANNEL primary key (USER_ID, CHANNEL_ID)
)
organization index;
这是一个索引组织表，表的所有字段组成一个主键索引，用索引组织表是没有问题的。
3、查找top sql发现有如下sql操作USER_TO_CHANNEL
1,404,990 335 4,194.0 2.3 6.48 6.67 garq0xt0u3tkp
Module: JDBC Thin Client
select A.CHANNEL_ID, B.CHANNEL_NAME from USER_TO_CHANNEL A,PUB_CHANNEL B where A
.USER_ID=283 and A.CHANNEL_ID=B.CHANNEL_ID and B.CHANNEL_TYPE='HTTP'
SQL> select count(*) from USER_TO_CHANNEL;
COUNT(*)
----------
4259
SQL> select count(*) from PUB_CHANNEL;
COUNT(*)
----------
4260
SQL>
4、查看sql的执行计划
SQL> set autotrace traceonly;
SQL> select A.CHANNEL_ID, B.CHANNEL_NAME
2 from USER_TO_CHANNEL A, PUB_CHANNEL B
3 where A.USER_ID = 283
4 and A.CHANNEL_ID = B.CHANNEL_ID
5 and B.CHANNEL_TYPE = 'HTTP';
33 rows selected.
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 1496 | 25 |
| 1 | NESTED LOOPS | | 34 | 1496 | 25 |
| 2 | TABLE ACCESS FULL| PUB_CHANNEL | 4053 | 142K| 24 |
| 3 | INDEX UNIQUE SCAN| PK_USER_TO_CHANNEL | 1 | 8 | 0 |
-------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4194 consistent gets
0 physical reads
0 redo size
责任编辑：小草
您现在的位置： 