|
-
Qusetions On Db_cache_advice
hi all,
I have installed oracle 9i few days back and its running non stop since then. I had set the db_cache_advice=READY immediately after the creation of my database. Now to gather the statistics I set it to ON mode and after that executed the select ....from V$DB_CACHE_ADVICE. Now my question to you guys is why the estd_physical_read_factor is showing 1 for all the 20 different cache sizes?
thanks for all your replies.
Parijat Paul
-
Solution
Hi,
There May be 2 reasons...
Please check the init.ora Parameter whether you have
1)pre 9i parameter db_block_buffers disabled
2)also db_cache_size is enabled.
Also you have to allow some time till the work load stabilizes ,after that if you query and check you will be right.
If you have still doubt on this Kindly post your concern.
Cheers
Padmam
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
-
hi,
thanks for your reply.
i' ve checked both these parameters, db_block_buffer is set to 0 and db_cach_size is set to 128MB. do i need to alter the system and keep the advice mode to ON for couple of ours before running the select statement? the advice mode is set to READY mode and i switch it to ON just before running the select statement.
thanks
Parijat Paul
-
You need to run more heavier queries to get the proper output from v$db_cache_advice like in the production env.
The db_cache_advice gives you statistics on how much physical reads will occur based on the different size of your buffer cache. The estd_physical_read_factor is not the thing to watch over here. Look at what the value of estd_physical_reads is.
On a freshly started database. My test database.
Code:
SYS@ACME.WORLD> show parameter db_cache_advice
NAME TYPE VALUE
------------------------------------ ----------- ----------------
db_cache_advice string ON
SYS@ACME.WORLD> show parameter db_cache_size
NAME TYPE VALUE
------------------------------ ----------- --------------------
db_cache_size big integer 226492416
The above values of db_cache_size are almost same for both my test DB on my local PC and the DEV Server.
Code:
sys@ACME.WORLD> SELECT size_for_estimate
2 ,buffers_for_estimate
3 ,estd_physical_read_factor
4 ,estd_physical_reads
5 FROM v$db_cache_advice
6 WHERE NAME = 'DEFAULT'
7 AND block_size = (SELECT VALUE
8 FROM v$parameter
9 WHERE NAME = 'db_block_size')
10 AND advice_status = 'ON';
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
24 3003 1 84
48 6006 1 84
72 9009 1 84
96 12012 1 84
120 15015 1 84
144 18018 1 84
168 21021 1 84
192 24024 1 84
216 27027 1 84
240 30030 1 84
264 33033 1 84
288 36036 1 84
312 39039 1 84
336 42042 1 84
360 45045 1 84
384 48048 1 84
408 51051 1 84
432 54054 1 84
456 57057 1 84
480 60060 1 84
20 rows selected.
sys@ACME.WORLD>
The above output doesn't give a clear picture , all ESTD_PHYSICAL_READ_FACTOR are 1 and ESTD_PHYSICAL_READS is 84 as there is hardly any transaction.
On the dev DB which has much more transations than my local DB.
Code:
SYS@DTGR01.WORLD> show parameter db_cache_advice
NAME TYPE VALUE
------------------------------------ ----------- ----------------
db_cache_advice string ON
SYS@DTGR01.WORLD> show parameter db_cache_size
NAME TYPE VALUE
------------------------------ ----------- --------------------
db_cache_size big integer 224492416
sys@DTGR01.WORLD> SELECT size_for_estimate
2 ,buffers_for_estimate
3 ,estd_physical_read_factor
4 ,estd_physical_reads
5 FROM v$db_cache_advice
6 WHERE NAME = 'DEFAULT'
7 AND block_size = (SELECT VALUE
8 FROM v$parameter
9 WHERE NAME = 'db_block_size')
10 AND advice_status = 'ON';
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
16 1,985 2.84 7,988,323 <-- 7% of current size
32 3,970 2.02 5,673,723
48 5,955 1.06 2,997,951
64 7,940 1.00 2,815,606
80 9,925 .97 2,732,799
96 11,910 .95 2,688,192
112 13,895 .94 2,635,109
128 15,880 .90 2,540,468
144 17,865 .60 1,697,546
160 19,850 .55 1,541,303
176 21,835 .50 1,417,261
192 23,820 .43 1,214,343
208 25,805 .37 1,029,681
224 27,790 .32 901,064 <-- current size
240 29,775 .25 711,725
256 31,760 .20 573,298
272 33,745 .17 482,895
288 35,730 .15 414,356
304 37,715 .13 372,711
320 39,700 .12 330,743 <-- 140% of current size
The above output shows that if the cache was 208MB, rather than the current size of 224MB, the estimated number of physical reads would be 1 million (1,029,681). Increasing the cache size beyond its current size would not provide a significant benefit. So look at ESTD_PHYSICAL_READS.
So you can use db_cache_advice to find out the optimum size for your db_cache_size. You can collect the stats, change the size of your db_cache_size based on v$db_cache_advice and then run the stats again and again till you find what value of db_cache_size reduces the ESTD_PHYSICAL_READS significantly.
HTH
Amar
"There is a difference between knowing the path and walking the path."
 
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote