Optimizer - Intelligent Data Systems Laboratory
Download
Report
Transcript Optimizer - Intelligent Data Systems Laboratory
DB Tuning : Chapter 10. Optimizer
이상근
Intelligent Database Systems Lab
School of Computer Science & Engineering
Seoul National University, Seoul, Korea
Center for E-Business Technology
Seoul National University
Seoul, Korea
Optimizer ?
Optimizer is like brain of database system
Optimizer is one of Oracle DBMS processes. It finds the best(?)
execution plan of input SQL query
Optimizer
Query Rewrite
Query Optimizing
Optimizer Select
Rule based Optimizer
SQL Parsing
SELECT * FROM EMP
What we do when we tune :
Helping optimizer to make
better decision
Important factors:
Index, Partition, Parallel
Processing
CLIENT
Cost based Optimizer
Query Execution plan
Copyright 2008 by CEBT
Semantic Tech & Context - 2
Important Tuning Points: SQL Parsing
Cursor Shared
In large data warehouse system, it may not be important.
However, OLTP system like web system. 1 second different can
be very significant. We’d better bind variable to share cursor for
the same SQL queries that have different variables
Shared Pool Size
Should be enough
Eliminating useless columns and tables
Trivial
Copyright 2008 by CEBT
Semantic Tech & Context - 3
More about optimizer
Rule-based Optimizer
It has several priority rules in it. Following the rules, it finds the
best execution plan
Rank 1 : Table Access using ROWID
Rank 2 : Table Access using Unique Index
Rank 3 : Table Access using Index
Rank 4 : Full Table Scan
Pros
–
Developers can choose the execution plan as they want
–
If you know how it works, you can use it as you want (of course not
easy)
–
Latest version of Oracle Rule-based optimizer use statistics too!
Copyright 2008 by CEBT
Semantic Tech & Context - 4
More about optimizer
Cost-based Optimizer
It uses statistics to find the best execution plan
Analyzing needs to lock tables -> Overhead
Not as good as we hope
–
Can’t be better than the execution plan that people made
Type:
–
First Row
–
All Rows
Select * from EMP;
Select count(*) from EMP;
The author doesn’t recommend cost-based optimizer
We can’t easily guess what the execution plan will be
Not easy to modify execution plan
Copyright 2008 by CEBT
Semantic Tech & Context - 5
Execution Plan
The results made by optimizer
It’s a plan how a SQL will be executed
How to check out execution plan
Many ways to do this
–
Explain
–
Autotrace
–
TKPROF Trace
–
10046 Trace
Read from page.233~
Copyright 2008 by CEBT
Semantic Tech & Context - 6