Hi,
Oracle on Linux - Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
Following are the results of v$dba_cache_advice, v$shared_pool_advice
Based on this output i have following queries
1) Shall i change the Buffer Cache to 144 MB (which is now 112 MB)
2) Increasing Shared Pool Size (which is 208 MB now) will not give any gain even if increased up to 400 MB
Buffers_for_estimate Estd_physical_reads Estd_physical_read_factor Size_for_estimate
2,002 19,317,663 1.29 16
4,004 18,787,930 1.26 32
6,006 17,731,128 1.19 48
8,008 15,512,951 1.04 64
10,010 15,252,318 1.02 80
12,012 15,092,329 1.01 96
14,014 14,921,717 1.00 112
16,016 14,775,354 0.99 128
18,018 11,490,132 0.77 144
20,020 11,207,783 0.75 160
22,022 11,025,132 0.74 176
24,024 10,911,466 0.73 192
26,026 10,751,855 0.72 208
28,028 10,586,252 0.71 224
30,030 10,429,097 0.70 240
32,032 10,317,416 0.69 256
34,034 10,209,459 0.68 272
36,036 10,156,217 0.68 288
38,038 10,099,800 0.68 304
40,040 10,015,581 0.67 320
*******************************************
SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_TIME_SAVED ESTD_LC_MEMORY_OBJECT_HITS
112 0.54 1 3,084,762 95,126,992
144 0.69 1 3,084,766 95,129,353
176 0.85 1 3,084,767 95,129,830
208 1.00 1 3,084,767 95,129,850
240 1.15 1 3,084,767 95,129,850
272 1.31 1 3,084,767 95,129,850
304 1.46 1 3,084,767 95,129,850
336 1.62 1 3,084,767 95,129,850
368 1.77 1 3,084,767 95,129,850
400 1.92 1 3,084,767 95,129,850
432 2.08 1 3,084,767 95,129,850
However, the following formula which is theoratically correct is giving too different value.
select sum(a.spspv) "Packages/Views", sum(a.spssql) "SQL Statements",
sum(a.spsusr) "SQL Users", round((sum(a.spspv) + sum(a.spssql) +
sum(a.spsusr)) * 2.5,-6) "Estimated shared_pool_size"
from (select sum(sharable_mem) spspv, 0 spssql, 0 spsusr
from v$db_object_cache
union all
select 0, sum(sharable_mem), 0 from v$sqlarea
where executions > 5
union all
select 0, 0, sum(250 * users_opening) from v$sqlarea) a;
Please suggest on this.
Thanks in Advance,
Pratap
|
] 
Blog
Search
Help
Members
Register
Login
Home
