Oracle 19c

Performance Tuning Workshop

Day Two
Day One Recap
• Which optimizer features of 19c are disabled for on-premise

• Ans) Will share a slide on this which explains this well - begining of next class

• Have gathered new statistics but explain plan has not changed

• Ans) New statistics will be used only when the statement is next hard parsed

• How can you exclude a table from auto gather stats?

• Ans) Can lock the table statistics so that statistics are not gathered - or can set the PUBLISH preference to FALSE even if statistics are
gathered

• Where is the synopses information is stored?

• Ans) [Link]$_OPTSTAT_SYNOPSIS$ and [Link]$_OPTSTAT_SYNOPSIS_HEAD$ tables in SYSAUX tablespace

• Any specific parameter needs to be enabled to enable auto gather stats after a bulk upload?

• Ans) NO - but 19c real-time statistics only available for certain platforms - otherwise statistics are gathered automatically for CTAS
and INSERT statements with APPEND hint
• Can we flush an execution plan only?

• Ans) We flush the SQL statement - which means both the parse tree and execution plan via ALTER SYTEM FLUSH
SHARED_POOL or even single SQL using DBMS_SHARED_POOL.PURGE

• can you please elaborate more on db_file_multiblock_read_count parameter should we set the value manually or
Oracle sets it automatically

• Ans) This will be covered in session on tuning database buffer cache

• For the production db how to update stats without flush the shared pool

• Ans) When we flush the shared pool statistics are not "updated" - but when the statement is reloaded or reparsed
(or if Oracle automatically invalidates cursor), any fresh statistics will be used and execution plans can change

• How do we change the default behaviour to PENDING?

• Ans) we can execute - DBMS_STATS.set_table_prefs('SCOTT', 'EMP', 'PUBLISH', 'false') - or even use set_global_prefs
• In the range partition can we exclude partitions which are generally used for historical data and that data is not [Link] i want
to gather only for new partitions

• Ans) Use Incremental Statistics

• When gathering statistics do table get locks? or contention while the query is running?when gathering statistics will do the
library cache lock on table?

• Ans) Not really - it is just a table scan which happens. But look online and you will see some bugs encountered related to
library cache locks for partitioned tables - I guess when synopses are being updated in data dictionary

• The column statistics is a new feature in 19c?

• Ans) No this has been around since 11g R2

• What is the driving table and cam I change the order of the join

• Ans) We will cover this in Table Joins section of the course – but you can use the ORDERED hint to change the join order

• I did not understand Real-time statistics part – can you explain this again as well as Dynamic Statistics – my results are different
• Ans) Will do a demo
 What is an Execution Plan?

• The execution plan for a SQL statement is a set of instructions

• Tell the database how to access the data and join it together
• Plans come in two varieties:
• Explain
• Execution
• An explain plan predicts how Oracle will process your query
• An execution plan describes the steps it actually took
 Why Do Execution Plans Change?
• Stale or missing statistics
• Have gathered fresh statistics (The Monday morning syndrome…)
• Optimizer engine has changed after a database upgrade
• Index has been added or dropped or Table structure has changed
• Database optimizer related [Link] parameters have been changed
• Parsed representation of the SQL statement is not in the Library Cache –
statements are aged out
• Reparsing the same SQL statement now leads to generation of a new plan
 It’s All About the Data

• Data quantity has changed (significantly)

• Maybe much more rows inserted – or maybe many rows deleted
• Data distribution has changed via an update (data is now skewed)
• These can be the cause of bad performance because:
• Join order has changed
• Join type has changed
• Table access method has changed
 Statistics – Food for the Optimizer
• In order for the Cost Based Optimizer to accurately determine the cost for an
execution plan, it must have some information
• Information about all of the objects (tables and indexes) accessed in the SQL statemen
• Information about the system on which the SQL statement will be run
• This information is commonly referred to as statistics
• Stored in the Data Dictionary of the database
Some Myths:
• The first is that there’s a common misperception that if no new statistics are gathered
and assuming nothing else is altered in the database, that execution plans must always
remain the same
• That by not collecting statistics, one somehow can ensure and guarantee the database
will simply perform in the same manner and generate the same execution plans
Data Dictionary and Optimizer
 How do we gather optimizer statistics
• By default, Oracle Database uses automatic optimizer statistics collection
• Database automatically runs DBMS_STATS to collect optimizer statistics for all
schema objects for which statistics are missing or stale
• You can also update and manage optimizer statistics by manually executing
DBMS_STATS
• Also, when optimizer statistics are missing, stale, or insufficient, the database
automatically gathers dynamic statistics during a parse (depends on level of
dynamic sampling)
 19c New Stuff
• Automatic optimizer statistics collection job which calls DBMS_STATS package
runs in predefined maintenance windows
• For volatile tables statistics can go stale between two consecutive executions of
such automatic statistics collection jobs
• The presence of stale statistics could potentially cause performance problems
because the optimizer is choosing sub-optimal execution plans
• The new feature introduced in Oracle 19c called High-Frequency Automatic
Optimizer Statistics collection complements the standard automatic statistics
collection job
• Statistics also collected for CONVENTIONAL DML statements – not just CTAS and
INSERT with APPEND hint – Real-time Statistics
• Column Group or Extended Statistics
• Incremental Statistics for Partitioned Tables
• Gathering Statistics Preferences
• Pending and Published Statistics
• Dynamic Statistics
• Re-optimization
• SQL Plan Directives
• Statistics Advisor
 Adaptive Query Optimization
• One of the biggest changes to the optimizer in Oracle Database 12c is Adaptive
Query Optimization
• To make run-time adjustments to execution plans
• Discover additional information that can lead to better statistics
• Two distinct aspects in Adaptive Query Optimization
• Adaptive Plans which focuses on improving the initial execution of a query
• Adaptive Statistics which uses additional information to improve subsequent
executions
• Adaptive plans enable the optimizer to defer the final plan decision for a
statement until execution time
• Optimizer has a default plan or initial plan choice
• During execution, the statistics collector gathers information about the execution
and buffers a portion of rows
• At runtime if significant differences are detected between cardinality estimates
versus actual number of rows returned then use the ‘alternative sub-plans’
Optimizer calculates the “Inflection Point” at optimization time

Statistics Collector buffers rows as query starts

If rows buffered exceed Inflection Point change the execution plan

By default, the explain plan command will show only the initial or default plan chosen by the optimizer

Whereas the DBMS_XPLAN.DISPLAY_CURSOR function displays only the final plan used by the query
• To see all of the operations in an adaptive plan, including the positions of the
statistics collectors, the additional format parameter ‘+adaptive’ must be
specified in the DBMS_XPLAN functions
• In this mode an additional notation (-) appears in the id column of the plan,
indicating the operations in the plan that were not used (inactive)
Oracle 12c Release 1

Default value was TRUE for BOTH

 Oracle 12c Release 2
Back port available for [Link]

Optimizer Adaptive Statistics known to have caused significant performance problems after 12c upgrades
for some customers

Default TRUE Default FALSE

 Influencing the Optimizer

• Statistics gathered via DBMS_STATS

• Hints
• SQL Profiles
• SQL Plan Management
• Database Parameters
 Hints
• A hint is an instruction to the optimizer to follow an ‘application developer desired’ execution
plan
• Make decisions for the optimizer because you have more in-depth knowledge about data
distribution patterns
• In a test or development environments, hints are useful for testing the performance of a specific
access path- test use of a specific index when there are several indexes defined on the same table
• Changes in the database or host environment can make hints obsolete and even have negative
consequences
• Recommended to use tools like SQL Tuning Advisor, SQL plan management, and SQL Performance
Analyzer, to address performance problems not solved by the optimizer before considering hints

SELECT /*+ FIRST_ROWS(10) */ * FROM employees;

SELECT /* This is a COMMENT */ * FROM employees;
• The database ignores incorrectly specified hints
 Hints

• Single-table Hints
SELECT /*+ INDEX (emp emp_department_ix)*/ employee_id, department_id
FROM emp
WHERE department_id > 50;

• Multi-table hints
SELECT /*+ LEADING(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;

• Query Block
SELECT /*+ STAR_TRANSFORMATION */ s.time_id, s.prod_id, s.channel_id
FROM sales s, times t, products p, channels c
WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id
AND s.channel_id = c.channel_id AND c.channel_desc = 'Tele Sales’;

Hints override any instance-level or session-level parameter settings

 Hints

Hints for join orders, access paths, join types

SELECT /*+ FULL(e) */ employee_id, last_name
FROM [Link] e
WHERE last_name LIKE :b1;

SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id

FROM employees
WHERE department_id > 50;

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * [Link]

FROM orders h, order_items l
WHERE l.order_id = h.order_id;
 19c Hint Usage Report
• In earlier releases no error was reported if an incorrect hint was used or if there
was any syntax error in the hint usage
• Tuning a sub-optimal execution plan became difficult and sometimes we are left
wondering why a full table scan is still occurring when an INDEX hint has been
specified!
• The database did not record or issue any error messages for hints that it ignores
• Hint Usage Report feature is enabled by default when using any DBMS_XPLAN
functions like DISPLAY, DISPLAY_CURSOR, DISPLAY_WORKLOAD_REPOSITORY or
DISPLAY_SQL_PLAN_BASELINE
 SQL Profiles
• SQL Profile is a database object
• Collection of auxiliary statistics on a query, including all tables and columns
referenced in the query
• The database can use the auxiliary information to improve execution plans
• Unlike hints, no changes to application source code are required when using
profiles
• The profile is stored in the data dictionary once accepted -
DBA_SQL_PROFILES
• The optimizer uses this information during optimization to determine the
most optimal plan
 SQL Profiles
• Profiles can be created on two occasions :
• When SQL Tuning Advisor is run manually on high resource SQL statements in
comprehensive mode
• When SQL Tuning Advisor runs automatically in default maintenance window
• Optimizer has time to do a more comprehensive analysis
• Performs additional analysis to determine whether the execution plan produced
can further be improved
• Create and Execute a Tuning Task
• One of the recommendations of the tuning task could be to implement a SQL
Profile
• “Tuning without touching the code” via SQL Profiles
• The SQL Profile is not "locking a plan in place", but rather giving the optimizer yet
more bits of information it can use to get the right plan – Tom Kyte
 SQL Quarantine
• SQL Quarantine can be used to eliminate the overhead of runaway queries
• Resource Manager can be configured to terminate long running statements – say
running longer than 10 minutes
• However, the statement may run repeatedly before being terminated, wasting 10
minutes of resources each time it is executed
• Configure the database to automatically quarantine the plans for SQL statements
terminated by Database Resource Manager for exceeding resource limits
• ‘Costly’ statement now runs just once before it is quarantined
• Quarantined SQL statement is then placed on a blacklist of plans that the database
will not execute for this statement
• The execution plan is quarantined, not the statement itself
• Specify if all plans or only single plan is quarantined
• Plans are quarantined and not the SQL statement
• DBMS_SQLQ PL/SQL package to force a plan to be quarantined as well as set
configuration options for a quarantined plan
• V$SQL.SQL_QUARANTINE column indicates whether a plan was quarantined for a
statement
• The AVOIDED_EXECUTIONS column indicates how often Oracle Database
prevented the statement from running with the quarantined plan
• What do we do once SQL is quarantined?
• Try and manually tune the statement so that a new plan is generated
• Does the new plan not exceed specified resource limits
• No - Allowed to execute again
• Yes – plan goes back into quarantine
Disable the quarantine

Now the query is not prevented from immediate execution

Is cancelled once the Resource Manager plan directive related to elapsed time takes effect
 Shared Pool
SGA

Log
Buffer Cache
Buffer
 Components of Shared Pool

• Library Cache
• Stores the parse tree and execution plans for SQL statements
• Stores PL/SQL code
• JAVA classes
• Data Dictionary Cache
• Store data dictionary information
• Usernames, segment information, profile data, tablespace information, sequence
numbers etc
• Server Result Cache
• When configured stores query result sets
• Subsequent queries retrieve results directly from cache
• Space allocation in the shared pool is based on LRU algorithm
• Space in shared pool is not unlimited – SHARED_POOL_SIZE
• Oldest objects and cursors aged out of the shared pool as more objects
are loaded into the shared pool
• New objects require memory allocation
• Objects are made up of chunks of memory (1k and 4k)
• Sharing of cursors
• Cursors not shared – hard parsing
• Sharing of cursors – soft parsing
• LRU operations in shared pool is protected by a latch or mutex
 Latches and Mutexes

• Latch is an inexpensive type of lock to control access to shared data

structures stored in the SGA
• Latches are required to gain access to the shared pool
• Mutexes (Mutual Exclusion Object) are more granular lower level object
locks
• Latch like a door to get into the shared pool memory
• Mutex is like a revolving door – very short duration
• Both latches and mutexes provide shared access for reads and exclusive
access for any update operations
• There are multiple latches – not unlimited latches!
• That is why we have latch contention – eg. Library Cache Latch
 Latches and Mutexes

• If process cannot acquire a latch it waits – called a miss

• If process cannot acquire a mutex it waits – called a sleep
• latch: library cache pin and latch:shared pool
• cursor:mutex (S or X)
• One process is attempting to obtain exclusive access to the mutex while it is being
changed by another process
• V$LATCH_MISSES
• V$MUTEX_SLEEP
 Important Parameters
• SESSION_CACHED_CURSORS
• Is used to improve the performance of “soft parse”
• As new SQL statement is issued, Oracle checks the library cache for parsed SQL statements
• session_cached_cursors can be used to pin the cursors for the most popular SQL
• The latch associated with searching the library cache for a cursor being present or not is
avoided
• Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor
• Default value is 50

• CURSOR_SHARING
• Exact (default) – SQL statements must be identical to share cursors
• Force – SQL statements which are similar (identical except values of some literals) will share
cursors by using bind variables
• OPEN_CURSORS
• A particular session may execute many SQL statements
• Governs the total number of open cursors for any given session
• Sets limit to the number of cursors a single session can open
• Like a governor to prevent sessions from consuming too much library
cache memory
• Default value of 50 may be too small for certain types of applications
• ora-1000 maximum open cursors exceeded
• SHARED_POOL_SIZE also dependent on this setting so consideration to
begin before increasing
• SHARED_POOL_RESERVED_SIZE
• If shared pool is fragmented then contiguous free space may not be available to load large
packages
• Reserves contiguous memory in shared pool to satisfy requests for large chunks of memory
• Reserves this pool of memory by taking it from the memory allocated to the shared pool
• Avoids performance degradation in those situations where shared pool fragmentation forces
Oracle to search for and then free chunks of unused pool memory to satisfy the current
request
• By default Oracle reserves 5% of the value of the SHARED_POOL_SIZE
• Handles any cursor which requires more than 4KB of memory
• V$SHARED_POOL_RESERVED
• The reserved pool is too small when the value for REQUEST_FAILURES is more than zero
and constantly increasing
 Private and Shared SQL Area
• Each SQL statement has a private and shared SQL area
• The private SQL area contains information specific to individual user session – like bind
variables
• The shared SQL area contains the parse tree and execution plan for the SQL statement
• SELECT * FROM employees has a plan and parse tree stored in one shared SQL area
• SELECT * FROM departments has a plan and parse tree stored in a separate shared SQL
area
• Cursor Sharing - Multiple private SQL areas can reference a single shared SQL area
• A shared SQL area that is accessed by multiple statements is known as a shared cursor
 SQL Statement Execution

Parsing
Syntax check Soft Parse
Parsed representation of the SQL statement EXISTS

Semantic check

Search the shared pool for an existing parsed Shared pool check
representation of the SQL statement

NOT FOUND!
Hard Parse
Generation of multiple execution plans
Select one with lowest cost Optimization

Generation of query plan

Row source
generation

Execution
 Parse Phase
• When application issues a SQL statement a parse call is made
• Parse phase checks for semantics
• Checks for syntax
• Checks for privileges
• Searches for matching cursor in the shared pool
• Allocates a private SQL area in PGA
• Can we do a Soft Parse or a Hard Parse?
 Parse Phase

• The text of the SQL statement is hashed

• The database looks for a matching hash value for an existing SQL statement in the
shared pool
• No matching hash value – hard parse
• Matching hash value found
• Then do a text match
• If text match fails – hard parse
• If text match succeeds, then determine whether the SQL can share an existing parent
cursor
• Then the parent cursor has an associated child cursor – soft parse
• Every parsed SQL statement has a parent cursor and one or more child cursors
• A very high resource-intensive aspect of hard parsing is accessing the library
cache and data dictionary cache numerous times to check the data dictionary
• This is to build the parse tree and execution plan
• When the database accesses the shared pool it acquires a latch on required
objects so that their definition does not change during the check
• Latch contention increases statement execution time and decreases concurrency
• Hard parses incur high CPU and memory overhead and can create serious
performance problems
• Web applications that accept user input via a form, and then generate SQL
statements dynamically where only literal values change – candidates for
excessive hard parses
 Cursor Sharing

