对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