oracle數(shù)據(jù)庫性能優(yōu)化實(shí)務(wù)第9講pga和排序

Oracle數(shù)據(jù)庫性能優(yōu)化實(shí)務(wù)第9講：PGA和排序主講人：白鱔華章培訓(xùn)網(wǎng)、[]華章培訓(xùn)網(wǎng)Oracle的PGA非共享部分只允許SERVER進(jìn)程寫入包含:Sort

areaSession

informationCursor

stateStack

spaceUserprocessPGAServerprocess排序操作可能產(chǎn)生排序操作的情況ORDER

BY語句DISTINCT語句GROUP

BY語句高級(jí)統(tǒng)計(jì)操作（比如CUBE）UNION操作SORT

MERGE

JOIN操作排序操作的三種模式OPTIMAL或者CACHED完全使用內(nèi)存排序性能最佳1-PASS排序操作中產(chǎn)生了一次磁盤讀寫M-PASS排序操作中產(chǎn)生了多次磁盤讀寫需要盡可能避免排序模式響應(yīng)時(shí)間內(nèi)存使用Optimal1

pass排序執(zhí)行的優(yōu)化盡可能使用OPTIMAL的排序可以存在少量的1-PASS排序盡可能避免M-PASS排序例外情況：物理內(nèi)存不足存在特大的排序（比如超過1G）CPU資源緊張PGA手工管理9.0以前版本需要手工設(shè)置一系列參數(shù)SORT_AREA_SIZEHASH_AREA_SIZEBITMAP_MERGE_AREA_SIZECREATE_BITMAP_AREA_SIZESORT_AREA_RETAINED_SIZEWORKAREA_SIZE_POLICY參數(shù)PGA自動(dòng)管理9.0以后版本W(wǎng)ORKAREA_SIZE_POLICY=AUTO設(shè)置PGA_AGGREGATE_TARGET不需要設(shè)置*_AREA_SIZE參數(shù)PGA_AGGREGATE_TARGET設(shè)置了PGA的估算值可動(dòng)態(tài)修改系統(tǒng)根據(jù)該參數(shù)調(diào)整PGA的使用對(duì)于9i，只有獨(dú)立服務(wù)器模式使用自動(dòng)PGA管理10G開始，所有模式都是用PGA自動(dòng)管理PGA_AGGREGATE_TARGET與WORKAREA每個(gè)進(jìn)程的PGA工作區(qū)受到兩個(gè)參數(shù)的限制（串行）不得超過PGA_AGGREGATE_TARGET的5%不得超過_SMM_MAX_SIZE定義的大小并行查詢中PGA工作區(qū)的限制每個(gè)PX進(jìn)程不得超過_SMM_PX_MAX_SIZE/DOP的大小每個(gè)進(jìn)程的所有工作區(qū)的總和不能超過_PGA_MAX_SIZE其他合PGA相關(guān)的內(nèi)部參數(shù)_smm_advice_enabled：TRUE激活V$PGA_ADVICE_smm_advice_log_size：設(shè)置PGAADVICE的歷史日志的大小

_smm_auto_max_io_size/_smm_auto_min_io_size_smm_min_size

_smm_trace臨時(shí)表空間和臨時(shí)段臨時(shí)表空間可以提供臨時(shí)段從7.3.4開始臨時(shí)表空間中的臨時(shí)段算法發(fā)生了改變每個(gè)實(shí)例在每個(gè)臨時(shí)表空間中只分配一個(gè)臨時(shí)段臨時(shí)段在實(shí)例運(yùn)行期間只分配不回收排序操作在臨時(shí)段中的分配單位是擴(kuò)展通過SORT

EXTENTPOOL機(jī)制（SEP）來管理臨時(shí)段RAC環(huán)境中，如果某個(gè)實(shí)例臨時(shí)段不足，而表空間無法擴(kuò)充擴(kuò)展，可以從其他實(shí)例中偷取臨時(shí)表空間中的臨時(shí)段的作用排序、表連接等的臨時(shí)空間為臨時(shí)表提供存儲(chǔ)臨時(shí)段不僅僅存在于臨時(shí)表空間臨時(shí)表空間-10G新功能好處:不同的會(huì)話使用不同的表空間并行查詢中使用不同的表空間在數(shù)據(jù)庫級(jí)設(shè)置多個(gè)臨時(shí)表空間TablespaceTEMP1TablespaceTEMPn…Default

tablespaceEXAMPLETemporary

tablespace

groupTEMP臨時(shí)表空間100%問題很多DBA發(fā)現(xiàn)臨時(shí)表空間總是處于100%使用分析：數(shù)據(jù)庫的正常行為不需要回收臨時(shí)段注意監(jiān)控ALERT

LOGPGA手工管理下的優(yōu)化要點(diǎn)設(shè)置適當(dāng)?shù)?_area_sizeDBA容易忽略sort_area_size以外的參數(shù)SORT_AREA_RETAINED_SIZE不宜設(shè)置過小注意ORA-4030，加強(qiáng)物理內(nèi)存監(jiān)控參數(shù)設(shè)置考慮大多數(shù)會(huì)話的情況，特殊需要在會(huì)話級(jí)修改*_area_size等參數(shù)PGA自動(dòng)管理下的PGA優(yōu)化要點(diǎn)設(shè)置適當(dāng)?shù)腜GA_AGGREGATE_TARGET參數(shù)注意_PGA_MAX_SIZE參數(shù)的設(shè)置注意PGA實(shí)際內(nèi)存的使用注意物理內(nèi)存監(jiān)控，防止ORA-4030自頂向下的問題分析方法，從總體到某個(gè)具體的SQLPGA自動(dòng)管理的監(jiān)控-第一步fromv$pgastatSQL>select

*NAMEVALUE

UNITaggregatePGAtarget

parameterbytesaggregatePGAautotargetbytesglobal

memorybound 1073741824

bytestotal

PGAinuse788483072bytestotal

PGAallocated1638338560bytesmaximum

PGA

allocated6631036928bytestotal

freeable

PGA

memory429850624bytesprocess

count545max

processes

count1199PGA

memory

freed

back

to

OS10902709927936bytestotal

PGA

used

for

auto

workareas11700224bytesmaximum

PGA

used

for

autoworkareas1505202176bytestotal

PGA

used

for

manualworkareas0bytesmaximum

PGA

used

for

manualworkareas537600bytesover

allocation

count0bytes

processed73457958117376bytesextra

bytes

read/written875484558336bytescache

hitpercentage98.82percentpute

count(total)449299查看PGA的緩沖區(qū)情況SQL>

select

name,round(value/1024/1024,1)

as

Mb

from

v$pgastat

where

unit

=

'bytes';NAME

MBaggregate

PGA

target

parameteraggregate

PGA

auto

targetglobal

memorybound1536013157.41024total

PGAinuse753.5total

PGAallocated1567.5maximum

PGA

allocated6323.8total

freeable

PGA

memory411.1PGA

memory

freed

back

to

OS10400491.7total

PGA

used

for

auto

workareas12.9maximum

PGA

used

for

autoworkareas1435.5total

PGA

used

for

manualworkareas0maximum

PGA

used

for

manualworkareas.5bytes

processed70074058.7extra

bytes

read/written835427監(jiān)控V$SQL_WORKAREA_HISTOGRAMSELECT

LOW_OPTIMAL_SIZE/1024/1024low_mb,(HIGH_OPTIMAL_SIZE+1)/1024/1024

high_mb,optimal_executions,onepass_executions,multipasses_executionsFROM

v$sql_workarea_histogramWHERE

total_executions

!=0and

(low_optimal_size/1024/1024

>=

8

or

total_executions

>optimal_executions);例子LOW_MBHIGH_MB

