|
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.
| 
转播
