58.内存管理-DBA工作的重中之重
1.SGA自动内存管理
sys@ORCL11G 2023-03-09 10:08:35> select table_name,buffer_pool from dba_tables where owner = 'SCOTT';
TABLE_NAME BUFFER_
------------------------------ -------
DEPT DEFAULT
EMP DEFAULT
BONUS DEFAULT
SALGRADE DEFAULT
Elapsed: 00:00:00.05
sys@ORCL11G 2023-03-09 10:08:47> select INDEX_NAME,BUFFER_POOL from dba_indexes where OWNER = 'SCOTT';
INDEX_NAME BUFFER_
------------------------------ -------
PK_DEPT DEFAULT
PK_EMP DEFAULT
Elapsed: 00:00:00.04
sys@ORCL11G 2023-03-09 10:08:50> show parameter sga_t;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0
sys@ORCL11G 2023-03-09 10:09:03> show parameter memory_t;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 1536M
sys@ORCL11G 2023-03-09 10:09:10>
--改变缓冲池大小
sys@ORCL11G 2023-03-09 10:11:13> alter table scott.emp storage(buffer_pool keep);
Table altered.
Elapsed: 00:00:00.05
--建议设置大小，如果内存不够，会从sga或者自动内存管理中借用内存
sys@ORCL11G 2023-03-09 10:11:26> alter system set db_keep_cache_size = 50m;
System altered.
Elapsed: 00:00:00.03 
sys@ORCL11G 2023-03-09 10:11:57> show parameter shared_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 11744051
shared_pool_size big integer 0
sys@ORCL11G 2023-03-09 10:16:24> show parameter large_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
sys@ORCL11G 2023-03-09 10:19:54> show parameter log_buff;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 6995968
自动共享内存
2.PGA自动内存管理
--10g设置
--1.sga pga分别自动管理
--2.日志内存，单独设置大小
--v$pga_target_agvice 看命中率是不是100%，PGA_TARGET_FACTOR=1，是当前PGA_TARGET_FOR_ESTIMATE内存
sys@ORCL11G 2023-03-09 10:47:26> select * from v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
81788928 .125 ON 64773120 9583 0 100 1
163577856 .25 ON 64773120 9583 0 100 0
327155712 .5 ON 64773120 9583 0 100 0
490733568 .75 ON 64773120 9583 0 100 0
654311424 1 ON 64773120 9583 0 100 0
785173504 1.2 ON 64773120 9583 0 100 0
916035584 1.4 ON 64773120 9583 0 100 0
1046897664 1.6 ON 64773120 9583 0 100 0
1177759744 1.8 ON 64773120 9583 0 100 0
1308622848 2 ON 64773120 9583 0 100 0
1962934272 3 ON 64773120 9583 0 100 0
2617245696 4 ON 64773120 9583 0 100 0
3925868544 6 ON 64773120 9583 0 100 0
5234491392 8 ON 64773120 9583 0 100 0
14 rows selected.
Elapsed: 00:00:00.00
--v$sga_target_advice 看ESTD_DB_TIME_FACTOR是否不再变化，如果没有变化，取SGA_SIZE值
sys@ORCL 2023-03-09 10:51:33> select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
456 .5 16 1.1667 12536 144 208 0
684 .75 14 1 12536 336 224 0
912 1 14 1 12536 576 224 0
1140 1.25 14 1 12536 768 224 0
1368 1.5 14 1 12536 1008 224 0
1596 1.75 14 1 12536 1200 224 0
1824 2 14 1 12536 1440 224 0
7 rows selected.
Elapsed: 00:00:00.05
--11g 12c
--sga+pga 自动内存管理， memroy_target > 0
--v$memory_target_advice 看ESTD_DB_TIME最小值，是否不再变化，不再变化取MEMORY_SIZE
sys@ORCL11G 2023-03-09 10:53:55> select * from v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
768 .5 8 1 0
1152 .75 8 1 0
1536 1 8 1 0
1920 1.25 8 1 0
2304 1.5 8 1 0
2688 1.75 8 1 0
3072 2 8 1 0
7 rows selected.
Elapsed: 00:00:00.10
--11g 12c
--如果memory_target = 0 与 10g内存管理一致
