Difference between revisions of "EXPLAIN"

From Lianjapedia
Jump to: navigation, search
Line 11: Line 11:
 
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.
 
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.
  
−
Note: from Lianja v2.1, issuing [[SET EXPLAIN|SET EXPLAIN ON]] causes all executed SQL SELECT commands to output their EXPLAIN execution plan to the console.
+
Note: from Lianja v2.1, issuing [[SET EXPLAIN|SET EXPLAIN ON]] causes all executed SQL SELECT statements to output their EXPLAIN execution plan to the console.  The EXPLAIN execution plan itself is also much more detailed from Lianja v2.1.  
  
 
==Example==
 
==Example==
 
<code lang="recital">
 
<code lang="recital">
−
> EXPLAIN SELECT * FROM example
+
> open database southwind
−
sqlcnt=100
+
> explain select * from employees where hiredate < gomonth(date(),-36)
−
> EXPLAIN SELECT * FROM example WHERE start_date < date()
+
********************************************************************************
−
Could not optimize WHERE condition
+
Explaining execution plan for command:
−
sqlcnt=100
+
select * from employees where hiredate < gomonth(date(),-36)
−
> EXPLAIN SELECT * FROM example WHERE account_no = '000'
+
--------------------------------------------------------------------------------
−
Optimized using index tag 'ACCOUNT_NO'
+
SET EXCLUSIVE ON
−
sqlcnt=99
+
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)
 +
... EXPLAIN execution plan output as above
 +
> select * from employees inner join orders on employees.employeeid = orders.employeeid
 +
... EXPLAIN execution plan output as above
  
 
// Send EXPLAIN output to a text file
 
// Send EXPLAIN output to a text file
Line 28: Line 95:
 
set console off
 
set console off
 
set alternate on
 
set alternate on
−
EXPLAIN SELECT * FROM example
+
explain select * from employees inner join orders on employees.employeeid = orders.employeeid
 
close alternate
 
close alternate
 
</code>
 
</code>

Revision as of 06:11, 14 June 2016

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.

Note: from Lianja v2.1, issuing SET EXPLAIN ON causes all executed SQL SELECT statements to output their EXPLAIN execution plan to the console. The EXPLAIN execution plan itself is also much more detailed from Lianja v2.1.

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)
... EXPLAIN execution plan output as above
> select * from employees inner join orders on employees.employeeid = orders.employeeid
... EXPLAIN execution plan 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 alterna
********************************************************************************
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)
... EXPLAIN execution plan output as above
> select * from employees inner join orders on employees.employeeid = orders.employeeid
... EXPLAIN execution plan 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 alterna
********************************************************************************
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)
... EXPLAIN execution plan output as above
> select * from employees inner join orders on employees.employeeid = orders.employeeid
... EXPLAIN execution plan 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
Retrieved from "https://www.lianja.com/doc/index.php?title=EXPLAIN&oldid=13447"