1 / 23

Leveraging Oracle Database 10g Performance Features in the Real World

Session id:40244. Leveraging Oracle Database 10g Performance Features in the Real World. Andrew Holdsworth Director Real World Performance Server Technologies Oracle Corporation. Agenda. Oracle9i Feedback Oracle10g Preparation Some Interesting Numbers. Oracle9i Feedback.

chastity-dominguez
Download Presentation

Leveraging Oracle Database 10g Performance Features in the Real World

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session id:40244 Leveraging Oracle Database 10g Performance Features in the Real World Andrew HoldsworthDirector Real World Performance Server Technologies Oracle Corporation

  2. Agenda • Oracle9i Feedback • Oracle10g Preparation • Some Interesting Numbers

  3. 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 R&D projects

  4. 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. Don’t try and second guess the software.

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

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

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

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

  9. 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 !

  10. Oracle10g Preparation • RBO to CBO Migration should have been done by now • Procrastinate no longer !

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

  12. 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. col+0 = :1 or col||’’ = :2 • Be pragmatic some SQL statements are better rewritten now • Look for Data Skew and Cardinality issues

  13. 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 x$dual in Oracle9i

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

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

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

  17. 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 SQL*Loader • Linear scaling with additional loaders on each CPU • Caveat the I/O subsystem must be able to keep up !

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

  19. Oracle10g Preparation • PL/SQL Compiler Improvements

  20. Oracle9i to Oracle10g Some Interesting Numbers

  21. Reminder – please complete the OracleWorld online session surveyThank you.

More Related