转自:http://blog.chinaunix.net/uid-21271882-id-1828799.html


 

 

一、TOP监控情况

System: crmdb1                                        Fri May 28 10:06:06 2010

Load averages: 0.47, 0.52, 0.53

1115 processes: 1068 sleeping, 46 running, 1 zombie

Cpu states:

CPU   LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS

 0    0.55  36.3%   0.0%   1.2%  62.5%   0.0%   0.0%   0.0%   0.0%

 1    0.42  31.0%   0.0%   6.3%  62.7%   0.0%   0.0%   0.0%   0.0%

 2    0.40  32.2%   0.0%   1.4%  66.4%   0.0%   0.0%   0.0%   0.0%

 3    0.40  26.7%   0.0%   2.4%  70.9%   0.0%   0.0%   0.0%   0.0%

 8    0.45  28.3%   0.0%   1.8%  69.9%   0.0%   0.0%   0.0%   0.0%

 9    0.49  34.0%   0.0%   3.1%  62.9%   0.0%   0.0%   0.0%   0.0%

10    0.53  78.8%   0.0%   6.1%  15.1%   0.0%   0.0%   0.0%   0.0%

11    0.55  45.6%   0.0%   1.2%  53.2%   0.0%   0.0%   0.0%   0.0%

---   ----  -----  -----  -----  -----  -----  -----  -----  -----

avg   0.47  39.1%   0.0%   2.9%  58.0%   0.0%   0.0%   0.0%   0.0%

 

System Page Size: 4Kbytes

Memory: 14820884K (4681268K) real, 18808076K (5799412K) virtual, 2369248K free Page# 1/62

 

CPU TTY  PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU COMMAND

11   ? 14024 oracle   241 20 15794M 26876K run     21:16 99.60 99.43 oraclengcrm

 2   ? 12899 oracle   241 20 15771M  5868K run   1916:37 55.51 55.41 oraclengcrm

 2   ? 18469 oracle   154 20 15774M  7676K sleep  591:04 19.73 19.69 oraclengcrm

 1   ? 18467 oracle   154 20 15774M  7476K sleep  611:00 14.11 14.09 oraclengcrm

 8   ? 18471 oracle   154 20 15774M  7436K sleep  576:03  9.70  9.68 oraclengcrm

11   ? 18486 oracle   154 20 15774M  7380K sleep  565:34  7.04  7.02 oraclengcrm

 9   ? 20916 oracle   148 20 15774M  7776K sleep    0:34  5.39  5.38 oraclengcrm

 2   ? 29664 oracle   154 20 15773M  7336K sleep 2026:26  5.32  5.31 oraclengcrm

 8   ? 18484 oracle   196 20 15774M  7468K run    594:40  4.36  4.35 oraclengcrm

 2   ? 12919 oracle   154 20 15772M  6132K sleep  142:29  4.24  4.23 oraclengcrm

 3   ?  3703 oracle   154 20 15774M  7564K sleep  117:26  3.65  3.64 oraclengcrm

 3   ? 18551 oracle   154 20 15773M  5892K sleep 1369:37  3.45  3.45 oraclengcrm

 9   ?  3284 oracle   154 20 15775M  8568K sleep   35:05  3.38  3.38 oraclengcrm

11   ?  3278 oracle   154 20 15774M  7864K sleep   32:01  3.29  3.29 oraclengcrm

 9   ?  2427 oracle   154 20 15774M  7460K sleep    5:19  2.81  2.80 oraclengcrm

10   ? 15324 oracle   154 20 15775M  7536K sleep    0:43  2.66  2.66 oraclengcrm

 0   ? 16137 oracle   154 20 15773M  6264K sleep 5544:26  2.46  2.46 oraclengcrm

二、查看具体SQL语句

1.首先查看PID 14024对应SQL

SQL> SELECT SQL_TEXT FROM V$SQLTEXT WHERE (ADDRESS,HASH_VALUE) =

  2  (select SQL_ADDRESS,SQL_HASH_VALUE from v$session  where sid =

  3  (

  4  select sid from v$session where v$session.paddr = (

  5  select addr from v$process where spid =&spid)

  6  )

  7  ) order by piece;

Enter value for spid: 14024

