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

来源：这里教程网时间：2026-03-03 21:22:18 作者：

[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 --//产生新的子光标，执行计划的信息不全。