Home    |    Instructor-led Training    |    Online Training     
         
 
Courses
ADA
Adobe
Agile
AJAX
Android
Apache
AutoCAD
Big Data
BlockChain
Business Analysis
Business Intelligence
Business Objects
Business Skills
C/C++/Go programming
Cisco
Citrix
Cloud Computing
COBOL
Cognos
ColdFusion
COM/COM+
CompTIA
CORBA
CRM
Crystal Reports
Data Science
Datawarehousing
DB2
Desktop Application Software
DevOps
DNS
Embedded Systems
Google Web Toolkit (GWT)
IPhone
ITIL
Java
JBoss
LDAP
Leadership Development
Lotus
Machine learning/AI
Macintosh
Mainframe programming
Mobile
MultiMedia and design
.NET
NetApp
Networking
New Manager Development
Object oriented analysis and design
OpenVMS
Oracle
Oracle VM
Perl
PHP
PostgreSQL
PowerBuilder
Professional Soft Skills Workshops
Project Management
Rational
Ruby
Sales Performance
SAP
SAS
Security
SharePoint
SOA
Software quality and tools
SQL Server
Sybase
Symantec
Telecommunications
Teradata
Tivoli
Tomcat
Unix/Linux/Solaris/AIX/
HP-UX
Unisys Mainframe
Visual Basic
Visual Foxpro
VMware
Web Development
WebLogic
WebSphere
Websphere MQ (MQSeries)
Windows programming
XML
XML Web Services
Other
Oracle Database 10g: SQL Tuning
Oracle Training Overview

This Oracle 10g courseware training guide book will equip developers to build efficient SQL statements and to tune applications from the standpoint of an application developer. When this effort is complemented by database server and possibly PL/SQL application tuning, then a highly efficient environment has been built.

Oracle Training Prerequisites

The following course is a mandatory prerequisite: ORACLE DATABASE 10G: INTRODUCTION TO SQL – COMPLETE LIBRARY.
  • While not mandatory, the following course is a suggested prerequisite which is helpful prior to completing this course: ORACLE DATABASE 10G: ARCHITECTURE FOR DEVELOPERS.
Oracle Training Audience

  • Target audience for this course is both senior application developers and database administrators.
  • Developers who will be building, debugging and tuning complex SQL statements will benefit from this course. Database administrators responsible for tuning the database will need to collaborate with application developers in order to implement the techniques discussed herein.
Oracle Training Certification Examination

This course considers subjects which pertain to the certification for an Oracle Certified Developer. Following the training sessions Oracle Corporation recommends six-months production experience with the technology prior to taking the certification examinations. While this is good practice it may not be necessary for all individuals.

Oracle Training Objectives

Major subject areas to be explored are:
  • Consider the unique and differing tuning issues in OLTP database and data warehouse environments.
  • Learn the phases of SQL statement execution within the database instance.
  • Use various techniques to examine SQL execution plans which are produced.
  • Learn about the operation of the Oracle Cost-Based Optimizer, including optimizer statistics and histograms.
  • Influence the behavior of the Optimizer and tune SQL statement execution.
  • Utilize the database advisory framework and those Advisors specifically intended for SQL tuning.
  • Use private and public stored outlines to achieve absolute execution plan stability.
  • Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.
Oracle Training About Tuning Curriculum

The context in which the subjects considered within this course is important to understand to see the specific areas of focus in this course and the tuning and problem areas addressed by the other courses within the tuning curriculum:
  • ORACLE DATABASE 10G: SQL TUNING. This course considers tuning and monitoring techniques helpful in writing efficient SQL code and troubleshooting problem areas. These techniques are helpful whether the SQL statements are executed in isolation or as part of PL/SQL program units, Java classes, other 3GL programs, and so on. This is the course you are now taking.
  • ORACLE DATABASE 10G: ADVANCED PL/SQL PROGRAMMING & TUNING. This course considers similar topics pertaining to PL/SQL program units. Obviously, any enclosed SQL statements which are also well tuned will contribute towards the efficiency of the application as a whole.
  • ORACLE DATABASE 10G: ADMINISTRATION III – PERFORMANCE TUNING. The environment within which both SQL statements and PL/SQL program units execute is obviously the Oracle database. Therefore, if the efficiency of the database as a whole is compromised then the efficiency of any objects which execute within the database will similarly suffer. This course allows one to tune and monitor the performance of the overall database. A well-tuned and properly configured database environment offers the circumstances in which database applications, and their objects such as SQL statements and PL/SQL program units, will operate efficiently.
Oracle Training Course duration

4 Days

Oracle Training Course outline

CONTEMPLATING THE TUNING ISSUES
  • DIFFERENCES BETWEEN APPLICATIONS
  • THE CHALLENGES OF TUNING
  • THE IMPACT OF THE SYSTEMS INFRASTRUCTURE
  • USING THE MANAGEMENT & ADVISORY FRAMEWORK
