Difference between revisions of "EXPLAIN"

Latest revision as of 11:19, 4 June 2018

Purpose

Display information about the optimization and result count of an SQL SELECT statement

Syntax

EXPLAIN <SELECT statement>

See Also

CLOSE ALTERNATE, CREATE INDEX, INDEX, PROFILE, SELECT, SET ALTERNATE, SET CONSOLE, SET EXPLAIN, SQLCNT

Description

When an SQL SELECT statement is preceded by the EXPLAIN command, information is displayed about the optimization that would be carried out on that SELECT statement and the number of results that it would return. Analysis of the EXPLAIN output can be used by the Developer to target index creation for the maximum performance of SELECT statements.

Issuing SET EXPLAIN ON causes all executed SQL SELECT statements to output their EXPLAIN execution plan to the console.

Example

open database southwind
explain select * from employees where hiredate < gomonth(date(),-36)

********************************************************************************
Explaining execution plan for command:
select * from employees where hiredate < gomonth(date(),-36)
--------------------------------------------------------------------------------
SET EXCLUSIVE ON
SET NETWORKSHARE OFF
SET SMARTQUERY OFF
SET STRCOMPARE ON (Case insensitive string comparisons with padding)
SET DCACHE ON (Table page cache)
SET DCACHEBLOCKSIZE TO 8192 (Size of table cache pages)
SET DCACHESIZE TO 10 (Number of table pages to cache)
SET ICACHE TO 50 (Index node cache)
SET SQLCACHE ON (SQL Query cache)
SET SQLCACHESIZE TO 65536 (Size of SQL Query cache extents)
SET SQLBUFFERSIZE TO 1024 (Number of SQL write cache buffers)
WHERE condition for table 'employees' could not be optimized
Total I/O read operations was 0
Total I/O read size was 0.0000MB
Total I/O write size was 0.0000MB
Total SEEK operations performed was 0
Total SEEK operations failed was 0
Total locks performed 0
Total rlocks performed was 0
Total unlocks performed was 0 (0 active)
Total I/O read cache hits was 11
Total I/O read cache misses was 0
Total I/O index reads was 0
Total I/O index cache hits was 0
Total I/O index cache misses was 0
9 records selected in 27ms

explain select * from employees inner join orders on employees.employeeid = orders.employeeid

********************************************************************************
Explaining execution plan for command:
select * from employees inner join orders on employees.employeeid = orders.employeeid
--------------------------------------------------------------------------------
Optimized JOIN to table 'orders' using index tag 'EMPLOYEEID' keytype=N keylength=8
SET EXCLUSIVE ON
SET NETWORKSHARE OFF
SET SMARTQUERY OFF
SET STRCOMPARE ON (Case insensitive string comparisons with padding)
SET DCACHE ON (Table page cache)
SET DCACHEBLOCKSIZE TO 8192 (Size of table cache pages)
SET DCACHESIZE TO 10 (Number of table pages to cache)
SET ICACHE TO 50 (Index node cache)
SET SQLCACHE ON (SQL Query cache)
SET SQLCACHESIZE TO 65536 (Size of SQL Query cache extents)
SET SQLBUFFERSIZE TO 1024 (Number of SQL write cache buffers)
Performing join
Parent table 'employees' has 12 records, table size is 128.000KB, DCACHE is ON, 
  DCACHEBLOCKSIZE 8451, record size 313, records per page 27
Child table 'orders' has 834 records, table size is 256.000KB, DCACHE is ON, 
  DCACHEBLOCKSIZE 8451, record size 251, records per page 33
Processing parent table 'employees' sequentially
Total I/O read operations was 215
Total I/O read size was 1.7296MB
Total I/O write size was 0.0000MB
Total SEEK operations performed was 11
Total SEEK operations failed was 0
Total locks performed 0
Total rlocks performed was 0
Total unlocks performed was 0 (0 active)
Total I/O read cache hits was 846
Total I/O read cache misses was 214
Total I/O index reads was 8
Total I/O index cache hits was 32
Total I/O index cache misses was 8
822 records selected in 75ms

// Cause all SQL SELECT statements to use EXPLAIN
set explain on
select * from employees where hiredate < gomonth(date(),-36)
select * from employees inner join orders on employees.employeeid = orders.employeeid

EXPLAIN execution plans output as above

// Send EXPLAIN output to a text file
set alternate to explain
set console off
set alternate on
explain select * from employees inner join orders on employees.employeeid = orders.employeeid
close alternate

EXPLAIN execution plans output to explain.txt