Shulou(Shulou.com)06/01 Report--

This article is about how to configure the memory of the oracle database server. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

SGA: is the memory area used to store database information, which is shared by database processes. It contains the data and control information of the Oracle server, it is allocated in the actual memory of the computer where the Oracle server resides, and then write to the virtual memory if the actual memory is not enough.

PGA: an area of memory that contains the data and control information of a service process. It is created by Oracle when a service process starts and is not shared. An Oracle process has a PGA memory area. A PGA can only be accessed by the service process that owns it, and only the Oracle code in that process can read and write it. Therefore, the structure in PGA does not require Latch protection.

Our focus is to set up SGA. Theoretically, SGA can account for 1 of the physical memory of the OS system.

The advice given by ORACLE is: OLTP system PGA= (Total Memory) * 80% 20%. DSS system PGA= (Total Memory) * 80% / 50%.

ORACLE recommends a database server, 80% of the memory to the database, 20% of the memory to the operating system, so how to allocate memory to a database server?

SQL > select * from v$pgastat

NAME VALUE UNIT

-

Aggregate PGA target parameter 104857600 bytes

-this value is equal to the value of the parameter PGA_AGGREGATE_TARGET. If this value is 0, PGA automatic management is disabled.

Aggregate PGA auto target 75220992 bytes

-indicates how much more memory PGA can provide for autorun mode, which is usually close to pga_aggregate_target-total pga inuse.

Global memory bound 20971520 bytes

-the maximum value executed by the workspace. If this value is less than 1m, increase the PGA size immediately.

Total PGA inuse 30167040 bytes

-the total size of the currently allocated PGA, which may be greater than PGA if the PGA setting is too small. This value is close to select sum (pga_used_mem) from v$process.

Total PGA allocated 52124672 bytes

-the total amount spent in the workspace

Maximum PGA allocated 67066880 bytes

Total freeable PGA memory 0 bytes-No free PGA

Process count 23-there are currently 23 process

Max processes count 25

PGA memory freed back to OS 0 bytes

Total PGA used for auto workareas 8891392 bytes

Maximum PGA used for auto workareas 22263808 bytes

Total PGA used for manual workareas 0 bytes-automatic management for 0

Maximum PGA used for manual workareas 0 bytes-automatic management for 0

Over allocation count 0

If the PGA setting is too small, causing PGA to sometimes be greater than the value of PGA_AGGREGATE_TARGET, 0 here means that PGA does not extend a value greater than TARGET, and if this value occurs, increase the PGA size.

Bytes processed 124434432 bytes

Extra bytes read/written 0 bytes

Cache hit percentage 100percent-hit rate of 100%. Increase PGA if it is too small

Recompute count (total) 6651

19 rows selected

SQL > select max (pga_used_mem) / 1024 + 1024 M from vault process;-current process consumes the largest amount of memory

M

-

9.12815189

SQL > select min (pga_used_mem) / 1024ax 1024m from v$process where pga_used_mem > 0;-process consumes the least memory

M

-

0.19186878

SQL > select max (pga_used_mem) / 1024ap1024m from v$process;-maximum memory ever consumed by process

M

-

9.12815189

SQL > select sum (pga_used_mem) / 1024 + 1024 from vault process;-Total PGA consumed by current process

SUM (PGA_USED_MEM) / 1024 Compact 1024

-

28.8192501068115

How to set up PGA? We can simulate the operation of the system during the stress test phase, and then run it.

Select (select sum (pga_used_mem) / 1024ap1024 from v$process) / (select count (*) from v$process) from dual; gets the approximate amount of memory consumed by a process, and then estimates how many connections the system will have, such as 500connections.

So Sessions=1.1*process + 5 minutes 500, then processes=450, multiplied by the memory consumed by a process, can roughly estimate how much PGA needs to be set up.

It is best to set the value of PGA larger than the calculated value. After setting the value of PGA, you can set the total memory to PGA/0.16 according to the nature of the system. If the system is OLTOP, then you can also estimate the size of SGA. It is recommended to allocate more memory, which is cheap anyway.

The following is an excerpt from eygle's rules about the maximum memory (serial operations) that an process can allocate:

Prior to 10gR1, the maximum memory that can be allocated by a process for serial operations (non-parallel) is min (5% pgaheratetargets 100m)

After 10gR2, there are the following rules for the maximum memory that a process can allocate for serial operations (non-parallel):

If pga_aggregate_target2.5G, then the maximum memory is 2.5G.

SQL > SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

2 FROM SYS.x$ksppi x, SYS.x$ksppcv y

3 WHERE x.inst_id = USERENV ('Instance')

4 AND y.inst_id = USERENV ('Instance')

5 AND x.indx = y.indx

6 AND x.ksppinm LIKE'% & par%'

7 /

NAME VALUE DESCRIB

- -

_ smm_max_size 20480 maximum work area size in auto mode (serial)

SQL > show parameter pga

NAME TYPE VALUE

-

Pga_aggregate_target big integer 100M

The maximum memory that can be allocated by one of my process here is 20m, because my PGA=100M conforms to the above rules.

The implicit parameter _ smm_max_size indicates that a process can allocate the largest memory.

I bought piner's book "oracle High availability Environment". This is a good time to learn.

Summarize what you have seen and publish it here. Today, the first chapter is about SGA and PGA.

In the future, the summary will be published here and shared with you.

The structure of SGA and PGA is shown below:

SGA:

View SGA:

Sqlp > show sga

Or select * from v$sga

Total System Global Area 289406976 bytes

Fixed Size 1248600 bytes

Variable Size 176161448 bytes

Database Buffers 109051904 bytes

Redo Buffers 2945024 bytes

Fixed Size: including database and instance control information, status information, dictionary information, etc., it is fixed in SGA at startup and will not be changed.

Variable Size: includes shard pool, large pool, java pool, stream pool, cursor area, and other structures

Database Buffers: the place where blocks of data are buffered in a database, which is the largest place in SGA, and determines database performance.

Redo Buffers: a place that provides REDO buffering, which is not too large in OLAP

V$sgastat records some statistics of SGA.

V$sga_dynamic_components saves some adjustment records of areas in the SGA that can be adjusted manually.

Shard pool:

Shard_pool_size determines its size and manages it automatically after 10g.

The data dictionary and control area structure in Shard_pool cannot be directly controlled by the user, and only the sql buffer (library cache) is related to the user.

Use DBMS_SHARED_POOL.KEEP stored procedures to pin frequently accessed procedures or packages in a shared pool.

Manually clear the contents of the shared pool: alter system flush shard_pool

Several common views related to shared pools:

V$sqlarea records all sql statistics, including execution times, physical reads, logical reads, elapsed time, etc.

V$sqltext_with_newline fully displays sql statements, marks statements through hash_value, and sorts them with piece

V$sql_plan saves the execution plan of sql, which can be viewed through the tool

V$shared_pool_advice 's prediction of shared pool can be used as a reference for adjusting SGA.

Data buffer:

In OLTP system, the hit rate of data buffer is required to be more than 95%.

Select sum (pins) "execution", sum (pinhits) "hits"

((sum (pinhits) / sum (pins)) * 100) "pinhitration"

Sum (reloads) "misses", ((sum (pins) / (sum (pins))

+ sum (reloads) * 100) "relhitratio"

From V$librarycache

PINS NUMBER Number of times a PIN was requested for objects of this namespace

PINHITS NUMBER Number of times all of the metadata pieces of the library object were found in memory

RELOADS NUMBER Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk

Oracle calls the database obtained from data buffer cache hit, and the foot cache miss obtained from disk

Blocks in the data buffer are managed through dirty lists (dirty list) and LRU lists (LRU list).

Data buffer can be subdivided into: the corresponding parameters of default pool, keep pool and recycle pool are db_cache_size, db_keep_cache_size and db_recycle_size to represent the buffer size, respectively.

Starting from 9i, oracle supports tablespaces of different block sizes. Accordingly, data buffers of different block sizes can be specified for tablespaces of different block sizes, and data buffers of different block sizes can be specified with the corresponding db_nk_cache_size, where n can be 2, 4, 6, 16 or 32.

V$db_cache_advice 's prediction of data buffers can be used as a reference for adjusting data buffer.

V$bh and x$bh record how data blocks are buffered in data buffer, and you can find hot blocks in the system through this view. Use the following statement to find the hotspot object in the system where the top 10 hotspot is located:

Select / * + rule*/ owner,object_name from dba_objects

Where data_object_id in

(select obj from

(select obj from x$bh order by tch 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.