wudiyuqing
作者wudiyuqing2016-05-17 01:14
系统运维工程师, 新华三技术有限公司

oralce 11G SGA 自动管理、监控

字数 46848阅读 3481评论 0赞 1

11G 动态内存管理(监控):

1、 两个重要的参数:

Memory_target、Sga_target  参数根据自己的理解总结如下:

当Memory_target、Sga_target同时为0时不启用SGA自动管理。

当Memory_target为零、Sga_target不为零时启动SGA自动管理SGA内存上限为Sga_target的值。

当Memory_target不为零、Sga_target为零时启动SGA自动管理SGA内存下限可以理解为零上限Memory_target值(不考虑PGA情况下)。

当Memory_target不为零、Sga_target不为零时启动SGA自动管理SGA内存下限为Sga_target的值,上限Memory_target值(不考虑PGA情况下)。

一般情况oralce 自动分配SGA占据Memory_target值的60% ,PGA占据40%。

 

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 1552M

memory_target                               big integer 1552M

parallel_servers_target               integer   8

pga_aggregate_target                  big integer 0

sga_target                               big integer 0

说明: Image 007

如上采用自动管理。

共享池

查看共享池的大小为0 采用自动管理:

SQL> show parameter shared

 

NAME                                        TYPE       VALUE

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

hi_shared_memory_address       integer   0

max_shared_servers                     integer

shared_memory_address                      integer   0

shared_pool_reserved_size         big integer 20132659

shared_pool_size                   big integer 0

shared_server_sessions               integer

shared_servers                                integer   1

 

db_cache_size

SQL> show parameter db_cache_size

 

NAME                                        TYPE       VALUE

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

db_cache_size                                 big integer 0

java_pool

SQL> show parameter java

 

NAME                                        TYPE       VALUE

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

java_jit_enabled                     boolean TRUE

java_max_sessionspace_size      integer   0

java_pool_size                                 big integer 0

java_soft_sessionspace_limit      integer   0

 

large_pool

SQL> show parameter large_pool

 

NAME                                        TYPE       VALUE

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

large_pool_size                     big integer 0

streams_pool_size

SQL> show parameter stream

 

NAME                                        TYPE       VALUE

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

streams_pool_size                big integer 0

查看oracle启动后现在各个组件的值:

 

SQL> desc v$memory_dynamic_components

 Name                                             Null?    Type

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

 COMPONENT                                                   VARCHAR2(64)

 CURRENT_SIZE                                               NUMBER

 MIN_SIZE                                                NUMBER

 MAX_SIZE                                                NUMBER

 USER_SPECIFIED_SIZE                                  NUMBER

 OPER_COUNT                                                 NUMBER

 LAST_OPER_TYPE                                          VARCHAR2(13)

 LAST_OPER_MODE                                       VARCHAR2(9)

 LAST_OPER_TIME                                         DATE

 GRANULE_SIZE                                               NUMBER

 

SQL> select component,current_size,min_size,max_size from v$memory_dynamic_components;

 

COMPONENT                   CURRENT_SIZE   MIN_SIZE    MAX_SIZE

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

shared pool                           402653184  402653184     402653184

large pool                               16777216   16777216     16777216

java pool                        16777216   16777216     16777216

streams pool                         33554432   33554432     33554432

SGA Target                            973078528  973078528     973078528

DEFAULT buffer cache                 486539264  486539264     486539264

KEEP buffer cache                        0       0              0

RECYCLE buffer cache                          0       0              0

DEFAULT 2K buffer cache                   0       0              0

DEFAULT 4K buffer cache                   0       0              0

DEFAULT 8K buffer cache                   0       0              0

 

COMPONENT                   CURRENT_SIZE   MIN_SIZE    MAX_SIZE

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

DEFAULT 16K buffer cache                  0       0              0

DEFAULT 32K buffer cache                  0       0              0

Shared IO Pool                               0       0              0

PGA Target                           654311424  654311424     654311424

ASM Buffer Cache                        0       0              0

 

16 rows selected.

说明: Image 009

 

