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
2273 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
33 rows processed
oracle默认采用NL进行全表扫描
此时的逻辑读=consistent gets+db block gets=4194
5、在sql语句中加入hint（1）
SQL> select /*+use_hash(b,a)*/ 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 | 27 |
| 1 | HASH JOIN | | 34 | 1496 | 27 |
| 2 | INDEX RANGE SCAN | PK_USER_TO_CHANNEL | 34 | 272 | 2 |
| 3 | TABLE ACCESS FULL| PUB_CHANNEL | 4053 | 142K| 24 |
-------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
112 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
33 rows processed
在sql语句中加入hint，此时的逻辑读有明显下降
此时的逻辑读=consistent gets+db block gets=112
6、在sql语句中加入hint（2）
SQL> select /*+ use_nl(b,a) leading(a) */ 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 | 3
6 |
| 1 | NESTED LOOPS | | 34 | 1496 | 3
6 |
| 2 | INDEX RANGE SCAN | PK_USER_TO_CHANNEL | 34 | 272 |
2 |
| 3 | TABLE ACCESS BY INDEX ROWID| PUB_CHANNEL | 1 | 36 |
1 |
| 4 | INDEX UNIQUE SCAN | PK_CHANNEL | 1 | |
0 |
--------------------------------------------------------------------------------
---
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
77 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
33 rows processed
SQL>
结论：还是由于sql的质量比较差导致的逻辑读比较多。
此时的逻辑读=consistent gets+db block gets=77
感慨：原因虽然找到了，在一个线上系统查找该sql很困难，总之在一个线上系统做点调整真难