|
不仅仅是全表扫描了哦,我觉得问题是尽量的减少物理读。
1、查看ORDER BY 物理读语句
SQL ordered by Reads
Total Disk Reads: 12,115,756
Captured SQL account for 70.1% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
2,152,599 10 215,259.90 17.77 77.00 534.69 7sxmjqjnabnsg SELECT A1.*, A2.PROMISE_DATE, ...
1,405,676 8 175,709.50 11.60 57.60 403.41 cra8wvghugr7n SELECT DC.*, CN.PROJECT_NO FRO...
896,882 4 224,220.50 7.40 33.89 206.60 fb03srz25zs7n SELECT DC.*, CN.PROJECT_NO FRO...
541,074 1 541,074.00 4.47 9.98 38.97 99dcd25zktzz0 select a.job_no, a.project_nam...
540,855 1 540,855.00 4.46 9.89 40.85 3nz6jp0f15q7c select a.job_no, a.project_nam...
423,997 2 211,998.50 3.50 15.67 83.92 3wd0cpyb5v009 select * from ( select d.jo...
374,129 2 187,064.50 3.09 15.39 112.23 f3nq78dm2db75 select substr(c.Delivery, 5, 2...
212,094 2 106,047.00 1.75 7.64 47.18 72yh203fqxq3s select substr(d.item_value, 5,...
207,056 1 207,056.00 1.71 7.93 55.34 aw0mv2kxaxduk select t.job_no, t.seq_no, t.i...
205,591 1 205,591.00 1.70 7.68 82.00 66yn37twvjg8j PL/SQL Developer SELECT A1.*, A2.PROMISE_DATE, ...
205,017 2 102,508.50 1.69 8.15 55.09 ffc152cz3443y select substr(c.Delivery, 5, 2...
189,527 2 94,763.50 1.56 8.17 62.98 bt82s0qrzh299 select substr(deli.Delivery, 5...
180,942 2 90,471.00 1.49 6.71 46.08 394sgda9hwyrk SELECT DC.*, CN.PROJECT_NO FRO...
126,636 2 63,318.00 1.05 4.13 20.55 b5hfbh2vmjv8p SELECT TB1.*, TB5...
123,465 1 123,465.00 1.02 3.36 19.25 gav5rrrcfujhj PL/SQL Developer select * from (SELECT D.JOB_NO...
2、查看order by 物理读 对象确定哪些段物理读比较严重
Segments by Physical Reads
Total Physical Reads: 12,115,756
Captured Segments account for 97.8% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
DCDBA DCDAT01 DCJBSC TABLE 8,270,637 68.26
CNDBA CNDAT01 CNSPEC TABLE 2,530,273 20.88
SDDBA SDDAT01 SDSPEC TABLE 362,987 3.00
DCDBA DCIDX01 PK_DCJBSC INDEX 309,202 2.55
ENDBA ENDAT01 ENPMMS TABLE 165,473 1.37
明显这里解决68%这个对象问题可能得到很好的效果。
3、看看SGA 建议,是否需要调高SGA
SGA Target Size (M) SGA Size Factor Est DB Time (s) Est Physical Reads
608 0.25 5,524,895 4,497,036,202
1,216 0.50 4,429,554 4,065,786,942
1,824 0.75 3,429,430 3,671,770,163
2,432 1.00 2,328,035 3,237,607,057
3,040 1.25 1,807,720 3,032,990,291
3,648 1.50 1,281,118 2,825,459,679
4,256 1.75 966,601 2,614,367,699
4,864 2.00 962,876 2,614,367,699 |
|