View the execution plan graphically or textually from a QCD database or from the Database Query Log (DBQL) when:
Note: DBQL XML plans larger than 31K cannot be viewed using this procedure when connected to Teradata Database 13.00. This limitation is removed for Teradata Database 13.10 and higher.
Use the Explain graphics or text to check for query performance issues.
Note: After a plan is loaded using the Select Execution Plan(s) to Open dialog box, right-click on a column heading under Available Execution Plans to access the shortcut menu. For more information, see “Shortcut Menus” on page 51. Also, the columns can be resized. For more information, see “Dialog Boxes” on page 51.
After inserting an execution plan into a QCD using INSERT EXPLAIN, view the Explain results graphically or textually.
1 Click .
The Select Execution Plan(s) to Open View window appears.
Figure 9: Select Execution Plan(S) to Open View (Workload)
2 Use the following steps to select a QCD:
a In the Tree View, right-click on the Query Capture Databases Node to display a shortcut menu.
b In the shortcut menu, select Browse QCD to list all Available QCD names under Query Capture Databases Node. See figure below.
Figure 10: Browse QCD
3 Select a QCD from the list.
a Right-click a QCD name to display a menu.
b Select the Browse Plans menu item to display the Browse QCD Plans dialog box.
Figure 11: Browse QCD Plans
c Use one of the following options:
Note: This option is available only for Teradata Database version 13.10 and later.
4 Click the OK button to retrieve the specified plans.
5 Select the checkbox next to one or more plans in the Available Execution Plans list.
Note: Click Add All to all available plans to the Selected Execution Plans list, and skip step 6.
6 [Optional] To see complete statement text, select a QueryID in the Available list, right-click and select the Statement Text menu item for a specific QueryID. For more information on the Statement Text dialog box, see “Viewing Statement Text” on page 80.
Click Open to display the plans. The plans appear in the main window. for more information, see “Summary Information Window” on page 25.
After inserting an execution plan into a QCD using INSERT EXPLAIN, view the Explain results graphically or textually.
1 Click .
The Select Execution Plan(s) to Open View window appears.
Figure 12: Select Execution Plan(S) to Open View (Specific Plans)
2 Use the following steps to select a QCD:
a In the Tree View, right-click on the Query Capture Databases Node to display a shortcut menu.
b In the shortcut menu, select Browse QCD to list all Available QCD names under Query Capture Databases Node. See Figure 13 below.
Figure 13: Browse QCD3 Select a QCD from the list.
a Right-click a QCD name to display a shortcut menu.
b Select the Browse Plans menu item to display the Browse QCD Plans dialog box.
Figure 14: Browse QCD Plansc Use one of the following options:
Note: In the Query Tag box, the symbols % and _ (underscore) are wildcards that can be used independently or in combination. The % symbol represents any string of zero or more arbitrary characters. For example, query% matches query, query1, query12, and so on. The _ symbol represents one arbitrary character. Therefore, any single character is acceptable in the position in which the _ appears.
4 Select XML Plans to view the available XML plans in the selected QCD.
Note: This option is available only for Teradata Database 13.10 and later.
5 Click the OK button to retrieve the specified plans.
6 Select the checkbox next to one or more plans in the Available Execution Plans list.
Note: Click Add All to add all available plans to the Selected Execution Plans list and skip step 7.
7 [Optional] To see complete statement text, select a QueryID in the Available List, right-click to select the Statement Text menu item for a specific QueryID. For more information on the Statement Text dialog box, see “Viewing Statement Text” on page 80.
8 Click Open to display the plans. The plans appear in the main window. for more information, see “Summary Information Window” on page 25.
After inserting an execution plan into a QCD using INSERT EXPLAIN, view the Explain results graphically or textually.
1 Click .
The Select Execution Plan(s) to Open View (Range of Plans) window appears.
Figure 15: Select Execution Plan(S) to Open View (Range of Plans)2 Use the following steps to select a QCD:
3 Select a QCD from the list. See Figure 13 on page 66.
a Right-click a QCD name to display a shortcut menu.
b Select the Browse Plans menu item to display the Browse QCD Plans dialog box.
c In the Query ID box, enter a range such as, 1-100, or 1,3,6-10.
Figure 16: Browse QCD Plans
4 Select XML Plans to view available XML plans in the selected QCD.
Note: This option is available only for Teradata Database version 13.10 and later.
5 Click the OK button to retrieve the specified plans.
6 Select the check box next to one or more plans in the Available Execution Plans list.
Note: Click Add All to add all available plans to the Selected Execution Plans list and skip step 7.
7 [Optional] To see complete statement text, select a QueryID in the Available list, right-click to select the Statement Text menu item for a specific QueryID. For more information on the Statement Text dialog box, see “Viewing Statement Text” on page 80.
8 Click Open to display the plans.
After inserting an execution plan into a QCD using INSERT EXPLAIN, view the Explain results graphically or textually.
1 Click .
The Select Execution Plan(s) to Open View (Different Server) window appears.
Figure 17: Select Execution Plan(S) to Open View (Different Server)2 Use the following steps to select a QCD:
3 Load plans from a different server:
a Click the Connect button in the toolbar of the Open Plan window.
b Enter logon information.
c In the shortcut menu, select Browse QCD to list all Available QCD names under Query Capture Databases Node. In the shortcut menu, select Browse QCD to list all Available QCD names under Query Capture Databases Node.
4 Under the Query Capture Databases Node, Select a specific QCD. Right-click on the QCD to display the shortcut menu. Select the Browse Plans menu item to display the Browse QCD Plans dialog box. Select XML Plans to view the available XML plans in the selected QCD.
Note: This option is available only for Teradata Database version 13.10 and later.
Figure 18: Browse QCD Plans
5 Click the OK button to retrieve the specified plans.
6 Select the check box next to one or more plans in the Available Execution Plans list.
Note: Click Add All to add all available plans to the Selected Execution Plans list and skip step 7.
7 [Optional] To see complete statement text, select a QueryID in the Available list, right-click to select the Statement Text menu item for a specific QueryID. For more information on the Statement Text dialog box, see “Viewing Statement Text” on page 80.
8 Click Open to display the plans.
After inserting an execution plan into a QCD using INSERT EXPLAIN, view the Explain results graphically or textually.
1 Click .
The Select Execution Plan(s) to Open View window appears.
Figure 19: Select Execution Plan(S) to Open View (Offline Plans)
2 Load one or more offline plans:
a Right-click the Offline Plan menu item and select Browse Offline Plans to display the Open dialog box.
b From the Open dialog box, choose one or more plans.
When selecting a file, the description of the plan appears in the Description box. If the file contains more than one plan, the Description box indicates how many plans the file contains.
c Click Open.
3 Right-click on the Plan Node and select the Add menu item to add the single plan to the Selected Execution Plans list.
4 [Optional] To see complete statement text, select a QueryID in the Available list, right-click to select the Statement Text menu item for a specific QueryID. For more information on the Statement Text dialog box, see “Viewing Statement Text” on page 80.
5 Click Open to display the plans.
The plans appear in the main window. For more information, see “Summary Information Window” on page 25
Figure 20: Select Execution Plan(S) to Open View (DBQL Plans)
View the Explain results graphically or textually after the XML execution plan has been logged into the DBQL.
Note: DBQL XML plans larger than 31K cannot be viewed using this procedure when connected to Teradata Database 13.00. This limitation is removed for Teradata Database 13.10 and higher.
1 Click .
The Select Execution Plan(s) to Open View (DBQL) window appears.
Figure 21: Select Execution Plan(S) to Open View (DBQL)
2 In the tree view, under Available Plans, right-click on the DBQL XML Plans node and select the Browse DBQL Plans menu item.
The Select DBQL Criteria dialog box opens.
3 Under Log Option, select one of the following:
4 [Optional] Under DBQL Filter Options, enter filter options:
Descriptions of DBQL criteria selection are given in the following table.
|
Group |
Subgroup |
Box or Button |
Description |
|
Select Criteria |
Select Date |
Start Date |
The start date, in the format MM/DD/YYYY, to use when searching for queries on or after the date. If no start date is specified, all statements from the DBQL are loaded. |
|
End Date |
The end date, in the format MM/DD/YYYY, to use when searching for queries on or before the date. If a start date is specified but no end date is specified, all statements from the DBQL beginning at the start date are loaded. |
||
|
Select Time |
Start Time |
A start time in the format HH:MM:SS to use in searching for queries on or after the start time. If no start time is specified, the default start time of 00:00:00 is used. |
|
|
End Time |
An end time in the format HH:MM:SS to use in searching for queries on or before the end time. If no start time is specified, the default end time of 13:59:59 is used. |
||
|
|
None |
Step Count |
The minimum number of steps for the queries received from the DBQL. |
|
|
None |
Result Rows |
The minimum number of rows returned for the queries retrieved from the DBQL. |
|
|
None |
Select Users and Accounts |
Use this button to open the Select Users and Accounts dialog box and browse queries submitted by a set of users or accounts. From the Select Users and Accounts dialog box, browse a list of users and accounts, and select one or more users or one or more accounts to include in the search. Selected user or account names are displayed on the right‑hand side of the dialog box.  |
|
Select QueryBand |
None |
Name |
The name of a Query Band Name‑Value pair. See “SET QUERY_BAND” in SQL Data Definition Language B035-1144 for more information. |
|
Value |
The value of a Query Band Name‑Value pair. |
||
|
List Names |
Use to list all the Query Band Names in the Name‑Value list. |
||
|
Add |
Use to add the selected Query Band Name‑Value pair to the Name‑Value list. |
||
|
Rem |
Use to remove the selected Query Band Name‑Value pair from the Name‑Value list. |
||
|
Name‑Value |
The combination of the Name and Value entries (Name‑Value pair). |
||
|
Filter |
Use to filter out some of the Query Band Name‑Value pairs. |
||
|
|
Estimated Time Proc |
|
The minimum estimated processing time for the queries received from the DBQL. |
|
|
CPU Time |
|
The minimum processing time for the queries received from the DBQL. |
5 Click OK to close the Select DBQL Criteria window.
6 In the Select Execution Plan(s) to Open View (DBQL Plans) window, right-click on the QueryID and select the Add menu-item checkbox in the Available Execution Plans list.
Note: Right-click a specific DBQL and select Add All to add all available plans to the Selected Execution Plans list.
7 [Optional] To see a complete statement text, right-click Statement Text and select a statement and click. For more information on the Statement Text dialog box, see “Viewing Statement Text” on page 80.
8 Click Open to display the plans.
9 Click OK to close the Select Criteria dialog box.
After Capturing a Dynamic (IPE) plan into SystemFE.Opt_XMLPlan_Table, view the Explain results graphically or textually. Select Dynamic Plans from the following screen using the steps below the screen.
Figure 23: Select Execution Plans to View Dynamic Plans
1 In the Tree View, right-click on the Dynamic Plans node to display a shortcut menu.
2 In the shortcut menu, select Browse Dynamic Plans to list SystemFE node and right-click SystemFE shortcut menu to display all Available Dynamic Plan Names under Dynamic Plans Database Node.
3 Right-click on the Plan Node and select the Add menu item to add the single plan to the Selected Execution Plans list.
4 [Optional] To see complete statement text, select a Request Name in the Available list, right-click to select the Statement Text menu item for a specific Request. For more information on the Statement Text dialog box, see “Viewing Statement Text” on page 80.
5 Click Open to display the plans.