oracle优化思路课件.ppt

* 10G版本之后监控语句： select sql_text,event,sid,serial#,program,machine,b.seconds_in_wait from v$sql a,v$session b where a.sql_id = b.sql_id and a.hash_value = b.sql_hash_value and b.event not in (SQL*Net message from client, gcs remote message,SQL*Net message to client, rdbms ipc message, ges remote message) and sql_text not like select b.inst_id,sql_text,event,sid,serial#,program,machine from gv$sql% order by event; * 目前金三系统主要是IO压力大: direct path read 、db file scattered read、db file sequential read 在10g中，对于大表的串行扫描默认是通过缓存的，在11g中，决定是直接读取还是通过缓存读取，要基于表大小、缓冲区大小和其他多种统计信息。因为避免了闩锁（Latche），因此直接路径读取比离散读（Scattered Read）更快，对其他进程影响更小。在全表扫描的时候，如果ORACLE认为这个表足够大，就会启用直接路径读取，而不需要像11G之前，必须先把数据加载到buffer cache。 cpu高一般是内存消耗严重，逻辑读比较高引起的 I/O高一般是物理读 * 索引快速全扫描是以多快读的方式来进行，无序， 出现db file scattered read等待事件。 --是否需要创建索引或者分区？ 索引全扫描，单快读，会消耗大量IO，有序，出现db file sequential read等待事件。 --是否走全表扫描比走索引快？ * 真实的执行计划 查看真实的执行计划 1) select * from table(dbms_xplan.display_cursor(aztjhk6x2fzmx,0,ADVANCED)); --可以看到绑定变量的值 2) select * from table(dbms_xplan.display_cursor(null,null,ADVANCED)); 3) select * from table(dbms_xplan.display_awr(sql_id)); 查看历史sql的执行计划，已经不在内存中 4)Explain plan for … Select * from table(dbms_xplan.display); 查看的执行计划不准确,因为sql并没有真正执行 * select row_.*, rownum from (select sum(a.BNSR) as D26, sum(a.BNSR_SJGD + a.BNSR_ZY_SJGD) as D17, sum(a.BNSR_QXJ) as D10, sum(a.BNSR_DSJ) as D9, sum(a.BNSR_SX) as D8, sum(a.BNSR_SJGX) as D7, sum(a.BNSR_SJGD) as D6, sum(a.BNSR_SJ) as D5, sum(a.BYSR) as C26, sum(a.BYSR_SJGD + a.BYSR_ZY_SJGD) as C17, sum(a.BYSR_QXJ) as C10, sum(a.BYSR_DSJ) as C9, sum(a.BYSR_SX) as C8, sum(a.BYSR_SJGX) as C7, sum(a.BYSR_SJGD) as C6, sum(a.BYSR_SJ) as C5, a.TJRQ as aDM2ZSTJBBSSRKBTJRQ, a.ZSXM_DM as aDM2ZSTJBBSSRKB8