Performance Tuning for Oracle

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

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).

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 ...
   
   CREATE MATERIALIZED VIEW LOG ON workorder
      WITH ROWID, SEQUENCE (time_id, calendar_year)
      INCLUDING NEW VALUES;
   
   CREATE MATERIALIZED VIEW LOG ON times
      WITH ROWID, SEQUENCE (prod_id)
      INCLUDING NEW VALUES;
   
   CREATE MATERIALIZED VIEW sales_mv
      BUILD IMMEDIATE
      REFRESH FAST ON COMMIT
      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;

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.

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:
   • first time - where answer = ':b1' - :b1='Yes' - then no index FOREVER
   • first time - where answer = ':b1' - :b1='Maybe' - then index ALWAYS

 alter system set db_keep_cache_size=200m scope=both ;
 alter table small_table storage (buffer_pool keep);

 create index myindex on customer(upper(surname)) ;

 select * from customer where upper(surname) = 'FRASER' ;

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 )

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

EXISTS can benefit from indexes on the joining column.

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 ) ;

        C1
----------
         2

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

        C1
----------
         2

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 ) ;

        C1
----------
         2

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

        C1
----------
         2

• With 8i and earlier - hash anti joins are not enabled by default, you must set the always_anti_join parameter to 'hash' first, either at session or database level. Also, the subqueries must exclude nulls.
• With 9i and 10g - hash anti joins are allowed by default, although the subqueries must still exclude nulls.
• With 11g - "null aware hash anti joins" work even with nulls.

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.

Example:

MERGE INTO emp e1 USING emp_load e2 ON (e2.empno = e1.empno)
WHEN MATCHED THEN 
  update set e1.sal = e2.sal
WHEN NOT MATCHED THEN
  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);

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

Fast:

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)
loop
  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)
loop
  insert into emp (empno, sal) values data.empno, data.sal ;
  commit ;
end loop;
/

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

This does the work of five queries in one go:

SELECT last_name, job_id, salary,
  (CASE
    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%'
    ELSE 'NOT APPLICABLE'
  END ) Raise
FROM employees;

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

The SGA

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

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 10.2.0.3 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

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