Optimizer statistics are used for:
- Accessing data and determining which index to use
- Joining tables
- Evaluating expressions and conditions
Cost Based Optimizer (CBO)
Determines plan of attack for statements based on statistics.Data stored in dictionary views are referred to as “optimizer statistics” because they are used by the CBO. The DBMS_STATS code package allows gathering and displaying this data.
When does optimizer consider data stale?
After 10 percent of the data has changed since the last statistics were gathered on the table.
Using DBMS_STATS
.GATHER_TABLE_STATS - tab, col and index stats
.GATHER_INDEX_STATS - just index stats
.GATHER_SCHEMA_STATS - all obj’s in schema
.GATHER_DATABASE_STATS - all objs in all schemas
.GATHER_DICTIONARY_STATS - on sys-owned dict obj’s
.GATHER_FIXED_OBJECTS_STATS - on dyn perf tables
.GATHER_SYSTEM_STATS - runtime sys stats with wkld.
Constants are:
AUTO_CASCADE - Automatically choose whether to gather index stats with table stats
AUTO_DEGREE - Auto-choose best parallelism degree
AUTO_INVALIDATE - Auto-choose whether to invalidate dependent cursors during stats collection
AUTO_SAMPLE_SIZE - Auto-choose best sample size for table
How to test stats before publishing
- By default, statistics are published as soon as they are gathered. To pause between gathering and publishing…:
- Use dbms_stats_set.set_[schema | table | global]_prefs to set PUBLISH to FALSE.
- Gather stats manually with dbms_stats package
- Verify stats if needed by querying DBA_TAB_PENDING STATS (or relevant table).
- Test SQL code by making the stats visible with ALTER SESSION SET optimizer_use_pending_statistics = TRUE.
- When ready to publish statistics, use dbms_stats.publish_pending_stats(…).
Segment Advisor
Identifies table and index segments that have space available for reclamation, and recommends how to reclaim it.
SQL Tuning Advisor
Analyzes code packages by running fragments of the code on a sample of data. It looks for changes that make significant performance gains and bundles them together as a “SQL Profile” and recommends its acceptance. The same profile can then later be used for the same package and is transparent to the user since no changes to the original code are required on the coder’s part.
AWR
Statistics gathered and stored in the dynamic performance views (V$ views) are not persistent and are lost when updated or when the DB shuts down. Automatic Workload Repository stores statistics from perf views across shutdowns.
- MMON (Memory Monitor) and MMNL (Memory Monitor Light) processes gather statistics for AWR from the SGA.
- MMON wakes up every 60 minutes and gathers data from dict views, perf views and optimizer, and storing them in the SYS schema (SYSAUX tablespace). This forms the AWR.
- Stats stored 8 days by default.
- Change stat gathering interval and retention period with DBMS_WORKLOAD_REPOSITORY package or via OEM.
STATISTICS_LEVEL init param
Sets level of statistics gathering.
- BASIC - disables AWR and most other diag monitoring.
- TYPICAL - Standard level. Enables AWR.
- ALL - captures all of TYPICAL plus execution plans and timing info from the OS.
Active Session History (ASH)
Data sampled at intervals from all active sessions.
- Saved to V$ views and AWR snapshots.
- See V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY.
- Sampled every second by MMNL
- Stored in circular buffer in SGA, 1 row per session.
- Can run ASH report with $ORACLE_HOME/rdbms/admin/ashrpt.sh
AWR Baselines
Good idea to create a baseline while performance is nominal, which saves that data from purging. Can be used in event of changes/failure to compare performance.
- Single baseline captured at a repeating interval.
- Repeating baseline captures multiple for some interval (like every 5 minutes) during a recurring time interval (ex. 10am-11am Fridays).
