Oracle Performance Tuning

1
Oracle Performance Tuning

• JHPWD Enterprises

2
Who am I?

• Worked with Oracle databases over 10 years
• Built a variety of OLAP and OLTP systems
• Worked in development and performance tuning for
  7 years
• OCP 11g PL/SQL Developer
• OCA 11g DBA (OCP pending beta test scoring)

3
Set a tuning goal

• Make it measurable
• Decrease runtime by 30
• Nightly batch job must run in 3 hours
• User response in 4 secs
• Make it reasonable
• Calculate the best possible performance
• Cost threshold could achieve certain
  performance but at great cost
• When youve reached it
• STOP!
• Could always tune more dont get stuck
• Move on to the next priority (theres always
  another problem)

4
Different levels of tuning

• Database parameters DBA
• Often reactive
• Can check AWR, statspack proactively
• DBA can also help with object level parameters,
  and all areas with developers as well
• Application design and structure Developer
• Tuning starts here.
• If the application approach is bad (bind
  variables, schema structure) is bad, difficult to
  fix

5
Different levels of tuning

• Schema design and structure Developer DBA
• DBA may have more knowledge of some options
  (physical objects)
• Developer should consider the effects of table
  structure and program structure on performance,
  both on throughput and concurrency
• SQL statements (hints, statistics, indexes)
  Developer

6
Database parameters tools

• SGA
• Large pool (parallel ops)
• DB block cache
• Keep cache
• Recycle cache
• PGA
• Sort area size
• Hash area size
• Database replay SQL Performance Analyzer
• Capture a database workload or set of SQL
  statements
• Compare performance after some application or
  setting changes

7
Application design and structure

• Things that change depending on the purpose of
  the application
• Use bind variables
• Parse once, execute many
• Same execution plan regardless of where clause
  (bind variable peeking in 11g Oracle will
  automatically optimize if this causes a bad plan)
• Really for OLTP
• Reduce latches good for concurrency
  scalability

8
Application design and structure

• Things that change depending on the purpose of
  the application
• Dont use bind variables
• Parse each time you execute
• New execution plan each time (potentially 11g
  changes this again, as 11g will optimize this by
  peeking)
• Best for OLAP

9
Application design and structure

• Things that dont
• Use the database itll be storing your data for
  a long time
• Foreign key constraints
• Check constraints
• Column values in (xxx,yyy), then can eliminate
  tables from views or union all
• Not null constraints allow using index fast full
  scan to count rows
• Or have an index with a not null and a nullable
  value to count rows where column is not null
• Check constraints on materialized views w/fast
  refresh
• Triggers are evil(tm Tom Kyte) avoid them when
  one of the options above works
• These options help both data integrity and
  performance a good reason to use constraints in
  your data warehouse (at least without validation,
  to give the optimizer more information. And the
  data might not be what you expect...)

10
Application design and structure

• Things that dont
• Use procedures or views as APIs for access to the
  data
• Each API allows one transaction (add order,
  modify order...)
• Dont put all your logic in the middle tier
• Its often slow, and causes too much network
  traffic if your developers dont know what
  theyre doing
• More maintainable if your application needs a
  table structure change, change the procedure/view
  instead of bugging the middle tier developers to
  make changes

11
Application design and structure

• Things that dont
• Use a SQL statement whenever possible
• If it cant be done in SQL, then resort to
  procedural logic
• Remember collections

12
Schema design and structure

• OLTP
• Optimize for writes (and localized reads)
• 3NF
• OLAP
• Optimize for reads across various levels
• Denormalize where appropriate
• Remember the constraints
• Ideal schema design, table structure, and code
  design
• Deleting records will leave empty space in
  database blocks
• Simultaneous inserts into tables can cause
  contention
• Simultaneous updates can cause missing/lost
  updates
• Key columns based on sequences can cause
  contention on index nodes
• Partitioning can help performance and/or
  maintenance
• Be careful not to copy designs specifically
  designed for extreme constraints (Palm schema on
  a database is a bad idea)

13
SQL Statements

• Automated Advisors
• SQL Plan Baselines (optimizer will only use a
  plan better than the baseline)
• SQL Tuning Advisor
• Indexes with high impact
• Statistics
• Optimal plans (profiles)
• SQL Access Advisor
• Indexes
• Materialized Views
• Partitioning (in 11g onwards)

14
SQL Statements Understand the CBO

• Indexes vs Full table Scans
• Full table scans are not always bad
• Indexes are not always good
• It depends on how much data you are retrieving
  from the table  
• Understand cardinality, selectivity, and joins
• Settings like optimizer_index_cost_adj,
  db_multiblock_read_count
• Materialized views
• Create aggregations or pre-join tables
• Fast refresh using view logs
• Automatically use the view when base tables
  queried
• Automatic query rewrite benefits from constraints
  dimensions

15
SQL Statements Understand the CBO

• Limitations of the CBO
• Correlated columns
• Birth months horoscope signs
• City zip code
• Datatype issues
• Putting dates in number columns
• Numbers in varchar
• Skewed data distribution
• Using a far-off date instead of a null
• Data that really is skewed (Oracle assumes even
  distribution)
• Will only use left most tree plan
• All tables are joined to the same tree, one by
  one
• Sometimes a bushy approach would be better (join
  two tables, then the next two to each other, join
  the two distinct resultsets together) but the
  optimizer WILL NOT do this without intervention

16
Help the CBO

• Statistics (a solution to the limitations)
• Histograms (on all/indexed columns)
• Virtual columns (11g )
• Dynamic sampling
• Hints (another solution)
• Dangerous because they can restrict the optimizer
  from a better plan
• Better to use approach hints instead of
  explicitly defining join usage, like first_rows_n
  or all_rows
• Can force a bushy plan (no_merge, query blocks,
  etc)
• AutoTrace TK/Prof
• Capture the plan used in execution of query
  (might be different from Explain)
• Compare estimated cardinality vs. actual
  cardinality