Oracle Performance Optimization Using the Wait Interface

1
Oracle Performance Optimization Using the Wait
Interface 7, 8i, 9i and Beyond

• Thursday, April 8th 2004

Gaja Krishna VaidyanathaIndependent
Consultantgajav_at_yahoo.com
2
Upcoming Book Unprecedented Stuff

• Oracle Insights Tales of the Oaktable
• A 12-author Book Project
• Dave Ensor
• Tim Gorman
• Kyle Hailey
• Anjo Kolk
• Tom Kyte
• Jonathan Lewis
• Connor McDonald
• Cary Millsap
• James Morle
• Mogens Norgaard
• David Ruthven
• Gaja Krishna Vaidyanatha
• Published by Apress (http//www.apress.com)

3
I am not an expertnot by any stretch of the
imagination.
Confession1
4
I am an engineer not a scientist.
Confession2
5
Advanced Tuning, Turbo-charged Tuning,
Push-Me-For-More-Power TuningLiesJust Plain
LiesOr is it called Marketing these days!!!
Confession3
6
There is only one way to optimize Oracle
performance The Right Way...Using the Wait
Interface
Confession4
7
Its almost duh!what is common sense is not
common practice - Steven Covey
8
Plan of Action

• What is Performance Optimization?
• What is the Oracle Wait Interface (OWI)?
• The Method Behind the Madness
• The Guts of OWI
• Diagnosing an Oracle performance problem A Demo
• Application Performance Optimization A DBAs
  Perspective
• Oracle Instance/Database Configuration
  Optimization
• Conclusion

9
What is Performance Optimization?

• Systematic and deliberate effort to achieve a
  system runtime goal, by eliminating one or more
  bottlenecks
• The goal needs to be clearly defined
• Query X runs in 40 minutes and needs to run in
  less than 4 minutes.
• Requires a simple diagnostic method that is
  repeatable
• No expert methods please, we have enough of those
• Remember Keep one thing in clear focus
• Reducing logical I/O is the ultimate goal in any
  performance tuning engagement
• This has a huge positive impact on response time

10
What is Performance Optimization?

• Requires capability to perform in-depth analysis
  of the diagnostic data
• Facilitate the path to resolving the underlying
  problem
• There is only one way to optimize Oracle
  performance
• The RIGHT Way!!!
• Everything else is just marketing gimmicks
• Advanced Tuning
• Turbo Tuningpush me for more power!!!

11
What is the Oracle Wait Interface (OWI)?

• Instrumentation within the Oracle Kernel that
  provides execution/wait times for various code
  paths
• Timestamp t1
• Code X
• Timestamp t2
• Time spent t2 t1
• Was introduced in 7.0.12
• Helps us find where is the time spent?
• Resource hogs are usually the bottlenecks
• Externalized via a set of V views
• VSystem_Event, VSession_Event, VSession_Wait
• Trace files generated via event 10046
• The only source of real performance diagnostic
  data
• VSysstat, VSessstat and such are supplementary
  data sources

12
The Method Behind the Madness
13
The Method Behind the Madness Allow me to blow
some steam off!!!

• Almost every cache-hit ratio out there is
  unintelligent
• Just flat boring numbers
• The most relevant ratio
• (Executions below response time goal/Executions
  above response time goal) 100
• Check out Jonathan Lewiss Fan Hit Ratio
• Survivor Story

14
The Method Behind the Madness The Mantra

• Response Time Service Time Wait Time
• Throughput Service Time Wait Time
• Service Time fn (Hardware specs, Laws of
  Physics)
• Wait Time fn (Wasteful Processing, Resource
  Contention, Substance Smoked Before Application
  Coding) -- -)
• You can never eliminate waits
• Why would you care for some waits if the
  application is within the response time goal?

15
The Method Behind the Madness

• Set Tuning Goals
• Benchmark current performance (if feasible)
• Identify the performance bottleneck
• Gather Oracle Diagnostic Data using OWI Prong I
• Gather OS Diagnostic Data Prong II
• Correlate both data sources
• Analyze tkprof trace data (9i and up includes
  wait data)
• Make one change at a time
• Rinse and repeat until tuning goal is achieved
• This is version-independent and will work from
  version 7.0.12 and up

16
The Method Behind the Madness Set Tuning Goals

• Why are you tuning?
• How does the business benefit from your effort?
• If SQL X runs in 0.3 seconds instead of 0.5, we
  will close 40 more orders each day
• Can you please define what fast means?
• Example - This SQL statement runs in 45 seconds
  and needs to complete within 7 seconds.
• Do you suffer from CTD?

17
The Method Behind the Madness

• Set Tuning Goals
• Benchmark current performance (if feasible)
• Identify the performance bottleneck
• Gather Oracle Diagnostic Data using OWI Prong I
• Gather OS Diagnostic Data Prong II
• Correlate both data sources
• Analyze tkprof trace data (9i and up includes
  wait data)
• Make one change at a time
• Rinse and repeat until tuning goal is achieved
• This is version-independent and will work from
  version 7.0.12 and up

18
The Method Behind the Madness Benchmark Current
Performance

• Timing of tracing/data collection depends on the
  time durationof the problem
• Record response time before and after a specific
  change
• Check whether TIMED_STATISTICSTRUE
• 15 alleged overhead is a myth
• Does not show up in trace timing data
• Set to FALSE if and only if there is a
  platform-specific bug
• STATISTICS_LEVELTYPICAL
• Introduced in 9iR2
• Valid values are BASIC, TYPICAL, ALL
• Control Switch for Performance Data Collection

19
The Method Behind the Madness Benchmark Current
Performance

• For a system-wide Problem
• Create a snapshot using STATSPACK
• or
• Use top, identify the PID and then proceed
• STATSPACK requires deleting one or more events
  from STATSIDLE_EVENT
• SQLNet message to client
• Analyze reports online on http//www.oraperf.com
• For a localized Problem
• Run the SQL statement
• May not be possible or feasible to always do this
• Effective problem diagnosis after the fact can
  be done only with long-term historical collections

20
The Method Behind the Madness

• Set Tuning Goals
• Benchmark current performance (if feasible)
• Identify the performance bottleneck
• Gather Oracle Diagnostic Data using OWI Prong I
• Gather OS Diagnostic Data Prong II
• Correlate both data sources
• Analyze tkprof trace data (9i and up includes
  wait data)
