銆愯浆杞姐€慜racle缁戝畾鍙橀噺銆佺‖瑙ｆ瀽銆佽蒋瑙ｆ瀽
杞浇鍦板潃锛歨ttp://cjjwzs.iteye.com/blog/1010263

Oracle鍦ㄦ墽琛孲QL璇彞鏃讹紝鏅亶瀛樺湪浠ヤ笅鍑犱釜姝ラ锛?
褰揝QL璇彞棣栨鎵ц锛孫racle灏嗙‘璁よ鍙ヨ鍙ョ殑璇硶鏄惁姝ｇ‘锛堣娉曡В鏋怱yntax parse锛夊苟杩涗竴姝ョ‘璁よ鍙ョ浉鍏宠〃鍜屽垪鐨勫瓨鍦ㄦ€х瓑鍥犵礌(璇箟瑙ｆ瀽semantic parse)浠ュ強浼樺寲鍣ㄥ喅瀹氭墽琛岃鍒掔瓑姝ラ銆傛暣涓繃绋嬬О涔嬩负纭В鏋愶紝纭В鏋愭秷鑰楀ぇ閲忕殑CPU鏃堕棿鍜岀郴缁熻祫婧愩€傜‖瑙ｆ瀽杩囧浼氭湁鏁堥檷浣庣郴缁熸€ц兘銆?
鑻ヤ箣鍓嶅凡杩涜杩囩‖瑙ｆ瀽锛屼笖瑙ｆ瀽鍚庣殑鍒嗘瀽鏍戝拰鎵ц璁″垝浠嶅瓨鍦ㄤ簬鍏变韩姹犱腑锛屽垯鍚屾牱鐨凷QL浠呴渶瑕佽蒋瑙ｆ瀽銆傝蒋瑙ｆ瀽灏嗚緭鍏ョ殑SQL璇彞杞崲涓哄搱甯屼唬鐮侊紝鍚屽叡浜睜鍐呭搱甯岄摼琛ㄤ笂鐨勫凡鏈夎褰曡繘琛屽姣旓紝鎵惧嚭瀵瑰簲鐨勬父鏍囦俊鎭紝浣跨敤宸叉湁鐨勬墽琛岃鍒掓墽琛屻€?
缁戝畾鍙橀噺,灏嗗疄闄呯殑鍙橀噺鍊间唬鍏QL璇彞涓€?
鎵цSQL璇彞锛屾煡璇㈣鍙ュ皢杩斿洖缁撴灉闆嗐€?
涓嶄娇鐢ㄧ粦瀹氬彉閲忕殑SQL璇彞锛孫racle鏃犳硶灏嗗畠浠涓虹浉鍚岀殑锛屽浠ヤ笅涓ゅ彞璇彞锛?
select * from emp where empno=1234
select * from emp where empno=5678
鍥犱负鑷敱鍙橀噺鐨勪笉鍚岋紝Oracle璁や负浠ヤ笂鏄?鍙ヤ笉鍚岀殑璇彞锛屽垯褰撶涓€鏉¤纭В鏋愬悗锛岀浜屾潯SQL鎵ц鏃朵粛鏃犳硶閬垮厤纭В鏋愩€傚疄闄呭湪浠ヤ笂涓嶄娇鐢ㄧ粦瀹氬彉 閲忕殑鎯呭喌涓紝鍙鑷敱鍙橀噺鏈夋墍鏀瑰彉鍒欓渶瑕佷竴娆＄‖瑙ｆ瀽銆傝繖鏄己鐑堝缓璁娇鐢ㄧ粦瀹氬彉閲忕殑涓昏鍘熷洜锛屼娇鐢ㄧ粦瀹氬彉閲忕殑璇彞鍙橀噺鐨勫疄闄呭€间粎鍦⊿QL鎵ц鐨勬渶鍚庨樁娈佃浠?鍏ャ€傚浠ヤ笅璇彞锛?
select * from emp where empno=:x
璇ヨ鍙ヤ娇鐢ㄧ粦瀹氬€?x鏇夸唬鑷敱鍙橀噺锛屽湪搴旂敤涓鍙ュ彲鑳戒互棰勭紪璇戞垨鏅€氱紪璇戠殑鏂瑰紡瀛樺湪锛屼粎鍦ㄦ墽琛岄樁娈典唬鍏ュ彉閲忓€硷紝澶氭鎵ц浠呴渶瑕佷竴娆＄‖瑙ｆ瀽锛岃緝涓嶄娇鐢ㄧ粦瀹氬彉閲忔儏鍐垫€ц兘澶уぇ鎻愬崌銆?
鍚屾椂杩囧鐨勭‖瑙ｆ瀽杩樹細寮曞彂鍏变韩姹犵鐗囪繃澶氱殑闂銆傚洜涓烘瘡褰撻渶瑕佺‖瑙ｆ瀽涓€涓猄QL鎴栬€匬LSQL璇彞鏃讹紝閮介渶瑕佷粠shared pool涓垎閰嶄竴鍧楄繛缁殑绌洪棽绌洪棿鏉ュ瓨鏀捐В鏋愮粨鏋溿€侽racle棣栧厛鎵弿shared pool鏌ユ壘绌洪棽鍐呭瓨锛屽鏋滄病鏈夊彂鐜板ぇ灏忔濂藉悎閫傜殑绌洪棽chunk锛屽氨鏌ユ壘鏇村ぇ鐨刢hunk锛屽鏋滄壘鍒版瘮璇锋眰鐨勫ぇ灏忔洿澶х殑绌洪棽chunk锛屽垯灏嗗畠鍒嗚锛屽 浣欓儴鍒嗙户缁斁鍒扮┖闂插垪琛ㄤ腑銆傚洜涓鸿繃澶氱殑纭В鏋愬姞鍓т簡鍐呭瓨娈靛垎閰嶇殑闇€姹傦紝杩欐牱灏变骇鐢熶簡纰庣墖闂銆傜郴缁熺粡杩囬暱鏃堕棿杩愯鍚庯紝灏变細浜х敓澶ч噺灏忕殑鍐呭瓨纰庣墖銆傚綋璇锋眰鍒?閰嶄竴涓緝澶х殑鍐呭瓨鍧楁椂锛屽敖绠hared pool鎬荤┖闂茬┖闂磋繕寰堝ぇ锛屼絾鏄病鏈変竴涓崟鐙殑杩炵画绌洪棽鍧楄兘婊¤冻闇€瑕併€傝繖鏃讹紝灏卞彲鑳戒骇鐢?ORA-4031閿欒銆?
閫氬父鎴戜滑鍙互閫氳繃浠ヤ笅SQL璇彞灏嗙郴缁熶腑闈炵粦瀹氬彉閲忕殑璇彞鎵惧嚭锛?
SELECT substr(sql_text,1,40) 鈥淪QL鈥?
count(*) ,
sum(executions) 鈥淭otExecs鈥?
FROM v$sqlarea
WHERE executions < 5 鈥?璇彞鎵ц娆℃暟
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30 鈥?鎵€鏈夋湭鍏变韩鐨勮鍙ョ殑鎬荤殑鎵ц娆℃暟
ORDER BY 2;
浠ヤ笂璇彞鍦ㄥ疄闄呬娇鐢ㄤ腑substr鍑芥暟鎴彇鍒扮殑瀛楃涓查暱搴﹂渶瑕佽涔庡疄闄呮儏鍐典簣浠ュ彉鍖栥€?
瀵逛簬闈炵粦瀹氬彉閲忎笖鐭湡鍐呮棤娉曚慨鏀圭殑搴旂敤锛孫racle瀛樺湪鍙傛暟cursor_sharing鍙互鏀瑰杽鍏惰〃鐜般€俢ursor_sharing榛樿涓?exact,瀵逛娇鐢ㄨ嚜鐢卞彉閲忕殑璇彞涓嶅仛棰濆澶勭悊锛涘綋璁句负force鏃讹紝闈炵粦瀹氬彉閲忕殑SQL璇彞琚繘涓€姝ュ鐞嗕互杈惧埌鍏变韩SQL鐨勭洰鐨勶紝浣嗕互涓婂鐞嗘楠ゅ悓鏍疯 娑堣€椾竴瀹氱殑CPU鏃堕棿锛涘綋璁句负similar鏃讹紝鑻ユ暟鎹簱瀛樺湪璇彞鐩稿叧缁熻淇℃伅鍒欏叾琛ㄧ幇濡俥xact,鑻ユ棤缁熻淇℃伅鍒欒〃鐜颁负force銆?cursor_sharing鍙傛暟鏄疧racle閽堝鏃犳硶淇敼鐨勯潪缁戝畾鍙橀噺搴旂敤鎵€鎻愬嚭鐨勬姌涓柟妗堬紝浣哻ursor_sharing涓篺orce鍊兼椂瀛樺湪涓€ 瀹歋QL寮曞彂bug鎴栬鍙ユ棤鏁堢殑鎯呭喌锛屼笖棰濆鐨勫鐞嗘搷浣滃悓鏍烽渶瑕佹秷鑰椾竴瀹氶噺鐨凜PU鏃堕棿鍜岀郴缁熻祫婧愩€傛晠閽堝绯荤粺鎬ц兘鐨勬渶浼樻柟妗堝線寰€鏄洿鎺ヤ慨鏀瑰簲鐢ㄤ唬鐮侊紝浣?鐢ㄧ粦瀹氬彉閲忕壒鎬с€?