Transcript SOM Sponsors: ORACLE STATISTICS GATHERING STRATEGY & VERSION CONTROL By: Yury Velikanov (Pythian) & All of you.

SOM Sponsors:
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
- Application
- Statistics
- Data
SOM Sponsors:
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
SOM Sponsors:
How often ?
•Never
•Each 24 hours
•Each SQL run
•Weekly/Monthly/Yearly
•AUTO MAGICALLY :)
•Important!
SOM Sponsors:
What % gather statistics ?
•100% all the time
•10% all the time
•AUTO
SOM Sponsors:
Should we lock statistics
•staging tables
•temporary tables
•tables that we don’t want application to gather
statistics itself
SOM Sponsors:
Stats and Huge Tables
•Partitioning
- Could copying statistics from one older to new partition
•Huge table
- Why we should gather statistics at all :)
SOM Sponsors:
Statistics gathering performance
•Parallel option!
•Memory settings!
•Do not gather stats there where we do not need it
SOM Sponsors:
Universal statistics gathering
•Statistics gathering time windows and related
techniques
•Automatic statistics gathering (pros/cons)
SOM Sponsors:
Statistics gathering features in new
Oracle RDBMS versions (11GR2)
•http://www.articles.freemegazone.com/11genhanced-optimizer-statistics-maintenance.php
•Setting STALE_PERCENT per Object
•Pending Statistics (gather but not publish)
•Extended Statistics (multi columns)
SOM Sponsors:
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
SOM Sponsors:
Do we still need to use analyse
command as alternative to
dbms_stats?
SOM Sponsors:
Stats and DEV/TEST/UAT
environment. How to manage
those?
SOM Sponsors:
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
SOM Sponsors: