Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to analyze SGA and PGA memory management. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Memory usage in Oracle instances is divided into two categories: the program global area (program global area, PGA) and the system global area (system global area, SGA). The former is dedicated to each session, while the latter is shared by all Oracle processes. All memory used by Oracle is virtual memory. The Oracle process cannot determine whether the connected memory is in RAM or has been swapped to disk. Switching weakens performance and should be avoided.

1. PGA memory management

The PGA memory area includes a dedicated SQL area (stack area), a specified cursor (cursor area), a work area for sorting operations (sort area), and session-specific memory variables (session area). Since the 9i release, the management of PGA has been automated.

Two initialization parameters for PGA:

Workarea_size_policy: default auto, which means that Oracle can assign PGA to sessions as needed, while striving to keep the total amount of PGA allocation within the pga_aggregate_target range.

Pga_aggregate_target:11g defaults to 0, while 10g is not. In addition, in 9i, it only works for dedicated server connection mode, and 10g starts for both private and shared connections.

Col workarea_size_policy for a30

Select p1.value WORKAREA_SIZE_POLICY

To_char (p2.value / 1024 / 1024) | |'M' PGA_AGGREGATE_TARGET

From v$parameter p1, v$parameter p2

Where p1.name = 'workarea_size_policy' and p2.name =' pga_aggregate_target'

WORKAREA_SIZE_POLICY PGA_AGGREGATE_TARGET

AUTO 0M

You can view the PGA size allocated for all sessions through the view v$sesstat

Select to_char (round (sum (value) / 1024 / 1024, 2)) | |'M' session_pga_memory from v$sesstat natural join v$statname where name = 'session pga memory'

SESSION_PGA_MEMORY

117.59M

You can view the status and statistics of PGA memory through the view v$pgastat

Col name for a50

Col value for a20

Select name, to_char (round (value / 1024 / 1024)) | |'M' value from v$pgastat

NAME VALUE

Aggregate PGA target parameter 1831M

Aggregate PGA auto target 1632M

Global memory bound 183M

Total PGA inuse 18M

Total PGA allocated 55M

Maximum PGA allocated 415M

Total freeable PGA memory 26M

Process count 0M

Max processes count 0M

PGA memory freed back to OS 139987M

Total PGA used for auto workareas 0M

Maximum PGA used for auto workareas 174M

Total PGA used for manual workareas 0M

Maximum PGA used for manual workareas 1M

Over allocation count 0M

Bytes processed 539718M

Extra bytes read/written 292953M

Cache hit percentage 0M

Recompute count (total) 3M

Several important indicators

The sum of aggregate PGA target parameter:PGA memory.

The amount of memory allocated by the aggregate PGA auto target:PGA sort area.

Global memory bound: limits the amount of PGA memory used by a single process.

Cache hit percentage: the percentage of sorting completed in the PGA sort area.

If the cache hit percentage ratio is less than 100%, you can consider increasing the total amount of PGA to increase the size of the sort area.

II. SGA memory management

SGA consists of the following chunks:

Fixed area (Fixed Size): stores information about each component in the SGA and cannot be modified in size.

Variable area (Variable Size): including shared pool, large pool, stream pool, JAVA pool.

Database cache cache (Database buffer cache): the size is specified by the parameter db_cache_size (the parameter db_cache_size defaults to 0 after 10g).

Redo log buffer cache (Redo log buffer cache): the size is usually larger than the setting of the parameter log_buffer because the protection page is also set in memory to protect the log buffer.

The following command shows an overview of memory allocation for SGA

Show sga

Total System Global Area 4960579584 bytes

Fixed Size 2184232 bytes

Variable Size 2902461400 bytes

Database Buffers 2046820352 bytes

Redo Buffers 9113600 bytes

Or

Select * from v$sga

NAME VALUE

--

Fixed Size 2184232

Variable Size 2902461400

Database Buffers 2046820352

Redo Buffers 9113600

