首页 / ORACLE / Oracle性能优化
Oracle性能优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle性能优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含66655字，纯文字阅读大概需要96分钟。
内容图文
SQL共享原理
ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了SQL的执行性能并节省了内存的使用。
为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA(systemglobal area)的共享池(shared buffer poo1)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用。
可惜的是，Oracle只对简单的表提供高速缓冲(cache bufferiIlg)，这个功能并不适用于多表连接查询。数据库管理员必须在启动参数文件中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle会首先在这块内存中查找相同的语句。
要使用内存中共享池的SQL，必须满足以下条件：当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空格、换行等），两个语句所指的对象必须完全相同 （同义词与表是不同的对象）两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 。Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句必须完全相同(包括空格、换行等)。
能够使用共享的语句必须满足三个条件：
① 字符级的比较。
当前被执行的语句和共享池中的语句必须完全相同。
例如： SELECT * FROM ATABLE;和下面每一个SQL语句都不同：
SELECT *from ATABLE;
Select * From Atable;
② 语句所指对象必须完全相同 即两条SQL语句操作的数据库对象必须同一。
③语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变量以相同的值：
●第一组 select pin，name from people where pin = ：blk1.pin；
select pin，name from people where pin =：blk1.pin；
●第二组 select pin，name from people where pin =：blk1.ot_jnd；
select pin，name from people where pin = ：blk1.ov_jnd；
为什么要绑定变量？
下面这个语句每执行一次就需要在SHARE POOL 硬解析一次，一百万用户就是一百万次，消耗CPU和内存，如果业务量大，很可能导致宕库……
如果绑定变量，则只需要硬解析一次，重复调用即可。
select * from dConMsg where contract_no = 32013484095139
硬解析即整个SQL语句的执行需要完完全全的解析，生成执行计划。而硬解析，生成执行计划需要耗用CPU资源，以及SGA资源。在此不得不提的是对库缓存中闩的使用。闩是锁的细化，可以理解为是一种轻量级的串行化设备。当进程申请到闩后，则这些闩用于保护共享内存的数在同一时刻不会被两个以上的进程修改。在硬解析时，需要申请闩的使用，而闩的数量在有限的情况下需要等待。大量的闩的使用由此造成需要使用闩的进程排队越频繁，性能则逾低下。
绑定变量解决硬解析问题
未使用绑定变量的语句
sprintf(sqlstr, "insert into scott.test1 (num1, num2) values (%d,%d)",n_var1, n_var2);
EXEC SQL EXECUTE IMMEDIATE :sqlstr ;
EXEC SQL COMMIT;
使用绑定变量的语句
strcpy(sqlstr, "insert into test (num1, num2) values (:v1, :v2)");
EXEC SQL PREPARE sql_stmt FROM :sqlstr;
EXEC SQL EXECUTE sql_stmt USING :n_var1, :n_var2;
EXEC SQL COMMIT;
SQL优化
SQL优化的一般性原则
目标：
- 减少服务器资源消耗（主要是磁盘IO）；
设计方面：
- 尽量依赖oracle的优化器，并为其提供条件；
- 合适的索引，索引的双重效应，列的选择性；
编码方面：
- 利用索引，避免大表FULL TABLE SCAN；
- 合理使用临时表；
- 避免写过于复杂的sql，不一定非要一个sql解决问题；
- 在不影响业务的前提下减小事务的粒度；
优化概括
- 创建表的时候。应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数；大数据表删除，用truncate table代替delete。
- 合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。
- 查询尽量用确定的列名，少用*号。select count(key)from tab where key> 0性能优于select count(*)from tab；
- 尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多or运算的查询，建议分成多个查询，用union all联结起来；多表查询的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从右到左，所以记录少的表放在右边。
- 尽量多用commit语句提交事务，可以及时释放资源、解锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：alter table．．．cache；
随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。SQL语句的编写原则和SQL语句的优化，请跟我一起学习以下几方面：
- 避免复杂的多表关联
select …
from user_files uf, df_money_files dm,
cw_charge_record cc
where
uf.user_no = dm.user_no
and dm.user_no = cc.user_no
and ……
and not exists(select …)
很难优化，随着数据量的增加性能的风险很大。
- 避免使用 '* '
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法。不幸的是,这是一个常低效的方法。实际上,ORACLE在解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间；只提取你所要使用的列，使用别名能够加快解析速度；
- 避免使用耗费资源的操作
带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作， 而其他的至少需要执行两次排序。
通常, 带有UNION， MINUS， INTERSECT的SQL语句都可以用其他方式重写。
- 用EXISTS替换DISTINCT
例如:
低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT ‘X’
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
- 用UNION-ALL 替换UNION ( if possible)
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.
举例:
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
- 自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．
举例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
- 至少要包含组合索引的第一列
如果索引是建立在多个列上， 只有在它的第一个列(leading column)被where子句引用时，优化器才会选择使用该索引。
SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10));
Table created.
SQL> create index multindex on multiindexusage(inda,indb);
Index created.
SQL> set autotrace traceonly
SQL> select * from multiindexusage where inda = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)
SQL> select * from multiindexusage where indb = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'
很明显，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引。
- 避免在索引列上使用函数
WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
举例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
- 避免使用前置通配符
WHERE子句中， 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始， 索引将不被采用。
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO LIKE '%109204421';
在这种情况下，ORACLE将使用全表扫描。
- 避免在索引列上使用NOT
通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
举例:
低效: (这里,不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE <> 0;
高效: (这里,使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
- 避免在索引列上使用 IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果至少有一个列不为空，则记录存在于索引中．
如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
- 避免出现索引列自动转换
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设EMP_TYPE是一个字符类型的索引列.
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO = 109204421
这个语句被ORACLE转换为:
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE TO_NUMBER(USER_NO) = 109204421
因为内部发生的类型转换, 这个索引将不会被用到!
- 在查询时尽量少用格式转换
如用 WHERE a.order_no = b.order_no不用WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1) ¨= TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1) ?
- 减少访问数据库的次数
当执行每条SQL语句时， ORACLE在内部执行了许多工作，解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。
- 使用DECODE来减少处理时间
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
- 减少对表的查询
在含有子查询的SQL语句中，要特别注意减少对表的查询。
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
- WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,?当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。
如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：
select * from emp e,dept d?
where d.deptno >10 and?e.deptno =30?;?
如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。
select * from emp e,dept d?
where e.deptno = 30 and?d.deptno >10?;
最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。
- Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
- 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。
select * from employss
where
first_name||''||last_name ='Beill Cliton';
系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。
select * from employee
where
first_name ='Beill' and last_name ='Cliton';
- 带通配符（%）的like语句 *
通配符（%）在搜寻词首出现，Oracle系统不使用last_name的索引。
select * from employee where last_name like '%cliton%';
在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：
select * from employee where last_name like 'c%';
- 用Where子句替换HAVING子句
避免使用HAVING子句， HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。
例如:
低效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
高效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
GROUP BY REGION
顺序
WHERE > GROUP > HAVING
- 用NOT EXISTS 替代 NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。
使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。
例如:
¨语句1 ¨ ¨SELECT dname, deptno FROM dept WHEREdeptno NOT IN (SELECT deptno FROM emp);
¨ ¨语句2 ¨ ¨SELECT dname, deptno FROM dept WHERENOT EXISTS
(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
¨2要比1的执行性能好很多。
因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。
- *用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。
通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。
- 避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分．优化器将不
使用索引而使用全表扫描．
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
- 用>= 替代 >
如果DEPTNO上有一个索引。
高效:
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效:
SELECT *
FROM EMP
WHERE DEPTNO >3
- 通过使用>=、<=等，避免使用NOT命令
例子：
¨select * from employee where?salary <> 3000;对这个查询，可以改写为不使用NOT： ¨select * from employee where?salary<3000 or salary>3000;虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
- 如果有其它办法，不要使用子查询
- 外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+"，可以替代效率十分低下的 not in 运算，大大提高运行速度。例如，下面这条命令执行起来很慢：
- 尽量多使用COMMIT
事务是消耗资源的，大事务还容易引起死锁
COMMIT所释放的资源:
- 回滚段上用于恢复数据的信息.
- 被程序语句获得的锁
- redo log buffer 中的空间
- ORACLE为管理上述3种资源中的内部花费
- 用TRUNCATE替代DELETE
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)
而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。
- 计算记录条数
和一般的观点相反, count(*比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的。
例如 COUNT(EMPNO)
- 字符型字段的引号
比如有的表PHONE_NO字段是CHAR型,而且创建有索引，
但在WHERE条件中忘记了加引号，就不会用到索引。
WHERE PHONE_NO=‘13920202022’
WHERE PHONE_NO=13920202022
SQL语句的处理过程
1. DML语句处理(insert, update, delete)
每种类型的语句都需要如下阶段：¨? 第1步: Create a Cursor 创建游标 ¨? 第2步: Parse the Statement 分析语句 ¨? 第5步: Bind Any Variables 绑定变量 ¨? 第7步: Run the Statement 运行语句 ¨? 第9步: Close the Cursor 关闭游标 如果使用了并行功能，还会包含下面这个阶段： ¨? 第6步: Parallelize the Statement 并行执行语句 如果是查询语句，则需要几个额外的步骤： ¨? 第3步: Describe Results of a Query 描述查询的结果集 ¨? 第4步: Define Output of a Query 定义查询的输出数据 ¨? 第8步: Fetch Rows of a Query 取查询出来的行
第1步: 创建游标(Create a Cursor) ¨由程序接口调用创建一个游标（cursor）。任何SQL语句都会创建它，特别在运行DML语句时，都是自动创建游标的，不需要开发人员干预。多数应用中，游标的创建是自动的。然而，在预编译程序(pro*c)中游标的创建，可能是隐含的，也可能显式的创建。在存储过程中也是这样的。
第2步:分析语句(Parse the Statement)
在语法分析期间，SQL语句从用户进程传送到Oracle，SQL语句经语法分析后，SQL语句本身与分析的信息都被装入到共享SQL区。在该阶段中，可以解决许多类型的错误。
语法分析分别执行下列操作：
- 翻译SQL语句，验证它是合法的语句，即书写正确
- 实现数据字典的查找，以验证是否符合表和列的定义
- 在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义
- 验证为存取所涉及的模式对象所需的权限是否满足
- 决定此语句最佳的执行计划
- 将它装入共享SQL区
- 对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点
以上任何一步出错误，都将导致语句报错，中止执行。
- 只有在共享池中不存在等价SQL语句的情况下，才对SQL语句作语法分析。在这种情况下，数据库内核重新为该语句分配新的共享SQL区，并对语句进行语法分析。进行语法分析需要耗费较多的资源，所以要尽量避免进行语法分析，这是优化的技巧之一。
- 语法分析阶段包含了不管此语句将执行多少次，而只需分析一次的处理要求。Oracle只对每个SQL语句翻译一次，在以后再次执行该语句时，只要该语句还在共享SQL区中，就可以避免对该语句重新进行语法分析，也就是此时可以直接使用其对应的执行计划对数据进行存取。这主要是通过绑定变量(bind variable)实现的，也就是我们常说的共享SQL，后面会给出共享SQL的概念。
- 虽然语法分析验证了SQL语句的正确性，但语法分析只能识别在SQL语句执行之前所能发现的错误(如书写错误、权限不足等)。因此，有些错误通过语法分析是抓不到的。例如，在数据转换中的错误或在数据中的错（如企图在主键中插入重复的值）以及死锁等均是只有在语句执行阶段期间才能遇到和报告的错误或情况。
查询与其它类型的SQL语句不同，因为在成功执行后作为结果将返回数据。
第3步: 描述查询结果(Describe Results of a Query)
描述阶段只有在查询结果的各个列是未知时才需要；例如，当查询由用户交互地输入需要输出的列名。在这种情况要用描述阶段来决定查询结果的特征（数据类型，长度和名字）。
第4步: 定义查询的输出数据(Define Output of a Query)
在查询的定义阶段，你指定与查询出的列值对应的接收变量的位置、大小和数据类型，这样我们通过接收变量就可以得到查询结果。如果必要的话，Oracle会自动实现数据类型的转换。这是将接收变量的类型与对应的列类型相比较决定的。
第5步: 绑定变量(Bind Any Variables)
- Oracle知道了SQL语句的意思，但仍没有足够的信息用于执行该语句。Oracle 需要得到在语句中列出的所有变量的值。在该例中，Oracle需要得到对department_id列进行限定的值。得到这个值的过程就叫绑定变量(binding variables)
- 此过程称之为将变量值捆绑进来。程序必须指出可以找到该数值的变量名（该变量被称为捆绑变量，变量名实质上是一个内存地址，相当于指针）。应用的最终用户可能并没有发觉他们正在指定捆绑变量，因为Oracle 的程序可能只是简单地指示他们输入新的值，其实这一切都在程序中自动做了。
- 因为你指定了变量名，在你再次执行之前无须重新捆绑变量。你可以改变绑定变量的值，而Oracle在每次执行时，仅仅使用内存地址来查找此值。
- 如果Oracle 需要实现自动数据类型转换的话（除非它们是隐含的或缺省的），你还必须对每个值指定数据类型和长度。关于这些信息可以参考oracle的相关文档，如Oracle Call Interface Programmer's Guide
第6步: 并行执行语句(Parallelize the Statement ) ¨ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs语句中执行相应并行查询操作，对某些DDL操作，如创建索引、用子查询创建表、在分区表上的操作，可以执行并行操作。并行化可导致多个服务器进程(oracle server processes)为同一个SQL语句工作，使该SQL语句可以快速完成，但是会耗费更多的资源，所以除非很有必要，否则不要使用并行查询。
第7步: 执行语句(Run the Statement) ¨此时，Oracle拥有所有需要的信息与资源，可以真正运行SQL语句了。如果该语句为SELECT查询或INSERT语句，则不需要锁定任何行，因没有数据需要被改变。如果语句为UPDATE或DELETE语句，则该语句影响的所有行都被锁定，防止该用户提交或回滚之前，别的用户对这些数据进行修改。这保证了数据的一致性。 ¨对于某些语句，你可以指定执行的次数，这称为批处理(array processing)。指定执行N次，则绑定变量与定义变量被定义为大小为N的数组的开始位置，这种方法可以减少网络开销，也是优化的技巧之一。
第8步: 取出查询的行(Fetch Rows of a Query)
¨在fetch阶段，行数据被取出来，每个后续的存取操作检索结果集中的下一行数据，直到最后一行被取出来。上面提到过，批量的fetch是优化的技巧之一。第9步: 关闭游标(Close the Cursor)SQL语句处理的最后一个阶段就是关闭游标。
2. DDL 语句处理(create .. , drop .. , alter .. , )(insert, update, delete)
DDL语句的执行不同与DML语句和查询语句的执行，这是因为DDL语句执行成功后需要对数据字典数据进行修改。对于DDL语句，语句的分析阶段包括：分析、查找数据字典信息和执行。事务管理语句、会话管理语句、系统管理语句只有分析与执行阶段，为了重新执行该语句，会重新分析与执行该语句。?Oralce优化器
Oracle的优化器共有3种模式：RULE (基于规则)、COST(基于成本)、CHOOSE(基于选择)。
设置缺省的优化器的方法，是在启动参数文件中针对OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语句级别或是会话级别对其进行覆盖。
为了使用基于成本的优化器(CBO，Cost—Based Optimizer)，必须经常运行analyze命令，以增加数据库中的对象统计信息(object statistics)的准确性。
如果数据库的优化器模式设置为基于选择，那么实际的优化器模式将和是否运行过analyze命令有关。如果数据表已经被analyze过，优化器模式将自动切换成CBO，反之，数据库将采用RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化器。为避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。
2 种类型的优化器：
基于规则的优化器
基于代价的优化器。
不同之处：取得代价的方法与衡量代价的大小不同。
基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO)
基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。例如，如果一个查询的where条件(where clause)包含一个谓词(predicate，其实就是一个判断条件，如”=”, “>”, ”<”等)，而且该谓词上引用的列上有有效索引，那么优化器将使用索引访问这个表，而不考虑其它因素，如表中数据的多少、表中数据的易变性、索引的可选择性等。此时数据库中没有关于表与索引数据的统计性描述，如表中有多上行，每行的可选择性等。优化器也不考虑实例参数，如multi block i/o、可用排序内存的大小等，所以优化器有时就选择了次优化的计划作为真正的执行计划，导致系统性能不高。如，对于 select * from emp where deptno = 10;如果是使用基于规则的优化器，而且deptno列上有有效的索引，则会通过deptno列上的索引来访问emp表。在绝大多数情况下，这是比较高效的，但是在一些特殊情况下，使用索引访问也有比较低效的时候，现举例说明： 1) emp表比较小，该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引访问emp表反而要好。因为表比较小，极有可能数据全在内存中，所以此时做全表扫描是最快的。而如果使用索引扫描，需要先从索引中找到符合条件记录的rowid，然后再一一根据这些rowid从emp中将数据取出来，在这种条件下，效率就会比全表扫描的效率要差一些。2) emp表比较大时，而且deptno = 10条件能查询出表中大部分的数据如(50%)。如该表共有4000万行数据，共放在有500000个数据块中，每个数据块为8k，则该表共有约4G，则这么多的数据不可能全放在内存中，绝大多数需要放在硬盘上。此时如果该查询通过索引查询，则是你梦魇的开始。db_file_multiblock_read_count参数的值200。如果采用全表扫描，则需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引扫描，假设deptno列上的索引都已经cache到内存中，所以可以将访问索引的开销忽略不计。因为要读出4000万x 50% = 2000万数据，假设在读这2000万数据时，有99.9%的命中率，则还是需要20000次I/O,比上面的全表扫描需要的2500次多多了，所以在这种情况下，用索引扫描反而性能会差很多。在这样的情况下，用全表扫描的时间是固定的，但是用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。
基于代价的优化器 -- Cost Based Optimization(简称CBO)
Oracle把一个代价引擎(Cost Engine)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优的执行计划。查询耗费的资源可以被分成3个基本组成部分：I/O代价、CPU代价、network代价。
¨I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中，在一般情况下，该代价是处理一个查询所需要的最主要代价，所以我们在优化时，一个基本原则就是降低查询所产生的I/O总次数。 ¨CPU代价是处理在内存中数据所需要的代价，如一旦数据被读入内存，则我们在识别出我们需要的数据后，在这些数据上执行排序(sort)或连接(join)操作，这需要耗费CPU资源。对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说，存在network代价，用来量化传输操作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。?判断当前数据库使用何种优化器 由optimizer_mode初始化参数决定的。该参数可能的取值为：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule。具体解释如下： ¨RULE为使用RBO优化器。 ¨CHOOSE则是根据实际情况，如果数据字典中包含被引用的表的统计数据，即引用的对象已经被分析，则就使用CBO优化器，否则为RBO优化器。 ¨ALL_ROWS为CBO优化器使用的第一种具体的优化方法，是以数据的吞吐量为主要目标，以便可以使用最少的资源完成语句。 ¨FIRST_ROWS为优化器使用的第二种具体的优化方法，是以数据的响应时间为主要目标，以便快速查询出开始的几行数据。 ¨FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法，让优化器选择一个能够把响应时间减到最小的查询执行计划，以迅速产生查询结果的前 n 行。该参数为ORACLE 9I新引入的。 ¨查看命令：show parameter OPTIMIZER_MODE
Oracle执行计划
什么是优化： 优化是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句（SELECT,INSERT,UPDATE或DELETE）中的一个重要步骤。对Oracle来说，执行这样的语句有许多不同的方法，譬如说，将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定语句能执行得有多快。Oracle中称之为优化器（Optimizer）的组件用来选择这种它认为最有效的执行计划。共享SQL语句
为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池(通过设置shared buffer pool参数值)和尽可能的使用绑定变量的方法执行SQL语句。
Rowid的概念
rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除该列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。
为什么使用Rowid
rowid对访问一个表中的给定的行提供了最快的访问方法，通过ROWID可以直接定位到相应的数据块上，然后将其读到内存。我们创建一个索引时，该索引不但存储索引列的值，而且也存储索引值所对应的行的ROWID，这样我们通过索引快速找到相应行的ROWID后，通过该ROWID，就可以迅速将数据查询出来。这也就是我们使用索引查询时，速度比较快的原因。
在ORACLE8以前的版本中，ROWID由FILE 、BLOCK、ROW NUMBER构成。随着oracle8中对象概念的扩展，ROWID发生了变化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER构成。利用DBMS_ROWID可以将rowid分解成上述的各部分，也可以将上述的各部分组成一个有效的rowid。
Row Source(行源)
用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。
Predicate(谓词)
一个查询中的WHERE限制条件。Driving Table(驱动表)
该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动行源(driving row source)更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有限制条件(如等值限制)，则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source，后面会给出具体说明。在我们后面的描述中，一般将该表称为连接操作的row source 1。
Probed Table(被探查表)
该表又称为内层表(INNER TABLE)。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表(实际上应该为返回较大row source的表)且相应的列上应该有索引。在我们后面的描述中，一般将该表称为连接操作的row source 2。
组合索引(concatenated index)
由多个列构成的索引，如create index idx_emp on emp(col1, col2, col3, ……)，则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。
可选择性(selectivity)
比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的”唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。
为了执行语句，Oracle可能必须实现许多步骤。这些步骤中的每一步可能是从数据库中物理检索数据行，或者用某种方法准备数据行，供发出语句的用户使用。Oracle用来执行语句的这些步骤的组合被称之为执行计划。执行计划是SQL优化中最为复杂也是最为关键的部分，只有知道了ORACLE在内部到底是如何执行该SQL语句后，才能知道优化器选择的执行计划是否为最优的。执行计划对于DBA来说，就象财务报表对于财务人员一样重要。所以我们面临的问题主要是：
显示下面SQL语句的执行计划。 ¨SELECT ename, job, sal, dname ¨FROM emp, dept ¨WHERE emp.deptno = derpt.deptno ¨AND NOT EXISTS ¨( SELECT * ¨FROM salgrade ¨WHERE emp.sal BETWEEN losal AND hisal );此语句查询薪水不在任何建议薪水范围内的所有雇员的名字，工作，薪水和部门名。 如图显示了上例执行计划的图形表示： 执行计划的步骤
实现执行计划步骤的顺序执行计划中的步骤不是按照它们编号的顺序来实现的：Oracle首先实现上图树结构图形里作为叶子出现的那些步骤(例如步骤3、5、6)。由每一步返回的行称为它下一步骤的行源。然后Oracle实现父步骤。 Oracle以下列顺序实现这些步骤：
1、Oracle实现步骤５，并将结果ROWID返回给第４步。 有时语句执行时，并不是象上面说的那样一步一步有先有后的进行，而是可能并行运行，如在实际环境中，3、5、4步可能并行运行，以便取得更好的效率。从上面的树型图上，是很难看出各个操作执行的先后顺序，而通过ORACLE生成的另一种形式的执行计划，则可以很容易的看出哪个操作先执行，哪个后执行，这样的执行计划是我们真正需要的，后面会给出详细说明。现在先来看一些预备知识。
|
优化器在形成执行计划时需要做的一个重要选择是如何从数据库查询出需要的数据。对于SQL语句存取的任何表中的任何行，可能存在许多存取路径(存取方法)，通过它们可以定位和查询出需要的数据。优化器选择其中自认为是最优化的路径。在物理层，oracle读取数据，一次读取的最小单位为数据库块(由多个连续的操作系统块组成)，一次读取的最大值由操作系统一次I/O的最大值与multiblock参数共同决定，所以即使只需要一行数据，也是将该行所在的数据库块读入内存。逻辑上，oracle用如下存取方法访问数据：
¨1 全表扫描（Full Table Scans, FTS） ¨2 通过ROWID的表存取（Table Access by ROWID或rowid lookup） ¨3 索引扫描（Index Scan或index lookup）全表扫描
为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read_count参数设定)，而非只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以高效实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。由于HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有改善，一般我们需要使用truncate命令来使HWM值归为0。幸运的是oracle 10G后，可以人工收缩HWM的值。由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据被交换出内存。使用FTS的前提：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。 n使用全表扫描的例子： ¨SQL> explain plan for select * from dual; ¨Query Plan ¨----------------------------------------- ¨SELECT STATEMENT [CHOOSE] Cost= ¨TABLE ACCESS FULL DUAL?
通过ROWID的表存取（Table Access by ROWID或rowid lookup）
行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。为通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。此存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们经常在执行计划中看到该存取方法，如通过索引查询数据。使用ROWID存取的方法：
¨SQL> explain plan for select * from dept ¨where rowid = 'AAAAyGAADAAAAATAAF'; ¨Query Plan ¨------------------------------------ ¨SELECT STATEMENT [CHOOSE] Cost=1 ¨TABLE ACCESS BY ROWID DEPT [ANALYZED]?索引扫描（Index Scan或index lookup）
通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，该次i/o只会读取一个数据库块。在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫由2步组成： ¨ (1) 扫描索引得到对应的rowid值。 ¨ (2) 通过找到的rowid从表中读出具体的数据。 每步都是单独的一次I/O，但对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，则其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，极其费时间。所以如果对大表进行索引扫描，取出的数据如果大于总量的5% -- 10%，使用索引扫描会效率下降很多。 如下列所示： ¨SQL> explain plan for select empno, ename from emp ¨where empno=10; ¨Query Plan ¨------------------------------------ ¨SELECT STATEMENT [CHOOSE] Cost=1 ¨TABLE ACCESS BY ROWID EMP [ANALYZED] ¨INDEX UNIQUE SCAN EMP_I1注意TABLE ACCESS BY ROWID EMP部分，这表明这不是通过FTS存取路径访问数据，而是通过rowid lookup存取路径访问数据的。此例中，所需要的rowid是由于在索引查找empno列的值得到的，这种方式是INDEX UNIQUE SCAN查找，后面给予介绍，EMP_I1为使用的进行索引查找的索引名字。 根据索引的类型与where限制条件的不同，有4种类型的索引扫描： １、索引唯一扫描(index unique scan) ２、索引范围扫描(index range scan) ３、索引全扫描(index full scan) ４、索引快速扫描(index fast full scan)
索引唯一扫描(index unique scan) 通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建一个索引：create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where deptno = ‘DEV’语句则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行），Oracle经常实现唯一性扫描。 使用唯一性约束的例子： ¨SQL> explain plan for select empno,ename from emp where empno=10; ¨Query Plan ¨------------------------------------ ¨SELECT STATEMENT [CHOOSE] Cost=1 ¨TABLE ACCESS BY ROWID EMP [ANALYZED] ¨INDEX UNIQUE SCAN EMP_I1
索引范围扫描(index range scan) 使用索引存取多行数据，如果索引是组合索引，如索引唯一扫描所示，且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between) 使用索引范围扫描的例子： ¨ SQL> explain plan for select empno,ename from emp ¨ where empno > 7876 order by empno; ¨ Query Plan ¨ -------------------------------------------------------------------------------- ¨ SELECT STATEMENT [CHOOSE] Cost=1 ¨ TABLE ACCESS BY ROWID EMP [ANALYZED] ¨ INDEX RANGE SCAN EMP_I1 [ANALYZED] 在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况： ¨
索引全扫描(index full scan) ¨与全表扫描对应，也有相应的全索引扫描。在某些情况下，可能进行全索引扫描而不是范围扫描，需要注意的是全索引扫描只在CBO模式下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。全索引扫描的例子： ¨An Index full scan will not perform single block i/o's and so it may prove to be inefficient. ¨e.g. ¨Index BE_IX is a concatenated index on big_emp (empno, ename) ¨SQL> explain plan for select empno, ename from big_emp ¨order by empno,ename; ¨Query Plan ¨-------------------------------------------------------------------------------- ¨SELECT STATEMENT [CHOOSE] Cost=26 INDEX FULL SCAN BE_IX [ANALYZED]?
索引快速扫描(index fast full scan) 扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。索引快速扫描的例子： ¨ BE_IX 索引是一个多列索引：big_emp (empno,ename) ¨ SQL> explain plan for select empno, ename from big_emp; ¨ Query Plan ¨------------------------------------------ ¨SELECT STATEMENT [CHOOSE] Cost=1 ¨INDEX FAST FULL SCAN BE_IX [ANALYZED] ¨只选择多列索引的第2列： ¨SQL> explain plan for select ename from big_emp; ¨Query Plan ¨------------------------------------------ ¨SELECT STATEMENT [CHOOSE] Cost=1INDEX FAST FULL SCAN BE_IX [ANALYZED] Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。Join过程的各个步骤经常是串行操作，即使相关的row source可以被并行访问，即可以并行的读取做join连接的两个row source的数据，但是在将表中符合限制条件的数据读入到内存形成row source后，join的其它步骤一般是串行的。有多种方法可以将2个表连接起来，当然每种方法都有自己的优缺点，每种连接类型只有在特定的条件下才会发挥出其最大优势。
row source(表)之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表，即将该表作为驱动表，这样可以先应用某些限制条件，从而得到一个较小的row source，使连接的效率较高，这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时，应用where子句中对该表的限制条件。
根据2个row source的连接条件的中操作符的不同，可以将连接分为等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。下面以等值连接为例进行介绍。在后面的介绍中，都已： ¨ SELECT A.COL1, B.COL2 ¨ FROM A, B ¨ WHERE A.COL3 = B.COL4; 为例进行说明，假设A表为Row Soruce1，则其对应的连接操作关联列为COL 3；B表为Row Soruce2，则其对应的连接操作关联列为COL 4；
连接类型： 1. 排序 - - 合并连接 (Sort Merge Join (SMJ) ) 2. 嵌套循环 (Nested Loops (NL) ) 3. 哈希连接 (Hash Join) 4.笛卡儿乘积(Cartesian Product)?
排序 - - 合并连接(Sort Merge Join (SMJ) ) 内部连接过程： 1. 首先生成row source1需要的数据，然后对这些数据按照连接操作关联列(如A.col3)进行排序。 2. 随后生成row source2需要的数据，然后对这些数据按照与sortsource1对应的连接操作关联列(如B.col4)进行排序。 3. 最后两边已排序的行被放在一起执行合并操作，即将2个rowsource按照连接条件连接起来。
!8431bb36e2fa4647aa490996eeaee021.jpg!如果row source已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其费资源的操作， 特别是对于较大的表。 预先排序的row source包括已经被索引的列(如a.col3或b.col4上有索引)或row source已经在前面的步骤中被排序了。尽管合并两个row source的过程是串行的，但是可以并行访问这两个row source(如并行读入数据，并行排序).排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个row source都已经预先排序，则这种连接方法的效率较高。
SMJ连接的例子：
SQL> explain plan for
select /*+ ordered */ e.deptno, d.deptno
from emp e, dept d
where e.deptno = d.deptno
order by e.deptno, d.deptno;
Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]
嵌套循环(Nested Loops (NL) )
该连接过程是一个2层嵌套循环，则外层循环的次数越少越好，这也就是我们为什么将小表或返回较小row source的表作为驱动表(用于外层循环)的理论依据。但是这个理论只是一般指导原则，因为遵循这个理论并不能总保证使语句产生的I/O次数最少。有时不遵守这个理论依据，反而会获得更好的效率。如果使用这种方法，决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确，将会导致语句的性能很差、很差。 内部连接过程： ¨Row source1的Row 1 -------------- -- Probe -> Row source 2 ¨Row source1的Row 2 -------------- -- Probe -> Row source 2 ¨Row source1的Row 3 -------------- -- Probe -> Row source 2 ¨……. ¨Row source1的Row n -------------- -- Probe -> Row source 2从内部连接过程来看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题。这只是理论指导原则，目的是使整个连接操作产生最少的物理I/O次数，而且如果遵守这个原则，一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则，反而能用更少的物理I/O实现连接操作，那尽管违反指导原则吧！因为最少的物理I/O次数才是我们应该遵从的真正的指导原则，在后面的具体案例分析中就给出这样的例子。 在上面的连接过程中，我们称Row Source1为驱动表或外部表。Row Source2被称为被探查表或内部表。在NESTED LOOPS连接中，Oracle读取row source1中的每一行，然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理row source1中的下一行。这个过程一直继续，直到row source1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。如果不使用并行操作，最好的驱动表是那些应用了where 限制条件后，可以返回较少行数据的的表，所以大表也可能称为驱动表，关键看限制条件。对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并行功能。当然，有时对查询使用并行操作并不一定会比查询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU，多个硬盘控制器)，所以要具体问题具体对待。NL连接的例子：
SQL> explain plan for
select a.dname, b.sql
from dept a, emp b
where a.deptno = b.deptno;
Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
NESTED LOOPS
TABLE ACCESS FULL DEPT [ANALYZED]
TABLE ACCESS FULL EMP [ANALYZED]
哈希连接 (Hash Join, HJ)
理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。较小的row source被用来构建hash table与bitmap，第2个row source被用来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动表，当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。要使哈希连接有效，需要设置HASH_JOIN_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置hash_area_size参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低。
HASH连接的例子：
SQL> explain plan for
select /*+ use_hash(emp) */ empno
from emp, dept
where emp.deptno = dept.deptno;
Query Plan
----------------------------
SELECT STATEMENT [CHOOSE] Cost=3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP
笛卡儿乘积(Cartesian Product)
当两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡儿乘积，这通常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，除此之外，我们要尽量避免使用笛卡儿乘积。注意在下面的语句中，在2个表之间没有连接。
¨SQL> explain plan for ¨select emp.deptno, dept,deptno ¨from emp,dept ¨Query Plan ¨------------------------------ ¨SLECT STATEMENT [CHOOSE] Cost=5 ¨MERGE JOIN CARTESIAN ¨TABLE ACCESS FULL DEPT ¨SORT JOIN ¨TABLE ACCESS FULL EMPCARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行，dept表有m行，笛卡尔乘积的结果就是得到n * m行结果。?在哪种情况下用哪种连接方法比较好：
1.排序 - - 合并连接(Sort Merge Join, SMJ)：
ü 对于非等值连接，这种连接方式的效率是比较高的。 ü 如果在关联的列上都有索引，效果更好。 ü 对将2个较大的row source做连接，该连接方法比NL连接要好些。 ü 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。 2.嵌套循环(Nested Loops, NL)： ü 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。 NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经 连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。 3.哈希连接(Hash Join, HJ)： ü 此方法是在oracle7后来引入的，使用了比较先进的连接理论，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能 取得较好的性能。 ü 在2个较大的row source之间连接时会取得相对较好的效率，在一个 row source较小时则能取得更好的效率。 ü 只能用于等值连接中。 要为一个语句产生执行计划，有三种方法，我们这里只介绍最简单的方法：在plsql中新建Explain Plan Window，然后将要产生执行计划的sql放到该窗口中执行，或者在普通的SQL Window中选择sql语句，按F5也可以产生执行计划窗口。需要注意的是，以上方法并不会真正执行sql，只是产生执行计划。
通过如下示例进行分析演示：
例1：
假设LARGE_TABLE是一个较大的表，且username列上没有索引，则运行下面的语句：
SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;
Query Plan -----------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]
在这个例子中，TABLE ACCESS FULL LARGE_TABLE是第一个操作，意思是在LARGE_TABLE表上做全表扫描。当这个操作完成之后，产生的row source中的数据被送往下一步骤进行处理，在此例中，SELECT STATEMENT 操作是这个查询语句的最后一步。Optimizer=CHOOSE 指明这个查询的optimizer_mode，即optimizer_mode 初始化参数指定的值，它并不是指语句执行时真的使用了该优化器。决定该语句使用何种优化器的唯一方法是看后面的cost部分。如果给出的是下面的形式，则表明使用的是CBO优化器，此处的cost表示优化器认为该执行计划的代价:
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14) 假如执行计划中给出的是类似下面的信息，则表明是使用RBO优化器，因为cost部分的值为空，或者压根就没有cost部分。
¨ SELECT STATEMENT Optimizer=CHOOSE Cost= ¨ SELECT STATEMENT Optimizer=CHOOSE ¨ 这样我们从Optimizer后面的信息中可以得出执行该语句时到底用了什么样的优化器。特别的，如果Optimizer=ALL_ROWS|FIRST_ROWS| FIRST_ROWS_n，则使用的是CBO优化器； 如果Optimizer=RULE，则使用的是RBO优化器。cost属性的值是一个在oracle内部用来比较各个执行计划所耗费代价的值，从而使优化器可以选择最好的执行计划。不同语句的cost值不具有可比性，只能对同一个语句的不同执行计划的cost值进行比较。[:Q65001] 表明该部分查询是以并行方式运行的。里面的数据表示这个操作是由并行查询的一个slave进程处理的，以便该操作可以区别于串行执行的操作。[ANALYZED] 表明操作中引用的对象被分析过了，在数据字典中有该对象的统计信息可以供CBO使用。?例2：
假定A、B、C都是不是小表，且在A表上一个组合索引：A(a.col1,a.col2) ，注意a.col1列为索引的引导列。
select A.col4
from A , B , C
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
6 consistent gets
0 physical reads
34
0 redo size
551 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed
在表做连接时，只能2个表先做连接，然后将连接后的结果作为一个row source，与剩下的表做连接，在上面的例子中，连接顺序为B与A先连接，然后再与C连接：
B <---> A <---> C
col3=10 col3=5如果没有执行计划，分析一下，上面的3个表应该拿哪一个作为第一个驱动表？从SQL语句看来，只有B表与C表上有限制条件，所以第一个驱动表应该为这2个表中的一个，到底是哪一个呢？B表有谓词B.col3 = 10，这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3 = 10)用上，从而得到一个较小的row source, 所以B表应该作为第一个驱动表。而且这样的话，如果再与A表做关联，可以有效利用A表的索引(因为A表的col1列为leading column)。当然上面的查询中C表上也有谓词(C.col3 = 5)，有人可能认为C表作为第一个驱动表也能获得较好的性能。让我们再来分析一下：如果C表作为第一个驱动表，则能保证驱动表生成很小的row source，但是看看连接条件A.col2 = C.col2，此时就没有机会利用A表的索引，因为A表的col2列不为leading column，这样nested loop的效率很差，从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。 基于以上原则：上面查询比较好的连接顺序为(B - - > A) - - > C。如果数据库是基于代价的优化器，它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说，CBO都会选择正确的连接顺序，如果CBO选择了比较差的连接顺序，我们还可以使用ORACLE提供的hints来让CBO采用正确的连接顺序。如下所示：
¨ select /*+ ordered */ A.col4 ¨ from B,A,C ¨ where B.col3 = 10 ¨ and A.col1 = B.col1 ¨ and A.col2 = C.col2 ¨ and C.col3 = 5 n通过执行计划如何判断驱动表：在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。具体解释如下：
得到去除妨碍判断的索引扫描后的执行计划：
¨Execution Plan ¨----------------------------------------------------------------------------------------------- ¨0 SELECT STATEMENT Optimizer=CHOOSE ¨1 0 MERGE JOIN ¨2 1 SORT (JOIN) ¨3 2 NESTED LOOPS ¨4 3 TABLE ACCESS (FULL) OF 'B' ¨5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A' ¨7 1 SORT (JOIN) ¨8 7 TABLE ACCESS (FULL) OF 'C' 通过执行计划如何判断驱动表：谈论上下关系时，只对连续的、缩进一致的行有效。
从这个图中我们可以看到，对于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS (FULL) OF 'B'，所以这一操作先执行，所以该操作对应的B表为第一个驱动表(外部表)，自然，A表就为内部表了。从图中还可以看出，B与A表做嵌套循环后生成了新的row source ，对该row source进行来排序后，与C表对应的排序了的row source(应用了C.col3 = 5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实：B表先与A表做嵌套循环，然后将生成的row source与C表做排序—合并连接。
通过分析上面的执行计划，我们不能说C表一定在B、A表之后才被读取，事实上，B表有可能与C表同时被读入内存，因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的，因为ORACLE读取数据时，如果就是需要一行数据也是将该行所在的整个数据块读入内存，而且还有可能为多块读。
看执行计划时，我们的关键不是看哪个操作先执行，哪个操作后执行，而是关键看表之间连接的顺序(如得知哪个为驱动表，这需要从操作的顺序进行判断)、使用了何种类型的关联及具体的存取路径(如判断是否利用了索引) 在从执行计划中判断出哪个表为驱动表后，根据我们的知识判断该表作为驱动表(就像上面判断ABC表那样)是否合适，如果不合适，对SQL语句进行更改，使优化器可以选择正确的驱动表。
使用hints提示：基于代价的优化器在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。例如: 对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们可以指示优化器使用全表扫描。在ORACLE中，是通过为语句添加hints(提示)来实现干预优化器优化的目的。
hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。可以用hints来实现：
¨1) 使用的优化器的类型 ¨2) 基于代价优化器的优化目标，是all_rows还是first_rows。 ¨3) 表的访问路径，是全表扫描、索引扫描，还是直接利用rowid。 ¨4) 表之间的连接类型 ¨5) 表之间的连接顺序 ¨6) 语句的并行程度 关于hints的介绍到此为止，大家有兴趣自己去了解。?SQL优化的一般性原则
目标：
- 减少服务器资源消耗（主要是磁盘IO）；
设计方面：
- 尽量依赖oracle的优化器，并为其提供条件；
- 合适的索引，索引的双重效应，列的选择性；
编码方面：
- 利用索引，避免大表FULL TABLE SCAN；
- 合理使用临时表；
- 避免写过于复杂的sql，不一定非要一个sql解决问题；
- 在不影响业务的前提下减小事务的粒度；
优化概括
- 创建表的时候。应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数；大数据表删除，用truncate table代替delete。
- 合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。
- 查询尽量用确定的列名，少用*号。select count(key)from tab where key> 0性能优于select count(*)from tab；
- 尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多or运算的查询，建议分成多个查询，用union all联结起来；多表查询的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从右到左，所以记录少的表放在右边。
- 尽量多用commit语句提交事务，可以及时释放资源、解锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：alter table．．．cache；
随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。SQL语句的编写原则和SQL语句的优化，请跟我一起学习以下几方面：
- 避免复杂的多表关联
select …
from user_files uf, df_money_files dm,
cw_charge_record cc
where
uf.user_no = dm.user_no
and dm.user_no = cc.user_no
and ……
and not exists(select …)
很难优化，随着数据量的增加性能的风险很大。
- 避免使用 '* '
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法。不幸的是,这是一个常低效的方法。实际上,ORACLE在解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间；只提取你所要使用的列，使用别名能够加快解析速度；
- 避免使用耗费资源的操作
带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作， 而其他的至少需要执行两次排序。
通常, 带有UNION， MINUS， INTERSECT的SQL语句都可以用其他方式重写。
- 用EXISTS替换DISTINCT
例如:
低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT ‘X’
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);
- 用UNION-ALL 替换UNION ( if possible)
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.
举例:
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
- 自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．
举例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
- 至少要包含组合索引的第一列
如果索引是建立在多个列上， 只有在它的第一个列(leading column)被where子句引用时，优化器才会选择使用该索引。
SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10));
Table created.
SQL> create index multindex on multiindexusage(inda,indb);
Index created.
SQL> set autotrace traceonly
SQL> select * from multiindexusage where inda = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)
SQL> select * from multiindexusage where indb = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'
很明显，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引。
- 避免在索引列上使用函数
WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
举例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
- 避免使用前置通配符
WHERE子句中， 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始， 索引将不被采用。
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO LIKE '%109204421';
在这种情况下，ORACLE将使用全表扫描。
- 避免在索引列上使用NOT
通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
举例:
低效: (这里,不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE <> 0;
高效: (这里,使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
- 避免在索引列上使用 IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果至少有一个列不为空，则记录存在于索引中．
如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
- 避免出现索引列自动转换
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设EMP_TYPE是一个字符类型的索引列.
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO = 109204421
这个语句被ORACLE转换为:
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE TO_NUMBER(USER_NO) = 109204421
因为内部发生的类型转换, 这个索引将不会被用到!
- 在查询时尽量少用格式转换
如用 WHERE a.order_no = b.order_no不用WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1) ¨= TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1) ?
- 减少访问数据库的次数
当执行每条SQL语句时， ORACLE在内部执行了许多工作，解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。
- 使用DECODE来减少处理时间
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
- 减少对表的查询
在含有子查询的SQL语句中，要特别注意减少对表的查询。
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
- WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,?当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。
如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：
select * from emp e,dept d?
where d.deptno >10 and?e.deptno =30?;?
如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。
select * from emp e,dept d?
where e.deptno = 30 and?d.deptno >10?;
最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。
- Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
- 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。
select * from employss
where
first_name||''||last_name ='Beill Cliton';
系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。
select * from employee
where
first_name ='Beill' and last_name ='Cliton';
- 带通配符（%）的like语句 *
通配符（%）在搜寻词首出现，Oracle系统不使用last_name的索引。
select * from employee where last_name like '%cliton%';
在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：
select * from employee where last_name like 'c%';
- 用Where子句替换HAVING子句
避免使用HAVING子句， HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。
例如:
低效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
高效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
GROUP BY REGION
顺序
WHERE > GROUP > HAVING
- 用NOT EXISTS 替代 NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。
使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。
例如:
¨语句1 ¨ ¨SELECT dname, deptno FROM dept WHEREdeptno NOT IN (SELECT deptno FROM emp);
¨ ¨语句2 ¨ ¨SELECT dname, deptno FROM dept WHERENOT EXISTS
(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
¨2要比1的执行性能好很多。
因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。
- *用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。
通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的。
- 避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分．优化器将不
使用索引而使用全表扫描．
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
- 用>= 替代 >
如果DEPTNO上有一个索引。
高效:
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效:
SELECT *
FROM EMP
WHERE DEPTNO >3
- 通过使用>=、<=等，避免使用NOT命令
例子：
¨select * from employee where?salary <> 3000;对这个查询，可以改写为不使用NOT： ¨select * from employee where?salary<3000 or salary>3000;虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
- 如果有其它办法，不要使用子查询
- 外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+"，可以替代效率十分低下的 not in 运算，大大提高运行速度。例如，下面这条命令执行起来很慢：
- 尽量多使用COMMIT
事务是消耗资源的，大事务还容易引起死锁
COMMIT所释放的资源:
- 回滚段上用于恢复数据的信息.
- 被程序语句获得的锁
- redo log buffer 中的空间
- ORACLE为管理上述3种资源中的内部花费
- 用TRUNCATE替代DELETE
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)
而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。
- 计算记录条数
和一般的观点相反, count(*比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的。
例如 COUNT(EMPNO)
- 字符型字段的引号
比如有的表PHONE_NO字段是CHAR型,而且创建有索引，
但在WHERE条件中忘记了加引号，就不会用到索引。
WHERE PHONE_NO=‘13920202022’
WHERE PHONE_NO=13920202022
内容总结
以上是互联网集市为您收集整理的Oracle性能优化全部内容，希望文章能够帮你解决Oracle性能优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。