Go语言视频零基础入门到精通

admin

161.Oracle数据库SQL开发之 SQL优化——比较执行查询的成本
          欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50186053
ORACLE数据库软件使用一个称为优化器的子系统，生成访问表中存储数据的最有效路径。优化器生成的路径称为执行计划。10g及以上版本自动收集表和索引中数据的统计信息，从而生成最优执行计划；称为基于成本的优化。
1.  检查执行计划
可以使用SQL*Plus EXPLAIN PLAN命令检查执行计划。
EXPLAIN PLAN命令使用SQL语句的执行计划填充表plan_table,称为计划表。
执行如下：
store@PDB1> desc plan_table;
  Name                                                           Null?  Type
  ------------------------------------------------------------- ------------------------------------
  STATEMENT_ID                                                              VARCHAR2(30)
  PLAN_ID                                                                 NUMBER
  TIMESTAMP                                                           DATE
  REMARKS                                                               VARCHAR2(4000)
  OPERATION                                                           VARCHAR2(30)
  OPTIONS                                                                 VARCHAR2(255)
  OBJECT_NODE                                                               VARCHAR2(128)
  OBJECT_OWNER                                                           VARCHAR2(128)
  OBJECT_NAME                                                              VARCHAR2(128)
  OBJECT_ALIAS                                                               VARCHAR2(261)
  OBJECT_INSTANCE                                                       NUMBER(38)
  OBJECT_TYPE                                                                 VARCHAR2(30)
  OPTIMIZER                                                            VARCHAR2(255)
  SEARCH_COLUMNS                                                    NUMBER
  ID                                                                    NUMBER(38)
  PARENT_ID                                                            NUMBER(38)
  DEPTH                                                                     NUMBER(38)
  POSITION                                                               NUMBER(38)
  COST                                                                        NUMBER(38)
  CARDINALITY                                                                 NUMBER(38)
  BYTES                                                                      NUMBER(38)
  OTHER_TAG                                                          VARCHAR2(255)
  PARTITION_START                                                        VARCHAR2(255)
  PARTITION_STOP                                                         VARCHAR2(255)
  PARTITION_ID                                                                NUMBER(38)
  OTHER                                                                    LONG
  OTHER_XML                                                          CLOB
  DISTRIBUTION                                                               VARCHAR2(30)
  CPU_COST                                                             NUMBER(38)
  IO_COST                                                                 NUMBER(38)
  TEMP_SPACE                                                         NUMBER(38)
  ACCESS_PREDICATES                                                   VARCHAR2(4000)
  FILTER_PREDICATES                                                     VARCHAR2(4000)
  PROJECTION                                                         VARCHAR2(4000)
  TIME                                                                        NUMBER(38)
  QBLOCK_NAME                                                             VARCHAR2(30)
如果不存在该计划表，执行utlxplan.sql
在$ORACLE_HOME/rdbms/admin/中。
生成执行计划语法如下：
store@PDB1> explain plan setstatement_id="customers" for select customer_id,first_name,last_name fromcustomers;
   
Explained.
查询计划表使用explain_plan.sql脚本如下：
UNDEFINE v_statement_id;
   
SELECT
   id ||
   DECODE(id, 0,"", LPAD(" ", 2*(level - 1))) || " " ||
   operation ||" " ||
   options || "" ||
   object_name|| " " ||
   object_type|| " " ||
   DECODE(cost,NULL, "", "Cost = " || position)
AS execution_plan
FROM plan_table
CONNECT BY PRIOR id = parent_id
AND statement_id = "&&v_statement_id"
START WITH id = 0
AND statement_id = "&v_statement_id";
查询如下：
store@PDB1> @explain_plan.sql
Enter value for v_statement_id: customers
old 12: AND statement_id = "&&v_statement_id"
new 12: AND statement_id = "customers"
old 14: AND statement_id = "&v_statement_id"
new  14:AND statement_id = "customers"
   
EXECUTION_PLAN
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT    Cost = 3
1         TABLE ACCESS FULL CUSTOMERSTABLE Cost = 1
表连接的执行计划如下：
store@PDB1> explain plan setstatement_id="products" for
     selectp.name,pt.name from products p,product_types pt where p.product_type_id =pt.product_type_id;
   
Explained.
查询如下：
store@PDB1> @explain_plan.sql
Enter value for v_statement_id: products
old 12: AND statement_id = "&&v_statement_id"
new 12: AND statement_id = "products"
old 14: AND statement_id = "&v_statement_id"
new 14: AND statement_id = "products"
   
EXECUTION_PLAN
----------------------------------------------------------------------------------------------------
0 SELECT STATEMENT    Cost = 6
1  HASH JOIN       Cost = 1
2    TABLE ACCESS FULL PRODUCT_TYPES TABLE Cost = 1
3    TABLE ACCESS FULL PRODUCTS TABLE Cost = 2
收集表统计信息：
使用10g之前的数据库版本，必须使用ANALYZE命令收集表统计信息。
例如：
store@PDB1> analyze table products computestatistics;
   
Table analyzed.
   
store@PDB1> analyze table product_types computestatistics;
   
Table analyzed.