Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, September 4, 2015

Determine and set sizing parameters for database structures

Determine and set sizing parameters for database structures:

Play with all the parameters that has "SIZE" in their name, below are some of them that has the keyword size.
 SHAIKDB>show parameter size

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size             integer     1048576
client_result_cache_size         big integer 0
create_bitmap_area_size          integer     8388608
db_16k_cache_size             big integer 0
db_2k_cache_size             big integer 0
db_32k_cache_size             big integer 0
db_4k_cache_size             big integer 0
db_8k_cache_size             big integer 0
db_block_size                 integer     8192
db_cache_size                 big integer 0
db_flash_cache_size             big integer 0
db_keep_cache_size             big integer 0
db_recovery_file_dest_size         big integer 3882M
db_recycle_cache_size             big integer 0
global_context_pool_size         string
hash_area_size                 integer     131072
java_max_sessionspace_size         integer     0
java_pool_size                 big integer 0
large_pool_size              big integer 0
max_dump_file_size             string     unlimited
object_cache_max_size_percent         integer     10
object_cache_optimal_size         integer     102400
olap_page_pool_size             big integer 0
parallel_execution_message_size      integer     16384
result_cache_max_size             big integer 5600K
sga_max_size                 big integer 1088M
shared_pool_reserved_size         big integer 14260633
shared_pool_size             big integer 0
sort_area_retained_size          integer     0
sort_area_size                 integer     65536
streams_pool_size             big integer 0
workarea_size_policy             string     AUTO


 
 
CREATE SPFILE FROM MEMORY;
 
CREATE SPFILE FROM PFILE;


SHOW PARAMETERS This SQL*Plus command displays the values of initialization parameters in effect for the current session.
SHOW SPPARAMETERS This SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE)
CREATE PFILE This SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor.
V$PARAMETER This view displays the values of initialization parameters in effect for the current session.
V$PARAMETER2 This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SYSTEM_PARAMETER This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values.
V$SYSTEM_PARAMETER2 This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SPPARAMETER This view displays the current contents of the SPFILE. The view returns FALSE values in the ISSPECIFIED column if an SPFILE is not being used by the instance.

SCOPE = SPFILE The change is applied in the server parameter file only. The effect is as follows:
  • No change is made to the current instance.
  • For both dynamic and static parameters, the change is effective at the next startup and is persistent.
This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY The change is applied in memory only. The effect is as follows:
  • The change is made to the current instance and is effective immediately.
  • For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
For static parameters, this specification is not allowed.
SCOPE = BOTH





The change is applied in both the server parameter file and memory. The effect is as follows:
  • The change is made to the current instance and is effective immediately.
  • For dynamic parameters, the effect is persistent because the server parameter file is updated.
For static parameters, this specification is not allowed.

 


No comments: