Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2154354
  • 博文数量: 157
  • 博客积分: 10047
  • 博客等级: 上将
  • 技术积分: 6757
  • 用 户 组: 普通用户
  • 注册时间: 2005-05-19 11:38
文章分类

全部博文(157)

文章存档

2011年(16)

2010年(50)

2009年(42)

2008年(49)

我的朋友

分类: Oracle

2008-07-04 16:51:29

作者:
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很困难,总之在一个线上系统做点调整真难。
阅读(1609) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~