Oracle Memory Architecture (III)

"Deep analysis--eygle" study notes

1.2.3 PGA Management:

[Email protected] Sql>show parameter area_size NAME                                 TYPE                   VALUE---------------------------------------------------------- ------------------------------bitmap_merge_area_size               integer                1048576create_bitmap_area_size              Integer                8388608hash_area_size                       integer                131072sort_area_size                       integer                65536workarea_size_ Policy                 String                 AUTO

Automated SQL Execution Memory management (Automated SQL Execution Memories Management), also known as automated PGA Management, uses this new feature to automatically manage and adjust the SQL memory area under an overall PGA usage limit, This greatly simplifies the work of the DBA, while also improving the performance of the database.

To achieve automatic PGA Management, Oracle introduces several new initialization parameters:

(1) Pga_aggregate_target This parameter specifies that the maximum PGA memory can be used for all session totals. This parameter can be changed dynamically, the range of values from 10M to (4096g-1) bytes.

(2) Workarea_size_policy This parameter is used to switch the PGA Memory Auto-management feature, which has two options: Auto and MANUAL, when set to auto, the database uses the automatic PGA management function, when set to MANUAL, You will still use the manual management method.

By default, the Workarea_size_policy parameter is set to Auto.

[Email protected] Sql>show parameterworkarea_size_polic NAME                                 TYPE                   VALUE------------------------------------------------------------------workarea_size_policy                 string                 AUTO  

It is also important to note that there are different categories of automatic PGA Management in different versions:

(1) in Oracle9i, thepga_aggregate_target parameter is valid only for dedicated connections in private server mode (Dedicatedserver), but not for shared server connections

(2) starting from oracle10g Pga_aggregate_target the connection to the dedicated server and the shared server are in effect at the same time.

Setting of the 1.2.4 parameter and memory allocation

Workspace performance is expected to achieve the following objectives:

Workarea Execution-optimal >= 90%

Workarea Execution-multipass = 0%

PGA Performance indicator script for production systems:

Select NAME,      VALUE,       value/decode (      (select SUM (VALUE) from                         v$sysstat                        WHERE NAME Like ' Workarea-executions% '),                       0,                       NULL,                       (SELECT SUM (VALUE) from                          v$sysstat                         WHERE NAME Like ' Workarea executions% ')) pct  fromv$sysstat WHERE namelike ' Workarea executions% ';

<strong>[email protected] Sql>select description,dest fromx$messages where description like ' SQL memory% '; DESCRIPTION                                        DEST------------------------------------------------------------SQL Memory Management calculation                  CKPT 15:26:04 [email protected] sql></strong>

[Email protected] Sql>select * from V$pgastat;name VALUE UNIT----------------------------------- ---------------------------------------Aggregate PGA target parameter 146800640 bytesaggregate PGA Auto Targe                           T 22099968 bytesglobal memory bound 29360128 bytestotal PGA inuse                     122360832 bytestotal PGA Allocated 144107520 bytesmaximum PGA allocated                                    163160064 bytestotal freeable PGA memory 11141120 bytesprocess Count 32max processes Count 36PGA memory freed back to OS 374669312 bytestotal PGA u Sed for auto workareas 0 bytesmaximum PGA used for auto Workareas 6313984 bytestotal PGA used for       Manual Workareas 0 bytesmaximum PGA used for manual workareas 0 bytesover allocation count                      0bytes processed 232915968 Bytesextra bytes Read/written 0 bytescache hits percentage percentrecompute count (total) 6 00419 rows selected.

1.2.6 PGA Tuning Recommendations

With the introduction of the automatic PGA adjustment feature, Oracle also introduces the corresponding dynamic performance view for optimization recommendations, and the PGA optimization recommendations are provided through V$pga_target_advice and V$pga_target_advice_histogra. The V$pga_target_advice view provides information such as PGA hit rate and Overalloc in different settings by evaluating different PGA settings.

15:32:23 [email protected] sql>select pga_target_for_estimate/1024/1024 pgamb, PGA_TARGET_FACTOR, 15:37:08 2 E     Std_pga_cache_hit_percentage, estd_overalloc_count15:37:08 3 from V$pga_target_advice; Pgamb pga_target_factor estd_pga_cache_hit_percentage estd_overalloc_count---------------------------------------        -------------------------------------17.5.125 85 4                            35.25 85 4 70.5                 85 4 105.75 85 4 140                           1 100 0) 168 1.2               100 0 196 1.4 100 0 224 1.6 100 0       252 1.8 100 0 280 2       100 0 420 3 100 0                           560 4 100 0 840 6 100 0 1120 8 100 0

V$pga_target_advice_histogram Views can be evaluated by sampling the size of different workspaces ? For statistical information

for analysis use. Several of the important fields are:

(1)low_optimal_size-histogram Evaluation range OPTIMAL lower limit (bytes)

