Here are those oracle parameter:
1._hash_join_enabled
Cause:
Oracle can only perform a hash join for an equijoin. Hash join is not available with the RBO. You must enable hash join optimization, using the initialization parameter HASH_JOIN_ENABLED .
-- Oracle performs a full table scan on each of the tables and splits each into as many partitions as possible based on the available memory.
-- Oracle builds a hash table from one of the partitions (if possible, Oracle selects a partition that fits into available memory).
Oracle then uses the corresponding partition in the other table to probe the hash table. All partition pairs that do not fit into memory are placed onto disk.
-- For each pair of partitions (one from each table), Oracle uses the smaller one to build a hash table and the larger one to probe the hash table.
Action:
ALTER SYSTEM RESET <parameter_name> scope = spfile sid = '*';
2._b_tree_bitmap_plans
Cause:
This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
enable the use of bitmap plans for tables w. only B-tree indexes
The “BITMAP CONVERSION TO ROWIDS” or “BITMAP CONVERSION FROM ROWIDS” execution plan steps were generally introduced in Oracle 9i when the default value for the hidden parameter _B_TREE_BITMAP_PLANS changed from FALSE to TRUE. The “BITMAP CONVERSION” plan is not always an optimal step when converting from b-tree indexes, and it can be very inefficient access plan in some cases. Bitmap conversion to ROWIDS does not require bitmap indexes, and it’s sometimes found in cases of SQL with complex WHERE clause conditions.
The bitmap conversion to rowids is sometimes seen with star transformations, so you could start with changing the initialization parameter STAR_TRANSFORMATION_ENABLED to FALSE. In my case it didn’t help, so I had to change the hidden parameter _B_TREE_BITMAP_PLANS to FALSE.
Action:
Can ALTER SESSION: TRUE
Can ALTER SYSTEM: IMMEDIATE
You can also turn off bitmap conversion at the session level, for testing:
alter session set _b_tree_bitmap_plans=false;
As always, notify Oracle technical support before changing any hidden parameters, as they can make your database unsupported.
3._optim_peek_user_binds
Cause:
The implementation of bind variable peeking could cause performance problems, and many shops would disable bind variable peeking by opening a SR on MOSC and getting permission to set _optim_peek_user_binds=false.
This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
enable peeking of user binds
Action:
Can ALTER SESSION: TRUE
Can ALTER SYSTEM: IMMEDIATE
Note: For testing purposes, you may want to turn-off optimizer bind variable peeking and this can be done in several ways::
Re-set the _optim_peek_user_binds=false hidden parameter.
Disable new histogram creation by re-analyze using dbms_stats with the argument: method_opt=> 'for all columns size 1';
Different Parameter:
1.aq_tm_processes
Cause:
-- number of AQ Time Managers to start.
-- AQ_TM_PROCESSES enables time monitoring of queue messages. The times can be used in messages that specify delay and expiration properties.
Values from 1 to 10 specify the number of queue monitor processes created to monitor the messages.
If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not created.
Action:
Parameter type Integer
Default value 0
Modifiable ALTER SYSTEM
Range of values 0 to 10
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
2.db_block_checksum
Cause:
-- store checksum in db blocks and check during reads.
-- DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block)
and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read - only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum.
In FULL mode, Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied.
In addition, Oracle gives every log block a checksum before writing it to the current log.If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces.
Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems.
If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk.
Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead.
In the FULL mode it causes 4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL.
For backward compatibility we preserve use of TRUE (implying TYPICAL) and FALSE (implying OFF) values.
Action:
Parameter type String
Default value TYPICAL
Modifiable ALTER SYSTEM
Range of values OFF | TYPICAL | FULL
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
3.db_cache_size
Cause:
-- Size of DEFAULT buffer pool for standard block size buffers.
-- DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).
The value must be at least 4M * number of cpus * granule size (smaller values are automatically rounded up to this value).
A user-specified value larger than this is rounded up to the nearest granule size. A value of zero is illegal because it is needed for the DEFAULT memory pool of the primary block size, which is the block size for the SYSTEM tablespace.
Action:
Parameter type Big integer
Syntax DB_CACHE_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set, then the default is either 48 MB or 4MB * number of CPUs * granule size, whichever is greater.
Modifiable ALTER SYSTEM
Basic No
Obsoleted: FALSE
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
4.db_keep_cache_size
Cause:
-- Size of KEEP buffer pool for standard block size buffers.
-- DB_KEEP_CACHE_SIZE specifies the size of the KEEP buffer pool.
The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).
Action:
Parameter type Big integer
Syntax DB_KEEP_CACHE_SIZE = integer [K | M | G]
Default value 0 (DB_KEEP_CACHE_SIZE is not configured by default)
Modifiable ALTER SYSTEM
Range of values Minimum: 0 (values greater than zero are automatically modified to be either the user-specified-size rounded up to granule size or 4MB * number of CPUs * granule size, whichever is greater)
Maximum: operating system-dependent
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
5.dml_locks
Cause:
-- dml locks - one for each table modified in a transaction.
-- A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete).
DML_LOCKS specifies the maximum number of DML locks—one for each table modified in a transaction.
The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.
The default value assumes an average of four tables referenced for each transaction. For some systems, this value may not be enough.
Action:
Parameter type Integer
Default value Derived: 4 * TRANSACTIONS
Modifiable No
Range of values 20 to unlimited; a setting of 0 disables enqueues
Basic No
Real Application Clusters You must set this parameter for every instance, and all instances must have positive values or all must be 0.
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: FALSE
6.java_pool_size
Cause:
-- size in bytes of java pool.
-- JAVA_POOL_SIZE specifies (in bytes) the size of the Java pool, from which the Java memory manager allocates most Java state during runtime execution.
This memory includes the shared in-memory representation of Java method and class definitions, as well as the Java objects that are migrated to the Java session space at end-of-call.
Action:
Parameter type Big integer
Syntax JAVA_POOL_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set: 24 MB, rounded up to the nearest granule size.
Modifiable ALTER SYSTEM
Range of values Minimum: 0 (values greater than zero are rounded up to the nearest granule size)
Maximum: operating system-dependent
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
7.job_queue_processes
Cause:
-- number of job queue slave processes.
-- JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs.
It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes.
Advanced queuing uses job queues for message propagation.
You can create user job requests through the DBMS_JOB package.
Some job queue requests are created automatically.
An example is refresh support for materialized views.
If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.
Action:
Parameter type Integer
Default value 0
Modifiable ALTER SYSTEM
Range of values 0 to 1000
Basic Yes
Real Application Clusters Multiple instances can have different values.
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
8.large_pool_size
Cause:
-- size in bytes of large pool .
-- LARGE_POOL_SIZE specifies (in bytes) the size of the large pool allocation heap.
The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers,
and by backup processes for disk I/O buffers.
Parallel execution allocates buffers out of the large pool only when SGA_TARGET is set.(When Oracle derives a default value, it adds 250K for each session for the shared server if DISPATCHERS is configured. The final derived value also includes a port-specific amount of memory for backup I/O buffers. The total derived default value can either be too large to allocate or can cause performance problems.
In that case, set LARGE_POOL_SIZE to a number sufficiently small so that the database can start.)
Action:
Parameter type Big integer
Syntax LARGE_POOL_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set, but a value is not specified for LARGE_POOL_SIZE, then the default is 0 (internally determined by the Oracle database). If LARGE_POOL_SIZE is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set, 0 if both of the following are true:
The pool is not required by parallel execution
DBWR_IO_SLAVES is not set Otherwise, derived from the values of PARALLEL_MAX_SERVERS, PARALLEL_THREADS_PER_CPU, CLUSTER_DATABASE_INSTANCES, DISPATCHERS, and DBWR_IO_SLAVES.
Note that a value derived in this way does not take into account the requirements used for Automatic Storage Management files. As a general guideline, you should add 600K to the size of the SGA on a database instance using ASM.
Modifiable ALTER SYSTEM
Range of values 300 KB to at least 2 GB
(actual maximum is operating system-specific)
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
9.optimizer_index_caching
Cause:
-- optimizer percent index caching.
-- OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.
The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache.
Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer.
As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans.
The default for this parameter is 0, which results in default optimizer behavior.
Action:
Parameter type Integer
Default value 0
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 0 to 100
Can ALTER SESSION: TRUE
Can ALTER SYSTEM: IMMEDIATE
10.optimizer_index_cost_adj
Cause:
-- optimizer index cost adjustment.
-- OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost.
For example, a setting of 50 makes the index access path look half as expensive as normal.
Action:
Parameter type Integer
Default value 100
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 1 to 10000
Can ALTER SESSION: TRUE
Can ALTER SYSTEM: IMMEDIATE
11.sga_max_size
Cause:
-- max total SGA size.
-- SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
Action:
Parameter type Big integer
Syntax SGA_MAX_SIZE = integer [K | M | G]
Default value Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
Modifiable No
Range of values 0 to operating system-dependent
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: FALSE
12.sga_target
Cause:
-- Target size of SGA.
-- SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
Streams pool (STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management.
You would set minimum values if an application component needs a minimum amount of memory to function properly.
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
Log buffer
Other buffer caches, such as KEEP, RECYCLE, and other block sizes
Fixed SGA and other internal allocations
The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.
Action:
Parameter type Big integer
Syntax SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled)
Modifiable ALTER SYSTEM
Range of values 64 to operating system-dependent
Basic Yes
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
13.shared_pool_reserved_size
Cause:
-- size in bytes of reserved area of shared pool.
-- SHARED_POOL_RESERVED_SIZE specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
You can use this parameter to avoid performance degradation in the shared pool in situations where pool fragmentation forces
Oracle to search for and free chunks of unused pool to satisfy the current request.
Action:
Parameter type Big integer
Syntax SHARED_POOL_RESERVED_SIZE = integer [K | M | G]
Default value 5% of the value of SHARED_POOL_SIZE
Modifiable No
Range of values Minimum: 5000
Maximum: one half of the value of SHARED_POOL_SIZE
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: FALSE
14.shared_pool_size
Cause:
-- size in bytes of shared pool.
-- SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool.
The shared pool contains shared cursors, stored procedures, control structures, and other structures. If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution message buffers from the shared pool.
Larger values improve performance in multi-user systems. Smaller values use less memory.
Action:
Parameter type Big integer
Syntax SHARED_POOL_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set (32-bit platforms): 32 M, rounded up to the nearest granule size. If SGA_TARGET is not set (64- bit platforms): 84 M, rounded up to the nearest granule size. For considerations when dealing with database instances using ASM, see "SHARED_POOL_SIZE and Automatic Storage Management".
Modifiable ALTER SYSTEM
Range of values Minimum: the granule size
Maximum: operating system-dependent
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
15.shared_servers
Cause:
-- number of shared servers to start up .
-- SHARED_SERVERS specifies the number of server processes that you want to create when an instance is started.
If system load decreases, then this minimum number of servers is maintained.
Therefore, you should take care not to set SHARED_SERVERS too high at system startup.
Action:
Parameter type Integer
Default value 0, meaning that shared server is not on.
If you are using shared server architecture or if the DISPATCHERS parameter is set such that the total number of dispatchers is more than 0, then the default value is 1.
Modifiable ALTER SYSTEM
Range of values The value of this parameter should be less than MAX_SHARED_SERVERS.
If it is greater than or equal to MAX_SHARED_SERVERS, then the number of servers will not be self-tuned but will remain constant, as specified by SHARED_SERVERS.
Basic Yes
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
1._hash_join_enabled
Cause:
Oracle can only perform a hash join for an equijoin. Hash join is not available with the RBO. You must enable hash join optimization, using the initialization parameter HASH_JOIN_ENABLED .
-- Oracle performs a full table scan on each of the tables and splits each into as many partitions as possible based on the available memory.
-- Oracle builds a hash table from one of the partitions (if possible, Oracle selects a partition that fits into available memory).
Oracle then uses the corresponding partition in the other table to probe the hash table. All partition pairs that do not fit into memory are placed onto disk.
-- For each pair of partitions (one from each table), Oracle uses the smaller one to build a hash table and the larger one to probe the hash table.
Action:
ALTER SYSTEM RESET <parameter_name> scope = spfile sid = '*';
2._b_tree_bitmap_plans
Cause:
This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
enable the use of bitmap plans for tables w. only B-tree indexes
The “BITMAP CONVERSION TO ROWIDS” or “BITMAP CONVERSION FROM ROWIDS” execution plan steps were generally introduced in Oracle 9i when the default value for the hidden parameter _B_TREE_BITMAP_PLANS changed from FALSE to TRUE. The “BITMAP CONVERSION” plan is not always an optimal step when converting from b-tree indexes, and it can be very inefficient access plan in some cases. Bitmap conversion to ROWIDS does not require bitmap indexes, and it’s sometimes found in cases of SQL with complex WHERE clause conditions.
The bitmap conversion to rowids is sometimes seen with star transformations, so you could start with changing the initialization parameter STAR_TRANSFORMATION_ENABLED to FALSE. In my case it didn’t help, so I had to change the hidden parameter _B_TREE_BITMAP_PLANS to FALSE.
Action:
Can ALTER SESSION: TRUE
Can ALTER SYSTEM: IMMEDIATE
You can also turn off bitmap conversion at the session level, for testing:
alter session set _b_tree_bitmap_plans=false;
As always, notify Oracle technical support before changing any hidden parameters, as they can make your database unsupported.
3._optim_peek_user_binds
Cause:
The implementation of bind variable peeking could cause performance problems, and many shops would disable bind variable peeking by opening a SR on MOSC and getting permission to set _optim_peek_user_binds=false.
This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
enable peeking of user binds
Action:
Can ALTER SESSION: TRUE
Can ALTER SYSTEM: IMMEDIATE
Note: For testing purposes, you may want to turn-off optimizer bind variable peeking and this can be done in several ways::
Re-set the _optim_peek_user_binds=false hidden parameter.
Disable new histogram creation by re-analyze using dbms_stats with the argument: method_opt=> 'for all columns size 1';
Different Parameter:
1.aq_tm_processes
Cause:
-- number of AQ Time Managers to start.
-- AQ_TM_PROCESSES enables time monitoring of queue messages. The times can be used in messages that specify delay and expiration properties.
Values from 1 to 10 specify the number of queue monitor processes created to monitor the messages.
If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not created.
Action:
Parameter type Integer
Default value 0
Modifiable ALTER SYSTEM
Range of values 0 to 10
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
2.db_block_checksum
Cause:
-- store checksum in db blocks and check during reads.
-- DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block)
and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read - only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum.
In FULL mode, Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied.
In addition, Oracle gives every log block a checksum before writing it to the current log.If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces.
Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems.
If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk.
Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead.
In the FULL mode it causes 4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL.
For backward compatibility we preserve use of TRUE (implying TYPICAL) and FALSE (implying OFF) values.
Action:
Parameter type String
Default value TYPICAL
Modifiable ALTER SYSTEM
Range of values OFF | TYPICAL | FULL
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
3.db_cache_size
Cause:
-- Size of DEFAULT buffer pool for standard block size buffers.
-- DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).
The value must be at least 4M * number of cpus * granule size (smaller values are automatically rounded up to this value).
A user-specified value larger than this is rounded up to the nearest granule size. A value of zero is illegal because it is needed for the DEFAULT memory pool of the primary block size, which is the block size for the SYSTEM tablespace.
Action:
Parameter type Big integer
Syntax DB_CACHE_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set, then the default is either 48 MB or 4MB * number of CPUs * granule size, whichever is greater.
Modifiable ALTER SYSTEM
Basic No
Obsoleted: FALSE
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
4.db_keep_cache_size
Cause:
-- Size of KEEP buffer pool for standard block size buffers.
-- DB_KEEP_CACHE_SIZE specifies the size of the KEEP buffer pool.
The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).
Action:
Parameter type Big integer
Syntax DB_KEEP_CACHE_SIZE = integer [K | M | G]
Default value 0 (DB_KEEP_CACHE_SIZE is not configured by default)
Modifiable ALTER SYSTEM
Range of values Minimum: 0 (values greater than zero are automatically modified to be either the user-specified-size rounded up to granule size or 4MB * number of CPUs * granule size, whichever is greater)
Maximum: operating system-dependent
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
5.dml_locks
Cause:
-- dml locks - one for each table modified in a transaction.
-- A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete).
DML_LOCKS specifies the maximum number of DML locks—one for each table modified in a transaction.
The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.
The default value assumes an average of four tables referenced for each transaction. For some systems, this value may not be enough.
Action:
Parameter type Integer
Default value Derived: 4 * TRANSACTIONS
Modifiable No
Range of values 20 to unlimited; a setting of 0 disables enqueues
Basic No
Real Application Clusters You must set this parameter for every instance, and all instances must have positive values or all must be 0.
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: FALSE
6.java_pool_size
Cause:
-- size in bytes of java pool.
-- JAVA_POOL_SIZE specifies (in bytes) the size of the Java pool, from which the Java memory manager allocates most Java state during runtime execution.
This memory includes the shared in-memory representation of Java method and class definitions, as well as the Java objects that are migrated to the Java session space at end-of-call.
Action:
Parameter type Big integer
Syntax JAVA_POOL_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set: 24 MB, rounded up to the nearest granule size.
Modifiable ALTER SYSTEM
Range of values Minimum: 0 (values greater than zero are rounded up to the nearest granule size)
Maximum: operating system-dependent
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
7.job_queue_processes
Cause:
-- number of job queue slave processes.
-- JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs.
It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes.
Advanced queuing uses job queues for message propagation.
You can create user job requests through the DBMS_JOB package.
Some job queue requests are created automatically.
An example is refresh support for materialized views.
If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.
Action:
Parameter type Integer
Default value 0
Modifiable ALTER SYSTEM
Range of values 0 to 1000
Basic Yes
Real Application Clusters Multiple instances can have different values.
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
8.large_pool_size
Cause:
-- size in bytes of large pool .
-- LARGE_POOL_SIZE specifies (in bytes) the size of the large pool allocation heap.
The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers,
and by backup processes for disk I/O buffers.
Parallel execution allocates buffers out of the large pool only when SGA_TARGET is set.(When Oracle derives a default value, it adds 250K for each session for the shared server if DISPATCHERS is configured. The final derived value also includes a port-specific amount of memory for backup I/O buffers. The total derived default value can either be too large to allocate or can cause performance problems.
In that case, set LARGE_POOL_SIZE to a number sufficiently small so that the database can start.)
Action:
Parameter type Big integer
Syntax LARGE_POOL_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set, but a value is not specified for LARGE_POOL_SIZE, then the default is 0 (internally determined by the Oracle database). If LARGE_POOL_SIZE is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set, 0 if both of the following are true:
The pool is not required by parallel execution
DBWR_IO_SLAVES is not set Otherwise, derived from the values of PARALLEL_MAX_SERVERS, PARALLEL_THREADS_PER_CPU, CLUSTER_DATABASE_INSTANCES, DISPATCHERS, and DBWR_IO_SLAVES.
Note that a value derived in this way does not take into account the requirements used for Automatic Storage Management files. As a general guideline, you should add 600K to the size of the SGA on a database instance using ASM.
Modifiable ALTER SYSTEM
Range of values 300 KB to at least 2 GB
(actual maximum is operating system-specific)
Basic No
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
9.optimizer_index_caching
Cause:
-- optimizer percent index caching.
-- OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.
The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache.
Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer.
As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans.
The default for this parameter is 0, which results in default optimizer behavior.
Action:
Parameter type Integer
Default value 0
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 0 to 100
Can ALTER SESSION: TRUE
Can ALTER SYSTEM: IMMEDIATE
10.optimizer_index_cost_adj
Cause:
-- optimizer index cost adjustment.
-- OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost.
For example, a setting of 50 makes the index access path look half as expensive as normal.
Action:
Parameter type Integer
Default value 100
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 1 to 10000
Can ALTER SESSION: TRUE
Can ALTER SYSTEM: IMMEDIATE
11.sga_max_size
Cause:
-- max total SGA size.
-- SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
Action:
Parameter type Big integer
Syntax SGA_MAX_SIZE = integer [K | M | G]
Default value Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
Modifiable No
Range of values 0 to operating system-dependent
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: FALSE
12.sga_target
Cause:
-- Target size of SGA.
-- SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
Streams pool (STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management.
You would set minimum values if an application component needs a minimum amount of memory to function properly.
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
Log buffer
Other buffer caches, such as KEEP, RECYCLE, and other block sizes
Fixed SGA and other internal allocations
The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.
Action:
Parameter type Big integer
Syntax SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled)
Modifiable ALTER SYSTEM
Range of values 64 to operating system-dependent
Basic Yes
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
13.shared_pool_reserved_size
Cause:
-- size in bytes of reserved area of shared pool.
-- SHARED_POOL_RESERVED_SIZE specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
You can use this parameter to avoid performance degradation in the shared pool in situations where pool fragmentation forces
Oracle to search for and free chunks of unused pool to satisfy the current request.
Action:
Parameter type Big integer
Syntax SHARED_POOL_RESERVED_SIZE = integer [K | M | G]
Default value 5% of the value of SHARED_POOL_SIZE
Modifiable No
Range of values Minimum: 5000
Maximum: one half of the value of SHARED_POOL_SIZE
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: FALSE
14.shared_pool_size
Cause:
-- size in bytes of shared pool.
-- SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool.
The shared pool contains shared cursors, stored procedures, control structures, and other structures. If you set PARALLEL_AUTOMATIC_TUNING to false, then Oracle also allocates parallel execution message buffers from the shared pool.
Larger values improve performance in multi-user systems. Smaller values use less memory.
Action:
Parameter type Big integer
Syntax SHARED_POOL_SIZE = integer [K | M | G]
Default value If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.
If SGA_TARGET is not set (32-bit platforms): 32 M, rounded up to the nearest granule size. If SGA_TARGET is not set (64- bit platforms): 84 M, rounded up to the nearest granule size. For considerations when dealing with database instances using ASM, see "SHARED_POOL_SIZE and Automatic Storage Management".
Modifiable ALTER SYSTEM
Range of values Minimum: the granule size
Maximum: operating system-dependent
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
15.shared_servers
Cause:
-- number of shared servers to start up .
-- SHARED_SERVERS specifies the number of server processes that you want to create when an instance is started.
If system load decreases, then this minimum number of servers is maintained.
Therefore, you should take care not to set SHARED_SERVERS too high at system startup.
Action:
Parameter type Integer
Default value 0, meaning that shared server is not on.
If you are using shared server architecture or if the DISPATCHERS parameter is set such that the total number of dispatchers is more than 0, then the default value is 1.
Modifiable ALTER SYSTEM
Range of values The value of this parameter should be less than MAX_SHARED_SERVERS.
If it is greater than or equal to MAX_SHARED_SERVERS, then the number of servers will not be self-tuned but will remain constant, as specified by SHARED_SERVERS.
Basic Yes
Can ALTER SESSION: FALSE
Can ALTER SYSTEM: IMMEDIATE
