最近日常檢查查看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很困難總之在一個線上系統做點調整真難
From:http://tw.wingwit.com/Article/program/Oracle/201311/18189.html
