variable possibility to bring the performance down ,
at first , check hit ratio (do it in peak hour)
select pr.value "Physical Reads",
cg.value "Consistent Gets",
bg.value "DB Block Gets",
round((1-(pr.value/(bg.value+cg.value)))*100,2) "Hit Ratio"
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name = 'physical reads'
and bg.name = 'db block gets'
and cg.name = 'consistent gets'IF Percent is less than 70% , increase DB_BLOCK_BUFFERSselect sum(pins) "Executions",
sum(reloads) "Cache Misses Executing",
(sum(reloads)/sum(pins)*100) "% Ratio"
from v$librarycacheIf % Ratio is above 1% , increase SHARE_POOL_SIZE.select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Get Misses",
100*(sum(getmisses)/sum(gets)) "Ratio"
from v$rowcacheIf % Ratio is above 12% , increase SHARED_POOL_SIZE
at first , check hit ratio (do it in peak hour)
select pr.value "Physical Reads",
cg.value "Consistent Gets",
bg.value "DB Block Gets",
round((1-(pr.value/(bg.value+cg.value)))*100,2) "Hit Ratio"
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name = 'physical reads'
and bg.name = 'db block gets'
and cg.name = 'consistent gets'IF Percent is less than 70% , increase DB_BLOCK_BUFFERSselect sum(pins) "Executions",
sum(reloads) "Cache Misses Executing",
(sum(reloads)/sum(pins)*100) "% Ratio"
from v$librarycacheIf % Ratio is above 1% , increase SHARE_POOL_SIZE.select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Get Misses",
100*(sum(getmisses)/sum(gets)) "Ratio"
from v$rowcacheIf % Ratio is above 12% , increase SHARED_POOL_SIZE
Data Dictionary Gets Get Misses Ratio
-------------------- ---------- ---------
306110 1998 .65270654
是否增加 shared_pool_size??
1.检测高速缓存的命中率 (高速缓存中存放的是最近被访问的数据块,经验值最好是90%或更高)
2.检测库缓存的命中率 (库缓存中存放的是最近执行SQL语句的执行计划和解析树,越接近1越好)
3.监测数据字典的命中率 (数据字典缓存区存放的是最近被访问的数据字典的信息,85%以上最好),根据你的检测结果,该值不用修改。
另外,我建议你可以为数据量大的表,建立partition,将大表通过时间戳拆分为若干个小表,即便于维护,亦提高了查询的速度。
不知道楼主的数据助理都做什么动作?update?insert?
应该和那些命中率没有关系。
可不可以把init.ora贴出来看看。
谢谢!
open_cursors = 100
max_enabled_roles = 30
db_file_multiblock_read_count = 8 # INITIAL
# db_file_multiblock_read_count = 8 # SMALL
# db_file_multiblock_read_count = 16 # MEDIUM
# db_file_multiblock_read_count = 32 # LARGEdb_block_buffers = 19200 # INITIAL
# db_block_buffers = 100 # SMALL
# db_block_buffers = 550 # MEDIUM
# db_block_buffers = 3200 # LARGEshared_pool_size = 52428800 # INITIAL
# shared_pool_size = 3500000 # SMALL
# shared_pool_size = 5000000 # MEDIUM
# shared_pool_size = 9000000 # LARGElarge_pool_size = 614400
java_pool_size = 20971520log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800processes = 59 # INITIAL
# processes = 50 # SMALL
# processes = 100 # MEDIUM
# processes = 200 # LARGEparallel_max_servers = 5 # SMALL
# parallel_max_servers = 4 x (number of CPUs) # MEDIUM
# parallel_max_servers = 4 x (number of CPUs) # LARGElog_buffer = 32768 # INITIAL
# log_buffer = 32768 # SMALL
# log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # LARGE
不同的操作语句性能低的原因和优化方式都不尽相同。所以还是请更清楚地描述
你所做的操作吧。