These notes are from OCP Oracle 9i Database: Performance Tuning Exam Guide by Charles Pack.
V$SYSTEM_EVENT
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.
V$WAITSTAT
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.
V$SYSSTAT
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.
V$FILESTAT
This contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time.
V$TEMPSTAT
This view contains information about file read/write statistics.
V$LATCH
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
V$LATCH_PARENT contains statistics about parent latches. The columns of V$LATCH_PARENT are identical to those in V$LATCH.
V$LATCH_CHILDREN
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:
analyze
dbms_utility.analyze_schema
dbms_stats
V$SGASTAT
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.
V$LIBRARYCACHE
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
* TABLE/PROCEDURE
* BODY
* TRIGGER
V$DB_OBJECT_CACHE
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_POOL_SIZE
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:
sort_area_retained_size
bitmap_merge_area_size
create_bitmap_area_size
hash_area_size.
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:
undo_management
undo_tablespace
undo_suppress_errors
undo_retention
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.
Shared Server
Configure:
Oracle Net - tnsnames or other naming service.
init.ora - circuits, dispatchers, max_dispatchers, shared_servers, max_shared_servers, shared_server_sessions
v$shared_server_monitor
v$dispatcher
v$dispatcher_rate
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.
Application Tuning
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.
Materialized View
refresh:
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 ...
Package DBMS_OUTLN.
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;
Private outlines:
exc dbms_outln_edit.created_edit_tables
create private outline newoutln from
alter session set use_private_outlines=true;
create or replace outline ____ from private ____; # Publish it.
DBMS_STATS:
height-based
valued-based
histogram good for skewed data.
DBMS_STATS.create_stat_table
DBMS_STATS.export_schema_stats
DBMS_STATS.import_schema_stats
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.
RESOURCE MANAGER
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_pending_area
create_plan
create_simple_plan (does it all in one step)
create_consumer_group
create_plan_directive
validate_pending_area
submit_pending_area
dbms_resource_manager.switch_consumer_group_for_sess/user
dbms_resource_manager_privs.grant_switch_consumer_group (grant to user not role)
dbms_resource_manager_privs.set_initial_consumer_group
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.