Use of this parameter may increase your performance of the Oracle OLTP database

The following are the parameters that may cause the Performance issue in Oracle Database:

AUDIT_TRAIL is used to enable or disable database auditing. It consumer resources if you do not need them then please disabled them.

--Default Enable setting:
ALTER SYSTEM SET AUDIT_TRAIL=DB scope=spfile;
--Disable the default setting:
alter system set audit_trail=none scope=spfile;

--Need restart of the database

OPTIMIZER_DYNAMIC_SAMPLING is used to gather dynamic statistics, and the size of the sample uses to gather the statistic. The default value is 2. It will start gathering stats during the execution time of the query which increases the timing of SQL.

--For disable this feature
alter system set optimizer_dynamic_sampling=0 scope=both;

--No need to restart the database

OPTIMIZER_ADAPTIVE_PLANS used the adaptive plans. Adaptive plans are execution plans built and decided at run time based on statistics collected as the query executes. The default value is TRUE. It also runs at execution time.

-- For disable this feature:
alter system set optimizer_adaptive_plans=false scope=both;

--No need to restart the database.

PARALLEL_DEGREE_POLICY is used for the automatic degree of parallelism. This feature is used in the Enterprise Edition of Oracle. Its default value is MANUAL

-- If you have large database in TB and also have free CPU then you can enable it.
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=AUTO scope=both;

--No Need to Start the database.

SESSION_CACHED_CURSORS specifies the number of session cursors to cache. Repeated parse calls of the same SQL or PL/SQL statement cause the session cursor for that statement to be moved into the session cursor cache. The default value is 50. So, it’s better to increase its value.

-- Increase the value
alter system set session_cached_cursors=300 scope=spfile;

-- Need restart of the database.

OPTIMIZER_INDEX_COST_ADJ used 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 value is 100. In an OLTP environment, result in huge performance gain if the value is between 10 to 30.

-- Decrease the value means use index scan over full table scan.
alter system set optimizer_index_cost_adj = 25 scope=both;

--No need to restart

OPTIMIZER_INDEX_CACHING is used to adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators. The default value is 0.

-- To increase the performance
alter system set optimizer_index_caching  = 80 scope=both;

==No need to restart

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation. The default value is 128.

--In case of full table scan, we can increase its value to increase performance
alter system set db_file_multiblock_read_count = 32768;