Implications of Setting Oracle9iR2’s Statistical Collection Level James F. Koopmann Co-Founder & Chief Architect

1. Implications of Setting Oracle9iR2’s Statistical Collection Level James F. Koopmann Co-Founder & Chief Architect dbDoctor Inc. Colorado www.dbdoctor.net Jkoopmann@dbdoctor.net james_koopmann@yahoo.com

2. Content 1. Why statistics are important, Who Needs them ? 2. What is the new method of setting up statistical collections. 3. How you can see what your settings are currently 4. How you can change your settings. 5. A test scenario for determining system impact of collecting statistics. 6. SPFILE Usage 7. Various Examples 8. Information exchange

3. Why Do We Care About Statistics, Who Needs Them TRUE or FALSE • Collecting Statistics • will • make my • database run faster

4. Why Do We Care About Statistics, Who Needs Them TRUE or FALSE • Collecting Statistics • will in the future • make my • database run faster

5. Why Do We Care About Statistics, Who Needs Them • It’s All About Problem Detection • What Are Problems? • Problems are anything that causes us, or our database discomfort. • Problems are anything that occurs in relation to the database that is a deviation from the norm. • A few questions to ask yourself • If a batch job runs one hour longer than normal, is it a problem if no one knows about it? • If you take down the listener and no one tries to connect to your database, is it a problem? • If the database is down and isn’t being used, is it a problem?

6. Why Do We Care About Statistics, Who Needs Them It’s All About Problem Detection Lazy man method of finding problems a. You can find problems by snooping around in the database b. You can find problems from user complaints c. You can find problems when the system crashes or is unavailable d. Use of rudimentary statistics (wall clock, system feel) Intelligent method of finding problems a. Realizes that aggregate statistics aren’t enough b. Has a method of detecting change, where have I been and where am I going. c. Hard and fast statistics (V$ tables, iostat, vmstat, …).

7. Why Do We Care About Statistics, Who Needs Them • The Problem with Problems • We have to fix them • Mistakes are common and we can’t rid ourselves of them • Be compassionate • Be humble • Receive new ideas with an open but analytical mind • Research for yourself • Have reliable sources • 4. Validate what you have heard or read • don’t take for granted everything you read or hear • “Your mileage may vary” • 5. Accept only after validation • This is the best part, you can count on what you have learned • You can use new knowledge to validate other scenarios

8. New to Oracle9iR2 STATISTICS_LEVEL Controls Collection of Advisories and Statistics • Shared Pool Advice • Buffer Cache Advice • PGA Advice • MTTR Advice • Segment Level Statistics • Timed Statistics • Timed OS Statistics • Plan Execution Statistics

9. New to Oracle9iR2 STATISTICS_LEVEL is a Dynamic Parameter DYNAMIC Parameter can be modified using the ALTER SESSION or ALTER SYSTEM statement while an instance is running. Syntax we are all familiar with ALTER SESSION SET parameter_name = value Change the value of a parameter for the duration of the session. The value of this parameter does not change for other sessions in the instance. ALTER SYSTEM SET parameter_name = value Change the value of the parameter for all current and future sessions. Record the change in the alert.log ALTER SYSTEM SET parameter_name = value [DEFERRED] Change only applies to future sessions that will connect to the database. Record the change in the alert.log

10. New to Oracle9iR2 V$STATISTICS_LEVEL Column Values(?) Description STATISTICS_NAME Name of the statistics/advisory. DESCRIPTION Description of what the statistics/advisory does and what it can be used for. SESSION_STATUS ENABLED|DISABLED Status of the statistics/advisory for this session. SYSTEM_STATUS ENABLED|DISABLED Status of the statistics/advisory system-wide. ACTIVATION_LEVEL BASIC|TYPICAL|ALL Indicates the level of STATISTICS_LEVEL than enable this statistics/advisory STATISTICS_VIEW_NAME If there is a single view externalizing this statistics/advisory, the name of that view.If there is no such a view, this column is empty. If there are multiple views involved, the DESCRIPTION column should mention the view names. SESSION_SETTABLE YES|NO. Whether this statistics/advisory can be set at the session level.

