Why are some initialization parameters db_cache_size and shared_pool_size 0?

Let's look at a strange phenomenon.

SQL> Col name for A40
SQL> Col value for A30
SQL> select name, Value
2 from V $ Parameter
3 where name in
4 ('large _ pool_size ', 'java _ pool_size', 'shared _ pool_size ', 'streams _ pool_size ',
'Db _ cache_size ');

NAME value
----------------------------------------------------------------------
Shared_pool_size 0
Large_pool_size 0
Java_pool_size 0
Streams_pool_size 0
Db_cache_size 0

 

What's going on? Why is it all 0? Check the parameter db_cache_size in references. One sentence is as follows:

IfSGA_TARGETIs set: If the parameter is not specified, then the default is0(Internally determined by the Oracle database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.

This is very clear, because the 10g assm is used for automatic shared memory management.

So how can we check the system's automatic adjustment of these parameters at a certain time point?

SQL> select X. ksppinm name, Y. ksppstvl value, X. ksppdesc describ
2 from SYS. x $ ksppi X, SYS. x $ ksppcv y
3 Where X. inst_id = userenv ('instance ')
4 and Y. inst_id = userenv ('instance ')
5 and X. indx = Y. indx
6 and X. ksppinm like '% pool_size %'
7/

NAME value describ
----------------------------------------------------------------------
_ Numa_pool_size not specif aggregate size in bytes of NUMA pool
IED

_ Shared_pool_size 79691776 actual size in bytes of Shared Pool
Shared_pool_size 0 size in bytes of Shared Pool
_ Large_pool_size 4194304 actual size in bytes of large pool
Large_pool_size 0 size in bytes of large pool
_ Java_pool_size 4194304 actual size in bytes of Java pool
Java_pool_size 0 size in bytes of Java pool
_ Streams_pool_size 0 actual size in bytes of streams pool
Streams_pool_size 0 size in bytes of the streams pool

NAME value describ
----------------------------------------------------------------------
_ Io_shared_pool_size 4194304 size of I/O buffer pool from SGA
_ Backup_io_pool_size 1048576 memory to reserve from the large pool
Global_context_pool _ global application context pool size in
Size bytes

Olap_page_pool_size 0 size of the OLAP page pool in bytes

13 rows selected.

SQL> Edit
Wrote file afiedt. Buf

1 select X. ksppinm name, Y. ksppstvl value, X. ksppdesc describ
2 from SYS. x $ ksppi X, SYS. x $ ksppcv y
3 Where X. inst_id = userenv ('instance ')
4 and Y. inst_id = userenv ('instance ')
5 and X. indx = Y. indx
6 * and X. ksppinm like '% db_cache_size %'
SQL>/

NAME value describ
----------------------------------------------------------------------
_ Db_cache_size 83886080 actual size of default buffer pool for S
TANDARD block size buffers

Db_cache_size 0 size of default buffer pool for standard
Block Size Buffers
As you can see from the simhei section in the above results, these parameters starting with two underscores determine the allocation of the current SGA, which is also a parameter for dynamic memory management adjustment. Changes to these parameters will be recorded in the spfile file and will remain valid at the next database startup.

The new dynamic view v $ sga_dynamic_components added by Oracle 10 Gb allows you to view the time and type of adjustment for each dynamic component.

SQL> select component, current_size, min_size, last_oper_type,
2 last_oper_mode, to_char (last_oper_time, 'yyyy-mm-dd hh24: MI: ss') Lot
3 from V $ sga_dynamic_components
4/

Component current_size min_size last_oper_typ last_lot lot
---------------------------------------------------------------------------
--------
Shared Pool 79691776 75497472 grow immediate
20:11:08
Large pool 4194304 4194304 static
Java pool 4194304 4194304 static
Streams pool 0 0 static
Default buffer cache 83886080 83886080 shrink immediate
20:11:08
Keep buffer cache 0 0 static
Recycle buffer cache 0 0 static
Default 2 K buffer Ca 0 0 static
Che

Default 4 K buffer Ca 0 0 static

Component current_size min_size last_oper_typ last_lot lot
---------------------------------------------------------------------------
--------
Che

Default 8 K buffer Ca 0 0 static
Che

Default 16 K buffer C 0 0 static
Ache

Default 32 K buffer C 0 0 static
Ache

Component current_size min_size last_oper_typ last_lot lot
---------------------------------------------------------------------------
--------
ASM buffer cache 0 0 static

13 rows selected.