莫在浮沙筑高台

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';

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

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);

这次查出来就快乐。。