1 / 14

Oracle Statistics gathering strategy & version control

Oracle Statistics gathering strategy & version control. By: Yury Velikanov (Pythian) & All of you. Problem statement. The most dangerous issue is … Dramatically changed performance What change performance? Application’s changes Statistics changes Data changes Version control

mackenzie-donaldson
Download Presentation

Oracle Statistics gathering strategy & version control

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. Oracle Statistics gathering strategy & version control By: Yury Velikanov (Pythian) & All of you

  2. Problem statement • The most dangerous issue is … • Dramatically changed performance • What change performance? • Application’s changes • Statistics changes • Data changes • Version control • Application • Statistics • Data

  3. Oracle Statistics gathering strategy • How often do we need to gather statistics (if we need to gather it at all) • What percentage should be used to gather statistics • Should we lock statistics for any type of objects (staging tables, temporary tables etc) • Partitioning and copying statistics from one partition to others • Statistics gathering time windows and related techniques • Automatic statistics gathering (pros/cons) • Statistics gathering features in new Oracle RDBMS versions (11GR2) • Statistics gathering and version control • Do we still need to use analyse command as alternative to dbms_stats? • Stats and DEV/TEST/UAT environment. How to manage those? • Statistics gathering performance

  4. How often ? • Never • Each 24 hours • Each SQL run • Weekly/Monthly/Yearly • AUTO MAGICALLY :) • Important!

  5. What % gather statistics ? • 100% all the time • 10% all the time • AUTO

  6. Should we lock statistics • staging tables • temporary tables • tables that we don’t want application to gather statistics itself

  7. Stats and Huge Tables • Partitioning • Could copying statistics from one older to new partition • Huge table • Why we should gather statistics at all :)

  8. Statistics gathering performance • Parallel option! • Memory settings! • Do not gather stats there where we do not need it

  9. Universal statistics gathering • Statistics gathering time windows and related techniques • Automatic statistics gathering (pros/cons)

  10. Statistics gathering features in new Oracle RDBMS versions (11GR2) • http://www.articles.freemegazone.com/11g-enhanced-optimizer-statistics-maintenance.php • Setting STALE_PERCENT per Object • Pending Statistics (gather but not publish) • Extended Statistics (multi columns)

  11. Statistics & version control • This is close to perfect strategy • It doesn't address the problem for 100% • There are always changes in the application • There are always changes in the date

  12. Do we still need to use analyse command as alternative to dbms_stats?

  13. Stats and DEV/TEST/UAT environment. How to manage those?

  14. Best practices • Save previous statistics before gathering a new one • Gather statistics rare as possible • Manage exceptions • Lock statistics • Test changes in the statistics gathering approuch

More Related