如果游标没有关闭，那么通过TKPROF生成的输出将不会包含精确的执行计划，在原始的trace文件中要么没有STAT行，或则行数量遗失。当然可以通过EXPLAIN选项来产生执行计划，但这个时候产生的执行计划不一定是正确的。如果游标关闭，STAT行写入原始的trace文件。
解决方法：
可以通过执行其他无关的SQL来关闭游标或通过dbms_session.reset_package过程来完成。
验证方法：
对比两种不同方式，检查在游标关闭和非关闭两种情况下，是否生成了对应的执行计划。
验证过程：
1. 创建一个过程，在过程中打开一个游标，执行某些操作，然后关闭游标。
创建表：
SQL> desc t_plan;
Name Null? Type
----------------------------------------- -------- --------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
创建过程：
CREATE OR REPLACE PROCEDURE scott.tkprof_test
AS
counts NUMBER;
CURSOR c1
IS
SELECT COUNT (*)
FROM t_plan
GROUP BY owner;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO counts;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.put_line ('counts:' counts);
END LOOP;
CLOSE c1;
END;
2. 登录SQL*PLUS，打开一个session，设置SQL_TRACE为true，执行步骤1中创建的过程，不执行reset_package过程，设置SQL_TRACE为false。
SQL> alter session set sql_trace=true;
Session altered.
SQL> exec tkprof_test;
counts:99
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=false;
Session altered.
3. 通过TKPROF命令，检查步骤2输出文件。
********************************************************************************
SELECT COUNT (*)
FROM t_plan
GROUP BY owner
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.11 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.11 0 4 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************
4. 登录SQL*PLUS，打开一个session，设置SQL_TRACE为true，执行步骤1中创建的过程，执行reset_package过程，设置SQL_TRACE为false。
SQL> alter session set sql_trace=true;
Session altered.
SQL> exec tkprof_test;
counts:99
PL/SQL procedure successfully completed.
SQL> exec dbms_session.reset_package;
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=false;
Session altered.
5. 通过TKPROF命令，检查步骤4输出文件。
********************************************************************************
SELECT COUNT (*)
FROM t_plan
GROUP BY owner
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.03 0 4 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT GROUP BY
99 TABLE ACCESS FULL T_PLAN
********************************************************************************
注意：
1. 执行reset_package必须在alter session set sql_tracle = false命令之前执行，否则同样不能得到执行计划。
2. 对于单个在SQL*PLUS中执行的语句，执行完成后，可以通过“Select * from dual”或者“alter session set sql_tracle = ture”，游标会立刻关闭。
3. 今天读到Tom的《Oracle Expert one by one》10章的时候，提到通过原始的trace文件判断是否已经生成可信任的执行计划。在原始的trace文件中，STAT行纪录了运行时精确的执行计划，同时也会包含每一步执行计划中关联的正确的记录行数。也提到了只有当相关游标关闭之后，才会产生相关纪录。回过头看一下上面关闭游标的例子，其生成的trace文件相关部分内容为：
=====================
PARSING IN CURSOR #2 len=61 dep=1 uid=59 oct=3 lid=59 tim=19288630504 hv=2097396184 ad='66c8c0a4'
SELECT COUNT (*)
FROM t_plan
GROUP BY owner
END OF STMT
PARSE #2:c=0,e=38470,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=19288630498
EXEC #2:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19288660693
FETCH #2:c=15625,e=170,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=19288665392
FETCH #2:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19288670162
EXEC #1:c=15625,e=83199,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=4,tim=19288675090
=====================
PARSING IN CURSOR #3 len=52 dep=0 uid=59 oct=47 lid=59 tim=19288679793 hv=1697159799 ad='66c61e20'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #3:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19288679789
EXEC #3:c=0,e=209,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=19288702763
*** 2007-06-13 14:35:20.000
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=59 oct=47 lid=59 tim=19313599313 hv=1443640743 ad='66b81f64'
BEGIN dbms_session.reset_package; END;
END OF STMT
PARSE #1:c=0,e=342,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19313599304
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT GROUP BY '
STAT #2 id=2 cnt=99 pid=1 pos=1 obj=30711 op='TABLE ACCESS FULL T_PLAN '
EXEC #1:c=0,e=21520,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=19313684039
=====================
已经将相关部分highlight，可以看到当查询的SELECT COUNT (*) FROM t_plan GROUP BY owner; 执行完成之后，并没有生成STAT纪录。直到调用dbms_session.reset_package方法之后，生成了STAT相关纪录。从STAT的记录行，可以看到操作以及对应的行数。
没有评论:
发表评论