Oracle consistent gets
Metric consistent gets
Oracle Tips by Burleson Consulting
consistent gets
the number of times a
consistent read (a logical RAM buffer I/O) was requested to get data
from a data block. Part of Oracle tuning is to increase logical I/O
by reducing the expensive disk I/O (physical reads), but high
consistent gets presents it's own tuning challenges, especially when
we see super high CPU consumption (i.e. the &top 5 timed events& in
an AWR report).
Tuning Consistent Gets
Many shops with super-high consistent gets have high CPU
consumption and this is quickly fixed by adding more CPU's to the
server. Note that Oracle expert
sees &buffer chains latch& thrashing (latch
overhead) as a major contributor to high CPU consumption on
highly-buffered Oracle databases (e.g. 64-bit Oracle with a 50 gig
db_cache_size):&
& The closer a system gets to processor saturation, the more
troublesome latch gets become--presuming the chain is hot.
While cache buffers chains latch thrashing may seem
like a nebulous place to put blame for high processor utilization,
trust me, it isn't.&.&
Types of Consistent Gets
Not all buffer touches are created equal, and
Oracle has several types of &consistent gets&, the term used by
Oracle to describe an Oracle I/O that is done exclusively from the
buffer cache.& Oracle AWR and STATSPACK reports mention several
types of consistent gets, all undocumented:
consistent gets
consistent gets from cache
consistent gets - examination
consistent gets direct
Some Oracle experts claim that
these undocumented underlying mechanism can be revealed and
that these consistent gets metrics may tell us about data
clustering& Mladen Gogala, author of && makes
about consistent gets:
&The [consistent gets]
overhead is the time spent by Oracle to maintain its own
structures + the time spent by OS to maintain its own
structures. So, what exactly happens during a consistent
get in the situation described? As I don't have access
to the source code, I cannot tell precisely, with 100%
of certainty, but based on my experience, the process
goes something like this:
1) Oracle calculates
the hash value of the block and searches the SGA
hash table for the place where the block is located.
2) Oracle checks the SCN of the block and compares
it with the SCN of the current transaction. Here,
I'll assume that this check will be OK and that no
read consistent version needs to be constructed.
3) If the instance is a part of RAC, check the
directory and see whether any other instance has
modified the block. It will require communication
with the GES process using the IPC primitives (MSG
system calls). MSG system calls are frequently
implemented using device driver which brings us to
the OS overhead (context switch, scheduling)
4) If everything is OK, the block is paged in the
address space of the requesting process. For this
step I am not exactly sure when does it happen, but
it has to happen at some point. Logically, it would
look as the last step, but my logic may be flawed.
Here, of course, I assume a soft fault. Hard fault
would mean that a part of SGA was swapped out.
All of this is an
overhead of a consistent get and it is the simplest
case. How much is it in terms of microseconds, depends
on many factors, but the overhead exists and is strictly
larger then zero. If your SQL does a gazillion of
consistent gets, it will waste significant CPU power and
time to perform that.&
For more insights on consistent
gets, we see expert
who has a great description of the
internal mechanisms within consistent gets.&
Kevin goes on to describe the internals of a consistent get:
&The routine is kcbget() (or one of his special
purpose cousins). It doesn't really &search& a hash
*table* if you will.& A hash table would be more of
a &perfect hash& structure and to implement that, every
possible hash value has to be known when the table is
set up. That would mean knowing every possible database
block address.
Instead, it hashes to a bucket that has similar
hashed dbas chained off off it in a linked list. So it
is more of a scan of the linked list looking for the
right dba and right version of it.
The particulars of the structures under a get are not as
important as remembering that before walking that chain,
the process has to obtain the latch on the chain. &
Consistent gets - examination
Mike Ault notes that &consistent gets - examinations& are
related to buffer management overhead and data access
overhead such as index reads and undo writes:
&consistent gets – examination is from reading
something like undo blocks…
Other examples of &consistent gets – examination&
are: reading the root block of an index, reading an undo
block while creating a consistent read data block,
reading a block in a single table hash cluster - unless
it is found to have the ‘collision flag’ set.&
Steve Karam, OCM notes about &consistent gets -
examination&:
&Consistent gets - examination are a different
kind of consistent get& that only requires a single
latch, saving CPU.& The most common use of a consistent
get - examination is to read undo blocks for consistent
read purposes, but they also do it for the first part of
an index read and in certain cases for hash clusters.
So if you're doing a query on a couple tables that are
mostly cached, but one of them has uncommitted DML
against it at the time, you'll do consistent gets for
the standard data in the cache, and the query will do
consistent gets - examination to read the undo blocks
and create re this doesn't
necessarily save CPU unfortunately, because while the
consistent gets - examination only acquire one latch,&
creating the read consistent data block also takes a
However, I think that when you use single table hash
clusters (or the new 10g Sorted Hash Clusters I
mentioned once that automatically sort by a key so they
don't need order by) you can get a performance gain,
because reads from the blocks of a hash cluster are
usually consistent get - examination, therefore they
only need one latch instead of two.&&
Interpreting
consistent gets in reports
Here is a STATSPACK (pr AWR)
report& we see displays for &consistent gets& and
&consistent gets - examinations&:&
Statistic&&&&&&&&&&&&&&&&&&&&&&&&
Total&&&&&&&&&&&&&
per Second&&&& per Trans
--------------------------------- ------------------
-------------- -----------
consistent gets&&&&&&&&&&&&&&&&&&&&&&&&&& 35,024,284&&&&&&&
9,718.2&&&&& 3,703.9
consistent
gets - examination&&&&&&&&&&&& 12,148,672&&&&&&&
3,370.9&&&&& 1,284.8
forum had a problem in which a user had trouble, &when we run set
autotrace on or similar execution statistics.&& The problem was
resolved in part with this advices: &consistent gets is the blocks
in consistent mode (sometimes reconstructed using information from
Undo logs). So this reconstruction from UNDO (rollback segments)& takes more resources (reads
actually), which will end up as high consistent gets.&
Consistent gets and many other Oracle performance metrics are discussed in
my book && by Rampant TechPress.&
Get Complete Oracle Tuning Details&
The landmark book
&& has been
updated with over 800 pages of expert performance tuning tips.
It's packed with scripts and tools to hypercharge Oracle performance and you can
for 40% off directly from the publisher.
��
Burleson is the American Team
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.&
Feel free to ask questions on our
experience!
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
&Oracle technology is changing and we
strive to update our BC Oracle support information.& If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback.& Just&
&and include the URL for the page.
&&&&&&&&&&&&&&&&&&&&
Burleson Consulting
The Oracle of
Database Support
Copyright (C) 1996 -& 2014
All rights reserved by
Oracle (C)
is the registered trademark of Oracle Corporation.您所在的位置： &
Oracle数据库consistent gets使用的特例
Oracle数据库consistent gets使用的特例
本文我们主要介绍了Oracle数据库中使用consistent gets做性能比较的一个特殊的例子，并分析了其特殊的原因，通过本文我们能够更深刻地理解consistent gets，希望能够对您有所帮助。
Oracle数据库中，consistent gets在判断一段SQL的性能时非常有用，通常来讲比较两段SQL的性能好坏不是看谁的执行时间短，而是看谁的consistent gets小。不过这也不是绝对的，下面这个例子就是一个反例。
反例子如下：
ETL@RACTEST&create&table&test(&a&int); &Table&created.&Elapsed:&00:00:00.05 &ETL@RACTEST&ETL@RACTEST&begin &&&2&&for&i&in&1..10000&loop &&&3&&insert&into&test&values&(i); &&&4&&end& &&&5&& &&&6&&/&PL/SQL&procedure&successfully&completed.&Elapsed:&00:00:00.44 &ETL@RACTEST&set&autot&trace &ETL@RACTEST&ETL@RACTEST&select&*&from& &10000&rows&selected.&Elapsed:&00:00:00.05&Execution&Plan&Plan&hash&value:&&------------------------------------------------------------- &|&Id&&|&Operation&&&&&&&&&|&Name&|&Rows&&|&Bytes&|&Cost&(%CPU)|&Time&&&&&|&|&&&0&|&SELECT&STATEMENT&&|&&&&&&|&10000&|&&&126K|&&&&&6&&&(0)|&00:00:01&| &|&&&1&|&&TABLE&ACCESS&FULL|&TEST&|&10000&|&&&126K|&&&&&6&&&(0)|&00:00:01&| &--------------------------------------------------------------------------&Note&&&&-&dynamic&sampling&used&for&this&statement &Statistics&&&&&&&&&&&0&&recursive&calls &&&&&&&&&&&0&&db&block&gets &&&&&&&&&690&&consistent&gets &&&&&&&&&&&0&&physical&reads &&&&&&&&&&&0&&redo&size &&&&&&214231&&bytes&sent&via&SQL*Net&to&client &&&&&&&&7791&&bytes&received&via&SQL*Net&from&client &&&&&&&&&668&&SQL*Net&roundtrips&to/from&client &&&&&&&&&&&0&&sorts&(memory) &&&&&&&&&&&0&&sorts&(disk) &&&&&&&10000&&rows&processed&可以看到select&*读了690个内存块。&ETL@RACTEST&select&*&from&test&order&by&1;&10000&rows&selected.&Elapsed:&00:00:00.04&Execution&Plan&Plan&hash&value:&&-------------------------------------------------------------------- &|&Id&&|&Operation&&&&&&&&&&|&Name&|&Rows&&|&Bytes&|&Cost&(%CPU)|&Time&&&&&|&|&&&0&|&SELECT&STATEMENT&&&|&&&&&&|&10000&|&&&126K|&&&&&7&&(15)|&00:00:01&| &|&&&1&|&&SORT&ORDER&BY&&&&&|&&&&&&|&10000&|&&&126K|&&&&&7&&(15)|&00:00:01&| &|&&&2&|&&&TABLE&ACCESS&FULL|&TEST&|&10000&|&&&126K|&&&&&6&&&(0)|&00:00:01&| &---------------------------------------------------------------------------&Note&&&&-&dynamic&sampling&used&for&this&statement &Statistics&&&&&&&&&&&0&&recursive&calls &&&&&&&&&&&0&&db&block&gets &&&&&&&&&&23&&consistent&gets &&&&&&&&&&&0&&physical&reads &&&&&&&&&&&0&&redo&size &&&&&&174288&&bytes&sent&via&SQL*Net&to&client &&&&&&&&7791&&bytes&received&via&SQL*Net&from&client &&&&&&&&&668&&SQL*Net&roundtrips&to/from&client &&&&&&&&&&&1&&sorts&(memory) &&&&&&&&&&&0&&sorts&(disk) &&&&&&&10000&&rows&processed&
再看一下order by，竟然只有23个逻辑读！
1. select *
2. select * from test order by 1;
第1个SQL比第2个SQL效率高是毋庸置疑的。但是为什么第2个SQL的consistent gets如此之少，我起初也是百思不得其解，最终我在ASK TOM中找到了答案。
一：通常情况下，不在logical RAM buffer中的数据要通过physical reads来读取，而physical reads后通常会紧跟着一个consistent gets。因此一般情况下consistent gets是要比physical reads大的。但是有一个特例，如果physical reads得到的数据直接用于HASH或者SORT，则只记为physical reads不记为consistent gets。所以加上order by后有可能physical reads多但consistent gets少。不过这个原因不是我这里现象产生的原因，因为我这个实验里根本没有physical reads。
二：arraysize的影响。arraysize是指读取数据时一次读取得到的行数。这个值默认为15，使用show arraysize命令可以查看。一个数据块例如有100条记录，那么并不是读取这个块一次就能取到所有数据，以arraysize=15为例，就要有100/15=7次consistent gets。把arraysize设置得大一点可以降低consistent gets，不过有时候可能会消耗更多的资源。如果我们做select count(0)操作，那么Oracle会把arraysize暂时设为test的行数，因此consistent gets会很少：
代码如下：
ETL@RACTEST&select&count(0)&from&&Elapsed:&00:00:00.00&Execution&Plan&Plan&hash&value:&&-------------- &|&Id&&|&Operation&&&&&&&&&&|&Name&|&Rows&&|&Cost&(%CPU)|&Time&&&&&|&|&&&0&|&SELECT&STATEMENT&&&|&&&&&&|&&&&&1&|&&&&&6&&&(0)|&00:00:01&| &|&&&1&|&&SORT&AGGREGATE&&&&|&&&&&&|&&&&&1&|&&&&&&&&&&&&|&&&&&&&&&&| &|&&&2&|&&&TABLE&ACCESS&FULL|&TEST&|&10000&|&&&&&6&&&(0)|&00:00:01&| &-------------------------------------------------------------------&Note&&&&-&dynamic&sampling&used&for&this&statement &Statistics&&&&&&&&&&&0&&recursive&calls &&&&&&&&&&&0&&db&block&gets &&&&&&&&&&23&&consistent&gets &&&&&&&&&&&0&&physical&reads &&&&&&&&&&&0&&redo&size &&&&&&&&&515&&bytes&sent&via&SQL*Net&to&client &&&&&&&&&465&&bytes&received&via&SQL*Net&from&client &&&&&&&&&&&2&&SQL*Net&roundtrips&to/from&client &&&&&&&&&&&0&&sorts&(memory) &&&&&&&&&&&0&&sorts&(disk) &&&&&&&&&&&1&&rows&processed&
可以看到select count(0)只需要23个逻辑读。一共10000条数据，.667 ，好，667+23=690！和第1个SQL的consistent gets竟然惊人的一致！这不是巧合，这就是consistent gets的计算公式。我们还可以发现select count(0)和第2个SQL的consistent gets竟然也惊人地一致，都是23！
TOM的解释是：
在select * from test order by 1;时，Oracle也把arraysize临时设为test表的行数，它把所有数据先全部取出来放到sort区做排序，而在sort区的读取就不算在consistent gets里了。所以虽然第2个SQL和select count(0)的consistent gets相同，但它的效率一定比select count(0)低，我们看执行计划里的COST便可以得知，第2个SQL的COST为7，select count(0)的COST为6，第1个SQL的COST也为6。（COST相同并不代表执行效率完全相同）
关于Oracle数据库consistent gets的知识就介绍到这里了，希望本次的介绍能够对您有所收获！
【编辑推荐】
【责任编辑： TEL：（010）】
关于&&的更多文章
Oracle集群，也称Oracle RAC，称为“真正应用集群”。是Oracle新
数据库产品
数据库综合
数据库新闻
维基百科将切换到另外一款开源数据库MariaDB
51CTO将为您整理数据清洗方面的技术文章和相关资料，
这个专题系统地介绍和分析比较了业界主流的Yahoo! S4
数据挖掘，越来越多的体现在企业的数据报表上，也会为
本书是Inside Microsoft SQL Server 2000的作者Kalen Delaney的又一经典著作，是Inside Microsoft SQL Server 2005系列四本著作
51CTO旗下网站trackbacks-1
关于Oracle中各个命中率的计算以及相关的调优
1)Library Cache的命中率:
.计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)
SQL&SELECT SUM(pinhits)/sum(pins)&&
&&&& FROM V$LIBRARYCACHE;
通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。
2)计算共享池内存使用率:
SQL&SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'
&&& &FROM V$SGASTAT
&&& &WHERE NAME = 'free memory' AND POOL = 'shared pool';
其中: &TSP_IN_M是你的总的共享池的SIZE(M)
共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。
查询空闲的共享池内存:
SQL&SELECT * FROM V$SGASTAT
&&& &WHERE NAME = 'free memory' AND POOL = 'shared pool';
3)db buffer cache命中率:
计算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]
SQL&SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
&&&&& 1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
&&&&& FROM V$BUFFER_POOL_STATISTICS
&&&&& WHERE NAME='DEFAULT';
通常应在90%以上，否则，需要调整,加大DB_CACHE_SIZE
另外一种计算命中率的方法(摘自ORACLE官方文档&&数据库性能优化&&):
命中率的计算公式为: Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / (db block gets + consistent gets - physical reads direct - physical reads direct (lob))
分别代入上一查询中的结果值,就得出了Buffer cache的命中率
SQL&SELECT NAME, VALUE
&&& &FROM V$SYSSTAT
&&& &WHERE NAME IN('session logical reads',
&'physical reads',
&'physical reads direct',
&&&&&&&&&&&&&& 'physical reads direct (lob)',
&&&&&&&&&&&&&& 'db block gets', 'consistent gets');
4)数据缓冲区命中率：
SQL& select value from v$sysstat where name ='physical reads';
SQL& select value from v$sysstat where name ='physical reads direct';
SQL& select value from v$sysstat where name ='physical reads direct (lob)';
SQL& select value from v$sysstat where name ='consistent gets';
SQL& select value from v$sysstat where name = 'db block gets';
这里命中率的计算应该是
令 x = physical reads direct + physical reads direct (lob)
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区
5)共享池的命中率：
SQL& select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$
假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存
6)计算在内存中排序的比率:
SQL&SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)';—查询内存排序数
SQL&SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)';—查询磁盘排序数
--caculate sort in memory ratio
SQL&SELECT round(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM
此比率越大越好,太小整要考虑调整,加大PGA
7)PGA的命中率:
计算公式:BP x 100 / (BP + EBP)
BP: bytes processed
EBP: extra bytes read/written
SQL&SELECT * FROM V$PGASTAT&WHERE NAME='cache hit percentage';
或者从OEM的图形界面中查看
我们可以查看一个视图以获取Oracle的建议值:
SQL&SELECT round(PGA_TARGET_FOR_ESTIMATE/) target_mb,
&&&&&& ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
&&&&&& ESTD_OVERALLOC_COUNT
&&&&&& FROM V$PGA_TARGET_ADVICE;
The output of this query might look like the following:
&TARGET_MB&CACHE_HIT_PERC&ESTD_OVERALLOC_COUNT
----------&&&&&&&&&&--------------&&&&&&&--------------------
&& 63&&&&&&&&&&&&& 23&&&&&&&&&&&&&&&&&& 367
&125&&&&&&&&&&&&& 24&&&&&&&&&&&&&&&&&&& 30
&250&&&&&&&&&&&&& 30&&&&&&&&&&&&&&&&&&&& 3
&&375&&&&&&&&&&&&& 39&&&&&&&&&&&&&&&&&&&& 0
&500&&&&&&&&&&&&& 58&&&&&& &&&&&&&&&&&&&&0
&600&&&&&&&&&&&&& 59&&&&&&&&&&&&&&&&&&&& 0
&700&&&&&&&&&&&&& 59&&&&&&&&&&&&&&&&&&&& 0
&800&&&&&&&&&&&&& 60&&&&&&&&&&&&&&&&&&&& 0
&900&&&&&&&&&&&&& 60&&&&&&&&&&&&&&&&&&&& 0
在此例中:PGA至少要分配375M
我个人认为PGA命中率不应该低于50%
以下的SQL统计sql语句执行在三种模式的次数: optimal memory size, one-pass memory size, multi-pass memory size:
SQL&SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total,4)) percentage
&&& FROM (SELECT name, value cnt, (sum(value) over ()) total FROM V$SYSSTAT WHERE name like 'workarea exec%');
8)共享区字典缓存区命中率
计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)
命中率应大于0.85
SQL&select sum(gets-getmisses-usage-fixed)/sum(gets)
　　from v$
9)数据高速缓存区命中率
计算公式：1-(physical reads / (db block gets + consistent gets))
命中率应大于0.90最好
SQL&select name,value
　　from v$sysstat
　　where name in ('physical reads','db block gets','consistent gets');
10)共享区库缓存区命中率
计算公式：SUM(pins - reloads) / SUM(pins)
命中率应大于0.99
SQL&select sum(pins-reloads)/sum(pins)
　　from v$
11)检测回滚段的争用
SUM(waits)值应小于SUM(gets)值的1%
SQL&select sum(gets),sum(waits),sum(waits)/sum(gets)
　　from v$
12)检测回滚段收缩次数
SQL&select name,shrinks
　　from v$rollstat, v$rollname
　　where v$rollstat.usn = v$rollname.
-----------------------------------------------------------------------------
几个常用的检查语句
1. 查找排序最多的SQL:
SQL&SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS
&&& FROM V$SQLAREA
&&& ORDER BY SORTS DESC;
2.查找磁盘读写最多的SQL:
SQL&SELECT * FROM
&& (SELECT sql_text,disk_reads "total disk" , executions "total exec",disk_reads/executions "disk/exec" FROM v$sql WHERE executions&0 and is_obsolete='N' ORDER BY&4 desc)
&& WHERE ROWNUM&11 ;
3.查找工作量最大的SQL(实际上也是按磁盘读写来排序的):
SQL&select substr(to_char(s.pct, '99.00'), 2) || '%' load,s.executions executes,p.sql_text
&&& from(select address,disk_reads,executions,pct,rank() over (order by disk_reads desc) ranking from
&& (select address,disk_reads,executions,100 * ratio_to_report(disk_reads) over () pct
&&& from sys.v_$sql
&& where command_type != 47)
&& where disk_reads & 50 * executions) s,sys.v_$sqltext p
&& where s.ranking &= 5 and p.address = s.address
&& order by 1, s.address, p.
4. 用下列SQL工具找出低效SQL:
SQL&select executions,disk_reads,buffer_gets,round((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,round(disk_reads/executions,2) reads_per_run,sql_text
From v$sqlarea
Where executions&0 and buffer_gets &0 and (buffer_gets-disk_reads)/buffer_gets&0.8
Order by 4
5、根据sid查看对应连接正在运行的sql
SQL&select /*+ push_subq */command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,’&’||address sql_address,’N’status
From v$sqlarea
Where address=(select sql_address from v$session where sid=&sid);
***************Oracle 缓冲区命中率低的分析及解决办法******************
首先确定下面的查询结果:
1,缓冲区命中率的查询(是否低于90%):
select round((1 - sum(decode(name,'physical reads',value,0)) /
(sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0))) ),4) *100 || '%' chitrati
2,使用率的查询(有无free状态的数据快.):
select count(*), status from v$
3,相关等待事件的查询(是否有相关等待事件)
select event,total_waits from v$system_event where event in ('free buffer waits');
4,当前大小(是否已经很大)
select value/ cache_size from v$parameter where name='db_cache_size'
5,top等待事件分析(Db file scatered read的比率是否大)
select event ,total_waits,suml
(select event,total_waits,round(total_waits/sumt*100,2)||'%' suml
(select event,total_waits from v$system_event ),
(select sum(total_waits) sumt from v$system_event)
order by total_waits desc)
where rownum&6
and event not like 'rdbms%'
and event not like 'pmon%'
and event not like 'SQL*Net%'
and event not like 'smon%';
6,db_cache_advice建议值（9i后的新特性,可以根据他更好的调整cache_size）
select block_size,size_for_estimate,size_factor,estd_physical_reads from v$db_cache_
缓冲区命中率(低于90的命中率就算比较低的).
没有free不一定说明需要增加,还要结合当前cache_size的大小(我们是否还可以再增大,是否有需要增加硬件,增加开销),
空闲缓冲区等待说明进程找不到空闲缓冲区，并通过写出灰缓冲区，来加速数据库写入器生成空闲缓冲区，当DBWn将块写入磁盘后，灰数据缓冲区将被释放，以便重新使用.产生这种原因主要是:
1，DBWn可能跟不上写入灰缓冲区：i/0系统较慢，尽量将文件均匀的分布于所有设备，
2，缓冲区过小或过大。
3，可以增加db_writer_processes数量。
4,可能有很大的一个事物，或者连续的大事物
我们需要长期观察这个事件是否长期存在并数值一直在增大,如果一直在增大,则说明需要增大db_cache大小.或优化sql.
数据分散读等待,通常表现存在着与全表扫描相关的等待，逻辑读时，在内存中进行的全表扫描一般是零散地，而并非连续的被分散到缓冲区的各个部分，可能有索引丢失，或被仰制索引的存在。该等待时间在数据库会话等待多块io读取结束的时候产生，并把指定的块数离散的分布在数据缓冲区。这意味这全表扫描过多，或者io不足或争用，
存在这个事件,多数都是问题的,这说明大量的全部扫描而未采用索引.
db_cache_advice对我们调整db_cache_size大小有一定的帮助,但这只是一个参考，不一定很精确。
通过上面6种情况的综合分析,判断是否需要增加大cache_size. 或者把常用的（小）表放到keep区。
但多数的时候做这些不会解决质的问题,
而真正的问题主要是对sql语句的优化(如:是否存在大量的全表扫描等)
索引是在不需要改变程序的情况下,对数据库性能，sql语句提高的最实用的方法.
我在生产中遇到过类似的问题,200M的cache_size,命中率很低21%,但通过对sql语句的优化(添加索引,避免全表扫描),命中率增加到96%,程序运行时间由原来的2小时减少到不到10分钟.
这就提到了怎么定位高消耗的sql问题.全表扫描的问题,在这里不做细致的解说,这里只说明方法,我会在相关的章节专门介绍怎么使用这些工具
1,sql_trace跟踪session.用tkprof 分别输出磁盘读,逻辑读,运行时间长的sql进行优化.这些高消耗的sql一般都伴随着全表扫描.
2,statspack分析.在系统繁忙时期进行时间点的统计分析,产看TOP事件是否有Db file scatered read.并查看TOP sql语句是否存在问题等.
注：电脑学习网首发。
还要说一句：当然在硬件允许的情况下，尽量增大db_cache_size 减少磁盘读，但并不是越大越好，一定要根据自己的库数据量的程度来调节，因为大的db_cache_size同样会增大数据库管理的开销，当然可能开销并不会明显的影响数据库的性能，硬件价格也越来越低，这就需要我们具体问题具体分析了，在我看来物尽其用就最好了，尽量不要浪费，找到问题的本质。调优是一件很艺术的事。
***********************Oracle数据库缓冲区命中率*****************&
1、查看Oracle数据库缓冲区命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
&&& round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
&&& from v$sysstat a, v$sysstat b, v$sysstat c
&&& where a.statistic# = 40 and b.statistic# = 41
&&& and c.statistic# = 42;
2、Tags: oracle&
数据库缓冲区命中率：
sql&select value from v$sysstat where name ='physical reads';
sql&select value from v$sysstat where name ='physical reads direct';
sql&select value from v$sysstat where name ='physical reads direct(lob)';
sql&select value from v$sysstat where name ='consistent gets';
sql&select value from v$sysstat where name ='db block gets';
这里命中率的计算应该是
令x＝physical reads direct + physical reads direct(lob)
命中率＝100－(physical reads -x)/(consistent gets +db block gets -x)*100
通常如果发现命中率低于90%，则应该调整应用可以考虑是否增大数据加
共享池的命中率
sql& select sum(pinhits)/sum(pins)*100 "hit radio" from v$
如果共享池的命中率低于95％就要考虑调整应用（通常是没应用bind var)或者增加内存。
关于排序部分
sql& select name,value from v$sysstat where name like '%sort%';
如果我们发现sorts(disk)/(sorts(memory)+sorts(disk))的比例过高，则通常意味着sort_area_size部分内存教较小，可考虑调整相应的参数。
关于log_buffer
sql&select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');
假如redo buffer allocation retries/redo entries的比例超过1%我们就可以考虑增加log_buffer.
阅读(...) 评论()