SET EXPLAIN
Contents
Purpose
Determines whether or not issuing SQL SELECT statements displays information about its optimization and result count
Syntax
SET EXPLAIN ON | OFF | (<expL>)
See Also
CLOSE ALTERNATE, CREATE INDEX, EXPLAIN, INDEX, PROFILE, SELECT, SET ALTERNATE, SET CONSOLE, SQLCNT
Description
With SET EXPLAIN ON, when a SQL SELECT statement is issued, 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 and cache settings for the maximum performance of SELECT statements.
With SET EXPLAIN OFF, SQL SELECT statements are executed normally. To display the EXPLAIN execution plan for an individual SQL SELECT statement, precede it with the EXPLAIN command.
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