old   5: select addr from v$process where spid =&spid)

new   5: select addr from v$process where spid =14024)

 

SQL_TEXT

----------------------------------------------------------------

select DISTINCT c.log_id FROM tab_ren_ykc_02 a , UOP_CRM1.TF_B_V

ALUECARD_SALE_DETAIL b,uop_crm1.TF_B_RES_SALE_LOG  c WHERE a.sno

>=b.start_value AND a.sno <= b.end_value AND b.log_id=c.log_id

 

2.查看对应用户名、程序名

SQL> set linesize 300

SQL> col machine for a15

SQL> col USERNAME for a15

SQL> col MODULE for a20

SQL> col CLIENT_INFO for a15

SQL> col PROGRAM for a20

SQL> select MACHINE,USERNAME,SID,MODULE,CLIENT_INFO,PROGRAM

  2     from v$session

  3     where paddr=(select addr from v$process where spid=&spid);

Enter value for spid: 14024

old   3:        where paddr=(select addr from v$process where spid=&spid)

new   3:        where paddr=(select addr from v$process where spid=14024)

 

MACHINE         USERNAME               SID MODULE               CLIENT_INFO    PROGRAM

--------------- --------------- ---------- -------------------- --------------- --------------------

WORKGROUP\LT-RE UCR_CRM1               328 PL/SQL Developer                    plsqldev.exe

NZY

3.查看执行计划

SQL> explain plan for

  2  select DISTINCT c.log_id

  3    FROM UCR_CRM1.tab_ren_ykc_02             a,

  4         UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,

  5         uop_crm1.TF_B_RES_SALE_LOG c

  6   WHERE a.sno >= b.start_value

  7     AND a.sno <= b.end_value

  8     AND b.log_id = c.log_id;

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 95865419

 

-------------------------------------------------------------------------------------------------------------------

 

[C1] 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - access("B"."LOG_ID"="C"."LOG_ID")

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   8 - filter("A"."SNO"<="B"."END_VALUE")

   9 - access("A"."SNO">="B"."START_VALUE")

       filter("A"."SNO">="B"."START_VALUE")

 

25 rows selected.

4.查看表中记录数

SQL> select count(*) from UCR_CRM1.tab_ren_ykc_02;

 

  COUNT(*)

----------

     50393

 

SQL> select count(*) from UCR_CEN1.TF_B_VALUECARD_SALE_DETAIL

  2  ;

 

  COUNT(*)

----------

    115229

 

SQL> select count(*) from UCR_CEN1.TF_B_RES_SALE_LOG;

 

  COUNT(*)

----------

    281006

5.查看谓词索引

select DISTINCT c.log_id

  FROM UCR_CRM1.tab_ren_ykc_02             a,

       UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,

       uop_crm1.TF_B_RES_SALE_LOG c

 WHERE a.sno >= b.start_value

   AND a.sno <= b.end_value

   AND b.log_id = c.log_id;

a.sno无索引

b表索引

 alter table UCR_CEN1.TF_B_VALUECARD_SALE_DETAIL

  add constraint PK_TF_B_VALUECARD_SALE_DETAIL primary key (LOG_ID, START_VALUE)

c表索引

alter table UCR_CEN1.TF_B_RES_SALE_LOG

  add constraint PK_TF_B_RES_SALE_LOG primary key (LOG_ID, LOG_MONTH);

6.SQL执行时间

select DISTINCT c.log_id

  FROM UCR_CRM1.tab_ren_ykc_02             a,

       UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,

       uop_crm1.TF_B_RES_SALE_LOG c

 WHERE a.sno >= b.start_value

   AND a.sno <= b.end_value

   AND b.log_id = c.log_id;

 

......

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

 

5851 rows selected.

 

Elapsed: 00:21:26.00[C2] 

7.建立索引

create index UCR_CRM1.test_sno on UCR_CRM1.tab_ren_ykc_02 (sno);

create index UCR_CEN1.test_start on UCR_CEN1.TF_B_VALUECARD_SALE_DETAIL (start_value);

create index UCR_CEN1.test_end on UCR_CEN1.TF_B_VALUECARD_SALE_DETAIL (end_value);[C3] 

8分析表

