对SQL语句进行分析和优化

q989

　　安装和查看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
　　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
　　------------------------------------------------------------------------------------------

　　|>　　------------------------------------------------------------------------------------------
　　|   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
　　2  select 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