oracle数据库性能优化实务第9讲pga和排序

Oracle数据库性能优化实务第9讲：PGA和排序主讲人：白鳝华章培训网、[]华章培训网Oracle的PGA非共享部分只允许SERVER进程写入包含:Sort

areaSession

informationCursor

stateStack

spaceUserprocessPGAServerprocess排序操作可能产生排序操作的情况ORDER

BY语句DISTINCT语句GROUP

BY语句高级统计操作（比如CUBE）UNION操作SORT

MERGE

JOIN操作排序操作的三种模式OPTIMAL或者CACHED完全使用内存排序性能最佳1-PASS排序操作中产生了一次磁盘读写M-PASS排序操作中产生了多次磁盘读写需要尽可能避免排序模式响应时间内存使用Optimal1

pass排序执行的优化尽可能使用OPTIMAL的排序可以存在少量的1-PASS排序尽可能避免M-PASS排序例外情况：物理内存不足存在特大的排序（比如超过1G）CPU资源紧张PGA手工管理9.0以前版本需要手工设置一系列参数SORT_AREA_SIZEHASH_AREA_SIZEBITMAP_MERGE_AREA_SIZECREATE_BITMAP_AREA_SIZESORT_AREA_RETAINED_SIZEWORKAREA_SIZE_POLICY参数PGA自动管理9.0以后版本WORKAREA_SIZE_POLICY=AUTO设置PGA_AGGREGATE_TARGET不需要设置*_AREA_SIZE参数PGA_AGGREGATE_TARGET设置了PGA的估算值可动态修改系统根据该参数调整PGA的使用对于9i，只有独立服务器模式使用自动PGA管理10G开始，所有模式都是用PGA自动管理PGA_AGGREGATE_TARGET与WORKAREA每个进程的PGA工作区受到两个参数的限制（串行）不得超过PGA_AGGREGATE_TARGET的5%不得超过_SMM_MAX_SIZE定义的大小并行查询中PGA工作区的限制每个PX进程不得超过_SMM_PX_MAX_SIZE/DOP的大小每个进程的所有工作区的总和不能超过_PGA_MAX_SIZE其他合PGA相关的内部参数_smm_advice_enabled：TRUE激活V$PGA_ADVICE_smm_advice_log_size：设置PGAADVICE的历史日志的大小

_smm_auto_max_io_size/_smm_auto_min_io_size_smm_min_size

_smm_trace临时表空间和临时段临时表空间可以提供临时段从7.3.4开始临时表空间中的临时段算法发生了改变每个实例在每个临时表空间中只分配一个临时段临时段在实例运行期间只分配不回收排序操作在临时段中的分配单位是扩展通过SORT

EXTENTPOOL机制（SEP）来管理临时段RAC环境中，如果某个实例临时段不足，而表空间无法扩充扩展，可以从其他实例中偷取临时表空间中的临时段的作用排序、表连接等的临时空间为临时表提供存储临时段不仅仅存在于临时表空间临时表空间-10G新功能好处:不同的会话使用不同的表空间并行查询中使用不同的表空间在数据库级设置多个临时表空间TablespaceTEMP1TablespaceTEMPn…Default

tablespaceEXAMPLETemporary

tablespace

groupTEMP临时表空间100%问题很多DBA发现临时表空间总是处于100%使用分析：数据库的正常行为不需要回收临时段注意监控ALERT

LOGPGA手工管理下的优化要点设置适当的*_area_sizeDBA容易忽略sort_area_size以外的参数SORT_AREA_RETAINED_SIZE不宜设置过小注意ORA-4030，加强物理内存监控参数设置考虑大多数会话的情况，特殊需要在会话级修改*_area_size等参数PGA自动管理下的PGA优化要点设置适当的PGA_AGGREGATE_TARGET参数注意_PGA_MAX_SIZE参数的设置注意PGA实际内存的使用注意物理内存监控，防止ORA-4030自顶向下的问题分析方法，从总体到某个具体的SQLPGA自动管理的监控-第一步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的缓冲区情况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监控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监控脚本（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分析一般来说只需要关注总体情况有些时候PGA总体没有问题，但是个别SQL会导致问题了解某个SQL的PGA使用情况是解决个体问题的第一步PGA监控脚本（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监控脚本（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