对SQL语句进行分析和优化
安装和查看ORACLE执行计划
ORACLE在执行SQL语句时使用的步骤的集合叫做执行计划
前起条件：
在目录：$ORACLE_HOME/RDBMS/ADMIN目录下的执行utlxplan.sql
查看执行计划：
EXPLAN PLAN FOR
CREDIT @ORCL>explain plan for select * from creditcard;
Explained.
看SQL执行计划的信息
CREDIT @ORCL>select a.operation,options,object_name,object_type,id,parent_id from plan_table a order by id;
更直观：
CREDIT @ORCL>select lpad(' ',2*(level-1)) || operation || ' ' || options || ' ' || object_name || ' ' || decode(id,0,'cost='||position) "Query Plan" from plan_table connect by prior id=parent_id;
Query Plan
------------------------------------------------------------------------------------------------------------------------
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
这个也可以查询：
CREDIT @ORCL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2658862924
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1332 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CREDITCARD | 9 | 1332 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
打开自动跟踪功能：
set autotrace on
通过ROWID访问表的执行计划：
SYS AS SYSDBA@ORCL>explain plan for
2 select * from hr.departments where rowid='AAAR5QAAFAAAACvAAa';
Explained.
Elapsed: 00:00:00.05
SYS AS SYSDBA@ORCL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 313428322
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
8 rows selected.
连接查询的执行计划：
优化案例分析:
提高GROUP BY 语句的效率：
select cardno,sum(amount) from consume group by cardno having cardno='9555xxxx3' or cardno='9555xxxx8';
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 4475K| 175 (3)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 114K| 4475K| 175 (3)| 00:00:03 |
| 3 | TABLE ACCESS FULL| CONSUME | 114K| 4475K| 171 (1)| 00:00:03 |
-------------------------------------------------------------------------------
1. 进行全表扫描TABLE ACCESS FULL
2.执行分组统计HASH GROUP BY
3.执行过滤操作FILTER
分析：过滤操作在分组统计之后，所有分组统计处理的数据量比较大
优化后语句：
select cardno,sum(amount) from consume where "CARDNO"='9555xxxx3' OR "CARDNO"='9555xxxx8' group by cardno;
使用EXISTS代替IN关键字
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
方法1：运行以下脚本，生成plan_table表
SQL> @/u01/app/oracle/product/10.2/db_1/rdbms/admin/utlxplan.sql
Table created.
SQL> explain plan for
2 select deptno from scott.dept group by deptno;
Explained.
SQL> select id,operation,options,object_name,position from plan_table;
ID OPERATION OPTIONS OBJECT_NAME POSITION
---- -------------------- --------------- ------------------------- ----------
0 SELECT STATEMENT 1
1 SORT GROUP BY NOSORT 1
2 INDEX FULL SCAN PK_DEPT 1
方法2：oracle提供v$sql_plan来
SQL> select id,options,operation,object_name,cost
2 from v$sql_plan
3 where object_owner='SCOTT';
no rows selected－－没有数据的原因是：刚刚的explain plan for命令只产生执行计划，而不是真正执行语句
SQL> select deptno from scott.dept group by deptno;
DEPTNO
----------
10
20
30
40
SQL> select id,operation,options,object_name,position from plan_table;
ID OPERATION OPTIONS OBJECT_NAME POSITION
---- -------------------- -------------------- -------------------- ----------
0 SELECT STATEMENT 1
1 SORT GROUP BY NOSORT 1
2 INDEX FULL SCAN PK_DEPT 1
站在用户的角度思考问题，与客户深入沟通，找到江干网站设计与江干网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：成都网站设计、成都网站建设、企业官网、英文网站、手机端网站、网站推广、空间域名、雅安服务器托管、企业邮箱。业务覆盖江干地区。
文章题目：对SQL语句进行分析和优化
文章链接：http://myzitong.com/article/jecgpe.html