Performance Tuning for Oracle

by Andrew Fraser
  1. Code Listings
  2. More 10046 tracing
  3. Memory on 32 Bit Windows
  4. Introduction to PL/SQL
  5. Presentation by Allan Young, in pdf or pptx format.
  6. Tkprof
  7. SQL Tuning
  8. Cary Milsap, Diagnosing Performance Problems
  9. 10g Performance Tuning Guide
  10. 9iR2 Performance Tuning Guide
  11. 9iR1 Performance Tuning Guide
  12. 8i Performance Tuning Guide
  13. 7 Performance Tuning Guide

Coding Best Practice
  1. Comments - always author, date, purpose, change history at top
  2. Style - Code blocks, as little code as possible and readable, write for debugging
  3. SQL - create view, ||, SQL from SQL, inline views
  4. PL/SQL - loops
  5. Shell - vi, sed, awk, grep, functions
  6. SQL*Plus - set define off verify off pages 0, col, (ttitle btitle)

Performance Tuning - Methodology

  1. Old method - 'guess and grimace' with ratios
  2. New method - diagnose with oracle wait interface

Performance Tuning - Tuning Steps

  1. Quickly check the obvious database/server wide settings with:
    1. Check database and listener are up and that you can login ok
    2. Check alert log
    3. Check system OS log (/var/adm/messages, windows event viewer, dmesg, ?aix)
    4. Run sar. If cpu is high, find out why with top or topas or "/usr/ucb/ps aux|head". Can use spid.sql to identify oracle session behind unix process)
    5. sho sga - check buffer cache is not too low.
    6. cat init.ora/spfile - check no crazy settings
    7. are tables analyzed? - check last_analyzed column.
    8. run sw.sql to check what sessions are waiting on. If it is locks/enqueues, run ?/rdbms/admin/utllockt to identify cause.
  2. Look for any huge high read SQL that is obviously the problem
  3. Use 10046 trace to identify problem precisely, looking up any unusual waits in the oracle wait interface book.
  4. SQL Tuning - Check for quick win easy changes (remove hints, sort_area_size, hash_join_enabled, check tables analyzed)
  5. SQL Tuning - Check for missing indexes
  6. SQL Tuning - Start rewriting code


Run ?/rdbms/admin/utllock.sql to identify who holds locks. Lock wait events are named "enqueue" (for normal row data locks) or "latch free" (internal short duration lock).

SQL Tuning Steps

Book for this is Mark Gurry, Oracle SQL Tuning Pocket Reference, O'Reilly 2002.

  1. Tables analyzed, and stats up to date

  2. Try stripping out any hints.

  3. Try easy parameter changes:
    alter session set workarea_size_policy=manual;
    alter session set sort_area_size = 1048576000 ;
    alter session set sort_area_size = 16384 ;
    alter session set optimizer_mode=first_rows;
    alter session set cursor_sharing=exact ;
    alter session set optimizer_index_cost_adj = 10 ;
  4. Identify what columns could have indexes used. See which are currently indexed.

  5. Histograms

  6. Code rewrites:
    Switch 'in' and 'exists', 'not in' and 'not exists', add 'is not null' to 'not in' for hash anti joins.
    Remove outer joins
    SQL processing rather than plsql loops - with upsert/merge perhaps.

  7. Data warehouses/reporting: bitmap indexes, materialised views.
    create materialised view mv_workorder as ...
       WITH ROWID, SEQUENCE (time_id, calendar_year)
       WITH ROWID, SEQUENCE (prod_id)
       AS SELECT t.calendar_year, p.prod_id, 
          SUM(s.amount_sold) AS sum_sales
          FROM times t, products p, sales s
          WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
          GROUP BY t.calendar_year, p.prod_id;

Common SQL issues
  1. Missing indexes
  2. Function around indexed column - remove function or create a function based index
  3. Unnecessary subqueries - could be replaced with a simple join instead (less of an issue with newer versions)
  4. Unnecessary outer joins - test performance effect of removing the (+)'s
  5. Better to replace IN with EXISTS or replace EXISTS with IN
  6. Hash anti join not used with NOT exists / NOT in
  7. OR with subqueries being used instead of UNION of different queries - see this on Maximo
  8. Table or index has had many deletes, very few rows in each block - rebuild
  9. Inappropriate view being used - has joins that aren't needed by this particular statement
  10. IF-ELSE or multiple SQL statements being used instead of one CASE/DECODE statement
  11. Bind variables not being used - although is only an issue if a lot of time/cpu is being spent parsing.
  12. Skewed data - use histograms with analyze/dbms_stats
  13. Old stats on tables - reanalyze
  14. Missing stats on tables - analyze
  15. Incorrect init.ora settings - test effect of removing them through alter session
  16. (strictly a functional error rather than a performance issue) OR's can be dangerous - if brackets are missed from around them, results will be wrong, and also performance will be poor.

Why is my index not being used?
  1. where surname != 'FRASER'
  2. where upper(surname) = 'FRASER'
  3. where surname is null
  4. where surname like '%FRASER'
  5. small tables
  6. histograms:

    90% rows 'Yes'
    1% rows 'No'
    1% 'Maybe'
    1% 'Not in'
    1% 'Later'
    where answer = 'Yes'

    to 8i where answer = ':b1'
    9i and up bind variable peeking:
Note that is possible to permanently cache small tables:
 alter system set db_keep_cache_size=200m scope=both ;
 alter table small_table storage (buffer_pool keep);
Also note that it is possible to create function based indexes:
 create index myindex on customer(upper(surname)) ;
In order to fix:
 select * from customer where upper(surname) = 'FRASER' ;
Tom Kyte has an article on function based indexes.
Which is faster - EXISTS or IN?

Depends on the relative sizes:

select from outer_query where x IN ( select y from subquery )
select from outer_querry where EXISTS ( select null from subquery where x=y )
IN is faster than EXISTS for subqueries that return few rows.
EXISTS is faster than IN for subqueries that return many rows.

Fastest is
Small subquery Huge subquery
Small outer query
Huge outer query
Either, varies

EXISTS can benefit from indexes on the joining column.

Which is faster - NOT EXISTS or NOT IN?

Note that unlike EXISTS and IN, these two aren't logically the same, produce different resultsets. NULLs in the subquery completely throw NOT IN. For example:

SQL> create table t1 (c1 number) ;

Table created.

SQL> create table t2 (c2 number) ;

Table created.

SQL> insert into t1 values ( 1 ) ;

1 row created.

SQL> insert into t1 values ( 2 ) ;

1 row created.

SQL> insert into t2 values ( 1 ) ;

1 row created.

SQL> select * from t1 where c1 not in ( select c2 from t2 ) ;


SQL> select * from t1 where not exists ( select null from t2 where c1 = c2 ) ;


SQL> insert into t2 values ( null ) ;

1 row created.

SQL> select * from t1 where c1 not in ( select c2 from t2 ) ;

no rows selected

SQL> select * from t1 where not exists ( select null from t2 where c1 = c2 ) ;


SQL> select * from t1 where c1 not in ( select c2 from t2 where c2 is not null ) ;

Hash Anti Joins are very much faster for NOT IN / NOT EXISTS. NOT EXISTS can benefit from indexes on the joining column.
Upsert / Merge

Merge is a SQL command that performs a series of conditional update and/or insert operations. A row is updated if it exists, inserted if it doesn't. Merge was first introduced with Oracle 9i.


MERGE INTO emp e1 USING emp_load e2 ON (e2.empno = e1.empno)
  update set e1.sal = e2.sal
  insert (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  values (e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, e2.deptno);
This is much faster than the old plsql equivalent of checking each row individually, then deciding whether to update it or insert it, one at a time.

You even find developers coding straight inserts and updates individually inside plsql loops. For example:


update emp set emp.sal = ( select emp_load.sal from emp_load where emp.empno=emp_load.empno ) ;
for data in ( select sal from emp_load)
  update emp set emp.sal=data.sal where emp.empno=data.empno ;
end loop;
insert into emp (empno, sal) select empno, sal from emp_load ;
for data in ( select empno,sal from emp_load)
  insert into emp (empno, sal) values data.empno, data.sal ;
  commit ;
end loop;
The placing of the commit in the last example makes it even slower.

In general, aim to code in single operation SQL statements, rather than repeating lots of individual statement executions.

Use CASE (formerly DECODE) for performance

This does the work of five queries in one go:

SELECT last_name, job_id, salary,
    WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN '10%'
    WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
    WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%'
    WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
  END ) Raise
FROM employees;
Case can also be used instead of plsql if-then-else logic.

Before 9i, decode was used for this functionality instead. It does the same as case, but is not as easy to read or debug.

