Oracle Tuning Tips and Sample Tuning Settings
Now, you should always run the optimizer built into the Oracle database. A set of tools called
DBMS_STATS are used by Oracle to gather database statistics and analyze tables and indxes so that
Oracle can make better performing decisions at execution time.
__ 1. Create a file that contain the analyzer commands to run in sqlplus. Create a file called
analyze_mydb.sql to contain the following lines:
execute dbms_stats.gather_schema_stats (ownname=>'MYSCHEMAOWNER',\\
method_opt=>'FOR ALL COLUMNS SIZE 1', \\
granularity=>'ALL', options=>'GATHER',\\
cascade=>TRUE, degree => 16) ;
execute dbms_stats.gather_schema_stats (ownname=>'SYS',\\
method_opt=>'FOR ALL COLUMNS SIZE 1', \\
granularity=>'ALL', options=>'GATHER', \\
cascade=>TRUE, degree => 16) ;
execute dbms_stats.gather_schema_stats (ownname=>'SYSTEM',\\
method_opt=>'FOR ALL COLUMNS SIZE 1', \\
granularity=>'ALL', options=>'GATHER', \\
cascade=>TRUE, degree => 16) ;
Note: \\ used to break line for display...above is a total of 3 lines only.
The three dbms_stats commands above will gather statistics for objects
owned by Oracle users SYS, SYSTEM, and MYSCHEMAOWNER as reflected by the
ownname argument. These three commands will gather statistics on all objects.
The script shown is specifically for the database where the owner is MYSCHEMAOWNER.
__ 2. Then at a command line prompt, run:
set oracle_sid=mydb
echo @analyze_mydb | sqlplus "/ as sysdba"
__ 3. Run your app again to see if time improves.
__ 4. Repeat the above on a regular basis.
|
The following sample Oracle performance testing applies to Oracle Release 9.2.0.6 with the
latest operating system and application patches applied on the AIX, Windows, and Solaris platforms.
These parameters and values enhanced performance (response times) and gained scalability (in throughput) in our lab tests, but
they may not give your application the boost you seek. Table space usage and other system tunings (e.g., OS, network, disks, etc.)
can make huge differences in overall performance too - see other parts of this site for additional tuning tips.
AIX and Solaris Oracle Tuning Settings by area:
Update the parameters in the initmydb.
# ---------------------------------------------
# SGA memory and buffer pool parameters
# ---------------------------------------------
db_block_size = 4096
large_pool_size = 0
java_pool_size = 0
pga_aggregate_target = 1500M
db_cache_size = 600M
shared_pool_size = 600M
# ---------------------------------------------
#Process limits parameters
# ---------------------------------------------
enqueue_resources = 180000
processes = 1500
db_files = 1024
sessions = 1000
# ---------------------------------------------
# Optimizer related parameters.
# ---------------------------------------------
optimizer_index_caching = 99
optimizer_index_cost_adj = 15
optimizer_max_permutations = 1999
# ---------------------------------------------
#Cursor related parameters
# ---------------------------------------------
cursor_sharing = SIMILAR
session_cached_cursors = 500
open_cursors = 3000
cursor_space_for_time = TRUE
# ---------------------------------------------
#Undo and rollback related parameters.
# ---------------------------------------------
undo_retention = 7200
undo_tablespace = UNDO01
# ---------------------------------------------
# Logging and tracing parameters
# ---------------------------------------------
background_dump_dest = /d17/bdump
user_dump_dest = /d17/udump
core_dump_dest = /d17/cdump
max_dump_file_size = unlimited
log_checkpoint_interval = 0
log_checkpoint_timeout = 0
log_buffer = 524288
log_parallelism = 4
# ---------------------------------------------
# Statistics
# ---------------------------------------------
timed_statistics = true
# ---------------------------------------------
# Misc
# ---------------------------------------------
compatible = 9.2.0
|
Oracle on Windows Tuning Settings:
Update the parameters in the initmydb.ora file normally located in D:\oracle\ora92\database:
# ---------------------------------------------
# Instance
# ---------------------------------------------
db_block_size = 4096
db_cache_size = 500M
shared_pool_size = 125M
large_pool_size = 0
java_pool_size = 0
pre_page_sga = true
pga_aggregate_target = 500M
WORKAREA_SIZE_POLICY=AUTO
db_files = 1024
open_cursors = 500
processes = 1000
log_checkpoint_interval = 10000
log_buffer = 1048576
# ---------------------------------------------
# Optimizer
# ---------------------------------------------
cursor_sharing = similar
cursor_space_for_time = true
log_checkpoint_interval = 0
# ---------------------------------------------
# Logging to external storage; hopefully i: is a fast drive
# ---------------------------------------------
background_dump_dest = i:\mydb\mydb\bdump
user_dump_dest = i:\mydb\mydb\udump
core_dump_dest = i:\mydb\mydb\cdump
max_dump_file_size = 10240
# ---------------------------------------------
# OracleText
# ---------------------------------------------
job_queue_processes = 1
# ---------------------------------------------
# Misc
# ---------------------------------------------
compatible = 9.2.0
|
For details on each tuning parameter listed above, search on Oracle tuning FAQ.
|
