如何解析oracle执行计划

一、通过PL/SQL Dev工具

创新互联公司服务项目包括新安网站建设、新安网站制作、新安网页制作以及新安网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，新安网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到新安省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！

1、直接File-New-Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。

2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。

注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。

二、通过sqlplus

1．最简单的办法

Sql set autotrace on

Sql select * from dual;

执行完语句后，会显示explain plan 与 统计信息。

这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：

Sql set autotrace traceonly

这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：

(1）在要分析的用户下：

Sqlplus @ ?

dbmsadminutlxplan.sql

(2) 用sys用户登陆

Sqlplus @ ?sqlplusadminplustrce.sql

Sqlplus grant plustrace to user_name;

- - user_name是上面所说的分析用户

　2．用explain plan命令

(1) sqlplus explain plan for select * from testdb.myuser

(2) sqlplus select * from table(dbms_xplan.display);

上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：

SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,

buffer_gets/executions AVG FROM v$sqlarea

WHERE executions0 AND buffer_gets 100000 ORDER BY 5;

ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG

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

66D83D64 select t.name, (sel 421531 60104 7.01336017

66D9E8AC select t.schema, t.n 1141739 2732 417.913250

66B82BCC select s.synonym_nam 441261 6 73543.5

从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。

3、启用SQL_TRACE跟踪所有后台进程活动：

全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)

当前session中设置：

SQL alter session set SQL_TRACE=true;

SQL select * from dual;

SQL alter session set SQL_TRACE=false;

对其他用户进行跟踪设置：

SQL select sid,serial#,username from v$session where username='XXX';

SID SERIAL# USERNAME

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

127 31923 A

128 54521 B

开启跟踪:SQL exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);

关闭跟踪:SQL exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);

然后使用oracle自带的tkprof命令行工具格式化跟踪文件。

4、使用10046事件进行查询：

10046事件级别：

Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE

Lv4 - Level 1 + 绑定值(bind values)

Lv8 - Level 1 + 等待事件跟踪

Lv12 - Level 1 + Level 4 + Level 8

全局设定：

OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"

当前session设定：

开启:SQL alter session set events '10046 trace name context forever, level 8';

关闭:SQL alter session set events '10046 trace name context off';

对其他用户进行设置：

SQL select sid,serial#,username from v$session where username='XXX';

SID SERIAL# USERNAME

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

127 31923 A

SQL exec dbms_system.set_ev(127,31923,10046,8,'A');

5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)

一般，一次跟踪可以分为以下几步：

1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。

2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。

3、找到跟踪文件，并对其进行格式化，然后阅读或分析。

--使用一下SQL找到当前session的跟踪文件：

SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_namefrom( select p.spid from v$mystat m,v$session s, v$process pwhere m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,( select t.instance from v$thread t,v$parameter vwhere 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' ) d;-- 其它用户的 session SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name from ( select p.spid from v$session s, v$process p where s.sid= '27' and s. SERIAL#= '30' 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' ) d;

--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中

SQL $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc d:/explain_format.txt

文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)

TKPROF: Release 9.2.0.1.0 - Production on 星期二 4月 20 13:59:20 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call********************************************************************************

alter session set events '10046 trace name context forever, level 8'

call count cpu elapsed disk query current rows

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

Parse 0 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

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

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

Misses in library cache during execute: 1

Optimizer goal: CHOOSE

Parsing user id: SYS

2020-01-20 oracle中sql如何执行，什么是硬解析和软解析

1.语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。

2.语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。

3.权限检查：看用户是否具备访问该数据的权限。

4.共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析和硬解析又该怎么理解呢？在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。

5.优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。

6.执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执行语句了。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。我们上面已经讲到了库缓存区，它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

如何避免硬解析，尽量使用软解析呢？在 Oracle 中，绑定变量是它的一大特色。绑定变量就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。

Oracle中的硬解析与软解析

Oracle中的SQL在执行之前进行解析 一个硬解析包括下面的步骤

加载到共享池中 SQL源代码被加载到内存中

语法解析 Oracle检查语法拼写错误

语义解析 Oracle验证来自数据字典的所有表名和列名并且验证你是否有权访问这些数据

查询转换 如果允许(query_rewrite=true) oracle将把复杂的SQL转换为等价的简单形式

优化 根据模式的统计信息创建执行计划（在 g中或许会使用动态的样本统计信息）

创建可执行文件 Oracle创建一个服务于SQL查询的 调用本地文件的可执行文件

Oracle提供了 shared_pool_size参数来缓存SQL从而使我们不需要重复解析SQL 但是 如果shared_pool_size设置太小或者在代码中使用了非重用的SQL（例如包含直接量where name= fred ) SQL语句可能会过期

在Oracle中软解析和硬解析的区别是什么？仅仅是上面用红色标明的第一步 也就是说 软解析不需要重新装载到共享池（以及相关的内存分配）

通常很高的解析调用次数（ /秒）表明你的系统有大量不同的SQL语句 或者你的SQL语句没有被重用（例如 没有使用绑定变量）

硬解析需要把 SQL语句加载到共享池中 硬解析比软解析差很多 因为它牵涉到共享池中内存的分配和管理 一旦被加载 SQL必须完全重新检查语法和语义以及生成可执行目标

如果shared_pool_size设置的太小或者SQL语句没有重用 则会发生大量的硬解析

lishixinzhi/Article/program/Oracle/201311/17371

oracle sql是怎么解析的

导读：Oracle的后台运作原理是什么？我们的一条命令是如何被执行的？今天我们就从一条简单的Select语句开始，看看Oracle数据库后台的运作机制。

Select语句可以说是DBA和数据库开发者在工作中使用最多的语句之一，但这条语句是如何执行？在Oracle数据库中又是如何运作的呢?今天我们就从一条简单的Select语句开始，看看Oracle数据库后台的运作机制。这对于我们之后的系统管理与故障排除非常有帮助。

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

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

第二步：语句解析

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

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

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

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

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

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

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

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

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

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

第三步：语句执行

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

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

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

第四步：提取数据

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

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

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

二是绝大部分SQL语句都是按照这个处理过程处理的。我们DBA或者基于Oracle数据库的开发人员了解这些语句的处理过程，对于我们进行涉及到SQL语句的开发与调试，是非常有帮助的。有时候，掌握这些处理原则，可以减少我们排错的时间。特别要注意，数据库是把数据查询权限的审查放在语法语义的后面进行检查的。所以，有时会若光用数据库的权限控制原则，可能还不能满足应用软件权限控制的需要。此时，就需要应用软件的前台设置，实现权限管理的要求。而且，有时应用数据库的权限管理，也有点显得繁琐，会增加服务器处理的工作量。因此，对于记录、字段等的查询权限控制，大部分程序涉及人员喜欢在应用程序中实现，而不是在数据库上实现。

Oracle分析工具什么时候使用？如何使用？

...分析可以使用关键字 analyze

或者使用三方工具 比如 plsql里面就有相关功能

分享文章：oracle解析器怎么看 oracle查询分析器
链接分享：http://kswjz.com/article/hjgosc.html