Oracle SQL Tuning: Tuning SQL and the Optimizer

Application Developers Database Administrators and Designer/Developers. Learners should be familiar with the basic principles of SQL and PL/SQL or have completed courses 61110 61111 61112 and 60113. In addition learners should have taken the first part in this series 60141.

• Identify the factors that affect network traffic.
• Identify the factors that affect parsing.
• Identify the guidelines for the reuse of parsed code.
• Identify the factors in SQL code design that affect response time.
• Identify the methods to improve network performance.
• Identify the differences in optimization approaches.
• Influence the costbased optimization to improve performance.

Unit 1: SQL and Parsing

• Identify the advantages of using array processing.
• Enable array processing in SQL*Plus.
• Identify the advantages of using server-side code.
• Identify whether a given code uses an explicit or an implicit cursor.
• Identify the advantage of using explicit cursors.
• Identify the advantages of using explicit cursors.
• Identify the language that will process a given task in the minimum possible time.
• Identify the methods used for maintaining a shared pool.
• Identify the contents of a shared SQL area.
• Sequence the steps performed in the Oracle Server for processing a SQL statement.
• Identify the benefits of sharing cursors.
• Identify whether or not the given pair of statements will share cursors.
• Identify the guidelines for coding SQL in order to share cursors.
• Match the columns of the V$LIBRARYCACHE view with the information that they contain.
• Match the columns of the V$SQLAREA view with the information that they contain.

Unit 2: Efficient SQL Code Design: Guidelines

• Identify the type of the SQL statements that are most likely to benefit from tuning.
• Identify the conditions that will block the use of indexes in a given code.
• Identify the guidelines for writing the SQL code that requires low response time.
• Identify whether or not a query to accomplish a given task should use a given view.
• Identify the type of query that will be appropriate in a given situation.
• Select the query that will produce minimum network traffic in a given situation.
• Identify the guidelines to be followed when testing SQL statements.

Unit 3: Influencing the Optimizer

• Identify the functions of the optimizer.
• Identify the transformations that an optimizer makes to a SQL statement.
• Match the operators with the transformation made to them by the optimizer.
• Determine whether a given condition has good or bad selectivity.
• Determine the type of selectivity that a given condition with bind variable will have.
• Identify the steps used by the optimizer in rule-based optimization.
• Identify the benefits of cost-based optimization.
• Identify the steps used by the optimizer in cost-based optimization.
• Set the optimization approach at the instance level by using the OPTIMIZER_MODE initialization parameter.
• Collect the statistics of a table by using the ANALYZE command.
• Identify the guidelines for using histograms.
• Collect histogram data for a specific column in a table.
• Match the various join methods with the situation in which they are used.
• Match the data retrieval methods available to the optimizer with their features.
• Match the access path hints with the optimizer functions they enable.
• Specify a hint for the FULL access path in a given query.
• Sequence the steps in preparing for a star query.
• Identify the hint that gives the highest performance when used in a give type of star query.
• Set up a hash join at the instance level.

8

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).