4
Starting a Tuning Session

This chapter contains the following topics:

• Starting a Tuning Session
• Creating and Working with Tuning Sessions
• Selecting a Statement for Tuning

Starting a Tuning Session

To begin your tuning session, you should have the following:

• the SQLADMIN or DBA role assigned to you, as described in the next section.
• "Create Table" privileges

Oracle SQL Analyze can be started from Tuning Pack drawer on the Oracle Enterprise Manager console, from the menu, or from the System Prompt.

To begin Oracle SQL Analyze from the menu, select Tools=>Tuning Pack=>Oracle SQL Analyze.

To begin from the System prompt, type vmq.

Assigning the SQLADMIN Role

In order to run Oracle SQL Analyze, you should assign the SQLADMIN role to the user that will run Oracle SQL Analyze. Having this role will make the tool more useful but it is not required.

The VMQROLE.SQL script has been provided to help automate the process of creating the SQLAMDIN role. It is located in the $ORACLE_HOME\SYSMAN\ADMIN directory.

1. From the DBA Management Pack program group via the Start menu, select the SQLPLUS Worksheet icon to start the application.
2. Use the Login Information dialog box to connect SYS to the database on which you want to run SQL Analyze.
3. From the Worksheet menu, select Run Local Script and browse to the Oracle_Home\sysman\admin directory to run the VMQROLE.SQL script which creates the SQLADMIN role for the managed database.
4. In the SQL*Plus Worksheet, assign the SQLADMIN role to the user by typing: Grant SQLADMIN to <user>;
5. Exit SQL*Plus Worksheet.

Creating and Working with Tuning Sessions

Throughout the tuning process, you will be working in a main window divided into a number of separate panes. The types of panes and their locations within the main window depend upon the type of action you are performing. For example, when you are generating and analyzing explain plans, the window will be divided into the Navigator pane, the SQL Text pane, and the Details pane.

The following sections describe how the Oracle SQL Analyze interface is used for different operations.

Oracle SQL Analyze Repository

Oracle SQL Analyze stores the information for tuning sessions in the Enterprise Manager repository. The following information is saved when you select Save to Repository from the File menu:

• information necessary to reconstruct the Navigator tree, including initialization parameter information and SQL and explain plan object names
• SQL statements
• all explain plans and related statistics

The list of discovered nodes in the Navigator window reflects the information provided by Oracle Enterprise Manager.

Object properties and their estimates/statistics are not saved in the repository.

Oracle SQL Analyze Main Window

The Main window is the basic work area for SQL statement creation and tuning. It displays the databases, sessions, and SQL statements you are working with, as well as the explain plans for the SQL statements.

As shown in Figure 4-1, the Main window is usually divided into three panes:

• The Navigator pane displays a tree view listing available nodes, objects, and SQL statements.
• The SQL Text pane lists the selected SQL statement. It also displays the list of SQL statements called by TopSQL or the SQL History.
• The Details pane displays information about the selected statement, including explain plans, object properties, and performance statistics.

Figure 4-1 Main Window

Text description of the illustration all.gif

The Navigator Pane

The Navigator pane provides access to the database services against which you can tune your SQL statements. The Navigator pane is always displayed, and is shown in Figure 4-2.

Figure 4-2 Navigator Pane

Text description of the illustration navtree.gif

The top level is the database node, just as it is represented in Oracle Enterprise Manager Navigator pane.

Clicking on the "+" symbol for a Database reveals the Initialization Parameters, TopSQL, SQL History, SQL Statement, Explain Plan, and Tuning Recommendation objects related to that database.

Clicking on the TopSQL object activates the TopSQL filtering operation, which allows you to sort SQL statements stored in V$SQLAREA according to the resources they consume. TopSQL items can be double-clicked as well as dragged and dropped.

Selecting the SQL History object calls the SQL History, which allows you to sort SQL statements stored in the SQL History repository.

Clicking on the Initialization Parameters object displays instance parameters you can edit to simulate different database environments.

The SQL Statement Object contains a single specific syntactical version of a SQL statement. Clicking on this object displays the statement in the SQL Text window.