Starting with the 10g version, the management of SGA is automated (automatic shared memory management ASMM). Automatic shared memory management requires that the statistics_level parameter be set to typical or all. Automatic shared memory management introduces a new background process, MMAN (Memory Manager), which is used to dynamically adjust memory components according to memory recommendations collected continuously by the system.

Several initialization parameters for SGA:

Shared_pool_size: shared pool size.

Db_cache_size: the database cache cache size, which is the size of the default pool in the buffer pool.

Large_pool_size: big pool size.

Streams_pool_size: stream pool size.

Java_pool_size:java pool size.

The above parameters start at 10g and default to 0 under automatic shared memory management.

Log_buffer: log buffer size, static parameter. It is the only SGA structure in SGA that cannot be adjusted dynamically. It is fixed when the instance is started and cannot be managed automatically. The default value may be correct and can be adjusted to larger than the default value, but this often leads to performance degradation. If the setting is lower than the default value, the setting is ignored.

Sga_target:11g defaults to 0Jing 10g, which is the same as sga_max_size, and a value equal to 0 means that automatic shared memory management (ASMM) is disabled.

The upper limit of sga_max_size:sga_target, static parameter.

View the allocation of components in SGA

Col shared_pool_size for a18

Col shared_pool_size for a15

Col db_cache_size for a15

Col large_pool_size for a15

Col streams_pool_size for a18

Col java_pool_size for a15

Col log_buffer for a15

Col sga_target for a15

Col sga_max_size for a15

Select to_char (p1.value / 1024 / 1024) | |'M' shared_pool_size

To_char (p2.value / 1024 / 1024) | |'M' db_cache_size

To_char (p3.value / 1024 / 1024) | |'M' large_pool_size

To_char (p4.value / 1024 / 1024) | |'M' streams_pool_size

To_char (p5.value / 1024 / 1024) | |'M' java_pool_size

To_char (p6.value / 1024 / 1024) | |'M' log_buffer

To_char (p7.value / 1024 / 1024) | |'M' sga_target

To_char (p8.value / 1024 / 1024) | |'M' sga_max_size

From v$parameter p1

V$parameter p2

V$parameter p3

V$parameter p4

V$parameter p5

V$parameter p6

V$parameter p7

V$parameter p8

Where p1.name = 'shared_pool_size'

And p2.name = 'db_cache_size'

And p3.name = 'large_pool_size'

And p4.name = 'streams_pool_size'

And p5.name = 'java_pool_size'

And p6.name = 'log_buffer'

And p7.name = 'sga_target'

And p8.name = 'sga_max_size'

SHARED_POOL_SIZ DB_CACHE_SIZE LARGE_POOL_SIZE STREAMS_POOL_SIZE JAVA_POOL_SIZE LOG_BUFFER SGA_TARGET SGA_MAX_SIZE

--

0M 0M 0M 7.328125M 0M 1232M

What really determines the current size of each component is determined by a set of hidden parameters with double underscores.

Col name for a40

Col value for a20

Col pdesc for a70

Select x.ksppinm name, y.ksppstvl / 1024 / 1024 | |'M 'value, x.ksppdesc pdesc

From sys.x$ksppi x, sys.x$ksppcv y

Where x.indx = y.indx

And x.ksppinm in ('_ shared_pool_size','_ _ db_cache_size','_ _ large_pool_size','_ _ streams_pool_size','_ _ java_pool_size','_ _ sga_target','_ _ pga_aggregate_target')

NAME VALUE PDESC

_ _ shared_pool_size 208M Actual size in bytes of shared pool

_ _ large_pool_size 32M Actual size in bytes of large pool

_ _ java_pool_size 16M Actual size in bytes of java pool

_ _ streams_pool_size 0M Actual size in bytes of streams pool

_ _ sga_target 736M Actual size of SGA

_ _ db_cache_size 432M Actual size of DEFAULT buffer pool for standard block size buffers

_ _ pga_aggregate_target 496M Current target size for the aggregate PGA memory consumed

By generating the pfile file, you can also see its contents

Create pfile from spfile

