最近日常检查查看awr报告发现由一个索引的逻辑读很大
Tablespace Subobject Obj Logical
Owner Name Object Name Name Type Reads %Total
BASECONF TBS_DEFALU PK_USER_TO_CHANNEL INDEX
VIPBILL TBS_DEFALU BIL_COLLECT_BIT TABLE
BASECONF DATA_ LAYER_CHANNEL_TO_NOD TABLE
BASECONF DATA_ PK_ INDEX
LOGBILL TBS_BAND_D IX_DAILYTABLE_ALL P INDEX
表结构如下
Create table
create table USER_TO_CHANNEL
(
USER_ID NUMBER() not null
CHANNEL_ID CHAR() not null
constraint PK_USER_TO_CHANNEL primary key (USER_ID CHANNEL_ID)
)
organization index;
这是一个索引组织表表的所有字段组成一个主键索引用索引组织表是没有问题的
查找top sql发现有如下sql操作USER_TO_CHANNEL
garqxtutkp
Module: JDBC Thin Client
select ACHANNEL_ID BCHANNEL_NAME from USER_TO_CHANNEL APUB_CHANNEL B where A
USER_ID= and ACHANNEL_ID=BCHANNEL_ID and BCHANNEL_TYPE=HTTP
SQL> select count(*) from USER_TO_CHANNEL;
COUNT(*)
SQL> select count(*) from PUB_CHANNEL;
COUNT(*)
SQL>
查看sql的执行计划
SQL> set autotrace traceonly;
SQL> select ACHANNEL_ID BCHANNEL_NAME
from USER_TO_CHANNEL A PUB_CHANNEL B
where AUSER_ID =
and ACHANNEL_ID = BCHANNEL_ID
and BCHANNEL_TYPE = HTTP;
rows selected
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| | SELECT STATEMENT | | | | |
| | NESTED LOOPS | | | | |
| | TABLE ACCESS FULL| PUB_CHANNEL | | K| |
| | INDEX UNIQUE SCAN| PK_USER_TO_CHANNEL | | | |
Note
PLAN_TABLE is old version
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
oracle默认采用NL进行全表扫描
此时的逻辑读=consistent gets+db block gets=
在sql语句中加入hint（）
SQL> select /*+use_hash(ba)*/ ACHANNEL_ID BCHANNEL_NAME
from USER_TO_CHANNEL A PUB_CHANNEL B
where AUSER_ID =
and ACHANNEL_ID = BCHANNEL_ID
and BCHANNEL_TYPE = HTTP;
rows selected
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost |
| | SELECT STATEMENT | | | | |
| | HASH JOIN | | | | |
| | INDEX RANGE SCAN | PK_USER_TO_CHANNEL | | | |
| | TABLE ACCESS FULL| PUB_CHANNEL | | K| |
Note
PLAN_TABLE is old version
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
在sql语句中加入hint此时的逻辑读有明显下降
此时的逻辑读=consistent gets+db block gets=
在sql语句中加入hint（）
SQL> select /*+ use_nl(ba) leading(a) */ ACHANNEL_ID BCHANNEL_NAME
from USER_TO_CHANNEL A PUB_CHANNEL B
where AUSER_ID =
and ACHANNEL_ID = BCHANNEL_ID
and BCHANNEL_TYPE = HTTP;
rows selected
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost
|
| | SELECT STATEMENT | | | |
|
| | NESTED LOOPS | | | |
|
| | INDEX RANGE SCAN | PK_USER_TO_CHANNEL | | |
|
| | TABLE ACCESS BY INDEX ROWID| PUB_CHANNEL | | |
|
| | INDEX UNIQUE SCAN | PK_CHANNEL | | |
|
Note
PLAN_TABLE is old version
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL>
结论还是由于sql的质量比较差导致的逻辑读比较多
此时的逻辑读=consistent gets+db block gets=
感慨原因虽然找到了在一个线上系统查找该sql很困难总之在一个线上系统做点调整真难
