Download PDF
Download page Database Query Execution Plan Window.
Database Query Execution Plan Window
The Database Query Execution Plan window can help you to determine the most efficient execution plan for your queries. Once you've discovered a potentially problematic query, you can run the EXPLAIN PLAN statement to check the execution plan that the database created. A query's execution plan reveals whether the query is optimizing its use of indexes and executing efficiently. This information is useful for troubleshooting queries that are executing slowly.
Access the Database Query Execution Plan Window
To access the Database Query Execution Plan window:
- To view a query execution plan, click the name of the database.
- Click the Queries tab.
- Click a SELECT statement to examine and click View Query Details.
- Click the Execution Plan tab.
Database Query Execution Plan Window Features
From the Database Query Execution Plan window, you can:
- Click Explain to view the execution plan for a query.
- Click Schema to choose a different schema and then explain the execution plan based on that schema. If you want to view the execution plan for a query that belongs to a schema outside the scope of your database account permissions, you can enter a username and password for a different database account that has access to the schema.
- If you want to improve a suboptimal query, you can modify the query and paste it into the Explain another query box. Click the Explain button to generate the execution plan for your modified query and determine whether the modified query is more efficient.
- Click the down arrow next to the query name at the top of the page to choose to view queries and query execution plans of a different database Collector. You can either select the database Collector name from the list or search for the database Collector in the search bar. Click the refresh icon to show only database Collectors that meet your search criteria.
If you are using SQL Server or Azure you can view the execution plan in one of the following ways:
- Select a cached execution plan and click View Cached Plan to view the plan details.
- Double-click a cached execution plan.
If you are using MySQL, you can also:
- Choose to explain the query on another schema. In Plan Details, from the list on the right, choose the schema name and then click Explain.
- Copy the text in the Parsed SQL output box and paste it into the Explain another query box where you can edit it and then explain the edited version of the query.
If you are using MSSQL, you can perform these steps to view the costly operations (the amount of work such as CPU usage, I/O, and memory required to complete the operation) in the execution plan along with the warnings (if any) in the plan:
- Under Query Plan Handle(s), select a cached execution plan.
- Click View Cached Plan.
You can view the following details:
Top Plan Operations:
| Field Name | Description |
|---|---|
| Operation | The object scan (table or index) operation in the execution plan |
Database | The database name of the object |
Schema | The schema of the object in the database |
| Object Name | The name of the object (table or index) that is scanned in the operation |
Estimated I/O Cost | The estimated cost of the input/output (I/O) to perform the operation |
| Estimated CPU Cost | The estimated CPU cost to perform the operation |
| Estimated number of rows | The estimated number of rows in the object |
Warnings: This table includes the warnings in the execution plan related to any issue such as Typecast. This table helps you in understanding the potential performance-degrading activities that happen during the query execution. This table includes these fields:
- Issue: The issue description for the warning.
- Expression: The query expression used in the execution plan that correlates to the issue.
If you are using Oracle database, you can view the cached execution plan details along with the top plan operations and the referenced objects. To view the details, double click a cached execution plan under View Cached Plan(s). These details are displayed:
Top Plan operations
| Field Name | Description |
|---|---|
| Operation | The name of the internal operation performed in the step (such as, TABLE ACCESS) |
| Options | A variation on the operation that is described in the Operation column (such as, FULL) |
Referenced Objects
Field Name | Description |
|---|---|
| Tree Node | The step number in sequence used in the execution plan |
| Operation | The object scan operation (table or index) in the execution plan |
| Object Type | The type of the database object (index or table) |
| Object Name | The name of the database object |
From the Database Query Execution Plan window, you can view:
- Cached Execution Plan(s): identifying details of the cached execution plan, which vary from one database to another. For SQL Server, the execution plan is rendered as a diagram.
- Plan Details: execution plan details, showing the step by step procedure the database followed to process the selected query.
- Referenced Objects: the database objects accessed by the execution plan.
