如何看懂ORACLE执行计划

打开Oracle Database软件后进入软件数据库主界面，在PL/SQL下按F5查看执行计划，在这里面可以看到基数、优化器、耗费等基本信息 如图

瓜州ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：13518219792（备注：SSL证书合作）期待与您的合作！

2

在SQL*PLUS，PL/SQL的命令窗口下执行下面步骤 ：

SQLEXPLAIN PLAN FOR

SELECT * FROM SCOTT.EMP; --要解析的SQL脚本

SQLSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

如图

完成以上步骤后还需要在SQL*PLUS下输入代码执行命令：

SQLSET TIMING ON --控制显示执行时间统计数据 SQLSET AUTOTRACE ON EXPLAIN 　 --这样设置包含执行计划、脚本数据输出，没有统计信息

如图

接着我们输入第二段代码：

SQL执行需要查看执行计划的SQL语句 SQLSET AUTOTRACE OFF 　 --不生成AUTOTRACE报告，这是缺省模式

然后是第三段

SQL SET AUTOTRACE ON 　 --这样设置包含执行计划、统计信息、以及脚本数据输出 SQL执行需要查看执行计划的SQL语句 SQLSET AUTOTRACE OFF

第四段代码：

SQL SET AUTOTRACE TRACEONLY 　--这样设置会有执行计划、统计信息，不会有脚本数据输出

最后输入第5段代码：

SQL执行需要查看执行计划的SQL语句 SQLSET AUTOTRACE TRACEONLY STAT --这样设置只包含有统计信息 SQL执行需要查看执行计划的SQL语句

需要注意的是：在Oracle Database中，PL/SQL Developer 工具并不完全支持所有的SQL*Plus命令，如果执行“SET AUTOTRACE ON”命令就会报错，出现接下来的情况：

SQL SET AUTOTRACE ON;

Cannot SET AUTOTRAC

PL/SQL DEVELOPER工具里面执行上面脚本过后，我们是看不到相关信息的，这时我们可以通过输入脚本代码查询执行过的信息，代码如下：

SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' || P.SPID || '.trc' TRACE_FILE_NAME FROM ( SELECT P.SPID FROM V$MYSTAT M, V$SESSION S, V$PROCESS P WHERE M.STATISTIC# =1 AND S.SID = M.SID AND P.ADDR = S.PADDR ) P, ( SELECT T.INSTANCE FROM V$THREAD T, V$PARAMETER V WHERE V.NAME ='thread' AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE)) ) I, (SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T

如图所示：

我们通过Oracle Database,查看执行计划后，通常要以文本的形式保存下来，可以输入命令：tkprof D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc h:\out.txtoutputfile explain=etl/etl 执行 如图

执行上面命令后，就可以查看生成的文本文件了如图

以上就是如何用Oracle Database,查看执行计划的步骤，需要注意的是PL/SQL Developer 工具并不完全支持所有的SQL*Plus命令，执行SET AUTOTRACE ON 就如此，在PL/SQL Developer工具下执行此命令会报错。

OracleSQL精妙SQL语句讲解

行列转换 行转列

DROP TABLE t_change_lc;

CREATE TABLE t_change_lc (card_code VARCHAR ( ) q NUMBER bal NUMBER);

INSERT INTO t_change_lc

SELECT card_code ROWNUM q trunc(dbms_random VALUE * ) bal FROM dual CONNECT BY ROWNUM =

UNION

SELECT card_code ROWNUM q trunc(dbms_random VALUE * ) bal FROM dual CONNECT BY ROWNUM = ;

SELECT * FROM t_change_lc;

SELECT a card_code

SUM(decode(a q a bal )) q

SUM(decode(a q a bal )) q

SUM(decode(a q a bal )) q

SUM(decode(a q a bal )) q

FROM t_change_lc a

GROUP BY a card_code

ORDER BY ;

行列转换 列转行

DROP TABLE t_change_cl;

CREATE TABLE t_change_cl AS

