执行计划中数量为0?[@more@]从Metalink中找到答案
如果游标没有关闭，那么通过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的记录行，可以看到操作以及对应的行数。

ITPub博客

执行计划中Row 数量为0

最新文章