By Peter Teoh ([email protected], http://web.singnet.com.sg/~petermag/oracle.html)
** Redo Log files ensure that redo log are allocated on the fast disk,
with minimum activities.
** Temporary tablespaces ensure that temporary tablespaces are allocated
on the fast disk, with minimum activities.
** Fragmentation of tablespaces defragmentize tablespaces, equal
blocksize for INITIAL and NEXT extents.
** Shared Pool Sizing 1/3 or more of total physical memory, and check
for thrashing/paging/swapping activity.
** DB_BLOCK_BUFFER to enable buffering of data from datafiles during
query and updates/inserts operation.
** Use BIND variables to minimize parsing of SQL and enable SQL area
reuse, and standardize bind-variable naming conventions.
** Identical SQL statements literally identical to enable SQL area
reuse.
** Initial/Next Extents sizing ensure initial and next are
the same. Should be as small as possible to avoid wastage of
spaces, but at the same time large enough to minimize time spent in frequent
allocation.
** PCTINCREASE zero to ensure minimum fragmentization.
** Small PCTUSED and large PCTFREE to ensure sufficient spaces
for INSERT intensive operation.
** Freelist groups large values to ensure parallelization of
INSERT-intensive operation.
** INITRANS and MAXTRANS large values to enable large number
of concurrent transactions to access tables.
** Readonly tablespaces to minimize latches/enqueues resources,
as well as PINGING in OPS.
** Create indexes for frequently accessed columns especially
for range scanning and equality conditions in where clause.
** Use hash indexes if equality conditions is used, and no range
scanning involved.
** If joining of tables is used frequently, consider Composite
Indexes.
** Use Clustered tables columns allocated together.
** Create Index-Organized Tables when data is mostly readonly
to localize both the data and indexes together.
** Use PARALLEL hints to make sure Oracle parallel query is used.
** IO slaves to enable multiple DB writers to write to disks.
** Minextents and Maxextents sizing ensure as large as possible
to enable preallocation.
** Avoid RAID5 IO intensive (redo log, archivelog, temporary
tablespace, RBS etc)
** MTS mode to optimize OLTP transaction, but not BATCH environment.
** Partition Elimination to enable unused tablespaces partition
to be archived.
** Performance hit seriously when bitmap indexes used in table
with heavy DML. Might have to drop and recreate the bitmap indexes.
** Increase LOG_SIMULTANEOUS_COPIES minimize contention for
redo copy latches.
** In SQLLoader - using direct path over conventional path loading.
** Using parallel INSERT... SELECT when inserting data that already
exists in another table in the database faster than parallel direct loader
using SQLLoader.
** Create table/index using UNRECOVERABLE option to minimize
REDO LOG updating. SQLloading can use unrecoverable features, or ARCHIVELOG
disabled.
** Alter index REBUILD parallel 2 to enable 2 parallel processes
to index concurrently.
** Use large redo log files to minimize log switching frequency.
** Loading is faster when using SQLLOADING if data source is
presorted in a file.
** Drop the indexes, and disable all the constraints, when using
SQLloader. Recreate the indexes after SQLloader has completed.
** Use Star Query for Data Warehousing-like application: /*+
ORDERED USE_NL(facts) INDEX(facts fact_concat) */ or /*+ STAR */.
** Using Parallel DDL statements in:
** CREATE INDEX
** CREATE TABLE ... AS SELECT
** ALTER INDEX ... REBUILD
** The parallel DDL statements for partitioned tables and indexes are:
** CREATE TABLE ... AS SELECT
** CREATE INDEX
** ALTER TABLE ... MOVE PARTITION
** ALTER TABLE ... SPLIT PARTITION
** ALTER INDEX ... REBUILD PARTITION
** ALTER INDEX ... SPLIT PARTITION
** Parallel analyze on partitioned table - ANALYZE {TABLE,INDEX}
PARTITION.
** Using Asynchronous Replication instead of Synchrnous replication.
** Create snapshot log to enable fast-refreshing.
** In Replication, use parallel propagation to setup multiple
data streams.
** Using ALTER SESSION
.HASHED_JOINED_ENABLED.
** Using ALTER SESSION
. ENABLE PARALLEL DML.
** Use ANALYZE TABLE
.ESTIMATE STATISTICS for large tables, and
COMPUTE STATISTICS for small table, to create statistics to enable Cost-Based
Optimizer to made more accurate decision on optimization technique for
the query.
** To reduce contention on the rollback segments, at most 2 parallel
process transactions should reside in the same rollback segment.
** To speed up transaction recovery, the initialization parameter
CLEANUP_ROLLBACK_ENTRIES should be set to a high value approximately equal
to the number of rollback entries generated for the forward-going operation.
** Using raw devices/partition instead of file system partition.
** Consider increasing the various sort related parameters:
** sort_area_size
** sort_area_retained_size
** sort_direct_writes
** sort_write_buffers
** sort_write_buffer_size
** sort_spacemap_size
** sort_read_fac
** Tune the database buffer cache parameter BUFFER_POOL_KEEP
and BUFFER_POOL_RECYCLE to keep the buffer cache after use, or age out
the data blocks to recycle the buffer cache for other data.
** Larger values of LOG_BUFFER reduce log file I/O, particularly
if transac-tions are long or numerous. The default setting is four times
the maximum data block size for the host operating system.
** DB_BLOCK_SIZE should be multiple of OS block size.
** SHARED_POOL_SIZE The size in bytes of the area devoted to
shared SQL and PL/SQL statements.
** The LOCK_SGA and LOCK_SGA_AREAS parameters lock the entire
SGA or particular SGA areas into physical memory.
** You can force Oracle to load the entire SGA into main memory
by set ting the PRE_PAGE_SGA=TRUE in the init.ora file. This load slows
your startup process slightly, but eliminates cache misses on the library
and data dictionary during normal runs.
** Enable DB_BLOCK_CHECKSUM if automatic checksum on datablocks
is needed, performance will be degraded slightly.
** Use EXPLAIN PLAN to understand how Oracle process the query
utlxplan.sql.
** Choose between FIRST_ROWS or ALL_ROWS hint in an individual
SQL state-ment to determine the best response time required for returning
data.
** Use bitmap indexes for low cardinality data.
** Use full-table scan when the data selected ranged over a large
percentage of the tables.
** Use DB_FILE_MULTIBLOCK_READ_COUNT to enable full table scans
by a single multiblock read. Increase this value if full table
scan is desired.
** Check if row migration or row chaining has occurred - running
utlchain.sql.
** Choose between offline backup or online backup plan.
** Tuning memory in Solaris in /etc/system file:
? shmsys:shminfo_shmmax
? shmsys:shminfo_shmmin
? shmsys:shminfo_shmni
? shmsys:shminfo_shmseg
? semsys:seminfo_semmap
? semsys:seminfo_semmni
? semsys:seminfo_semmns
? semsys:seminfo_semmnu
? semsys:seminfo_semmsl
? semsys:seminfo_semopm
? semsys:seminfo_semume
? semsys:seminfo_semusz
? semsys:seminfo_semvmx
? semsys:seminfo_semaem
? msgsys:msgmap
? msgsys:msgma
? msgsys:msgnb
? msgsys:msgmni
? msgsys:msgssz
? msgsys:msgtql
? msgsys:msgseg
0) for super machines with 4 GB of ram & swap > 12 GB
set shmsys:shminfo_shmmax=3221225471
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=1024
set shmsys:shminfo_shmseg=100
set semsys:seminfo_semmni=1024
set semsys:seminfo_semmns=163840
set semsys:seminfo_semmsl=160
set semsys:seminfo_semmap=163840
set semsys:seminfo_semmnu=163840
set msgsys:msginfo_msgmap=163840
set msgsys:msginfo_msgmax=6144
set msgsys:msginfo_msgmni=640
set msgsys:msginfo_msgssz=64
set msgsys:msginfo_msgtql=640
set msgsys:msginfo_msgseg=32768
1) For high end machines with 2 GB of RAM & 6 GB of swap, we
recommend the following:
set shmsys:shminfo_shmmax=1073741824
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=250
set shmsys:shminfo_shmseg=100
set semsys:seminfo_semmni=750
set semsys:seminfo_semmns=75000
set semsys:seminfo_semmsl=100
set semsys:seminfo_semmap=75000
set semsys:seminfo_semmnu=75000
set msgsys:msginfo_msgmap=75000
set msgsys:msginfo_msgmax=6144
set msgsys:msginfo_msgmni=640
set msgsys:msginfo_msgssz=64
set msgsys:msginfo_msgtql=640
set msgsys:msginfo_msgseg=32768
2) For medium end machines with 1 GB of RAM & 3 GB of swap we
recommend the following:
set shmsys:shminfo_shmmax=536870912
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=150
set shmsys:shminfo_shmseg=50
set semsys:seminfo_semmni=500
set semsys:seminfo_semmns=50000
set semsys:seminfo_semmsl=100
set semsys:seminfo_semmap=50000
set semsys:seminfo_semmnu=50000
set msgsys:msginfo_msgmap=50000
set msgsys:msginfo_msgmax=2048
set msgsys:msginfo_msgmni=512
set msgsys:msginfo_msgssz=32
set msgsys:msginfo_msgtql=512
set msgsys:msginfo_msgseg=16384
3) For small end machines with 512 MB of RAM * 1.5 - 2 GB swap we
recommend the following:
set shmsys:shminfo_shmmax=134217728
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=50
set semsys:seminfo_semmni=250
set semsys:seminfo_semmns=25000
set semsys:seminfo_semmsl=100
set semsys:seminfo_semmap=25000
set semsys:seminfo_semmnu=25000
set msgsys:msginfo_msgmap=25000
set msgsys:msginfo_msgmax=1024
set msgsys:msginfo_msgmni=224
set msgsys:msginfo_msgssz=16
set msgsys:msginfo_msgtql=224
set msgsys:msginfo_msgseg=8192