Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
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)
Trainer expertise on SQL tuning
