Oracle SQL Tuning: Diagnostics and Tuning Schema

Course Overview

This course is the first in a two-part series on SQL Statement Tuning. This course will help experienced application developers to learn a tuning methodology they can use to maximize database performance with minimal effort. Participants will gain experience creating optimal access paths, such as partitioned views, B-tree indexes, bitmapped indexes, index clusters, and hash clusters. Participants will then be able to test the results of their tuning efforts using performance analysis tools.

Learn To:

  • Identify the need and areas for tuning the performance of SQL statements.

    •  
  • Identify tuning steps.

    •  
  • Identify the tools that help you test an diagnose performance problems with a SQL statement.

    •  
  • Use efficient data access methods that are available for the optimizer.

    •  
  • Create the appropriate database objects before starting to tune SQL statements.
Content Emphasis

Skills-Based

Audience

Application Developers, Database Administrators, and Designer/Developers.

Total Learning Time

6 - 8 Hour(s)

Course Contents

Unit 1: Managing Performance
Duration: 2 - 3 Hour(s)
  • Identify the causes of SQL performance problems.
  • Identify the main system areas that can be addressed with the tuning process.
  • Sequence the steps involved in the tuning methodology.
  • Identify the guidelines to be followed when applying the tuning methodology.
Unit 2: Analyzing SQL Performance
Duration: 2 - 3 Hour(s)
  • Match the identifiers in the EXPLAIN PLAN command with their descriptions.
  • Match the columns in the EXPLAIN PLAN output table with their descriptions.
  • Identify the combination of operation and option used, given the interpretation of the EXPLAIN PLAN output.
  • Display the execution plan worked out by the optimizer for a given query by using the EXPLAIN PLAN command.
  • Set the SQL Trace initialization parameters in the parameter file.
  • Switch on SQL Trace.
  • Match the columns in the TKPROF output with their descriptions.
  • Match the TKPROF timing output columns with their descriptions.
  • Identify the additional components of the TKPROF output.
  • Sequence the steps to be performed for using SQLTrace.
  • Turn on autotrace option in the specified format.
  • Match the various performance analysis tools available tools available for identifying expensive statements with their functions.
Unit 3: Tuning the Schema
Duration: 2 Hour(s)
  • Identify the levels of B*-tree index structure.
  • Identify the features of a B*-tree index scan.
  • Identify the columns on which a B*-tree index should ideally be created.
  • Create a B*-tree index by using SQL commands.
  • Identify the features of bitmapped indexes.
  • Identify the advantages of bitmapped indexes.
  • Create a bitmapped index by using the CREATE INDEX command.
  • Identify the characteristics of the different cluster types.
  • Create a hash cluster by using the CREATE CLUSTER command.
  • Identify the guidelines for using a hash cluster.
  • Create an index cluster by using the CREATE CLUSTER command.
  • Identify the advantages of partition views.
  • Sequence the steps to create a partition view.
Regular price: $279.95padSale price: $249.95pad

Click here for Secure Yahoo Order: Oracle SQL Tuning: Diagnostics and Tuning Schemapad

Get Train in Oracle
Oracle Training CD

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

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.