Hi
In one of our database running on 11.2.0.2(platform HP-UX) I wanted to set the streams_pool_size parameter to zero(or reset to it's default value) and allow AMM to automatically allocate memory to streams only when it is needed and shrink back to 0 again. I tried both setting it to zero by using the alter system scope=spfile as well as alter system reset. Each time after setting these values when I queried the v$memory_dynamic_components to see if there is any memory allocated to streams I see there is some memory(current max and current min) allocated to streams, although the show parameter value is 0. When I compared all the parameters with another database ( including v$memory_dynamic_components) I see it's (streams max/min) value is 0. I dumped the pfiles for both from spfile to see what's the reason for this inconsistency and I see that the pfile for both databases has some parameters starting with DBNAME._ (like ABCDB._streams_pool_size=4194304 and XYZDB._streams_pool_size=0) .The database(ABCDB) where the parameter ABCDB._streams_pool_size is set to 4194304 returns the same value in v$memory_dynamic_components as shown below and the databse (XYZDB) where XYZDB._streams_pool_size=0 returns 0 . It seems like the streams_pool_size is derived from these parameters and setting it to 0 or resetting it does not help. I am not sure how and from where these parameters are coming from and how they are controlling the streams pool. Appreciate you help in this regard.
SQL> select component, current_size, max_size from v$memory_dynamic_components;
Component CURRENT_SIZE MAX_SIZE
------------------------- ------------ ----------
shared pool 545259520 599785472
large pool 4194304 4194304
java pool 4194304 4194304
streams pool 4194304 4194304
SGA Target 1287651328 1287651328
DEFAULT buffer cache 717225984 717225984
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
I have another database where the streams value is '0'
SQL> select component, current_size, max_size from v$memory_dynamic_components;
Component CURRENT_SIZE MAX_SIZE
------------------------- ------------ ----------
shared pool 612368384 612368384
large pool 4194304 4194304
java pool 4194304 4194304
streams pool 0 0
SGA Target 1027604480 1027604480
DEFAULT buffer cache 394264576 394264576
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
Parameters common on both databases.
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1228M
sga_target big integer 0
SQL> show parameter streams;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 2G
memory_target big integer 2G
shared_memory_address integer 0
pfile (created from spfile for) ABCDB:
ABCDB.__db_cache_size=717225984
ABCDB.__java_pool_size=4194304
ABCDB.__large_pool_size=4194304
ABCDB.__oracle_base='/oracle/product'#ORACLE_BASE set from environment
ABCDB.__pga_aggregate_target=859832320
ABCDB.__sga_target=1287651328
ABCDB.__shared_io_pool_size=0
ABCDB.__shared_pool_size=545259520
ABCDB.__streams_pool_size=4194304
*._kgl_large_heap_warning_threshold=16777216
*._trace_files_public=true
*.compatible='11.2.0.2.0'
*.control_files='/redoA/controlABCDB01.ctl','/redoB/controlABCDB02.ctl','/redoC/controlABCDB03.ctl'
*.db_block_size=16384
*.db_domain='world'
*.db_file_multiblock_read_count=16
*.db_name='ABCDB'
*.diagnostic_dest='/oracle/admin'
*.disk_asynch_io=false
*.instance_name='ABCDB'
*.java_pool_size=0
*.job_queue_processes=4
*.large_pool_size=0
*.local_listener='ABCDB'
*.log_buffer=1048576
*.log_checkpoint_interval=999999
*.log_checkpoint_timeout=1800
*.memory_max_target=2147483648
*.memory_target=2147483648
*.open_cursors=1000
*.open_links=20
*.os_authent_prefix=''
*.pga_aggregate_target=0
*.processes=500
*.recyclebin='OFF'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=false
*.service_names='ABCDB.world'
*.sga_max_size=0
*.sga_target=0
*.shared_pool_reserved_size=0
*.shared_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
Pfile for XYZDB :
XYZDB.__db_cache_size=394264576
XYZDB.__java_pool_size=4194304
XYZDB.__large_pool_size=4194304
XYZDB.__oracle_base='/oracle/product'#ORACLE_BASE set from environment
XYZDB.__pga_aggregate_target=1119879168
XYZDB.__sga_target=1027604480
XYZDB.__shared_io_pool_size=0
XYZDB.__shared_pool_size=612368384
XYZDB.__streams_pool_size=0
*._kgl_large_heap_warning_threshold=16777216
*._trace_files_public=true
*.compatible='11.2.0.2.0'
*.control_files='/redoA/controlXYZDB01.ctl','/redoB/controlXYZDB02.ctl','/redoC/controlXYZDB03.ctl
*.db_block_size=16384
*.db_domain='world'
*.db_file_multiblock_read_count=16
*.db_name='XYZDB'
*.diagnostic_dest='/oracle/admin'
*.disk_asynch_io=false
*.instance_name='XYZDB'
*.java_pool_size=0
*.job_queue_processes=4
*.large_pool_size=0
*.local_listener='XYZDB'
*.log_buffer=1048576
*.log_checkpoint_interval=999999
*.log_checkpoint_timeout=1800
*.memory_max_target=2147483648
*.memory_target=2147483648
*.open_cursors=1000
*.open_links=20
*.os_authent_prefix=''
*.pga_aggregate_target=0
*.processes=500
*.recyclebin='OFF'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=false
*.service_names='XYZDB.world'
*.sga_max_size=0
*.sga_target=0
*.shared_pool_reserved_size=0
*.shared_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'