(2) OPTIMAL Upper Limit (bytes) inhigh_optimal_size-histogram evaluation interval

(3) estimated OPTIMAL execution times inestd_optimal_executions-histogram evaluation interval

(4) estimated ONEPASS execution times inestd_onepass_executions-histogram evaluation interval

(5) estimated multipass execution times inestd_multipasses_executions-histogram evaluation interval

(6) estimated total number of executions inestd_total_executions-histogram evaluation interval

SELECT pga_target_factor factor,       low_optimal_size/1024 Low,       ROUND (high_optimal_size/1024) high,       Estd_optimal_executions estd_opt,       estd_onepass_executions estd_op,       estd_multipasses_executions estd_mp,       estd_total_executions estd_exec  From V$pga_target_advice_histogram WHERE pga_target_factor = 0.25 and   estd_total_executions > 0;

</pre><pre name= "code" class= "HTML" >

<span style= "font-family:arial, Helvetica, Sans-serif;" ></span>

<span style= "font-family:arial, Helvetica, Sans-serif;" > Heap Name= "PGA heap"  desc=0xbaf3ca0   --Note here </span></strong></span>

 EXTENTSZ=0X20C0 alt=216 het=32767 rec=0 flg=3 opc=2 parent= (nil) owner= (nil) nex= (nil) xsz=0xfff0 heap= (nil) Fl2=0x60,nex = (nil), Dsxvers=1, dsxflg=0x0 dsx firstext=0xf7fa7720extent 0 addr=0x7fadf7c72010 Chunk 7fadf7c72020 sz= 65504 F  Ree "" EXTENT 1 addr=0x7fadf7d00010 Chunk 7fadf7d00020 sz= 28920 Perm "Perm" alo=9384 Chunk 7fadf7d07118 sz= 7656 free "" Chunk 7fadf7d08f00 sz= 4224 Freeab Le "diag PGA" Ds=0x7fadf82157e0 Chunk 7fadf7d09f80 sz= 4224 freeable "diag PGA" ds=0x7fadf82157                                                                                                                              E0/heap Chunk 7fadf7fd28d0 sz= 7608 Perm "Perm" alo=7608 Chunk 7fadf7fd4688 sz= 4  0 Free "" Chunk 7fadf7fd46b0 sz= freeable "Dbgdiniteventgr" Chunk 7fadf7fd4700 sz= 184 freeable "SDBGRF:IOSB" Chunk 7fadf7fd47b8 sz= freeable "Skgfzctx" Chunk 7fadf7fd4808 sz= 376           Freeable "PLS CCA hpdesc" EXTENT addr=0x7fadf7fcd860 Chunk 7fadf7fcd870 sz= 20512 Perm "Perm "Alo=20512extent addr=0x7fadf7fc9958 Chunk 7fadf7fc9968 sz= 16072 Perm" Perm "alo=16072e Xtent addr=0x7fadf7fc7868 Chunk 7fadf7fc7878 sz= 3240 Perm "Perm" alo=3240 Chunk 7FADF           7fc8520 sz= 3184 Perm "Perm" alo=3184 Chunk 7fadf7fc9190 sz= 1088 Perm "Perm       "alo=1088 Chunk 7fadf7fc95d0 sz= 168 perm" Perm "alo=168 Chunk 7fadf7fc9678 sz= "Chunk 7fadf7fc96a8 sz= freeable" Koh-kghu callh "Chunk 7FADF7FC      96F0 sz= 568 freeable "Joxp heap" EXTENT addr=0x7fadf7fc5778 Chunk 7fadf7fc5788 sz= 4848 Perm "Perm" alo=4848 Chunk 7fadf7fc6a78 sz=-freeable "Krbabrpgarespms" Chunk 7fadf7fc6c30 sz=-freeable "Krbabrpgareqctx" Chunk 7fadf7fc6c68 sz= 424 freeable "krbabrpgareqmsg" Chunk 7fadf7fc6e10 sz= 260           0 freeable "Kjztprq struct" EXTENT addr=0x7fadf7fc3688 Chunk 7fadf7fc3698 sz= 7720 Perm "Perm "alo=7720 Chunk 7fadf7fc54c0 sz= freeable" REGHEAPD_KDLWPG "Chunk 7fadf7fc5560 sz=   Reeable "Kcfis gctx" Chunk 7fadf7fc55b8 sz= [freeable Krbabrpgastbyre] EXTENT Chunk 7fadf7fb5e38 sz= 55328 Perm [perm] alo=55328extent addr=0x7fadf7fb3d38 Chunk 7FA       Df7fb3d48 sz= 4888 Perm "Perm" alo=4888 Chunk 7fadf7fb5060 sz= 2992 recreate "Ksfq heap "Latch= (Nil)

