Course Objective
Understanding how a SQl query is executed .
Writing advanced queries.
Index Overview and Optimization
Index organized table
Table partitions and operations on partitions
Inside the Oracle Optimizers
Identify problem SQL statements
Modify a SQL statement to perform at its best
Trace an application
Understand how the Query Optimizer makes decisions about how to access
data
Interpret execution plans
Use optimizer hints effectively
Generate a load test
Contents
1 .Understanding how a SQl query is executed.
Oracle Database internal data structures. For example (Library Cache and sql query
performance )
Sql statement processing overview. (How exactly the query execution works).
SQL statement execution fundamentals: PARSE, EXECUTE, BIND, FETCH
arraysize issues, etc.
2. Writing advanced queries
Analytical queries
Cube and Rollup
Complex Joins
Materialized Views
General tips for writing efficient queries.
(For example Avoiding SQL re-parsing , Replacing subqueries with joins etc)
3. Locks and blocks.
Concept
Identifying locking problems
Tips on how to avoid them.
4. Oracle index internals
When an index should be used
When to use B-Tree indexes
When to use Bitmap Indexes
When to use reverse-key indexes
When to use function-based indexes
Local and global partitioned indexes
Creating and checking an index
What if you create a bad index?
Dropping an index and caution to be exercised
Increasing performance by indexing the SELECT and WHERE columns
The Oracle ROWID
Use the Fast Full Scan feature to guarantee success
Caching a table into memory
Using the INDEX_STATS view
The binary height of an index
Choosing between multiple indexes on a table (use the most selective)
How to avoid comparing unmatched data types, causing index suppression
Forcing index usage
5. Index organized table
6. Tuning with Table partitions
Table partition concept.
Operations on partitions.
How table partitions improves performance.
7. Tuning with Parallel query
Basic Concepts of Parallel Operations
Parallel DML and DDL Statements and Operations
Parallelism and Partitions
Inter- and Intraoperation Parallelization
Creating Table and Index Examples Using Parallel Operations
Parallel DML Statements and Examples
Monitoring Parallel Operations via the V$ Views
Using EXPLAIN PLAN and AUTOTRACE on Parallel Operations
Tuning Parallel Execution and the Oracle Initialization Parameters
Parallel Loading
Performance Comparisons
8. Inside the Oracle Optimizers
Rule-based optimization
Cost-based optimization
Tunning with Optimizer hints .
The top hints used, the available hints and groupings, and specifying
multiple hints
Building and using STORED OUTLINES
Statistics: Collecting, managing and understanding statistics
Using histograms .Managing low-selectivity columns with histograms.
When the optimizers fail
9. Identify problem SQL statements
Analyzing of SQL statement
What queries do I tune? Querying the V$SQLAREA and V$SQL
views
Some useful new 10g views for locating resource-intensive
sessions and queries
Interpreting Execution Plans
Using tkprof Utility
How to read trace file
Tracing using Set Auto trace in SQL Plus .
10. Query Optimization
Optimizing SQL statement .
Basic Optimization concept
Choosing Acess Path
Merging of two indexes
Index fast full scan .
Execution of joins
Nested loops and features
Merge Sort
Hash Join
Index Join
Cluster Join
Bitmap Join
Methods of optimization
Estimate statistics
Using DBMS_Stat
Finding out plan of execution query.
Generating plan table
Generation of plan for the statement
How oracle executes statements
Queries involving sorting .
Compare and contrast access methods: table access full, access
by rowed . Index scan type overview: index unique scan, index range
scan, index skip scan, index fast full scan, index full scan, index joins
11. MISC
Viewing file and tablespace information to determine problem areas
Finding out what users are doing and which resources they are using etc .
12. Review dynamic views associated with execution plans.
13. Using STATSPACK and the AWR Report
14. Any tools or utitilies with oracle or open source that can be used.
15 . Case Study of modifying a SQL statement to perform at its best and
retesting it .
