对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> 更直观：
CREDIT @ORCL>select lpad(' ',2*(level-1)) || operation || ' ' || options || ' ' || object_name || ' ' || decode(id,0,'cost='||position) "Query Plan" from plan_table connect by prior> 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
--------------------------------------------------------------------------------
|> --------------------------------------------------------------------------------
| 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
2select * 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
------------------------------------------------------------------------------------------
|> ------------------------------------------------------------------------------------------
| 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';
-------------------------------------------------------------------------------
|> -------------------------------------------------------------------------------
| 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
2select deptno from scott.dept group by deptno;
Explained.
SQL> select>
> ---- -------------------- --------------- ------------------------- ----------
0 SELECT STATEMENT 1
1 SORT GROUP BY NOSORT 1
2 INDEX FULL SCAN PK_DEPT 1
方法2：oracle提供v$sql_plan来
SQL> select > 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>
> ---- -------------------- -------------------- -------------------- ----------
0 SELECT STATEMENT 1
1 SORT GROUP BY NOSORT 1
2 INDEX FULL SCAN PK_DEPT 1
页:
[1]