Implications

1

• Implications
• of Setting
• Oracle9iR2s
• Statistical Collection Level
• James F. Koopmann
• Co-Founder Chief Architect
• dbDoctor Inc.
• Colorado
• www.dbdoctor.net
• Jkoopmann_at_dbdoctor.net
• james_koopmann_at_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

• Its 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 isnt being used, is
  it a problem?

6
Why Do We Care About Statistics, Who Needs Them
Its 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 arent 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 cant 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
• dont 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

1. Shared Pool Advice
2. Buffer Cache Advice
3. PGA Advice
4. MTTR Advice
5. Segment Level Statistics
6. Timed Statistics
7. Timed OS Statistics
8. 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
VSTATISTICS_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 ENABLEDDISABLED Status of
the statistics/advisory for this
session. SYSTEM_STATUS ENABLEDDISABLED Status
of the statistics/advisory system-wide. ACTIVATIO
N_LEVEL BASICTYPICALALL 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_SETTAB
LE YESNO. Whether this statistics/advisory can
be set at the session level.
11
New to Oracle9iR2

• SQLgt SELECT statistics_name, statistics_view_name
  , description FROM vstatistics_level

VSTATISTICS_LEVEL
STATISTICS_NAME STATISTICS_VIEW_NAME
DESCRIPTION -------------------------
--------------------- ----------------------------
------------ Shared Pool Advice
VSHARED_POOL_ADVICE Predicts the impact of
different values of
shared_pool_size on elapsed
parse time saved PGA Advice
VPGA_TARGET_ADVICE Predicts the impact of
different values of
pga_aggregate_target on the
performance of
memory intensive SQL
operators Segment Level Statistics VSEGSTAT
Enables gathering of segment access
statistics Timed OS Statistics
Enables gathering of timed operating
system
statistics
Buffer Cache Advice VDB_CACHE_ADVICE
Predicts the impact of different cache sizes on

number of physical reads
MTTR Advice VMTTR_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 VSQL_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?

• SQLgt 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 TNSlost 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_leveltypical
scopememory alter system reset
statistics_level scopespfile sid Switch
timed_statistics to be under statistics_level
control alter system set timed_statisticstrue
scopememory alter system reset
timed_statistics scopespfile sid
18
SPFILE
Setting a Parameter that is a List of Strings
alter system set control_files '/u01/app/oracl
e/oradata/saigon/control01.ctl', '/u01/app/oracle
/oradata/saigon/control02.ctl', '/u01/app/oracle/
oradata/saigon/control03.ctl' scopespfile
19
STATISTICS_LEVEL
Dynamic Parameters
Parameter ALTER SESSION ALTER SYSTEM ALTER SYSTEM DEFERRED
STATISTICS_LEVEL
Db_cache_advice
Timed_statistics
Timed_os_statistics
20
STATISTICS_LEVEL
Three Settings Override

Statistic / Advisory BASIC TYPICAL ALL SESSION SPFILE
Shared Pool Advice
Buffer Cache Advice db_cache_advice
PGA Advice
MTTR Advice
Segment Level Statistics
Timed Statistics timed_statistics
Timed OS Statistics timed_os_statistics
Plan Execution Statistics
21
Take a Look at Your Settings

• SQLgt SELECT name, value, isses_modifiable,
• issys_modifiable,isdefaul
  t,ismodified
• FROM vparameter
• WHERE NAME IN ('statistics_level','db_ca
  che_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

Override Control (VPARAMETER)
TRUE Default Value (NO SPFILE
entry) FALSE SPFILE entry

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

• SQLgt SELECT statistics_name, session_status,
  system _status
• activation_level,
  session_settable
• FROM vstatistics_level

Override Control (VSTATISTICS_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
23
Take a Look at Your Settings

• 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 vparameter and
  vstatistics_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.

Dont Get Confused
24
Take a Look at Your Settings

• The setting of timed_os_statistics to anything
  other than 0 (zero) or 5 will switch the
  SESSION_STATUS SYSTEM_STATUS in
  VSTATISTICS_LEVEL to UNKNOWN

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
25
Human Impact
The Most Hated Statements

1. You must set TIMED_STATISTICS to TRUE in
   the parameter file.
2. I recommend using timed statistics.
3. Doing this will have a small negative effect
   on system performance.
4. You will incur minimal resource overhead
5. You cannot afford to be without the information
   that timed statistics provides.
6. Gathering OS statistics is very expensive.

26
Human Impact
Typical Responses

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

27
System Impact
Test Scenarios
Why

1. Reduce stress associated with statistical
   collection
2. Who else will help
3. Validate for myself

Statistic / Advisory BASIC TYPICAL ALL
Shared Pool Advice
Buffer Cache Advice
PGA Advice
MTTR Advice
Segment Level Statistics
Timed Statistics
Timed OS Statistics
Plan Execution Statistics
28
System Impact
Basic Terminology
User / Job Request (workload) (vsysstat)
Throughput
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 (vsysstat)
Wait Times (vsystem_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)
Statistic BASIC timed_statistics TYPICAL TYPICAL / BASIC ALL ALL / TYPICAL ALL / BASIC
execute count 520370 505683 (2.82) 498753 (1.37) 4.15
calls to get snapshot scn kcmgss 703731 682467 (3.02) 674396 (1.18) 4.17
session logical reads 1087327 1052570 (3.20) 1041518 (1.05) 4.21
db block changes 446278 431281 (3.36) 427297 (0.92) 4.25
user commits 39405 38323 (2.75) 37766 (1.45) 4.16
31
System Impact
Test Scenarios (wait types)
Statistic BASIC timed_statistics TYPICAL TYPICAL / BASIC ALL ALL / TYPICAL ALL / BASIC
Total Waits 44451 42719 (3.90) 42558 (.38) (4.26)
Total Timeouts 40584 39272 (3.23) 38859 (1.05) (4.25)
Total Time Waited 682 634 (7.04) 655 (-3.31) (3.96)
32
System Impact
Test Scenarios (resource types)
Statistic BASIC timed_statistics TYPICAL ALL TYPICAL / BASIC ALL / TYPICAL ALL / BASIC
redo blocks written 146059 141432 139607 3.17 1.29 4.42
redo entries 224570 216854 214942 3.44 0.88 4.29
redo size 68865028 66699016 65852920 3.15 1.27 4.37
redo writes 40685 39394 38970 3.17 1.08 4.22
change write time 1238 1227 1221 0.89 0.49 1.37
enqueue requests 143264 137748 137173 3.85 0.42 4.25
enqueue waits 8 33 20 -312.50 39.39 -150.00
consistent changes 7162 6460 6833 9.80 -5.77 4.59
physical reads 3210 2778 3116 13.46 -12.17 2.93
physical writes 3297 2946 3040 10.65 -3.19 7.79
parse count (total) 4818 4545 4680 5.67 -2.97 2.86
sorts (memory) 1937 1830 1885 5.52 -3.01 2.68
sorts (rows) 4251 4244 4243 0.16 0.02 0.19
table fetch by rowid 11975 11041 11505 7.80 -4.20 3.92
table scan blocks gotten 85358 82778 81920 3.02 1.04 4.03
table scan rows gotten 400388 386772 383741 3.40 0.78 4.16
table scans (long tables) 17 16 17 5.88 -6.25 0.00
table scans (short tables) 122229 118631 117138 2.94 1.26 4.17
33
System Impact
More Data
Give Me a Call file///C/temp/Vsysstat.htm file/
//C/temp/Vsysstat_xls.htm file///C/oradoc/920do
c/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 VDB_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 SQLgt SELECT
name, block_size, 100(1 -
(physical_reads / (db_block_getsconsistent_gets))
) buffhit FROM vbuffer_pool_statis
tics

• How we size the buffer cache
• Just issue the SQL to see the hit ratio
• Got smarter and diffd 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
Diffd Buffer Cache Hit Ratio
SQLgt SELECT b.name, b.block_size,
100(1 - ((e.physical_reads-b.physical_reads)
/ ((e.db_block_gets-b.d
b_block_gets) (e.consistent_gets-b.c
onsistent_gets)))) buffhit FROM
beg_buffer_pool_statistics b, end_buffer_pool_stat
istics e WHERE b.namee.name AND
b.block_sizee.block_size

• How to diff
• Create table beg_buffer_pool_statistics as select
  from vbuffer_pool_statistics
• Run workload through system
• Create table end_buffer_pool_statistics as select
  from vbuffer_pool_statistics
• Issue above SQL

37
Buffer Cache Advisory
Use of VDB_CACHE_ADVICE

• SQLgt SELECT name, block_size,
  size_for_estimate,
• estd_physical_read_factor,
  estd_physical_reads
• FROM VDB_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 VDB_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
• Dont reduce the size of your cache if you are
  going to incur more physical reads
• Dont 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 Donts

40

• Implications
• of Setting
• Oracle9iR2s
• Statistical Collection Level
• James F. Koopmann
• dbDoctor Inc.
• Colorado
• www.dbdoctor.net
• Jkoopmann_at_dbdoctor.net
• james_koopmann_at_yahoo.com