Jerry Held

1
(No Transcript)
2
Leveraging Oracle Database 10g Performance
Features in the Real World
Session id40244

• Andrew HoldsworthDirector Real World Performance
  
• Server Technologies Oracle Corporation

3
Agenda

• Oracle9i Feedback
• Oracle10g Preparation
• Some Interesting Numbers

4
Oracle9i Feedback

• Over the last year the Real World Performance
  group has been involved in a large number of
  performance related projects
• OLTP and Operational Systems
• Data Warehouse and DSS Systems
• Escalated Customer projects
• Proof of Concept Projects
• Classic RD projects

5
Oracle9i Feedback

• We are seeing to much de-tuning of Oracle
  Databases
• Setting of init.ora parameters to non default
  values without good reason
• With the exception of init.ora parameters that
  set buffer sizes the system defaults are usually
  optimum.
• The impact of excessive meddling can be seen in
  terms of poor optimizer plans, wasted memory, and
  serialization points.
• In summary let the software run in the manner it
  was designed to be ran. Dont try and second
  guess the software.

6
Oracle9i Feedback

• We are seeing far to many projects that attempt
  to use new features because they are there and
  are neglecting design steps.
• Seeing poor data design in terms of table design
• Poor index designs
• Application design errors resulting in
  serialization
• It is our general rule that an initial design
  should focus on the relational basics.
• Only when the general design is robust and the
  performance/administration issues are understood
  should db features be applied e.g. partitioning,
  parallelism, smart indexes, etc.

7
Oracle9i Feedback

• Top 10 Errors
• The documentation of the top 10 errors has meant
  that we are seeing these problems less
• Looking to review the list for 10g documentation

8
Oracle9i Feedback

• 64 Bit Computing misconceptions still exist
• 64 Bit systems do not mean they are faster by
  definition
• 64 Bit systems allow bigger caches and sort areas
• 64 Bit systems have larger working sets and this
  impacts CPU efficiency

9
Oracle9i Feedback

• Use of Parallelism and Partitioning for large
  scale DSS systems is still in the minority
• Not enough parallelism being adopted. Still
  seeing many serial batch jobs often driven by
  nested loop queries
• Use of partitioning is mainly for easing database
  administration issues
• Partitioning should be used for data elimination
  and enhancing hash joins and parallel dml

10
Oracle9i Feedback

• Much Mismanagement of the CBO
• Statistics Abuse common
• Expectations often unrealistic
• CBO often shows up defects in the data design
• Often CBO is being used without anyone knowing !

11
Oracle10g Preparation

• RBO to CBO Migration should have been done by now
• Procrastinate no longer !

12
Oracle10g Preparation

• Some Home Truths about RBO to CBO migration
• It is not a trivial process. Anticipate there
  will be issues involved in the process
• It only takes one statement to be badly optimized
  out of 1000s to ruin your day
• Testing and Validation is key to this process

13
Oracle10g Preparation

• RBO to CBO issues to watch out for
• Gather good statistics on real data and back them
  up
• Watch out for RBO specific SQL e.g. col0 1 or
  col 2
• Be pragmatic some SQL statements are better
  rewritten now
• Look for Data Skew and Cardinality issues

14
Oracle10g Preparation

• The DUAL table becomes a virtual row source
• Eliminates the cache buffer chains latch
  serialization point
• Really good for old applications where select .
  From DUAL is very common
• Can be simulated with xdual in Oracle9i

15
Oracle10g Preparation

• Hash partitioned Indexes
• Eliminates the primary key hotspot in insert
  heavy applications
• Migrates the hot spot to series of warm spots
• Very important for RAC or large SMP
  implementations

16
Oracle10g Preparation

• Lobs have had considerable code path reduction
  activity to make performance similar to LONG RAW
• Design decisions include
• To CACHE or NOCACHE
• Storage inline or not
• Space usage and wastage
• Chunk Size
• Logging

17
Oracle10g Preparation

• LOB issues to watch for
• Inline to Outline Migration
• Cache flooding
• OCI LOB Buffering
• Use of Array Interface

18
Oracle10g Preparation

• Datapump is an new set of utilities built upon
  existing direct path and PQ technology.
• Allows rapid export/import of datasets
• Scales like SQLLoader
• Linear scaling with additional loaders on each
  CPU
• Caveat the I/O subsystem must be able to keep up !

19
Oracle10g Preparation

• PL/SQL Compiler Improvements
• Considerable speed up for optimized and
  non-optimized compilation of Pl/SQL
• Applicable for both interpreted and NCOMP Pl/SQL

20
Oracle10g Preparation

• PL/SQL Compiler Improvements

21
Oracle9i to Oracle10g Some Interesting Numbers
22
Reminder please complete the OracleWorld
online session surveyThank you.
23
(No Transcript)