1、EXPLAIN PLAN:目的是给出一个特定SQL的查询计划。Oracle查询计划可以在逻辑上分析SQL语句的优劣。
安装:
1、sqlplus " / as sysdba"登录
2、@$ORACLE_HOME/rdbms/admin/utlxplan.sql
3、create public synonym plan_table for plan_table;
4、grant select ,insert,update ,delete on plan_table to public
注:这里没有考虑安全性,plan_table对所有人都开发了增删改查权限,如果需要有安全性,可以针对某一个USER创建一个plan_table;
使用:
@$ORACLE_HOME/rdbms/admin/utlxpls.sql
注:还有一个utlxplp.sql用于显示并行查询计划的内容;
分析报告说明:
以oracle的scott用户登录(这个用户是被锁定的,使用前使用alter user scott account unlock;解锁)。
SQL> explain plan for SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno AND empno = 7782;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 117 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 409 | 12270 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
注意看Operation的“缩进”。这个语句被解析成一棵“树”。这课树大约是
1
/ \
2 4
/ \
3 5
上面的数字,和PLAN_TABLE_OUTPUT中的“Id”是对应的。简单来说,就是这个语句有2,4构成;2又由3构成;4由5构成。
详细的SQL查询计划可以参阅其他书籍,作为初学者可以仅仅关注一下报告中的“TABLE ACCESS FULL”。如果你有where子句还有这个TABLE ACCESS FULL出现,表明你缺少索引(这仅仅针对初学者,但是现实生活中却也能解决60-70%的问题)。
2、AUTOTRACE:目的是为了给出执行SQL时,实际进行了多少工作。
autotrace的特点在于简单。
安装:
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to public;
SQL> grant select_catalog_role to scott; (Oracle 的sqldeveloper需要执行此语句)
使用:
SQL> set autotrace on
SQL> 运行你的SQL.
能够看到如下内容。
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
801 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
统计(Statistics)内容说明:
recursive calls:本SQL语句所嵌套调用的SQL的数目。
db block gets:逻辑读;从高速缓存中读取的总块数。
consistent gets:逻辑读;一致性读。
physical reads:物理读;从数据文件中的读数据、或者从temp中的读数据。
redo size:重做数据的大小。
bytes sent via SQL*Net to client:
bytes received via SQL*Net from client:
SQL*Net roundtrips to/from client:
sorts (memory):内存中的排序
sorts (disk):交换到临时表空间中的排序
rows processed:受影响的行。
这些内容十分丰富,不是三言两语能够说明清楚的,有兴趣可以参见后面列出的参考书目。
3、TKPROF:将底层的跟踪文件转换成易懂的格式。
这东西无须安装,关键是如何启用它。
启用:
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';
然后再执行你需要执行的SQL
获取跟踪文件名
select rtrim(c.value,'/')||'/'||d.instance_name||'_ora_'||ltrim(to_char(a.spid))||'.trc'
from v$process a, v$session b, v$parameter c, v$instance d
where a.addr=b.paddr
and b.audsid=sys_context('userenv','sessionid')
and c.name='user_dump_dest'
生成tkprof报告
tkprof /usr/...........................xxxx.trc tkprof.prf
查看分析报告
首先,“查找”找到你执行那个SQL。后面列出大致如下内容。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.32 3.05 187 44994 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.40 3.14 187 44994 0 1
以及下面的内容,这是等待事件,非常有用。
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 187 0.06 0.56
SQL*Net message from client 2 0.00 0.00
********************************************************************************