Further, a specific data storage structure can be dumped out (DS), such as the above DS dcd00c0 Its space use size of sz= 14036, first to the space address conversion:

Select To_number (' baf3ca0 ', ' xxxxxxxxxx ') from dual15:50:33 [email protected] sql>/to_number (' baf3ca0 ', ' Xxxxxxxxxx ')---------------------------------                        19603369615:50:33 [email protected] sql>

Use the following command to dump the contents of the fixed address space:

Alter session SET Events ' immediate trace name heapdump_addr level 1, addr n ';

The above calculated address space can be dumped by the following command:

sql> ALTER sessionset EVENTS ' immediate trace name heapdump_addr Level 1, addr 196033696 ';

Session altered.

To get the dump file script:

SELECT A.value | | B.symbol | | C.instance_name | | ' _ora_ ' | | D.spid | |       '.  TRC ' Trace_file from  (select VALUE from v$parameter WHERE NAME = ' user_dump_dest ') A,       (select SUBSTR (VALUE,-6, 1) Symbol from          v$parameter         WHERE NAME = ' user_dump_dest ') b,       (SELECT instance_name from v$instance) C,       ( SELECT spid from          v$session s, v$process p, v$mystat m         WHERE s.paddr = p.addr and           s.sid = M.sid and           m.stat istic# = 0) D;

Check this process dump file to find the following Heap address information and space allocation:

[[email protected] ~] $grep Heap/u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_6443.trc<strong ><span style= "color: #ff0000;" >heap DUMP Heap Name= "PGA Heap" desc=0xbaf3ca0</span></strong> parent= (nil) owner= (nil) nex= (nil) xsz=     0xfff0 heap= (nil) Chunk 7fadf7fc96f0 sz= 568 freeable "Joxp heap" Chunk 7fadf7fc54c0 sz= 160     Freeable "REGHEAPD_KDLWPG" Chunk 7fadf7fb5060 sz= 2992 recreate "Ksfq heap" latch= (nil) Chunk 7FADF7FB5C10 sz= freeable "Ksfq heap descr" Chunk 7fadf7fb0790 sz= 232 freeable "Iovecheapd_k  DLW "Chunk 7fadf7fb0878 sz= 304 freeable" BCHEAPD_KDLWPGA "Chunk 7fadf7fb09a8 sz= 208 freeable "SIOHEAPD_KDLWPG" Chunk 7fadf7fb0ad8 sz= freeable "Ksz PGA Subheap" total heap size = 499952<str Ong><span style= "color: #ff0000;" >heap DUMP Heap name= "Top call heap" desc=0xbaf94e0</span></strong> pareNt= (nil) owner= (nil) nex= (nil) Xsz=0xfff8 heap= (nil) Chunk 7fadf7ccb7d8 sz= 1032 recreate "Callheap"     Latch= (nil) Chunk 7fadf7cceb78 sz= 4224 freeable "callheap" ds=0xbaf85c0 Chunk 7fadf7ccfbf8 sz= 1032 recreate "Callheap" latch= (nil) total heap size = 524096

<strong><span style= "color: #ff0000;" >heap DUMP heap name= "Top Uga Heap" desc=0xbaf9700</span></strong> parent= (nil) owner= (nil) nex= (nil) Xsz =0xfff8 heap= (nil) Chunk 7fadf7c62018 sz= 65512 freeable "Session heap" DS=0X7FADF7CD5BE0 Chunk 7FADF 7d10018 sz= 65512 freeable "Session heap" DS=0X7FADF7CD5BE0 Chunk 7fadf7cf0018 sz= 65512 freeable " Session heap "DS=0X7FADF7CD5BE0 Chunk 7fadf7ce0068 sz= 65432 Recreate" Session heap "latch= (nil) Total Heap size = 262048<strong><span style= "color: #ff0000;" >heap DUMP Heap Name= "PGA Heap" desc=0xbaf3ca0</span></strong> parent= (nil) owner= (nil) nex= (nil) xsz= 0x1fff0 heap= (nil) Chunk 7fadf7fc96f0 sz= 568 freeable "Joxp heap" Chunk 7fadf7fc54c0 sz= 16     0 freeable "REGHEAPD_KDLWPG" Chunk 7fadf7fb5060 sz= 2992 recreate "Ksfq heap" latch= (nil) Chunk 7FADF7FB5C10 sz= FreeabLe "Ksfq heap descr" Chunk 7fadf7fb0790 sz= 232 freeable "IOVECHEAPD_KDLW" Chunk 7fadf7fb0878 sz= 304 freeable "BCHEAPD_KDLWPGA" Chunk 7fadf7fb09a8 sz= 208 freeable "SIOHEAPD_KDLWPG" Chunk 7fadf7f B0ad8 sz= freeable "Ksz PGA Subheap" total heap size = 827504

Visible from the red font above,Pga,cga,uga is assigned independently in automatic management mode.