oracle骞惰鏌ヨ甯歌闂 --杞浇
鍦∣LAP鐜锛屼互鍒╃敤澶氱殑CPU鍜屽唴瀛樿祫婧愭潵鍔犻€熷鐞嗘暟鎹紝涔熷嵆oracle鐨勫苟琛屾煡璇€傚崟涓狢PU鍚屼竴鏃跺埢鍙兘鏈嶅姟涓€涓繘绋嬶紝濡傛灉鏈夊涓狢PU锛屾彁楂楥PU鍒╃敤寰嬶紝灏卞彲浠ュ悓鏃惰繍琛屽涓繘绋嬨€備篃灏辨槸鍘熸潵鍗曚釜杩涚▼澶勭悊鐨勫彉鎴愬涓繘绋嬪苟琛屽鐞嗗姞閫熸墽琛屾椂闂淬€傚苟琛屾墽琛屽彧鏄湪鍏ㄨ〃澶勭悊鎴栬€呭垎鍖哄強鍦ㄥ垎鍖鸿〃涓墽琛屾湰鍦扮储寮曟椂鐢ㄥ埌銆備笅闈㈡儏鍐典細鐢ㄥ埌骞惰鏌ヨ锛?
鍏ㄨ〃鎵弿銆?rebuild index銆乽pdate (鍏ㄨ〃鎴栧垎鍖鸿〃)銆乮nsert鐨勫苟琛屽瓙鏌ヨ銆佹湰鍦扮储寮曚娇鐢ㄣ€佹壒閲忔彃鍏ワ紝璞QLLDR銆佸垱寤轰复鏃惰〃
姣斿鎴戜滑鎵ц
select /*+ parallel(c1 ,2) */
...
from customers c1
order by ...process a process b
fetch rows from fetch rows from
customers customers
|| ||
|| ||
^^ ^^
process c process d
sort rows(a-k) sort rows(l-z)
combine rows
||
return result set
杩欓噷鎴戜滑鐪嬪埌杩欎釜杩涚▼鍒嗘暎鎴?涓繘绋嬶紝鎺掑簭涓悇璐熻矗a-k鍜宭-z锛岃繖鏍峰氨鍙互骞惰澶勭悊
鎴戜滑鍦ㄥ垎鍖鸿〃涓紝涔熷彲浠ョ敤涓€涓苟琛屼粠杩涚▼瀵瑰簲涓€涓垎鍖鸿〃濡傛灉浣犵殑骞惰搴︽槸3锛岄偅涔堜綘鍙兘灏辨瘮鏅€氱殑鎵ц閫熷害鎻愰珮3鍊?
娉ㄦ剰鍦ㄥ崟CPU涓嬶紝濡傛灉浣跨敤骞惰锛岄偅涔堝氨鍙兘閫犳垚鎬ц兘涓嬮檷锛岃€屼笖涔熻璁剧疆鍚堥€傜殑骞惰搴?
骞惰閫傚悎瀵逛簬闀挎椂闂磋繍琛岀殑璇彞
鍦╫ltp鍙兘骞朵笉閫傚悎浣跨敤骞惰澶勭悊锛屽洜涓轰簨鍔″苟鍙戞瘮杈冨锛屾瘡涓敤鎴烽兘瑕佷娇鐢–PU锛孋PU鐨勮礋杞芥湰鏉ュ氨姣旇緝楂?
鎵€浠ュ苟琛屽鐞嗚繕鏄瘮杈冮€傚悎olap涓殑鎵归噺瀵煎叆,sqlldr,mis report鍜宱ltp涓璻ebuild index銆?

 

1銆佺湅鐪嬪苟琛岄€変欢鏄惁瀹夎
Select * FROM V$OPTION
where parameter like 'Parallel%';
鐪嬬湅
Parallel execution鏄笉鏄疶RUE

2銆佸鏋滄槸TRUE锛屾墽琛岃鍙ュ悗鏌ョ湅
select * from V$pq_sesstat;
where name like '%Parallelized';

濡傛灉Queries Parallelized>>0灏辫鏄庢槸鎵ц浜嗗苟琛?

3銆佸彲浠ュ己鍒朵娇鐢≒ARALLEL锛屽拰CPU鏁伴噺鏃犲叧锛屼笉杩囧湪鍗曚釜CPU涓嬩娇鐢ㄥ苟琛屾病鏈変粈涔堝ソ澶?
alter session force parallel query锛?