11. New to Oracle9iR2 • SQL> SELECT statistics_name, statistics_view_name, description FROM v$statistics_level V$STATISTICS_LEVEL STATISTICS_NAME STATISTICS_VIEW_NAME DESCRIPTION ------------------------- --------------------- ---------------------------------------- Shared Pool Advice V$SHARED_POOL_ADVICE Predicts the impact of different values of shared_pool_size on elapsed parse time saved PGA Advice V$PGA_TARGET_ADVICE Predicts the impact of different values of pga_aggregate_target on the performance of memory intensive SQL operators Segment Level Statistics V$SEGSTAT Enables gathering of segment access statistics Timed OS Statistics Enables gathering of timed operating system statistics Buffer Cache Advice V$DB_CACHE_ADVICE Predicts the impact of different cache sizes on number of physical reads MTTR Advice V$MTTR_TARGET_ADVICE Predicts the impact of different MTTR settings on number of physical I/Os Timed Statistics Enables gathering of timed statistics Plan Execution Statistics V$SQL_PLAN_STATISTICS Enables collection of plan execution statistics

12. SPFILE Is the Init.ora File Going Away ? Simplified Parameter Management Change in STARTUP Sequence Push to Migrate from Parameter File

13. SPFILE DO You Have One? SQL> show parameter spfile NAME TYPE VALUE --------------------------- --------- -------------- spfile string

14. SPFILE How Do I Get One? Help, I Want My INIT.ORA

15. SPFILE Items to Consider Must be SYSOPER or SYSDBA to create one Occasionally save you SPFILE SPFILE is a binary file SHUTDOWN / STARTUP to recognize ORA-12547: TNS:lost contact ALTER SYSTEM RECOGNIZE command

16. SPFILE Setting a Parameter ALTER SYSTEM SET • DEFERRED, changes the value for the parameter for sessions connecting after the statement is issued. • SCOPE, specifies when the change will take effect. • MEMORY - the change takes effect immediately but is not available after next startup • SPFILE - the change is made in the server parameter file only and available after next startup • BOTH - MEMORY + SPFILE, this is the default

17. SPFILE Resetting a Parameter ALTER SYSTEM RESET Switch to default statistics_level alter system set statistics_level=typical scope=memory; alter system reset statistics_level scope=spfile sid=‘*’; Switch timed_statistics to be under statistics_level control alter system set timed_statistics=true scope=memory; alter system reset timed_statistics scope=spfile sid=‘*’;

18. SPFILE Setting a Parameter that is a List of Strings alter system set control_files= '/u01/app/oracle/oradata/saigon/control01.ctl', '/u01/app/oracle/oradata/saigon/control02.ctl', '/u01/app/oracle/oradata/saigon/control03.ctl' scope=spfile;

19. STATISTICS_LEVEL Dynamic Parameters

20. STATISTICS_LEVEL Three SettingsOverride

21. Take a Look at Your Settings Override Control (V$PARAMETER) TRUE = Default Value (NO SPFILE entry) FALSE = SPFILE entry SQL> SELECT name, value, isses_modifiable, issys_modifiable,isdefault,ismodified FROM v$parameter WHERE NAME IN ('statistics_level','db_cache_advice', 'timed_statistics', 'timed_os_statistics'); NAME VALUE ISSES_MODIFIABLE ISSYS_MODIFIABLE ISDEFAULT ISMODIFIED ---------------------- ------- ---------------- ---------------- --------- --------- timed_statistics FALSE TRUE IMMEDIATE TRUE FALSE timed_os_statistics 0 TRUE IMMEDIATE TRUE FALSE db_cache_advice OFF FALSE IMMEDIATE TRUE FALSE statistics_level TYPICAL TRUE IMMEDIATE TRUE FALSE FALSE = Not Modified since startup MODIFIED = ALTER SESSION SYSTEM_MOD = ALTER SYSTEM

