|
SQL> select COMPONENT,CURRENT_SIZE/1024/1024||'MB' MB from V_$SGA_DYNAMIC_COMPONENTS
2 where COMPONENT in ('DEFAULT buffer cache','KEEP buffer cache');
COMPONENT MB
------------------------------ ------------------------------------------
DEFAULT buffer cache 352MB
KEEP buffer cache 0MB
--查看内存，整齐摆放的数据并未在keep内存里，说明在default里
SQL> alter system set db_keep_cache_size=12m;
System altered.
SQL> select COMPONENT,CURRENT_SIZE/1024/1024||'MB' MB from V_$SGA_DYNAMIC_COMPONENTS
2 where COMPONENT in ('DEFAULT buffer cache','KEEP buffer cache');
COMPONENT MB
------------------------------ ------------------------------------------
DEFAULT buffer cache 340MB
KEEP buffer cache 12MB
--开辟keep内存
SQL> conn hr/hr
Connected.
SQL> set autot on
SQL> select count(*) from t1;
COUNT(*)
----------
27392
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 26589 | 85 (0)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
--重新做全表扫描，优化器虽然已经发现内存default里有数据，但是keep内存开辟了，表又是keep的，但keep里没找到数据
--所以在此强制做了物理读
--说明keep参数为0的时候，表虽然是keep的，但数据还是在default里的，keep的大小并未被asmm自动分配
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
310 consistent gets
307 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from t1;
COUNT(*)
----------
27392
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 26589 | 85 (0)| 00:00:02 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
310 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> spool off
如果考keep，该参数，还是打开了吧，recycle同理
|