pl/sql 性能分析

SET TIMING ON SERVEROUTPUT ON
DECLARE
   v_count NUMBER(10);
BEGIN
   SELECT COUNT(rowid)
   INTO v_count
   FROM person
   WHERE gender = 'F'
   AND activity_id = '11'
   AND hair_color = 'BLONDE'
   AND age = 34
   AND eye_color = 'BROWN';
   DBMS_OUTPUT.PUT_LINE(v_count);
END;

person 有4万8千条...

这条SQL 耗费了 将近10秒 因为做了全表扫描 因为条件中没有任何加索引的列。。

那么如何分析呢

ALTER SESSION SET SQL_TRACE = TRUE;
运行之前把SQL_TRACE 打开

运行之后把SQL_TRACE 关掉

select value from v$parameter where name='user_dump_dest';

或者  alter system set user_dump_dest='c:\temp';

可得到跟踪文件的位置。。

E:\ORACLE\PRODUCT\10.1.0\ADMIN\ORCL\UDUMP


然后进入命令行看下这个目录

2008-04-23  16:48             6,770 orcl_ora_1136.trc
2008-04-22  17:56             2,217 orcl_ora_1472.trc
2008-04-23  08:58             2,941 orcl_ora_1716.trc
2008-04-23  16:10             7,608 orcl_ora_2364.trc
2008-04-24  09:02             2,941 orcl_ora_2464.trc
2008-04-23  08:58             2,590 orcl_ora_2512.trc
2008-04-22  17:56             3,836 orcl_ora_2540.trc
2008-04-22  17:59             1,237 orcl_ora_3096.trc
2008-04-24  09:03             2,525 orcl_ora_3148.trc
2008-04-24  10:18           179,058 orcl_ora_3648.trc
2008-04-22  17:58            47,879 orcl_ora_3668.trc
2008-04-22  17:55             2,912 orcl_ora_4312.trc
2008-04-22  17:59             2,912 orcl_ora_4648.trc
2008-04-22  17:59               880 orcl_ora_4840.trc
2008-04-22  17:57               942 orcl_ora_5020.trc
2008-04-22  18:27           141,451 orcl_ora_6004.trc

找到刚才生成的文件。。orcl_ora_1136.trc


然后在命令行运用命令 tkprof

我这里用。。

E:\oracle\product\10.1.0\admin\orcl\udump>tkprof orcl_ora_1136.trc c:/sql_trace.
txt explain=plsql/oracle sort=exeela,prsela,fchela

语法可以参加命令帮助。。

得到文件sql_trace.txt 可以看下这个文件。。

SELECT COUNT(ROWID)
FROM
PERSON WHERE GENDER = 'F' AND ACTIVITY_ID = '11' AND HAIR_COLOR = 'BLONDE'
  AND AGE = 34 AND EYE_COLOR = 'BROWN'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.98      10.36      71288      72202          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.00      10.37      71288      72203          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=72202 pr=71288 pw=0 time=10360093 us)
  36371   TABLE ACCESS FULL PERSON (cr=72202 pr=71288 pw=0 time=8037861 us)

********************************************************************************

CPU占用了不到2秒 执行时间10秒以上。。

结论是这种语句造成了多种进程查询。。

解决办法 如果条件的基数不大 加索引 或者加位图索引。。

DROP INDEX gender_idx;
CREATE BITMAP INDEX person_idx
ON person(gender,
          activity_id,
          hair_color,
          age,
          eye_color);

这次查出来就快乐。。