转自：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
-------------------------------------------------------------------------------------------------------------------
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.
|
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. |
9加HINT操作
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.
|
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 |
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_DETAIL和UCR_CEN1.TF_B_RES_SALE_LOG）连接时会取得相对较好的效率，在一个 row source（UCR_CRM1.tab_ren_ykc_02）较小时则能取得更好的效率。
One Response