oracle 执行计划查询

通过优化器获取执行计划:评估出来的
1.EXPLAIN PLAN
简介:优化器将基于当前数据库对象统计信息生成执行计划和相关信息，而不实际执行EXPLAIN PLAN后面的语句。并且将解释后的执行计划放入全局临时表PLAN TABLE(系统的全局临时表)中。
EXPLAIN PLAN SET STATEMENT_ID ='test02' FOR select * from EMPLOYEES,DEPARTMENTS,COUNTRIES;
select * from plan_table; ###（不推荐）
select * from table(dbms_xplan.display(null,'test02',null)); ###（推荐）

示例：
set linesize 200
set pagesize 200
ALTER SYSTEM FLUSH SHARED_POOL;
EXPLAIN PLAN SET STATEMENT_ID 'test06' FOR select * from EMPLOYEES,DEPARTMENTS,COUNTRIES;
select * from plan_table;
select * from table(dbms_xplan.display(null,'test06',null));

 

2.SET AUTOTRACE
简介:同样，优化器将基于当前统计信息生成执行计划和相关信息，而不实际执行此语句，
SET AUTOTRACE的使用方法如下:
USage:SET AUTOTRACE {OFF |ON |TRACEONLY |EXPLAIN | STATISTICS}
ON和traceonly的区别在于后者不产生SQL的输出结果;
EXPLAIN和STATISTICS的区别是，前者只看执行计划，后者只看统计信息。
一般使用的语句为:set autotrace traceonly
使用完毕后:set autotrace off

示例：
set autotrace traceonly
select * from EMPLOYEES,DEPARTMENTS,COUNTRIES;
set autotrace off

在内存中查看真实执行计划:
3.1 DBMS_XPLAN DISPLAY_CURSOR DISPLAY_AWR
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包.
dbms_xplan.display -格式化和显示PLANTABLE的内容(评估的)
dbms_xplan,display_cursor -格式化和现实任何己加载游标的执行计划的内容。（真实的）
dbms_.xplan.display_awr -格式化并显示AWR中存储的SQL语句的执行计划的内容。（真实的）

sql_id:
要显示执行计划的 SQL 语句的唯一标识符（SQL ID）。
如果为 NULL，则默认显示最近执行的 SQL 语句的执行计划。
cursor_child_no:
游标的子编号（Child Number）。
如果为 0，则显示所有子游标的执行计划。
format:
控制输出格式的选项。
常用值：
'BASIC': 显示最基本的执行计划信息。
'TYPICAL': 显示典型信息（默认值）。
'ALL': 显示所有信息
'ADVANCED': 显示高级信息，包括查询块和列投影。
'ALLSTATS LAST': 显示执行统计信息（需要启用 GATHER_PLAN_STATISTICS 提示）。

示例：
ALTER SYSTEM FLUSH SHARED_POOL;
select * from EMPLOYEES,DEPARTMENTS,COUNTRIES;
select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'advanced'));
select * from table(dbms_xplan.DISPLAY_CURSOR('4v4cwr3faq1kn',null,'advanced'));

示例：
ALTER SYSTEM FLUSH SHARED_POOL;
select * from EMPLOYEES,DEPARTMENTS,COUNTRIES;
select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'advanced'));
select * from table(dbms_xplan.DISPLAY_CURSOR('4v4cwr3faq1kn',null,'advanced'));

示例：
deepseek提问dbms_xplan.DISPLAY_CURSOR用法即可

3.2 DBMS_XPLAN.DISPLAY_AWR
如果SQL语句的执行计划已经在share_pool中被刷掉了，使用这个方法去查看执行计划
简介:用于获取AWR基表WRH$_SOL_PLAN
参数:
SQL_ID:前文讲过
PLAN_HASH_VALUE:表示对应的SQL语句在v$sql_plan中匹配PLAN_HASH_VALUE
若输入NULL则显示该语句所有的执行计划。
DB ID:默认获取本地v$database中的DATABASE_ID。
FORMAT:前文讲过

示例：
ALTER SYSTEM FLUSH SHARED_POOL;
Select sql_id from v$sql;
select * from table(dbms_xplan.display_awr('SQL_ID',NULL,NULL,'ADVANCED'));
select * from table(dbms_xplan.display_awr('gs8x9dtk9bxu9',NULL,NULL,'ADVANCED'));

4.STATISTICS_LEVEL = ALL
SET STATISTICS LEVEL = ALL
执行计划是SQL语句执行前基于当前的统计信息生成的，其中，ROWS,Bytes,Cost,Time等为评估值，为了获取更为准确的实际值，即A-ROWS,A-TIME等，数据在执行SQL语句时候需要做额外的收集。
为执行计划获取准确的实际值:
会话级:alter session set STATISTICS_LEVEL= ALL;
语句级:select /*+ gatherplan statistics */

示例：
ALTER SYSTEM FLUSH SHARED_POOL;
alter session set STATISTICS_LEVEL= ALL;
select * from EMPLOYEES,DEPARTMENTS;
select * from table(dbms_xplan.display_cursor(null,NULL,'ADVANCED ALLSTATS LAST'));

 

5.事件10046 trace
1.为了排除缓存的影响，首先我们情况buffercache (缓冲区缓存)，验证代码如下:
alter system flush buffer_cache;
2.alter session set events '10046 trace name context forever,level12'; --开启追踪
3.执行sgl语句;
SOL> select * from EMPLOYEES,DEPARTMENTS;
4.alter session set events '10046 trace name context off'; --关闭追踪
5.找到跟踪后产生的文件(开启10046前先用ls -ltr| grep 10046,看一下文件，执行结束后再看哪个是多出来的文件即可)
6.tkproftrc文件 目标文件 sys=no sort=prsela,exeela,fchela--格式化命令
7.Cat 查看目标文件

示例：
ALTER SYSTEM FLUSH SHARED_POOL;
alter system flush buffer_cache;

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set events '10046 trace name context forever,level 12';

SELECT e.last_name,d.department_name, e.salary FROM employees e,departments d WHERE salary < 3000 AND e.department_id = d.department_id ORDER BY salary DESC;

select * from dual;

alter session set events '10046 trace name context off';

tkprof prod_ora_12631_10046.trc prod_ora_12631_10046_out.trc sys=no waits=yes sort=prsela,exeela,fchela
cat prod_ora_12631_10046_out.trc
可参考deepseek tkprof用法