• Soft Parse - SQL statement is executed and matching cursor from previous execution
found in the shared pool
• Cursor is shared and performance is optimized
• Soft parse still requires syntax and privileges checking but does not need to generate an
execution plan
• Hard parse – SQL statement is executed for the first time and no matching cursor is
obviously available in shared pool
• Generates parse tree and execution plan which is a resource intensive process
• SQL statement gets one hard parse the first time that it is executed and one soft parse
for each additional session that uses the statement by sharing the cursor
• If cursor which was earlier shared is aged out of the shared pool it leads to hard parsing
all over again
 Parent and Child Cursors

Parent Cursor
• The first cursor is numbered 0 (the parent)
• Parent cursor contains the SQL text for the cursor
Child Cursor
• Cursors that reference the same exact SQL_TEXT - but are different in some fashion
• Child cursor stores execution plan, statistics and environment information like bind variables about
the execution of the statement
• User SCOTT issues ‘select * from myobjects;’
• USER DEMO also issues ‘select * from myobjects;’
• Same SCOTT uses different settings for optimizer related parameters in different sessions while still executing the
identical SQL statement
• SQL_ID in V$SQL will be the same
• CHILD_NUMBER will be different
• PARSING_SCHEMA_NAME will be different
• Parent Cursor 0 - 1 (first child), 2 (second child) ….
 Parent and Child Cursors
• Execute some SQL – generate a hash value
• Search library cache for parent cursor with matching hash value
• Once found it scans the list of versions (or children) one by one
• Find a match which has the correct set of conditions
• If it does not find one, then it creates a new version
• V$SQLAREA contains one row for each parent cursor
• V$SQL contains one row for each child cursor
• Multiple child cursors could mean possibly multiple execution plans
• Same SQL statement can have several versions (children)
• v$sql_shared_cursor shows the reason why the statement cannot be shared
• None of these SQL statements can use the same shared SQL area

• SELECT * FROM employees;

• SELECT * FROM Employees;
• SELECT * FROM employees;
• SELECT count(1) FROM employees WHERE manager_id = 121;
• SELECT count(1) FROM employees WHERE manager_id = 247;
• Unless we use CURSOR_SHARING=FORCE where similar statements can share SQL
areas
 Bind Variables

• Any change in a statement in terms of case, white spaces, new lines etc
will result in a new hash value and thus Oracle assumes it is a new
statement
• This will require an expensive parse
• Identical statements in all aspects but only literals are different
• Still requires a hard parse
• SELECT * FROM EMP WHERE JOB=‘CLERK’;
• SELECT * FROM EMP WHERE JOB=‘SALESMAN’;
• SELECT * FROM EMP WHERE JOB=‘MANAGER’;
• Replace hard coding with bind variables
• SELECT * FROM EMP WHERE JOB= :v_job;
 Using Bind Variables

DECLARE
v_column_name VARCHAR2(30):='Employee_id';
BEGIN
FOR i IN 100..105
LOOP
EXECUTE immediate 'delete from emp where '||v_column_name||'=‘||:i;
END LOOP ;
END;
/
HASH_VALUE and PLAN_HASH_VALUE

• Oracle will apply a hashing algorithm to the SQL statement and generates a hash value
• This hash value is what is used for identifying statements and searching the shared pool
for matches
• V$SQL column HASH_VALUE
• Two statements could be different but share the same execution plan
• V$SQL column PLAN_HASH_VALUE
• SELECT * FROM emp WHERE empid=1001;
• SELECT * FROM EMP WHERE empid=1001;
• Execution plan for both statements is the same so PLAN_HASH_VALUE is also the same
• HASH_VALUE will be different for each statement as the optimizer considers each
statement to be ‘different’
• Hash algorithm for each statement will generate a different hash value
Identify ‘Duplicate’ SQL Statements
 SQL with high Version Count

• SQL with high version count can cause performance problems

• Library cache latches and mutex cursor pin S wait for X
• Reuse of cursors not happening even with bind variables
• For repeated execution of the similar SQL, child cursor could not be reused
and thus a large number of new child cursors are generated
• Optimizer attempts to match the query text in the library cache using the
hash key generated examining SQL text
• Finds ‘parent’ cursor
• Has to scan child cursors or versions to find the right match
• Creating new child cursors means expensive hard parsing,
and searching the list of existing children means CPU load as well
 Invalidating Cursors

• Shared parsed areas are getting invalidated

• Gathering fresh statistics
• Creating new indexes
• Granting and revoking privileges
• Perform operations which can invalidate shared cursors in periods of light load or
user activity
• Invalidating a shared cursor will mean a hard parse will be required the next time
the cursor is invoked
• On DSS and DW systems characterised by many ad hoc type queries we can
expect higher percentage of hard parses as compared to OLTP type applications
• CBO may produce a different plan when a SQL statement is hard-parsed while
creating a new child cursor
 Bind Peeking
• In bind variable peeking the optimizer looks at the value in a bind variable ONCE
when the database performs a hard parse of the statement
• The optimizer does not look at the bind variable values before every parse other
than the first hard parse
• The optimizer must select the best plan without the presence of literals in the
SQL text
• Data is skewed
• Some users suddenly experience bad performance
• And suddenly performance is OK again
• First query with bind variable – full table scan – GOOD – 90% of data
• Second query with bind variable – also full table scan – BAD (should have been index scan
as only 1% of data)
• Third query with bind variable – also full table scan – BAD (should have been index scan as
only 2% of data)
• And continues until next hard parse
• Now query with bind variable using index scan
• But earlier query which was having good performance with full table scan now has bad
performance with index scan
 Sizing the Shared Pool

• Enterprise Manager Shared Pool Advisor

• Use V$SGASTAT to monitor free memory available in the shared pool

• V$LIBRARY_CACHE_MEMORY
• V$SHARED_POOL_ADVICE
• Provides information on estimated time saved during parsing using different
shared pool sizes
• Sizes range from 75% to 200% of current shared pool size
• Shared Pool Advisory section in AWR report
 Shared Pool Fragmentation

• ORA-04031: unable to allocate nn bytes of shared memory

• Avoid shared pool fragmentation by:
• Keeping frequently used large objects (pin) like packages and procedures
• DBMS_SHARED_POOL.KEEP (‘package name’)
• Reserve space for large objects
• Oracle 10g onwards fragmentation less of an issue as we now allocated
memory in more standard size 1K or 4K chunks
 Tuning the Shared Pool

• Make the shared pool larger

• Large shared pool can also have performance implications
• Make the code shareable – bind variables in place of literals
• Parse one execute many
• While statement is being (hard) parsed the library cache latch will be held
for a longer duration as opposed to a soft parse (cursors are shared)
• AWR Report Time Model section – hard parse elapsed time versus parse
time elapsed
 Tuning the Shared Pool

• AWR Report Instance Efficiency Percentages section view Execute to

Parse %, Parse CPU and % Non-Parse CPU
• Reloads to pin ratio should be very low (< 1%)
• Pin is when a soft parse occurs – cursor is found in the shared pool in an
already parsed form
• High values of Pct Miss for SQL Area would indicate cursors are not being
reused or are being reloaded
• Cursors being reloaded because possibly shared pool size is low and
cursors are being aged out very fast to make way for new cursors
 Monitoring Shared Pool Usage

• V$LIBRARYCACHE
• Contains statistics for items stored in the library cache
• Grouped by NAMESPACE column
• NAMESPACE values include SQL AREA, TABLE/PROCEDURE,
TRIGGER
• V$SGASTAT and V$SGAINFO
• V$SQL and V$SQLAREA
• V$SQLTEXT
• V$DB_OBJECT_CACHE
• Amount of sharable memory used by Package, Functions, Procedures etc
 Data Dictionary Cache

• Component of the Shared Pool

• During the parsing phase, the server process scans this memory structure
to resolve the database object names and validate access
• Does the user exist, does the table exist, does the columns exist, does the
user have the required privileges on the object
• Stored in SYSTEM tablespace on disk and in memory in the data
dictionary cache
 Execution Plan Stability

• Execution plans can change over time

• New optimizer version when database is upgraded
• Hardware or platform change
• Data changes
• Optimizer statistics changes
• Database parameter changes
• Most newly generated plans will provide better performance – the
optimizer in every newer version is ‘better’
• But new plans can also introduce sub-optimal performance
• Need to ensure plan stability
 SQL Plan Management

• Ensures the database uses only known or verified plans

• Prevent performance regression caused by plan changes
• Preserves performance through database upgrades and hardware or system
changes
• Provides plan stability
• Uses a mechanism called a SQL plan baseline
• Set of accepted plans that the optimizer is allowed to use for a SQL statement
• New plan to be chosen has to be verified to provide performance improvements
over existing plans stored in the SQL Plan Management Base (SMB)
• Plan Evolution – new plans added to existing SQL plan baselines
 SQL Plan Management
• SQL plan management base (SMB) is a logical repository stored in the
data dictionary
• Resides in the SYSAUX tablespace
• Log of SQL statements and their execution plan history
• Stores the SQL plan history for the set of captured SQL statements
• SQL plan history contains both SQL plan baselines (accepted plans) and
unaccepted plans
• Tracks statements in the statement log and creates baselines
automatically for repeatable SQL statements
SQL Plan Management
SQL Statement Log
A series of query
signatures used to identify
queries that have been
executed more than once
during automatic plan
capture
 SQL Plan Baselines
• Optimizer version changed via database upgrades
• Can result in a plan change for a small percentage of SQL statements
• Upgraded database only uses execution plans captured in the SQL plan
baselines before database was upgraded
• New plans are evaluated to determine if they provide optimizer cost
improvements over existing plans
• If they do provide improvements then SQL plan evolution takes place
• New plans added to the baseline as accepted plans
• Plans have a unique hash generated ‘signature’ assigned to them
 SQL Plan Baseline Components

Plan Capture
• Creation of SQL plan baselines
Plan Selection
• Ensures only accepted execution plans are used for statements with a SQL
plan baseline
• Record any new execution plans found for a statement as unaccepted plans
in the SQL plan baseline
Plan Evolution
• Evaluate all unaccepted execution plans for a given statement
• Only plans that show a performance improvement becoming accepted plans
in the SQL plan baseline
 SQL Plan Capture
• Capturing and storing information about plans related to a set of SQL
statements in the SQL Management Base
• Initial plan capture to occur automatically by setting an initialization
parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE
(default is FALSE)
• Manual plan capture
• User-initiated load of execution plan for a set of SQL statements into a
SQL plan baseline via DBMS_SPM package
• SQL statements can be captured directly from the shared pool or from a
SQL tuning set or from plans captured or stored in a staging table
 SQL Plan Evolution
• Process by which the optimizer verifies any new plans and adds them to an
existing SQL plan baseline
• Manually done via EVOLVE_SQL_PLAN_BASELINE function to compare the
performance between plans
• Or automatically done via SPM advisor automated task
• Plan Verification verifies that unaccepted plans perform at least as well as
accepted plans in a SQL plan baseline
• At this stage plan is marked as unaccepted
• Unaccepted plans are added to the plan baseline as accepted plans after the
database has proven that they perform as well or better than existing accepted
plans in the SQL plan management base
• Unaccepted plans are part of plan history and not part of plan baselines
 SQL Plan Evolution
• Plan evolution can be enabled manually or automatically via the SPM
Evolve Advisor Task
• Part of the Automatic SQL Tuning Advisor task
• SPM Evolve Advisor evolves plans that have recently been added to the
SQL plan baseline
• Simplifies plan evolution by eliminating the requirement to do it manually
• Configure automatic plan evolution via the DBMS_SPM package
• SET_EVOLVE_TASK_PARAMETER procedure
 SQL Plan Evolution
• ACCEPT_PLANS tuning task parameter
• Controls whether to accept recommended plans automatically or not
• Default is TRUE and SQL plan management automatically accepts all
plans recommended by the advisor task
• If set to FALSE the advisor task verifies the plans and generates a report
of its findings but does not evolve the plans
 User Interfaces for SQL Plan Management
• Cloud Control Performance Menu > SQL > SQL Plan Control
• DBMS_SPM package
• CREATE_STGTAB_BASELINE
• PACK_STGTAB_BASELINE
• UNPACK_STGTAB_BASELINE
• LOAD_PLANS_FROM_CURSOR_CACHE
• LOAD_PLANS_FROM_SQLSET
 Configuring the Capture and Use of SQL Plan
Baselines
• Control SQL plan management via database initialization parameters
• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
• Default value is FALSE
• Database does not automatically create an initial SQL plan baseline for any
repeatable SQL statement that does not already exist in the plan history
• OPTIMIZER_USE_SQL_PLAN_BASELINES
• Default value is TRUE
• Use stored plan for any SQL statement that has an existing SQL plan baseline
• Automatically adds any new plans to the SQL plan baseline as nonaccepted
plans
• Oracle 12c introduces Adaptive SQL Plan Management
• Auto-capture of SQL plan baselines is still disabled by default, but evolution of
existing baselines is now automated