Transcript db file sequential read

EVOLUTION OF THE
ORACLE OPTIMIZER
THINKING
Jurijs Velikanovs
7+ years Oracle DBA, OCP 7/8/8i/9i, OCA 9iAS R2
Topics:





Definition of database optimizer
Death of RBO or Why CBO is better
Limitations of earlier CBO versions
Some ways to workaround 8i +
9i Revolution
DEMO!
Introduction

About myself

Master degree in computer since
8
years as Oracle DBA
 OCP 7/8/8i/9i, OCA 9iAS R2
 Oracle9i Database Administrator Certified Master

 7+



http://otn.oracle.com/ocm/jvelikanovs.html
in IT Alise
First line Oracle support analyst
Development DBA, Consultant
DBA Teamleader
Introduction

About IT Alise

10+ years experience in Oracle development
 ~200
employees
 Main working directions



Oracle Custom Development
Oracle eBusiness Suite implementation
Oracle Expert services
Definition of database optimizer
Definition of optimizer

Oracle9i Database Concepts
“The optimizer determines the most efficient way
to run a SQL statement.”
Definition of optimizer

Nowadays it means my opinion
”If application design reflects business needs you
don’t have to think about particular SQL
performance”
I. Optimizer will find optimal path himself
II. You still have to think about application design
Death of RBO !
or
Why CBO is Better ?
DEMO!
INDEX & RBO 
tfc1/2/3.sql
RBO

Technically can’t choose optimal way
 Don’t

know anything about data distribution
There is set of rules
 You
have to think about execution plan writing SQL
 Stabile life. SQL executes equally:
yesterday/today/tomorrow


RBO is dead 
But what would say CBO? DEMO
tfc2.sql
CBO

How CBO found the way?
 DEMO event 10053 tfc4.sql
 PARAMETERS USED BY THE OPTIMIZER
 BASE STATISTICAL INFORMATION
 SINGLE TABLE ACCESS PATH
 OPTIMIZER STATISTICS AND COMPUTATIONS
 GENERAL PLANS

Main Characteristics
 Works
using statistics
 Looks through “all” possible paths
 Calculates COST and choose path with lowest cost
 There a lot of assumption
Limitations of earlier CBO
versions
DB_FILE_MULTIBLOCK_READ_COUNT

FULL SCAN cost
 DEMO

tfc4sql
‘adjusted’ multi-block read value for COST
calculation
 number
of blocks / aMBRC
 Some blocks can be in memory
 Don’t do MBR across extents
 Oracle don’t believe you (MBRC)

Actual Adjusted
4
4.175
8
6.589
16
10.398
32
16.409
64
25.895
128
40.865
© Jonathan Lewis
All IO are equal
 Single
block read = Multiblock read time
Limitations of earlier CBO (COST)

All IO are equal
 Single

Think in PIO units
 LIO

= PIO (buffer pool?)
All Join operations speed are equal
 Full

block read = Multiblock read time
Scan = Nested Loop = Hash Join = Merge Join …
All other operation ignored
 Comparization,
Functions, …
Some ways to workaround 8i +
Some ways to workaround 8i +

Think in PIO units
 LIO

= PIO (buffer pool?)
OPTIMIZER_INDEX_CACHING (o_i_c)
Measurement 0-100%
 Default value are far from real world = 0%
 How many blocks accessible via
“db file sequential read” are in the cache

 'buffer
cache hit ratio'
Some ways to workaround 8i +

All IO are equal
 Single

block read = Multiblock read time
OPTIMIZER_INDEX_COST_ADJ (o_i_c_a)
Measurement 0-10000%
 Default value are far from real world = 100%


“db file sequential read” cost * o_i_c_a = REAL COST
 “db
file sequential reads” / ”db file scattered read”
 DEMO tfc5sql
Conclusion 8i

Do not underestimate parameters impact
 DB_FILE_MULTIBLOCK_READ_COUNT
 OPTIMIZER_INDEX_CACHING
 OPTIMIZER_INDEX_COST_ADJ

Default values far from real world needs
 OPTIMIZER_INDEX_CACHING=0%
 OPTIMIZER_INDEX_COST_ADJ=100%
9i Revolution
9i Revolution = SYSTEM_STATS

Compute




Retrieve


DBMS_STATS.
GATHER_SYSTEM_STATS(gathering_mode => 'START');
DO THE LOAD …
DBMS_STATS.
GATHER_SYSTEM_STATS(gathering_mode => 'STOP');
select * from sys.aux_stats$;
DEMO ss.sql
Part 1: Multi block reads

Statistics computed







SREADTIM:
Average time for a single-block read request in milliseconds
MREADTIM:
Average time for a multi-block read request in milliseconds
MBRC:
Average number of blocks in a multi-block read.
FullScan time = MREADTIM * BLOKS / MBRC
Cost = FullScan time /SREADTIM
Cost = Response time measured in single block reads
DEMO tfc6.sql
Part 2: CPU Costing Model

Statistics computed
 CPUSPEED:
Oracle calibration of system CPU speed

Costing formula (Oracle9i Database Performance Tuning Guide and Reference) :
COST = (
#SRds * SREADTIM +
#MRds * MREADTIM +
#CPUCycles / CPUSPEED
) / SREADTIM


CPUCycles
 Virtually every operation have cost in terms of CPU usage
 HASH/SORT/PREDICATES SEARCH/IRS/IUS/TFS...
 There is internal Oracle table for operation calibration
 Model allows measure CPU usage
DEMO ss1.sql
Conclusion

It is really REVOLUTION!

RBO is dead !!!
The CBO keeps evolving
Statistics & Parameters are essential
Lot of wrong assumptions in earlier versions
9i+ revolutionary switch SYS_STATs
There are still assumptions







Design is most important!
And now you can have time for design!
Q&A
Jurijs Velikanovs
+371-9268222
[email protected]
http://otn.oracle.com/ocm
Show Time





@testsort
@test_b1
@test_b2
@test_b3
@test_b4