• Make one change at a time
• Rinse and repeat until tuning goal is achieved
• This is version-independent and will work from
  version 7.0.12 and up

21
The Method Behind the Madness Identify the
performance bottleneck

• Gather required diagnostic data (Prong I)
• VSystem_Event
• Conglomerate of all events since instance startup
• VSession_Event
• Historical event data for all currently connected
  sessions
• VSession_Wait
• Waits of currently active sessions
• 1 row/active session
• Data here is merged into VSession in Oracle 10g

22
The Method Behind the Madness Identify the
performance bottleneck

• Gather required diagnostic data (Prong I)
• Determine the PID from top (Similar effort on
  Windows)
• Helps identify top CPU consumers
• Sample scenarios Determining location of the
  pain
• One process is hogging the CPU
• Pain caused by one PID

23
The Method Behind the Madness Identify the
performance bottleneck

• Gather required diagnostic data (Prong I)
• Sample scenarios Determining location of the
  pain
• Multiple processes are hogging the CPUs
• Pain caused by multiple PIDsPick one PID at a
  time
• Multiple processes are evenly utilizing the CPU,
  but not hogging it
• Pain caused by multiple PIDsPick one PID
• This usually is an system-wide I/O problem

24
The Method Behind the Madness Identify the
performance bottleneck

• Gather required diagnostic data (Prong I) Data
  Correlation
• Determine the PID from top
• Helps identify top CPU consumers
• Determine SPID of the server process to be traced
• Map the PID to the Oracle Server Process (join
  vprocess, vsession)
• select S.Username, P.Spid, S.Sid, S.Serial
• from V_SESSION S, V_PROCESS P
• where S.PADDR P.ADDR
• and S.Username upper('oracle_user_name')
• Trace the session
• alter session set event.. Prior to Oracle8i
• dbms_system.set_ev() Oracle 8i and up
• dbms_monitor. - Oracle 10g and up

25
The Method Behind the Madness Why is the OS
Prong required?

• CPU or memory bottlenecks are not revealed within
  OWI
• Some memory bottlenecks can cause sub-centisecond
  waits within Oracle
• Paging and Latch Free
• System-level I/O Bottlenecks
• Helps identify bottlenecks external to Oracle

26
The Method Behind the Madness Prong II CPU
Bottlenecks

• CPU Utilization
• sar u 5 10000
• usr, sys, wio, idle
• wio and sys typically should be lt 10-15
• 0 idle is OK, so long as wio and sys is
  nothigh
• Comparable commands in Windows
• CPU Run Queues
• Run queue is the only deterministic CPU
  bottleneck metric
• Average run queue lt (2 ( of CPUs)
• vmstat 5 10000 Reveals system run queue
• The column r in the vmstat output is the CPU
  run queue
• top Runnable provides the CPU run queue

27
The Method Behind the Madness Prong II Memory
Bottlenecks

• Memory Utilization
• vmstat S 5 10000
• sr (scanrate) should be in and around 0
• Bug in Solaris 2.6/2.7
• swapins and swapouts should be 0
• Level of paging should also be at a minimum (In
  this day and age, paging should be non-
  existent)
• Oversized SGAs (to attain high cache-hit ratios)
  are potential culprits for paging and swapping
• Check for high I/O activity on swapfile devices

28
The Method Behind the Madness Prong II I/O
Bottlenecks

• I/O Utilization
• sar d 5 10000
• Response times of gt 20ms on devices indicates
  I/O bottlenecks
• Response Time Service Time (AvServ) Wait Time
  (AvWait)
• Queuing is an indication of high response time
  and high wait times
• High disk queue numbers high response times
  ? I/O contention
• I/O requests should be evenly balanced across
  all devices
• Fewer the devices, better the overall utilization

29
The Method Behind the Madness

• Set Tuning Goals
• Benchmark current performance (if feasible)
• Identify the performance bottleneck(s)
• Gather Oracle Diagnostic Data using OWI Prong I
• Gather OS Diagnostic Data Prong II
• Correlate both data sources
• Analyze tkprof trace data (9i and up includes
  wait data)
• Make one change at a time
• Rinse and repeat until tuning goal is achieved
• This is version-independent and will work from
  version 7.0.12 and up

30
The Method Behind the Madness Making one change
at a time

• Change should be driven by diagnostic data
• Do not make arbitrary changes
• Example Mass changes to init.ora
• Remember Newtons Third Law of Motion
• For every action there is an equal and opposite
  reaction
• One of the oldest Texas quotes
• If it aint broke, dont fix it!

31
The Method Behind the Madness

• Set Tuning Goals
• Benchmark current performance (if feasible)
• Identify the performance bottleneck(s)
• Gather Oracle Diagnostic Data using OWI Prong I
• Gather OS Diagnostic Data Prong II
• Correlate both data sources
• Analyze tkprof trace data (9i and up includes
  wait data)
• Make one change at a time
• Rinse and repeat until tuning goal is achieved
• This is version-independent and will work from
  version 7.0.12 and up

32
The Method Behind the Madness - Summary

• Set tuning goals
• Benchmark
• Identify bottlenecks
• Make one change at a time
• Rinse and repeat until tuning goal is achieved

OWI Diagnostics Prong I(VSystem_Event,VSessio
n_Event, VSession_Wait) Perform necessary
drilldowns from OWI to (VSQL, VLatch, VSession)
OS Diagnostic Data Prong II Required for
additional information only if Prong I does not
reveal the problem
Two-pronged approach
33
The Guts of OWI(Credits Jonathan Lewis for
some of the trace events)
34
OWI

• VSystem_Event Wait data since last instance
  startup
• VSession_Event Wait data for connected
  sessions
• VSession_Wait/vSession Wait data for current
  sessions waiting
• For a dynamic problem - Tracing sessions using
  the 10046 event
• Additional sample drilldown views
• VSession (In 10g, we have session-wait
  information here)
• VSql
• VLatch

35
VSystem_Event

• Columns
• Event
• Total_Waits
• Total_Timeouts
• Time_Waited in centiseconds
• Avg_Wait in centiseconds
• Time_Waited_Micro in microseconds

36
VSession_Event

• Same as VSystem_Event
• This is each sessions wait history
• Additional Columns
• Sid Session identifier
• Max_Wait In centiseconds
• Volatile data, data is lost as sessions log on
  and off
• Broken in 9iR2 on some platforms
• SID in this view does not match SID in vsession
• It is one off one less than vsession

37
VSession_Wait The Hound Dog

• Complex view to understand
• Wait statistics are reported as they happen
• Reveals the vicinity of the bottleneck-)
• Columns you should care about
• Sid Session Identifier
• Seq Occurrence count for a wait
• Event Name of the event
• P1TEXT..P3TEXT Description of P1..P3
• P1..P3 Location of the bottleneck
• State Session state
• Wait_time Completed wait (in seconds)
• Seconds_In_Wait Wait in Progress (in seconds)

38
VSession_Wait (P1..P3)

• p1 - p3
• Provides location of the wait events
• Example For db file sequential read
• p1 is the file
• p2 is the block
• p3 is the number of blocks in the I/O call
• Example For latch free
• p2 is the latch
• Look up the latch with latch in vlatch

39
VSession_Wait (State)

• WAITING
• Currently waiting for the event.
• WAITED UNKNOWN TIME
• Timed_Statistics is not set to TRUE, i.e., is set
  to FALSE.
• WAITED SHORT TIME
• Waited for less than 1 centisecond.
• WAITED KNOWN TIME
• If the resource that is waited upon is gained at
  a later time, the state changes from Waiting to
  Waited Known Time.

40
VSession_Wait (Wait_Time)

• The value for this column is STATE dependent.
• If state (WAITING or WAITED UNKNOWN TIME or
  WAITED SHORT TIME) then Wait_Time
  Irrelevant
• End If
• If state (WAITED KNOWN TIME) then
  Wait_Time Actual wait time, in seconds
• End If

41
VSession_Wait (Seconds_In_Wait)

• The value for this column is STATE dependent.
• If STATE (WAITED UNKNOWN TIME or WAITED
  SHORT TIME or WAITED KNOWN TIME) then
  Seconds_In_Wait Irrelevant
• End If
• If STATE (WAITING) then Seconds_In_Wait
  Actual Wait Time In Seconds
• End If

42
VSession_Wait (Seconds_In_Wait)

• If Wait_Time 0 then Seconds_In_Wait
  Current Wait Elsif Wait_Time gt 0 then
  Seconds_In_Wait Seconds since start of previous
  wait End if
• (Seconds_In_Wait Wait_Time)/100 - Active
  of seconds since end of last wait
• Check Bug Nos. 2803772, 2843192, 2873528 -
  Seconds_In_Wait resets on every seq increment -
  Non-zero Wait_Time shows up as Null Event.

43
Types of Wait Events

• Non-Idle versus Idle
• Foreground versus Background
• There are obvious exceptions in both categories
• Non-Idle is usually foreground related
• db file sequential read
• db file scattered read
• SQLNet message to client
• SQLNet more data to client
• Idle is usually background related
• pmon timer
• smon timer
• SQLNet message from client Exception, this is
  for a foreground

44
Event 10046

• Flexible SQL_TRACE
• Levels
• 0 Off Setting SQL_TRACE FALSE
• 1 Statistics Setting SQL_TRACE TRUE
• 4 Statistics Bind Variable Values
• 8 Statistics Wait Events
• 12 Statistics Wait Events Bind Variable
  Values
• BEWARE - At level 8 and above, large amounts of
  trace information will be generated

45
Enabling 10046 dbms_system. - 8i

• PROCEDURE SET_BOOL_PARAM_IN_SESSION
• Argument Name Type
  In/Out Default?
• ------------------------------
  ----------------------- ------ --------
• SID NUMBER
  IN
• SERIAL NUMBER
  IN
• PARNAM VARCHAR2
  IN
• BVAL BOOLEAN
  IN
• PROCEDURE SET_INT_PARAM_IN_SESSION
• Argument Name Type
  In/Out Default?
• ------------------------------
  ----------------------- ------ --------
• SID NUMBER
  IN
• SERIAL NUMBER
  IN
• PARNAM VARCHAR2
  IN
• INTVAL BINARY_INTEGER IN
• PROCEDURE SET_EV
• Argument Name Type
  In/Out Default?
• ------------------------------
  ----------------------- ------ --------

46
Enabling 10046 The Recipe

• Identify the sessions process ID (SPID)
• - select S.Username, P.Spid, S.Sid, S.Serial
  from VSESSION S, VPROCESS P where S.PADDR
  P.ADDR and S.Username XXX
• Set timed_statistics to true (if not already set)
• exec dbms_system.set_bool_param_in_session(sid,
  serial, 'TIMED_STATISTICS', TRUE)
• Prevent trace file truncation due to default
  setting of MAX_DUMP_FILE_SIZE
• exec dbms_system.set_int_param_in_session(sid,
  serial, 'MAX_DUMP_FILE_SIZE', 2147483647)
• Turn on trace
• exec dbms_system.set_ev(sid, serial, 10046, 8,
  '')
• Run the application
• Turn off trace
• exec dbms_system.set_ev(sid, serial, 10046, 0,
  '')
• Locate trace file in USER_DUMP_DEST using SPID
  from 1
• Run TKPROF on trace file

47
Some Useful Diagnostic Events a
not-so-well-know package

• 10046 SQL Tracing and Wait Data Collection
• 10053 Optimizer tracing
• 10032, 10033 Sort tracing
• 10128 Partition tracing
• 10391 Parallel Query tracing
• 10060 Query Transformations tracing (Query
  unnesting/View Merging)
• 10730 SQL Statement tracing (Row-level
  security)
• DBMS_OLAP For diagnosing queries to
  Materialized Views and Query rewrites

48
Reading the output of TKPROF

• call count cpu elapsed disk query current
  rows
• Parse 1 0.02 0.02 0
  0 0 0
• Execute 1 0.01 0.01 0 0
  0 0
• Fetch 27 0.24 0.36 1230 2342
  0 399
• Totals 29 0.27 0.39
  1230 2342 0 399
• Elapsed CPU (Wait for I/O or Context
  Switch)
• Disk Physical I/O
• Query Current Logical I/O

49
Getting Oracles Plan of Action

• Query - SELECT a.id,a.name,b.name FROM author
  a, book b WHERE a.author_id
  b.book_author_id AND a.author_id 101
  ORDER BY b.name
• Reading the Execution Plan
• Query Plan
• --------------------------------------------------
  ------------------------------
• 1.0 SELECT STATEMENT Statement1 Cost 148
• 2.1 SORT ORDER BY (7th)
• 3.1 FILTER (6th)
• 4.1 NESTED LOOPS (5th)
• 5.1 TABLE ACCESS BY ROWID AUTHOR (2nd)
• 6.1 INDEX UNIQUE SCAN AUTHOR_ID UNIQUE
  (1st) 5.2 TABLE ACCESS BY ROWID BOOK (4th)
• 6.2 INDEX RANGE SCAN BOOK_AUTH_ID NON-UNIQUE
  (3rd)

50
Putting it all together

• Multiple snapshots and deltas on vsystem_event
  reveal db file sequential read as the wait
  event with the highest frequency
• This wait event is for single block reads
• We start our path down to vsession_event and
  then to vsession_wait
• P1 and P2 reveal the file and block of the
  object
• With dba_extents and dba_ data_files, the culprit
  segment(s) are singled out

51
Putting it all together

• Join vsession_wait with vsql gets the SQL
  executed by the waiting sessions
• Using OS prong you collect I/O statistics. Device
  response times are sub-optimal
• sar d reveals device response times at 80ms
• Data collection and analysis diagnoses the
  problem as index overuse.
• Root cause Too many RULE Hints

52
Whats new in Oracle 10g ? - Active Session
History!

• New source of Oracle database performance data in
  10g
• An active session is one which is in a user call
• Parse
• Execute
• Fetch
• Provides historical information about recently
  sampled active sessions
• ASH VSESSION_WAIT with History
• Note In 10g VSESSION_WAIT is integrated with
  VSESSION
• It facilitates spot analysis of both foreground
  and background sessions

53
Extreme Example 1

• 8GB SGA (4.5GB Shared Pool)
• Pre-tuning state
• Init.ora was tuned like crazy.
• To attain a high library cache-hit ratio memory
  was periodically added (in vain).
• Bad response times with online queries.
• System was experiencing severe parsing hiccups.
• Bottleneck - Severe shared pool latch contention
• Cause - Lack of bind variable usage
• Post-tuning state - On fixing the application,
  shared pool was shrunk to 256MB. Pre-tuning
  state symptoms vanished.

54
Extreme Example 2

• 6GB SGA (4GB Database Buffer Cache)
• Pre-tuning State
• Tuned the heck out of Init.ora parameters.
• Cache-hit ratios were nice and balmy (90s).
• Terrible response times.
• High-levels of CPU usage.
• Bottleneck Severe contention for the cache
  buffers chains andcache buffers lru chain
  latches. Moderate contention on db
  filesequential read and buffer busy waits.
• Cause - Correlated sub-queries, queries forced
  to use indexes,lack of enough freelists many
  concurrent insert tables
• Post-tuning State - Fixed the application, added
  more freelistsand freelists groups to the
  relevant tables, DB cache sized to 1GB

55
The Method the Madness Useful Sites

• www.orapub.com - Craig Shallahamers website
• www.hotsos.com - Cary Millsaps website
• www.evdbt.com - Tim Gormans website
• www.jlcomp.demon.co.uk - Jonathan Lewis
• www.scaleabilities.co.uk - James Morle

56
The Method the Madness Useful Papers Books

• The YAPP Paper By Anjo Kolk, Shari Yamaguchi
  Jim Viscusi
• James Morles Scaling Oracle8i
• Jonathan Lewiss Practical Oracle8i
• Cary Millsaps Optimizing Oracle Performance
• Tom Kytes Effective Oracle by Design
• Oracle Performance Tuning 101 Yours Truly et.
  al

57
Diagnosing an Oracle Performance Problem A Demo
58
Diagnosing an Oracle Performance Problem A Demo

• View script toolkit
• Run an I/O-intensive PL/SQL program
• Use scripts to identify performance problem

59
Application Performance Optimization A DBAs
Perspective(Credits Wolfgang Breitling Tim
Gorman on the Optimizer Section)
60
Application Performance Optimization A DBAs
Perspective

• What is the 80-20 rule?
• Optimization Methodologies
• Controlling the Optimization Method
• Controlling the Optimizers Behavior
• Using Hints
• Calculation of object statistics Why, How, How
  Much and How Often?
• Indexing Strategies
• When should you rebuild your fragmented indexes?
• Join Strategies
• Managing Database Latching
• Some low-hanging fruit in SQL Land

61
What is the 80-20 rule? - My Perspective

• 80 of the worlds problems are NOT created by a
  DBA - -)
• There is a nasty corollary that can be derived
  from this
• Oracle Instance Configuration and System
  Optimization will provide lt 20 impact
• 20 (or less) of your SQL will inflict 80 of
  more of the pain in your system
• 80 or more of the I/O on your system is
  generated by SQL on lt 20 of the objects on the
  system

62
Optimization Methodologies

• Rule
• Rigid
• Preferred method prior to Oracle 7.3.4
• Stable across releases (except in 10g)
• De-supported in 10g
• Does not need any statistics
• Cost
• Flexible
• Preferred method in Oracle 8.0 and higher
• Requires object-level statistics
• By default, is naïve with its assumptions
• Utilizes new functionality in the database across
  different releases

63
Optimization Methodologies Rule

• A bunch of rules - 15 basic rules
• Rule 15 - Full Table Scan
• Rule10 Access by a Composite Index Prefix
• Rule9 Access by a Single-Column Index or Index
  Merge
• Rule8 Access by a Composite Index (An index
  with more than 1 column) entire key
• Rule1 - Access by ROWID

64
Optimization Methodologies Cost

• Basic Premise - Execute SQL in the least
  expensive way
• Requires statistics to determine cost of the plan
• Cost XXXX
• Prior to 9iR2 it was the cost of performing I/O
• In 9iR2, the CPU cost is also factored in
• Need to explicitly set DBMS_STATS.SET_SYSTEM_STATS
  
• Stats are stored in SYS.AUX_STATS
• Affected by DB_FILE_MULTIBLOCK_READ_COUNT

65
Optimization Methodologies Cost

• Statistics can be - ESTIMATEd or COMPUTEd
• ANALYZE is rumored to be on the de-support train
• In 9i and up use DBMS_STATS
• Early releases of 8i was buggy especially with
  bitmapped indexes. Worth a shot.
• For Oracle Applications use FND_STATS or the new
  equivalent
• Use histograms in version 7.3 and up on skewed
  data but only on SQL with hard-coded values

66
Oracle 9i Optimizer Changes

• Histograms are used even with bind variables
• This is done with BIND VARIABLE VALUE PEEKING
• This is done at the FIRST PARSE of the cursor
• If bind variable values change, the plan does not
  change UNTIL the SQL is aged out
• So based on the bind-variable value, the plan may
  not be optimal

67
Oracle 9i More data on your SQL

• Enhanced statistics in VSQL and VSQLAREA
• VSQLAREA Some interesting columns
• Fetches
• CPU_Time
• Elapsed_Time
• Child_Latch
• VSQL Some interesting columns
• Fetches
• Plan_Hash_Value
• CPU_Time
• Elapsed_Time
• In 10g, there is a replacement for SQL_Hash_Value
  SQL_ID

68
Oracle 9i More data on your SQL

• PLAN_TABLE
• Create new PLAN_TABLE after each upgrade
  utlxplan.sql
• Contains the execution plan for SQL statements
  for EXPLAIN PLAN requests
• New Columns
• CPU_COST
• IO_COST
• TEMP_SPACE
• ACCESS_PREDICATES (Start and Stop values for
  Indexes)
• FILTER_PREDICATES

69
Oracle 9i More data on your SQL

• VSQL_PLAN_STATISTICS
• Provides statistics at the row-source level and
  also cumulative values
• STATISTICS_LEVEL ALL
• Statspack Snap Level gt 5

70
Oracle 9i New Access Paths

• INDEX_JOIN
• Small indexes with all columns to resolve the
  query without visiting the table
• In 8i _INDEX_JOIN_ENABLEDFALSE
• In 9i, it defaults to TRUE
• Index Skip Scan
• No longer require the leading column in the index
  to be referenced in the WHERE clause

71
Controlling the Optimization Method

• At the Instance Level OPTIMIZER_MODE
• FIRST_ROWS, ALL_ROWS, CHOOSE, RULE
• CHOOSE STATISTICS ALL_ROWS
• Preferred Setting CHOOSE
• Setting to ALL_ROWS prior to 9iR1 can cause
  statistics collection on the dictionary objects
  of SYS
• This can cause deadlocks and significant delays
  as queries to the Oracle Dictionary is optimized
  using RULE-based.

72
Controlling the Optimization Method

• At the Session Level alter session.
• At the Statement Level - / HINT /
• Changing initialization parameters
• DB_FILE_MULTIBLOCK_READ_COUNT
• HASH_MULTIBLOCK_IO_COUNT
• HASH_AREA_SIZE
• Setting Work-area Policy to AUTO and setting
  PGA_AGGREGATE_SIZE provides better stability
  against session-level parameters that affect the
  optimizer
• Note PGA_AGGREGATE_SIZE does not limit PL/SQL
  tables

73
Controlling the Optimizers Behavior

• Default values of some of the Optimizer-related
  parameters (OPTIMIZER_) require modification
  when execution plans go south-bound.
• OPTIMIZER_INDEX_CACHING
• OPTIMIZER_INDEX_COST_ADJ
• OPTIMIZER_MAX_PERMUTATIONS
• OPTIMIZER_DYNAMIC_SAMPLING
• In Oracle 9i, the following are required if you
  run into bug2992537
• EVENT 10076 Ensure cartesian products are
  costed
• alter session set events 10076, trace name
  context forever
• _ORDERED_NESTED_LOOP FALSE Ensure INDEX_FFS
  is cheaper than a range scan
• _TABLE_SCAN_COST_PLUS_ONE FALSE Ensure
  INDEX_FFS(single cost) is cheaper than a range
  scan
• _OPTIMIZER_NEW_JOIN_CARD_COMPUTATION FALSE
  Ensure cost calculated is close to that of Oracle
  817
• In Oracle 8 and before it was as easy as setting
  OPTIMIZER_SEARCH_LIMIT 1
• Now it is _OPTIMIZER_SEARCH_LIMIT and the mininum
  value is 3

74
Controlling the Optimizers Behavior

• Useful for tuning packaged applications
• Useful for tuning environments that require both
  hash and nested loop joins
• Please test first before deploying in production
• Testing can be done at the session-level
• Read Tim Gormans paper Searching for
  intelligent life in the Oracle Optimizer at
  http//www.evdbt.com
• Read Wolfgang Breitlings paper What is new in
  the Oracle 9i CBO at http//www.centrexcc.com

75
Controlling the Optimizers Behavior
OPTIMIZER_INDEX_CACHING

• Tells the optimizer the probability that it will
  find an index block in the cache
• Controls optimizer propensity to pick an index
  for SQL statement (usually using the nested loops
  join method)
• Defaults to 0
• Range of values - 0 100
• When set to a high value (90 or higher), the
  optimize isencouraged to use nested loops over
  other join methods
• This parameter should be changed on all
  transactional systems

76
Controlling the Optimizers Behavior
OPTIMIZER_INDEX_COST_ADJ

• Controls the optimizers propensity to use index
  scans
• Functionality similar to OPTIMIZER_INDEX_CACHING
• Defaults to 100
• Range of values - 1- 10000
• Need to determine the appropriate value on a
  per-database basis

77
Controlling the Optimizers Behavior
OPTIMIZER_INDEX_COST_ADJ

• Calculating OPTIMIZER_INDEX_COST_ADJ
• SELECT EVENT, AVERAGE_WAITFROM VSYSTEM_EVENTWHE
  RE EVENT LIKE db file s
• EVENT AVERAGE_WAITS
• db file sequential read .33178629
• db file scattered read 2.190087
• In the above example, the average single-block
  I/O requests takes only 15 of the average
  multi-block I/O. On this systemset
  OPTIMIZER_INDEX_COST_ADJ to 15.
• Source - Tim Gormans paper

78
Controlling the Optimizers Behavior
OPTIMIZER_MAX_PERMUTATIONS

• Number of plan permutations that the optimizer
  chooses from.
• Choice of driving table is key for query
  performance
• Defaults to 80000 - Driving table is usually the
  smallest table
• When set lt 80000 - Driving table is determined
  from 8 different plans
• The plan with the lowest cost is picked
• The default is 2000 in Oracle 9i and up

79
Controlling the Optimizers Behavior
OPTIMIZER_DYNAMIC_SAMPLING

• New parameter in Oracle 9i
• Normally used for unanalyzed tables, but there
  are exceptions
• Assists in better estimation of cardinality and
  selectivity
• Relevant for long-running queries (over a few
  seconds)
• When OPTIMIZER_FEATURES_ENABLE is set to lt 9.0.2,
  there will be no dynamic sampling