SQL> execute dbms_stats.gather_table_stats('UCR_CRM1','TAB_REN_YKC_02');

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:02.52

SQL>execute dbms_stats.gather_table_stats('UCR_CEN1','TF_B_VALUECARD_SALE_DETAIL');

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:05.11

SQL> execute dbms_stats.gather_table_stats('UCR_CEN1','TF_B_RES_SALE_LOG');

 

PL/SQL procedure successfully completed.

9HINT操作

select /*+ use_hash(a,b,c)*/DISTINCT c.log_id

  FROM UCR_CRM1.tab_ren_ykc_02             a,

       UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,

       uop_crm1.TF_B_RES_SALE_LOG c

 WHERE a.sno >= b.start_value

   AND a.sno <= b.end_value

   AND b.log_id = c.log_id;

……

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

 

5851 rows selected.

 

Elapsed: 00:00:01.35[C4] 

10.SQL执行时执行计划

select /*+ gather_plan_statistics use_hash(a,b,c)*/DISTINCT c.log_id

  FROM UCR_CRM1.tab_ren_ykc_02             a,

       UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,

       uop_crm1.TF_B_RES_SALE_LOG c

 WHERE a.sno >= b.start_value

   AND a.sno <= b.end_value

   AND b.log_id = c.log_id;

…….

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

2.0100E+15

 

5851 rows selected.

 

Elapsed: 00:00:01.79

SQL>  select sql_id,child_number from v$sql where sql_text like 'select /*+ gather_plan_statistics use_hash(a,b,c)*/DISTINCT c.log_i%';

 

SQL_ID        CHILD_NUMBER

------------- ------------

3nmkyfatckg9j            0

 

Elapsed: 00:00:00.90

SQL> select * from table(dbms_xplan.display_cursor('3nmkyfatckg9j',0,'allstats last'));

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  3nmkyfatckg9j, child number 0

-------------------------------------

select /*+ gather_plan_statistics use_hash(a,b,c)*/DISTINCT c.log_id   FROM UCR_CRM1.tab_ren_ykc_02             a,

UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,       uop_crm1.TF_B_RES_SALE_LOG c  WHERE a.sno >= b.start_value    AND a.sno <= b.end_value    AND

b.log_id = c.log_id

 

Plan hash value: 3730029898

 

-----------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

-----------------------------------------------------------------------------------------------------------------------------------------------------

 

PLAN_TABLE_OUTPUT

[C5] 

11.优化后SQL语句

select /*+ use_hash(a,b,c)*/DISTINCT c.log_id

  FROM UCR_CRM1.tab_ren_ykc_02             a,

       UOP_CRM1.TF_B_VALUECARD_SALE_DETAIL b,

       uop_crm1.TF_B_RES_SALE_LOG c

 WHERE a.sno >= b.start_value

   AND a.sno <= b.end_value

   AND b.log_id = c.log_id;

 

 

三、问题总结:

       SQL谓词操作无索引,在添加索引之后,执行效率仍无提高。查看执行计划时,发现MERGE JOIN操作是导致问题的所在。CBO采用SMJ合并联接操作,由于SORT JOIN返回的row source过大,则又会导致使用过多的rowid在表中查询数据时,数据库性能下降,因为过多的I/O操作。优化后采用HJ哈希联接,2个较大的row source之间(UCR_CEN1.TF_B_VALUECARD_SALE_DETAILUCR_CEN1.TF_B_RES_SALE_LOG)连接时会取得相对较好的效率,在一个 row sourceUCR_CRM1.tab_ren_ykc_02较小时则能取得更好的效率。

 [C1]谓词两边都排序,导至I/O开销过大

 [C2]执行时间

2126

 [C3]此索引为临时测试建立,优化后已删除,请开发人员按此脚本建立索引

 [C4]添加HINT后,执行时间为1

 [C5]采用Hash连接,提高执行效率

 

One Response


    还没有评论!
1  

Leave your comment

请留下您的姓名(*)

请输入正确的邮箱地址(*)

请输入你的评论(*)


感谢开源 © 2016. All rights reserved.&3Q Open Source&^_^赣ICP备15012863^_^
乐于分享共同进步 KreativeThemes