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
發表文章
