Analyzing SQL statements (formerly Visual Explain)

Creating an access plan graph

Using Visual Explain windows and notebook

Creating explain snapshots

Using sample snapshots to learn how to use Visual Explain

Creating the explain tables

Creating explain snapshots for dynamic SQL statements

Creating explain snapshots for static SQL statements

Displaying and using an access plan graph

Displaying an access plan graph

Dynamically explaining an SQL statement

Choosing from a list of previously explained SQL statements

Choosing from a list of explainable statements in a package

Magnifying and viewing different parts of a graph

Using the zoom slider to magnify parts of a graph

Reading the symbols in an access plan graph

Getting more details about the objects in a graph

Getting statistics for tables, indexes, and table functions

Getting statistics for tables spaces and functions

Getting statistics for columns in an SQL statement

Getting statistics for indexes on a table

Getting details about operators in a graph

Getting information about configuration parameters and bind options

Changing the appearance of a graph

Changing graph settings

Changing node settings

Changing operator settings

Changing color settings for basic operators

Changing color settings for extended operators

Changing color settings for update operators

Changing color settings for miscellaneous operators

Changing operand settings

Improving an access plan

Examples for single-partition database environments

Query 1 - Running a query with no indexes and no statistics

Query 2 - Collecting current statistics for the tables and indexes

Query 3 - Creating indexes on columns used to join tables in a query

Query 4 - Creating additional indexes on table columns

Examples for partitioned database environments

Query 1 - Running a query with no indexes and no statistics

Query 2 - Collecting current statistics for the tables and indexes

Query 3 - Creating indexes on columns used to join tables in a query

Query 4 - Creating additional indexes on table columns

Basic query used in the examples

Following along with the examples

Following along interactively with Query 1

Following along interactively with Query 2

Following along interactively with Query 3

Following along interactively with Query 4

Dynamically explaining an SQL statement

Reading the contents of the Visual Explain windows

Reading the contents of the Explainable Statements window

Reading the contents of the Explained Statements History window

Reading the contents of the Access Plan Graph window

Reading the contents of the Column Distribution Statistics window

Reading the contents of the Index Statistics window

Reading the contents of windows showing function statistics

Reading the contents of the Optimization Parameters window

Reading the contents of the Page Fetch Pairs Statistics window

Reading the contents of the Referenced Column Statistics window

Reading the contents of the Table Statistics window

Reading the contents of the Table Space Statistics window

Tuning your SQL queries

Using runstats

Creating appropriate indexes

Choosing appropriate configuration parameters

Choosing appropriate bind options

Reorganizing table data

Designing queries to retrieve only required data

Tuning your table design

Viewing explainable statements for a package

Filtering explainable statements

Viewing explainable statements

Viewing text (or optimized text) for an SQL statement

Viewing the history of previously explained SQL statements

Changing explained statements

Finding character strings

Filtering explained statements

Removing explained statements

Viewing a graphical representation of an access plan

Printing an access plan graph

Viewing optimization parameters

Table spaces

Viewing table spaces

Viewing table space statistics

Functions

Viewing functions

Viewing function statistics

Operators

Viewing operator details

Viewing partitioned databases

Operands

Viewing indexes

Viewing index statistics

Viewing page fetch pairs statistics

Viewing table statistics

Viewing referenced columns

Viewing referenced column statistics

Viewing column distribution statistics

Viewing table function statistics