Key Points of Oracle performance adjustment: SGA

 

I. Shared pool tunning

 

The Optimization of the Shared pool should be given priority, because a cache miss occurs in the shared pool, which is more costly than the data buffer, because the dictionary data is generally longer than the data stored in the library cache in the memory, the key is the library cache Optimization.

 

Gets :( parse) the number of times the object is searched in namespace;

 

Pins: (execution) the number of times the object is read or executed in the namespace;

 

Reloads: (reparse) the number of times library cache misses occur during execution, resulting in the re-parsing of SQL statements.

 

1. Check whether the gethitratio of the SQL area in v $ librarycache exceeds 90%. If not, check the application code to improve the efficiency of the application code.

 

Select gethitratio from v $ librarycache where namespace = 'SQL region ';

 

2. The ratio of reloads/pins in v $ librarycache should be less than 1%. If it is greater than 1%, the value of shared_pool_size should be increased.

 

Select sum (pins) "executions", sum (reloads) "cache misses", sum (reloads)/sum (pins) from v $ librarycache;

 

Reloads/pins> 1% has two possibilities: insufficient library cache space and invalid objects referenced in SQL.

 

3. The shared pool reserved size is generally 10% of the shared pool size and cannot exceed 50%. The request misses in V $ shared_pool_reserved is 0 or does not continue to grow, or the free_memory is greater than 50% of the shared pool reserved size, which indicates that the shared pool reserved size is too large and can be compressed.

 

4. convert large anonymous pl/SQL code blocks into small anonymous pl/SQL code blocks to call the stored procedure.

 

5. From 9i, you can save the execution plan and SQL statement in the library cache for performance diagnosis. We can see execution plans from v $ SQL _plan.

 

6. Keep large objects in the shared pool. Large objects are the main cause of memory fragmentation. in order to free up space, many small objects need to be removed from the memory, thus affecting user performance. Therefore, you need to keep some common large objects in the shared pool, and the following objects must be kept in the shared pool:

 

A. Frequently Used storage process;

 

B. Compiled triggers for frequently operated tables

 

C. Sequence, because the number may be lost after the Sequence is removed from the shared pool.

 

Search for large objects not saved in the library cache:

 

Select * from v $ db_object_cache where sharable_mem> 10000 and type in ('package', 'Procedure ', 'function', 'package body') and kept = 'no ';

 

Save these objects in the library cache:

 

Execute dbms_shared_pool.keep ('package _ name ');

 

Corresponding Script: dbmspool. SQL

 

7. check whether a large anonymous pl/SQL code block exists. Two solutions:

 

A. convert to a small anonymous block to call the Stored Procedure

 

B. Keep it in the shared pool.

 

Check whether there are too many anonymous pl/SQL blocks:

 

Select SQL _text from v $ sqlarea where command_type = 47 and length (SQL _text)> 500;

 

8. Optimization of Dictionary cache

 

To avoid the occurrence of Dictionary cache misses or the number of misses remains stable, you can adjust the size of the dictionary cache indirectly by adjusting the shared_pool_size.

 

Percent misses should be low: Most should be less than 2%, and the total should be less than 15%

 

Select sum (getmisses)/sum (gets) from v $ rowcache;

 

If the value exceeds 15%, the value of shared_pool_size is increased.

 

 

 

 

Ii. Buffer Cache

 

1. Set the granule size. db_cache_size defines the default buffer pool size in bytes.

 

If SGA <128 M, granule = 4 M, otherwise granule = 16 M, that is, when you need to adjust sga, increase the size in granule, and the size of sga should be an integer multiple of granule.

 

2. Adjust the buffer cache size according to v $ db_cache_advice.

 

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM v $ db_cache_advice where name = 'default' AND advice_status = 'on' AND block_size = (SELECT Value FROM v $ parameter where name = 'db _ block_size ');

Estd_physical_read_factor <= 1

 

 

3. Calculate cache hit ratio> 90% of buffer cache. If it is lower than 90%, use the following solutions:

 

◆ Increase the buffer cache value;

 

◆ Multiple buffer pools are used;

 

◆ Cache table;

 

◆ Create an independent buffer cache for sorting and parallel reads;

 

Select name, value FROM v $ sysstat where name in ('session logical reads', 'Physical reads', 'Physical reads Direct', 'Physical reads direct (lob )');