Here is a typical pfile content from Oracle 10g:

Mes.__db_cache_size=482344960

Mes.__java_pool_size=8388608

Mes.__large_pool_size=4194304

Mes.__shared_pool_size=104857600

Mes.__streams_pool_size=4194304

* .audit_file_dest='D:\ oracle\ product\ 10.2.0Universe adminUniverse mesmp adump'

* .background_dump_dest='D:\ oracle\ product\ 10.2.0DUBDUP'

* .compatible='10.2.0.1.0'

* .control_files='D:\ oracle\ product\ 10.2.0\ oradata\ mes\ control01.ctl','D:\ oracle\ product\ 10.2.0\ oradata\ mes\ control02.ctl','D:\ oracle\ product\ 10.2.0\ oradata\ mes\ control03.ctl'

* .core_dump_dest='D:\ oracle\ product\ 10.2.0Universe adminUniverse mesmax cdump'

* .db_block_size=8192

* .db_domain=''

* .db_file_multiblock_read_count=16

* .db_name='mes'

* .db_recovery_file_dest='D:\ oracle\ product\ 10.2.0 Universe flashworthy recoveryrecovery area'

* .db_recovery_file_dest_size=2147483648

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=mesXDB)'

* .job_queue_processes=10

* .open_cursors=300

* .optimizer_index_caching=90

* .optimizer_index_cost_adj=20

* .pga_aggregate_target=203423744

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

* .sga_target=612368384

* .undo_management='AUTO'

* .undo_tablespace='UNDOTBS1'

* .user_dump_dest='D:\ oracle\ product\ 10.2.0

Through the view v$sga_dynamic_components, you can see the adjustment information of the dynamic components in SGA.

Col component for a30

Select component

Current_size

User_specified_size

Min_size

Max_size

Granule_size

Last_oper_type

Last_oper_mode

To_char (last_oper_time, 'yyyy-mm-dd hh34:mi:ss') last_oper_time

From v$sga_dynamic_components

COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE MIN_SIZE MAX_SIZE GRANULE_SIZE LAST_OPER_TYP LAST_OPER LAST_OPER_TIME

Shared pool 369098752 0 335544320 369098752 16777216 GROW DEFERRED 2017-10-05 14:31:17

Large pool 16777216 0 16777216 16777216 16777216 STATIC

Java pool 16777216 0 16777216 16777216 16777216 STATIC

Streams pool 0 0 0 16777216 STATIC

DEFAULT buffer cache 352321536 0 352321536 385875968 16777216 SHRINK DEFERRED 2017-10-05 14:31:17

KEEP buffer cache 0 0 0 16777216 STATIC

RECYCLE buffer cache 0 0 0 16777216 STATIC

DEFAULT 2K buffer cache 0 0 0 16777216 STATIC

DEFAULT 4K buffer cache 0 0 0 16777216 STATIC

DEFAULT 8K buffer cache 0 0 0 16777216 STATIC

DEFAULT 16K buffer cache 0 0 0 16777216 STATIC

DEFAULT 32K buffer cache 0 0 0 16777216 STATIC

Shared IO Pool 0 0 0 16777216 STATIC

ASM Buffer Cache 0 0 0 16777216 STATIC

View the actual size currently assigned to SGA

Select to_char (round (sum (bytes) / 1024 / 1024, 2)) | |'M' sga_memory from v$sgastat

SGA_MEMORY

-

810.49M

Categorize and view the allocation information of each component in SGA

Select nvl2 (pool, pool, name) name, to_char (round (sum (bytes) / 1024 / 1024, 2)) | | M'memory from v$sgastat group by nvl2 (pool, pool, name) order by 1

NAME MEMORY

Buffer_cache 272M

Fixed_sga 2.07M

Java pool 16M

Large pool 16M

Log_buffer 8.41M

Shared pool 464M

Streams pool 32M

Query flashback buffer size in SGA

Select * from v$sgastat where name = 'flashback generation buff'

POOL NAME BYTES

Shared pool flashback generation buff 3981120

