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); 这次查出来就快乐。。
