Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

Oracle tuning for Siebel training class

3 days
© 2007-2016 by Burleson Corporation

This is a three-day course that is taught at your company site with up to 20 students.
 
Click here for on-site course prices

Key Features

* Understand how Oracle tuning applies to Siebel applications

* See how to tune Siebel SQL without touching the source code

* Learn instance tuning optimization for Siebel ERP applications

* See how to tune Siebel SQL with materialized views and query rewrite


 

  

Siebel, like many third-party vendor products which run on Oracle platforms, present special tuning challenges.  In these ERP systems, the DBA staff does not have direct control over the SQL within the Siebel application, and must reply on special Oracle tools to tune SQL execution plans.

This three-day class is an overview of Oracle tuning as it applies to ERP systems such as Siebel.  Students will learn the basics of instance and environmental tuning, with a focus of using SQL tuning and materialized views to improve application performance.

Additional days of training on Oracle tuning that applies to Siebel can be arranged to be customized according to your specific training needs.
 

This Oracle tuning training class is designed for the working financial Oracle professional and previous experience with billing and collections concepts is helpful.

Previous experience with relational database management and SQL is helpful, but this class is self-contained and has no formal prerequisites other than an understanding of the business concepts.

  Oracle Silver Bullets
Real-world Oracle performance Secrets

Donald K. Burleson

ISBN: 0-9759135-2-2

     
  Oracle SQL Tuning & CBO Internals

Kimberly Floss

ISBN: 0-9745993-3-6

Oracle Siebel Tuning Course
Course Syllabus topics
Copyright © 2007-2016  by Burleson Corporation

 

Day 1 morning: Tuning the Oracle Data access layer

This section takes an in-depth look into the concepts and skills needed to tune Oracle with SQL Students will learn about the broader issues of tuning with SQL, and then delve into tuning with the Optimizers, the Explain Plan Utility, Hints, and other tuning techniques. After completing this course, students will be able to tune all Oracle SQL and take the actions required to ensure optimal SQL performance.

In conjunction with the other four courses in the Oracle Performance Tuning Certification Series, this course helps students prepare for Oracle Certified Professional Exam number 1Z0-014, Oracle: Performance Tuning, part of the Oracle DBA certification track.

Learning Objectives - After completing this course, students will be able to:

  • Define the major features of Oracle SQL
  • Tune using ANSI extensions
  • Tune using the Explain Plan Utility
  • Understand SQL Optimizer modes
  • Tune using SQL hints

This area will examine all of the SQL within the Oracle library cache and determine the best opportunities for tuning of SQL.  The focus on the SQL tuning will be to tune the most frequently executed statements first, and move down the list as time permits.  The areas for SQL tuning will be to optimize execution plans by removing unnecessary full-table scans, fully utilizing indexes, and implementing advanced hints to derive the optimal execution plan for each query.  This may also involve replacing native SQL with PL/SQL procedures.  Tuning SQL statements using Oracle “hints” will be explained and techniques for testing SQL in a separate instance to determine the optimal execution plan and minimal run time will be explored. 
 

  • Cost-based vs. Rule-based optimization – See the relative benefits of cost-based vs rule based optimization and see when to use rule and cost hints.
     

  • Tuning with indexes – Learn a technique for identifying unnecessary full-table scans.
     

  • Tuning sub-queries –See the optimal execution plans for subqueries with the IN clause.
     

  • Tuning with hints – See the techniques for using the use_hash, use_aj and other important Oracle tuning hints.
     

  • Tuning with Parallel query - This section describes how parallel query works, and how to best provide parallelism for Oracle.

 

Day 1 afternoon: Tuning the external environment

This section will teach students about external influences on Oracle performance, how to measure them, and how to ensure that the external environment is properly configured for Oracle. Students will learn how to take advantage of all the UNIX-based tools for monitoring, configuring, and tuning the external environment. The course also covers tuning issues related to the operating system, tuning for effective CPU usage, reorganizing tables to improve I/O performance and more.

The next section will focus on identifying and correcting external influences including RAM memory shortages, disk I/O bottlenecks, and CPU shortages.  The external environment will be monitored using the vmstat and iostat utilities.  This section will discuss the iterations of recommendations relating to environmental tuning such as OS dispatching priorities, swap space allocation, and other OS-specific issues. 

  • vmstat utility – Interpreting the runque, page-in and the CPU metrics.

  • iostat utility – Using the iostat utility to determine disk I/O access patterns and I/O bottlenecks.

