]
FOR
Gi¶i thÝch:
SET: Gi¸ trÞ ®Æc biÖt cho cét STATEMENT_ID trong c©y ph©n tÝch lÖnh. Gi¸ trÞ ngÇm ®Þnh lµ null.
INTO: Tªn b¶ng chøa kÕt qu¶ cña c©y ph©n tÝch lÖnh. B¶ng nµy ph¶i ®îc t¹o ra tríc khi sö dông c©u lÖnh EXPLAIN PLAN.
Phô lôc B Tèi u c©u lÖnh trªn view Trong phÇn mÒm tra cøu v¨n b¶n ph¸p qui
PhÇn mÒm Tra cøu v¨n b¶n ph¸p qui dïng ®Ó tra cøu v¨n b¶n cña UBND c¸c tØnh vµ cña V¨n phßng chÝnh phñ ®· ban hµnh b»ng WEB trªn m¹ng diÖn réng cña V¨n phßng chÝnh phñ. Mçi v¨n b¶n cã mét m· v¨n b¶n, mét chñ ®Ò, mét m· ®Þa ph¬ng, cã néi dung trÝch yÕu vµ néi dung toµn v¨n. Mét v¨n b¶n khi ®îc ph¸t hµnh (gäi lµ v¨n b¶n triÓn khai) th× cã thÓ ®îc dùa trªn mét sè v¨n b¶n kh¸c gäi lµ v¨n b¶n c¨n cø. Trong CSDL ë mçi ®Þa ph¬ng ®Òu c¸c View lµ hîp (UNION ALL) cña c¸c cã b¶ng chøa v¨n b¶n kh«ng mËt vµ v¨n b¶n mËt ®ång thêi cã ¶nh cña c¸c v¨n b¶n trªn trung ¬ng (c¸c Snapshot).
HiÖn t¹i CSDL cña tØnh NghÖ An (m· ®Þa ph¬ng lµ NAN) cã 4110 v¨n b¶n vµ tra cøu néi dung cña mét v¨n b¶n víi hÖ ch¬ng tr×nh cha tèi u th× thêi gian ®¸p øng lµ 41.23 gi©y (Server IBM). Sau khi tiÕn hµnh ph©n tÝch c©u lÖnh th× ®iÓm bÊt hîp lý ë chç do View cã chøa to¸n tö tËp hîp UNION ALL vµ chØ cã c¸c ®iÒu kiÖn AND trong mÖnh ®Ò WHERE trong khi Oracle chØ tèi u c©u lÖnh nh vËy víi ®iÒu kiÖn OR (xem phÇn tèi u c©u lÖnh trªn View), do vËy ph¶i chuyÓn c©u lÖnh truy xuÊt trªn View b»ng viÖc kÕt hîp c©u lÖnh vµo View (truy xuÊt trùc tiÕp víi b¶ng råi hîp c¸c kÕt qu¶). C©u lÖnh sau khi tèi u cã thêi gian ®¸p øng lµ 2.36 gi©y (b»ng 5.7%). Díi ®©y lµ néi dung c©u lÖnh t×m ra v¨n b¶n c¨n cø v¨n b¶n triÓn khai víi m· v¨n b¶n vµ m· ®Þa ph¬ng ®ang tra cøu, néi dung c©y ph©n tÝch lÖnh, thêi gian thùc hiÖn ®îc tÝnh to¸n bëi hÖ thèng.
C©u lÖnh t×m v¨n b¶n c¨n cø khi cha tèi u lµ 21.03 gi©y, c©u lÖnh sau khi tèi u cã thêi gian ®¸p øng lµ 1.26 gi©y (b»ng 4.8%). C©u lÖnh t×m v¨n b¶n triÓn khai khi cha tèi u lµ 18.99 gi©y, c©u lÖnh sau khi tèi u cã thêi gian ®¸p øng lµ 0.49 gi©y (b»ng 2.5%).
-
C©u lÖnh chän ra v¨n b¶n c¨n cø khi cha tèi u:
SELECT V_CCTHDP.MA_CC, V_CCTHDP.MA_DP_CC, V_VBCC.SO, V_VBCC.KY_HIEU, V_VBCC.NGAY_PH,
V_VBCC.TRICH_YEU, V_CCTHDP.MA_DP_VB, V_CCTHDP.MA_VB
FROM V_CCTHDP V_CCTHDP, V_VBTHDP V_VBCC, V_S_DMDP V_S_DMDP1, V_S_DMLVB V_S_DMLVB1,
V_S_DMCD V_S_DMCD1
WHERE V_VBCC.MA_VB = V_CCTHDP.MA_CC and V_VBCC.MA_DP = V_CCTHDP.MA_DP_CC
and V_S_DMDP1.MA_DP = V_VBCC.MA_DP and V_S_DMLVB1.MA_LOAI_VB = V_VBCC.MA_LOAI_VB
and V_S_DMCD1.MA_CHU_DE = V_VBCC.MA_CHU_DE
and (V_CCTHDP.MA_DP_VB = 'NAN') and (V_CCTHDP.MA_VB = 25);
Thêi gian thùc hiÖn
call count cpu elapsed disk query current rows
------- ----------- -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 2 0.05 4.66 0 0 3 0
Fetch 1 0.66 16.33 356 429 2462 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.75 21.03 356 429 2465 0
C©y ph©n tÝch lÖnh
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 MERGE JOIN
0 SORT (JOIN)
0 MERGE JOIN
0 SORT (JOIN)
0 MERGE JOIN
4110 SORT (JOIN)
4110 MERGE JOIN
56 SORT (JOIN)
56 TABLE ACCESS (FULL) OF 'SNAP$_S_DMCD'
4110 SORT (JOIN)
4110 VIEW OF 'V_VBTHDP'
4110 SORT (UNIQUE)
4110 UNION-ALL
5 TABLE ACCESS (FULL) OF 'VBDP'
4105 TABLE ACCESS (FULL) OF 'SNAP$_S_VBKM'
0 TABLE ACCESS (FULL) OF 'SNAP$_S_VBM'
0 SORT (JOIN)
0 VIEW OF 'V_CCTHDP'
0 SORT (UNIQUE)
0 UNION-ALL
0 TABLE ACCESS (BY ROWID) OF 'CCDP'
1 INDEX (RANGE SCAN) OF 'IDX_CCDP_VB_DPVB'
(NON-UNIQUE)
0 TABLE ACCESS (BY ROWID) OF 'SNAP$_S_CC'
1 INDEX (RANGE SCAN) OF 'IDX_S_CC_VB_DPVB'
(NON-UNIQUE)
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'SNAP$_S_DMLVB'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'SNAP$_S_DMDP'
-
C©u lÖnh t×m v¨n b¶n c¨n cø sau khi ®· tèi u:
SELECT V_CCTHDP.MA_CC, V_CCTHDP.MA_DP_CC, VBDP.SO, VBDP.KY_HIEU, VBDP.NGAY_PH,
VBDP.TRICH_YEU, V_CCTHDP.MA_DP_VB, V_CCTHDP.MA_VB
FROM V_CCTHDP V_CCTHDP, VBDP VBDP, V_S_DMDP V_S_DMDP1,
V_S_DMLVB V_S_DMLVB1, V_S_DMCD V_S_DMCD1
WHERE (V_CCTHDP.MA_VB = 26) and (V_CCTHDP.MA_DP_VB = 'NAN')
and VBDP.MA_VB = V_CCTHDP.MA_CC and VBDP.MA_DP = V_CCTHDP.MA_DP_CC
and VBDP.MA_DP = V_S_DMDP1.MA_DP and VBDP.MA_LOAI_VB = V_S_DMLVB1.MA_LOAI_VB
and VBDP.MA_CHU_DE = V_S_DMCD1.MA_CHU_DE
UNION
SELECT V_CCTHDP.MA_CC, V_CCTHDP.MA_DP_CC, VBTW.SO, VBTW.KY_HIEU, VBTW.NGAY_PH,
VBTW.TRICH_YEU, V_CCTHDP.MA_DP_VB, V_CCTHDP.MA_VB
FROM V_CCTHDP V_CCTHDP, SNAP$_S_VBKM VBTW, V_S_DMDP V_S_DMDP1,
V_S_DMLVB V_S_DMLVB1, V_S_DMCD V_S_DMCD1
WHERE (V_CCTHDP.MA_VB = 26) and (V_CCTHDP.MA_DP_VB = 'NAN')
and VBTW.MA_VB = V_CCTHDP.MA_CC and VBTW.MA_DP = V_CCTHDP.MA_DP_CC
and VBTW.MA_DP = V_S_DMDP1.MA_DP and VBTW.MA_LOAI_VB = V_S_DMLVB1.MA_LOAI_VB
and VBTW.MA_CHU_DE = V_S_DMCD1.MA_CHU_DE
Thêi gian thùc hiÖn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.12 0.16 2 0 2 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 0.51 1.10 464 8506 447 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.63 1.26 466 8506 449 0
C©y ph©n tÝch lÖnh
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 UNION-ALL
0 MERGE JOIN
0 SORT (JOIN)
0 MERGE JOIN
0 SORT (JOIN)
0 MERGE JOIN
5 SORT (JOIN)
5 NESTED LOOPS
56 TABLE ACCESS (FULL) OF 'SNAP$_S_DMCD'
5 TABLE ACCESS (BY ROWID) OF 'VBDP'
61 INDEX (RANGE SCAN) OF 'VBDP_MA_CDE_I'
(NON-UNIQUE)
0 SORT (JOIN)
0 VIEW OF 'V_CCTHDP'
0 SORT (UNIQUE)
0 UNION-ALL
0 TABLE ACCESS (BY ROWID) OF 'CCDP'
1 INDEX (RANGE SCAN) OF 'IDX_CCDP_VB_DPVB'
(NON-UNIQUE)
0 TABLE ACCESS (BY ROWID) OF 'SNAP$_S_CC'
1 INDEX (RANGE SCAN) OF 'IDX_S_CC_VB_DPVB'
(NON-UNIQUE)
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'SNAP$_S_DMLVB'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'SNAP$_S_DMDP'
0 MERGE JOIN
0 SORT (JOIN)
0 MERGE JOIN
0 SORT (JOIN)
0 MERGE JOIN
4105 SORT (JOIN)
4105 NESTED LOOPS
56 TABLE ACCESS (FULL) OF 'SNAP$_S_DMCD'
4105 TABLE ACCESS (BY ROWID) OF 'SNAP$_S_VBKM'
4161 INDEX (RANGE SCAN) OF 'S_VBKM_MA_CDE_I'
(NON-UNIQUE)
0 SORT (JOIN)
0 VIEW OF 'V_CCTHDP'
0 SORT (UNIQUE)
0 UNION-ALL
0 TABLE ACCESS (BY ROWID) OF 'CCDP'
1 INDEX (RANGE SCAN) OF 'IDX_CCDP_VB_DPVB'
(NON-UNIQUE)
0 TABLE ACCESS (BY ROWID) OF 'SNAP$_S_CC'
1 INDEX (RANGE SCAN) OF 'IDX_S_CC_VB_DPVB'
(NON-UNIQUE)
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'SNAP$_S_DMLVB'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'SNAP$_S_DMDP'
-
C©u lÖnh t×m ra v¨n b¶n triÓn khai khi cha tèi u:
SELECT V_CCTKDP.MA_CC, V_CCTKDP.MA_DP_CC, V_CCTKDP.MA_DP_VB, V_CCTKDP.MA_VB, V_VBTK.SO,
V_VBTK.KY_HIEU, V_VBTK.NGAY_PH, V_VBTK.TRICH_YEU
FROM V_CCTHDP V_CCTKDP, V_VBTHDP V_VBTK, V_S_DMDP V_S_DMDP2,
V_S_DMLVB V_S_DMLVB2, V_S_DMCD V_S_DMCD2
WHERE V_VBTK.MA_VB = V_CCTKDP.MA_VB and V_VBTK.MA_DP = V_CCTKDP.MA_DP_VB
and V_S_DMDP2.MA_DP = V_VBTK.MA_DP and V_S_DMLVB2.MA_LOAI_VB = V_VBTK.MA_LOAI_VB
and V_S_DMCD2.MA_CHU_DE = V_VBTK.MA_CHU_DE
and (V_CCTKDP.MA_CC = 25) and (V_CCTKDP.MA_DP_CC = 'NAN');
Thêi gian thùc hiÖn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 2 0.01 3.99 0 0 3 0
Fetch 1 0.69 14.96 404 429 2470 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.74 18.99 404 429 2473 0
-
C©u lÖnh t×m v¨n b¶n triÓn khai sau khi ®· tèi u
SELECT V_CCTKDP.MA_CC, V_CCTKDP.MA_DP_CC, V_CCTKDP.MA_DP_VB,
V_CCTKDP.MA_VB, VBDP.SO, VBDP.KY_HIEU, VBDP.NGAY_PH, VBDP.TRICH_YEU
FROM V_CCTHDP V_CCTKDP, VBDP VBDP, V_S_DMDP V_S_DMDP2,
V_S_DMLVB V_S_DMLVB2, V_S_DMCD V_S_DMCD2
WHERE (V_CCTKDP.MA_CC = 26) and (V_CCTKDP.MA_DP_CC = 'NAN')
and VBDP.MA_VB = V_CCTKDP.MA_VB and VBDP.MA_DP = V_CCTKDP.MA_DP_VB
and VBDP.MA_DP = V_S_DMDP2.MA_DP and VBDP.MA_LOAI_VB = V_S_DMLVB2.MA_LOAI_VB
and VBDP.MA_CHU_DE = V_S_DMCD2.MA_CHU_DE
UNION
SELECT V_CCTKDP.MA_CC, V_CCTKDP.MA_DP_CC, V_CCTKDP.MA_DP_VB,
V_CCTKDP.MA_VB, VBTW.SO, VBTW.KY_HIEU, VBTW.NGAY_PH, VBTW.TRICH_YEU
FROM V_CCTHDP V_CCTKDP, SNAP$_S_VBKM VBTW, V_S_DMDP V_S_DMDP2,
V_S_DMLVB V_S_DMLVB2, V_S_DMCD V_S_DMCD2
WHERE (V_CCTKDP.MA_CC = 26) and (V_CCTKDP.MA_DP_CC = 'NAN')
and VBTW.MA_VB = V_CCTKDP.MA_CC and VBTW.MA_DP = V_CCTKDP.MA_DP_VB
and VBTW.MA_DP = V_S_DMDP2.MA_DP and VBTW.MA_LOAI_VB = V_S_DMLVB2.MA_LOAI_VB
and VBTW.MA_CHU_DE = V_S_DMCD2.MA_CHU_DE ;
Thêi gian thùc hiÖn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.05 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 0.41 0.44 2 8506 447 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.46 0.49 2 8506 447 0
Chia sẻ với bạn bè của bạn: