Page History
This section includes the following topics:
...
- About the SQL tab
...
- How the SQL tab is structured
...
- About the Related SQL selector
...
- About the View tabs
...
- About Tuning Actions
...
- How the SQL tab can help you identify performance problems
About the SQL tab
The process of explaining statements is a prerequisite for tuning. The explain process is designed to clarify the access path chosen for a statement and translate it into a visual medium. The SQL tab helps you identify bottlenecks and guides you through the steps required to tune SQL statements.
...
If you are running Oracle 10g and later, you can also access the execution plan associated with a CREATE TABLE as SELECT statements. In addition to examining the plan, this lets you gain additional insight into a problem by analyzing the findings displayed for these statements.
Note: The statement will only explained. It will not be parsed.
Note: To analyze an execution plan, Precise for Oracle provides you with a full picture of the objects (table, indexes, and so on) participating in the plan. You can change a plan by modifying your query or changing the schema, for example by adding an index. To assist in this, Precise for Oracle can provide you with index recommendations for your statement.
One technique to identify the source of the problem is to view the historical information of statements, showing performance degradation. A change in the schema, volume, or execution plan may explain the impact on performance.
...
The following table lists the tabs and entities from which you can launch the SQL tab.
Table 8-1 Launching 1 Launching the SQL tab in context
Tab Entities
Dashboard Statement
...
What-If Statement that is affected by one or more index evaluations.
In the Current, Activity, and Objects tabs, you can either launch the SQL tab by clicking the SQL tab button when the selected entity in the Main area is a statement, or by selecting a statement in the Association area and clicking the Tune icon that appears before the SQL text.
Note: For a PL/SQL, the SQL tab offers only limited functionality.
If the SQL tab is opened with no statement in context, a message prompts you to open a statement.
...
There can be statements with many execution plans. Only the latest 7 plans appear on the tree. Click More..., if available, to view additional execution plans.
see See “About the Dashboard tab” on page 43. see , “About the Current tab” on page 51.see , “About the Activity tab” on page 61. see , and “About the Objects tab” on page 87.*
How the SQL tab is structured
The SQL tab lets you analyze execution plans and explain results so that you can tune statements and achieve optimal results.
The following controls determine the information displayed in the SQL tab:
...
- Related SQL selector
...
- View tabs
...
- Tuning Action buttons
About the Related SQL selector
The Related SQL selector appears only in the Plan view if a statement has a related SQL. The first time you visit the SQL tab, it will not be visible. It lets you choose one of the alternative statements that are associated with an original statement. An original statement is one captured by the Collector, imported, or entered manually. Full details on the related SQL are held in the Related SQL view.
Do any of the following to create a related SQL:
...
- When you edit a statement and save an alternative. see “Editing an existing statement” on page 154.
...
- When you click New Alternatives in the Related SQL view, and Precise for Oracle creates an alternative SQL for you.
...
- See “Generating new alternatives” on page 156.
...
- When an index recommendation is made in the What-If tab and you click Compare to switch to the SQL tab, in which case a copy of the statement is saved along with its new virtual index and execution plan.
...
- See “About the What-If tab” on page 159.
...
...
About the View tabs
The SQL tab divides the information on the selected statement into different views. Each view has a different layout and presents different information.
The following views are available:
■ Plan
■ Run Alternatives
...
- Plan
- Run Alternatives
- More ...
About viewing the execution plan of a statement
...
Two types of execution plans are available when launching to the SQL tab:
- Real execution
...
- plan. The plan which was collected from Oracles V$ tables. Additional real-time statistics are available for these plans. For example, you can view a breakdown of In Oracle time for a plan as compared to other plans, or a breakdown of its I/O when accessing Oracle objects.
- When launching to the SQL tab with a specific SQL statement in context, Precise for Oracle presents actual execution plan information, including actual plan steps, and information and statistics for multiple plans. For example, if a statement has several different execution plans, all are displayed.
...
Note: This feature is only available for Oracle 10.1.0.4 monitored instances and later.
...
- Estimated execution
...
- plan. Depending upon the version of Oracle running on your system (such as Oracle 8i), you may be only able to generate and run an estimated execution plan. At times, real execution plan information is lost (as when they are removed from the system before Precise for Oracle could access them). If this happens, all real-time statistics (such as, I/O, and In Oracle) are considered
...
- to belong to other plans.
...
...
The Plan view is divided into two panes. The Execution Plan tree is displayed in the left pane. The Details area is displayed in the right pane. The information displayed in the Details area is controlled by the information tabs (Highlights, Expanded Text, Objects, and More...) that are located above this area.
The following table describes the information displayed in the Plan view.
Table 8-2 Information 2 Information tabs
Information Tabs Description
...
Displays details on the execution plan, where the statement is stored, and the SQL text of the statement
Note: If you use the Oracle rule-based Optimizer, cost information is not available. If tables and indexes are not analyzed, statistics changes are not available.
About the Execution Plan tree
...
The execution plan's text or expanded text (with all referenced views expanded) appears both at the bottom of the execution plan tree (by clicking the plus sign (+) to view text) or at the top of the Details area (right pane). The text that relates to the selected step in the Plan tree is highlighted. This lets you view the text of the statement, the execution plan, and additional information, such as the objects referenced by the statement, all at once. see See “About expanded text of a statement in the Plan view” on page 135.
Actions that can be performed on the tree
You can perform the following actions on the execution plan tree:
...
- The Playback controls, located at the top of the tree, enable you to freely move within the execution plan of an explained statement.
...
- By moving the pointer over the execution plan steps, you can view more statistical information, such as Estimated
...
- Cost, Estimated Rows, and Estimated I/O Cost, and description of the specified step.
...
- Selecting a specific step highlights nested steps and affects the information displayed in the information tabs: the relevant SQL text is highlighted on the Text tab; the referenced tables, indexes, and columns are shown on the Objects tab; and the relevant step is highlighted on the Statistics tab.
...
- Clicking the plus sign (+), located at the bottom of the tree, displays and highlights the statement's formatted text or expanded text.
...
About the text of a statement in the Plan view
The Highlights tab, in the SQL tab, displays the execution plan's formatted text and findings that enable you to identify the most probable cause of the problem your statement is experiencing. The Findings table and Findings Details area provide additional information on what steps you can take to continue your analysis and lets you launch to the appropriate tab, with the statement in context.
The text that relates to the selected step in the execution plan tree (on the left) is highlighted. The highlight color is based on the selected step type. The structured text of the execution plan is displayed in the right pane.
When a table is highlighted, all table columns appearing in the text are highlighted in the same color as the table. The following is an explanation of the major step types.
About steps that access a table or index
The table and its columns are highlighted and color-coded. The columns that are accessed by an index are underlined.
About sort steps
All tables whose columns were used by sort steps are highlighted and color-coded. Each table and its corresponding columns have their own color. The columns that participate in the sort are underlined.
About join steps
The methodology for highlighting nested loop steps differs slightly from that of the merge join and hash join steps. Nested loop steps are highlighted as follows: all tables and their corresponding columns participating in a sub-tree of the outer table (the first step that is a direct descendant of the nested loop step) are highlighted in blue; all tables and their corresponding columns participating in a sub-tree of the inner table (the second step that is a direct descendant of the nested loop step) are highlighted in red.
The highlight methodology for the other join steps (hash join and merge join) differs slightly. The first table and all its corresponding columns are highlighted in blue. The columns that are used for the join are underlined. The second table and all its corresponding columns are highlighted in red. The columns that are used for the join are underlined.
About expanded text of a statement in the Plan view
...
In the SQL tab, a statement with the original text appears in the bottom left corner and the expanded text on the right at the top. The expanded view text is shown in bold. The statement highlighting picks out the columns within the view referenced in the explain plan.
About the objects that are referenced by the execution plan
...
Each table has a title denoting the entities highlighted. The lower table could have a title denoting either the columns of a table or the columns of an index.
Note: If tables and indexes are not analyzed, statistics information is not available.
About tables in use
The Tables in use lists all the referenced tables in the tree.
The following table describes the information displayed for the tables that are accessed in the execution plan.
Table Table 8-3 Information 3 Information on tables used in the plan
Column Description
Launches the Objects tab so that you can focus on the specified table.
Locates and highlights all the steps in the execution plan that access the specified table.
Table 8-3 Information on tables used in the plan
Column Description
Used Indicates whether the specified table is used in the selected step in the execution plan tree.
...
I/O Wait Time of IO wait in the specific plan.
About the indexes defined for the selected table
...
The following table describes the information displayed for the indexes that are used to access the selected table.
Table 8-4 Information 4 Information on the indexes used to access the selected table
Column Description
Launches Launches the Objects tab so that you can focus on the specified index.
...
B-Level Depth of a B*Tree index.
Table 8-4 Information on the indexes used to access the selected table
Column Description
Clustering Factor Clustering factor of the index.
The clustering factor is an important factor in determining how efficiently an index range scan will retrieve data from the table. It measures the degree to which the data in the index and its table are in the same order or, put another way, the probability that the next row to be fetched from the table is in the same block as the current row. It can vary between the number of blocks in the table (the best case because they are in the same order) and the number of rows in the table (the worst case because they are completely out of sync). The clustering factor tends to become worse over time as data is inserted and deleted. Note that the clustering factor makes no difference for a unique index lookup.
...
Locality Indicates if the index is local or global.
About the columns in table or index
...
The following table describes the information displayed for the columns of the table.
Table 8-5 5 Information on the columns of a table
Column Description
Launch the Objects tab so that you can focus on the specified column.
...
Indexes List of indexes in which the column is used.
About statistical information on all operators in the execution plan
The Statistics tab displays statistical information on all steps in the execution plan tree. You can sort steps by their estimated cost or by any other column in the table.
The following table shows the information displayed in statistics table for the execution plan.
Table 8-6 Execution 6 Execution plan statistics
Column Description
Locates and highlights the step in the Execution Plan tree that matches the selected step in the grid.
...
Plan Hash Plan hash value.
About information on statement plan, location, and text
...
The following table describes how the information on the Workshop tab is structured.
Table 8-7 Structure 7 Structure of information on the Workshop tab
Item Description
Explained on Date the latest explain plan was first generated.
...
Cabinet Cabinet where the statement is stored.
Table 8-7 Structure of information on the Workshop tab
Item Description
Folder Folder within the cabinet Folder Folder within the cabinet where the statement is stored.
...
Comment User-defined comment that can be entered against a statement when it is saved. see “Editing the properties of a statement” on page 31.
About the Recommend button
...
We recommend you to use the Oracle Advisor, as this provides an extended Oracle functionality. The recommendation van be evaluated by the What-If analysis.
Note: The Precise Advisor on the SQL tab will be used instead of the Oracle Advisor when: the Oracle Advisor is not installed, the Oracle version does not support it, or the usage of the Oracle Advisor was manually disabled in our application (for more information on how to manually disable the default Oracle Advisor, see “Disabling the Oracle Advisor” on page 139.
Disabling the Oracle Advisor
When you decide to disable the Oracle Advisor, you have to make changes in the registry file per instance. The result will be that the Precise Advisor will be used with the following limitation:
...
- When clicking the Recommend link in the Finding on the Activity tab the following message will appear:
Unable to perform the Recommend function. Verify that the Oracle Advisor is enabled in the Precise registry.
To disable the Oracle Advisor
1 For each instance, open the <SID_name>.xml file in the following location:
\products\i3fp\registry\products\indepth-oracle\hosts\<host_name>\oracle
2 Under the <recommend> tag, add/edit the following tag:
...
Where N disables the Oracle Advisor.
3 Save the <SID_name>.xml file.
4 Restart the Precise for Oracle FocalPoint.
About the Run Alternatives view
...
Each row in the Alternatives table (displayed in the left pane) can represent details of one of the following:
...
- The original statement.
...
- Any of the statements alternatives.
...
- If you decide to run the original statement or any of its alternatives, a new row, describing the details of the run, is added to the table.
The following table describes the information displayed for the alternative statements.
Table 8-8 Alternative 8 Alternative Statements
Column Description
Launches to the Plan view with the statement in context and lets you view the statement's execution plan, in the Highlights tab.
...
Table Scan Blocks Number of blocks fetched from tables.
Table 8-8 Alternative Statements
Column Description
Recursive Calls Number of recursive SQL calls that Oracle made to the data dictionary while executing the statement.
...
Actual Executions Number of executions completed.
About the text of an alternative statement
This view displays the text associated with the alternative statement.
About the plan tree of an alternative statement
This view displays the plan associated with the alternative statement, or the original statement.
About the Run Info view
The Run Info view displays bind variables and session parameters used during the run of the selected alternative statement.
About the Extended Statistics view
...
The following table describes the additional statistics information displayed for each alternative.
Table 8-9 Extended 9 Extended Statistics table
Column Description
# Displays the sequential number of the step
...
Executions Displays the number of executions of the alternative statement
About the History view of resource consumption
...
The History view is divided into two areas:
...
- Main area
...
- Change History area
...
...
Note: If you use the Oracle rule-based Optimizer, cost information is not available. If tables and indexes are not analyzed, statistics changes are not available.
About the Main area of the History view
...
The following views are available in the Main area:
■ Overview
■ Text
- Overview
- Text
About the overview of history information
The Overview, in the SQL tab, displays the following overtime graphs:
Table 8-10 Graphs 10 Graphs
Graph Description
In Oracle (Avg.) Illustrates the average resource consumption of the statement over time; does not detail the states or substates.
...
The execution plan changes information is generated whenever you explain the statements using Precise for Oracle, either using the PMDB process or the SQL tab.
The statistics changes information is generated whenever you run the PMDB process Collect Schema Changes. It scans all the objects referenced by the statement.
The schema changes information is generated whenever you run the PMDB process Collect Schema Changes. It scans all the objects referenced by the statement. see “About the Change History area” on page 143.
Executions Displays the number of executions of the selected statement over time.
About the text of a statement in the History view
The Text view, in the SQL tab, displays the full SQL text of the statement and information on the statement and execution plan, as follows:
Table 8-11 Text 11 Text view display
Information Description
...
Saved on The date that the statement was saved.
Table 8-11 Text view display
Information Description
Parsing Information Indicates whether Precise for Oracle has performed an extra level of parsing above that performed by Oracle to support more detailed analysis and syntax color highlighting.
For statements that are explained in the background, parsing must be enabled in the Explain Statements PMDB process in AdminPoint (see the Precise Administration Guide) for statements that are explaining in the background. For statements that you explain in the SQL tab, parsing must be enabled from Settings>General Settings>SQL.
By default, parsing is enabled. Some statements cannot be parsed. See the Precise Administration Guide. see See “About SQL settings” on page 27.
...
Parsing User In the case of an imported or manually saved statement, the Oracle user that was specified at the time; in the case of an automatically captured statement, the first Oracle user that the Precise for Oracle Collector found running the statement.
About the Change History area
The Change History area displays the changes that may have affected the performance of the statement over time. Information on the following type of changes is displayed:■
- All changes
...
...
- Schema changes
...
...
- Statistics changes
...
- Execution plan changes
...
...
- Run statistics history
...
About viewing all changes
The All Changes view displays a list of all changes made. The changes can be statistics changes, schema changes, and execution plan changes. If there are many changes, you may want to use one of the other change types or filter your search using the More... option. see See “Associating entities with data that meets specific criteria” on page 30.
The following table describes the information displayed in the All Changes table.
Table 8-12 12 All Changes table
Column Description
Switches to the Plan view and displays the first access plan that was created by the Explain Statements process after the specified change was detected.
...
Timestamp Indicates the date that the change was detected.
Table 8-12 All Changes table
Column Description
Change Type Displays the type of change detected. Can be one of the following values:
...
Different Execution Plan, the Change Details column gives the estimated cost.
About schema changes
The Schema Changes view displays a list of all schema type changes made.
The following table describes the information displayed in the Schema Changes table.
Table 8-13 Schema 13 Schema Changes table
Column Description
Switches to the Plan view and displays the first access plan that was created by the Explain Statements process after the specified change was detected.
...
Columns Indicates the columns in the object that were affected by the change.
About statistics changes
The Statistics Changes view displays a list of changes made to Oracle statistics on tables, indexes, and columns. The following table describes the information displayed in the Statistics Changes table.
Table 8-14 Statistics 14 Statistics Changes table
Column Description
Switches to the Plan view and displays the first access plan that was created by the Explain Statements process after the specified change was detected.
Table 8-14 Statistics Changes table
Column Description
Launches the Objects tab with the selected instance.
...
Clustering Factor A change in the clustering factor of an index.
The clustering factor is an important factor in determining how efficiently an index range scan will retrieve data from the table. It measures the degree to which the data in the index and its table are in the same order or, put another way, the probability that the next row to be fetched from the table is in the same block as the current row. It can vary between the number of blocks in the table (the best case because they are in the same order) and the number of rows in the table (the worst case because they are completely out of sync). The clustering factor tends to become worse over time as data is inserted and deleted. Note that the clustering factor makes no difference for a unique index lookup.
About execution plan changes
The Execution Plan Changes view displays a list of all the access plans of the statement as detected by the Explain Statement process.
The following table describes the information displayed in the Execution Plan Changes table.
Table 8-15 Execution 15 Execution Plan Changes table
Column Description
Switches to the Plan view and displays the first access plan that was created by the Explain Statements process after the specified change was detected.
...
Index Scans Number of index scans performed.
Table 8-15 Execution Plan Changes table
Column Description
Steps Bar graph showing the number of steps in the execution plan and a visual breakdown of the different types of operations performed.
About the run statistics history
The Run Statistics History view displays Oracle run statistics from when the statement has been run manually from within the tool. Any changes that it makes are rolled back. The only overhead is the execution time. A time-out may be specified in the Run dialog box to prevent the statement from taking too long. see See “Running a statement” on page 155.
...
The following table explains the information shown on the General tab.
Table 8-16 Information 16 Information shown on the General tab
Column Description
Timestamp Displays when the statement was run.
...
Status One of the following states: Started, Completed, or an error message if an error has occurred.
The following table explains the information shown on the Statistics tab.
Table 8-17 Information 17 Information shown on the Statistics tab
Column Description
Timestamp When the statement was run.
...
Table Scans Number of full table scans performed.
Table 8-17 Information shown on the Statistics tab
Column Descriptionscans performed.
Table Scans by Row ID Number of accesses to a table by Row ID. A Row ID contains the address of a row in a table and is the fastest way to gain access to an individual row, although not necessarily to multiple rows. Access using Row ID usually follows an index scan, because Row IDs are stored in the leaf blocks of an index.
...
Recursive Calls Number of recursive SQL calls that Oracle made to the data dictionary while executing the statement.
Comparing execution plans
...
The Compare view is usually opened in context from other views within the SQL tab (such as History or Related SQL) or other tabs (such as What-If). To open the Compare view in the context of the selected execution plan and compare it with the latest execution plan, click the Compare icon.
Click the Compare icon to display the list of execution plans available for a statement.
To compare the execution plans of a specified statement
...
- Click the Compare icon.
...
- From the list of access plans that belong to the statement and related SQL, choose the access plan or related
...
- SQL you want to use for a comparison from the list.
...
- Click OK.
The statement you selected is displayed in the right pane. The statement's text is displayed in the bottom pane.
About the All Plans view
The All Plans view displays information on all the available plans. Use this view to analyze and compare the plans to determine which is the best plan to run. Every row in the All Plans table (displayed in the left pane), describes a different plan belonging to the current statement.
The following table describes the information displayed in the All Plans table.
Table 8-18 All 18 All Plans table
Column description
Launches to the SQL tab with the selected plan in context.
...
First Detected For actual plans, displays when the plan was first encountered.
For estimated plans, displays when the statement was first explained and the plan was produced.
Table 8-18 All Plans table
Column description
Last Detected For actual plans, displays when the plan was last encountered.
For estimated plans, displays when the statement was last explained and the plan was produced.
...
Executions Displays the number of times the plan was executed, during the selected time frame.
About the all plans overview
The All Plans overview lets you analyze how plans changed over time. This information is displayed in the following graphs:
...
- In Oracle (Avg.) vs. Executions
It displays the average time spent in Oracle vs. the number of executions, over time. This graph lets you analyze the scalability of the different plans displayed in the All Plans table.
It notices that at a certain point in time one of the plans was replaced by another and that the AVG in-oracle time of the second plan is considerably higher than the first one.
...
- In Oracle
It displays the total time spent in Oracle by statements which were using this plan over time.
...
- Cost
It displays the cost of the retrieved for the plan over time.
About the Plan tree
The Plan tree displays execution plan of the selected statement.
About the Bind Variables view
...
Precise for Oracle can capture statements with bind variables if the following conditions exist:
...
- Oracle 10.1.0.4 or later is running
...
- The relevant PMDB process (Collect Bind Variables) is up and running.
...
- The statement is a heavy statement that is run frequently.
Different values for the same bind variable can dramatically change the performance of a statement. By analyzing a statement's bind values, you can:
...
- Run a statement with a real set of bind values, and observe how different binds sets influence the statement's execution).
...
- Note the existence of different sets of bind values for a statement.
...
- Determine if a correlation can be drawn between a statements performance to the bind values used during the period in question.
...
About the Bind Variables table
The following information is displayed for each bind set in the Bind Variables table in the left pane:
Table 8-19 Bind set
Bind Set Description
...