Oracle9i Database: Performance Tuning (10)

Other

n Unique compressed index -> default and max value for compression is “Num of columns in the index - 1”

Non-Unique compressed index -> default and max value for compression is “Num of columns in the index”

n Full table scans will place the blocks at the LRU end of the LRU list.

n A fully normalized tables often cause a high number of table joins

n EXECUTE dbms_shared_pool.keep('package_name') pin large objects in the library cache

n Make sure that there is free memory and no cache misses before setting the CURSOR_SPACE_FOR_TIME initialization parameter to TRUE

n LRU and the checkpoint queue are the two lists used to manage the blocks in the buffer cache

n V$SYSTEM_EVENT

Ø total waits for particular events

n V$SESSION_EVENT

Ø display information on waits for an event by session

n V$SESSTAT

Ø display user session statistics

n V$SYSSTAT

Ø the number of sorts in memory, sorts on disk, and rows being sorted

Ø the number of full table scans

n V$INSTANCE_RECOVERY display the number of redo blocks that need to be processed during recovery to satisfy FAST_START_IO_TARGET

n when dirty blocks are written to disk or when a server process is searching for blocks to write to required cache buffers LRU chain latches.

n UNDO_RETENTION initialization parameter specify the time in seconds to retain undo information

n breakable parse locks is held by a statement or PL/SQL object in the library cache for every object it references

n CATBLOCK.SQL creates the DBA_LOCKS and DBA_BLOCKERS views used by the UTLLOCKT.SQL script

n a row-exclusive lock on all the updated rows and a shared lock on the table

n Shared Row Exclusive lock no longer requires an index on the foreign key column in the child table because the implementation of referential integrity has changed

n When monitoring locks , two view DBA_WAITERS and DBA_BLOCKERS created by the CATBLOCK.SQL script displays information about who is holding or waiting for which tables

n use the OVERFLOW clause when creating an index-organized table to allow data that exceeds the index's PCTTHRESHOLD value to be stored in an OVERFLOW segment

n DBMS_SPACE.USED_SPACE use to find the high-water mark and the number of blocks above the high-water mark if analyzing the table is impossible

n Q. How can you calculate the size of the recycle pool? by totaling the buffer cache blocks used by the object or by using the physical reads statistics from a tracing pool