SQL> desc v$memory_resize_ops

 Name                                             Null?    Type

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

 COMPONENT                                                   VARCHAR2(64)

 OPER_TYPE                                             VARCHAR2(13)

 OPER_MODE                                          VARCHAR2(9)

 PARAMETER                                           VARCHAR2(80)

 INITIAL_SIZE                                           NUMBER

 TARGET_SIZE                                                   NUMBER

 FINAL_SIZE                                              NUMBER

 STATUS                                                    VARCHAR2(9)

 START_TIME                                           DATE

 END_TIME                                              DATE

 

SQL> select start_time,component,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE from v$memory_resize_ops;

 

START_TIM COMPONENT                          INITIAL_SIZE TARGET_SIZE FINAL_SIZE

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

15-MAY-16 shared pool                                   0       402653184  402653184

15-MAY-16 PGA Target                                    0       654311424  654311424

15-MAY-16 java pool                                        0       16777216   16777216

15-MAY-16 streams pool                                          0       33554432   33554432

15-MAY-16 SGA Target                                    0       973078528  973078528

15-MAY-16 DEFAULT buffer cache                486539264       486539264  486539264

15-MAY-16 ASM Buffer Cache                                0                0          0

15-MAY-16 DEFAULT buffer cache                         0       486539264  486539264

15-MAY-16 DEFAULT 2K buffer cache                    0                0          0

15-MAY-16 DEFAULT 4K buffer cache                    0                0          0

15-MAY-16 DEFAULT 8K buffer cache                    0                0          0

 

START_TIM COMPONENT                          INITIAL_SIZE TARGET_SIZE FINAL_SIZE

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

15-MAY-16 DEFAULT 16K buffer cache                 0                0          0

15-MAY-16 DEFAULT 32K buffer cache                 0                0          0

15-MAY-16 KEEP buffer cache                       0                0          0

15-MAY-16 RECYCLE buffer cache                         0                0          0

15-MAY-16 large pool                                      0       16777216   16777216

 

16 rows selected.

 

说明: Image 011

我的机器sga分配调整:

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 1552M

memory_target                               big integer 1552M

parallel_servers_target               integer   8

pga_aggregate_target                  big integer 0

sga_target                               big integer 0

查看sga配置建议视图:

 

SQL> desc v$memory_target_advice

 Name                                             Null?    Type

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

 MEMORY_SIZE                                                NUMBER

 MEMORY_SIZE_FACTOR                                        NUMBER

 ESTD_DB_TIME                                              NUMBER

 ESTD_DB_TIME_FACTOR                                      NUMBER

 VERSION                                                  NUMBER

可以看到 1 代表我当前的内存,当调整内存的时候数据库的速度不会提升,是因为我的数据库里面什么都没有跑没有备份导入导出操作等。         真正的生产库需按业务需求调整。

 

SQL> select * from v$memory_target_advice

  2  ;

 

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION

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

         776                .5               213                  1           0

       1164               .75               213                  1           0

       1552                 1              213                  1           0

       1940              1.25               213                  1           0

       2328               1.5               213                  1           0

       2716              1.75               213                  1           0

       3104                 2              213                  1           0

说明: Image 010

 

按照sga60%算:

SQL> select 1552*1024*1024*0.6 from dual;

 

1552*1024*1024*0.6

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

          976433971

Pag:

、SQL> select 1552*1024*1024*0.4 from dual;

 

1552*1024*1024*0.4

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

          650955981

差不多。

 

修改MEMORY_TARGET 的大小改变SGA 和pga的内存大小:

Scope=spfile 只能修改参数文件无法直接修改,修改后重启数据库生效:

SQL> alter system set memory_max_target=2328M scope=spfile;

 

System altered.

 

 

SQL> alter system set memory_target=2328M scope=spfile;

 

System altered.

说明: Image 012

 

重启后:

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 2336M

memory_target                               big integer 2336M

parallel_servers_target               integer   8

pga_aggregate_target                  big integer 0

sga_target                               big integer 0

说明: Image 001

 

