[20250118]设置10046事件与statistics_level=all.txt

--//一般调试优化sql语句，我以前喜欢在sql语句加入GATHER_PLAN_STATISTICS提示，这样可以获得准备的执行计划相关信息。
--//缺点就是要修改sql语句。后来采用设置会话statistics_level=all，这样操作简单。

--//最近发现设置10046事件获得的执行计划类似设置statistics_level=all，通过例子说明。

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
SCOTT@book01p> @ 46on 1
Session altered.

SCOTT@book01p> select * from emp where empno =7788 ;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 1987-07-13 00:00:00       3000                    20

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4tk8q5a2qav10, child number 0
-------------------------------------
select * from emp where empno =7788
Plan hash value: 2949544139
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |    39 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "EMP"@"SEL$1"
   2 - SEL$1 / "EMP"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)
25 rows selected.
--//只要设置就可以获得执行计划的相关信息,当然还是不建议这样使用。
--//如果不设置情况如下：

SCOTT@book01p> select * from emp where empno =7788 ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 1987-07-13 00:00:00       3000                    20

SCOTT@book01p> @ dpc '' '' ''

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4tk8q5a2qav10, child number 1
-------------------------------------
select * from emp where empno =7788
Plan hash value: 2949544139
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |    39 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "EMP"@"SEL$1"
   2 - SEL$1 / "EMP"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
--//产生新的子光标，执行计划的信息不全。