1 / 31

Managing Cost-Based Optimiser Statistics in PeopleSoft

spence
Download Presentation

Managing Cost-Based Optimiser Statistics in PeopleSoft

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. Managing Cost-Based Optimiser Statistics in PeopleSoft

    2. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 2 Who Am I? Oracle Database Specialist Independent consultant System Performance tuning PeopleSoft ERP Oracle RDBMS Book www.psftdba.com UKOUG PeopleSoft Technology SIG Committee

    3. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 3 If you are an Oracle DBA Next Week In Birmingham 3-days

    4. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 4 Agenda Cost Based Optimiser Statistics Permanent Tables Database/Schema-Wide Maintenance Interim Tables Working Storage/Temporary/Reporting Populated by Batch Statistics Managed by Batch I am going to talk about Oracle databases The principles apply to all databases The specifics are different

    5. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 5 Cost Based Optimiser A mathematical model Given a SQL statement Uses volumetric statistics Determines optimal execution plan Like predicting the weather Doesnt always produce the best answer Deficiencies in the model Inaccuracies in data (statistics)

    6. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 6 What do you mean by optimal? An execution plan has a cost That cost is an estimate of how long the query will take. In practice that may not be the best execution plan. The Optimiser might not know enough about the data. Skew Correlation

    7. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 7 What do you mean correlation? Imagine table of people Attributes include: Star sign, Month of Birth How many people born in August? 1 in 12 How many people are Leos? 1 in 12 How many Leos born in August? 1 in 144? No. 1 in 16, We know that data is correlated

    8. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 8 What do you mean correlation? Example from HR. PS_JOB (emplid VARCHAR2(11) ,effdt DATE ) From a real system 931012 rows 142224 EMPLIDs 7456 EFFDTs There is one day with 77985 rows! 77985 on day when a salary review was processed Migration gives you a long historical tail Some businesses put future effective dated records 7456 days is 20 years, but actual range over 45 years77985 on day when a salary review was processed Migration gives you a long historical tail Some businesses put future effective dated records 7456 days is 20 years, but actual range over 45 years

    9. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 9 General Principles Tell the database the truth about the objects it is working on. At least enough of the truth for the Optimiser to make the right decision System Statistics reflect performance of disks Object Statistics reasonably up to date Histograms to describe skew of individual columns. Oracle 11g will build histograms on combinations of columns

    10. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 10 Corner Cases There will be times when that is not enough Hints (which are directives) And in extreme cases Manually adjust statistics Tuning by Cardinality Feedback (Wolfgang Breitling) http://www.centrexcc.com/papers.html

    11. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 11 System Statistics Introduced in Oracle 9i Not automatically collected In which case arbitrary defaults used Tells Oracle about CPU Speed Cost of code path Single v- Multi-block Disk Speed Relative cost of index v- scan

    12. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 12 By default, how does Oracle maintain statistics? Maintenance Window New in Oracle 10g Previously you had to define your own job Default 10pm 6am weekdays + weekends Refresh stale object statistics Stale means >10% change Table monitoring enabled by default user_tab_statistics dbms_stats.flush_database_monitoring_info Automatic Histogram Collection dbms_stats.flush_database_monitoring_info user_tab_statistics dbms_stats.flush_database_monitoring_info user_tab_statistics

    13. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 13 Histograms? Statistics include minimum and maximum value for every column Optimiser assumes values evenly distributed in that range. Describe non-uniformities in data distributions Frequency v- Height Balanced Up to 254 buckets Parse Overhead More input data, more calculations

    14. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 14 Maintaining Statistics What usually happens! Leave to to default behaviour We keep the statistics up to date to maintain system stability I sometimes see Legacy 9i scripts, that collect stats on everything at high sample size. My Advice 90% of time, default is fine Rather like traction control on a car Take control, at least for tables where you have had to resolve a problem

    15. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 15 Taking Control Techniques: Lock Statistics Delete Statistics Optimiser Dynamic Sampling Modifying PeopleSoft Behaviour

    16. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 16 Locking Statistics New in Oracle 10g Table (and indexes) omitted from database/schema-wide statistics operations dbms_stats.gather_table_stats & ANALYZE commands generate an error when stats are locked dbms_stats has force=>TRUE

    17. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 17 Optimiser Dynamic Sampling Extra statistics collected on-the-fly at parse time. Usually for when there are no object statistics Useful for when object statistics may lead to errors in estimation of selectivity. Recommendation: Set to Level 4 default is 2 Enabled for tables with 2 column predicates This helps where columns correlated I have found setting ODS to level 4 beneficial. It isnt a magic bullet, but it is helpful.I have found setting ODS to level 4 beneficial. It isnt a magic bullet, but it is helpful.

    18. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 18 Working Storage/Reporting Tables In an ideal world Clear the table %TruncateTable() Populate table Gather statistics %UpdateStats() Use tables

    19. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 19 %UpdateStats Application Engine macro Invokes DDL Model Dynamically defined in PSDDLMODEL So you can change this Used to be ANALYZE TABLE From PT8.48 delivered with dbms_stats.gather_table_stats http://blog.psftdba.com/2007/05/changes-to-ddl-models-in-peopletools.html

    20. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 20 DDL Model Problems (1) %UpdateStats(record name ,[HIGH/LOW]) Low DDL Model specifies 1% sample High dbms_stats.auto_sample_size Dynamically calculated by Oracle often less than 1%

    21. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 21 DDL Model Problems (2) %UpdateStats(record name ,[HIGH/LOW]) Low FOR ALL COLUMNS SIZE 1 Removes all histograms High FOR ALL INDEXED COLUMNS SIZE 1 Removes histograms from indexed columns Leaves histograms and min/max values on unindexed column values unchanged.

    22. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 22 %UpdateStats A good idea, but a blunt instrument. Replace it with custom PL/SQL package wrapper.ps_stats() If statistics locked suppress gather Doesnt analyze Global Temporary Tables Refreshes stale statistics on partitioned tables Meta-data driven to control behaviour per record to override defaults http://blog.psftdba.com/2009/06/controlling-how-updatestats-collects.html

    23. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 23 COBOL %UpdateStats Expansion hard coded in COBOL Still produces ANALYZE in PT8.49 GP Cobol uses stored statements STORE GPPSERVC_U_STATH %UPDATESTATS(PS_GP_PYE_HIST_WRK); It is possible to recode statements to call wrapper STORE GPPSERVC_U_STATH BEGIN wrapper.ps_stats(p_ownname=>user,p_tabname=>'PS_GP_PYE_HIST_WRK'); END;;

    24. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 24 PeopleSoft Temporary Records Permanent Database Tables used for Temporary Working Storage Application Engine automatically clears the data out of the table. Truncate or Delete Neither invalidate statistics. Statistics will reflect the previous data No point including these tables in maintenance window Statistics gathered will always be out of date.

    25. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 25 Statistics on Temporary Records Delete and lock Statistics To omit them from schema/database-wide maintenance activities Optimiser Dynamic Sampling So you dont need to explicitly collect statistics But you will sometimes! %UpdateStats Or Process needs to refresh statistics Use wrapper.ps_stats() otherwise ORA-20005

    26. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 26 Deleting Statistics in Application Engine Programs Delete statistics on temporary tables on allocation to Application Engine instance Trigger on PS_AETEMPTBLMGR Ensures that you dont use statistics collected by previous Application Engine With different process instance number http://blog.psftdba.com/2009/04/statistics-management-for-peoplesoft.html Tables keyed by PI. Min/Max columns values reflect previous PI always Query predicates on PI Oracle will assume no rows match. Miscosts cardinalityTables keyed by PI. Min/Max columns values reflect previous PI always Query predicates on PI Oracle will assume no rows match. Miscosts cardinality

    27. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 27 Oracle 10g Statistics Retention By default Oracle 10g retains a history of object statistics for 31 days. Global setting, not per object or schema dbms_stats writes previous values to SYS.WRI$_OPTSTAT%HISTORY tables Lots of concurrent batches with lots of %UpdateStats? you could have a locking problem 31 days after go live. http://blog.psftdba.com/2009/06/oracle-10g-statistics-history-retention.html You might choose to disable statistics retention. Retention is a good idea for restoring previous versions of stats should an execution plan suddenly change for the worse because the stats have changed. I think dbms_stats is also responsible for clearing the history. That means concurrent dbms_stats can lock each other out.Retention is a good idea for restoring previous versions of stats should an execution plan suddenly change for the worse because the stats have changed. I think dbms_stats is also responsible for clearing the history. That means concurrent dbms_stats can lock each other out.

    28. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 28 Statistics on Partitioned Tables Partitioning is a strategy that is usually adopted for very large tables. Gathering Statistics can be time consuming Have to update partition-level statistics on every partition Then calculate table-level statistics.

    29. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 29 Time-based Partitioning Annual/Monthly/Weekly/Periodic partitions Historical Partitions havent changed Future partitions created in advance are still empty GATHER STALE only available for database/schema-wide operations http://blog.psftdba.com/2009/04/statistics-management-for-partitioned.html Wrapper package can refresh stale partitions only. On a T&L project I partitioned a table where PeopleSoft delivered an %UpdateStats on that table. So it was obviously important to refresh the statsOn a T&L project I partitioned a table where PeopleSoft delivered an %UpdateStats on that table. So it was obviously important to refresh the stats

    30. Managing Cost-Based Optimiser Statistics in PeopleSoft 2009 www.go-faster.co.uk 30 Conclusion Whatever you decide to do about statistics, make sure its your decision. Defaults may not be the right decision all of the time. My suggestions in this presentation are all in production on a large system. The wrapper package is my solution to series of problems

    31. Questions?

    32. Managing Cost-Based Optimiser Statistics in PeopleSoft

More Related