SELECT a card_code

SUM(decode(a q a bal )) q

SUM(decode(a q a bal )) q

SUM(decode(a q a bal )) q

SUM(decode(a q a bal )) q

FROM t_change_lc a

GROUP BY a card_code

ORDER BY ;

SELECT * FROM t_change_cl;

SELECT t card_code

t rn q

decode(t rn t q t q t q t q ) bal

FROM (SELECT a * b rn

FROM t_change_cl a

(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM = ) b) t

ORDER BY ;

行列转换 行转列 合并

DROP TABLE t_change_lc_ma;

CREATE TABLE t_change_lc_ma AS SELECT card_code quarter_ ||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_ma;

SELECT t card_code substr(MAX(sys_connect_by_path(t q ; )) ) q

FROM (SELECT a card_code

a q

row_number() over(PARTITION BY a card_code ORDER BY a q) rn

FROM t_change_lc_ma a) t

START WITH t rn =

CONNECT BY t card_code = PRIOR t card_code

AND t rn = PRIOR t rn

GROUP BY t card_code;

行列转换 列转行 分割

DROP TABLE t_change_cl_ma;

CREATE TABLE t_change_cl_ma AS

SELECT t card_code substr(MAX(sys_connect_by_path(t q ; )) ) q

FROM (SELECT a card_code

a q

row_number() over(PARTITION BY a card_code ORDER BY a q) rn

FROM t_change_lc_ma a) t

START WITH t rn =

CONNECT BY t card_code = PRIOR t card_code

AND t rn = PRIOR t rn

GROUP BY t card_code;

SELECT * FROM t_change_cl_ma;

SELECT t card_code

substr(t q

instr( ; || t q ; rn)

instr(t q || ; ; rn) instr( ; || t q ; rn)) q

FROM (SELECT a card_code a q b rn

FROM t_change_cl_ma a

(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM = ) b

WHERE instr( ; || a q ; rn) ) t

ORDER BY ;

实现一条记录根据条件多表插入

DROP TABLE t_ia_src;

CREATE TABLE t_ia_src AS SELECT a ||ROWNUM c b ||ROWNUM c FROM dual CONNECT BY ROWNUM= ;

DROP TABLE t_ia_dest_ ;

CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));

DROP TABLE t_ia_dest_ ;

CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));

DROP TABLE t_ia_dest_ ;

CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));

SELECT * FROM t_ia_src;

SELECT * FROM t_ia_dest_ ;

SELECT * FROM t_ia_dest_ ;

SELECT * FROM t_ia_dest_ ;

INSERT ALL

WHEN (c IN ( a a )) THEN

INTO t_ia_dest_ (flag c) VALUES(flag c )

WHEN (c IN ( a a )) THEN

INTO t_ia_dest_ (flag c) VALUES(flag c )

ELSE

INTO t_ia_dest_ (flag c) VALUES(flag ||flag c ||c )

SELECT c c f flag f flag FROM t_ia_src;

如果存在就更新 不存在就插入用一个语句实现

DROP TABLE t_mg;

CREATE TABLE t_mg(code VARCHAR ( ) NAME VARCHAR ( ));

SELECT * FROM t_mg;

MERGE INTO t_mg a

USING (SELECT the code code the name NAME FROM dual) b

ON (de = de)

WHEN MATCHED THEN

UPDATE SET a NAME = b NAME

WHEN NOT MATCHED THEN

INSERT (code NAME) VALUES (de b NAME);

抽取/删除重复记录

DROP TABLE t_dup;

CREATE TABLE t_dup AS SELECT code_ ||ROWNUM code dbms_random string( z ) NAME FROM dual CONNECT BY ROWNUM= ;

INSERT INTO t_dup SELECT code_ ||ROWNUM code dbms_random string( z ) NAME FROM dual CONNECT BY ROWNUM= ;

SELECT * FROM t_dup;

SELECT * FROM t_dup a WHERE a ROWID (SELECT MIN(b ROWID) FROM t_dup b WHERE de=de);

SELECT de b NAME

FROM (SELECT de

a NAME

row_number() over(PARTITION BY de ORDER BY a ROWID) rn

FROM t_dup a) b

WHERE b rn ;

IN/EXISTS的不同适用环境

t_orders customer_id有索引

SELECT a *

FROM t_employees a

WHERE a employee_id IN

(SELECT b sales_rep_id FROM t_orders b WHERE b customer_id = );

SELECT a *

FROM t_employees a

WHERE EXISTS (SELECT

FROM t_orders b

WHERE b customer_id =

AND a employee_id = b sales_rep_id);

t_employees department_id有索引

SELECT a *

FROM t_employees a

WHERE a department_id =

AND EXISTS

(SELECT FROM t_orders b WHERE a employee_id = b sales_rep_id);

SELECT a *

FROM t_employees a

WHERE a department_id =

AND a employee_id IN (SELECT b sales_rep_id FROM t_orders b);

FBI

DROP TABLE t_fbi;

CREATE TABLE t_fbi AS

SELECT ROWNUM rn dbms_random STRING( z ) NAME SYSDATE + dbms_random VALUE * dt FROM dual

CONNECT BY ROWNUM = ;

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_ ;

CREATE INDEX idx_fbi_ ON t_fbi(trunc(dt));

SELECT * FROM t_fbi WHERE trunc(dt) = to_date( yyyy mm dd ) ;

不建议使用

SELECT * FROM t_fbi WHERE to_char(dt yyyy mm dd ) = ;

LOOP中的MIT/ROLLBACK

DROP TABLE t_loop PURGE;

create TABLE t_loop AS SELECT * FROM user_objects WHERE = ;

SELECT * FROM t_loop;

逐行提交

DECLARE

BEGIN

FOR cur IN (SELECT * FROM user_objects) LOOP

INSERT INTO t_loop VALUES cur;

MIT;

END LOOP;

END;

模拟批量提交

DECLARE

v_count NUMBER;

BEGIN

FOR cur IN (SELECT * FROM user_objects) LOOP

INSERT INTO t_loop VALUES cur;

v_count := v_count + ;

IF v_count = THEN

MIT;

END IF;

END LOOP;

MIT;

END;

真正的批量提交

DECLARE

CURSOR cur IS

SELECT * FROM user_objects;

TYPE rec IS TABLE OF user_objects%ROWTYPE;

recs rec;

BEGIN

OPEN cur;

WHILE (TRUE) LOOP

FETCH cur BULK COLLECT

INTO recs LIMIT ;

forall 实现批量

FORALL i IN recs COUNT

INSERT INTO t_loop VALUES recs (i);

MIT;

EXIT WHEN cur%NOTFOUND;

END LOOP;

CLOSE cur;

END;

悲观锁定/乐观锁定

DROP TABLE t_lock PURGE;

CREATE TABLE t_lock AS SELECT ID FROM dual;

SELECT * FROM t_lock;

常见的实现逻辑 隐含bug

DECLARE

v_cnt NUMBER;

BEGIN

这里有并发性的bug

SELECT MAX(ID) INTO v_cnt FROM t_lock;

here for other operation

v_cnt := v_cnt + ;

INSERT INTO t_lock (ID) VALUES (v_cnt);

MIT;

END;

高并发环境下 安全的实现逻辑

DECLARE

v_cnt NUMBER;

BEGIN

对指定的行取得lock

SELECT ID INTO v_cnt FROM t_lock WHERE ID= FOR UPDATE;

在有lock的情况下继续下面的操作

SELECT MAX(ID) INTO v_cnt FROM t_lock;

here for other operation

v_cnt := v_cnt + ;

INSERT INTO t_lock (ID) VALUES (v_cnt);

MIT; 提交并且释放lock

END;

硬解析/软解析

DROP TABLE t_hard PURGE;

CREATE TABLE t_hard (ID INT);

SELECT * FROM t_hard;

DECLARE

sql_ VARCHAR ( );

BEGIN

hard parse

java中的同等语句是 Statement execute()

FOR i IN LOOP

sql_ := insert into t_hard(id) values( || i || ) ;

EXECUTE IMMEDIATE sql_ ;

END LOOP;

MIT;

soft parse

java中的同等语句是 PreparedStatement execute()

sql_ := insert into t_hard(id) values(:id) ;

FOR i IN LOOP

EXECUTE IMMEDIATE sql_

USING i;

END LOOP;

MIT;

END;

正确的分页算法

SELECT *

FROM (SELECT a * ROWNUM rn

FROM (SELECT * FROM t_employees ORDER BY first_name) a

WHERE ROWNUM = )

WHERE rn ;

分页算法(why not this one)

SELECT a * ROWNUM rn

FROM (SELECT * FROM t_employees ORDER BY first_name) a

WHERE ROWNUM = AND ROWNUM ;

分页算法(why not this one)

SELECT b *

FROM (SELECT a * ROWNUM rn

FROM t_employees a

WHERE ROWNUM =

ORDER BY first_name) b

WHERE b rn ;

OLAP

小计合计

SELECT CASE

WHEN a deptno IS NULL THEN

合计

WHEN a deptno IS NOT NULL AND a empno IS NULL THEN

小计

ELSE

|| a deptno

END deptno

a empno

a ename

SUM(a sal) total_sal

FROM scott emp a

GROUP BY GROUPING SETS((a deptno) (a deptno a empno a ename) ());

分组排序

SELECT a deptno

a empno

a ename

a sal

可跳跃的rank

rank() over(PARTITION BY a deptno ORDER BY a sal DESC) r

密集型rank

dense_rank() over(PARTITION BY a deptno ORDER BY a sal DESC) r

不分组排序

rank() over(ORDER BY sal DESC) r

FROM scott emp a

ORDER BY a deptno a sal DESC;

当前行数据和前/后n行的数据比较

SELECT a empno

a ename

a sal

上面一行

lag(a sal) over(ORDER BY a sal DESC) lag_

下面三行

lead(a sal ) over(ORDER BY a sal DESC) lead_

FROM scott emp a

lishixinzhi/Article/program/Oracle/201311/16728

从一条select语句看Oracle数据库查询工作原理

假如 我们现在利用Select语句从数据库查询数据 Oracle数据库是如何运作的呢?从中我们可以领悟到什么呢?下面 就结合一条简单的select语句 看看Oracle数据库后台的运作机制 这对于我们之后的系统管理与故障排除非常有帮助

第一步 客户端把语句发给服务器端执行

当我们在客户端执行select语句时 客户端会把这条SQL语句发送给服务器端 让服务器端的进程来处理这语句 也就是说 Oracle客户端是不会做任何的操作 他的主要任务就是把客户端产生的一些SQL语句发送给服务器端 虽然在客户端也有一个数据库进程 但是 这个进程的作用跟服务器上的进程作用事不相同的 服务器上的数据库进程才会对SQL语句进行相关的处理 不过 有个问题需要说明 就是客户端的进程跟服务器的进程是一一对应的 也就是说 在客户端连接上服务器后 在客户端与服务器端都会形成一个进程 客户端上的我们叫做客户端进程;而服务器上的我们叫做服务器进程 所以 由于所有的SQL语句都是服务器进程执行的 所以 有些人把服务器进程形象地比喻成客户端进程的 影子

第二步 语句解析

当客户端把SQL语句传送到服务器后 服务器进程会对该语句进行解析 同理 这个解析的工作 也是在服务器端所进行的 虽然这只是一个解析的动作 但是 其会做很多 小动作

查询高速缓存 服务器进程在接到客户端传送过来的SQL语句时 不会直接去数据库查询 而是会先在数据库的高速缓存中去查找 是否存在相同语句的执行计划 如果在数据高速缓存中 刚好有其他人使用这个查询语句的话 则服务器进程就会直接执行这个SQL语句 省去后续的工作 所以 采用高速数据缓存的话 可以提高SQL语句的查询效率 一方面是从内存中读取数据要比从硬盘中的数据文件中读取数据效率要高 另一方面 也是因为这个语句解析的原因

不过这里要注意一点 这个数据缓存跟有些客户端软件的数据缓存是两码事 有些客户端软件为了提高查询效率 会在应用软件的客户端设置数据缓存 由于这些数据缓存的存在 可以提高客户端应用软件的查询效率 但是 若其他人在服务器进行了相关的修改 由于应用软件数据缓存的存在 导致修改的数据不能及时反映到客户端上 从这也可以看出 应用软件的数据缓存跟数据库服务器的高速数据缓存不是一码事

语句合法性检查

当在高速缓存中找不到对应的SQL语句时 则数据库服务器进程就会开始检查这条语句的合法性 这里主要是对SQL语句的语法进行检查 看看其是否合乎语法规则 如果服务器进程认为这条SQL语句不符合语法规则的时候 就会把这个错误信息 反馈给客户端 在这个语法检查的过程中 不会对SQL语句中所包含的表名 列名等等进行SQL他只是语法上的检查

语言含义检查

若SQL语句符合语法上的定义的话 则服务器进程接下去会对语句中的字段 表等内容进行检查 看看这些字段 表是否在数据库中 如果表名与列名不准确的话 则数据库会就会反馈错误信息给客户端

所以 有时候我们写select语句的时候 若语法与表名或者列名同时写错的话 则系统是先提示说语法错误 等到语法完全正确后 再提示说列名或表名错误 若能够掌握这个顺序的话 则在应用程序排错的时候 可以节省时间

获得对象解析锁

当语法 语义都正确后 系统就会对我们需要查询的对象加锁 这主要是为了保障数据的一致性 防止我们在查询的过程中 其他用户对这个对象的结构发生改变 对于加锁的原理与方法 我在其他文章中已经有专门叙述 在这里就略过不谈了

数据访问权限的核对

当语法 语义通过检查之后 客户端还不一定能够取得数据 服务器进程还会检查 你所连接的用户是否有这个数据访问的权限 若你连接上服务器的用户不具有数据访问权限的话 则客户端就不能够取得这些数据 故 有时候我们查询数据的时候 辛辛苦苦地把SQL语句写好 编译通过 但是 最后系统返回个 没有权限访问数据 的错误信息 让我们气半死 这在前端应用软件开发调试的过程中 可能会碰到 所以 要注意这个问题 数据库服务器进程先检查语法与语义 然后才会检查访问权限

确定最佳执行计划

当语句与语法都没有问题 权限也匹配的话 服务器进程还是不会直接对数据库文件进行查询 服务器进程会根据一定的规则 对这条语句进行优化 不过要注意 这个优化是有限的 一般在应用软件开发的过程中 需要对数据库的sql语言进行优化 这个优化的作用要大大地大于服务器进程的自我优化 所以 一般在应用软件开发的时候 数据库的优化是少不了的

当服务器进程的优化器确定这条查询语句的最佳执行计划后 就会将这条SQL语句与执行计划保存到数据高速缓存 如此的话 等以后还有这个查询时 就会省略以上的语法 语义与权限检查的步骤 而直接执行SQL语句 提高SQL语句处理效率

第三步 语句执行

语句解析只是对SQL语句的语法进行解析 以确保服务器能够知道这条语句到底表达的是什么意思 等到语句解析完成之后 数据库服务器进程才会真正的执行这条SQL语句

这个语句执行也分两种情况 一是若被选择行所在的数据块已经被读取到数据缓冲区的话 则服务器进程会直接把这个数据传递给客户端 而不是从数据库文件中去查询数据 若数据不在缓冲区中 则服务器进程将从数据库文件中查询相关数据 并把这些数据放入到数据缓冲区中

