Segments by Logical Reads
- Total Logical Reads: 584,021,980
- Captured Segments account for 99.1% of Total
|
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
Logical Reads |
%Total |
|
PROD |
PROD_TBS |
PK_TEST |
|
INDEX |
161,827,056 |
27.71 |
|
PROD |
PROD_TBS |
PK_TEST1 |
|
INDEX |
128,519,568 |
22.01 |
|
PROD |
PROD_TBS |
TEST1 |
|
TABLE |
59,829,760 |
10.24 |
|
PROD |
PROD_TBS |
TEST2 |
|
TABLE |
54,734,688 |
9.37 |
|
PROD |
PROD_TBS |
TEST3 |
|
TABLE |
37,143,040 |
6.36 |
分析后这些SEGMENT都是以上语句涉及到的，因为语句基本同形，我就开始了SQL调优。一切好像都已经找到根源，如果我能减少这些语句逻辑读那问题就解决了。
但是后来我发现问题并非如此，CACHE FUSION传递的是逻辑块，但是引起逻辑读最高的语句不一定就是引起CACHE FUSION的语句。后来我查看了SQL按照CLUSTER WAIT排序
SQL ordered by Cluster Wait Time
|
Cluster Wait Time (s) |
CWT % of Elapsd Time |
Elapsed Time(s) |
CPU Time(s) |
Executions |
SQL Id |
SQL Module |
SQL Text |
|
18.02 |
49.06 |
36.73 |
24.68 |
3 |
|
select ti.serialno itemNo, ti.... |
|
|
14.58 |
48.02 |
30.35 |
18.81 |
2 |
|
select ti.serialno itemNo, ti.... |
|
|
14.18 |
47.90 |
29.60 |
18.39 |
2 |
|
select ti.serialno itemNo, ti.... |
|
|
11.81 |
48.58 |
24.32 |
15.23 |
2 |
|
select ti.serialno itemNo, ti.... |
|
|
10.08 |
52.87 |
19.06 |
9.99 |
1 |
|
select ti.serialno itemNo, ti.... |
|
|
9.22 |
51.46 |
17.92 |
10.29 |
1 |
|
select ti.serialno itemNo, ti.... |
|
|
9.01 |
43.47 |
20.73 |
11.44 |
2 |
|
select ti.serialno itemNo, ti.... |