Course Outline

Application Tuning Methodology

Architecture database and instance

  • Server processes
  • Memory structure (SGA, PGA)
  • Parsing and share cursors
  • The data files, log files, parameter files

Analysis of the command execution plan

  • Hypothetical plan (EXPLAIN PLAN, SQLPlus AutoTrac XPlane)
  • The actual execution plan (V $ SQL_PLAN, XPlane, AWR)

Monitoring the performance and find bottlenecks in the process

  • Monitoring the current status of the instance by system dictionary views
  • The monitoring of historical dictionaries
  • Tracking application (SQLTrace, TkProf, TreSess

The optimization process

  • Properties cost optimization and regulated
  • Determination to optimize

Control work cost-based optimizer by:

  • Session parameters and instance
  • Tips (hints)
  • Patterns of query plans

Statistics and Histograms

  • Impact statistics and histograms for performance
  • The methods of collecting statistics and histograms
  • Strategy of counting and estimating statistics
  • Management statistics: blocking, copying, editing, automation of collection, monitoring changes
  • Dynamic data sampling (temporary plates, complex predicates)
  • Multi-column statistics, based on expressions
  • Statistics System

The logical and physical structure of the database

  • Spaces tables.
  • segments
  • Extensions (EXTENTS)
  • Blocks

Data storage methods

  • The physical aspects of the table
  • temporary Tables
  • Tables index
  • external Tables
  • Partition Table (span, letter, hash, mixed)
  • Physical reorganization of tables

Materialized views and mechanism QUERY REWRITE

Methods of data indexing

  • Building B-TREE indexes
  • Properties index
  • Indexes: a unique, multi-column, function, inverse
  • Compression indices
  • Reconstruction and merging indexes
  • Virtual indexes
  • Indexes private and public
  • Bitmap Indexes and junction

Case study - full-scan data

  • The impact of a place at the table level and block performance readings
  • Loading Data conventional and direct path
  • The order of predicates

Case Study - access to data via the index

  • Methods of reading index (UNIQUE SCAN RANGE SCAN FULL SCAN FAST FULL SCAN MIN / MAX SCAN)
  • Using functional indices
  • The selectivity index (Clustering Factor)
  • Multi-column indexes and SKIP SCAN
  • NULL and indexes
  • Index tables (IOT)
  • Impact indices DML operations

Case Study - sorting

  • Sorting memory
  • Sort index
  • Sort linguistic
  • The effect of entropy to sort (Clustering Factor)

Case Study - joins and subqueries

  • The merger: MERGE, HASH, NESTED LOOP
  • Joins in OLTP and OLAP systems
  • The order of switching
  • Outer Joins
  • AntI-join
  • Joins incomplete (SEMI)
  • Subqueries simple
  • Correlated subqueries
  • The views, the WITH clause

Other operations cost-based optimizer

  • Buffer Sort
  • INLIST
  • VIEW
  • FILTER
  • Count Stop Key
  • Result Cache

Inquiries dispersed

  • Read query plans for use dblinks
  • Choosing the leading mark

Parallel processing

Requirements

  • The free use of the basics of SQL and knowledge of Oracle database environment (preferably Oracle 11g completion of training - Native SQL for Programmers - Workshops)
  • Practical experience in working with Oracle
  28 Hours
 

Testimonials (1)

Related Courses

Related Categories