SQL STATEMENT EXECUTION
  • THE EXECUTION PHASES
  • SQL EXECUTION MODES
VIEWING THE EXECUTION PLAN
  • MORE ABOUT THE EXECUTION PLAN
  • VIEWING THE EXECUTION PLAN
  • MORE INTERPRETATION OF THE PLAN
UNDERSTANDING THE COST BASED OPTIMIZER (CBO)
  • OPTIMIZATION GOALS
  • OPTIMIZER_FEATURES_ENABLE Parameter
  • CBO COMPONENTS
OPTIMIZER OPERATIONS
  • TABLE ACCESS OPERATIONS
  • JOIN OPERATIONS
  • INDEX OPERATIONS
MANAGING OPTIMIZER STATISTICS
  • MORE ABOUT OPTIMIZER STATISTICS
  • AUTOMATIC DATABASE STATISTICS COLLECTION
  • STATISTICS_LEVEL Parameter
  • GATHER_STATS_JOB
  • MANUALLY GATHERING STATISTICS
  • USING THE DBMS_STATS() PACKAGE
  • GATHER_TABLE_STATS()
  • GATHER_INDEX_STATS()
  • GATHER_SCHEMA_STATS()
  • GATHER_DATABASE_STATS()
  • USING THE EM INTERFACE
  • COLLECTING & MANAGING SYSTEM STATISTICS
  • MANAGING SYSTEM STATISTICS
  • CREATE_STAT_TABLE()
  • GATHER_SYSTEM_STATS()
  • GET_SYSTEM_STATS()
  • SET_SYSTEM_STATS()
  • IMPORT_SYSTEM_STATS()
  • EXPORT_SYSTEM_STATS()
  • DELETE_SYSTEM_STATS()
  • DROP_STAT_TABLE()
PERFORMANCE MONITORING & PROACTIVE MAINTENANCE
  • THE MANAGEMENT & ADVISORY FRAMEWORK
  • REAL-TIME MONITORS
  • MANAGING THE AUTOMATIC WORKLOAD REPOSITORY
  • AUTOMATIC DATABASE DIAGNOSTIC MONITOR
  • METRICS & ALERT THRESHOLDS
USING ADDM & THE SQL TUNING ADVISOR
  • APPLICATION TUNING SETUP
  • APPLICATION MONITORING & TUNING WITH EM
  • SQL TUNING ADVISOR
PROGRAMMATIC ACCESS TO THE SQL TUNING ADVISOR
  • ABOUT SQL TUNING ADVISOR TASKS
  • SQL PROFILE MANAGEMENT
  • ACCEPT_SQL_PROFILE()
  • ALTER_SQL_PROFILE()
  • DROP_SQL_PROFILE()
  • SQL TUNING SET MANAGEMENT
USING THE SQL ACCESS ADVISOR
  • USING THE SQL ACCESS ADVISOR
  • PERFORMING A QUICK TUNE TASK
  • INDEX OPERATION DATABASE PARAMETERS
  • OPTIMIZER_INDEX_COST_ADJ
  • OPTIMIZER_INDEX_CACHING
  • SKIP_UNUSABLE_INDEXES
OPTIMIZER HINTS
  • ABOUT HINTS
  • HINT EXPLANATIONS
HISTOGRAMS
  • WHY ARE HISTOGRAMS NEEDED?
  • HISTOGRAMS INTERNAL STRUCTURE
  • MANUALLY MANAGING HISTOGRAMS
USING PLAN STABILITY
  • ABOUT PLAN STABILITY
  • PREPARING FOR STORED OUTLINES
  • CREATING & USING STORED OUTLINES
  • DATA DICTIONARY STORAGE
  • MANAGING STORED OUTLINES
  • DROP OUTLINE
  • ALTER OUTLINE
  • DBMS_OUTLN()
EDITING PRIVATE OUTLINES
  • ABOUT PRIVATE OUTLINES
  • EDITING PRIVATE OUTLINES
  • UTILIZING PRIVATE OUTLINES
EXPLOITING & MANAGING CURSOR SHARING
  • ABOUT CURSOR SHARING
  • BIND VARIABLES & CURSOR PEEKING
  • USING THE CURSOR_SHARING PARAMETER
USING TKPROF & AUTOTRACE
  • USING TKPROF
  • USING AUTOTRACE
MANAGING SCHEMA OBJECTS
  • ABOUT CHAINED & MIGRATED ROWS
  • VALIDATING DATABASE OBJECTS
      Please contact your training representative for more details on having this course delivered onsite or online

      Training Outlines - the one stop shopping center for IT training.
      © Training Outlines All rights reserved