Oracle SQL执行计划与优化

落日映苍穹つ 2022-08-23 11:58 242阅读 0赞

*  # 目录 #
    
      
    
 *  系统函数Userenv
 *  ROWID、ROWNUM
 *  分析Oracle索引扫描四大类
 *  DML语句-MERGE
 *  DML语句-CALL
 *  账号与日期的命令
 *  oracle table-lock的5种模式
 *  EXPLAIN PLAN查看相关
 *  EXPLAIN PLAN总结
 *  SQL优化器如何进行评估优化
 *  执行计划稳定性深入研究
 *  Oracle查询优化4大方面的主要途径
 *  SQL优化例子

# 系统函数Userenv #

Oracle中USERENV和SYS\_CONTEXT用来返回当前session的信息，其中，userenv是为了保持向下兼容的遗留函数，推荐使用sys\_context函数调用userenv命名空间来获取相关信息。

1、 USERENV(OPTION)

返回当前的会话信息.

OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.

OPTION='LANGUAGE'返回数据库的字符集.

OPTION='SESSIONID'为当前会话标识符.

OPTION='ENTRYID'返回可审计的会话标识符.

OPTION='LANG'返回会话语言名称的ISO简记.

OPTION='INSTANCE'返回当前的实例.

OPTION='terminal'返回当前计算机名

SELECT USERENV('LANGUAGE') FROM DUAL;

2、sys\_context

select

SYS\_CONTEXT('USERENV','TERMINAL') terminal,

SYS\_CONTEXT('USERENV','LANGUAGE') language,

SYS\_CONTEXT('USERENV','SESSIONID') sessionid,

SYS\_CONTEXT('USERENV','INSTANCE') instance,

SYS\_CONTEXT('USERENV','ENTRYID') entryid,

SYS\_CONTEXT('USERENV','ISDBA') isdba,

SYS\_CONTEXT('USERENV','NLS\_TERRITORY') nls\_territory,

SYS\_CONTEXT('USERENV','NLS\_CURRENCY') nls\_currency,

SYS\_CONTEXT('USERENV','NLS\_CALENDAR') nls\_calendar,

SYS\_CONTEXT('USERENV','NLS\_DATE\_FORMAT') nls\_date\_format,

SYS\_CONTEXT('USERENV','NLS\_DATE\_LANGUAGE') nls\_date\_language,

SYS\_CONTEXT('USERENV','NLS\_SORT') nls\_sort,

SYS\_CONTEXT('USERENV','CURRENT\_USER') current\_user,

SYS\_CONTEXT('USERENV','CURRENT\_USERID') current\_userid,

SYS\_CONTEXT('USERENV','SESSION\_USER') session\_user,

SYS\_CONTEXT('USERENV','SESSION\_USERID') session\_userid,

SYS\_CONTEXT('USERENV','PROXY\_USER') proxy\_user,

SYS\_CONTEXT('USERENV','PROXY\_USERID') proxy\_userid,

SYS\_CONTEXT('USERENV','DB\_DOMAIN') db\_domain,

SYS\_CONTEXT('USERENV','DB\_NAME') db\_name,

SYS\_CONTEXT('USERENV','HOST') host,

SYS\_CONTEXT('USERENV','OS\_USER') os\_user,

SYS\_CONTEXT('USERENV','EXTERNAL\_NAME') external\_name,

SYS\_CONTEXT('USERENV','IP\_ADDRESS') ip\_address,

SYS\_CONTEXT('USERENV','NETWORK\_PROTOCOL') network\_protocol,

SYS\_CONTEXT('USERENV','BG\_JOB\_ID') bg\_job\_id,

SYS\_CONTEXT('USERENV','FG\_JOB\_ID') fg\_job\_id,

SYS\_CONTEXT('USERENV','AUTHENTICATION\_TYPE') authentication\_type,

SYS\_CONTEXT('USERENV','AUTHENTICATION\_DATA') authentication\_data

from dual

# ROWID、ROWNUM #

认识ROWID

SQL> select rowid from dept where rownum < 2;

ROWID各列信息格式如下

数据对象编号        文件编号        块编号           行编号

OOOOOO             FFF               BBBBBB         RRR

简单查看ROWID信息

select rowid ,

substr(rowid,1,6) "OBJECT",

substr(rowid,7,3) "FILE",

substr(rowid,10,6) "BLOCK",

substr(rowid,16,3) "ROW"

from dept

ROWNUM是oracle系统顺序分配为从查询返回的行的编号。

返回的第一行分配的是1，第二行是2，依此类推。

这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。

# 分析Oracle索引扫描四大类 #

学习Oracle时，你可能会遇到Oracle索引扫描问题，这里将介绍Oracle索引扫描问题的解决方法，在这里拿出来和大家分享一下。根据索引的类型与where限制条件的不同，有4种类型的Oracle索引扫描：

◆索引唯一扫描(index unique scan)

◆索引范围扫描(index range scan)

◆索引全扫描(index full scan)

◆索引快速扫描(index fast full scan)

(1) 索引唯一扫描(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

(2) 索引范围扫描(index range scan)

使用一个索引存取多行数据，同上面一样，如果索引是组合索引，如(1)所示，而且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种情况：

(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)

(b) 在组合索引上，只使用部分列进行查询，导致查询出多行

(3) 索引全扫描(index full scan)

与全表扫描对应，也有相应的全Oracle索引扫描。在某些情况下，可能进行全Oracle索引扫描而不是范围扫描，需要注意的是全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

全Oracle索引扫描的例子：

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\]

(4) 索引快速扫描(index fast full scan)

扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。

索引快速扫描的例子：

SQL> explain plan for select empno,ename from big\_emp;

Query Plan

SELECT STATEMENT \[CHOOSE\] Cost=1

INDEX FAST FULL SCAN BE\_IX \[ANALYZED\]

SQL> explain plan for select ename from big\_emp;

Query Plan

SELECT STATEMENT \[CHOOSE\] Cost=1

INDEX FAST FULL SCAN BE\_IX \[ANALYZED\]

# DML语句-CALL #

測試oracle直接call java程式

當初oracle提供這個功能是為了讓java的程式師可方便寫的一些功能在oracle中使用，這樣子也就可不必學pl/sql了

但我暫時還沒有找到效能上的差別是多少的官方資料。

不過有做過就記錄一下，以免以後真的遇到還要上網找資料

1、先寫一個很簡單的java程式 (檔名叫 test.java，請注意大小寫)

public class test

public static void main(String\[\] a) \{

System.out.println("this is java");

2、編譯成class

D:\\>javac test.java

3、查看一下是否ok

D:\\>dir test.\*

磁碟區 D 中的磁碟是 本機磁碟

磁碟區序號: 7098-7819

目錄: D:\\

2007-03-14 11:38a                 414 test.class

2007-03-14 11:37a                 120 test.java

2 個檔案             534 位元組

0 個目錄   9,290,588,160 位元組可用

4、進sqlplus了

D:\\>sqlplus sys/binhu as sysdba

SQL\*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 14 11:41:20 2007

連線至閒置的執行處理.

SQL> startup

ORACLE 執行處理已啟動.

Total System Global Area 201326592 bytes

Fixed Size                  1248092 bytes

Variable Size              92275876 bytes

Database Buffers          104857600 bytes

Redo Buffers                2945024 bytes

資料庫已掛載.

資料庫已開啟.

5、建立相關的別名跟設定

SQL> create or replace directory java\_dir as 'd:\\';

已建立目錄.

SQL> create or replace java class using bfile(java\_dir,'test.class');

2 /

已建立 Java.

SQL> create or replace procedure test\_java as language java name 'test.main(java.lang.String\[\])';

2 /

已建立程序.

6、準備測試了

SQL> set serveroutput on size 2000

SQL> call dbms\_java.set\_output(2000);

已完成呼叫.

SQL> execute test\_java;

this is java

已順利完成 PL/SQL 程序.

SQL> call test\_java();

this is java

已完成呼叫.

心得:

使用上還算簡單，但真正上線的db會有多少人讓java在上面跑呢，因為這又多了一個變數

到時db掛了，還要多花心力去查看是否是由java引起的問題=.="

除非oracle有大力推行跟公司有規定使用的話，不然我還是不會讓java跑在db上的，呵呵

\-bash-3.00$ pwd

/export/home/oracle

\-bash-3.00$ vi test.java

"test.java"［新文件］

public class test

public static void main(String\[\] a) \{

System.out.println("this is java");

\-bash-3.00$ javac test.java

\-bash-3.00$ sqlplus / as sysdba

SQL> create or replace directory java\_dir as '/export/home/oracle';

Directory created.

SQL> create or replace java class using bfile(java\_dir,'test.class');

Java created.

SQL> create or replace procedure test\_java as language java name 'test.main(java.lang.String\[\])';

Procedure created.

SQL> set serveroutput on size 2000

SQL> call dbms\_java.set\_output(2000);

已完成呼叫.

SQL> execute test\_java;

this is java

已順利完成 PL/SQL 程序.

SQL> call test\_java();

this is java

ERROR at line 1:

ORA-29516: Aurora assertion failure: Assertion failure at eox.c:317

Uncaught exception System error:   java/lang/UnsupportedClassVersionError

根据http://edu.136z.com/DataBase/27839.html的解决方法

设置了LD\_LIBRARY\_PATH，

SQL> CALL SGRADE(:sgrade) INTO :output

SQL> print output

调用完全正确

# DML语句-MERGE #

把数据从一个表复制到另一个表，插入新数据或替换掉老数据是每一个ORACLE DBA都会经常碰到的问题。

在ORACLE9i以前的年代，我们要先查找是否存在老数据，如果有用UPDATE替换，否则用INSERT语句插入，其间少不了还有一些标记变量等等，繁琐的很。

现在ORACLE9i专为这种情况提供了MERGE语句，使这一工作变得异常轻松，Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.

Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的

2、UPDATE和INSERT子句可以加WHERE子句

3、ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

首先创建示例表：

create table PRODUCTS

(

PRODUCT\_ID INTEGER,

PRODUCT\_NAME VARCHAR2(60),

CATEGORY VARCHAR2(60)

);

insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');

insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');

insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');

insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');

insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');

commit;

create table NEWPRODUCTS

(

PRODUCT\_ID INTEGER,

PRODUCT\_NAME VARCHAR2(60),

CATEGORY VARCHAR2(60)

);

insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');

insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');

insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');

insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');

commit;

1、可省略的UPDATE或INSERT子句

在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据表NEWPRODUCTS的PRODUCT\_ID字段是否匹配来updates表PRODUCTS的信息:

MERGE INTO products p

USING newproducts np

ON (p.product\_id = np.product\_id)

WHEN MATCHED THEN

UPDATE

SET p.product\_name = np.product\_name,

p.category = np.category

SQL> SELECT \* FROM products;

PRODUCT\_ID PRODUCT\_NAME CATEGORY

---------- -------------------- ----------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS CAMERA ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

SQL>

SQL> ROLLBACK;

Rollback complete.

SQL>

在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行. 它们的产品名字和种 类被更新为表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT\_ID插入到表PRODUCTS中, 对于在两个表中能够匹配上PRODUCT\_ID的数据不作任何处理. 从这个例子你能看到PRODUCT\_ID=1700的行被插入到表PRODUCTS中.

SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p.product\_id = np.product\_id)

4 WHEN NOT MATCHED THEN

5 INSERT

6 VALUES (np.product\_id, np.product\_name,

7 np.category);

1 row merged.

SQL> SELECT \* FROM products;

PRODUCT\_ID PRODUCT\_NAME CATEGORY

---------- -------------------- ----------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS IS50 ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER DVD

1700 WAIT INTERFACE BOOKS

2、带条件的Updates和Inserts子句

你能够添加WHERE子句到UPDATE或INSERT子句中去, 来跳过update或insert操作对某些行的处理. 下面例子根据表NEWPRODUCTS来更新表PRODUCTS数据, 但必须字段CATEGORY也得同时匹配上:

SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p.product\_id = np.product\_id)

4 WHEN MATCHED THEN

5 UPDATE

6 SET p.product\_name = np.product\_name

7 WHERE p.category = np.category;

2 rows merged.

SQL> SELECT \* FROM products;

PRODUCT\_ID PRODUCT\_NAME CATEGORY

---------- -------------------- ----------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS CAMERA ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER DVD

SQL>

SQL> rollback;

在这个例子中, 产品ID为1502,1601和1666匹配ON条件但是1666的category不匹配. 因此MERGE命令只更新两行数据. 下面例子展示了在Updates和Inserts子句都使用WHERE子句:

SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p.product\_id = np.product\_id)

4 WHEN MATCHED THEN

5 UPDATE

6 SET p.product\_name = np.product\_name,

7 p.category = np.category

8 WHERE p.category = 'DVD'

9 WHEN NOT MATCHED THEN

10 INSERT

11 VALUES (np.product\_id, np.product\_name, np.category)

12 WHERE np.category != 'BOOKS'

SQL> /

1 row merged.

SQL> SELECT \* FROM products;

PRODUCT\_ID PRODUCT\_NAME CATEGORY

---------- -------------------- ----------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS IS50 ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

SQL>

注意由于有WHERE子句INSERT没有插入所有不匹配ON条件的行到表PRODUCTS.

3、无条件的Inserts

你能够不用连接源表和目标表就把源表的数据插入到目标表中. 这对于你想插入所有行到目标表时是非常有用的. Oracle 10g现在支持在ON条件中使用常量过滤谓词. 举个常量过滤谓词例子ON (1=0). 下面例子从源表插入行到表PRODUCTS, 不检查这些行是否在表PRODUCTS中存在:

SQL> MERGE INTO products p

2 USING newproducts np

3 ON (1=0)

4 WHEN NOT MATCHED THEN

5 INSERT

6 VALUES (np.product\_id, np.product\_name, np.category)

7 WHERE np.category = 'BOOKS'

SQL> /

1 row merged.

SQL> SELECT \* FROM products;

PRODUCT\_ID PRODUCT\_NAME CATEGORY

---------- -------------------- ----------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS IS50 ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER DVD

1700 WAIT INTERFACE BOOKS

6 rows selected.

SQL>

4、新增加的DELETE子句

Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.

下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行.

SQL> MERGE INTO products p

2 USING newproducts np

3 ON (p.product\_id = np.product\_id)

4 WHEN MATCHED THEN

5 UPDATE

6 SET p.product\_name = np.product\_name,

7 p.category = np.category

8 DELETE WHERE (p.category = 'ELECTRNCS')

9 WHEN NOT MATCHED THEN

10 INSERT

11 VALUES (np.product\_id, np.product\_name, np.category)

SQL> /

4 rows merged.

SQL> SELECT \* FROM products;

PRODUCT\_ID PRODUCT\_NAME CATEGORY

---------- -------------------- ----------

1501 VIVITAR 35MM ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

1700 WAIT INTERFACE BOOKS

SQL>

产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所以被更新为表NEWPRODUCTS中的值.

# 账号与日期的命令 #

与账号有关的命令

select username, account\_status from dba\_users;

alter user scott account unlock;

alter user scott identified by 'oracle';

conn scott/oracle

select table\_name from user\_tables;

与日期格式有关的命令

select PROPERTY\_NAME,PROPERTY\_VALUE from database\_properties;

select \* from v$nls\_parameters

alter session set nls\_date\_format='yyyy-mon-dd HH24:mi:ss';

alter system set nls\_date\_format='yyyy-mon-dd HH24:mi:ss' scope=spfile;

# oracle table-lock的5种模式 #

ROW SHARE 行共享，允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁

ROW EXCLUSIVE  行排他，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁

SHARE 共享，不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁

SHARE ROW EXCLUSIVE  共享行排他，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁

EXCLUSIVE 排他，其他用户禁止更新任何行，禁止其他用户同时加任何锁

Oracle中的锁定可以分为几类：

DML lock（data lock），

DDL lock（dictionary lock)和internal lock/latch。

DML lock又可以分为row lock和table lock。

row lock在select.. for update/insert/update/delete时隐式自动产生，

而table lock除了隐式产生，也可以调用lock table <table\_name> in </table\_name> name来显示锁定。

如果不希望别的session lock/insert/update/delete表中任意一行，只允许查询，可以用lock table table\_name in exclusive mode。(X)这个锁定模式级别最高，并发度最小。

如果允许别的session查询或用select for update锁定记录，不允许insert/update/delete，可以用lock table table\_name in share row exclusive mode。(SRX)

如 果允许别的session查询或select for update以及lock table table\_name in share mode，只是不允许insert/update/delete，可以用lock table table\_name in share mode。(share mode和share row exclusive mode的区别在于一个是非抢占式的而另一个是抢占式的。进入share row exclusive mode后其他session不能阻止你insert/update/delete，而进入share mode后其他session也同样可以进入share mode，进而阻止你对表的修改。(S)

还有两种锁定模式，row share(RS)和row exclusive(RX)。他们允许的并发操作更多，一般直接用DML语句自动获得，而不用lock语句。

# EXPLAIN PLAN查看相关 #

\---

一．相关的概念

Rowid的概念

Recursive Sql概念

Predicate(谓词)

DRiving Table(驱动表)

Probed Table(被探查表)

组合索引(concatenated index)

可选择性(selectivity)

二．oracle访问数据的存取方法

1） 全表扫描（Full Table Scans， FTS）

2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）

3）索引扫描（Index Scan或index lookup）有4种类型的索引扫描：

（1） 索引唯一扫描（index unique scan）

（2） 索引范围扫描（index range scan）

在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况：

（a） 在唯一索引列上使用了range操作符（> < <> >= <= between）

（b） 在组合索引上，只使用部分列进行查询，导致查询出多行

（c） 对非唯一索引列上进行的任何查询。

（3） 索引全扫描（index full scan）

（4） 索引快速扫描（index fast full scan）

三、表之间的连接

1，排序 - - 合并连接（Sort Merge Join， SMJ）

2，嵌套循环（Nested Loops， NL）

3，哈希连接（Hash Join， HJ）

另外，笛卡儿乘积（Cartesian Product）

总结Oracle连接方法

Oracle执行计划总结概述

\+++

一．相关的概念

Rowid的概念：rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。 对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行 修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。

Recursive SQL概念：有时为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为''recursive calls''或''recursive SQL statements''.如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不必关心这些recursive SQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。当然DML语句与SELECT都可能引起recursive SQL.简单的说，我们可以将触发器视为recursive SQL.

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访问数据的存取方法

1） 全表扫描（Full Table Scans， FTS）

为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件一个多块读操作可以使一次I/O能读取多块数据块（db\_block\_multiblock\_read\_count参数设定），而不是只读取一个数据块，这极大的减 少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模 式下，每个数据块只被读一次。

使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% —— 10%，或你想使用并行查询功能时。

使用全表扫描的例子：

SQL> explain plan for select \* from dual;

Query Plan

-----------------------------------------

SELECT STATEMENT\[CHOOSE\] Cost=

TABLE ACCESS FULL DUAL

2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）

行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。

这种存取方法不会用到多块读操作，一次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\]

3）索引扫描（Index Scan或index lookup）

我们先通过index查找到数据对应的rowid值（对于非唯一索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据，这 种查找方式称为索引扫描或索引查找（index lookup）。一个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

但是如果查询的数据能全在索引中找到，就可以避免进行第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很高的

SQL> explain plan for select empno from emp where empno=10;-- 只查询empno列值

Query Plan

------------------------------------

SELECT STATEMENT \[CHOOSE\] Cost=1

INDEX UNIQUE SCAN EMP\_I1

进一步讲，如果sql语句中对索引列进行排序，因为索引已经预先排序好了，所以在执行计划中不需要再对索引列进行排序

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\]

从这个例子中可以看到：因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的行，因此避免了进一步排序操作。

根据索引的类型与where限制条件的不同，有4种类型的索引扫描：

索引唯一扫描（index unique scan）

索引范围扫描（index range scan）

索引全扫描（index full scan）

索引快速扫描（index fast full scan）

（1） 索引唯一扫描（index unique scan）

通过唯一索引查找一个数值经常返回单个ROWID.如果存在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

（2） 索引范围扫描（index range scan）

使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况下是在谓词（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种情况：

（a） 在唯一索引列上使用了range操作符（> < <> >= <= between）

（b） 在组合索引上，只使用部分列进行查询，导致查询出多行

（c） 对非唯一索引列上进行的任何查询。

（3） 索引全扫描（index full scan）

与全表扫描对应，也有相应的全索引扫描。而且此时查询出的数据都必须从索引中可以直接得到。

全索引扫描的例子：

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\]

（4） 索引快速扫描（index fast 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=1

INDEX FAST FULL SCAN BE\_IX \[ANALYZED\]

三、表之间的连接

Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。在后面的叙 述中，我们将会使用“row source”来代替“表”，因为使用row source更严谨一些，并且将参与连接的2个row source分别称为row source1和row source 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（+））。上面的各个连接的连接原理都基本一样，所以为了简单期间，下面以等值连接为例进行介绍。

在后面的介绍中，都以以下Sql为例进行说明：

SELECT A.COL1， B.COL2

FROM A， B

WHERE A.COL3 = B.COL4；

假设A表为Row Soruce1，则其对应的连接操作关联列为COL 3；

B表为Row Soruce2，则其对应的连接操作关联列为COL 4；

连接类型：

目前为止，无论连接操作符如何，典型的连接类型共有3种：

排序 - - 合并连接（Sort Merge Join （SMJ） ）

嵌套循环（Nested Loops （NL） ）

哈希连接（Hash Join）

另外，还有一种Cartesian product（笛卡尔积），一般情况下，尽量避免使用。

1，排序 - - 合并连接（Sort Merge Join， SMJ）

内部连接过程：

1） 首先生成row source1需要的数据，然后对这些数据按照连接操作关联列（如A.col3）进行排序。

2） 随后生成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列（如B.col4）进行排序。

3） 最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来

下面是连接步骤的图形表示：

MERGE

/\\

SORTSORT

Row Source 1Row Source 2

如果row source已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其费资源的操 作，特别是对于较大的表。预先排序的row source包括已经被索引的列（如a.col3或b.col4上有索引）或row source已经在前面的步骤中被排序了。尽管合并两个row source的过程是串行的，但是可以并行访问这两个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\]

排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个row source都已经预先排序，则这种连接方法的效率也是蛮高的。

2，嵌套循环（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\]

3，哈希连接（Hash Join， HJ）

这种连接是在oracle 7.3以后引入的，从理论上来说比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连接的例子：

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

要使哈希连接有效，需要设置HASH\_JOIN\_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置 hash\_area\_size参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还 要低。

另外，笛卡儿乘积（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 EMP

CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行，dept表有m行，笛卡尔乘积的结果就是得到n \* m行结果。

最后，总结一下，在哪种情况下用哪种连接方法比较好：

排序 - - 合并连接（Sort Merge Join， SMJ）：

a） 对于非等值连接，这种连接方式的效率是比较高的。

b） 如果在关联的列上都有索引，效果更好。

c） 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。

d） 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O.

嵌套循环（Nested Loops， NL）：

a） 如果driving row source（外部表）比较小，并且在inner row source（内部表）上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。

b） NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。

哈希连接（Hash Join， HJ）：

a） 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash\_area\_size参数，才能取得较好的性能。

b） 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。

c） 只能用于等值连接中

+++

Oracle执行计划的概述

---

Oracle执行计划的相关概念：

Rowid：系统给oracle数据的每行附加的一个伪列，包含数据表名称，数据库id，存储数据库id以及一个流水号等信息，rowid在行的生命周期内唯一。

Recursive sql：为了执行用户语句，系统附加执行的额外操作语句，譬如对数据字典的维护等。

Row source（行源）：oracle执行步骤过程中，由上一个操作返回的符合条件的行的集合。

Predicate（谓词）：where后的限制条件。

Driving table（驱动表）：又称为连接的外层表，主要用于嵌套与hash连接中。一般来说是将应用限制条件后，返回较少行源的表作为驱动表。在后面的描述中，将driving table称为连接操作的row source 1。

Probed table（被探查表）：连接的内层表，在我们从driving table得到具体的一行数据后，在probed table中寻找符合条件的行，所以该表应该为较大的row source，并且对应连接条件的列上应该有索引。在后面的描述中，一般将该表称为连接操作的row source 2.

Concatenated index（组合索引）：一个索引如果由多列构成，那么就称为组合索引，组合索引的第一列为引导列，只有谓词中包含引导列时，索引才可用。

可选择性：表中某列的不同数值数量/表的总行数如果接近于1，则列的可选择性为高。

Oracle访问数据的存取方法：

Full table scans, FTS(全表扫描)：通过设置db\_block\_multiblock\_read\_count可以设置一次IO能读取的数据块个数，从而有效减少全表扫描时的IO总次数，也就是通过预读机制将将要访问的数据块预先读入内存中。只有在全表扫描情况下才能使用多块读操作。

Table Access by rowed（通过rowid存取表，rowid lookup）：由于rowid中记录了行存储的位置，所以这是oracle存取单行数据的最快方法。

Index scan（索引扫描index lookup）：在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的rowid值，索引扫描分两步1，扫描索引得到rowid；2，通过 rowid读取具体数据。每步都是单独的一次IO，所以如果数据经限制条件过滤后的总量大于原表总行数的5%－10％,则使用索引扫描效率下降很多。而如果结果数据能够全部在索引中找到，则可以避免第二步操作，从而加快检索速度。

根据索引类型与where限制条件的不同，有4种类型的索引扫描：

Index unique scan（索引唯一扫描）：存在unique或者primary key的情况下，返回单个rowid数据内容。

Index range scan（索引范围扫描）：1，在唯一索引上使用了range操作符（>,<,<>,>=,<=,between）；2，在组合索引上，只使用部分列进行查询；3，对非唯一索引上的列进行的查询。

Index full scan（索引全扫描）：需要查询的数据从索引中可以全部得到。

Index fast full scan（索引快速扫描）：与index full scan类似，但是这种方式下不对结果进行排序。

目前为止，典型的连接类型有3种：

Sort merge join（SMJ排序－合并连接）：首先生产driving table需要的数据，然后对这些数据按照连接操作关联列进行排序；然后生产probed table需要的数据，然后对这些数据按照与driving table对应的连接操作列进行排序；最后两边已经排序的行被放在一起执行合并操作。排序是一个费时、费资源的操作，特别对于大表。所以smj通常不是一个特别有效的连接方法，但是如果driving table和probed table都已经预先排序，则这种连接方法的效率也比较高。

Nested loops（NL嵌套循环）：连接过程就是将driving table和probed table进行一次嵌套循环的过程。就是用driving table的每一行去匹配probed table 的所有行。Nested loops可以先返回已经连接的行，而不必等待所有的连接操作处理完成才返回数据，这可以实现快速的响应时间。

Hash join（哈希连接）：较小的row source被用来构建hash table与bitmap，第二个row source用来被hashed，并与第一个row source生产的hash table进行匹配。以便进行进一步的连接。当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。但需要设置合适的hash\_area\_size参数且只能用于等值连接中。

另外，还有一种连接类型：Cartesian product（笛卡尔积）：表的每一行依次与另外一表的所有行匹配，一般情况下，尽量避免使用。

# EXPLAIN PLAN总结 #

在ORACLE数据库中,需要对SQL语句进行优化的话需要知道其执行计划,从而针对性的进行调整.ORACLE的执行计划的获得有几种方法,下面就来总结下

1、EXPLAIN的使用

Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。

1.1 、安装

要使用EXPLAIN首先要执行相应的脚本，创建出Explain\_plan表。

具体脚本执行如下：

$ORACLE\_HOME/rdbms/admin/utlxplan.sql (UNIX) 该脚本后会生成一个表这个程序会创建一个名为plan\_table的表。

1.2 、使用

常规使用语法：

explain PLAN \[ SET STATEMENT\_ID \[=\] < string literal > \] \[ INTO < table\_name > \]

FOR < sql\_statement >

其中：

STATEMENT\_ID：是一个唯一的字符串，把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。

TABLE\_NAME：是plan表名，它结构如前所示，你可以任意设定这个名称。

SQL\_STATEMENT：是真正的SQL语句。

比如：

SQL>explain plan set statement\_id='T\_TEST' for select \* from t\_test;

SQL>

Explained

执行下面语句可以查询到执行计划

SQL>SELECT A.OPERATION,OPTIONS,OBJECT\_NAME,OBJECT\_TYPE,ID,PARENT\_ID

2 FROM PLAN\_TABLE a

3 WHERE STATEMENT\_ID='T\_TEST'

4 ORDER BY Id;

也可以用这句话 select \* from table(dbms\_xplan.display); 可以把所有PLAN\_TABLE里的数据罗列出来。

2 、AUTOTRACE的使用方法

2.1、安装

用sys用户运行脚本ultxplan.sql

建立这个表的脚本是：（UNIX:$ORACLE\_HOME/rdbms/admin, Windows:%ORACLE\_HOME%\\rdbms\\admin）ultxplan.sql。

SQL> connect sys/sys@colm2 as sysdba;

SQL> @C:\\oracle\\ora92\\rdbms\\admin\\utlxplan.sql;

SQL> create public synonym plan\_table for plan\_table; --建立同义词

SQL> grant all on plan\_table to public; --授权所有用户

要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色，这个脚本在目录（UNIX:$ORACLE\_HOME/sqlplus/admin, Windows:%ORACLE\_HOME%\\sqlplus\\admin）中；

SQL> @C:\\oracle\\ora92\\sqlplus\\admin\\plustrce.sql;

然后将角色plustrace授予需要autotrace的用户；

SQL>grant plustrace to public;

经过以上步骤的设置，就可以在sql\*plus中使用autotrace了

2、2 使用

使用起来非常方便，只要使用一条命令就可以了

SQL>SET AUTOTRACE ON；

\*autotrace功能只能在SQL\*PLUS里使用

其他一些使用方法：

2.2.1、在SQLPLUS中得到语句总的执行时间

SQL> set timing on;

2.2.2、只显示执行计划--（会同时执行语句得到结果）

SQL>set autotrace on explain

比如：

sql> select count(\*) from test;

count(\*)

\-------------

Execution plan

\----------------------------

0 select statement ptimitzer=choose (cost=3 card=1)

1 0 sort(aggregate)

2 1 partition range(all)

3 2 table access (full) of 't\_test' (cost=3 card=900)

2.2.3、只显示统计信息---（会同时执行语句得到结果）

SQL>set autotrace on statistics;

（备注：对于SYS用户，统计信息将会是0）

2.2.4、显示执行计划，屏蔽执行结果--（但语句实质还执行的

SQL> set autotrace on traceonly;

（备注：同SET AUTOTRACE ON; 只不过不显示结果，显示计划和统计）

2.2.5、仅仅显示执行计划，屏蔽其他一切结果--(语句还是执行了)

SQL>set autotrace on traceonly explain;

对于仅仅查看大表的Explain Plan非常管用。

2.2.6、关闭

SQL>set autotrace off;

总结：SQLPLUS 下的自动显示功能，在看执行计划中其语句还是会被执行的。尤其在执行UPDATE/DELETE语句时请千万注意，ORACLE是先执行脚本同时显示执行计划的，即使使用set autotrace on traceonly explain;

这个时候推荐使用EXPLAIN PLAN FOR来看或者PL/SQL等第三方工具

3、第三工具来看执行计划

如果在PL/SQL中使用选择要查询语句显示执行计划，则只需要SQL WINDOWS 窗口里面输入要查询的SQL语句，然后选择按键F5或者在菜单TOOLS？D？D>Explain Plan 菜单按键就可以在执行计划窗口查看该语句的执行计划。

在TOAD语句中在执行当前的SQL窗口中选择下方的Explain PlanTAB页即可以查看要执行语句的执行计划信息。

4、限制

explain真正的唯一的限制是用户不能去解释其它用户的表，视图，索引或其它类型，用户必须是所有被解释事物的所有者，如果不是所有者而只有select权限，explain会返回一个错误。

# SQL优化器如何进行评估优化 #

Oracle优化器在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是：

·要么结果表达式能够比源表达式具有更快的速度。

·要么源表达式只是结果表达式的一个等价语义结构。

不同的SQL结构有时具有同样的操作（例如：= ANY (subquery) and IN (subquery)），Oracle会把他们映射到一个单一的语义结构。

下面将讨论优化器如何评估优化如下的情况和表达式：

常量 LIKE 操作符 IN 操作符 ANY和SOME 操作符 ALL 操作符 BETWEEN 操作符 NOT 操作符

传递（Transitivity） 确定性（DETERMINISTIC）函数

常量

常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式：

· sal > 24000/12

· sal > 2000

· sal\*12 > 24000

如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。

注意：优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应用程序开发者应该尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。

LIKE 操作符

优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。

例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH'优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME字段的类型是CHAR(10)， 那么优化器将不做任何转换。

IN 操作符

优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。例如，优化器会把表达式ename IN ('SMITH','KING','JONES')替换为:

ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES'

ANY和SOME 操作符

优化器将跟随（following）值列表的ANY和SOME检索条件用等价的同等操作符和“OR”组成的表达式替换。

例如，优化器将如下所示的第一条语句用第二条语句替换：

· sal > ANY (:first\_sal, :second\_sal)

· sal > :first\_sal OR sal > :second\_sal

优化器将跟随子查询的ANY和SOME检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式。

例如，优化器将如下所示的第一条语句用第二条语句替换：

· x > ANY (SELECT sal FROM emp WHERE job = 'ANALYST')

· EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal)

ALL 操作符

优化器将跟随值列表的ALL操作符用等价的“=”和“AND”组成的表达式替换。

例如，sal > ALL (:first\_sal, :second\_sal)表达式会被替换为：

sal > :first\_sal AND sal > :second\_sal

对于跟随子查询的ALL表达式，优化器用ANY和另外一个合适的比较符组成的表达式替换。例如，优化器会把表达式 x > ALL (SELECT sal FROM emp WHERE deptno = 10) 替换为：

NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10))

接下来优化器会把第二个表达式适用ANY表达式的转换规则转换为下面的表达式：

NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal)

BETWEEN 操作符

优化器总是用“>=”和“<=”比较符来等价的代替BETWEEN操作符。例如：优化器会把表达式sal BETWEEN 2000 AND 3000用sal >= 2000 AND sal <= 3000来代替。

NOT 操作符

优化器总是试图简化检索条件以消除“NOT”逻辑操作符的影响，这将涉及到“NOT”操作符的消除以及代以相应的比较运算符。

例如，优化器将下面的第一条语句用第二条语句代替：

· NOT deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR')

· deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR')

通常情况下一个含有NOT操作符的语句有很多不同的写法，优化器的转换原则是使“NOT”操作符后边的子句尽可能的简单，即使可能会使结果表达式包含了更多的“NOT”操作符。例如，优化器将如下所示的第一条语句用第二条语句代替：

· NOT (sal < 1000 OR comm IS NULL)

· NOT sal < 1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL

传递（Transitivity）

如果“WHERE”子句的两个检索条件涉及了一个共同的字段，优化器有时会根据传递原理推断出第三个检索条件，随后可以根据这个推断出的条件对语句进行优化，推断出的条件可能会激活一个原来的检索条件没有激活的潜在的接口路径（access path）。注意：传递仅仅被用在基于代价（cost-based）的优化中。

假设有一个这样的包含两个检索条件的“WHERE”子句：WHERE 字段1 常量 AND字段1 = 字段2，在这个例子里，优化器会推断出新的检索条件：字段2 常量。在这里，是比较运算符=、!=、^=、<>、>、<= 或 >=之中的任何一个，常量是指任何一个涉及了操作符、SQL函数、文字、绑定变量（bind variables）或者关联变量（correlation variables）的常量表达式。

例如，考虑这样一个包含两个各自使用了字段EMP.DEPTNO的检索条件的WHERE子句的查询：

SELECT \* FROM emp, dept WHERE emp.deptno = 20 AND emp.deptno = dept.deptno;

使用传递优化，优化器会推断出如下条件：dept.deptno = 20。如果有索引存在于EMP.DEPTNO字段上，这个条件会使调用这个索引的接口路径有效。注意：优化器只能对字段关联常量的表达式进行推断，而不是字段关联字段的表达式。例如，包含这样条件的WHERE子句：字段1 字段3 AND 字段1 = 字段2，这种情况不能推断出表达式：字段2 < comp\_oper> 字段3。

确定性（DETERMINISTIC）函数

在某些情况下，优化器能够使用先前的函数返回结果而不是重新执行用户定义的函数，这仅仅对那些以限制的方式来执行的函数来说是有效的。这些函数必须对任何的输入都有同样的返回值，函数的结果必须不能因为包（PACKAGE）变量、数据库或会话（SESSION）的参数（例如NLS参数）不同而变化，如果函数在将来重新定义，返回值必须对任何参数来说仍然与以前的返回值相同。函数的创建者可以在以CREATE FUNCTION、CREATE PACKAGE或者CREATE TYPE声明函数时根据以上的要求使用DETERMINISTIC关键字向数据库申明该函数为确定性函数，数据库不会对确定性函数的合法性进行校验，即使一个函数明显的使用了包变量或操作了数据库，仍然可以被定义为确定性函数，这就是说如何安全合法的使用和定义确定性函数是程序员的责任。

当确定性函数在同一个查询里被多次调用，或者被基于函数的索引或物化视图（materialized view）调用时，有可能被一个已经计算出的值取代。

# 执行计划稳定性深入研究 #

什么是执行计划

所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息的，是由数据库来决定的。

我们先简单的看一个执行计划的对比：

SQL> set autotrace traceonly

执行计划一：

SQL> select count(\*) from t;

COUNT(\*)

----------

24815

Execution Plan

0　　　SELECT STATEMENT Optimizer=CHOOSE

1　　0　 SORT (AGGREGATE)

2　　1　　 TABLE ACCESS (FULL) OF ’T’

执行计划二：

SQL> select count(\*) from t;

COUNT(\*)

24815

Execution Plan

0　　　SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1)

1　　0　 SORT (AGGREGATE)

2　　1　　 INDEX (FULL SCAN) OF ’T\_INDEX’ (NON-UNIQUE) (Cost=26 Card=28180)

这两个执行计划中，第一个表示求和是通过进行全表扫描来做的，把整个表中数据读入内存来逐条累加；第二个表示根据表中索引，把整个索引读进内存来逐条累加，而不用去读表中的数据。但是这两种方式到底哪种快呢？通常来说可能二比一快，但也不是绝对的。这是一个很简单的例子演示执行计划的差异。对于复杂的SQL（表连接、嵌套子查询等），执行计划可能几十种甚至上百种，但是到底那种最好呢？我们事前并不知道，数据库本身也不知道，但是数据库会根据一定的规则或者统计信息（statistics）去选择一个执行计划，通常来说选择的是比较优的，但也有选择失误的时候，这就是这次讨论的价值所在。

Oracle优化器模式

Oracle优化器有两大类，基于规则的和基于代价的，在SQLPLUS中我们可以查看init文件中定义的缺省的优化器模式。

SQL> show parameters optimizer\_mode

NAME　　　　　　　　　　　　　　　　 TYPE　　VALUE

optimizer\_mode　　　　　　　　　　 string　 CHOOSE

SQL>

这是Oracle8.1.7 企业版，我们可以看出，默认安装后数据库优化器模式为CHOOSE,我们还可以设置为 RULE、FIRST\_ROWS,ALL\_ROWS。可以在init文件中对整个instance的所有会话设置，也可以单独对某个会话设置：

SQL> ALTER SESSION SET optimizer\_mode　= RULE;

会话已更改。

SQL>　ALTER SESSION SET optimizer\_mode　= FIRST\_ROWS;

会话已更改。

SQL>　ALTER SESSION SET optimizer\_mode　= ALL\_ROWS;

会话已更改。

基于规则的查询，数据库根据表和索引等定义信息，按照一定的规则来产生执行计划；基于代价的查询，数据库根据搜集的表和索引的数据的统计信息（通过analyze 命令或者使用dbms\_stats包来搜集）综合来决定选取一个数据库认为最优的执行计划（实际上不一定最优）。RULE是基于规则的，CHOOSE表示如果查询的表存在搜集的统计信息则基于代价来执行(在CHOOSE模式下Oracle采用的是 FIRST\_ROWS)，否则基于规则来执行。在基于代价的两种方式中，FIRST\_ROWS指执行计划采用最少资源尽快的返回部分结果给客户端，对于排序分页页显示这种查询尤其适用，ALL\_ROWS指以总体消耗资源最少的方式返回结果给客户端。

基于规则的模式下，数据库的执行计划通常比较稳定。但在基于代价的模式下，我们才有更大的机会选择最优的执行计划。也由于Oracle的很多查询方面的特性必须在基于代价的模式下才能体现出来，所以我们通常不选择RULE(并且Oracle宣称从 Oracle 10i版本数据库开始将不再支持 RULE)。既然是基于代价的模式，也就是说执行计划的选择是根据表、索引等定义和数据的统计信息来决定的，这个统计信息是根据 analyze 命令或者dbms\_stats包来定期搜集的。首先存在着一种可能，就是由于搜集信息是一个很消耗资源和时间的动作，尤其当表数据量很大的时候，因为搜集信息是对整个表数据进行重新的完全统计，所以这是我们必须慎重考虑的问题。我们只能在服务器空闲的时候定期的进行信息搜集。这说明我们在一段时期内，统计信息可能和数据库本身的数据并不吻合；另外就是Oracle的统计数据本身也存在着不精确部分（详细参考Oracle DOCUMENT），更重要的一个问题就是及时统计数据相对已经比较准确，但是Oracle的优化器的选择也并不是始终是最优的方案。这也倚赖于Oracle对不同执行计划的代价的计算规则（我们通常是无法知道具体的计算规则的）。这好比我们决定从香港还是从北京去英国，车票、机票等实际价格到底是怎么核算出来的我们并不知道，或者说我们现在了解的价格信息，在我们乘车前往的时候，真实价格跟我们的预算已经发生了变化。所有的因素，都将影响我们的整个开销。

执行计划稳定性能带给我们什么

Oracle存在着执行计划选择失误的可能。这也是我们经常遇见的一些现象，比如总有人说我的程序在测试数据库中跑的很好，但在产品数据库上就是跑的很差，甚至后者硬件条件比前者还好，这到底是为什么？硬件资源、统计信息、参数设置都可能对执行计划产生影响。由于因素太多，我们总是对未来怀着一种莫名的恐惧，我的产品数据库上线后到底跑的好不好？于是Oracle提供了一种稳定执行计划的能力，也就是把在测试环境中的运行良好的执行计划所产生的OUTLINES移植到产品数据库，使得执行计划不会随着其他因素的变化而变化。

那么OUTLINES是什么呢？先要介绍一个内容，Oracle提供了在SQL中使用HINTS来引导优化器产生我们想要的执行计划的能力。这在多表连接、复杂查询中特别有效。HINTS的类型很多，可以设置优化器目标(RULE、CHOOSE、FIRST\_ROWS、ALL\_ROWS)，可以指定表连接的顺序，可以指定使用哪个表的哪个索引等等，可以对SQL进行很多精细的控制。通过这种方式产生我们想要的执行计划的这些HINTS,Oracle可以存储这些HINTS，我们称之为OUTLINES。通过STORE OUTLINES可以使得我们拥有以后产生相同执行计划的能力，也就是使我们拥有了稳定执行计划的能力。

这里想给出一个附加的说明就是，实际上，我们通过工具改写SQL，比如使用SQL　EXPERT改写后的SQL，这些不仅仅是加了HINTS而且文本都已经发生了变化的SQL，也可以存储OUTLINES，并可被应用到应用中。但这不是一定生效，我们必须测试检查是否生效。但由于就算给了错误的OUTLINES,数据库在执行的时候，也只是忽略过去重新生成执行计划而不会返回错误，所以我们才敢放心的这么使用。当然在Oracle文档中并没有指明可以这样做，文档中只是说明，如果存在OUTLINES的同时又在SQL中加了HINTS，则会使用OUTLINES而忽略HINTS。这个功能在LECCO将发布的产品中会使用这一功能，这样可以将SQL EXPERT的改写SQL的能力和稳定执行计划的能力结合起来，那么我们就对不能更改源代码的应用具有了相当强大的SQL优化能力。

也许我们会有疑问，假如稳定了执行计划，那还搜集统计信息干吗？这是因为几个原因造成的，首先，现在的执行计划对于未来发生了变化的数据未必就是合适的，存在着当前的执行计划不满足未来数据的变化后的效率，而新的统计信息的情况下所产生的执行计划也并不是全部都合理的。那这个时候，我们可以采用新搜集的统计信息，但是却对新统计信息下不良的执行计划采用Oracle提供的执行计划稳定性这个能力固定执行计划，这样结合起来我们可以建立满意的高效的数据库运行环境。

我们还需要关注的一个东西，Oracle提供的dbms\_stats包除了具有搜集统计信息的能力，还具有把数据库中统计信息（statistics）export/import的能力，还具有只搜集统计信息而使得统计信息不应用于数据库的能力（把统计信息搜集到一个特定的表中而不是立即生效），在这个基础上我们就可以把统计信息export出来再import到一个测试环境中，再运行我们的应用，在测试环境中我们观察最新的统计信息会导致哪些执行计划发生变化（DB EXPERT的Plan Version Tracer是模拟不同环境并自动检查不同环境中执行计划变化的工具），是变好了还是变差了。我们可以把变差的这一部分在测试环境中使用hints或者利用工具（SQL EXPERT是在重写SQL这一领域目前最强有力的工具）产生良好的执行计划的SQL，利用这些SQL可以产生OUTLINES,然后在产品数据库应用最新的统计信息的同时移植进这些OUTLINES。

最后说一下我们不得不使用执行计划稳定性能力的场合。我们假定Oracle的优化器的选择都是准确的，但是优化器选择的基础就是我们的SQL,这些SQL才从根本上决定了运行效率，这是更重要的一个优化的环节。SQL是基础（当然数据库的设计是基础的基础），一个SQL写的好不好，就相当于我们同样是要想去英国，但是我的起点在珠海，你的起点却在西藏的最边缘偏僻的一个地方，那不管你做怎样的最优路线选择，你都不如我在珠海去英国所花费的代价小。

# Oracle查询优化4大方面的主要途径 #

摘要：数据库的优化是个细致的工作，Oracle查询优化只是其中的一个部分，亦如何提高查询效率这个问题上，本文作者通过对Oracle数据库应用的实践经验 ，总结了四个方面来优化查询：从命中率提高、多表查询优化、大表查询优化和SQL优化。

数据库最基本的任务是存储、管理数据，而终端用户唯一能看到的数据库特性就是其性能：数据库以何速度处理某一指定查询的结果，并且将结果返回到用户所用的工具和应用程序。从大多数系统的应用实例来看，查询操作在各种数据库操作中所占据的比重最大、查阅新闻、 查看文件、 查询统计信息等。因此，数据库查询操作的效率是影响一个应用系统响应时间的关键因素。随着一个应用系统中数据的动态增长，数据量变大，数据库查询效率就会有所降低，应用系统的响应速度也随之减慢，尤其对于海量数据的管理和查询问题就更加突出，Oracle查询优化就显得尤为重要。

目前通用的数据库产品有很多种，其中Oracle数据库以其支持大数据库、多用户的高性能事务处理, 对业界各项工业标准的支持,完整的安全和完整性控制，支持分布式数据库利分布处理 具有可移植性、可兼容性和可连接性等突出优点倍受用户喜爱，应用较为广泛，在互联网数据库平台上处于领先地位、其Spatial技术能更加有效地管理地理信息，实现海量空间信息的存储和管理。本文结合Oracle数据库应用经验，从命中率提高、多表查询优化、大表查询优化和SQL优化等四个方面阐述Oracle查询优化的经验和方法。

Oracle查询优化第一方面：Oracle数据查询命中率的提高

“命中率（HITRATIO） 是指直接从内存中取得数据而不从磁盘中取得数据的比率，也就是查询请求的数据块已经在内存中次数的百分比”。影响命中率的因素有四种：字典表活动、临时段活动、回滚段活动、表扫描， 应用DBA可以对这四种因素进行分析，找出数据库命中率低的症结所在。

1)字典表活动

当一个SQL语句第一次到达Oracle内核时数据库对SQL语句进行分析，包含在查询中的数据字典对象被分解，产生SQL执行路径。如果SQL语句指向一个不在SGA中的对象??表或视图，Oracle执行SQL语句到数据典中查询有关对象的信息。数据块从数据字典表被读取到SGA的数据缓存中。由于每个数据字典都很小，因此，我们可缓存这些表以提高对这些表的命中率。但是由于数据字典表的数据块在SGA中占据空间，当增加全部的命中率时，它们会降低表数据块的可用空间， 所以若查询所需的时间字典信息已经在SGA缓存中，那么就没有必要递归调用。

2)临时段的活动

当用户执行一个需要排序的查询时，Oracle设法对内存中排序区内的所有行进行排序，排序区的大小由数据库的init.ora文件的数确定。如果排序区域不够大，数据库就会在排序操作期间开辟临时段。临时段会人为地降低OLTP(online transaction processing）应用命中率,也会降低查询进行排序的性能。如果能在内存中完成全部排序操作，就可以消除向临时段写数据的开销。所以应将SORT\_AREA\_SIZE设置得足够大，以避免对临时段的需要。这个参数的具体调整方法是:查询相关数据，以确定这个参数的调整。

select \* from v$sysstat where name='sorts(disk)'or name='sorts(memory);

大部分排序是在内存中进行的，但还有小部分发生在临时段， 需要调整 值，查看init.ora文件的 SORT\_AREA\_SIZE值，参数为：SORT\_AREA\_SIZE＝65536;将其调整到SORT\_AREA\_SIZE＝131072、这个值调整后，重启ORACLE数据库即可生效。

3)回滚段的活动

回滚段活动分为回滚活动和回滚段头活动。对回滚段头块的访问会降低应用的命中率， 对OLTP系统命中率的影响最大。为确认是否因为回滚段影响了命中率，可以查看监控输出报表中的“数据块相容性读一重写记录应用” 的统计值，这些统计值是用来确定用户从回滚段中访问数据的发生次数。

4)表扫描

通过大扫描读得的块在数据块缓存中不会保持很长时间， 因此表扫描会降低命中率。为了避免不必要的全表扫描，首先是根据需要建立索引，合理的索引设计要建立人对各种查询的分析和预测上，笔者会在SQL优化中详细谈及；其次是将经常用到的表放在内存中，以降低磁盘读写次数。例如 Alter table your\_table\_name cathe。

Oracle查询优化第二方面：多表查询的优化

在进行多表联合查询时，数据库可能会采取MERGEJOINS、NESTED LOOP、HASH JOIN。其中，不论什么时候哈希联结要比另两种联结开销要小。

我们可以使用哈希联结代替MERGEJOINS、NESTED LOOP联结、因此，在应用中，可添加一些设置使得数据库在有多大联合查询发生时使用哈希联结。其方法是:以 oracle用户身份登录数据库服务器，在initosid.ora文件中添加:

HASH\_JOIN\_ENABLED=TRUE

HASJ\_AREA\_SIZE=26000

修改完后,重新启动数据库,使这些参数值生效。

Oracle查询优化第三方面：大表查询优化

数据库中有些表是增长非常快的，记录量很大，对这种表进行访问时，索引的好处就微乎其微了，通常采用两种办法来进行大表访问的优化。

1)大表建立在哈希簇中

create cluster TRADE\_CLUSTER(vuserid integer)

storage(initial 50M next 50M)

hash is vuserid

size 60 hashkeys 10000000;/\*hashkeys指定了在哈希表里的所期望的行数。\*/ create table

trade\_detail\_new as select \* from trade\_detail cluster

TRADE\_CLUSTER(userid);

drop table trade\_detail;

rename trade\_detail\_new to trade\_detail;

2)建分区表

将一个大表分开放置在几个逻辑分区中或者是将一个大表分成了几张小表 ，即可以单独对这些小表进行查询，也可以union all一起查询。

例如：将 一个记录交易详情的表拆分：

create trade\_detail\_1 as select \* from trade\_detail

where trade\_time between to\_date('mm-dd','01-01')and to\_date('mm-dd','03-31');

alter table trade\_detail\_1 add constraint check\_trade\_detail\_1

check (trade\_time between to\_date('mm-dd','01-01')and to\_date('mm-dd','03-31'));

同样,建立起另几张按交易发生的季度而划分的表。然后创建执行四个表联合的视图；

create view trade\_detail as select \* from trade\_detail\_1

union all select \* from trade\_detail\_2

union all select \* from trade\_detail\_3

union all select \* from trade\_detail\_4;

这样在查询某段时间内的数据时只访问小表就可以了，需要时也可进行联合查询。

Oracle查询优化第四方面：SQL优化

应用程序的执行最终将归结为数据库中的SQL语句执行，SQL语句消耗了70%到90%的数据库资源。因此SQL语句的执行效率最终决定了ORACLE数据库的性能。许多程序员认为查询优化是DBMS（数据库管理系统）的任务，与程序员所编写的SQL语句关系不大，这是错误的。一个好的查询计划往往可以使程序性能提高数十倍。另外，SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低。

SQL优化的主要途径是：

a.有效索引的建立。在经常进行连接，但是没有指定为外键的列上建立索引；在频繁进行排序或分组（即进行group by 或 order by 操作）的列上建立索引；在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引；如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。

为了降低I/O竟争, 索引要建在与用户表空间不在同一磁盘上的索引空间里。索引分为：分区索引、完全索引、唯一索引、位图索引等几种类型，在建立索引前，应该测量这个索引的选择性，索引的选择性是指索引列里不同值的数目与表中记录数的比。

b.在有大量重复值并且经常有范围查询（例如 between,>,<>=,<=）的列,或是用到order by、group by的列,可考虑建立群集索引 ；

c.要经常同时存取多列,目每列都含有重复值可考虑建立组合索引

d.优化表达式，在能使用范围查询时尽可能使用范围索引， 而少用“like”,因为“LIKE”关键字支持的通配符匹配特别耗费时间。

f.使用Oracle语句优化器（oracle optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。

# SQL优化例子 #

**（1）** 选择最有效率的表名顺序(只在基于规则的优化器中有效)：

ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

**（2）** WHERE子句中的连接顺序．：

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

**（3）** SELECT子句中避免使用 ‘ \* ‘：

ORACLE在解析的过程中, 会将'\*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

**（4）** 减少访问数据库的次数：

ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；

**（5）** 在SQL\*Plus , SQL\*Forms和Pro\*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200

**（6）** 使用DECODE函数来减少处理时间：

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

**（7）** 整合简单,无关联的数据库访问：

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

**（8）** 删除重复记录：

最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM EMP X WHERE X.EMP\_NO = E.EMP\_NO);

**（9）** 用TRUNCATE替代DELETE：

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

**（10）** 尽量多使用COMMIT：

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:

COMMIT所释放的资源:

a. 回滚段上用于恢复数据的信息.

b. 被程序语句获得的锁

c. redo log buffer 中的空间

d. ORACLE为管理上述3种资源中的内部花费

**（11）** 用Where子句替换HAVING子句：

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

**（12）** 减少对表的查询：

在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：

SELECT TAB\_NAME FROM TABLES WHERE (TAB\_NAME,DB\_VER) = ( SELECT

TAB\_NAME,DB\_VER FROM TAB\_COLUMNS WHERE VERSION = 604)

**（13）** 通过内部函数提高SQL效率.：

复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的

**（14）** 使用表的别名(Alias)：

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

**（15）** 用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例子：

（高效）SELECT \* FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

(低效)SELECT \* FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

**（16）** 识别'低效执行'的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 DESC;

**（17）** 用索引提高效率：

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

**（18）** 用EXISTS替换DISTINCT：

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：

(低效):

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);

**（19）** sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

**（20）** 在java代码中尽量少用连接符“＋”连接字符串！

**（21）** 避免在索引列上使用NOT 通常，

我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

**（22）** 避免在索引列上使用计算．

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．

举例:

低效：

SELECT … FROM DEPT WHERE SAL \* 12 > 25000;

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12;

**（23）** 用>=替代>

高效:

SELECT \* FROM EMP WHERE DEPTNO >=4

低效:

SELECT \* FROM EMP WHERE DEPTNO >3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

**（24）** 用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC\_ID 和REGION上都建有索引.

高效:

SELECT LOC\_ID , LOC\_DESC , REGION

FROM LOCATION

WHERE LOC\_ID = 10

UNION

SELECT LOC\_ID , LOC\_DESC , REGION

FROM LOCATION

WHERE REGION = “MELBOURNE”

低效:

SELECT LOC\_ID , LOC\_DESC , REGION

FROM LOCATION

WHERE LOC\_ID = 10 OR REGION = “MELBOURNE”

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

**（25）** 用IN来替换OR

这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．

低效:

SELECT…. FROM LOCATION WHERE LOC\_ID = 10 OR LOC\_ID = 20 OR LOC\_ID = 30

高效

SELECT… FROM LOCATION WHERE LOC\_IN IN (10,20,30);

**（26）** 避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.

低效: (索引失效)

SELECT … FROM DEPARTMENT WHERE DEPT\_CODE IS NOT NULL;

高效: (索引有效)

SELECT … FROM DEPARTMENT WHERE DEPT\_CODE >=0;

**（27）** 总是使用索引的第一个列：

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引

**（28）** 用UNION-ALL 替换UNION ( 如果有可能的话)：

当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT\_AREA\_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量

低效：

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'

**（29）** 用WHERE替代ORDER BY：

ORDER BY 子句只在两种严格的条件下使用索引.

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.

ORDER BY中所有的列必须定义为非空.

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.

例如:

表DEPT包含以下列:

DEPT\_CODE PK NOT NULL

DEPT\_DESC NOT NULL

DEPT\_TYPE NULL

低效: (索引不被使用)

SELECT DEPT\_CODE FROM DEPT ORDER BY DEPT\_TYPE

高效: (使用索引)

SELECT DEPT\_CODE FROM DEPT WHERE DEPT\_TYPE > 0

**（30）** 避免改变索引列的类型.:

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.

假设 EMPNO是一个数值类型的索引列.

SELECT … FROM EMP WHERE EMPNO = ‘123'

实际上,经过ORACLE类型转换, 语句转化为:

SELECT … FROM EMP WHERE EMPNO = TO\_NUMBER(‘123')

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.

现在,假设EMP\_TYPE是一个字符类型的索引列.

SELECT … FROM EMP WHERE EMP\_TYPE = 123

这个语句被ORACLE转换为:

SELECT … FROM EMP WHERETO\_NUMBER(EMP\_TYPE)=123

因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型

**（31）** 需要当心的WHERE子句:

某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.

在下面的例子里, (1)‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘||'是字符连接函数. 就象其他函数那样, 停用了索引. (3) ‘+'是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.

**（32）** a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.

b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

**（33）** 避免使用耗费资源的操作:

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎

执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT\_AREA\_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强

**（34）** 优化GROUP BY:

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.

低效:

SELECT JOB , AVG(SAL)

FROM EMP

GROUP by JOB

HAVING JOB = ‘PRESIDENT'

OR JOB = ‘MANAGER'

高效:

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = ‘PRESIDENT'