oralce执行计划分析优化

2024-02-12 16:08

本文主要是介绍oralce执行计划分析优化,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

 使用Explain
摘要:
Oracle RDBMS执行每一条SQL语句,都必须经过Oracle优化器的评估。所 以,了解优化器是如何选择(搜索)路径以及索引是如何被使用的,对优化SQL语句有很大的帮助。本文详细讨论了一种用于优化应用的性能诊断工具:Explain的使用方法。


介绍:

应 用的优化不仅需要知道应用作了什么,还必须知道应用是如何工作的以及使用何种数据库设计来支持,必须了解使用哪种类型的SQL语句,语句中表与视图的结构 及与这些表相关的各类索引。另外,优化整个应用系统可能并不是必需的,了解应用的各个部分可以让我们了解哪些部分是需要优化的。我们将主要讨论使用 Oracle RDBMS提供的性能优化工具进行SQL级的优化。

Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。Access Path对性能会有非常大的影响。我们将会讨论各种Access Path和使用的优缺点。


使用Explain

使用Explain工具需要创建Explain_plan表,这必须先进入相关应用表、视图和索引的所有者的帐户内。Oracle的介质中包含有执行此项工作的SQL源程序,例如:

ORA_RDBMS: XPLAINPL.SQL (VMS)

$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)

这个SQL程序应与catalog.sql在同一目录,这个程序会创建一个名为plan_table的表,表结构如下:

Name Type

STATEMEN_ID VARCHAR2(30)

TIMESTAMP DATE

REMARKS VARCHAR2(80)

OPERATION VARCHAR2(30)

OPTIONS VARCHAR2(30)

Object_node VARCHAR2(128)

Object_owner VARCHAR2(30)

Object_name VARCHAR2(30)

Object_instance NUMBER(38)

Object_type VARCHAR2(30)

Search_columns NUMBER(38)

ID NUMBER(38)

PARENT_ID NUMBER(38)

POSITION NUMBER(38)

OTHER LONG

这里介绍一些我们将会讨论的column的主要概念。如果需要每一个column的详细介绍,请看explain.doc文件。

STATEMENT_ID:为一条指定的SQL语句确定特定的执行计划名称。如果在EXPLAN PLAN语句中没有使用SET STATEMENT_ID,那么此值会被设为NULL。

OPERATION:在计划的某一步骤执行的操作名称,例如:Filters,Index,Table,Marge Joins and Table等。

OPTION:对OPERATION操作的补充,例如:对一个表的操作,OPERATION可能是TABLE ACCESS,但OPTION可能为by ROWID或FULL。

Object_Owner:拥有此database Object的Schema名或Oracle帐户名。

Object_name:Database Object名

Object_type:类型,例如:表、视图、索引等等

ID:指明某一步骤在执行计划中的位置。

PARENT_ID:指明从某一操作中取得信息的前一个操作。通过对与ID和PARENT_ID使用Connect By操作,我们可以查询整个执行计划树。

这个PLAN表一旦创建成功,用户就可在应用中使用EXPLAIN。使用语法如下:

EXPLAIN PLAN [ SET STATEMENT_ID [=] < string literal > ]

[ INTO < table_name > ]

FOR < sql_statement >

其中:

STATEMENT_ID是一个唯一的字符串,把当前执行计划与存储在同一PLAN表中的其它执行计划区别开来。

TABLE_NAME是plan表名,它结构如前所示,你可以任意设定这个名称。

SQL_STATEMENT是真正的SQL语句。

例如:

EXPLAIN PLAN

SET STATEMENT_ID=‘QUERY1’

FOR

SELECT

FROM EMP

WHERE DEPTNO=10;

执行后将会得到以下信息:

operation 50 succeeded

注意,如果在Explain语句中忽略INTO句,则EXPLAIN会使用PLAN_TABLE作为表名,我们可以用查询plan table的方法来检查执行计划,如:

SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID

FROM PLAN_TABLE

WHERE STATEMENT_ID=‘QUERY1’

ORDER BY ID;

将会返回如下:

OPERATION OPTION Object_name Object_type ID Parent_ID

