Page History

...

It is possible to perform the following tuning actions:

Table 8-23 Tuning actions

Action    Description

...

...

...

...

...

...

...

...

...

Opening an existing statement

The Open option lets you open an existing statement in the PMDB that was previously created by using the New button, collected by the Precise for Oracle Collector, or imported from the Activity tab. The SQL tab is populated with the explain plan of your chosen statement. Alternatively, you can open a statement by selecting it in another tab and clicking the SQL tab button or the Tune icon.

...

• Plan
• Recommend
• Run Alternatives
• History
• Compare
• All Plans
• Bind Variables

...

To open an existing statement

1. Click Actions>Open.
2. In the Open Statement dialog box, choose the relevant properties for the statement that you want to view from the drop-down lists, as follows:
   • Instance. Indicates the name of the instance that the statement belongs to.
   • Cabinet. Indicates the name of the cabinet that the statement is saved in.
   • Folder. Indicates the name of the folder that the statement is saved in.
   • Statement. Indicates the ID or user-defined name of the statement.
   • Comment. Provides an optional comment that was previously associated with the statement.
   • Text. Provides a preview of the SQL text for the currently selected statement.
     The maximum number of items displayed in the Cabinet, Folder, and Statement lists is limited.
3. Click OK.

Creating a new statement

The Create option lets you create a new statement and save it in the PMDB in a logical cabinet and folder hierarchy. However, you cannot create a new statement with the same combination of cabinet, folder, and name as an existing statement. You cannot create a statement with exactly the same text as an existing statement.

...

1. Click Actions>New.
2. In the New Statement dialog box, choose the relevant properties for the new statement from the drop-down lists and fill in the fields, as follows:
   • Instance. Indicates the name of the instance that the statement belongs to.
   • User. Indicates the Oracle user name that will parse the statement.
   • Cabinet. Indicates the name of the cabinet that the statement is saved in.
   • Folder. Indicates the name of the folder that the statement is saved in.
   • Statement. Indicates the user-defined name of the statement.
   • Comment. Provides an optional tuning comment that is to be associated with the statement.
   • Text. Indicates the SQL text for the statement.
     The maximum number of items displayed in the Cabinet, Folder, and Statement lists is limited. You may also type the names.
3. In the Text field, enter the text for the statement.
4. In the Comment field, type a tuning comment that is associated with the statement, if required.
5. Click OK. The new statement is saved in the PMDB in the cabinet and folder hierarchy.

Editing an existing statement

The Edit Text option lets you edit a statement that was saved in the PMDB through the New option. Collected and imported statements cannot be edited. However, you can use the Edit dialog box to save a related SQL statement and then edit the related SQL statement.Note:

You can edit existing statements in the following views:

...

To edit an existing statement

1. Click Actions>Edit Text. The Edit Statement dialog box opens.
2. In the Edit Statement dialog box, do one of the following:
   • Enter a new name for the statement in the text field to rename the existing statement.
   • Enter a new name for the statement, check the Save as alternative option to save your changes under a different name.
   Either choose or save it as a related statement by entering a new name.
   If the statement was collected by the Collector, you can only save it under a new name. In this case, the Save as alternative option is pre-selected and cannot be unchecked. See “About a statement's different versions” on page 151.
3. In the Text box, edit the SQL text for the statement.
4. Click Save. The edited statement is saved in the same cabinet and folder as the original.

Running a statement

The Run option lets you run a statement in the monitored database so that you can gather its execution statistics. Any changes that it makes are rolled back. The only overhead is the execution time. A timeout may be specified in the Run dialog box to prevent the statement execution from running too long.

You can run a statement in the Run Alternatives view. see “Editing the properties of a statement” on page 31.

To run a statement

1. Click Actions>Run.
2. On the General tab in the Run Statement dialog box, do the following:
   1. Select the database instance that the statement will run on. The maximum number of items displayed in the Cabinet, Folder, and Statement lists is limited.
   2. In the Description box, type a short description of the run.
   3. Enter the Oracle user name and password with which to log in to Oracle.
   4. Choose one of the following options: Fetch all rows or Fetch first n rows
   5. Specify the number of times that you want to run the statement. You may want to run it more than once to reduce the effect of having to load the buffer cache on the first run. Precise for Oracle calculates and displays the average value for each statistic over the series.
   6. Check the Time out after n minutes or hours, if you want to configure a timeout after which the execution of the statement is aborted if the timeout is exceeded. If this is an alternative version of a statement that you have saved, a reasonable timeout is the duration of the original version because you are unlikely to be interested in slower alternatives.
   7. Check Run in background, if you want to run the statement in the background.
3. On the Bind variables tab in the Run Statement dialog box, choose the bind variables with which to run the bind set as follows:
   1. Click Choose bind set.
   2. In the Bind Sets dialog box, choose the bind set you want to use to run the statement. see “About bind sets” on page 156.
   3. Click OK.
4. On the Advanced tab in the Run Statement dialog box, specify the session parameters to be used when running the statement:
   1. Type in a value for each session variable that you want to run the statement with.
   2. Click Add New Row if you want to add a new session parameter. Choose a session parameter name from the list, or enter a new one. Type in the value you want to run the statement with. See “About session parameters” on page 156.
   3. If you want to delete a session variable from the statement run, select the session variable you want to delete and click Delete.
   4. Click OK.
   5. Check the Extended run information (STATIC_LEVEL=ALL) option if you want additional statistics to be collected. These statistics are displayed in the Extended Statistics tab in the Run Alternatives view.
   6. Check the Array fetch option and specify the number of rows you want to be fetched, if you want to use an array fetch operation.
5. Click Fill Binds.
6. Click OK.

About bind sets

Bind variables are placeholders in a statement. When you use bind variables, you increase the probability that statements will be stored in memory, making them more immediately available to the next operation that needs them. Bind variables obtain their values from the last statement that was run. You can change the values of bind sets and analyze the values used when a statement is run, to help improve your tuning process.

About session parameters

Session variables obtain their values from the last statement run. You can define a new session variable to run the statement with, in the Add Session Parameters dialog box.

Re-explaining a statement

The Re-Explain option instructs Precise for Oracle to re-explain a statement. Precise for Oracle generates a new execution plan if one or more of the following conditions are met:

...

You can set the parsing user from Settings>General Settings>SQL tab. You can re-explain a statement in the following views:

...

To re-explain a statement, in the Plan or All Plans view, click Actions>Re-Explain.

Generating new alternatives

The Generate Alternatives option instructs Precise for Oracle to run the alternative SQL generator to see if it can find ways of re-writing the SQL so that the Oracle Optimizer comes up with a more efficient execution plan. It performs various mathematical transformations to the SQL, such as replacing EXISTS with IN, OR with UNION, but still so as to give an equivalent result set. The alternatives are saved in the same folder as the original and can be viewed in the Related SQL view.

You can control the settings for generating alternatives from Settings>General Settings>SQL tab, such as only generating alternatives that have a different execution plan or indexable columns.

...

To generate a new alternative, in the Run Alternatives view, click Actions>Generate Alternative.

Anchor
HowtheSQLtabcanhelpyouidentifyperformanceproblems HowtheSQLtabcanhelpyouidentifyperformanceproblems

How the SQL tab can help you identify performance problems

...

You can identify a performance problem by doing one or more of the following:

• Identifying problematic steps in the access plan
• Getting advice on a selected statement
• Creating a related SQL
• Comparing a related SQL

Anchor
Identifyingproblematicstepsintheaccessplan Identifyingproblematicstepsintheaccessplan

Identifying problematic steps in the access plan

The first step in tuning a statement is to identify problematic steps.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. Do one of the following:
   • Launch to the SQL tab with a statement in context, for tuning.
   • In the SQL tab, open the statement you want to analyze.
3. The SQL tab shows SQL text on the bottom left, and the execution plan on the top left of the SQL tab. For an example, we see that the SQL is accessing the ORDERS table using a full table scan. The top table on the right will show that the ORDERS table has 30000 rows in it. This is the problematic step.

Anchor
Gettingadviceonaselectedstatement Gettingadviceonaselectedstatement

Getting advice on a selected statement

In our example, we can identify in the statement text three conditions in the WHERE clause of the statement that restrict the amount of data required from the ORDERS table. However, the Oracle Optimizer has determined that every record in the ORDERS table must be read. There are several reasons for this occurrence. We can investigate this problem further by clicking the Recommend button.

Anchor
CreatingarelatedSQL CreatingarelatedSQL

Creating a related SQL

The Related SQL view lists the original statement along with any related statements that have been created. Initially there will only be the original statement.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. Do one of the following:
   • Launch to the SQL tab with a statement in context, for tuning.
   • In the SQL tab, open the statement you want to analyze.
3. Click Run Alternatives. It instructs Precise for Oracle to automatically suggest alternate ways of re-writing the SQL. For our example, two alternatives have been suggested for this statement. The first alternative has a lower cost than the original.
4. Click the Text tab to see a second alternative. The second alternative has no cost because it is using a /*+ RULE */ hint to force the use of Oracle's rule-based Optimizer, rather than the cost-based Optimizer.

Anchor
ComparingarelatedSQL ComparingarelatedSQL

Comparing a related SQL

You can carry your analysis of the change further by selecting the Compare view. The Compare view compares two access plans for the same statements.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. Do one of the following:
   • Launch to the SQL tab with a statement in context, for tuning.
   • In the SQL tab, open the statement you want to analyze.
3. Click the Compare icon. The alternative statement opens both the original and the alternative SQL statement in the Compare view.

...