This course is aimed at those already experienced with SQL for data retrieval. It covers a range of facilities to help understand how Oracle implements queries and a range of techniques to make them run faster and more efficiently.
This course is suitable for all recent versions of Oracle including 12c, 18c, and 19c.
General familiarity with Oracle as an SQL end user.
1 day. Hands on.
NOTE: This course may be combined with Oracle SQL and run over a total of four days, please contact us for more information.
This course is highly interactive and consists of theory coupled with plenty of practical sessions.
End users and developers can do much to get the best out of their system without necessarily involving the DBA.
After completion of the course each delegate should know the following:
- How to monitor how Oracle runs an SQL script and identify problem areas
- How the Oracle query optimiser uses the data dictionary
- How to explore and implement better querying access paths
Common causes of performance problems
Strategies & methodologies
Interpretation of EXPLAIN PLAN
Interpretation of AUTOTRACE
Reading and configuring SQL TRACE output
Use of column datatypes
Use of constraints
Use of different storage techniques
Different table access types in EXPLAIN PLAN & AUTOTRACE
Use of different index types
Different index access types in EXPLAIN PLAN & AUTOTRACE
Effects of different SQL expressions on index usage
SQL Construction & Design
Different ways of defining joins
Different ways of defining WHERE clauses
Different ways of minimizing overheads or parsing and executing SQL
Effects of non-summary functions
Effects of Wildcards
Implicit Data Type Conversion
Variations in the ORDER BY clause
Variations in aggregation functions
Variations in subqueries
Interpretation of table statistics
Interpretation of index statistics
Use of histograms