View free memory in SGA

Select pool, name, to_char (round (bytes / 1024 / 1024)) | | 'M'free_size from v$sgastat t where t.name like' free%'

POOL NAME FREE_SIZE

Shared pool free memory 76M

Large pool free memory 15M

Java pool free memory 16M

Streams pool free memory 16M

Several other parameters related to SGA

Show parameter sga

NAME TYPE VALUE

-

Lock_sga boolean FALSE

Pre_page_sga boolean FALSE

Sga_max_size big integer 584M

Sga_target big integer 584M

Lock_sga: static parameter that locks SGA in physical memory so that SGA does not use virtual memory and improves data reading speed.

Alter system set lock_sga = true scope = spfile

However, it should be noted that this parameter cannot be set with memory_target/memory_max_target, otherwise the instance cannot be started, and the following error is reported:

ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together

Pre_page_sga: static parameter. The function of this parameter is to read the whole SGA into physical memory when starting the database instance to improve the efficiency of the system.

Alter system set pre_page_sga = true scope = spfile

If you want to cancel the 10g SGA automatic shared memory management, set the parameter sga_target to 0, and the change takes effect immediately. The memory allocation of each SGA component can be seen through the query described above. Several memory parameters shared_pool_size, db_cache_size, large_pool_size, streams_pool_size, and java_pool_size are no longer 0, but are locked according to the actual allocation values of the previous automatic shared memory management, and these parameters are written to the spfile file, which can be persisted the next time the instance is restarted.

If you need to restore 10g of SGA automatic shared memory management, you can first restore the sga_target parameter to the same as sga_max_size. Although the parameter has been dynamically modified, it has not been changed back to automatic shared memory management. By querying the values of various SGA components, you can see that they are not automatically restored to 0, because they have been written into spfile and will not be automatically changed back even if the instance is restarted. At this point, you can generate pfile from the current spfile file, then modify the pfile file, cancel the settings of shared_pool_size, db_cache_size, large_pool_size, streams_pool_size, and java_pool_size parameters, and then restart the instance with the modified pfile file. After restart, you can see that each SGA component has been updated to the default value of 0, thus restoring the automatic shared memory management. Don't forget that because it is started from pfile at this time, pfile should immediately generate spfile to ensure that the changes are written to the spfile file. After that, you can restart the instance again, and query the values of each SGA component again after restart to confirm that the change is successful.

III. 11g automatic memory management

11g automates memory management one step further, introducing two new initialization parameters:

Memory_target: dynamic parameter, which can be adjusted without restarting the instance, but its value cannot exceed the limit of another instance parameter memory_max_target. This parameter allows the Oracle instance to manage server memory usage in general, implementing automatic memory management (AMM) (with the exception of the log_buffer parameter), which allows Oracle to convert memory between PGA and SGA as needed. A value equal to 0 means that automatic memory management (AMM) is disabled.

Memory_max_target: static parameter, which is adjusted by restarting the instance.

If the parameter pga_aggregate_target or sga_target is set with automatic memory management AMM enabled, the specified value will be the minimum size, and AMM will not make PGA or SGA lower than this value.

Select to_char (p1.value / 1024 / 1024) | |'M' memory_target

To_char (p1.value / 1024 / 1024) | |'M' memory_max_target

From v$parameter p1, v$parameter p2

Where p1.name = 'memory_target'

And p2.name = 'memory_max_target'

MEMORY_TARGET MEMORY_MAX_TARGET

1232M 1232M

You can see the adjustment information of the dynamic components in memory through the view v$memory_dynamic_components.

Col component for a30

Select component

Current_size

User_specified_size

Min_size

Max_size

Granule_size

Last_oper_type

Last_oper_mode

To_char (last_oper_time, 'yyyy-mm-dd hh34:mi:ss') last_oper_time

From v$memory_dynamic_components

COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE MIN_SIZE MAX_SIZE GRANULE_SIZE LAST_OPER_TYP LAST_OPER LAST_OPER_TIME

Shared pool 369098752 0 335544320 369098752 16777216 GROW DEFERRED 2017-10-05 14:31:17

Large pool 16777216 0 16777216 16777216 16777216 STATIC

Java pool 16777216 0 16777216 16777216 16777216 STATIC

Streams pool 0 0 0 16777216 STATIC

SGA Target 771751936 0 771751936 771751936 16777216 STATIC

DEFAULT buffer cache 352321536 0 352321536 385875968 16777216 SHRINK DEFERRED 2017-10-05 14:31:17

KEEP buffer cache 0 0 0 16777216 STATIC

RECYCLE buffer cache 0 0 0 16777216 STATIC

DEFAULT 2K buffer cache 0 0 0 16777216 STATIC

DEFAULT 4K buffer cache 0 0 0 16777216 STATIC

DEFAULT 8K buffer cache 0 0 0 16777216 STATIC

DEFAULT 16K buffer cache 0 0 0 16777216 STATIC

DEFAULT 32K buffer cache 0 0 0 16777216 STATIC

Shared IO Pool 0 0 0 16777216 STATIC

PGA Target 536870912 0 536870912 536870912 16777216 STATIC

ASM Buffer Cache 0 0 0 16777216 STATIC

The PGA Target here is the actual pga_aggregate_ target value.

IV. Memory consultant

1. PGA memory consultant

The consultant can only be enabled if the statistics_level parameter is set to typical or all.

Suggestions for querying the memory size of PGA

Col pga_target_for_estimate for a30

Select to_char (pga_target_for_estimate / 1024 / 1024, '999999') | |'M' pga_target_for_estimate

Pga_target_factor

Estd_extra_bytes_rw

Estd_pga_cache_hit_percentage

Estd_overalloc_count

From v$pga_target_advice

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT

-

152M. 125 2.3527E+10 99 7

304M. 25 1.3997E+10 99 0

609M. 5 1.2325E+10 100 0

913M. 75 1.2325E+10 100 0

1217M 1 1.2325E+10 100 0

1460M 1.2 9412871168 100 0

1704M 1.4 9412871168 100 0

1947M 1.6 9412871168 100 0

2191M 1.8 9412871168 100 0

2434M 2 9412871168 100 0

3651M 3 9412871168 100 0

4868M 4 9412871168 100 0

7302M 6 9412871168 100 0

9736M 8 9412871168 100 0

Estd_extra_bytes_rw: represents the amount of disk I / O evaluated when the PGA target is set to the estimate in the first column.

Estd_pga_cache_hit_percentage: represents the percentage of the estimated sort completed in the PGA.

Estd_overalloc_count:PGA overload allocation.

The pga_target_factor:PGA target factor, a row equal to 1, is the current setting.

2. Buffer_cache cache consultant

This recommendation is controlled by initialization parameter db_cache_advice and is a dynamic parameter. There are 3 available values: OFF, ON, READY. The default is ON. The meaning is as follows:

OFF: turns off the recommendation and does not allocate memory for the recommendation.

ON: open the recommendation and both CPU and memory overhead will occur.

READY: turns off the recommended memory but retains the recommended memory allocation.

Recommendations for viewing the SGA cache size

Select id, name, block_size, size_for_estimate, size_factor, estd_physical_read_factor, estd_physical_reads from v$db_cache_advice

ID NAME BLOCK_SIZE SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

3 DEFAULT 8192 112. 0897 1.3858 1.2789E+10

3 DEFAULT 8192 224. 1795 1.0969 1.0123E+10

3 DEFAULT 8192 336. 2692 1.0412 9608886688

3 DEFAULT 8192 448. 359 1.0126 9344961971

3 DEFAULT 8192 560. 4487 1.003 9255747045

3 DEFAULT 8192 672. 5385 1.0008 9236158453

3 DEFAULT 8192 784. 6282 1.0005 9232636063

3 DEFAULT 8192 896. 7179 1.0003 9230983775

3 DEFAULT 8192 1008. 8077 1.0002 9229925754

