鐢‥XPLAIN PLAN 鍒嗘瀽SQL璇?彞:
EXPLAIN PLAN 鏄?竴涓?緢濂界殑鍒嗘瀽SQL璇?彞鐨勫伐鍏?瀹冪敋鑷冲彲浠ュ湪涓嶆墽琛孲QL鐨勬儏鍐典笅鍒嗘瀽璇?彞. 閫氳繃鍒嗘瀽,鎴戜滑灏卞彲浠ョ煡閬揙RACLE鏄??庝箞鏍疯繛鎺ヨ〃,浣跨敤浠?涔堟柟寮忔壂鎻忚〃(绱㈠紩鎵?弿鎴栧叏琛ㄦ壂鎻?浠ュ強浣跨敤鍒扮殑绱㈠紩鍚嶇О.
浣犻渶瑕佹寜鐓т粠閲屽埌澶?浠庝笂鍒颁笅鐨勬?搴忚В璇诲垎鏋愮殑缁撴灉.
EXPLAIN PLAN鍒嗘瀽鐨勭粨鏋滄槸鐢ㄧ缉杩涚殑鏍煎紡鎺掑垪鐨? 鏈?鍐呴儴鐨勬搷浣滃皢琚?渶鍏堣В璇? 濡傛灉涓や釜鎿嶄綔澶勪簬鍚屼竴灞備腑,甯︽湁鏈?灏忔搷浣滃彿鐨勫皢琚??鍏堟墽琛?
NESTED LOOP鏄?皯鏁颁笉鎸夌収涓婅堪瑙勫垯澶勭悊鐨勬搷浣? 姝ｇ‘鐨勬墽琛岃矾寰勬槸妫?鏌ュ?NESTED LOOP鎻愪緵鏁版嵁鐨勬搷浣?鍏朵腑鎿嶄綔鍙锋渶灏忕殑灏嗚?鏈?鍏堝?鐞?
浣跨敤TKPROF宸ュ叿鍒嗘瀽SQL璇?彞:
SQL trace 宸ュ叿鏀堕泦姝ｅ湪鎵ц?鐨凷QL鐨勬?ц兘鐘舵?佹暟鎹?苟璁板綍鍒颁竴涓?窡韪?枃浠朵腑. 杩欎釜璺熻釜鏂囦欢鎻愪緵浜嗚?澶氭湁鐢ㄧ殑淇℃伅,渚嬪?瑙ｆ瀽娆℃暟.鎵ц?娆℃暟,CPU浣跨敤鏃堕棿绛?杩欎簺鏁版嵁灏嗗彲浠ョ敤鏉ヤ紭鍖栦綘鐨勭郴缁?
璁剧疆SQL TRACE鍦ㄤ細璇濈骇鍒? 鏈夋晥
ALTER SESSION SET SQL_TRACE TRUE
璁剧疆SQL TRACE 鍦ㄦ暣涓?暟鎹?簱鏈夋晥, 浣犲繀椤诲皢SQL_TRACE鍙傛暟鍦╥nit.ora涓??涓篢RUE, USER_DUMP_DEST鍙傛暟璇存槑浜嗙敓鎴愯窡韪?枃浠剁殑鐩?綍
鍐嶄娇鐢═KPROF瀵筎RACE鏂囦欢杩涜?鍒嗘瀽鍒嗘瀽缁撴灉鏇村姞鍑嗙‘銆佹竻妤?
鍦⊿QLPLUS 閰嶇疆AUTOTRACE:
SET AUTOTRACE OFF :涓嶈兘鑾峰緱AUTOTRACE鎶ュ憡. 杩欐槸榛樿?鐨?
SET AUTOTRACE ON EXPLAIN :浠呬粎鏄剧ず浼樺寲鍣ㄦ墽琛岃?鍒掔殑AUTOTRACE鎶ュ憡
SET AUTOTRACE ON STATISTICS :浠呬粎鏄剧ずSQL璇?彞鎵ц?鐨勭粺璁＄粨鏋滅殑AUTOTRACE鎶ュ憡
SET AUTOTRACE ON :鍖呮嫭涓婇潰涓ら」鍐呭?鐨凙UTOTRACE鎶ュ憡
SET AUTOTRACE TRACEONLY :涓嶴ET AUTOTRACE ON绫讳技,鎵?鏈夌殑缁熻?鍜屾暟鎹?兘鍦?紝浣嗕笉鍙?互鎵撳嵃
SQL 璋冩暣鐨勭洰鏍?
1锛夊幓鎺変笉蹇呰?鐨勫ぇ鍨嬭〃鐨勫叏琛ㄦ壂鎻忋??
2锛夌紦瀛樺皬鍨嬭〃鐨勫叏琛ㄦ壂鎻忋??
3锛夋牎楠屼紭鍖栫储寮曠殑浣跨敤銆?
4锛夋?楠屼紭鍖栫殑杩炴帴鎶?鏈???
鍦ㄨ?璁″拰寮?鍙戞椂璋冩暣锛?/strong>
褰撹?璁′綘鐨勭郴缁熸椂锛屼娇鐢ㄤ笅鍒椾紭鍖栨?ц兘鐨勫噯鍒欙細
- 娑堥櫎瀹㈡埛鏈猴紡鏈嶅姟鍣ㄥ簲鐢ㄤ腑涓嶅繀瑕佺殑缃戠粶浼犺緭锛屼娇鐢ㄥ瓨鍌ㄨ繃绋嬨??
- 浣跨敤閫傚悎浣犵郴缁熺殑Oracle鏈嶅姟鍣ㄩ?変欢锛堜緥濡傦紝骞惰?鏌ヨ?鎴栧垎甯冨紡鏁版嵁搴擄級銆?
- 闄ら潪浣犵殑搴旂敤鏈夌壒娈婄殑闇?瑕侊紝鍚﹀垯浣跨敤缂虹渷鐨凮racle閿併??
- 鍒╃敤鏁版嵁搴撹?浣忓簲鐢ㄦā鍧楋紝浠ヤ究鑳戒互姣忎釜妯″潡涓哄熀纭?鏉ヨ拷韪??ц兘銆?
- 閫夋嫨浣犵殑鏁版嵁鍧楃殑鏈?浣冲ぇ灏忋??-- 鍘熷垯涓婃潵璇村ぇ涓?浜涚殑鎬ц兘杈冨ソ銆?
- 鍒嗗竷浣犵殑鏁版嵁锛屼娇寰椾竴涓?妭鐐逛娇鐢ㄧ殑鏁版嵁鏈?湴瀛樿串鍦ㄨ?鑺傜偣涓???