Oracle performance tuning

db block get+consistent gets = logical read

 

consistent gets＝一致性读的block的个数，简单理解就是select访问的block个数 db block gets＝简单理解，就是update、insert、delete访问的block个数

 

logical I/O 与 physical I/O 的区别：

1）logical I/O是指对cache到 Buffer Cache 中的数据块(db block)的访问请求。又分为:consistent gets 和 db block gets(current gets) consistent gets代表对特定版本或时间的block的访问select db block gets代表对最新的或当前的block的访问，通常用于Insert,update,delete 2）physical I/O是指通过底层的I/O系统，对没有cache到 Buffer Cache 中的数据块(db block)的访问请求。其实就是oracle在buffer cache中没有发现需要的block,而去访问datafile,并且将block populate到buffer cache中的过程。

 

DB CPU：Amount of CPU time (in microseconds) spent on database user-level calls. This does not include the CPU time spent on instance background processes such as PMON.

DB time: Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the time spent on instance background processes such as PMON.

 

公式：DB TIME= DB CPU + Non-Idle Wait + Wait on CPU queue 

 

Instance activity Statistics & Wait event Statistics

SELECT * FROM V$STATNAME;
SELECT * FROM V$SYSSTAT;
SELECT * FROM V$SESSTAT;

SELECT * FROM V$SERVICE_STATS;

SELECT * FROM V$EVENT_NAME;
SELECT * FROM V$SYSTEM_EVENT;
SELECT * FROM V$SESSION_EVENT;

SELECT * FROM V$SERVICE_EVENT;

 

SELECT * FROM V$SYSTEM_WAIT_CLASS;
SELECT * FROM V$SESSION_WAIT_CLASS;
SELECT * FROM V$SERVICE_WAIT_CLASS;

 

Diagnostic:

SELECT * FROM V$DIAG_INFO;