Oracle Interview Preparation Notes Part 7

Use locally managed tablespaces (using EXTENT MANAGEMENT LOCAL clause).
Sizing segments appropriately (larger extents have a performance benefit). Make all extents a multiple of 5 *

DB_FILE_MULTIBLOCK_READ_COUNT.

Monitoring and preallocating extents.

Minimize Block Visits:

" Use a larger DB block size (especially in DSS systems, smaller is better for OLTP).
" Pack rows as closely as possible into blocks.
" Prevent row migration.
Pctfree = minimum percentage of free space in a block reserved for updates to its rows.
Pctused = minimum percentage of free space before to be hit before block can be reused (sent to the freelist).
Chaining = data for a row is too large to fit in single block, it is stored in a chain of blocks.
" With rows larger than block size, this is unavoidable.
Migration = an update increases amount of data in a block, Oracle will move it to another block (more I/O as the original block is kept and points to the new block).
Often caused when pctfree is defined too low.

ANALYZE TABLE LIST CHAINED ROWS

Must first run the utlchain.sql script to create the CHAINED_ROWS table.High-Water Mark: In a table scan, Oracle reads all blocks up to this mark DBMS_SPACE package can be used to get info on space used in segments.

Rebuilding Indexes:

Can monitor space usage using ANALYZE INDEX VALIDATE STRUCTURE.
SELECT (del_lf_rows_len / lf_rows_len) * 100 from index_stats;
Should be rebuilt if deleted entries represent 20% or more of current entries.
Use ALTER INDEX REBUILD - faster because it used the fast full scan feature.
UNRECOVERABLE - does not write redo log entries during rebuild (not compatible with NOLOGGING - does not write redo log entries until LOGGING is again specified).

Optimizing Sort Operations

Index creation, order or group by, distinct, union/intersect/minus ops, analyze, sort-merge joins. Sort is done in memory if it can be done in area smaller than Sort_area_size (part of PGA), else…

1) The data is split into smaller pieces (sort runs) and sorted individually.
2) The server process writes pieces to temporary segments on disk.
3) The sort runs are merged.

SORT_MULTIBLOCK_READ_COUNT – tune for fewer, larger I/Os - can cause > merge passes.
SORT_AREA_SIZE can be set in init.ora and also can be set dynamically with Alter session.
SORT_AREA_RETAINED_SIZE – how much data is kept in the sort area after the sort (keep equal if data is likely to be referenced again, lower if sort area size is large & memory is concern).

Temporary Tablespaces:

Designating TEMPORARY tablespaces effectively eliminates serialization of space management operations – Use the TEMPORARY clause when creating a TEMP tablespace.

Storage - Set initial and next extent size to multiple of sort_area_size, pctincrease 0.
Should be striped over as many disks as possible.
Query V$SORT_SEGMENT for info on space usage.
Query V$SORT_USAGE and V$session for info on active sessions using temps.
Avoid Sorts by:
Using NOSORT to create indexes (if data is already presorted before loading).
Using UNION ALL instead of UNION (does not eliminate duplicates).
Creating indexes on columns used in ORDER BY clauses or equijoins.
ANALYZE … FOR ALL INDEXES COLUMNS.

Ratio of sort to disk over memory should be less than 5%.

Query V$sysstat for sort information (memory / to disk / rows).

Tags

nathamar81 said: