最近在看《基于oracle的sql优化》这本书，把经常能用到的记下来，以备复习使用和加深记忆。
1.explain plan 命令
2.DBMS_XPLAN包
3.SQLPLUS中的AUTOTRACE开关
4.10046事件
5.10053事件
6.AWR报告或者staccpack报告
7.其他的脚本。
一.explanation plan命令
1.plsql中的快捷键其实就是这个命令的封装
2.基本语法：
explain plan for +sql
select * from table(dbms_xplan.display)
示例：
SQL> explain plan for select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 770 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 770 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected.
SQL>
二.DBMS_XPLAN包
select * from table(dbms_xplan.display); ---用于得到explain 配合得到执行计划。
select * from table(dbms_xplan.display_curosor(null,null,'advanced')); --用于得到刚刚执行过得sql的执行计划。
select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced')); ----用于查看指定sql的执行计划。
select * from table(dbms_xplan.display_awr('sql_id')); --查看目标sql的历史执行计划。
三.AUTOTRACE开关
autotrace开关不仅能够得到目标sql的执行计划，还可以看到执行过程中的资源消耗量。
SET AUTOTRACE {OFF|ON|TRACEONLY}
[EXPLAIN] ----执行计划
[STATISTICS] ---消耗的资源
on和traceonly的区别在于执行过程中的资源消耗量能不能看到。
示例：
SQL> set autotrace on
SQL> select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
EMPNO ENAME DNAME
---------- ---------- --------------
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 770 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 770 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
1006 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
四.10046事件与tkprof命令
4.1.10046事件所得到的的执行计划中明确表示了目标sql实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。
4.2.使用步骤：
首先在当前session中激活10046事件。
接着在此session中执行目标sql。
最后在此session中关闭10046事件。
4.3.位置：最后执行计划会生成一个trace文件。 ---位置 USER_DUMP_DEST ---- instancename_ora_spid.trc
4.4.激活10046事件的方法：
alter session set event '10046 trace name context forever,level 12'
oradebug event 10046 trace name context forever,level 12 ------推荐这种方法，因为可以通过 oradebug tracefile_name 得到当前session对应的trace文件的具体路径和名称。
4.5.关闭10046事件
alter session set event '10046 trace name context off
oradebug event 10046 trace name context off
4.6.生成的trc文件的查看
生成的文件不直观，用命令tkprof“翻译”一下。
示例：
SQL> oradebug setmypid
Statement processed.
SQL> select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
EMPNO ENAME DNAME
---------- ---------- --------------
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
14 rows selected.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/single/single/trace/single_ora_15021.trc
tkprof /u01/app/oracle/diag/rdbms/single/single/trace/single_ora_15021.trc /home/oracle/sql3.trc
五.如何得到真实的执行计划
5.1.除了使用autotrace开关得到的执行计划，所有sql真正被执行过从而得到的执行计划是准确的执行计划，如果没有被执行，则不一定是准确的。
5.2.autotrace开关得到的执行计划都不是真实的执行计划是因为autotrace得到的执行计划都源自于explain plan命令。
5.3.在这本书里，作者提供了自己的编写的存储过程用于得到真实的执行计划：(当然，前提条件时执行计划还在shared pool中)
printsql 使用方法
exec printsql(spid号,'spid');
测试：
select p.spid,s.sid from v$session s,v$process p where s.paddr=p.addr and s.sid in (select distinct sid from v$mystat);
SPID SID
------------------------ ----------
15438 11
select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;
SQL> set serveroutput on
SQL> set pagesize 10000
SQL> exec printsql(15493,'SPID');
--------------------------------------------------------------------------------
------
SELECT DECODE(SQL_HASH_VALUE,0,PREV_HASH_VALUE,SQL_HASH_VALUE) F
ROM V$SESSION WHERE SID=:B1
--------------------------------------------------------------------------------
------
The session id is 72
The status is ACTIVE
The sql hash value is 4149582315
The prev hash value is 2327521083
The osuser is oracle
The machine is single
The terminal is pts/7
The program is sqlplus@single (TNS V1-V3)
The event is SQL*Net message from client
--------------------------------------------------------------------------------
------
alter system kill session '72,689' immediate;
--------------------------------------------------------------------------------
------
The hash_value is 1831028534
The child_number is 0
The plan_hash_value is 1627146547
The execution is 3
The buffer_gets is 0
The gets_per_exec is 0
The rows_processed is 3
The rows_per_exec is 1
The disk_reads is 0
The reads_per_exec is 0
The cpu_time is .001
The cpu_per_exec is .000333333333333333333333333333333333333333
The ELAPSED_TIME is .001779
The ela_per_exec is .000593
--------------------------------------------------------------------------------
------
HASH_VALUE: 1831028534 CHILD_NUMBER: 0
--------------------------------------------------------------------------------
-------------------------------------------------------------
SELECT DECODE(SQL_HASH_VALUE,0,PREV_HASH_VALUE,SQL_HASH_VALUE) FROM V$SESSION
WHERE SID=:B1
Plan hash value: 1627146547
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | E-Rows | OMem | 1Mem |
Used-Mem |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | | | |
|
| 1 | MERGE JOIN CARTESIAN | | 1 | | |
|
| 2 | NESTED LOOPS | | 1 | | |
|
| * 3 | FIXED TABLE FIXED INDEX | X$KSLWT (ind:1) | 1 | | |
|
| * 4 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | | |
|
| 5 | BUFFER SORT | | 1 | 2048 | 2048 |
2048 (0) |
| * 6 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 | | |
|
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("W"."KSLWTSID"=:B1)
4 - filter("W"."KSLWTEVT"="E"."INDX")
6 - filter(("S"."INDX"=:B1 AND "S"."INST_ID"=USERENV('INSTANCE') AND
BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* parameter 'statistics_level' is set to 'ALL', at session or system level
PL/SQL procedure successfully completed.
感觉还是挺好用的，有兴趣的可以去这本书提供的地址http://www.dbsnake.net/books下载。
1226
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