TABLE ACCESS FULL EMP TABLE 1 1

1 row selected

这意味在这个查询中将会使用全表扫描,如果在EMP表上没有创建索引,对EMP的所有查询都将使用全表扫描,但是如果在DEPTNO列上创建一个非唯一的索引:

CREATE INDEX EMP_IDX ON EMP(DEPTNO);

现在,如果我们重新解释查询:

EXPLAIN PLAN

SET STATEMENT_ID=’QUERY2’

FOR

SELECT *

FROM EMP

WHERE DEPTNO=10;

然后检查计划表:

SELECT OPERATION, OPTIONS, OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID

FROM PLAN_TABLE

WHERE STATEMENT_ID=‘QUERY2’

ORDER BY IB;

将返回:

OPERATION OPTION Object_name Object_type ID Parent_ID

TABLE ACCESS BY RAWID EMP TABLE 1

INDEX RANGE SCAN EMP_IDX NON_UNIQUE 2 1

2 row selected

这 样,我们可以看到索引EMP_IDX被用于得到所有DEPTNO等于10的行,然后根据ROWID取得数据,索引存储了表中每行的ROWID,每当在索引 中找到一行,就会根据ROWID去查询该行的其余部分。如果是对一个很大的表的操作,这样的搜索路径较前一种(全表扫描)会对减少磁盘 I / O 操作有明显的效果。但是,如果索引是“低选择性的”,那么一个全表扫描可能会更有效。

考虑以下的查询及其执行计划:

EXPLAIN PLAN

SET STATEMENT_ID=‘QUERY3’

FOR

SELECT DEPTNO

FROM EMP

WHERE DEPTNO=10

执行路径的计划是:

OPERATION OPTION Object_name Object_type ID Parent_ID

INDEX RANGE_SCAN EMP_IDX NON_UNIQUE 1

以上的执行计划表示不需在table中取得数据,此查询只须使用索引。



EXPLAIN搜索路径解释

任 何SQL语句的执行计划都遵循一些优化原则,这些原则在Oracle数据库管理员手册中有详细介绍。同时,这些原则也被列在文本100040.163中。 这些原则都试图在从数据库取出数据时找出一条最佳搜索路径。一旦优化器评估过查询并确定了搜索路径,优化器就会创建一个执行计划树。我们可以使用 SQL*Plus查询plan table从而看到执行计划树:

COLUMN plan FORMAT a70

select lpad (‘ ‘, 3*level) || operation || (‘ || options ||’) ‘|| object_name || ‘ ‘ || object_type

from plan_table

connect by prior id=parent_id and statement_id=‘ & stmt_id’;

例如下面这个查询

SELECT ENAME

FROM EMP

WHERE DEPTNO=10

ORDER BY ENAME;

使用以上SQL语句检查plan table的结果是:

SORT ( ORDER BY )

TABLE ACCESS ( BY ROWID ) EMP

INDEX ( RANGE SCAN ) EMP_IDX N0N_UNIQUE

这个执行计划树表示在EMP_IDX索引上执行一个索引扫描,然后ENAME数据被按照ROWID从表中取了出来,最后这些数据被ORDER BY操作归类。如果EMP表大的话,那么这个执行计划树的最后一步可能花较长的时间。

假设我们解释如下查询:

select deptno, ename

from emp

where deptno between 1 and 30

order by deptno;

那么执行树为:

TABLE ACCESS ( BY ROWID) EMP

INDEX (RANGE SCAN) EMP_IDX NON_UNIQUE

请 注意,虽然在查询时使用了order by,但在执行树中并未出现SORT (ORDER BY)。为什么呢?不使用SORT有二个原因:1) deptno列上已经建立了index,已作过sort;2)deptno被定义为not null(如:DEPTNO NOT NULL NUMBER)。

假设下面这个普通的连接查询:

SELECT *

from emp. dept

where emp.deptno=dept.deptno

and sal >5000;

执行树为:

NESTED LOOPS ()

TABLE ACCESS (FULL)DEPT

TABLE ACCESS (BY ROWID)EMP

