Applications Database Optimization Panel

1
Applications Database Optimization Panel

• Wolfgang Breitling, Mike Brown, Cary Millsap,
  Sandra Vucinic, and Mark W. Farnham (moderator)

2
Wolfgang Breitling

• Independent consultant since 1996 specializing
  in Oracle and Peoplesoft setup, administration,
  and performance tuning
• Mathematics major from University of Stuttgart
• 25 years in database managementDL/1, IMS,
  ADABAS, SQL/DS, DB2, Oracle
• OCP certified DBA - 7, 8, 8i, 9i
• Oracle since 1993 (7.0.12),
• Member of the Oaktable Network
• breitliw_at_centrexcc.com

Centrex Consulting Corp.
3
Wolfgang Breitling

• breitliw_at_centrexcc.com
• Centrex Consulting Corp.
• www.centrexcc.com

4
Michael Brown, Colibri Limited
mbrown_at_colibrilimited.com

• Working with Oracle Database since 1994 (7.0.13)
• Working with EBS since 1997 (10.7 SC)
• On the board of the Database SIG since 2001
• DBSIG Chairman 2002-2006

5
Cary Millsap / Hotsos Enterprises, Ltd.

• Hotsos 1999present
• Co-founder, instructor, writer, developer,
  consultant
• Author Optimizing Oracle Performance with Jeff
  Holt
• Oracle Magazine Author of the Year 2004
• Co-author Oracle Insights Tales of the Oak Table
• Oracle Corporation 19891999
• VP System Performance Group
• Global Service Line leader
• Consultant at over 100 sites
• OFA inventor

6
Cary Millsap / Hotsos Enterprises, Ltd.

• Fill in the book signing schedule when we know it.

7
Sandra Vucinic

• Over 12 years of experience with Oracle database,
  applications, development and administration
  tools
• Project roles Architect, Oracle Apps DBA,
  Project Lead
• Director, OAUG Database SIG Board
• Director, OAUG SysAdmin SIG and Committee Chair,
  Oracle Tools and Utilities
• Director, OAUG Middleware SIG Board
• Director, SROAUG Board
• Presented at OAUG, NCOAUG, CSOAUG, SROAUG, Apps
  World and Open World conferences (40 sessions)
• Founded VLAD Group, Inc. in March, 2001

8
Mark W. Farnham

• DaTaSyS, circa 1977
• Oracle RDBMS circa 1985, V4?, SQL/DS
• EBS MPL 3.2, Oracle V5.x, January 1989
• EBS 4.0.4, Oracle V6.0.x, July 4, 1989
• Co-founding director of OAUG, May 1990
• President OAUG 1992-1994
• Member, Oracle VLDB and MOSES
• Co-inventor, US patent number 6,035,306

www.rsiz.com
9
Format Questions and Tips

• Well start with a tip from Wolfgang
• After that either you ask questions or I put the
  panel on the spot

10
Gathering Object Statistics 9i

• Determine which Tables need new
  StatisticsSimplest is to use some staleness
  criteria
• gather_table_stats, estimate dependent on table
  size (can be as low as 0.01)method_opt for all
  columns size 1 ? no histograms on
  everythingcascade false ? unless estimate100
• if num_rows0 delete the statistics
• else gather index stats on the tables indexes
  with est100and set optimizer_dynamic_sampling2
  
• Gather histograms on special columns with
  est100
• Other special cases