This section will also includes an easy method for capturing environmental statistics inside Oracle tables for long-term analysis.

In conjunction with the other four courses in the Oracle Performance Tuning Certification Series, this course helps students prepare for Oracle Certified Professional Exam number 1Z0-014, Oracle Performance Tuning, part of the Oracle DBA certification track.

Learning Objectives - After completing this course, students will be able to:

  • Define the major features of Oracle SQL
  • Tune using ANSI extensions
  • Tune using the Explain Plan Utility
  • Understand SQL Optimizer modes
  • Tune using the rule-based optimizer
  • Tune using the cost-based optimizer
  • Tune using SQL hints

Day 2:  Tuning the Oracle instance

This section takes an in-depth look into the memory internals of the Oracle SGA. Students will learn how to identify and diagnose Oracle memory performance problems, including problems in the library cache, data buffer cache and data dictionary cache. Once students complete the second course, they’ll be able to handle any SGA-related slowdown, and will have a set of pre-written SQL scripts that they can use to quickly identify performance problems.

Learning Objectives - After completing this course, students will be able to:

  • Tune the Oracle shared pool
  • Tune the data buffer cache
  • Tune Oracle rollback segments
  • Tune Oracle redo mechanisms Monitor and detect lock contention
  • Identify Oracle sort operations

This area will examine the behavior of the Oracle instance and look into the behavior of the Oracle background process and the SGA memory.  The topics for this section will include iterations of recommendations for changes to init.ora parameters, changes to redo log and rollback segments, and changes of physical file locations on disk. 

Oracle Instance Tuning

This section will also explore how to measures changes to the SGA performance and how to document the performance improvements.

  • Redo Logs – This section to introduce the performance characteristics of the Oracle online and archived redo logs.
     

  • UNDO (Rollback) segments – This section explores the interaction between the Oracle rollback segments and the performance of the Oracle database.
     

  • Data Buffer management - Buffer management issues will include the performance of Oracle with various values of db_block_size and the proper settings for db_block_buffers.  This section will also look at allocation of the DEFAULT, KEEP and RECYCLE pools.
     

  • Shared pool issues – This section will discuss the management of the components of the shared pool with a focus on the management of activity in the library cache.
     

  • Sorting issues – This section will describe the management of the sort_area_size parameter and how to determine the optimal setting for disk versus memory sorting.
     

  • Default SQL optimizer mode – This section will explore the optimizer_mode initialization parameter and how the default parameter can effect the database performance.
     

  • Miscellaneous Initialization parameters – Other init.ora parameters such as cursor_space_for_time will be discussed.
     

  • Concurrency Management - This will describe Oracle's serialization and locking schemes and show how to manage locks.
      

Day 3: Oracle SQL tuning for ERP systems

(references are to pages in Floss textbook)

Internal processing of SQL statements

Parsing SQL Syntax - (page 53-57)
SQL Semantic Analysis - (page 56)
Generating the execution plan - (page 181-185)
Using optimizer plan stability - (page 309-332)
Using the v$sql view  - (page 57)
Using the v$sql_plan view
Exercise – Query the library cache
 

Viewing SQL execution Internals

Using and interpreting explain plan -  (page 310)
Using TKPROF -  (page 205)
Using SQL*Trace -  (page 200)
Using set autotrace in SQL*Plus
Exercise – Create a plan table and view SQL plans
 

Altering SQL execution plans

Using hints - (page 87, 277)
Changing the system-wide optimizer mode - (page 92)
Changing optimizer mode for specific statements - (page 10)
Re-writing SQL queries - (page 54, 314)
Table join order evaluation - (page 88)
Using the ordered hint - (page 249)


DBA tuning for SQL Optimization

Inside the library cache – (page 222-229)
Avoiding SQL re-parsing - (page 223)
Cursor Sharing - (page 54-55)
Optimizer Plan Stability (stored outlines) - (page 310-317)
Parallel DML - (page 441)
Parallel SQL and partitions - (page 517)
SQL and the data warehouse


SQL Aggregate Functions

Grouping in several levels
Grouping and NULLS
CUBE and ROLLUP
Performance and grouping
Exercise – Using rollup and cube
Tuning SQL sub-queries

Correlated subqueries - (page 342)
Non-correlated subqueries - (page 342)
Replacing subqueries with joins - (page 385)
Exercise – Tune a complex query
Expert SQL Tuning Secrets

Materialized Views to pre-aggregate and pre-join - (page 54)
Adding indexes - (page 62)

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational