create pfile from spfile='***/spfileorcl.ora'
修改参数文件
[oracle@rac1 dbs]$ cat initorcl1.ora
orcl2.__db_cache_size=71303168--双下划线参数是由mman进程维护的内部设置被用于后续自动调整的起始点
添加orcl1.shared_pool_size=82331648--双下划线参数只适用于自动调整的模式
--因此设置手动管理内存最起码需指定共享池大小
--取消sga_target 新增或保留sga_max_size
使用pfile启动实例查看各组件使用内存情况
SQL> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 24M
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 4M
shared_pool_size                     big integer 80M
streams_pool_size                    big integer 0
--日志缓冲区
SQL> show parameter buffer
log_buffer                           integer     2927616
--高速缓冲区
SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 48M

使用advice评估合适内存大小
SQL> select * from v$fixed_table where name like '%ADVICE%';

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
GV$SHARED_POOL_ADVICE          4294951883 VIEW       65537
V$SHARED_POOL_ADVICE           4294951884 VIEW       65537
GV$JAVA_POOL_ADVICE            4294951995 VIEW       65537
V$JAVA_POOL_ADVICE             4294951996 VIEW       65537
GV$STREAMS_POOL_ADVICE         4294951992 VIEW       65537
V$STREAMS_POOL_ADVICE          4294951993 VIEW       65537
GV$PX_BUFFER_ADVICE            4294952089 VIEW       65537
V$PX_BUFFER_ADVICE             4294952090 VIEW       65537
GV$SGA_TARGET_ADVICE           4294952615 VIEW       65537
V$SGA_TARGET_ADVICE            4294952616 VIEW       65537
GV$DB_CACHE_ADVICE             4294951627 VIEW       65537

NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
V$DB_CACHE_ADVICE              4294951628 VIEW       65537
GV$MTTR_TARGET_ADVICE          4294951826 VIEW       65537
V$MTTR_TARGET_ADVICE           4294951825 VIEW       65537
GV$PGA_TARGET_ADVICE_HISTOGRAM 4294951848 VIEW       65537
V$PGA_TARGET_ADVICE_HISTOGRAM 4294951849 VIEW       65537
GV$PGA_TARGET_ADVICE           4294951851 VIEW       65537
V$PGA_TARGET_ADVICE            4294951852 VIEW       65537
----------------------------V$SHARED_POOL_ADVICE-----------------------------------
/**
*V$SHARED_POOL_ADVICE displays information about estimated parse time in the shared pool
*for different pool sizes. The sizes range from 10% of the current shared pool size
*or the amount of pinned library cache memory (whichever is higher) to 200% of the
*current shared pool size, in equal intervals. The value of the interval depends on
*the current size of the shared pool.
*/
SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE,SHARED_POOL_SIZE_FACTOR,ESTD_LC_TIME_SAVED from V$SHARED_POOL_ADVICE;

SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_TIME_SAVED
----------------------------- ----------------------- ------------------
                           56                      .7               2096
                           64                      .8               2131
                           72                      .9               2136
                           80                       1               2136
                           88                     1.1               2136
                           96                     1.2               2136
                          104                     1.3               2136
                          112                     1.4               2136
                          120                     1.5               2136
                          128                     1.6               2136
                          136                     1.7               2136

SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_TIME_SAVED
----------------------------- ----------------------- ------------------
                          144                     1.8               2136
                          152                     1.9               2136
                          160                       2               2136

14 rows selected.
--ESTD_LC_TIME_SAVED 共享池大小确定的情况下所节省的库高速缓存分析时间量
Estimated elapsed parse time saved (in seconds), owing to library cache memory objects
being found in a shared pool of the specified size. This is the time that would have
been spent in reloading the required objects in the shared pool had they been aged out
due to insufficient amount of available free memory.
--SHARED_POOL_SIZE_FACTOR Size factor with respect to the current shared pool size
共享池可以设置为72m
----------------------------V$DB_CACHE_ADVICE-----------------------------------
V$DB_CACHE_ADVICE contains rows that predict the number of physical reads for the cache
size corresponding to each row. The rows also compute a "physical read factor," which
is the ratio of the number of estimated reads to the number of reads actually performed
by the real buffer cache during the measurement interval.
--估计不同缓存大小所需的物理读取数

SQL> select name,SIZE_FOR_ESTIMATE,SIZE_FACTOR,ESTD_PHYSICAL_READS,ESTD_CLUSTER_READS from V$DB_CACHE_ADVICE;

NAME       SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS ESTD_CLUSTER_READS
---------- ----------------- ----------- ------------------- ------------------
DEFAULT                    4       .0833               13915                  0
DEFAULT                    8       .1667                9638                  0
DEFAULT                   12         .25                9118                  0
DEFAULT                   16       .3333                9014                  0
DEFAULT                   20       .4167                8910                  0
DEFAULT                   24          .5                8792                  0
DEFAULT                   28       .5833                8584                  0
DEFAULT                   32       .6667                8331                  0
DEFAULT                   36         .75                8198                  0
DEFAULT                   40       .8333                7767                  0
DEFAULT                   44       .9167                7663                  0