80
Controlling the Optimizers Behavior
OPTIMIZER_DYNAMIC_SAMPLING

• Range of values 0-10
• 0 No dynamic sampling
• 1 No dynamic sampling if query has less than 2
  tables, table has no indexes, has not been
  analyzed, a full-scan is deemed expensive
• 2 Dynamic sampling on all un-analyzed tables,
  of sampled blocks32
• 3 2 for tables where selectivity estimation
  was done
• 4 3 single table predicates that refer 2 or
  more columns in the WHERE clause
• 5 thru 9 Same as 4, but with sampled blocks64,
  128, 256, 1028, 4096
• 10 Same as 4, but all blocks in the table are
  sampled
• If the number of blocks at level 5-9 gt 50 of the
  total blocks, every block is sampled

81
10053 Trace File Analysis( Credits Peter Bach
of the OakTable)
82
Determining what the Optimizer is doing

• Setting 10053
• dbms_system.set_ev ()
• Valid levels 1 or 2
• Unlike other events level 2 produces less than 1
• Looking at the output
• Dissecting the plethora of information that is
  being presented
• Read Wolfgang Breitlings paper on
  http//www.centrex.com
• A Look under the Hood of CBO The 10053 Event

83
The SQL

• select      offer.code_label, sda.index1_value,
  count()from      service_history sh,    
  reference_code market,     service_da_array
  sda,     reference_code offer where     
  sh.service_id sda.service_id and     
  sh.service_status_code 3 and    
  to_date('15/12/2003 120000', 'dd/mm/yyyy
  hh24miss')             between
  sh.effective_start_date and sh.effective_end_date
  and     market.reference_type_id 903285 and
       market.reference_code sh.general_1 and
       market.code_label in ('MINNEAPOLIS') and
       sda.derived_attribute_id 907438 and     
  to_date('15/12/2003 120000', 'dd/mm/yyyy
  hh24miss')             between
  sda.effective_start_date and sda.effective_end_dat
  e and     offer.reference_type_id 905238 and
       offer.reference_code sda.index1_value
  group by      offer.code_label,
  sda.index1_value

84
The Old Execution Plan Autotrace Output

• Execution Plan ----------------------------------
  ------------------------    0      SELECT
  STATEMENT OptimizerCHOOSE (Cost1333 Card1
  Bytes101)    1    0   SORT (GROUP BY)
  (Cost1333 Card1 Bytes101)    2    1     TABLE
  ACCESS (BY INDEX ROWID) OF 'SERVICE_! HISTORY'
  (Cost2 Card9974 Bytes279272)    3    2      
  NESTED LOOPS (Cost1260 Card1 Bytes101)   
  4    3         NESTED LOOPS (Cost1258 Card1
  Bytes73)    5    4           MERGE JOIN
  (CARTESIAN) (Cost3 Card1 Bytes42)    6   
  5             TABLE ACCESS (BY INDEX ROWID) OF
  'REFERENCE_CODE' (Cost2 Card1 Bytes21)   
  7    6               INDEX (RANGE SCAN) OF
  'I_REFERENCE_CODE_REF_TYPE' (NON-UNIQUE) (Cost1
  Card26)    8    5             BUFFER (SORT)
  (Cost1 Card1 Bytes21)    9    8              
  TABLE ACCESS (BY INDEX ROWID) OF 'REFERENCE_CODE'
  (Cost1 Card1 Bytes21)   10   9                
  INDEX (RANGE SCAN) OF 'I_REFERENCE_CODE_REF_TYPE'
  (NON-UNIQUE)   11   4           TABLE ACCESS
  (BY INDEX ROWID) OF 'SERVICE_DA_ARRAY' (Cost1255
  Card50 Bytes1550)   12  11             INDEX
  (RANGE SCAN) OF 'I_SERVICE_DA_ARRAY_DA'
  (NON-UNIQUE)   13    3         INDEX (RANGE
  SCAN) OF 'P_SERVICE_HISTORY' (UNIQUE) (Cost1
  Card1)  Statistics ---------------------------
  -------------------------------           0 
  recursive calls           0  db block gets    
  8977230  consistent gets           0  physical
  reads        1556  redo size         772  bytes
  sent via SQLNet to client         652  bytes
  received via SQLNet from client           2 
  SQLNet roundtrips to/from client           2 
  sorts (memory)           0  sorts
  (disk)           5  rows processed

85
10053 Output

• GENERAL PLANSJoin
  order1 REFERENCE_CODE MARKET REFERENCE_CODE
  OFFER SERVICE_DA_ARRAY SDA SERVICE_HISTORY
  SH Now joining REFERENCE_CODE OFFER
  NL Join  Outer table cost 2  cdn 1 
  rcz 18  resp  2  Inner table
  REFERENCE_CODE    Access path tsc  Resc
  110    Join  Resc  112  Resp  112OPTIMIZER
  PERCENT INDEX CACHING 75  Access path index
  (join stp)      Index I_REFERENCE_CODE_REF_TYPE
    TABLE REFERENCE_CODE      RSC_CPU 0  
  RSC_IO 1  IX_SEL  0.0000e00  TB_SEL 
  2.1968e-04    Join  resc 3  resp 3Join
  cardinality  0 outer (0) inner (26) sel
  (1.0000e00)  flag0Grouping column cardin!
  ality CODE_LABEL    26Grouping column
  cardinality INDEX1_VAL    176  Best NL cost
  3  resp 3Join result cost 3  cdn 1  rcz 36

86
10053 Output

• Now joining SERVICE_DA_ARRAY SDA NL
  Join  Outer table cost 3  cdn 1  rcz 36 
  resp  3  Inner table SERVICE_DA_ARRAY   
  Access path tsc  Resc 2402    Join  Resc 
  2405  Resp  2405OPTIMIZER PERCENT INDEX CACHING
  75  Access path index (no sta/stp keys)     
  Index I_SERVICE_DA_ARRAY  TABLE
  SERVICE_DA_ARRAY      RSC_CPU 0   RSC_IO
  7166  IX_SEL  1.0000e00  TB_SEL 
  5.1224e-03    Join  resc 7169  resp
  7169OPTIMIZER PERCENT INDEX CACHING 75 
  Access path index (join stp)      Index
  I_SERVICE_DA_ARRAY_DA  TABLE SERVICE_DA_ARRAY  
      RSC_CPU 0   RSC_IO 1255  IX_SELnbs! p
  0.0000e00  TB_SEL  1.2500e-01    Join  resc
  1258  resp 1258Join cardinality  0 outer (0)
  inner (8753) sel (5.6818e-03) 
  flag0Grouping column cardinality
  CODE_LABEL    26Grouping column cardinality
  INDEX1_VAL    176  Best NL cost 1258  resp
  1258

87
10053 Output

• SM Join  Outer table     resc 3  cdn 1  rcz
  36  deg 1  resp 3  Inner table
  SERVICE_DA_ARRAY    resc 1561  cdn 8753  rcz
  31  deg  1  resp 1561    using join1
  distribution2 groups1    SORT resource     
  Sort statistics      Sort width          598
  Area size     1048576 Max Area size  
  104857600   Degree 1      Blocks to
  Sort        1 Row size           50
  Rows          1      Initial runs          1
  Merge passes        1 IO Cost / pass       
  145      Total IO sort cost 73      Total CPU
  sort cost 0      Total Temp space used 0   
  SORT resource      Sort statistics      Sort
  width          598 Area size     1048576 Max
  Area size   104857600   Degree 1      Blocks
  to Sort       25 Row size           45
  Rows       8753      Initial runs          1
  Merge passes        1 IO Cost / pass       
  169      Total IO sort cost 97      Total CPU
  sort cost 0      Total Temp space used 0 
  Merge join  Cost  1734  Resp  1734

88
10053 Output

• Join cardinality  0 outer (0) inner (8753)
  sel (5.6818e-03)  flag0Grouping column
  cardinality CODE_LABEL    26Grouping column
  cardinality INDEX1_VAL    176  Best NL cost
  1258  resp 1258SM Join  Outer table    
  resc 3  cdn 1  rcz 36  deg 1  resp 3  Inner
  table SERVICE_DA_ARRAY    resc 1561  cdn
  8753  rcz 31  deg  1  resp 1561    using
  join1 distribution2 groups1    SORT
  resource      Sort statistics      Sort
  width          598 Area size     1048576 Max
  Area size   104857600   Degr! ee 1      Blocks
  to Sort        1 Row size           50
  Rows          1      Initial runs          1
  Merge passes        1 IO Cost / pass       
  145      Total IO sort cost 73      Total CPU
  sort cost 0      Total Temp space used 0   
  SORT resource      Sort statistics      Sort
  width          598 Area size     1048576 Max
  Area size   104857600   Degree 1      Blocks
  to Sort       25 Row size           45
  Rows       8753      Initial runs          1
  Merge passes        1 IO Cost / pass       
  169      Total IO sort cost 97      Total CPU
  sort cost 0      Total Temp space used 0 
  Merge join  Cost  1734  Resp  1734

89
10053 Output

• HA Join  Outer table     resc 3  cdn 1  rcz
  36  deg 1  resp 3  Inner table
  SERVICE_DA_ARRAY    resc 1561  cdn 8753  rcz
  31  deg  1  resp 1561    using join8
  distribution2 groups1  Hash join one ptn
  Resc 1 !   Deg 1      hash_area  128
  (max12800)  buildfrag  1               
  probefrag   23 ppasses    1  Hash join   Resc
  1565   Resp 1565Join result cost 1258  cdn
  1  rcz 67

90
The New Execution Plan Autotrace Output 10076
and the 3 underscore parameters are set

• Execution Plan-----------------------------------
  ----------------------- ! nbsp 0      SELECT
  STATEMENT OptimizerCHOOSE (Cost1747 Card12
  Bytes1212)   1    0   SORT (GROUP BY)
  (Cost1747 Card12 Bytes1212)   2    1    
  TABLE ACCESS (BY INDEX ROWID) OF 'REFERENCE_CODE'
  (Cost1 Card26 Bytes546)   3    2       NESTED
  LOOPS (Cost1674 Card12 Bytes1212)   4   
  3         NESTED LOOPS (Cost1596 Card78
  Bytes6240)   5    4           HASH JOIN
  (Cost1442 Card77 Bytes3773)   6   
  5             TABLE ACCESS (BY INDEX ROWID) OF
  'REFERENCE_CODE' (Cost2 Card1 Bytes21)   7   
  6               INDEX (RANGE SCAN) OF
  'I_REFERENCE_CODE_REF_TYPE' (NON-UNIQUE) (Cost1
  Card26)   8    5             TABLE ACCESS
  (FULL) OF 'SERVICE_HISTORY' (Cost1439 Card9974
  Bytes279272)   9    4           TABLE ACCESS
  (BY INDEX ROWID) OF 'SERVICE_DA_ARRAY' (Cost2
  Card1 Bytes31)  10    9             INDEX
  (RANGE SCAN) OF 'I_SERVICE_DA_ARRAY' (NON-UNIQUE)
  (Cost1 Card1)  11    3         INDEX (RANGE
  SCAN) OF 'I_REFERENCE_CODE_REF_TYPE' (NON-UNIQUE)
• Statistics--------------------------------------
  --------------------         14  recursive
  calls          0  db block gets      12395 
  consistent gets          0  physical
  reads          0  redo size        772  bytes
  sent via SQLNet to client        652  bytes
  received via SQLNet from client          2 
  SQLNet roundtrips to/from client          1 
  sorts (memory)          0  sorts
  (disk)          5  rows processed

91
Using Hints

• Modifying the Optimizers Behavior will reduce
  the number of times you will need to use hints
• Hints to the optimizer is equivalent of
  application-level hard-coding.
• Across releases, hints pose an additional
  administrative overhead of performing regression
  testing.
• Across releases, the Optimizer has gotten very
  smart.
• Stored Outlines ? Stored Hints
• Time for a magical performance demo
• AVOID USING HINTS AS MUCH POSSIBLE!!!

92
Calculation of Object Statistics

• The Burning Question How often should I analyze
  and collect stats?
• Standard Answer As often as your data changes
• That is the WRONG answer
• Reasonable Answer As often as the statistical
  composition of your data changes
• It does not matter if you pump in 1,000,000 rows
  everyday
• Collect object statistics when the statistical
  distribution changes

93
Calculation of Object Statistics

• Why cant I just analyze everyday?
• ANALYZE invalidates all of the objects SQL in
  the cache
• That initiates a hard parse the next time
  around of every SQL statement
• If your system is already suffering from shared
  pool latch and library cache latch problems, the
  pain of re-parsing every SQL in the cache, will
  outweigh the benefit of new statistics
• Setting the MONITORING attribute has limited
  value
• Change is measured with a static percentage
  20???

94
Indexing Strategies

• The columns of an index should be ordered based
  on access patterns NOT distinct values
• There are a zillion types of indexes today
• Enlighten yourselves and your developers
• Contrary to some papers, bitmapped indexes should
  be used primarily for read-intensive applications
• Locking is done at the extent-level in the
  bitmaps
• Use PARALLELISM for creation and access
• Use NOLOGGING for creation

95
Indexing Strategies

• If PGA_AGGREGATE_SIZE is not used, use large
  SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE for
  index creations
• Indexes can be rebuilt online and in-place today
• Dont make index rebuilding a regular
  maintenance task every week
• Rebuild only when there is a need, here are some
  symptoms
• Application Response Time for a given query using
  a said index access is unacceptable
• There is trace data to corroborate that Index I/O
  is the key issue
• The number of blocks physically read is a
  significant number when compared to the total
  number of rows returned
• Clustering Factor of the index is bad and the
  query performs well defined range scans.

96
Join Strategies

• The normal joins
• Nested Loops
• Sort-Merge
• Hash
• Hash Joins are great for the following scenario
• Table A (small, say with 1000 rows)
• Table B (big, say 1,000,000 rows)
• SQL has a WHERE clause predicate that processes
  most of Table B

97
Managing Database Latching

• A latch is a piece of code run within the Oracle
  executable where access is serialized
• With a few exceptions, one and only one process
  can run this code at any given time
• Examples
• Reading a block in the DB Cache
• Writing a block in the DB Cache
• Moving blocks in the LRU List
• Hard parsing a SQL statement
• Soft parsing a SQL statement
• Allocating space in the redo log buffer
• Writing redo entries in the redo log buffer

98
Managing Database Latching

• Wasteful demand for latches in the database can
  be usually managed by the following
• Reducing logical I/O demand for SQL
• Avoiding unnecessary updates to tables
• Designing and coding SQL that use bind variable
• Using SESSION_CACHED_CURSORS

99
Some low-hanging fruit in SQL Land

• SQL-intensive code in PL/SQL
• Compute-intensive code in Java/C/C
• Dont code SQL and PL/SQL like Pascal
• Replacing simple PL/SQL (ifthenelse..end if)
  with DECODE when possible
• Reduce the number of context switches between SQL
  and PL/SQL
• Design your code to do as much in SQL
• Use Bind Variables
• Hard-coding values usually is a bad practice
• Prior to 9i, you may use this to force the
  optimizer to use histograms

100
Some low-hanging fruit in SQL Land

• Analytical functions may look complicated, but
  can provide very powerful programming
  capabilities
• Making the code in triggers just a call to a
  procedure
• Put all the code in the procedure
• Use XDUAL with a view on top of it instead of
  DUAL
• Use Set Operators (MINUS, INTERSECT)
• After all, we are still a relational database
• Equivalent Functionality of an Outer-join between
  Table A and Table B implemented using the MINUS
  set operaiton
• Select blah1, blah2 from AMINUSSelect blah3,
  blah4 from B -- What is in A that is NOT in B

101
Some low-hanging fruit in SQL Land

• Indexes on foreign key columns for master-detail
  transactional tables
• Using non-unique indexes for UNIQUE constraints
• Helps if you turn constraints ON and OFF
• With this, the index is NOT dropped
• Using the NOVALIDATE option with ENABLE of a
  constraint
• Creating hash clusters with 1 table
• For read-only tables that are look-up tables

102
Great literary works on good application design
and coding

• Tom Kytes Effective Oracle by Design
• John Beresniewiczs Oracle Built-In Packages
• Steven Feuersteins Oracle PL/SQL Programming
  Oracle PL/SQL Best Practices
• Guy Harrisons High Performance Tuning

103
Oracle Instance Configuration Optimization
104
Back to the Basics Why are we optimizing the
Instance?

• What are the wait events in your database?
• Are you suffering from CTD?
• Are you running your life on cache-hit ratios?
• Is it time for a drink or two?
• Friendly Advice - Dont touch anything without
  corroborating wait events that warrant a
  configuration change

105
Oracle Architecture Overview
System Global Area
Database Buffer Cache
L R U
Misc. Buffers
Main
Redo Log Buffer
Aux.
Repl.
Dirty
Shared Pool Area
Misc. Queues
Bootstrap Segment
Dictionary Cache
Library Cache
Server Process
PGA
/u10
Archived Redo Logs
User Process
106
What happens when you press Enter?

• SQL Statement Processing
• Parse
• Define
• Bind
• Execute
• Fetch (For SELECT only)

107
SQL Statement Processing - Parse

• Steps in Parsing
• Syntax Check
• Object Resolution
• Security Check
• Build Parse Tree Execution Plan
• Store Parse Tree Execution Plan in the Shared
  SQL Area (Lib. Cache)
• In 9i and up, bind variable value peeking is
  performed
• Hard vs. Soft Parse

108
SQL Statement Processing - Define

• Resolve and map data types on the client and the
  server
• SQLNet is involved
• ARRAYSIZE negotiated

109
SQL Statement Processing - Bind

• All bind variables(v_id, v_name etc.) are bound
  with their current values
• SQL is not hard-parsed for varying values
• Facilitates re-use of SQL

110
SQL Statement Processing - Execute

• Execution Plan is applied on the SGA
• Data is read or written to
• If SQL is DML then Redo Rollback is
  generated Transactional operations are
  performedEnd If

111
SQL Statement Processing - Fetch

• Data is fetched for SELECTs only
• Number of fetches is subject to ARRAYSIZE
• ARRAYSIZE has a direct impact on the amount of
  logical I/O in your database
• Perform array fetches and PL/SQL array processing
  when possible
• SQLPlus - SET ARRAYSIZE n
• Forms - Set the Records Fetched Block Property
  Sheet

112
Memory Allocation Management

• Configure no more than 50 of the memory on the
  box to all Oracle SGAs
• In 10g, there will be an equivalent for the SGA
  too
• The feature is called Auto SGA Tuning
• Lock the memory for the SGA
• This slows the paging daemon, utilizes less
  resources
• Platform specific MLOCK_SGA TRUE or
  equivalent
• Account memory for the filesystem buffer cache
  and the operating system
• Give the rest for Oracle PGAs
• If 9i and up use PGA_AGGREGATE_SIZE