SQL质量差导致逻辑读比较多Oracle认证考试
文章作者 100test 发表时间 2009:12:19 11:26:07
来源 100Test.Com百考试题网
"tb42" class="mar10">
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
0select 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>. 0select count(*) from USER_TO_CHANNEL.
COUNT(*)
----------
4259
SQL>. 0select count(*) from PUB_CHANNEL.
COUNT(*)
----------
4260
SQL>.