oracle骞惰鏌ヨ甯歌闂 -杞浇
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 STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=328)
1 0 TABLE ACCESS* (FULL) OF 'PAR_T' (Cost=1 Card=82 Bytes=328) :Q6000
1 PARALLEL_TO_SERIAL Select /*+ NO_EXPAND ROWID(A1) */ A1."A" FRO
M "PAR_T" PX_GRANULE(0, BLOCK_RANGE,
濡傛灉璇ヨ〃娌℃湁鎸囧畾骞惰搴?鍙互鍦ㄦ煡璇㈡椂鐢╤int 瀹炵幇,渚?
select /*+ full(t) parallel(t,5) */ * from your_table t where ...;
4.涓轰粈涔堟湰璇ュ苟琛屾墽琛岀殑鏌ヨ娌℃湁骞惰鎵ц鍛?
绯荤粺鐨勫苟琛屽害鐢眕arallel_max_servers 鍐冲畾,濡傛灉瀹冪殑骞惰搴︿负5.濡傛灉鏈変竴涓苟琛屽害涓?鐨勬煡璇㈠湪璺?閭ｄ箞绯荤粺鍦ㄨ繖鏉℃煡璇㈣繍琛屽畬鎴愬墠鏄笉鑳藉啀璺戝苟琛屾煡璇㈢殑,璇ュ苟琛屾煡璇㈠皢浼氫互闈炲苟琛屾柟寮忚繍琛?
5濡傛灉鏈夊苟琛屽害浣庝簬绯荤粺鏈€澶у苟琛屾暟鐨勬煡璇㈠湪璺?閭ｆ帴涓嬫潵鐨勫苟琛屾煡璇細鎬庝箞璺戝憿?
When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers.
If there are only 2 slaves available then we use these.
If there is only 1 slave available then we go serial
If there are none available then we use serial.
If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial
6.璁惧畾parallel_max_servers 澶氬ぇ涓哄ソ?
鍦ㄥCPU鐨勭幆澧冧腑,涓€鑸妸CPU-1鎴朇PU鐨勬暟閲忓仛涓渶澶у苟琛屾暟,鍥犱负骞惰鏌ヨ杩愯鏃惰繕闇€瑕佷竴涓繘绋嬪崗璋冨悇骞惰杩涚▼.瀵逛簬鍗旵PU娌′粈涔堝ソ璇寸殑.
7.骞惰鏌ヨ鑳芥彁楂樼郴缁熺殑鎬ц兘鍚?
骞惰鏌ヨ杩愯鏃?寰堝鏄撲細浣挎満鍣ㄨ繍琛屽湪楂樿礋鑽蜂笅,浠ょ郴缁熷鍏跺畠浜嬪姟鐨勫鐞嗘椂闂村ぇ澶у姞闀?骞惰鏌ヨ涓€鑸€傚悎鍦ㄩ潪涓氬姟楂樺嘲鍊间汉宸ユ墽琛?骞朵笉閫傚悎鍦ㄧ▼搴忎腑鎸囧畾杩愯骞惰鏌ヨ.
PINNER:
骞惰涓嶇瓑浜庡揩閫燂紝浠呬粎鏄€傚悎鍦ㄦ暟鎹粨搴撶幆澧冿紝浣庝笟鍔¤姹備笌浣庡苟鍙戞搷浣滅殑鏃跺€?
鍏稿瀷鐨凮LTP绯荤粺锛屽鏋滄垜浠殑绯荤粺锛屾槸缁濆涓嶅厑璁稿苟琛屾煡璇㈠嚭鐜扮殑銆?
涓汉鍊惧悜锛?
鍦ㄤ娇鐢∣racle Parallel Server鏃跺€欙紝灏介噺璁剧疆Parallel_min_percent涓洪潪0鍊硷紝鍘熷洜鏄洜涓猴紝Parallel_min_percent=0鐨勬儏鍐典笅锛屽綋slaves涓嶈冻鏃讹紝鏌ヨ浼氫覆琛屽寲鐨勮繘琛岋紝杩欐牱浣垮緱浜嗘煡璇㈢殑閫熷害鏇存參锛汸arallel_min_percent<>0鎯呭喌涓嬶紝褰搒laves涓嶈冻鏃禣racle浼氭姤鍑洪敊璇紝杩欐牱姣旇緝瀹规槗娓呮鎴戜滑闇€瑕佺殑slaves涓嶈冻銆?
鍦∣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 STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=328)
1 0 TABLE ACCESS* (FULL) OF 'PAR_T' (Cost=1 Card=82 Bytes=328) :Q6000
1 PARALLEL_TO_SERIAL Select /*+ NO_EXPAND ROWID(A1) */ A1."A" FRO
M "PAR_T" PX_GRANULE(0, BLOCK_RANGE,
濡傛灉璇ヨ〃娌℃湁鎸囧畾骞惰搴?鍙互鍦ㄦ煡璇㈡椂鐢╤int 瀹炵幇,渚?
select /*+ full(t) parallel(t,5) */ * from your_table t where ...;
4.涓轰粈涔堟湰璇ュ苟琛屾墽琛岀殑鏌ヨ娌℃湁骞惰鎵ц鍛?
绯荤粺鐨勫苟琛屽害鐢眕arallel_max_servers 鍐冲畾,濡傛灉瀹冪殑骞惰搴︿负5.濡傛灉鏈変竴涓苟琛屽害涓?鐨勬煡璇㈠湪璺?閭ｄ箞绯荤粺鍦ㄨ繖鏉℃煡璇㈣繍琛屽畬鎴愬墠鏄笉鑳藉啀璺戝苟琛屾煡璇㈢殑,璇ュ苟琛屾煡璇㈠皢浼氫互闈炲苟琛屾柟寮忚繍琛?
5濡傛灉鏈夊苟琛屽害浣庝簬绯荤粺鏈€澶у苟琛屾暟鐨勬煡璇㈠湪璺?閭ｆ帴涓嬫潵鐨勫苟琛屾煡璇細鎬庝箞璺戝憿?
When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers.
If there are only 2 slaves available then we use these.
If there is only 1 slave available then we go serial
If there are none available then we use serial.
If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial
6.璁惧畾parallel_max_servers 澶氬ぇ涓哄ソ?
鍦ㄥCPU鐨勭幆澧冧腑,涓€鑸妸CPU-1鎴朇PU鐨勬暟閲忓仛涓渶澶у苟琛屾暟,鍥犱负骞惰鏌ヨ杩愯鏃惰繕闇€瑕佷竴涓繘绋嬪崗璋冨悇骞惰杩涚▼.瀵逛簬鍗旵PU娌′粈涔堝ソ璇寸殑.
7.骞惰鏌ヨ鑳芥彁楂樼郴缁熺殑鎬ц兘鍚?
骞惰鏌ヨ杩愯鏃?寰堝鏄撲細浣挎満鍣ㄨ繍琛屽湪楂樿礋鑽蜂笅,浠ょ郴缁熷鍏跺畠浜嬪姟鐨勫鐞嗘椂闂村ぇ澶у姞闀?骞惰鏌ヨ涓€鑸€傚悎鍦ㄩ潪涓氬姟楂樺嘲鍊间汉宸ユ墽琛?骞朵笉閫傚悎鍦ㄧ▼搴忎腑鎸囧畾杩愯骞惰鏌ヨ.
PINNER:
骞惰涓嶇瓑浜庡揩閫燂紝浠呬粎鏄€傚悎鍦ㄦ暟鎹粨搴撶幆澧冿紝浣庝笟鍔¤姹備笌浣庡苟鍙戞搷浣滅殑鏃跺€?
鍏稿瀷鐨凮LTP绯荤粺锛屽鏋滄垜浠殑绯荤粺锛屾槸缁濆涓嶅厑璁稿苟琛屾煡璇㈠嚭鐜扮殑銆?
涓汉鍊惧悜锛?
鍦ㄤ娇鐢∣racle Parallel Server鏃跺€欙紝灏介噺璁剧疆Parallel_min_percent涓洪潪0鍊硷紝鍘熷洜鏄洜涓猴紝Parallel_min_percent=0鐨勬儏鍐典笅锛屽綋slaves涓嶈冻鏃讹紝鏌ヨ浼氫覆琛屽寲鐨勮繘琛岋紝杩欐牱浣垮緱浜嗘煡璇㈢殑閫熷害鏇存參锛汸arallel_min_percent<>0鎯呭喌涓嬶紝褰搒laves涓嶈冻鏃禣racle浼氭姤鍑洪敊璇紝杩欐牱姣旇緝瀹规槗娓呮鎴戜滑闇€瑕佺殑slaves涓嶈冻銆?