Oracle Performance Tuning
Table of Contents:
1. Basics of Oracle Internals
a. Oracle Architecture (Most Important One in tuning Queries).
b. Data files, control files, redo log files, init.ora, sp file, pwd file. Etc…
c. DB blocks, extents, segments, tablespaces…
2. Introduction to query Tuning.
a. Basic Steps to be followed before tuning.
b. Identifying the bottlenecks
c. Short term Fix and Long Term Fixes
d. Best practices to be followed while creating objects and writing queries.
e. Do I need to use SQL or PLSQL for specific requirement?
3. Internal processing of SQL statements
a. Statement Processing
b. Soft Parsing
c. Hard Parsing
d. SQL Semantic Analysis
e. Generating the execution plan
f. Using optimizer plan stability
g. Using the v$sql view
h. Using the v$sql_plan view
i. Exercise – Query the library cache
4. Gathering Statistics.
a. Purpose of statistics
b. When to gather and its impact
c. Types of statistics (table, column, system)
d. Histogram statistics
e. Dynamic sampling
f. using dbms_stats
g. using analyze statements
h. Exporting/importing statistics
i. Statistics management
5. Internals of Query Optimizer
a. Different modes of SQL optimization
b. Rule-based optimization
c. Cost-based optimization
d. All rows optimization
e. First_rows optimization
f. Components of the Optimizer
g. Bind variables.
6. Optimizer Access Paths and table joins
a. Basics of file I/O
b. Sequential reads vs. scattered reads
c. Table Full Scan, Rowid Scan, Index Scan
d. Nested Loop Joins
e. Sort Merge Joins
f. Outer Joins
g. Hash Joins
h. Cartesian Joins
i. Internal structure of Indexes storage
j. Index Range Scan
k. Index Fast scan /Index Fast Full scan
l. Index (Max/Min) scan
m. Index Skip Scan
o. Drawback of Bitmap Index (In OLTP Applications)
p. Nested Loop /Hash Join/Sort-Merge Join/ Hash Outer Join
q. Sequential Read/ Scattered Read
r. Dead Lock Solution for Wait Event Problems etc
7. Execution Plan
a. What is execution plan?
b. How to generate plan.
c. PLAN_TABLE Table
d. Reading Execution Plan
e. Parallel Execution Plan
f. Modes of Tracing
g. How To Read Smartly TKPROF
h. Raw trace files
i. Autotrace and Explain plan
j. Reading AWR Reports
k. Table join order evaluation
l. Using the ordered hint
m. Re-writing SQL queries
8. Optimizer Hints
a. when to use Hints
b. Impact of Hints on Execution Plan
c. Scope of hints (session-level, statement level)
d. Types of Hints
e. Forcing index usage
f. Using hints in subqueries
9. Index Optimization and Table compression
a. Types of Indexes
b. When to use which type of index
c. B-Tree indexes
d. Bitmap Indexes
e. Function-based Indexes
f. Clustered indexes
g. Index-only tables
h. Table Partitioning methodology for performance
10. DML Tuning
a. Optimizing Oracle SQL insert performance
b. High Impact insert Tuning Techniques
c. Block size and insert performance
d. Oracle Delete & Update Tuning
e. With clause
11. Tuning Subqueries
a. Tuning Subqueries
b. Types of SQL Subqueries
c. Subqueries in the where Clause
d. In vs. exists Subqueries
f. Correlated Subquery
g. Tuning Scalar Subqueries
h. Scalar Subquery Performance
i. Internals of Temporary Tables
j. Subquery Hint Tuning
k. Subquery Tuning with Index Hints
l. Materialized Views and its impact in performance
m. Query rewriting using MViews
n. With Clause
12. PLSQL Tuning
a. Do’s and Don’ts while writing PLSQL code.
b. Using Bind variables.
c. Improving Performance using Pipelined Functions
d. Bulk collect
f. Creating Packages/ Procedures/ Functions/ Triggers
g. Exception Handling
h. Drawback of Triggers
i. Autonomous Transaction etc
j. Optimizer Compiler
k. Data Caching Techniques (Package , Deterministic Function, Result Cache…etc)
l. Hierarchical Profile
m. NOCOPY Hint
1. Course will be covered in real time point of view.
2. Day wise notes.
3. Day wise workouts and interview questions on the topics covered.
This course is specifically designed to help you clear the certification exam successfully. The comprehensive content of the course along with demonstration of practical scenarios & examples will make you understand each and every topic in great depth. Since the course structure has a special focus on certification, hence you will go through a lot of real-time case studies and study material during the training that will help you crack the certification exam.
Both self-paced training and online instructor-led training have their own advantages and disadvantages. 1) Suitability - If you have no idea about the course content and have no experience on it, then online instructor-led training will help you understand the course content better and deeply. 2) Flexibility - Self-paced training is generally more flexible than the tutor-led training since you can learn at your own pace through the videos as and when you have time. 3) Doubt-clearing, assignments etc. - You can attempt assignments and get feedback from the tutor in instructor-led training and also can get your doubts cleared during the class. Cost (more for instructor-led training) and other factors are also important.
We have a highly qualified and experienced team of professionals who are experts in their fields. Our trainers are highly supportive and render a friendly learning environment to the students focusing on their career growth.
In normal circumstances the tutor should be able to reschedule the class as per your convenient time but if you accidently miss any particular class in a multi-student batch then you can catch-up from the corresponding session recording that will be shared with all students or you can request the tutor to hold a separate class for you later on the topics covered in that class.
We provide server access for you to practice on and our trainers will ensure that you get practical real-time experience and training with all the utilities required for in-depth understanding of the course.
Yes. We provide lifetime access to Uplatz Learning Hub where you can view or download the course material anytime.
Yes. We have special offer/discount on this course. Please send email to email@example.com asking for the discount and our team will be glad to help you.
Yes. Please check our refunds and cancellation policy (link given in the footer of our website) for more details.
You can send an email to firstname.lastname@example.org and Uplatz course team will respond back on your queries.