[bbk5145] 第50集 - 第五章 管理内存 05

Oracle Database Memory Parameters

Program Global Area(PGA)

Automatic PGA memory management is enabled by default.

Using the V$PARAMETER View

SQL> show parameter sga_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0
SQL> l
  1  select name,value,isdefault
  2  from v$parameter
  3* where name like '%size'
SQL> /

NAME                           VALUE                ISDEFAULT
------------------------------ -------------------- ---------
sga_max_size                   3271557120           TRUE
shared_pool_size               0                    TRUE
large_pool_size                0                    TRUE
java_pool_size                 0                    TRUE
streams_pool_size              0                    TRUE
shared_pool_reserved_size      27682406             TRUE
java_max_sessionspace_size     0                    TRUE
db_block_size                  8192                 FALSE
db_cache_size                  0                    TRUE
db_2k_cache_size               0                    TRUE
db_4k_cache_size               0                    TRUE

NAME                           VALUE                ISDEFAULT
------------------------------ -------------------- ---------
db_8k_cache_size               0                    TRUE
db_16k_cache_size              0                    TRUE
db_32k_cache_size              0                    TRUE
db_keep_cache_size             0                    TRUE
db_recycle_cache_size          0                    TRUE
db_flash_cache_size            0                    TRUE
db_recovery_file_dest_size     10737418240          FALSE
global_context_pool_size                            TRUE
create_bitmap_area_size        8388608              TRUE
bitmap_merge_area_size         1048576              TRUE
parallel_execution_message_siz 16384                TRUE

NAME                           VALUE                ISDEFAULT
------------------------------ -------------------- ---------
e

hash_area_size                 131072               TRUE
result_cache_max_size          8192000              TRUE
object_cache_optimal_size      102400               TRUE
sort_area_size                 65536                TRUE
sort_area_retained_size        0                    TRUE
client_result_cache_size       0                    TRUE
olap_page_pool_size            0                    TRUE
max_dump_file_size             unlimited            TRUE

30 rows selected.

Monitoring Automatic Memory Management

If you want to monitor the decisions made by Automatic Memory Management via a command line:

• V$MEMORY_DYNAMIC_COMPONENTS has the current status of all memory components
• V$MEMORY_RESIZE_OPS has a circular history buffer of the last 800 memory resize requests
• V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter

Memory Tuning Guidelines for the Library Cache

• Establish formatting conventions for developers so that SQL statements match in the cache.

  • 关键字使用大写，字段名称，表名称使用小写.

• Use bind variables
• Eliminate unnecessary duplicate SQL.
• Consider using CURSOR_SHARING.　　

  SQL> show parameter cursor_sharing
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------
  cursor_sharing                       string      EXACT

• Use PL/SQL when possible.
• Cache sequence numbers.
• Pin objects in the library cache.

Quiz

For best performance,you should enable both Automatic Memory Management(AMM) and Automatic Shared Memory Management(ASSM) by setting the MEMORY_TARGET and the SGA_TARGET parameters.

1. True
2. False

Oracle 11g之后，如果是开启了AMM and ASSM管理，设置了参数MEMORY_TARGET之后，SGA_TARGET参数，则可以不用再设置.

Summary

In this lesson,you should have learned how to:

• Describe the memory conponents in the SGA
• Implement Automatic Memory Management
• Manually configure SGA parameters
• Use automatic PGA memory management