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.
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
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.
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.
For more information on this Graphical Explain Plan feature of DB Solo, please view the user documentation.