SQL> select component,current_size,min_size,max_size,last_oper_type from v$memory_dynamic_components;

 

COMPONENT                   CURRENT_SIZE   MIN_SIZE    MAX_SIZE LAST_OPER_TYP

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

shared pool                           402653184  402653184     402653184 STATIC

large pool                               16777216   16777216     16777216 STATIC

java pool                        16777216   16777216     16777216 STATIC

streams pool                         33554432   33554432     33554432 STATIC

SGA Target                          1459617792 1459617792 1459617792 STATIC

DEFAULT buffer cache                 956301312  956301312     956301312 INITIALIZING

KEEP buffer cache                        0       0              0 STATIC

RECYCLE buffer cache                          0       0              0 STATIC

DEFAULT 2K buffer cache                   0       0              0 STATIC

DEFAULT 4K buffer cache                   0       0              0 STATIC

DEFAULT 8K buffer cache                   0       0              0 STATIC

 

COMPONENT                   CURRENT_SIZE   MIN_SIZE    MAX_SIZE LAST_OPER_TYP

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

DEFAULT 16K buffer cache                  0       0              0 STATIC

DEFAULT 32K buffer cache                  0       0              0 STATIC

Shared IO Pool                               0       0              0 STATIC

PGA Target                           989855744  989855744     989855744 STATIC

ASM Buffer Cache                        0       0              0 STATIC

 

16 rows selected.

说明: Image 002

 

SQL> select start_time,component,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE from v$memory_resize_ops;

 

START_TIM COMPONENT                          INITIAL_SIZE TARGET_SIZE FINAL_SIZE

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

15-MAY-16 shared pool                                   0       402653184  402653184

15-MAY-16 PGA Target                                    0       989855744  989855744

15-MAY-16 java pool                                        0       16777216   16777216

15-MAY-16 streams pool                                          0       33554432   33554432

15-MAY-16 SGA Target                                    0  1459617792 1459617792

15-MAY-16 DEFAULT buffer cache                956301312       956301312  956301312

15-MAY-16 ASM Buffer Cache                                0                0          0

15-MAY-16 DEFAULT buffer cache                         0       956301312  956301312

15-MAY-16 DEFAULT 2K buffer cache                    0                0          0

15-MAY-16 DEFAULT 4K buffer cache                    0                0          0

15-MAY-16 DEFAULT 8K buffer cache                    0                0          0

 

START_TIM COMPONENT                          INITIAL_SIZE TARGET_SIZE FINAL_SIZE

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

15-MAY-16 DEFAULT 16K buffer cache                 0                0          0

15-MAY-16 DEFAULT 32K buffer cache                 0                0          0

15-MAY-16 KEEP buffer cache                       0                0          0

15-MAY-16 RECYCLE buffer cache                         0                0          0

15-MAY-16 large pool                                      0       16777216   16777216

 

16 rows selected.

SGA 和PGA 改变了.

 

概要:

系统全局区,开启oracle后占用的内存空间,SGA 分为不同的池。通过v$sgastat查看:

SQL> select pool , sum(bytes) bytes fromv$sgastat group by pool;

 

POOL                    BYTES

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

                  978911080

java pool      16777216

streams pool   33554432

shared pool  402657976

large pool     16777216

一共配了约1.4G的sga内存,各个pool占用的内存如上。

SGA由java pool(java 池)、sharedpool(共享池)、large pool(大池)、streams pool(流池)和没有名字的池组成。其中那块没有名字的内存又包括块缓冲区(缓存的数据库块)、重做日志缓冲区和“固定SGA”区专用的内存。

详解:

重做日志缓冲区(redo log buffer)

重做日志缓冲区属于SGA。Oracle引入重做日志缓冲区的目的是用于数据恢复。

Oracle在执行任何DML和DDL操作改变数据之前,都会将恢复所需要的信息,先写入重做日志缓冲区,然后再写入 数据库高速缓冲区(Block buffer)。

Oracle执行DML语句时,只有编译(parse)和执行(execute)两个阶段:

(1)如果数据和回滚数据不在数据库高速缓冲区中,Oracle服务器进程会将它们从数据文件中读取到数据库高速缓冲区中。

(2)Oracle服务器进程会在要修改的数据行上添加行级锁。

(3)Oracle服务器进程将数据的变化信息和回滚所需的信息都写入重做日志缓冲区。

(4)Oracle服务器进程将对数据所做的修改和回滚所需的值都写入数据库高速缓冲区。然后将数据库高速缓冲区中的这些数据库标示为脏数据区(因为此时内存中的数据和外存中的数据是不一致的)。

(5)重做日志写进程将重做日志缓冲区中的数据写入重做日志文件中。

(6)数据库写进程将数据库高速缓冲区中的脏数据区中数据写入数据文件。

数据库缓冲区(Block buffer)

  BufferCache是SGA的一部分,Oracle利用Buffer Cache来管理data block,Buffer Cache的最终目的就是尽可能的减少磁盘I/O。

为了使自己要的数据不那么快老化。oracle把这块内存划分为三种类型:

    默认池(default pool):所有段块一般都在这个池中缓存。这就是原先的缓冲区池(原来也只有一个缓冲区池)。
     保持池(keep pool):按惯例,访问相当频繁的段会放在这个候选的缓冲区池中,如果把这些段放在默认缓冲区池中,尽管会频繁访问,但仍有可能因为其他段需要空间而老化(aging)。
     回收池(recycle pool):按惯例,访问很随机的大段可以放在这个候选的缓冲区池中,这些块会导致过量的缓冲区刷新输出,而且不会带来任何好处,因为等你想要再用这个块时,它可能已经老化退出了缓存。要把这些段与默认池和保持池中的段分开,这样就不会导致默认池和保持池中的块老化而退出缓存。

shared pool(共享池)

    共享池就是Oracle缓存一些“程序”数据的地方。在解析一个查询时,解析得到的表示(representation)就缓存在那里。在完成解析整个查询的任务之前,Oracle会搜索共享池,看看这个工作是否已经完成。你运行的PL/SQL代码就在共享池中缓存,所以下一次运行时,Oracle不会再次从磁盘重新读取。PL/SQL代码不仅在这里缓存,还会在这里共享。如果有1000个会话都在执行同样的代码,那么只会加载这个代码的一个副本,并由所有会话共享。Oracle把系统参数存储在共享池中。数据字典缓存(关于数据库对象的已缓存信息)也存储在这里。简单地讲,就像是厨房的水池一样,什么东西都往共享池里放。我的理解是:这就相当于公司的知识库,当遇见相同的问题直接查看知识库,不用在百度或者问如何解决,节约处理问题的时间。比如一个用户进行一次查询,在解析之前,查看共享池,这个sql语句是否已经缓存在这里了。如果在,他就没有必要再去进行解析了,因为已经解析好了,直接拿来用就可以了!这也是绑定变量为什么可以改善oracle性能的原因!

large pool 大池

     是把原来属于共享池里面的一些特殊的内存拿出来进行不同的处理。因为这些内存用完之后就可以立即释放,而共享池的内存不存在释放问题,因为是大家共享的。

    大池专门用于以下情况:
共享服务器连接,用于在SGA中分配UGA区,因为一个用户断开之后,UGA就可以立即释放!
语句的并行执行,允许分配进程间的消息缓冲区,这些缓冲区用于协调并行查询服务器。一旦发送了缓冲消息就可以立即释放!
备份,在某些情况下用于RMAN磁盘I/O缓冲区。因为写入磁盘之后,这些缓存可以立即释放!

Java pool-- Java池

   在数据库中运行Java代码时用到这部分内存。例如:编写Java存储过程在服务器内运行。需要注意的是,该内存与常见的Java编写的B/S系统并没关系。用JAVA语言代替PL/SQL语言在数据库中写存储过程才会用到这部分内存。

Stream pool 流池

   9iR2以上增加了“流”技术,10g以上在SGA中增加了流池。流是用来共享和复制数据的工具。

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

1

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

X社区推广