这里仍然要注意一点 就是Oracle数据库中 定义了很多种类的高速缓存 像上面所说的SQL语句缓存与现在讲的数据缓存 我们在学习数据库的时候 需要对这些缓存有一个清晰的认识 并了解各个种类缓存的作用 这对于我们后续数据库维护与数据库优化是非常有用的

第四步 提取数据

当语句执行完成之后 查询到的数据还是在服务器进程中 还没有被传送到客户端的用户进程 所以 在服务器端的进程中 有一个专门负责数据提取的一段代码 他的作用就是把查询到的数据结果返回给用户端进程 从而完成整个查询动作

从这整个查询处理过程中 我们在数据库开发或者应用软件开发过程中 需要注意以下几点

一是要了解数据库缓存跟应用软件缓存是两码事情 数据库缓存只有在数据库服务器端才存在 在客户端是不存在的 只有如此 才能够保证数据库缓存中的内容跟数据库文件的内容一致 才能够根据相关的规则 防止数据脏读 错读的发生 而应用软件所涉及的数据缓存 由于跟数据库缓存不是一码事情 所以 应用软件的数据缓存虽然可以提高数据的查询效率 但是 却打破了数据一致性的要求 有时候会发生脏读 错读等情况的发生 所以 有时候 在应用软件上有专门一个功能 用来在必要的时候清除数据缓存 不过 这个数据缓存的清除 也只是清除本机上的数据缓存 或者说 只是清除这个应用程序的数据缓存 而不会清除数据库的数据缓存

lishixinzhi/Article/program/Oracle/201311/17595

oracle数据库入门教程

oracle是块结构语言，oracle程序划分成几个部分，并在每个部分中写入逻辑代码块，每个块由三个子部分组成。

声明部分：此部分是以关键字DECLARE开头。这是一个可选部分，并定义了程序中要使用的所有变量，游标，子程序和其他元素。

可执行命令部分：此部分包含在关键字BEGIN和END之间，这是一个强制性部分。由程序的可执行oracle语句组成。应该有至少一个可执行代码行，可以只是一个NULL命令，表示不执行任何操作。

异常处理部分 ：此部分以关键字EXCEPTION开头。这是一个可选部分，包含处理程序中错误的异常。

每个oracle语句以分号(;)结尾。使用BEGIN和END可以将oracle块嵌套在其他oracle块中。以下是Poracle块的基本结构。

Hello World示例：

END；行表示oracle块的结尾。要从oracle命令行运行代码，需要在代码的最后一行之后键入/字符。当上述代码在oracle提示符下执行时，它会产生以下结果：

oracle标识符：oracle标识符是常量，变量，异常，过程，游标和保留字。标识符包括一个字母，可选地后跟多个字母，数字，美元符号，下划线和数字符号，不得超过30个字符。

默认情况下，标识符不区分大小写。例如，可以使用integer或INTEGER来表示一个数值。 不能使用保留关键字作为标识符。

扩展资料

oracle注释程序：注释可以在编写的oracle代码中包含的说明性文字，并帮助其他人阅读源代码。所有编程语言都允许某种形式的注释。

oracle支持单行和多行注释。注释中的所有字符都被oracle编译器忽略。oracle单行注释以分隔符开头 --(双连字符)，多行注释由/*和*/括起来。

当上述代码在oracle提示符下执行时，会产生以下结果：

oracle变量的名称由可选的字母，数字，美元($)符号，下划线和数字符号组成，不能超过30个字符。 默认情况下，变量名不区分大小写。不能将保留的oracle关键字用作变量名称。

oracle编程语言允许定义各种类型的变量，如：日期时间数据类型，记录，集合等，我们将在后面的章节中介绍。 在本章中仅学习基本的变量类型。

文章名称：oracle语句怎么看懂 oracle解析sql语句
标题URL：http://chengdu.cdxwcx.cn/article/hgggsp.html