The Explain Plan object contains a single explain plan generated for the SQL statement. Oracle SQL Analyze lets you generate Rule-based explain plans for all SQL statements, and Cost-based explain plans for SQL statements that have been analyzed with the ANALYZE command.

The Index Tuning Recommendations object is created if you generate index tuning recommendations for the SQL statement.

SQL Text Pane

The SQL Text pane, as shown in Figure 4-3, displays the SQL statement currently being analyzed.

In this pane, you can edit the statement.

Figure 4-3 SQL Text Pane

Text description of the illustration txtwin.gif

The statement name, type of explain plan that is currently displayed in the Details pane, and the owner of the current schema are listed at the top of the SQL Text pane.

The two buttons to the right of the text pane allow you to confirm or reject edits made to the SQL statement and revert back to the last saved version of the statement.

To confirm and save an edit, select the Apply button (marked by a check mark).

To reject an edit and revert back to the last saved version, select the Revert button ("X").

When you select either Apply or Revert, Oracle SQL Analyze will refresh all child objects of the SQL object, such as the explain plan or index tuning recommendations. If you select Cancel, the child objects will be marked as invalid until they are refreshed.

When you are using TopSQL or SQL History, the SQL Text window displays multiple SQL statements, sorted by order of resource consumption, as shown in Figure 4-4. You can drag these statements over to the Navigator window or double-click on these statements to create SQL Statement objects.

Figure 4-4 SQL Text Pane When Using TopSQL or SQL History

Text description of the illustration tswin.gif

Details Pane

The Details pane displays information about the subject of your analysis. Its size appearance differs depending upon the operation being performed.

If, for example, you are creating explain plans based on the statement in the text pane, the Details pane occupies the space to the right of the Navigator pane and below the SQL Text pane as shown in Figure 4-1. If you are examining initialization parameters of a database, the Details pane occupies all the space to the right of the Navigator pane, as shown in Figure 4-5.

Figure 4-5 Details Window Showing Initialization Parameters

Text description of the illustration ip.gif

Selecting a Statement for Tuning

Although there are several ways to begin a SQL tuning session, the most likely scenario is that you want to identify existing SQL statements that are creating bottlenecks in your system. Statements that have the most potential to improve performance, if tuned, are those that:

• consume the greatest overall resources
• consume the greatest resource per rows (or per run)
• are executed most frequently

You can use the TopSQL function to sort the statements located in the V$SQLAREA view (those statements that have been run or are ready to be run against the database) according to their resource consumption. TopSQL is described in the next section, "Selecting Statements with TopSQL."

You can also examine statements stored in the SQL History repository. You can select statements from the SQL History the same way you select statements fromTopSQL. For more information about the SQL History, see "Using SQL History".

Other ways to select a statement for tuning include:

• entering a new SQL statement, as discussed.
• importing a statement from a SQL file, as discussed.
• opening a previously used tuning session, as discussed.

Selecting Statements with TopSQL

TopSQL lets you examine SQL statements that have been used on the database and the resources they consume. Using the statistics in this list, you can determine which statements consume the most resources and then select them for tuning.

TopSQL takes its statistics from the V$SQLAREA view. The V$SQLAREA view lists statistics on shared SQL areas, and provides statistics on SQL statements that are in memory, parsed, and ready for execution or which might have been executed already.

To start a TopSQL analysis:

1. Click on the TopSQL object in the Navigator window. A SQL options dialog opens.
2. Select the resource in the Sort Statistic field for the consumption you want to measure.
3. In the Count area, select the number of SQL statements you want displayed.
4. In the Additional Filters area, you can retrieve TopSQL statements that contain a specific string (such as schema name or a specific DML identifier), or select the types of SQL you want displayed--either recursive or statements generated by SQL Analyze.
5. Select OK.

   The statements are displayed in the TopSQL View in order of their resource consumption.

6. Select a SQL statement you want to tune, drag it over to the Navigation pane, and drop it on the database node.
7. A new SQL object is created. You can now tune this statement in Oracle SQL Analyze.

