oracle11g 调整sga,进一步理解oracle11g之v$sga_resize_ops

最新推荐文章于 2023-10-31 19:11:07 发布

小财包

最新推荐文章于 2023-10-31 19:11:07 发布

阅读量444

点赞数

文章标签： oracle11g 调整sga

结论

1,v$sga_resize_ops记录已完成的近期850次SGA组件重定义大小的操作，注意是已经完成

与之对应的是v$sga_current_resize_ops它仅记录当前正在操作中的SGA组件重定义操作，注意是正在操作中的

2,由此引申出，ORACLE任何知识皆有正反性，对应即有对应的知识点，所以考虑问题要从自反和辩证方面分析，才会更进一步理解ORACLE

3,不要基于__db_cache_size(以及类似的SGA其它组件)显式调整SGA对应组件的大小，它不会记录到v$sga_resize_ops

4,可以随意增减__db_cache_size(其它类似),ORACLE不会有任何报错

5,如果仅是单方面调整了db_cache_size或其它组件大小(非隐含对应参数)，不管成功或失败，皆会记录到v$sga_resize_ops

6,如果要增加一个SGA组件大小，先要减少其它SGA组件的大小，然后才能增加这个SGA组件的大小，所以这是2个连续的操作

7,并且最终要调整的SGA组件大小并非和要调整的大小一致，我想这取决于SGA组件的大小以及各个组件内部的存储结构以入要调整组件的大小

测试

SQL> select * from v$version where rownum=1;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> col oper_type for a15

SQL> col parameter for a20

SQL> col component for a30

SQL> set linesize 300

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col oper_mode noprint

----未调整前的SGA组件重定义大小操作信息，此视图记录已经近期完成的850次重定义操作记录，如果是当前正在重定义操作，请到v$sga_current_resize_ops查看，二视图结构相同

SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME

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

streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME

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

java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29

13 rows selected.

SQL> show parameter sga_

NAME TYPE VALUE

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

sga_max_size big integer 548M

sga_target big integer 0

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 24326963

shared_pool_size big integer 464M

streams_pool_size big integer 0

NAME_1 VALUE_1 DESC1

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

__db_cache_size 50331648 Actual size of DEFAULT buffer pool for standard bl

ock size buffers

__shared_pool_size 486539264 Actual size in bytes of shared pool

SQL> select 50331648/1024/1024 as db_cache_size,486539264/1024/1024 as shared_pool_size from dual;

DB_CACHE_SIZE SHARED_POOL_SIZE

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

48 464

---我基于v$sga_resize_ops之前的测试，对于其列final_size，发现其与此__db_cache_size及__shared_pool_size,即和各个组件当前实际分配大小有关，所以我想看看直接手工调整共享池及DB CACHE大小，看不会

记录到上述视图中

SQL> alter system set "__shared_pool_size"=400m;

System altered.

SQL> alter system set "__db_cache_size"=112m;

System altered.

---可见虽然显式调整了共享池以及DB CACHE的当前实际大小，sga仍未发生重定义大小操作