Course Overview

Learn To:

• 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 cost-based optimization to improve performance.

Skills-Based

Audience

Application Developers, Database Administrators, and Designer/Developers.

Total Learning Time

6 - 8 Hour(s)

Course Contents

• 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.

• 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.

• 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.

Click here for Secure Yahoo Order: Oracle SQL Tuning: Tuning SQL and the Optimizer

Get Train in Oracle
Oracle Training CD

Best Regards,
Oracle Database, SQL, Application, Programming Tips