INDEX (RANGE SCAN) EMP_IDX NON_UNIQUE

NESTED LOOPS意味着在一个表(DEPT)上作了一个序列查询,同时在EMP表上的索引EMP_IDX中,每一个DEPTNO均作查找。这个查询被称为一个驱 动表( driving table )。在这种情况下,驱动表是DEPT。在这种类型的连接中,驱动表是被列在后面的表。因为两个表有相同级别的搜索路径 (都在deptno列上有非唯一的索引) ,既然至少有一个表上的所有记录必须被检索,那么在一个表上执行全表扫描,同时在另一个表的索引上寻找符合条件的记录是比较有效的。

这 种情况下,我们应把具有最少列的表作为驱动表放在from子句的最后,注意,在这种类型的连接中,from子句中表的先后次序决定了RDBMS的搜索路 径。如果where条件是有一个是返回一个单记录的话,那么以上查询中的驱动表应使用连接查询。假设在EMP表的EMPNO列上有一个唯一索引,那么以下 SQL语句:

SELECT *

from EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO

AND EMPNO=7735;

的执行树是:

NESTED LOOPS ( )

TABLE ACCESS (BY ROWID) EMP

INDEX (UNIQUE SCAN) EMP_UIDX UNIQUE

TABLE ACCESS (BY ROWID)DEPT

INDEX (RANGE SCAN) DEPT_IDX NON_UNIQUE

这个执行计划表示NESTED LOOPS只会执行一次,因为在驱动表EMP上执行了一个唯一索引检索。同时,在DEPT_IDX上进行索引检索。如果可以找到记录,那么可以根据ROWID取得这条记录。

在 Explain的输出中还可以看出其它的操作情况。例如查询中包括一个“connect by prior...”子句时,要使用connect by操作,RDBMS会根据连接条件在表上执行树遍历。我们刚才查询的执行计划树就是一个这样的例子。我们可以在这个查询中使用Explain并检查输 出。

我们还可以使用其它操作,例如FILTERS、PROJECT、UNION等。下面这个查询作一个FILTER操作:

SELECT DEPTNO, AVG (SAL), SUM(COM)

from emp

Group by deptno

having SUM(COMM)>500;

执行计划如下:

FILTERS ( )

SORT (GROUP BY)

TABLE ACCESS (FULL) EMP

执行完sort操作后,FILTERS会找出所有COMM大于500的记录。

PROJECT操作不太常见。当使用MINUS和UNIONS在所有的列上查询这些列的一个子集时才会用到。如果在SQL语句中使用了MINUS和UNION,那么在执行计划中我们也可以找到这两个操作。



视图:

我们还可以用EXPLAIN解释虚拟表或视图。因为应用中也会使用到,所以这样作是很有用的。我们只需对检索视图的查询使用EXPLAIN,就可以找到视图的搜索路径。假设如下视图:

create view EMP_VIEW AS

SELECT *

FROM EMP

WHERE DEPTNO>20;

如果只是从视图中查询,那么执行计划树如下:

TABLE ACCESS (BY) ROWID EMP

INDEX (RANGE SCAN) EMP_IDX NON_UNNIQUE

如果把这个视图与DEPT连接:

SELECT ENAME, LOC

FROM EMP_VIEWS, DEPT;

那么执行计划为:

NESTED LOOPS ()

TABLE ACCESS (FULL )DEPT

TABLE ACCESS (BY ROWID) EMP

INDEX (RANGE SCAN) EMP_IDX NON_UNIQUE

这与我们曾经提到的例子相似,如果在执行计划中某一步的操作是“view’,这是另一个操作,用于完成它的上一级操作。在视图定义在二个或多个表连接的情况下,会用到VIEW操作,这是一种不同的情况。

如果你需要在你的应用中使用视图,那么应该先确定这些视图会对你的性能有很大的益处。Oracle工程师经常发现在许多应用系统中,不正确的使用视图,这样对性能毫无益处。



限制:

虽 然任何SQL语句都可以用EXPLAIN解释,但对于没有查询的INSERT,UPDATE,DELETE操作来说,这个工具并没有太大的用处。没有子查 询的INSERT操作不会创建执行计划,但没有WHERE子句或子查询的UPDATE和DELETE操作会创建执行计划,因为这些操作必须先找出所要的记 录。

另外,如果你在SQL语句中使用其它类型如sequence等,EXPLAIN也能揭示它的用法。

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



总结:

优化应用不但需要了解整个数据库的结构,而且还必须对各种SQL语句的结构有相当的知识。你可以使用Explain这个工具来确认你使用的搜索路径是否是最快的一种。在优化应用时,SQL语句的优化应该是最先考虑的,然后再考虑数据库与操作系统的优化。

 

 

http://blog.csdn.net/loestar/archive/2005/05/31/385092.aspx

这篇关于oralce执行计划分析优化的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/702974

相关文章

Vue3 的 shallowRef 和 shallowReactive:优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉,那么对 shallowRef 和 shallowReactive 是否了解呢? 在编程和数据结构中,“shallow”(浅层)通常指对数据结构的最外层进行操作,而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素,而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层(Shallow) 定义

HDFS—存储优化(纠删码)

纠删码原理 HDFS 默认情况下,一个文件有3个副本,这样提高了数据的可靠性,但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码,采用计算的方式,可以节省约50%左右的存储空间。 此种方式节约了空间,但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件,都会执行此策略。 默认只开启对 RS-6-3-1024k

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

使用opencv优化图片(画面变清晰)

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化,使其看起来更清晰,同时保持尺寸不变,通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多,主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器:相机传

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

查看原文>>>全流程SWAP农业模型数据制备、敏感性分析及气候变化影响实践技术应用 SWAP模型是由荷兰瓦赫宁根大学开发的先进农作物模型,它综合考虑了土壤-水分-大气以及植被间的相互作用;是一种描述作物生长过程的一种机理性作物生长模型。它不但运用Richard方程,使其能够精确的模拟土壤中水分的运动,而且耦合了WOFOST作物模型使作物的生长描述更为科学。 本文让更多的科研人员和农业工作者

MOLE 2.5 分析分子通道和孔隙

软件介绍 生物大分子通道和孔隙在生物学中发挥着重要作用,例如在分子识别和酶底物特异性方面。 我们介绍了一种名为 MOLE 2.5 的高级软件工具,该工具旨在分析分子通道和孔隙。 与其他可用软件工具的基准测试表明,MOLE 2.5 相比更快、更强大、功能更丰富。作为一项新功能,MOLE 2.5 可以估算已识别通道的物理化学性质。 软件下载 https://pan.quark.cn/s/57

maven 编译构建可以执行的jar包

💝💝💝欢迎莅临我的博客,很高兴能够在这里和您见面!希望您在这里可以感受到一份轻松愉快的氛围,不仅可以获得有趣的内容和知识,也可以畅所欲言、分享您的想法和见解。 推荐:「stormsha的主页」👈,「stormsha的知识库」👈持续学习,不断总结,共同进步,为了踏实,做好当下事儿~ 专栏导航 Python系列: Python面试题合集,剑指大厂Git系列: Git操作技巧GO

衡石分析平台使用手册-单机安装及启动

单机安装及启动​ 本文讲述如何在单机环境下进行 HENGSHI SENSE 安装的操作过程。 在安装前请确认网络环境,如果是隔离环境,无法连接互联网时,请先按照 离线环境安装依赖的指导进行依赖包的安装,然后按照本文的指导继续操作。如果网络环境可以连接互联网,请直接按照本文的指导进行安装。 准备工作​ 请参考安装环境文档准备安装环境。 配置用户与安装目录。 在操作前请检查您是否有 sud

线性因子模型 - 独立分量分析(ICA)篇

序言 线性因子模型是数据分析与机器学习中的一类重要模型,它们通过引入潜变量( latent variables \text{latent variables} latent variables)来更好地表征数据。其中,独立分量分析( ICA \text{ICA} ICA)作为线性因子模型的一种,以其独特的视角和广泛的应用领域而备受关注。 ICA \text{ICA} ICA旨在将观察到的复杂信号