工程日志1-ORACLEsql语句调优.docVIP

（工程部）工程日志 填报日期：2013／12／02 客户名称 产品名称 Sql语句的调优 参与人员 梁旭 到达现场 项目环境 操作系统 Linux 主机名 Rh55 数据库版本 oracle 10.2.0.1.0 实例名 Prod Sql语句的调优 应用开发范式 开发范式1：不要轻易把字段嵌入到表达式（把建立索引的字段嵌入到表达式，会发生全表扫描） *创建表emp1，并为hiredate列创建索引 SCOTT@ prodcreate table emp1 as select * from emp; Table created. SCOTT@ prodcreate index emp1_hiredate_ind on emp1(hiredate); Index created. *把字段嵌入到表达式的查询表，生成执行计划 SCOTT@ prodselect * from emp1 where hiredate + 7 sysdate; 14 rows selected. Elapsed: 00:00:00.04 Execution Plan Plan hash value: 2226897347 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP1 | 14 | 1218 | 3 (0)| 00:00:01 | Predicate Information (identified by operation id): 1 - filter(INTERNAL_FUNCTION(HIREDATE)+7SYSDATE@!) Note - dynamic sampling used for this statement Statistics 168 recursive calls 0 db block gets 24 consistent gets 1 physical reads 0 redo size 1357 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed *改写sql语句，查询表生成执行计划 SCOTT@ prodselect * from emp1 where hiredatesysdate-7; 14 rows selected. Execution Plan Plan hash value: 2842187202 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP1 | 14 | 1218 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMP1_HIREDATE_IND | 14 | | 1 (0)| 00:00:01 | Predicate Information (identified by operation id): 2 - access(HIREDATESYSDATE@!-7) Note - dynamic sampling used for this statement Statistics 9 recursive calls 0 db block gets 10 consistent gets 0 physical reads