使用dbms_xplan.display_cursor查看执行计划的问题

星空下wjr · 发表于 2012-12-18 17:00

SQL> set serveroutput off;
SQL> select /*+ gather_plan_statistics */ * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2550.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5100.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1400.00 10
14 rows selected
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
8 rows selected

复制代码

貌似 set serveroutput off 没执行，这个该怎么解决？

bell6248 · 发表于 2012-12-18 17:27

加explain plan for .....

bell6248 · 发表于 2012-12-18 17:29

先执行explain plan for select * from emp，后在运行你那条语句即可

星空下wjr · 发表于 2012-12-18 17:59

bell6248 发表于 2012-12-18 17:29
先执行explain plan for select * from emp，后在运行你那条语句即可

explain plan for 查看解释执行计划，这里是想看实际的执行计划啊，explain plan for 没问题的，就是用
dbms_xplan.display_cursor()时关于serveroutput 的问题~

iori809 · 发表于 2012-12-18 18:16

星空下wjr 发表于 2012-12-18 17:59
explain plan for 查看解释执行计划，这里是想看实际的执行计划啊，explain plan for 没问题的，就是用
...

  你的环境是？

SQL> select /*+ gather_plan_statistics */ * from emp;
   EMPNO ENAME    JOB             MGR HIREDATE       SAL    COMM
---------- ---------- --------- ---------- --------------- ----- ----------
DEPTNO
------
   7369 SMITH    CLERK          7902 17-DEC-80       222
20
   7499 ALLEN    SALESMAN       7698 20-FEB-81       222       300
30
   7521 WARD    SALESMAN       7698 22-FEB-81       1250       500
30

   EMPNO ENAME    JOB             MGR HIREDATE       SAL    COMM
---------- ---------- --------- ---------- --------------- ----- ----------
DEPTNO
------
   7566 JONES    MANAGER       7839 02-APR-81       2975
20
   7654 MARTIN    SALESMAN       7698 28-SEP-81       1250    1400
30
   7698 BLAKE    MANAGER       7839 01-MAY-81       2850
30

   EMPNO ENAME    JOB             MGR HIREDATE       SAL    COMM
---------- ---------- --------- ---------- --------------- ----- ----------
DEPTNO
------
   7782 CLARK    MANAGER       7839 09-JUN-81       2450
10
   7788 SCOTT    ANALYST       7566 19-APR-87       3000
20
   7839 KING    PRESIDENT          17-NOV-81       5000
10

   EMPNO ENAME    JOB             MGR HIREDATE       SAL    COMM
---------- ---------- --------- ---------- --------------- ----- ----------
DEPTNO
------
   7844 TURNER    SALESMAN       7698 08-SEP-81       1500       0
30
   7876 ADAMS    CLERK          7788 23-MAY-87       1100
20
   7900 JAMES    CLERK          7698 03-DEC-81       950
30

   EMPNO ENAME    JOB             MGR HIREDATE       SAL    COMM
---------- ---------- --------- ---------- --------------- ----- ----------
DEPTNO
------
   7902 FORD    ANALYST       7566 03-DEC-81       3000
20
   7934 MILLER    CLERK          7782 23-JAN-82       1260
10

14 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fjhbyrfs7gygq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------------
----
| Id  | Operation       | Name | Starts | E-Rows | A-Rows | A-Time | Buffe
rs |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
| 1 |  TABLE ACCESS FULL| EMP  |    1 |    14 |    14 |00:00:00.01 |
8 |
--------------------------------------------------------------------------------
----

星空下wjr · 发表于 2012-12-18 18:19

iori809 发表于 2012-12-18 18:16
你的环境是？

SQL> select /*+ gather_plan_statistics */ * from emp;

oracle是10.2.0，在pl/sql dev上跑的

horizon · 发表于 2012-12-19 09:23

把9m7787camwh4m作为dbms_xplan.display_cursor第一个参数去运行看看

星空下wjr · 发表于 2012-12-19 09:49

horizon 发表于 2012-12-19 09:23
把9m7787camwh4m作为dbms_xplan.display_cursor第一个参数去运行看看

SQL> select * from table(dbms_xplan.display_cursor('9m7787camwh4m', 0, 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9m7787camwh4m, child number 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
8 rows selected

复制代码

还是一样啊、、、

星空下wjr · 发表于 2012-12-19 10:00

还是不行、、不过可以先标识sql，然后再v$sql里面去取sql_id和child_number,然后再用dbms_xplan.display_cursor()就ok了~
不知道啥原因。。。

SQL> select /* test *//*+ gather_plan_statistics */ * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2550.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5100.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1400.00 10
14 rows selected
SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%test%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
0200bsvkhc917 0 select wrm.last_ash_sample_id from WRM$_DATABASE_INSTANCE wrm, (select
5ms1dhxbadq64 0 update wrm$_snapshot set status = 0, flush_elapsed = greatest((cast
2hqz4n3062jhq 0 select /* test *//*+ gather_plan_statistics */ * from emp
cptm0vuy03d5g 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%test%'
SQL> select * from table(dbms_xplan.display_cursor('2hqz4n3062jhq', 0, 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2hqz4n3062jhq, child number 0
-------------------------------------
select /* test *//*+ gather_plan_statistics */ * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffe
--------------------------------------------------------------------------------
| 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 |
--------------------------------------------------------------------------------
12 rows selected

复制代码

dan0210 · 发表于 2015-7-5 22:01

我的也是这样，不过重启一下机器就可以了，暂时不知道是啥原因