MODULE 3
Execution Plan and Statistics
Statistic Types Generating Statistics
• Used by the Optimizer to estimate 1/0 and
System Statistics CPU costs
EXEC dbms_stats.gather_system_stats('Start'); • Should be generated regularly
SELECT * FROM sys.aux_stats$ ; • Should be gathered during a normal workload
• GATHER_DICTIONARY_STATS Procedure:
Optimizer Statistics EXEC dbms_stats.gather_dictionary_stats;
• Can be gathered manually or automatically. • GATHER_SCHEMA_STATS Procedure:
EXEC dbms_stats.gather_schema_stats(ownname=>'SH');
ANALYZE TABLE <table_name> COMPUTE STATISTICS;
• GATHER_DATABASE_STATS Procedure: • GATHER_TABLE STATS Procedure:
EXEC dbms_stats.gather_database_stats; EXEC dbms_stats.gather_table_stats(ownname=>'SH',
tabname=>'SALES', cascade=>true );
Statistics Generation
Optimizer Statistics:
• DBA_TABLES
• DBA_TAB_STATISTICS
• DBA_TAB_COL_STATISTICS
• DBA_INDEXES
• DBA_CLUSTERS
• DBA_TAB_PARTITIONS
• DBA_IND_PARTITIONS
• DBA_PART_COL_STATISTICS
Execution Plan Generation
Tools to analyze an execution plan:
1. Explain Plan:
• EXPLAIN PLAN FOR <QUERY>; (Generates the explain plan and saves into plan_table)
• SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY());
• EXPLAIN PLAN SET statement_id= 'MyID' FOR SELECT FROM EMPLOYEES WHERE employee_id = 100;
• EXPLAIN PLAN SET statement_id= 'MyID' INTO MyPlanTable FOR SELECT FROM EMPLOYEES WHERE
employee_id = 100;
2. Autotrace,
3. V$SQL_PLAN.
Autotrace
Autotrace automatically analyses our query and produces an appropriate
execution plan and the corresponding statistics:
• SET AUTOTRACE ON;
• SET AUTOTRACE ON [EXPLAIN | STATISTICS];
• SET AUTOTRACE TRACE[ONLY] ON [EXPLAIN | STATISTICS];
• SET AUTOTRACE OFF;
Autotrace uses plan_table like the explain plan.
V$SQL_PLAN VIEW
There are a lot of performance V$SQL_PLAN:
views that can be used for tuning: • Actual execution plans are stored
• V$SQLAREA here
• V$SQL_WORKAREA • It is very similar to plan_table
• V$SQL • It is connected to V$SQL view
• V$SQL_PLAN SELECT * FROM TABLE
(DBMS_XPLAN.DISPLAY_CURSOR('Sd4xt4hva9h94'));
• V$SQL_PLAN_STATISTICS
• V$SQL_PLAN_STATISTICS_ALL
Execution Plan Analysis
SELECT p.prod_id, p.prod_name, s.amount_sold, s.quantity_sold FROM
sales s, products p, customers c WHERE s.prod_id = p.prod_id AND s.cust_id
= c.cust_id AND s.cust_id IN (2,3,4,5);
Look for:
• Cost
• Access Methods
• Cardinality
• Join Methods &
Join Types
• Partition Pruning
Types of indexes
Table Indexing & Access Paths
ID F_NAME L_NAME INDEXES
E10297 Tyler Bennet E00127
E21437 John Rappl E01234 B-TREE Indexes Bitmap Indexes
E00127 George Woltman E03033
• Normal Index
E63535 Adam Smith E04242 • Function-Based Index
E04242 David McClellan E10001 • Index-Organized Table
E01234 Rich Holcomb E10297 (IOT)
E41298 Nathan Adams E16398
E43128 Richard Potter E21437
E27002 David Motsinger E27002 Common Index attributes:
E03033 Tim Sampair E41298 • Key Compression
E10001 Kim Arlich E43128
E16398 Timothy Grove E63535 • Reverse Key
Table Indices • Ascending - Descending Ordered Indexes.
Root Branches Leaves
0 0 0 0 0 0
24 11 5 2 1
51 50
23 10 4
201 2
51
101 3
151 In addition to the
Indexes, the 4
corresponding Row Id
201 Similar to
the above
is stored with it.
401 logic…
601
INDEXING
INDEXING
Sales Table
Channel Id
Types of Table & Index paths
Index Access Paths
Table Access Paths
• Index Unique Scan
• Table Access Full
• Index Range Scan
• Table Access by ROWID
• Index Full Scan
• Sample Table Scan
• Index Fast Full Scan
A
• Index Skip Scan
A N • Index Join Scan
• Index Organized Tables
A G N R
• Bitmap Access Paths
A B G H N o R S
Types of Table & Index paths
Table Access by ROWID
• ROWID is used in the where clause directly
• By an Index Scan operation
A N
A G N R
A B G H N o R S
Root Branches
50 16
35
201 50
1000 100
151 Leaves
201
INDEX 401
Unique 1000
Scan
N
Index Range Scan
If the data we queried is bounded from one or both sides, the optimizer can use index range
H N
scan.
Can be applied to b-tree indexes and bitmap indexes.
Can be applied to unique or non-unique indexes. D H J S
Normally, data is stored in ascending order in the indexes.
If the optimizer finds one or more leading columns with =, > or < signs it will use index range
scan.
A C D G H I O S
If the query includes an order by or group by clauses with the indexing columns, range scan will
not do any sort. It is already sorted. It should not have null values.
N
If order by clause has desc keyword, it will read the data in descending order.
You can create your index as descending.
N H
CREATE INDEX index_name ON employees (department_id DESC )
Function-based indexes can be accessed as index range scan.
S J H D
If wildcard characters are written on the right, it will perform index range scan
(WHERE department_name LIKE 'A%')
S O I H G D C A
Index Full Scan
All the rows of the tables are indexed
by their indexes. N
Optimizer uses the index full scan, H N
when:
D H J S
• Query has order by clause only with the
indexed columns. A C D G H I O S
• Query has group by clause only with the
indexed columns.
• Query requires a sort-merge join.
Index Fast Full Scan
If the query requests only the columns existing in the index, it uses IFF Scan
Can be applied to both b-tree and bitmap indexes.
Hints can be used to force the optimizer to use IFF Scan.
The differences of Index Full Scan vs Index Fast Full Scan:
Index Full Scan Index Fast Full Scan
Can read from the table. Reads from the index only.
Reads blocks one by one, sequentially. Reads multiple blocks simultaneously, in unorderly
manner.
Usually, it is Slower than Index Fast Full Scan. Is faster than Index Full Scan most of the times.
Can be used to eliminate sorting. Cannot be used.
Index Skip Scan
Not using the indexed columns on the WHERE clause: the optimizer will not use indexes.
We don't create indexes for unnecessary ROWS.
If the second, third, ... column of a composite index is used as an access predicate, the
optimizer will consider the index skip scan.
Index skip scan skips the leaves which do not have any chance to have any matching rows.
What are the advantages?
• Reduction of the number of indexes
• Decreases the index space
• Increases the overall performance by reducing index maintenance
Root Branches
50 16
35
201 50
1000 100
151 Leaves
SELECT * FROM 201
employees
WHERE age
BETWEEN 20 AND 30;
401
1000
INDEX Skip Scan
Index Join Scan
If an index stores the columns of a query, the optimizer will perform index fast full
scan.
If the combination of multiple indexes store the columns of a query, the optimizer will
join them and read the data from that join. (INDEX JOIN SCAN)
Key aspects of Index Join Scan:
• The combination of indexes must have every column of the select clause.
• There is no join limit. More than two indexes can be joined together to get the data.
• There might be any index access path before the index join scan
• If you write ROWID in the select clause, it will NOT perform index join scan.
Optimizer Hints
Optimizer hints are used to command the optimizer.
They force the optimizer to pick a specific action.
If the hint is not better, the optimizer will ignore it.
Hints can be operating on a single table, multi-tables, a query block, a specific statement.
Categories of the hints:
• Hints for optimization approaches
• Access Path Hints
• Query Transformation Hints
• Join Order Hints
• Join Operation Hints
• Parallel Execution Hints, etc.
Usage of hints:
Table name or its alias can be used as the hint parameter. But if an alias exists, table
name cannot be used!
There can be only one hint area.
Hints can be used after a SELECT, UPDATE or DELETE keywords.
Be careful on the hints you select, especially if you are using multiple hints. You may
lead the optimizer to a bad execution plan.
SELECT /*+ hint_name(p1 p2 p3 .. ) */ first_name FROM EMPLOYEES;
WHAT NEXT?