Home » RDBMS Server » Performance Tuning » why ESTD_PHYSICAL_READS and ESTD_PHYSICAL_READ_TIME are not change in proportional in v$db_cache_a
why ESTD_PHYSICAL_READS and ESTD_PHYSICAL_READ_TIME are not change in proportional in v$db_cache_a [message #275536] Sun, 21 October 2007 03:51 Go to next message
wqhhust
Messages: 4
Registered: September 2007
Junior Member
I have a system with much "db sequential read" so I want to increase the size of SGA, but when query v$db_cache_advice, I found that if I double the size of SGA, the ESTD_PHYSICAL_READ_TIME will be cut by 75%(from 199,882 to 48,111), but the ESTD_PHYSICAL_READS only be cut by less than 20% (from 747,430,817 to 563,865,027). So my question is why the read time reduced is not proportional to the physical read reduced.

select c.SIZE_FACTOR, c.ESTD_PHYSICAL_READ_TIME,c.ESTD_PHYSICAL_READS,c.ESTD_PHYSICAL_READ_TIME/c.ESTD_PHYSICAL_READS from v$db_cache_advice c

0.0974 433635 956840262 0.000453194767425035
0.1949 365333 895650942 0.000407896628997237
0.2923 324082 858695632 0.000377411958233881
0.3897 296338 833841374 0.000355388937560683
0.4872 272305 812311393 0.000335222431134854
0.5846 238312 781858679 0.000304801886070769
0.6821 220777 766149079 0.000288164544018201
0.7795 212371 758619403 0.000279944065706951
0.8769 206007 752917466 0.000273611663034524
0.9744 201095 748517560 0.000268657691878331
1 199882 747430817 0.000267425419789722
1.0718 196718 744596185 0.000264194208838177
1.1692 192556 740867370 0.000259906169170333
1.2667 189031 737709474 0.000256240439715432
1.3641 185867 734874924 0.000252923312430239
1.4615 177746 727599826 0.000244290877551694
1.559 153407 705795703 0.000217353264334056
1.6564 100702 658579515 0.000152907883871851
1.7538 74071 634722204 0.000116698296566918
1.8513 48118 600045018 8.01906499622E-5
1.9487 48111 563865027 8.53236106093879E-5
Re: why ESTD_PHYSICAL_READS and ESTD_PHYSICAL_READ_TIME are not change in proportional in v$db_cache [message #275545 is a reply to message #275536] Sun, 21 October 2007 10:09 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Optimizing specific client query
Next Topic: Performance impact on Oracle
Goto Forum:
  


Current Time: Sat Dec 03 03:46:27 CST 2016

Total time taken to generate the page: 0.04946 seconds