OPTIMAL_EXECUTIONS

ONEPASS_EXECUTIONS

MULTIPASSES_EXECUTIONS.015625.031250120.0625.125.25.58032029120121.68E+084024260798598104808161632046032641087256405721432128256151536428882509625651211620438102448191481024204892182258184096819201708192163840260603276865536020PGA監(jiān)控腳本（3）select

operation_type

as

type,

policy,

sid,

round(active_time/1000000,2)

as

a_sec,round(work_area_size/1024/1024,2)

aswsize,

round(expected_size/1024/1024,2)

as

exp,round(actual_mem_used/1024/1024,2)

as

act,round(max_mem_used/1024/1024,2)

as

max,number_passes

as

passes,

round(tempseg_size/1024/1024,2)

as

temp

fromv$sql_workarea_active;TYPEPOLICSIDA_SECWSIZEEXPACTMAXPASSESTEMPGROUP

BY

(HASH)AUTO418.063.063.16000GROUP

BY

(HASH)AUTO27841892.623.263.261.191.190CONNECT-BY

(SORT)AUTO993155.9814.4714.4712.9612.960WINDOW

(SORT)AUTO107942802.89.48.48.48.480CONNECT-BY

(SORT)AUTO993155.476.736.736.576.570GROUP

BY

(HASH)AUTO33941851.663.263.361.211.210WINDOW

(SORT)AUTO31241982.71.48.48.48.480GROUP

BY

(HASH)AUTO30341262.053.263.261.171.170GROUP

BY

(HASH)AUTO187375.153.263.371.151.150GROUP

BY

(HASH)AUTO171.013.063.16000GROUP

BY

(HASH)AUTO3079409.753.263.371.21.20WINDOW

(SORT)AUTO99141867.741.031.031.031.030分析具體SQL的情況PGA分析一般來說只需要關(guān)注總體情況有些時(shí)候PGA總體沒有問題，但是個(gè)別SQL會(huì)導(dǎo)致問題了解某個(gè)SQL的PGA使用情況是解決個(gè)體問題的第一步PGA監(jiān)控腳本（4）colopformat

a15

trunccol

policy

format

a8

trunccol

last

format

a10

truncset

numwidth

8set

line

200select

operation_type

as

op,operation_id

as

id,

policy,round(estimated_optimal_size/1024/1024,2)ase_opt,round(estimated_onepass_size/1024/1024,2)

as

e_one,round(last_memory_used/1024/1024,2)

as

l_mem,

last_execution

as

last,total_executions

as

tot,

optimal_executions

as

opt,

onepass_executions

as

one,multipasses_executions

asmult,round(active_time/1000000,2)

as

sec,

round(max_tempseg_size/1024/1024,2)

as

tmp_m,round(last_tempseg_size/1024/1024,2)

as

tmp_Lfrom

v$sql_workarea

where

sql_id='37qjh5yuha3x9';PA監(jiān)控腳本（4）-例子OPE_OPTE_ONEL_MEM

LASTTOT

OPT

ONE

MULTSEC

TMP_M

TMP_LSORT

(v2)

GROUP

BY

(HASH)59.92136.512.638.6953.26

OPTIMAL20.1

1

PASS1110010

289.530

382.3128128查找存在問題的SQLselect

sql_id,operation_type

as

op,

operation_id

as

id,round(estimated_optimal_size/1024/1024,2)

as

e_opt,round(estimated_onepass_size/1024/1024,2)

as

e_one,round(last_memory_used/1024/1024,2)

as

l_mem,Last_execution

as

last,total_executions

as

tot,

optimal_executions

as

opt,onepass_executions

as

one,multipasses_executions

as

mult,round(active_time/1000000,2)

as

sec,round(max_tempseg_size/1024/1024,2)

as

tmp_m,round(last_tempseg_size/1024/1024,2)

as

tmp_Lfrom

v$sql_workareawheremax_tempseg_size

is

not

nullorder

by

max_tempseg_size

desc;例子IDE_OPTE_ONEL_MEM

LASTTOT

OPTONE

MULTSECTMP_MTMP_L0

2515.020

481.010

2957.310

8726.810

3071.570

376.890

206.210

196.070

270.710

39.380

288.28SQL_ID

OPc940m2fhfdhqb

HASH-JOIN9n9h9vbfsutgf

GROUP

BY(SORT)33hqyxbdddcj6

GROUP

BY(HASH)czmtvcbdamr8v

HASH-JOIN71mcs1y2pguza

HASH-JOIN47m5kq4hw5f79

GROUP

BY(SORT)gk97kydxcdhf9

GROUP

BY(HASH)7n1hu6k66a369

HASH-JOINgs4aa3r85upzc

GROUP

BY(HASH)cmfzdunmgr1a1

GROUP

BY(HASH)3j6f35gyq3syd

GROUP

BY(HASH)0tzdnch3vc8tv

WINDOW

(SORT)0tzdnch3vc8tv

WINDOW

(SORT)2

20481

1147.911

1130.54

1412.227

975.0813

324.852

726.9113

967.371

352.851

278.171

321.922

355.882

323.8833.92 1123.07

1PASS14.09 97.62

1

PASS28.91 142.9

1

PASS34.26 1251.05

1

PASS19.88 432.83

1

PASS7.01 97.62

1

PASS22.47 126.81

1

PASS16.18 1108.92

1

PASS14.63 88.09

1

PASS14.23 36.15

1

PASS15.43 42.42

1

PASS6.1 97.62

1

PASS5.83 97.62

1

PASS1

01

01

01

01

01

01

01

01

01

01

02

01

01111111111121029026.580

4853.2352017921088768768448448448384320320320320352017921088768768448448448384320320320320利用STATSPACK/AWR分析PGA（1）%

Blocks

changed

per

Read:Rollback

per

transaction

%:8.940.81Recursive

Call

%:Rows

per

Sort:37.65122.30Instance

Efficiency

Percentages

(Target

100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Buffer

Nowait

%:Buffer

HitLibrary

HitExecute

to

Parse

%:Parse

CPU

to

Parse

Elapsd

%:99.84%:

99.46%:

100.0560.229.02Redo

NoWait

%:In-memory

Sort

%:Soft

Parse

%:Latch

Hit

%:%

Non-Parse

CPU:99.98100.00100.0099.4399.94利用STATSPACK/AWR分析PGA（2）PGA

Aggr

Summary DB/Inst:

SFOSS/sfoss2

Snaps:12838-12839->

PGA

cache

hit

%

-

percentage

of

W/A

(WorkArea)

data

processed

onlyin-memoryPGA

Cache

Hit

% W/A

MB

Processed Extra

W/A

MB

Read/Written98.4

302,582

4,894利用STATSPACK/AWR分析PGA（3）PGA

Aggr Auto

PGA PGA

MemTarget(M)

Target(M)

Alloc(M)W/A

PGAUsed(M)-BE10,24010,2407,7077,1273,278.36,938.0218.41,099.36.7

100.015.8

100.0%PGA

%Auto

%ManW/A

W/A W/A

Global

MemMem

Mem

Mem

Bound(K).0

1,048,570.0

1,048,570利用STATSPACK/AWR分析PGA（4）LowOptimalHighOptimalTotal

ExecsOptimal

Execs1-PassExecsM-PassExecs2K4K2,097,2682,097,2680064K128K7,4857,48500128K256K5,2145,21400256K512K4,7554,75500512K1024K394,849394,8391001M2M4,6954,695002M4M77653823804M8M31713917808M16M21888130016M32M1487474032M64M4400利用STATSPACK/AWR分析PGA（5）W/A

MBEstd

Extra