NAME       SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS ESTD_CLUSTER_READS
---------- ----------------- ----------- ------------------- ------------------
DEFAULT                   48           1                7559                  0
DEFAULT                   52      1.0833                7470                  0
DEFAULT                   56      1.1667                7470                  0
DEFAULT                   60        1.25                7470                  0
DEFAULT                   64      1.3333                7470                  0
DEFAULT                   68      1.4167                7470                  0
DEFAULT                   72         1.5                7470                  0
DEFAULT                   76      1.5833                7470                  0
DEFAULT                   80      1.6667                7470                  0

高速缓冲合理大小应是52m

-------------------------V$JAVA_POOL_ADVICE------------------------------------------
如果不运行java存储过程就不需要这个池
SQL> select JAVA_POOL_SIZE_FOR_ESTIMATE ,ESTD_LC_TIME_SAVED from V$JAVA_POOL_ADVICE;

JAVA_POOL_SIZE_FOR_ESTIMATE ESTD_LC_TIME_SAVED
--------------------------- ------------------
                          4                  0
                          8                  0
                         12                  0
                         16                  0
                         20                  0
                         24                  0
                         28                  0
                         32                  0
                         36                  0
                         40                  0
                         44                  0

JAVA_POOL_SIZE_FOR_ESTIMATE ESTD_LC_TIME_SAVED
--------------------------- ------------------
48 0
----------------------log_buffer-------------------------------------------
日志缓冲区的大小与cpu的数量有关 cpu_count=1 一般log_buffer = 256k

调整共享池、高速缓冲区等
[oracle@rac1 dbs]$ cat initorcl1.ora
orcl2.__db_cache_size=71303168
orcl1.db_cache_size=52m
orcl2.__java_pool_size=4194304
orcl1.__large_pool_size=4194304
orcl2.__large_pool_size=4194304
orcl2.__shared_pool_size=83886080
orcl1.shared_pool_size=72m
orcl1.__streams_pool_size=0
orcl2.__streams_pool_size=0
log_buffer=2927616
查看结果
SQL> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 24M
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 3774873
shared_pool_size                     big integer 72M
streams_pool_size                    big integer 0
SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 52M

-------------------------------PGA----------------------------------------------
Automatic SQL Execution Memory Management
Prior to Oracle9i optimization of the PGA memory structures could be very time
consuming depending on the type of operations the system was performing. Oracle9i
allows the DBA to leave configuration of the PGA up to Oracle by setting two initialization
parameters:

WORKAREA_SIZE_POLICY = AUTO
PGA_AGGREGATE_TARGET = 100000K The WORKAREA_SIZE_POLICY parameter tell the server that
it should take over PGA memory management. The PGA_AGGREGATE_TARGET parameter specifies
the total amount of memory the server can allocate to the PGA. Oracle quote the following
equations as a base for calculating the value of this parameter:

PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 20% for an OLTP system--TOTAL_MEM = sga
PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 50% for a DSS systemWhere TOTAL_MEMORY is the total available memory for the system. If multiple applications/instances are running on the machine the values should be adjusted accordingly.

For backwards compatibility Oracle9i allows manual configuration of the PGA using:

WORKAREA_SIZE_POLICY = MANUAL--如果是auto 则忽略下面的参数
SORT_AREA_SIZE =
HASH_AREA_SIZE =
BITMAP_MERGE_AREA_SIZE =
CREATE_BITMAP_AREA_SIZE =

SQL> show parameter work

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO
SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 16M

SQL> column name format a40;
SQL> select * from v$pgastat;

NAME                                          VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter             16777216 bytes --pga被设置为16m
aggregate PGA auto target                   4194304 bytes --4m可以被自动管理
global memory bound                         3354624 bytes
total PGA inuse                            84041728 bytes --80m左右的pga正在使用
total PGA allocated                       190299136 bytes --已分配190m左右的PGA
maximum PGA allocated                     217222144 bytes
total freeable PGA memory                  12320768 bytes --12m已分配的pga可以被重新指派
process count                                    38
max processes count                              43
PGA memory freed back to OS                45481984 bytes
total PGA used for auto workareas            169984 bytes

NAME                                          VALUE UNIT
---------------------------------------- ---------- ------------
maximum PGA used for auto workareas          616448 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas             0 bytes
over allocation count                          1006       --越过目标值的次数
bytes processed                            21968896 bytes
extra bytes read/written                          0 bytes
cache hit percentage                            100 percent
recompute count (total)                        1006

19 rows selected.

-------------------------------V$PGA_TARGET_ADVICE-------------------------------------
V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty if PGA_AGGREGATE_TARGET is not set. In addition, the content of this view is not updated if the STATISTICS_LEVEL parameter is set to BASIC. Base statistics for this view are reset at instance startup and when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

主要字段描述
ESTD_EXTRA_BYTES_RW --额外的读写字节数
Estimated number of extra bytes which would be read or written if PGA_AGGREGATE_TARGET
was set to the value of the PGA_TARGET_FOR_ESTIMATE column. This number is derived from
the estimated number and size of work areas which would run in one-pass (or multi-pass)
for that value of PGA_AGGREGATE_TARGET.
ESTD_OVERALLOC_COUNT --越过目标值的次数

ITPub博客

sga pga management

最新文章