Performance Tuning Basic Guidelines

** 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.