4銆佷綘鏄€庝箞鐭ラ亾璇彞娌℃湁浣跨敤PARALLEL?
濡傛灉浣犵敤EXPLAIN 锛岄偅涔堟湁涓や釜鑴氭湰鐪嬫墽琛岃鍒?
UTLXPLS.UTLXPLP鍓嶄竴涓槸鐪嬩覆琛岃鍒掔殑锛屽悗涓€涓墠鑳界湅鍒板苟琛岃鍒掞紝
濡傛灉浣犱娇鐢⊿ET AUTOTRACE锛岄偅涔堜綘濡傛灉鐪嬪埌P->S锛岄偅涔堣鏄庤鍒掑凡缁忔槸骞惰鐨勪簡銆?.涓庡苟琛屾煡璇㈡湁鍏崇殑鍙傛暟鏈夊摢浜?
parallel_adaptive_multi_user boolean
鍚敤鎴栫鐢ㄤ竴涓嚜閫傚簲绠楁硶锛屾棬鍦ㄦ彁楂樹娇鐢ㄥ苟琛屾墽琛屾柟寮忕殑澶氱敤鎴风幆澧冪殑鎬ц兘銆傞€氳繃鎸夌郴缁熻礋鑽疯嚜
鍔ㄩ檷浣庤姹傜殑骞惰搴︼紝鍦ㄥ惎鍔ㄦ煡璇㈡椂瀹炵幇姝ゅ姛鑳姐€傚綋 PARALLEL_AUTOMATIC_TUNING = TRUE 鏃讹紝鍏舵晥鏋滄渶浣炽€?
TRUE | FALSE 濡傛灉 PARALLEL_AUTOMATIC_TUNING = TRUE锛屽垯璇ュ€间负 TRUE锛涘惁鍒欎负 FALSE
parallel_automatic_tuning boolean
濡傛灉璁剧疆涓?TRUE锛孫racle 灏嗕负鎺у埗骞惰鎵ц鐨勫弬鏁扮‘瀹氶粯璁ゅ€笺€傞櫎浜嗚缃鍙傛暟澶栵紝浣犺繕蹇呴』涓?
绯荤粺涓殑琛ㄨ缃苟琛屾€с€?
TRUE | FALSE FALSE
parallel_execution_message_size integer
鎸囧畾骞惰鎵ц (骞惰鏌ヨ銆丳DML銆佸苟琛屾仮澶嶅拰澶嶅埗) 娑堟伅鐨勫ぇ灏忋€傚鏋滃€煎ぇ浜?2048 鎴?4096锛屽氨闇€
瑕佹洿澶х殑鍏变韩姹犮€傚鏋?PARALLEL_AUTOMATIC_TUNING =TRUE锛屽皢鍦ㄥぇ瀛樺偍姹犱箣澶栨寚瀹氭秷鎭紦鍐插尯銆?
2148 - 鏃犵┓澶с€?濡傛灉 PARALLEL_AUTOMATIC_TUNING 涓?FALSE锛岄€氬父鍊间负 2148锛涘鏋?
PARALLEL_AUTOMATIC_TUNING 涓?TRUE 锛屽垯鍊间负 4096 (鏍规嵁鎿嶄綔绯荤粺鑰屽畾)銆?
parallel_max_servers integer
鎸囧畾涓€涓緥绋嬬殑骞惰鎵ц鏈嶅姟鍣ㄦ垨骞惰鎭㈠杩涚▼鐨勬渶澶ф暟閲忋€傚鏋滈渶瑕侊紝渚嬬▼鍚姩鏃跺垎閰嶇殑鏌ヨ鏈?
鍔″櫒鐨勬暟閲忓皢澧炲姞鍒拌鏁伴噺銆?
0 -256 鐢?CPU_COUNT, PARALLEL_AUTOMATIC_TUNING 鍜?PARALLEL_ADAPTIVE_MULTI_USER 纭畾
parallel_min_percent integer
鎸囧畾骞惰鎵ц瑕佹眰鐨勭嚎绋嬬殑鏈€灏忕櫨鍒嗘瘮銆傝缃鍙傛暟锛屽彲浠ョ‘淇濆苟琛屾墽琛屽湪娌℃湁鍙敤鐨勬伆褰撴煡璇粠
灞炴椂锛屼細鏄剧ず涓€涓敊璇秷鎭紝骞朵笖璇ユ煡璇細鍥犳鑰屼笉浜堟墽琛屻€?
parallel_min_servers integer
鎸囧畾涓哄苟琛屾墽琛屽惎鍔ㄤ緥绋嬪悗锛孫racle 鍒涘缓鐨勬煡璇㈡湇鍔″櫒杩涚▼鐨勬渶灏忔暟閲忋€?
0 - PARALLEL_MAX_SERVERS銆?
parallel_threads_per_cpu integer
璇存槑涓€涓?CPU 鍦ㄥ苟琛屾墽琛岃繃绋嬩腑鍙鐞嗙殑杩涚▼鎴栫嚎绋嬬殑鏁伴噺锛屽苟浼樺寲骞惰鑷€傚簲绠楁硶鍜岃礋杞藉潎琛＄畻
娉曘€傚鏋滆绠楁満鍦ㄦ墽琛屼竴涓吀鍨嬫煡璇㈡椂鏈夎秴璐熻嵎鐨勮抗璞★紝搴斿噺灏忚鏁板€?
浠讳綍闈為浂鍊笺€?鏍规嵁鎿嶄綔绯荤粺鑰屽畾 (閫氬父涓?2)

2.褰撳墠鏃跺埢鏈夐偅浜涘苟琛屾煡璇㈠湪璺?
14:13:46 SQL> desc v$px_session
鍚嶇О 鏄惁涓虹┖? 绫诲瀷
----------------------------------------- -------- ------------------
SADDR RAW(4)
SID NUMBER
SERIAL# NUMBER
QCSID NUMBER
QCSERIAL# NUMBER
QCINST_ID NUMBER
SERVER_GROUP NUMBER
SERVER_SET NUMBER
SERVER# NUMBER
DEGREE NUMBER
REQ_DEGREE NUMBER


3.鎬庝箞鎵嶈兘璁╂煡璇㈡湁骞惰鎵ц?
濡傛灉寤鸿〃鏃舵寚瀹氫簡骞惰搴?渚?
Create TABLE LI2.PAR_T
(
a VARCHAR2 (5)
)
PARALLEL 5;
閭ｄ箞瀵硅琛ㄥ仛鍏ㄨ〃鎵弿鏃跺氨浼氬苟琛?
14:26:05 SQL> set autot on
14:26:11 SQL> select * from par_t;
鏈€夊畾琛?
宸茬敤鏃堕棿: 00: 00: 00.02
Execution Plan
----------------------
0 Select