These notes are from OCP Oracle 9i Database: Performance Tuning Exam Guide by Charles Pack.
This view is a summary of waits for an event by an instance. While V$SESSION_WAIT shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all the event waits on the instance since it started. It is useful to get a historical picture of waits on the system. By taking two snapshots and doing the delta on the waits, you can determine the waits on the system in a given time interval.
This view keeps a summary all buffer waits since instance startup. It is useful for breaking down the waits by class if you see a large number of buffer busy waits on the system.
This contains overall statistics for many different parts of Oracle, including rollback, logical and physical I/O, and parse data. Data from V$SYSSTAT is used to compute ratios, such as the buffer cache hit ratio.
This contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time.
This view contains information about file read/write statistics.
This contains detailed latch usage statistics for each latch, including the number of times each latch was requested and the number of times the latch was waited for.
V$LATCH_PARENT contains statistics about parent latches. The columns of V$LATCH_PARENT are identical to those in V$LATCH.
There are multiple latches in the database for some type of latches. V$LATCH provides aggregate summary for each type of latch. To look at individual latches, query the V$LATCH_CHILDREN view.
Methods for collecting statistics:
This view contains detailed information on the system global area (SGA).
SGA contains shared pool (library cache, dictionary cache, sometimes UGA).
If using MTS, configuring large pool can reduce library cache fragmentation.
Each row in this view contains statistics for one type of item kept in the library cache. The item described by each row is identified by the value of the NAMESPACE column. Rows with the following NAMESPACE values reflect library cache activity for SQL statements and PL/SQL blocks:
* SQL AREA
This view provides object level statistics for objects in the library cache (shared pool). This view provides more details than V$LIBRARYCACHE and is useful for finding active objects in the shared pool.
V$SQLAREA contains up to 1000 characters of the SQL, along with execution statistics.
V$SQLTEXT contains the entire SQL.
V$SQL_PLAN contains the actual plan. It could be useful to capture v$sql_plan for troubleshooting purposes when a SQL stops performing as expected.
DBMS_SHARED_POOL.KEEP will pin objects in the shared pool so they do not age out. alter system flush shared_pool will not clear pinned objects.
SHARED_POOL_RESERVED_SIZE provides space for larger memory allocations and defaults to 5% of the shared pool.
V$SHARED_POOL_RESERVED shows space used, misses, etc. If request_failures > 0 then increase shared_pool_size.
V$ROWCACHE dictionary cache statistics. Usually tuned by increasing shared_pool_size.
UGA, User Global Area. Contains user session data and cursor state. When using shared server, this moves from PGA to shared pool.
LARGE_POOL_SIZE Large pool, when configured, is used for RMAN IO buffers, parallel query message buffers, and UGA when using shared server. Does not have an LRU. Setting parallel_automatic_tuning=true will enable large pool.
If library cache hit is 100%, may set cursor_space_for_time=true.
When using star transformations or DSS queries, use cursor_sharing=exact.
SQL sorts and parallel reads bypass buffer cache, which implies the buffer cache does checkpoints to keep the buffer cache in sync.
Full table scan blocks are placed at end of LRU list.
Buffer cache read of block:
- reading process uses hash function to look for block in buffer cache
-- if found, block moves to MRU
-- if not found, LRU is searched for free buffer, block is read from disk and placed in free buffer
-- if block read from disk is too recent for query read consistency, look in rollback
DBWn writes when:
- dirty list length exceeds threshold
- free block not found in LRU
- every 3 seconds
- LGWR checkpoint
- alter tablespace begin backup / offline
- object is dropped
- shutdown normal / immediate / transactional
DB_CACHE_ADVICE enables statistics gathering for V$DB_CACHE_ADVICE.
SGA_MAX_SIZE is upper bound of dynamic SGA growth.
V$BUFFER_POOL general info for all buffer pools.
V$BUFFER_POOL_STATISTICS statistics for all buffer pools.
LRU latch contention:
cache buffer lru chain
The cache buffer lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained. Set to 1/2 number of CPUs. Monitor with v$system_event.
Cache buffer chains protect the list and are used when using the list. Monitor v$latch_children for high misses or sleeps.
Redo Log Buffer is a circular buffer in the SGA that holds information about changes made to the database.
- set to at least 1MB
- no real penalty for setting a little too high
- v$sysstat "redo buffer allocation retries" should be near zero
- v$session_wait "log buffer space" should be zero
JAVA_SOFT_SESSIONSPACE_LIMIT writes warning to trace file.
JAVA_MAX_SESSIONSPACE_SIZE hard limit that kills session.
DBWR_IO_SLAVES can simulate async IO.
DB_WRITER_PROCESSES is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).
When using workarea_size_policy=auto and pga_aggregate_target, do not set:
V$PGASTAT monitors PGA usage.
V$SYSSTAT where name like 'sort%' for sorts to memory/disk.
V$SORT_SEGMENT info on every sort segment.
V$SORT_USAGE.username shows usage by user.
V$LATCH stats for SGA latching.
Wait event "latch free" is when a process tries for a latch and can not get it, then it sleeps. It spins on multi-cpu machines.
If "willing to wait", the process tries and then spins.
Latch request is "immediate" when it already has one or more latches and needs another. If this latch is not obtained, it releases all latches and starts over.
V$ROLLNAME has names of online rollback segments.
V$ROLLSTAT rollback segments statistics.
V$TRANSACTION useful for joining on UndoSegmentNumber.
What generates undo:
delete generates the most
inserts only writes the rowid
update writes row and index.
V$UNDOSTAT undo consumption.
Automatic undo pfile parameters:
How to switch to a new undo tablespace:
alter system set undo_tablespace=___________
Existing transactions will continue to use the previous tablespace.
PMON Process MONitor cleans up aborted transactions and their locks.
DML_LOCKS=0. Enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE.
delayed block cleanout - lock on the row is cleaned by the next transaction doing DML on the row. The lock is not cleaned from the row when the original locking transaction does a commit.
V$LOCK - This view lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.
V$ENQUEUE_LOCK - This view displays all locks owned by enqueue state objects. The columns in this view are identical to the columns in V$LOCK.
V$LOCKED_OBJECT - This view lists all locks acquired by every transaction on the system.
DBA_BLOCKERS - DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
DBA_WAITERS - DBA_WAITERS shows all the sessions that are waiting for a lock, but do not hold locks for which another session is waiting.
utlloctt.sql shows locks in tree display.
DBMS_LOCK allows explicit sequential processing.
breakable parse lock A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock.
A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool.
Oracle Net - tnsnames or other naming service.
init.ora - circuits, dispatchers, max_dispatchers, shared_servers, max_shared_servers, shared_server_sessions
v$queue contains information on the shared server message queues.
User Global Area moves to the shared pool or large pool.
Dispatchers are limited to ~1000 connections each.
Startup/shutdown still requires a dedicated server connection.
Index cluster contains nulls. No index columns are specified, because the index is automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.
hash cluster - direct row access via algorithm.
cluster good for - minimal key updates, tables frequently joined.
B tree index - balanced, when leaf block fills it is split in half, optionally can compress repeated values.
B tree reverse key index - can be useful with sequentially increasing keys to avoid last page contention and excessive index heights. Only useful for exact key lookups, can not be used for range scans.
Function based index - B tree or bitmap. Need query rewrite to use it.
Index Organized Table - requires primary key.
ROW OVERFLOW can split out non-PK columns.
contains logical row ID that is guess to row overflow.
may be partitioned.
may have bitmap index by using a mapping table.
complete - truncate and run query.
fast - apply changes via materialized view log.
force - uses fast if possible, else uses complete.
on commit - automatic refresh after transaction commit.
on demand - manually call dbms_mview.
segment <- extent <- block <- os block
Initial and next extent defined at time of object creation or defaults to tablespace settings.
dbms_space_admin works on locally managed tablespaces.
High water mark is stored in segment header.
High water mark is raised in five block increments.
alter table table_name deallocate unused will drop high water mark if possible.
Temporary segments must use default block size.
PCTFREE how much table space to reserve for future row updates.
PCTUSED when to put table block on free list.
row chaining - row does not fit in an empty block so it is split across blocks.
row migration - when updated row won't fit in it's block and entire row is moved with a pointer remaining.
row migration - seen in statspack as "table fetch continued row" and in v$sysstat.
alter index index_name coalesce consolidates leaf blocks.
alter index index_name rebuild online moves the index so new storage parameters may be specified.
v$object_usage stores results of monitoring table and index access.
Outlines are stored in schema OUTLN.
Outlines are grouped into categories.
DBA_OUTLINES describes all outlines in the database.
DBA_OUTLINE_HINTS describes the set of hints that make up the all the outlines in the database.
Create outlines for all SQL:
alter system set create_stored_outlines=
alter system set create_stored_outlines=FALSE; # Turns it off.
create or replace outline myoutline on SELECT ...
Use the ALTER OUTLINE statement to rename a stored outline, reassign it to a different category, or regenerate it by compiling the outline's SQL statement and replacing the old outline data with the outline created under current conditions.
alter outline myoutline rebuild;
create private outline newoutln from
alter session set use_private_outlines=true;
create or replace outline ____ from private ____; # Publish it.
histogram good for skewed data.
dbms_system.set_sql_trace_in_session(SID=>' ', serial#=>' ', sql_trace=>TRUE)
explain plan set statement_id = ' ' for SELECT ...
set autotrace traceonly explain;
v$sql_plan has actual execution plan.
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
V$SQL_PLAN_STATISTICS provides execution statistics at the row source level for each child cursor.
Consumer groups contain like users.
One plan directive may be active in the instance at a time.
The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives.
create_simple_plan (does it all in one step)
dbms_resource_manager_privs.grant_switch_consumer_group (grant to user not role)
alter system set resource_manager_plan= ____;
In pfile, the parm is resource_manager_plan.
v$rsrc_plan displays the names of all currently active resource plans.
v$rsrc_consumer_group displays data related to currently active resource consumer groups.
DBA_RSRC_PLANS lists all resource plans in the database.