Platforms
Mac OS X Mac OS X
Windows Windows
Linux Linux

MySQL
Oracle
Sybase
SQL Server
DB2
PostgreSQL
Solid
SQLite
 

Explain Plan

Explain plan is a feature that allows you to see a visual representation of the execution plan of a query in a tree or table format. When you execute your queries in the query window and the execution plan is available, you can view the plan under the 'Plan' tab. This feature is useful for troubleshooting issues with slow queries, the plan may reveal that the optimizer decided to do use a plan that was unexpected.

Explain Plan is available for Oracle 9i and later, SQL Server, DB2 and Solid and is supported on all DB Solo's OS platforms, i.e. Windows, Linux and MacOS.

The 'Plan' tab is shown if the feature is supported for the database, the execution plan is available for the entered statement and the explain plan button is selected in the query window. The logged in user also needs the necessary privileges, see the sections below for more information about privileges. If you wish not to get the plan by default every time a query is executed, you can disable the feature from Settings / Query Window.


Graph View of the Execution Plan

In addition to the above treetable representation, execution plans can also viewed as graphs. Vertices of the graph represent steps of the plan and directional edges show what the ordering of the steps is.

You can print the execution plan using the Print-button at the bottom of the page. Changing the layout of the graph is simple by selecting some of the nodes and dragging them to a different location. You can also zoom in/out the graph by using the scroll wheel of your mouse.

Explain Plan Graph


Oracle Explain Plan

The visual explain plan feature is supported for Oracle 9i and later versions. Unlike many other products, DB Solo does not require you to have a PLAN_TABLE in place for this feature. Instead, the V$SQL_PLAN table is used which is more accurate since it contains the actual plan, not a predicted one like the PLAN_TABLE approach does.

For a user to be able to read the V$SQL_PLAN and V$SESSION tables, she must be granted the SELECT_CATALOG_ROLE role or the SELECT ANY DICTIONARY system privilege. The difference between these two is that SELECT_CATALOG_ROLE is a role that contains several privileges whereas SELECT ANY DICTIONARY is a single system privilege. You can check to see if you have the necessary privileges by issuing the 'SELECT COUNT(*) FROM V$SQL_PLAN' SQL statement in the query window. If you get the ORA-00942 (table or view does not exist) error, you need to ask your DBA to grant the necessary privileges.

The visual representation of the explain plan is constructed based on the following columns from the V$SQL_PLAN table

  • OPERATION - Name of the operation that was performed, e.g. TABLE ACCESS
  • OPTIONS - Variation of the operation, e.g. FULL in case of a full table scan, i.e. TABLE ACCESS (FULL)
  • OBJECT_NAME - Name of the table or index that this step applies to
  • COST - Cost of the operation assigned to this step by the optimizer, NULL for rule-based optimizer
  • CPU_COST - CPU cost of the operation assigned to this step by the optimizer, NULL for rule-based optimizer
  • IO_COST - IO cost of the operation assigned to this step by the optimizer, NULL for rule-based optimizer
  • CARDINALITY - Estimated number of rows produced by this step
  • OPTIMIZER - RULE or CHOOSE
  • ACCESS_PREDICATES - Predicate to locate rows in the access structure.
  • FILTER_PREDICATES - Predicate that will filter rows before returning them from this step

For the Oracle's cost-based optimizer (CBO) to work correctly, you need to regularly compute statistics for your tables and indexes using the Oracle-supplied DBMS_STATS package or the ANALYZE TABLE statement.


DB2 Explain Plan

Visual explain plan is supported for DB2 versions 8.x and 9.x. It is recommended to create the tables needed for retrieving the execution plan prior to using this feature in DB Solo. DB Solo can create these tables automatically if they are not present, but it is always preferred to create these tables separately. The tables need to be in the same schema you used to log in to the database, i.e. changing the schema in the query plan will not affect where DB2 looks for these tables. This is because the 'SET CURRENT SQLID' command has no effect, DB2 will still try to locate the execution plan tables in the log in schema (authorization ID).

Typically, the DB2 installation comes with a script named EXPLAIN.DDL that contains the CREATE TABLE and CREATE INDEX statements for these tables. The script is often in MISC directory under your DB2 server installation folder.

DB2 version 8.x execution plan tables are listed below:

EXPLAIN_INSTANCE
 EXPLAIN_STATEMENT 
 EXPLAIN_ARGUMENT 
 EXPLAIN_OBJECT 
 EXPLAIN_OPERATOR 
 EXPLAIN_PREDICATE 
 EXPLAIN_STREAM 
 ADVISE_INSTANCE 
 ADVISE_INDEX 
 ADVISE_WORKLOAD 
 ADVISE_MQT 
 ADVISE_PARTITION 
 ADVISE_TABLE

The following are the DB2 version 9.x execution plan tables:

EXPLAIN_DIAGNOSTIC 
 EXPLAIN_DIAGNOSTIC_DATA
 EXPLAIN_INSTANCE 
 EXPLAIN_STATEMENT
 EXPLAIN_ARGUMENT 
 EXPLAIN_OBJECT
 EXPLAIN_OPERATOR
 EXPLAIN_PREDICATE
 EXPLAIN_STREAM
 ADVISE_INSTANCE
 ADVISE_INDEX
 ADVISE_WORKLOAD
 ADVISE_MQT
 ADVISE_PARTITION
 ADVISE_TABLE 
							

Internally DB Solo uses the command EXPLAIN ALL SET QUERYNO = ? FOR <sql statement> to retrieve the plan from DB2.


SQL Server Explain Plan

To get the execution plan from SQL Server, the query tool uses the SET SHOWPLAN_ALL ON command internally. After issuing this command, the tool executes the query in the query window, retrieves the plan, then issues SET SHOWPLAN_ALL OFF. Following this, the query in the query window is executed one more time. This is necessary since the first time it was run, SQL Server does not really execute it, just returns the plan for the command. The required permissions for this feature default to all users.

  • PhysicalOp - Physical algorithm for this step
  • LogicalOp - Relational algebraic operator for this step
  • EstimateIO - Estimated IO cost of this step
  • EstimateCPU - Estimated CPU cost of this step
  • EstimateRows - Estimated number of rows produced by this step
  • AvgRowSize - Average size of the rows in bytes processed by this step
  • Argument - Additional information about this step
  • TotalSubtreeCost - Cumulative cost of this step and all its children
  • EstimateExecutions - Estimated number of times this operator will be executed
  • StmtText - Description of the operation

Solid Explain Plan

No special permissions are required to be able to get the execution plan in Solid. DB Solo uses the EXPLAIN PLAN FOR statement to retrieve the plan from the database.


More Information

For more information on this Graphical Explain Plan feature of DB Solo, please view the user documentation.

 

Forums
Join
Browse Archives