Oracle Performance Introduction

1
Oracle Performance Introduction

• John Pingel
• 314-322-7433
• John.pingel_at_wachovia.com
• Personal jpingel_at_usa.net

2
Personal Intro

• Name John Pingel
• Title Oracle Database Performance DBA
• Database Tuning Experience 10 years
• Past Companies
• Wells Fargo/Wachocia/AG Edwards
• Mastercard
• Ameren
• TSA
• United Agri Products
• Oracle Consulting
• Etc
• Education
• BS Computer Science (U of MO Rolla)
• MBA (Washington University)
• Master of Information Management (Washington
  University)
• Oracle Certifications 8i,9i,10g

3
Agenda

• Oracle DB Statistics - What do they actually do
  and why do I care about the details?  -  This
  will help you understand why the optimizer does
  what it does
• Understanding Oracle Execution Plans
• Provide -

4
DB Performance High Level Methodology

• Look at the wait event (ignore idle)
• Your read IO (sequential scattered should be on
  top)
• Look at the scale, are you waiting 24 hours in a
  1 hour period or are you waiting 1 hour in a 24
  hour period keep perspective!!
• Look at your REAL TOP SQL (by elapsed, buffer
  gets, by cpu)
• Unfortunately, based on the info above there are
  so many directions which one can take, it cannot
  be documented

5
Your REAL top queries (by buffer gets) I
believe AWR tables are licensed seperately

• SELECT buffer_gets, executions,
  DECODE(executions,0,0,round(buffer_gets/executions
  ,0)) Buffer_gets_per_Exec,
• (SELECT y.sql_text
• FROM DBA_HIST_SQLTEXT y
• WHERE a.dbid y.dbid AND a.sql_id
  y.sql_id AND SQL_text not like 'NESTED_TABLE_GET_
  REFS' and ROWNUM lt 2) sample_sql
• FROM ( SELECT TO_CHAR (force_matching_signa
  ture)
• force_matching_signature,
• SUM (executions_delta)
  executions,
• SUM (buffer_gets_delta)
  buffer_gets,
• MAX (sql_id) sql_id,
• MAX (a.dbid) dbid
• FROM DBA_HIST_SQLSTAT a,
  dba_hist_snapshot b
• WHERE a.snap_id b.snap_id
• AND a.instance_number
  b.instance_number
• AND a.dbid b.dbid
• AND Begin_interval_time gt
  (SYSDATE - 30)
• AND force_matching_signature
  ltgt 0
• AND TO_CHAR (TRUNC
  (begin_interval_time, 'HH'), 'HH24') BETWEEN '09'
• 
  AND '17'
• GROUP BY a.force_matching_signature

6
SQL Performance High Level Methodology

• Focus on
• Queries not making SLAs
• REAL Top 10 in DB (by cycle i.e. batch vs
  OLTP)
• Before you fix a sql plan, figure out why it
  needs to be fixed
• Does it break best practice sql writing
• Is the optimizer just making a bad choice
• Other (lots of reasons but these are typically
  the least likely)

7
SQL Performance Tuning

• Look at the Cardinalities (Number of rows in a
  plan step Oracle expects to come back)
  Reasonable?
• If you had to do this, how would you approach it?
  (We will talk about execution plans)

8
Typical Means

• Sql changes
• Application design changes
• i.e. row by row to bulk, bind variable (or
  literal usage), cursor reuse, keep connection
  open, etc)
• Init.ora (select parms)
• Statistics

9
Advanced Means

• Outlines
• SQL Profiles
• init.ora parameters (beyond simple)
• Hints
• statistics modifications (beyond simple)