Explain进行查询及应用优化

2/9/2008来源:Oracle教程人气:4273


  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列