Audience
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.
Objective
- 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.
Topics Include
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.
Duration
8
Minimum Requirements
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).
The network 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 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).
Media
Serebra Learning Corporation 119 - 7565 132nd Street Surrey BC V3W 1K5 Canada
