1.闲着无聊在客户一套较为重要的系统中提取了一个4天的AWR,发现下面这条SQL产生最多的物理读,四天运行了159次。平均每次物理读1G左右。SQL运行时间三分钟左右。
SQL:
SELECT DECODE(B.DH, 'MAIN', :1, B.DH) DH, COUNT(DISTINCT(A.PROCESSID)) VALUE,
B.MC COMPANYNAME FROM FMIS3000.WF_ACTIVITIES A, (SELECT DH, MC FROM FMIS3000.XTDW WHERE 1=1 )
B WHERE A.ACTTYPE = 1 AND TRIM(A.DH) = TRIM(B.DH) GROUP BY B.DH, B.MC;
2.我查看了执行计划:
PLAN_TABLE_OUTPUT
Plan hash value: 2953189885
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 191K| 18M| | 300K (1)| 01:10:12 |
| 1 | SORT GROUP BY | | 191K| 18M| 4006M| 300K (1)| 01:10:12 |
|* 2 | HASH JOIN | | 37M| 3516M| | 45650 (1)| 00:10:40 |
| 3 | TABLE ACCESS FULL| XTDW | 528 | 18480 | | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| WF_A