22. Take a Look at Your Settings Override Control (V$STATISTICS_LEVEL) What Level in STATISTICS_LEVEL to Activate Am I Collecting Am I Collecting STATISTICS SESSION SYSTEM ACTIVATION SESSION NAME STATUS STATUS LEVEL SETTABLE ------------------------- -------- -------- ---------- -------- Buffer Cache Advice ENABLED ENABLED TYPICAL NO MTTR Advice ENABLED ENABLED TYPICAL NO PGA Advice ENABLED ENABLED TYPICAL NO Segment Level Statistics ENABLED ENABLED TYPICAL NO Shared Pool Advice ENABLED ENABLED TYPICAL NO Timed Statistics ENABLED ENABLED TYPICAL YES Plan Execution Statistics DISABLED DISABLED ALL YES Timed OS Statistics DISABLED DISABLED ALL YES SQL> SELECT statistics_name, session_status, system _status activation_level, session_settable FROM v$statistics_level

23. Take a Look at Your Settings Don’t Get Confused If there is an entry in SPFILE then setting STATISTICS_LEVEL will not have an impact on that statistic or advisory. Even if the setting in the SPFILE is the DEFAULT, it will still not be under the control of the STATISTICS_LEVEL parameter. When looking at v$parameter and v$statistics_level after you have made changes, you may get confused. ALTER SYSTEM command will override any ALTER SESSION command RESET any parameters that you want to be controlled by the new STATISTICS_LEVEL parameter.

24. Take a Look at Your Settings Oracle Gets Confused NAME VALUE ISSES_MODIFIABLE ISSYS_MODIFIABLE ISDEFAULT ISMODIFIED ---------------------- ------- ---------------- ---------------- --------- --------- timed_os_statistics 10 TRUE IMMEDIATE FALSE FALSE STATISTICS SESSION SYSTEM ACTIVATION SESSION NAME STATUS STATUS LEVEL SETTABLE ------------------------- -------- --------- ---------- -------- Timed OS Statistics UNKNOWN UNKNOWN ALL YES The setting of timed_os_statistics to anything other than 0 (zero) or 5 will switch the SESSION_STATUS & SYSTEM_STATUS in V$STATISTICS_LEVEL to UNKNOWN

25. Human Impact The Most Hated Statements • You must set TIMED_STATISTICS to TRUE in the parameter file. • I recommend using timed statistics. • Doing this will have a small negative effect on system performance. • You will incur minimal resource overhead • You cannot afford to be without the information that timed statistics provides. • Gathering OS statistics is very expensive.

26. Human Impact Typical Responses • Sorry, Oracle Support does not have any specific benchmark information. • Contact ___________, they may have benchmark information. • Can anyone else share their input and experiences. • My Technical Consultant has not seen any specific benchmarks on this. • Take a look at the guide and if necessary we will try to get some development resources

27. System Impact Test Scenarios Why • Reduce stress associated with statistical collection • Who else will help • Validate for myself

28. System Impact Basic Terminology Throughput User / Job Request (workload) (v$sysstat) User Decides What to Do Next User Queues Next Transaction Database Completes Request LGWR wait for redo copy, log file sync, db file sequential read,… Sorts, Reads, Writes, Enqueues, Redo activity, Buffer cache activity, Parsing,… Coffee Break Think Time Resource Usage (v$sysstat) Wait Times (v$system_event) Database Response Time

29. System Impact Test Scenarios (workload types) execute count, Total number of calls (user and recursive) that executed SQL statements calls to get snapshot scn: kcmgss, Number of times a snapshot system change number (SCN) was allocated. The SCN is allocated at the start of a transaction. session logical reads, The sum of db_block_gets and consistent_gets. db block gets, Number of times a CURRENT block was requested consistent gets, Number of times a consistent read was requested for a block. db block changes, This statistic counts the total number of changes that were part of an update or delete operation that were made to all blocks in the SGA. This approximates total database work. It statistic indicates the rate at which buffers are being dirtied. user commits, Number of user commits. When a user commits a transaction, the redo generated that reflects the changes made to database blocks must be written to disk. Commits often represent the closest thing to a user transaction rate.

30. System Impact Test Scenarios (workload types)

