|
Provided by: Serebra Learning Corporation Oracle SQL Tuning: Tuning SQL and the OptimizerOracle |
![]() |
This course is the second in a two-part series on SQL Statement Tuning. This course will help application developers to design SQL statements that can reuse parsing and influence the optimizer to improve performance.
|
|
||||||||
Training
Provided by Serebra Learning Corporation
- E asked: What is the cost of this course?
- A asked: Hi, I am looking for a course which teaches Orace SQL tuning for developers, near Quincy, MA, USA. Most of the courses that I came across are for DBAs rather than developers. Could you please provide any information on who provides courses in this field. Thanks,
Oracle SQL Tuning: Tuning SQL and the Optimizer
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.
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
Minimum RequirementsThe 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).
About The Training Provider: Serebra Learning Corporation
Serebra Learning Corporation - Serebra Learning Corporation provides technology-based training solutions through a combination of Cortex, its proprietary learning management system (LMS), and a curriculum catalog with over 1, 825 current courseware titles. Founded in 1987 (as FirstClass Systems, with a name change to Serebra in 2001), Serebra has over sixteen years" experience delivering e-learning solutions to both...