Figure 4-6 TopSQL View

Text description of the illustration topsql.gif

TopSQL lets you sort the statements based on their use of the following database resources which are most likely to impact performance:

Buffer Cache Hit Ratio

The rate at which Oracle finds the data blocks it needs already in memory. The closer the hit ratio is to 100%, the better your system will perform.

Buffer Gets

The number of buffer gets for all cursors. A measurement of CPU usage, excessive buffer gets may indicate that this statement needs to be examined more closely.

Executions

The number of executions carried out for the statement.

CPU Usage Per Execution

This statistic shows the average number of buffer gets per execution. Excessive CPU usage may indicate that this statement needs to be examined more closely.

CPU Usage Per Row

The number of buffer gets per row processed.

Disk Reads

The number of disk reads for all cursors.

Disk Reads per Execution

This statistic takes the number of times the statement has been executed and calculates the number of disk reads per execution. Excessive disk reads may indicate that this statement needs to be examined more closely.

Executions

The number of times the statement was executed since it was brought into the library cache.

Parse Calls

The number of times a parsed representation of a SQL statement has been called-- or reused--for all cursors.

Parse Calls Per Execution

The number of times the SQL statement was parsed per execution. Ideally, a SQL statement should be parsed once and executed multiple times, but some front-end applications re-parse every time they execute. The ratio can range from 0 to 1. A ratio close to 0 is good. A ratio close to or equal to 1 indicates unnecessary parse calls.

Rows Processed

The total number of rows the parsed SQL statement returns. Depending upon the purpose of the statement, you may find the rows processed higher or lower than expected, indicating a need to examine this statement further.

Sorts

The number of sorts that was done for all cursors. An excessive number of sorts may indicate inefficient use of indexes or syntax that needs to be optimized.

Along with those listed above, the following statistics are displayed in the lower pane:

• Sharable Memory
• Persistent Memory
• Runtime Memory
• Sorts
• Version Count
• Loaded Versions
• Open Versions
• Users Opening
• Executions
• Users Executing
• Loads
• First Load Time
• Invalidations
• Parse Calls
• Disk Reads
• Buffer Gets
• Rows Processed
• Command Type
• Optimizer Mode
• Parsing User ID
• Parsing Schema ID
• Kept Versions
• Address
• Hash Value
• Module
• Module hash
• Action
• Action Hash
• Serializable Aborts
• Buffer Cache Hit Ratio
• CPU Usage Per Execution
• CPU Usage Per Row
• Disk Reads Per Execution
• Parse Calls Per Execution

You can read its complete list of V$SQLAREA statistics to gain a full understanding of the performance of a statement, then decide which statements require tuning, and what performance problems need to be addressed. These statistics are discussed more completely in the Oracle9i Database Performance Guide and Reference.

Entering a New Statement

To enter a new SQL statement, select SQL=>Create New SQL. Then enter the new statement in the SQL Statement pane located in the upper right portion of the Main Window.

Importing Statements From a SQL File

To import or copy an existing SQL statement into Oracle SQL Analyze:

From a SQL script

Select File => Open SQL to open a SQL script. A dialog will open, letting you select the desired SQL script.

From TopSQL or SQL History

Drag the desired SQL statement from the SQL Text pane onto a session or SQL object.

Opening a Previously Used Tuning Session

To open a previously used tuning session, click on the desired SQL statement object or explain plan object.

Printing

Oracle SQL Analyze prints SQL statements, explain plans, and statistical data for the statements and plans.

To print a SQL statement and its performance statistics, select the SQL object in the Navigator window, then select File=>Print.

To print a SQL statement, its explain plan, and the performance statistics for that explain plan, select the explain plan object in the Navigator window, then select File =>Print.

To print the list of TopSQL statements displayed in the SQL text pane, select TopSQL=>Print.

To print the list of SQL History statements displayed in the SQL Text pane, select History=>Print.

Saving

To save a SQL statement into a file, select File => Save SQL As.

To save the current tuning session, select File => Save to Repository.