31. System Impact Test Scenarios (wait types)

32. System Impact Test Scenarios (resource types)

33. System Impact More Data Give Me a Call file:///C:/temp/Vsysstat.htm file:///C:/temp/Vsysstat_xls.htm file:///C:/oradoc/920doc/server.920/a96536/apc2.htm

34. Buffer Cache Advisory Defined • Why: Disk is slow, memory is fast • What: Use to size the buffer caches • When: Performance is bad • Where: Populates V$DB_CACHE_ADVICE for each buffer cache in use (2K, 4K, 8K, 16K, 32K, KEEP, RECYCLE) • How: db_cache_advice / statistics_level

35. Buffer Cache Advisory A Tried and True Friend • Calculate Buffer Cache Hit Ratio • SQL> SELECT name, block_size, • 100*(1 - (physical_reads / (db_block_gets+consistent_gets))) buffhit • FROM v$buffer_pool_statistics; • How we size the buffer cache • Just issue the SQL to see the hit ratio • Got smarter and diff’d between a start and end time • Run a valid workload through system • Come up with a ballpark estimate on size • Increase buffer cache if hit ratio is low • Done if hit ratio is high

36. Buffer Cache Advisory Diff’d Buffer Cache Hit Ratio • SQL> SELECT b.name, b.block_size, • 100*(1 - ((e.physical_reads-b.physical_reads) / • ((e.db_block_gets-b.db_block_gets)+ • (e.consistent_gets-b.consistent_gets)))) buffhit • FROM beg_buffer_pool_statistics b, end_buffer_pool_statistics e • WHERE b.name=e.name AND b.block_size=e.block_size; • How to diff • Create table beg_buffer_pool_statistics as select * from v$buffer_pool_statistics • Run workload through system • Create table end_buffer_pool_statistics as select * from v$buffer_pool_statistics • Issue above SQL

37. Buffer Cache Advisory Use of V$DB_CACHE_ADVICE • SQL> SELECT name, block_size, size_for_estimate, • estd_physical_read_factor, estd_physical_reads • FROM V$DB_CACHE_ADVICE • WHERE advice_status = 'ON'; • How to get the statistics • Set db_cache_advice to ‘READY’ • Set db_cache_advice to ‘ON’ • Run a valid workload through system • Issue above SQL

38. Buffer Cache Advisory Output of V$DB_CACHE_ADVICE BLOCK Cache Estd Phys Estd Phys NAME SIZE Size Read Factor Reads -------- ----- ----- ----------- ---------- DEFAULT 8192 48 2.1133 343669940 DEFAULT 8192 96 1.7266 280783364 DEFAULT 8192 144 1.4763 240091867 DEFAULT 8192 192 1.3573 220733606 DEFAULT 8192 240 1.2801 208181172 DEFAULT 8192 288 1.2165 197842182 DEFAULT 8192 336 1.1686 190052917 DEFAULT 8192 384 1.1202 182180544 DEFAULT 8192 432 1.0877 176884743 DEFAULT 8192 480 1.0602 172420984 DEFAULT 8192 528 1.0196 165812231 DEFAULT 8192 544 1 162626093 DEFAULT 8192 576 .9765 158797352 DEFAULT 8192 624 .9392 152735392 DEFAULT 8192 672 .9216 149879874 DEFAULT 8192 720 .9013 146571255 DEFAULT 8192 768 .885 143928671 DEFAULT 8192 816 .8726 141908868 DEFAULT 8192 864 .8607 139972381 DEFAULT 8192 912 .8492 138098490 DEFAULT 8192 960 .8277 134610328 • Its all about reads • Don’t reduce the size of your cache if you are going to incur more physical reads • Don’t increase the size of your cache if you are not going to reduce the number of reads DEFAULT 8192 544 1.00 162626093 CURRENT

39. Comments Concerns Questions Answers Dos Don’ts &

40. Implications of Setting Oracle9iR2’s Statistical Collection Level James F. Koopmann dbDoctor Inc. Colorado www.dbdoctor.net Jkoopmann@dbdoctor.net james_koopmann@yahoo.com