Cache hit ratio = 1-(physical reads-physical reads direct (lob)/session logical reads;

Select 1-(phy. value-dir.value-lob.value)/log. value from v $ sysstat log, v $ sysstat phy, v $ sysstat dir, v $ sysstat LOB where log. name = 'session logical reads' and phy. name = 'physical reads' and dir. name = 'physical reads direct 'and lob. name = 'physical reads direct (lob )';

 

 

Factors that affect cache hit ratio:

 

◆ Full table Scan

 

◆ Application Design

 

◆ Random access to large tables

 

◆ Unbalanced distribution of cache hits

 

4. Table space uses automatic space management to eliminate the need for free space lists and reduce database competition.

 

 

 

 

3. Other SGA objects

 

1. redo log buffer

 

The corresponding parameter is log_buffer. The default value is related to OS, which is generally 500 kb. Check whether log buffer wait exists in v $ session_wait and redo buffer allocation retries exists in v $ sysstat.

 

A. check whether there is log buffer wait:

 

Select * from v $ session_wait where event = 'Log buffer wait ';

 

If a wait occurs, you can increase the log buffer size or move the log file to a disk with faster access speed.

 

B. Select name, value from v $ sysstat where name in ('redo buffer allocation retries', 'redo entries ')

 

Redo buffer allocation retries is close to 0, less than 1% of redo entries. If it continues to grow, it indicates that the process has to wait for the redo buffer space. If the Redo buffer allocation retries is too large, increase the value of log_buffer.

 

C. Check whether disk IO competition exists in log files

 

Select event, total_waits, time_waited, average_wait from v $ system_event where event like 'Log file switch completion % ';

 

If there is competition, you can consider transferring the log file to an independent, faster storage device or increasing the log file.

 

D. Check whether the checkpoint settings are reasonable

 

Check whether 'checkpoint not complete' exists in the alert. log file ';

 

Select event, total_waits, time_waited, average_wait from v $ system_event where event like 'Log file switch (check % ';

 

If there is a wait, adjust the log_checkpoint_interval and log_checkpoint_timeout settings.

 

E. Check the work of log archiver.

 

Select event, total_waits, time_waited, average_wait from v $ system_event where event like 'Log file switch (arch % ';

 

If there is a wait, check whether the storage device that saves the archived logs is full. Add a log file group and adjust log_archiver_max_processes.

 

F. DB_block_checksum = true, which increases the performance burden. (To ensure data consistency, add a checksum to the block when writing data in oracle, and verify the checksum when reading data)

 

2. java pool

 

For large applications, java_pool_size should be greater than or equal to 50 M. For general java stored procedures, the default 20 M is enough.

 

3. Check whether DBWn needs to be adjusted.

 

Select total_waits from v $ system_event where event = 'free buffer waits ';

 

 

 

========================================================== ============================================

 

 

Oracle 10g modify SGA, PGA size

 

 

 

 

I. Concepts

 

SGA refers to the System Global Area, which is the memory Area used to store database information. This information is shared by database processes.

 

PGA refers to the Process Global Area, which contains the data and control information of a single server Process or a single background Process. It is opposite to the SGA shared by several processes, PGA is a region used by only one process. PGA is allocated when a process is created and recycled when the process is terminated. Oracle 10 Gb provides automatic management of PGA memory. The pga_aggregate_target parameter can specify the maximum PGA memory. When pga_aggregate_target is greater than 0, Oracle automatically manages the pga memory, and the sum of the PGA occupied by each process is not greater than the value specified by pga_aggregate_target.

 

 

 

 

Ii. Configuration

 

Oracle recommends that the OLTP (on-line Transaction Processing) system accounts for 80% of the total system memory, and then allocates 80% to SGA and 20% to PGA. That is

 

SGA = system_total_memory * 80% * 80%

 

PGA = system_total_memory * 80% * 20%

 

 

 

 

Iii. Operations

 

Log on to the system as SYSDBA as a SYS user

 

Alter system set sga_max_size = 2000 m scope = spfile;

 

Alter system set sga_target = 2000 m scope = spfile;

 

Alter system set pga_aggregate_target = 500 m scope = spfile;

 

 

 

 

Then restart the database.

 

Finally, check whether it takes effect.

 

Show parameter sga_max_size;

 

Show parameter sga_target;

 

Show parameter pga_aggregate_target;

 

 

 

========================================================== ============================

 

 

 

 

Effects of 11gMEMORY_TARGET on SGA and PGA

The MEMORY_MAX_TARGET parameter is added to the 11g file. This parameter is as refreshing as the first SGA_MAX_SIZE parameter appears in 10g. Memory_max_target is used to set the maximum memory space occupied by Oracle to OS, and the maximum memory space occupied by Oracle SGA. Undoubtedly, in 11gOracle, it will go further to automatic memory management.

 

 

 

The sga_max_size of 10 Gb is dynamically allocated to the Shared Pool Size, database buffer cache, large pool, java pool, and redo log buffer, only the size of each memory block of SGA is re-allocated based on the Oracle running status. PGA needs to be set separately in 10 Gb.

 

 

 

The 11gMEMORY_MAX_TARGET parameter contains two parts: memory, one System global area (SGA) and the other system global area (PGA ). Obviously, PGA and SGA can be dynamically managed in 11 GB.

 

Let's take a look at the impact of Memory_target setting and not setting on SGA/PGA in 11g:

 

 

 

A: If Memory_target is set to A non-0 value

 

(In the following four cases, the size of SGA and PGA is allocated)

 

1: The size of sga_target and pga_aggregate_target has been set.

 

If the sga_target and pga_aggregate_target parameters have been set, these two parameters are allocated to the minimum value and their target values respectively.

 

Memory_Target = SGA_TARGET + PGA_AGGREGATE_TARGET, size and

 

Memory_max_size is consistent.

 

2: sga_target: set the size. pga_aggregate_target is not set.

 

Then pga_aggregate_target initialization value = memory_target-sga_target

 

3: No size is set for sga_target and pga_aggregate_target.

 

So the sga_target initialization value = memory_target-pga_aggregate_target

 

4: Neither sga_target nor pga_aggregate_target set the Oracle 11g size.

 

If neither sga_target nor pga_aggregate_target is set, Oracle does not have the minimum and default values for these two values. Oracle allocates the size based on the database running status. However, when the database is started, a fixed proportion is allocated:

 

Sga_target = memory_target * 60%

 

Pga_aggregate_target = memory_target * 40%

 

 

 

B: If Memory_target is not set or = 0 (the default value is 0 in 11g)

 

If the default value is 0 in 11g, The Memory_target function is canceled in the initial state. It is completely consistent with 10g in memory management and completely backward compatible.

 

(SGA and PGA are allocated in three cases)

 

1: When SGA_TARGET is set, the shared pool, buffer cache, redo log buffer, java pool, and larger pool in SGA are automatically adjusted.

 

Memory size. PGA depends on the size of pga_aggregate_target. Sga and pga cannot automatically increase or decrease.

 

2: SGA_target and PGA_AGGREGATE_TARGET are not set.

 

The size of each component in SGA must be clearly set. The size of each component cannot be adjusted automatically. PGA cannot automatically increase or contract.

 

3: Set MEMORY_MAX_TARGET, but MEMORY_TARGET = 0. This is the same as 10 Gb.

 

 

 

 

 

You can use the following to view the values of each component in 11g.

 

 

SQL> show parameter target

 

 

 

NAME TYPE VALUE

 

--------------------------------------------------------

 

Archive_lag_target integer 0

 

Db_flashback_retention_target integer 1440

 

Fast_start_io_target integer 0

 

Fast_start_mttr_target integer 0

 

Memory_max_target big integer 1024 M

 

Memory_target big integer 1024 M

 

Pga_aggregate_target big integer 0

 

Sga_target big integer 0

 

 

 

 

 

To monitor the situation of Memory_target, you can use the following three dynamic attempts:

 

V $ MEMORY_DYNAMIC_COMPONENTS

 

V $ MEMORY_RESIZE_OPS

 

V $ memory_target_advice

 

 

 

Use the following Command to adjust the size:

 

 

 

SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET = 1024 M SCOPE = SPFILE;

SQL> ALTER SYSTEM SET MEMORY_TARGET = 1024 M SCOPE = SPFILE;

SQL> ALTER SYSTEM SET SGA_TARGET = 0 SCOPE = SPFILE;

SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;