Comparing Top 5 Timed Events and Cache Advisory
From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Wed, 15 Jun 2011 18:38:43 -0700 (PDT)
Message-ID: <1da9e9ea-1934-401a-8a3c-a45a7ab8788a_at_18g2000prd.googlegroups.com>
There seems to be inconsistency between data in Top 5 Timed Events and Cache Advisory
wait Call
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------
D 5,008 .1 619 2.0 58,806,702 158,278,272 221.9
D 10,016 .2 1,239 1.7 49,510,240 120,698,597 169.2
D 15,024 .3 1,858 1.5 44,595,719 100,832,333 141.3
D 20,032 .4 2,478 1.4 41,273,666 87,403,398 122.5
D 25,040 .5 3,097 1.3 38,542,248 76,362,022 107.0
D 30,048 .6 3,717 1.2 35,967,561 65,954,188 92.5
D 35,056 .7 4,336 1.1 33,628,296 56,498,044 79.2
D 40,064 .8 4,955 1.1 31,907,259 49,540,991 69.4
D 45,072 .9 5,575 1.0 30,671,422 44,545,292 62.4
D 50,080 1.0 6,194 1.0 29,759,277 40,858,074 57.3 <<<<<<<--------
D 50,176 1.0 6,206 1.0 29,744,366 40,797,800 57.2
D 55,088 1.1 6,814 1.0 29,095,614 38,175,312 53.5
D 60,096 1.2 7,433 1.0 28,598,287 36,164,941 50.7
D 65,104 1.3 8,053 0.9 28,183,870 34,489,710 48.3
D 70,112 1.4 8,672 0.9 27,821,913 33,026,557 46.3
D 75,120 1.5 9,291 0.9 27,496,055 31,709,318 44.4
D 80,128 1.6 9,911 0.9 27,204,516 30,530,810 42.8
D 85,136 1.7 10,530 0.9 26,927,887 29,412,574 41.2
D 90,144 1.8 11,150 0.9 26,651,485 28,295,264 39.7
D 95,152 1.9 11,769 0.9 26,413,195 27,332,006 38.3
D 100,160 2.0 12,389 0.8 24,202,729 18,396,504 25.8
Date: Wed, 15 Jun 2011 18:38:43 -0700 (PDT)
Message-ID: <1da9e9ea-1934-401a-8a3c-a45a7ab8788a_at_18g2000prd.googlegroups.com>
There seems to be inconsistency between data in Top 5 Timed Events and Cache Advisory
Top 5 Events shows that 90% of all time is spend doing reads:
Top 5 Timed Events
Avg %Total
wait Call
Event Waits Time (s)(ms) Time
----------------------------------------- ------------ ----------- ------ ------ db file sequential read 18,010,465 103,171 6 87.3 CPU time 8,135 6.9 db file scattered read 2,772,236 4,252 2 3.6 read by other session 535,946 949 2 .8 db file parallel read 65,775 856 13 .7
However cache advisory shows that "Est % db time for Rds" is only 57.3% for current cache size:
Est
Phys
Estimated Est
Size for Size Buffers Read Phys Reads Est Phys %
dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------
D 5,008 .1 619 2.0 58,806,702 158,278,272 221.9
D 10,016 .2 1,239 1.7 49,510,240 120,698,597 169.2
D 15,024 .3 1,858 1.5 44,595,719 100,832,333 141.3
D 20,032 .4 2,478 1.4 41,273,666 87,403,398 122.5
D 25,040 .5 3,097 1.3 38,542,248 76,362,022 107.0
D 30,048 .6 3,717 1.2 35,967,561 65,954,188 92.5
D 35,056 .7 4,336 1.1 33,628,296 56,498,044 79.2
D 40,064 .8 4,955 1.1 31,907,259 49,540,991 69.4
D 45,072 .9 5,575 1.0 30,671,422 44,545,292 62.4
D 50,080 1.0 6,194 1.0 29,759,277 40,858,074 57.3 <<<<<<<--------
D 50,176 1.0 6,206 1.0 29,744,366 40,797,800 57.2
D 55,088 1.1 6,814 1.0 29,095,614 38,175,312 53.5
D 60,096 1.2 7,433 1.0 28,598,287 36,164,941 50.7
D 65,104 1.3 8,053 0.9 28,183,870 34,489,710 48.3
D 70,112 1.4 8,672 0.9 27,821,913 33,026,557 46.3
D 75,120 1.5 9,291 0.9 27,496,055 31,709,318 44.4
D 80,128 1.6 9,911 0.9 27,204,516 30,530,810 42.8
D 85,136 1.7 10,530 0.9 26,927,887 29,412,574 41.2
D 90,144 1.8 11,150 0.9 26,651,485 28,295,264 39.7
D 95,152 1.9 11,769 0.9 26,413,195 27,332,006 38.3
D 100,160 2.0 12,389 0.8 24,202,729 18,396,504 25.8
Shouldn't these two values to be the same?
This database is running batch processing. We are trying to justify
purchasing more memory.
The question is: how much improvement in batch run time we'll get if
we double memory size? 25%? 50%?
Received on Wed Jun 15 2011 - 20:38:43 CDT