3 DEFAULT 8192 1120. 8974 1.0001 9229177367

3 DEFAULT 8192 1232. 9872 1 9228548559

3 DEFAULT 8192 1248 1 1 9228424715

3 DEFAULT 8192 1344 1.0769. 9999 9227693021

3 DEFAULT 8192 1456 1.1667. 9998 9226879481

3 DEFAULT 8192 1568 1.2564. 9994 9222904948

3 DEFAULT 8192 1680 1.3462. 996 9191297489

3 DEFAULT 8192 1792 1.4359. 9926 9159918796

3 DEFAULT 8192 1904 1.5256. 9886 9123574082

3 DEFAULT 8192 2016 1.6154. 9868 9106458871

3 DEFAULT 8192 2128 1.7051. 9862 9100917681

3 DEFAULT 8192 2240 1.7949. 9848 9088286201

3. SGA memory consultant

The consultant can only be enabled if the statistics_level parameter is set to typical or all.

Query the SGA memory advisor, the third column represents the total time it takes to execute the SQL statement in the database when the SGA target is set to the value in the first column, and the row of SGA_TARGET_FACTOR=1 is the current setting.

Select sga_size, sga_size_factor, estd_db_time from v$sga_target_advice order by 2

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME

768. 5 3499668

1152. 75 1249677

1536 1 752455

1920 1.25 696773

2304 1.5 696548

2688 1.75 696548

3072 2 696548

4. Shared_pool shared Pool Advisor

Recommendations for viewing shared pool siz

Select shared_pool_size_for_estimate

Shared_pool_size_factor

Estd_lc_size

Estd_lc_memory_objects

Estd_lc_time_saved

Estd_lc_time_saved_factor

Estd_lc_memory_object_hits

From v$shared_pool_advice

SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_SIZE ESTD_LC_MEMORY_OBJECTS ESTD_LC_TIME_SAVED ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_MEMORY_OBJECT_HITS

112. 4 12 768 232. 9831 16977

140. 5 40 2280 234. 9915 37015

168. 6 59 3284 236 1 37458

196. 7 64 3611 236 1 37463

224. 8 64 3611 236 1 37463

252. 9 64 3611 236 1 37463

280 1 64 3611 236 1 37463

308 1.1 64 3611 236 1 37463

336 1.2 64 3611 236 1 37463

364 1.3 64 3611 236 1 37463

392 1.4 64 3611 236 1 37463

420 1.5 64 3611 236 1 37463

448 1.6 64 3611 236 1 37463

476 1.7 64 3611 236 1 37463

504 1.8 64 3611 236 1 37463

532 1.9 64 3611 236 1 37463

560 2 64 3611 236 1 37463

As you can see from the above data, the same effect can be achieved when the shared pool is 168m. The current setting is 280m, which wastes part of the memory. You can dynamically adjust the shared pool parameters to release memory:

Alter system set shared_pool_size = 168m

5. Memory target consultant

The view is not present in 10g. The consultant can only be enabled if the statistics_level parameter is set to typical or all.

Query the memory target advisor, the third column represents the total time it takes to execute the SQL statement in the database when the total memory allocation (SGA plus PGA) is set to the value in the first column, and the row of MEMORY_SIZE_FACTOR=1 is the current setting.

Select memory_size, memory_size_factor, estd_db_time from v$memory_target_advice

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME

496. 5 22

744. 75 22

992 1 22

1240 1.25 22

1488 1.5 22

1736 1.75 22

1984 2 22

Fifth, resident the program in memory

For frequently called database objects, they can be resident in memory to reduce the disk I / O and thus reduce the user's response time.

You can view the information of database objects in the shared pool library cache through the view v$db_object_cache. The following view shows the object TOP10 that executes the most times in the shared pool library cache:

Col owner for a10

Col name for a30

Col type for a20

Select *

From (select owner, name, type, sharable_mem, loads, executions, kept

From v$db_object_cache

Where owner = 'CMES'

Order by executions desc)

Where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.