Performance Tuning - Miscellaneous

  1. Hit ratios
  2. sga_target
  3. ORA-4031 unable to allocate %s bytes of shared memory
  4. Checkpoints
  5. Shared pool latches
  6. Cursor sharing / bind variables. Prepared statement forces a hard pass, so prepare once, execute over and over swapping bind variables each time.
  7. Wait events
  8. Overview statspack/awr
  9. Sorts - direct path reads/writes, used for hash also.
  10. Explain plan / autotrace
  11. 10046 trace
  12. tkprof
  13. Catting trace files pre tkprof
  14. Optimizer stats
  15. Block size - dbfmbrc 1m
  16. Migration / chaining
  17. Partitioning - generally no use for performance. But make sure sas big enough to hold them.
  18. Btree indexes
  19. bitmap indexes
  20. reverse key indexes
  21. IOTs
  22. function based indexes.
  23. Full text indexes.
  24. Views (or big complex SQL) instead of complex application logic.
  25. Materialised views
  26. SQL tuning - how to find missing indexes
  27. Why is my index not being used?
  28. Identify slow SQL from v$sql
  29. Identify what SQL is running from v$session-v$sql
  30. Identify wait events from v$session_wait
  31. PLSQL inefficiencies
  32. Context switches
  33. sysdate calls
  34. bulk collects
  35. plsql functions on a column (hub) - cant collect accurate histograms
  36. Lobs/Longs
  37. Nested subqueries
  38. Tuning - database settings
  39. Now not often found as issues. But still have to check.
  40. Low buffer cache - hit ratio
  41. Low shared pool
  42. Low sort area size / pga_aggregate_target - if many disk sorts or I/O on temp tablespaces
  43. Users should use a temporary tablespace
  44. Rollback segments - waits, extends, v$rollstat
  45. Log_buffer - smaller is better
  46. Locks enqueues latches - catblock.sql
  47. Plsql - dbms_application_info - to update module/action in v$session, v$session_longops.
  48. Plsql - go easy on the commits.
  49. Plsql - Use sql blocks where possible
  50. Bind variables - cursor_sharing=similar.
  51. DW Temporary tables
  52. DW Materialised views
  53. DW Processed_flag columns
  54. DW Bitmap indexes
  55. Insert append
  56. Upsert / merge
  57. Parallel Query
  58. shared server / multi threaded server
  59. bulk collect
  60. upgrade hardware
  61. lobs/longs
  62. Buffer pool keep
  63. compress tables and indexes
  64. on line redo logs - should switch at most every 20-30 mins or so, even during peak times. Check with v$archived_log.
  65. missed 1: sequence caches
  66. missed 2: veritas quick i/o
  67. missed 3: trace analyzer


  1. Around 3/4 of the physical memory should be assigned in total to all of oracle
  2. around half of that to SGA, and half to PGA
  3. Before 9i, the SGA had to be sized individually. There were three obligatory components:
  4. Buffer cache - big, most of the sga.
  5. Shared pool - medium.
  6. Log buffer - tiny, less than 1m, preferrably far less than 1m.
  7. And two optional components:
  8. large pool - small, used only for rman and parallel query
  9. java pool - small, used only for java programs (if any).
  10. For data warehouses, the memory split should be around 1/3 to SGA and 2/3 to PGA
  11. For windows servers with virus scanners, only around 2/3 of the physical memory should be assigned to oracle

Performance Tuning - Operating System
  1. Steve Adams on Direct I/O Long story short: set filesystemio_options to 'setall'. Applies from 9iR2 only (was hidden parameter before).
  2. 10gR2 Solaris kernel parameters
  3. 9iR2 Linux kernel parameters
  4. 9iR2 Solaris kernel parameters
  5. nasty Oracle bug with on Solaris that prevents use of direct io, and degrades performance if filesystemio_options=setall
  6. IBM AIX Configuration and Tuning for Oracle (pdf)
  7. IBM Tuning IBM AIX 5L for an Oracle Database (pdf)
  8. Linux Werner Puschitz
  9. Linux Summary

  10. Fs v db buffer cache - veritas quick i/o / veritas storage foundation for oracle
  11. 1m dbfmbrc - but watch optimizer
  12. Server physical memory and cpu's. (/usr/sbin/prtdiag, top, topas, 'sho cpu', mpstat -l)
  13. AIX Virtual Memory Manager
  14. Direct I/O
  15. Linux HugePages

  16. Identify version of unix: uname -a
  17. Solaris memory and cpu: /usr/sbin/prtdiag.
    On old versions, prtdiag could be in a different location, find with "find /usr -name prtdiag 2>/dev/null".
    In zone virtual servers you can get the error "prtdiag can only be run in the global zone" - in that case, top will show the memory and mpstat (or sho cpu) the number of cpus, but I don't know how to find the cpu speed in that case.
  18. Solaris messages: /var/adm/messages
  19. Solaris filesystems: /etc/vfstab
  20. Solaris swap: swap -s
  21. Solaris top: top or prstat or "/usr/ucb/ps aux|head"
  22. Solaris shared memory: ipcs -bm
  23. AIX memory and cpu: : prtconf
  24. AIX memory: prtconf or topas
  25. AIX cpus: prtconf, mpstat (or sho cpu) shows the number of cpus.
  26. AIX messages: errpt -a|more
  27. AIX filesystems: /etc/filesystems
  28. AIX swap: lsps -a
  29. AIX top: topas or "ps aux|head"
  30. AIX shared memory: ipcs -bm
  31. Linux memory: "cat /proc/meminfo" or free or top. Free displays memory in kb.
  32. Linux CPUs: cat /proc/cpuinfo
  33. Linux messages: dmesg
  34. Linux filesystems: /etc/fstab
  35. Linux swap: free
  36. Linux memory details: cat /proc/meminfo
  37. Linux top: top or "ps aux --sort -%cpu | head -20"
  38. Linux shared memory: ipcs -m
  39. All servers: SAN message log is /var/log/sddsrv.log or /var/adm/sddsrv.log
  40. All servers: "mount" shows the currently in play filesystems mount options

Suggested Timetable

  Day 1 Morning     Introduction and overview
  Presentation in pdf or pptx format
  Day 1 Afternoon     10046 trace
  Day 2 Morning     Tkprof
  Top SQL from statspack or v$sql  
  Day 2 Afternoon     SQL tuning
  Day 3 Morning     Initial checks
  The SGA and the PGA
  Miscellaneous tuning
  Day 3 Afternoon     Operating System performance