数据库
oracle数据库自启动和关闭脚本调用
数据库灾备恢复小结
DMS使用文档
Oracle12C安装文档_李光升
Centos配置yum源的操作步骤-李光升
CCS数据库突然卡顿问题解决-徐铭
Centos 7.9静默安装oracle 12cR2详细步骤
mysql5.7数据库sql语句数据类型隐式转换的反逻辑BUG
RDS MySQL临时文件导致实例磁盘空间满且出现“锁定中”状态
关于Mysql引擎Myisam和InnoDB的使用
MySQL查询语句中in和exists区别详解-知识点
Mysql 的存储过程中 WITH AS 的语法和使用
Mysql 性能调优-详解
oracle数据库DG搭建
SUSE linux 配置时钟同步服务NTP/Chrony
oracle数据库实例开启操作文档
oracle数据库实例关闭操作文档
oracle数据库DBS备份配置文档v2
ALiYun ECS CENTOS数据上传到OSS操作手册
Oracle - LOGMINER配置方法
oracle sysaux表空间爆满清理unified auditing数据
sysaux表空间爆满WRI$_ADV_OBJECTS占用严重
SAP权限对象
SYSAUX表空间WRI$\_ADV\_OBJECTS表过大清理V2
oracle：表访问方式
ORA性能诊断调优
数据字典(DICT)和性能事件类型(EVENT)
日志挖掘LogMiner
RMAN sql_id “0az7czjdw8z7j” 执行计划异常
Oracle11g Active Data Guard搭建、管理
ORACLE LOGMINER配置v2
oracle日志组调整
ECS linux服务器挂载nas磁盘失败
oracle数据库修改字符集
datapump数据泵导入导出
OA系统数据库ADG架构搭建
IPS数据库SQL执行异常:ORA-00600
oracle 12c 创建PDB
oracle数据库监听异常
lvm:阿里云磁盘扩容，lvm扩容
Redis基本操作
SAP S4 CLIENT COPY(800->400)
日常数据库操作
PGSQL操作
# ORA-00054: resource busy问题处理
OA数据库服务器故障分析报告
CCS数据库生产数据库（北京中心）覆盖到VER环境数据库（乌兰察布）:使用nonpdb to pdb迁移方式
WINDOWS环境Django框架连接MySQL数据库配置
Django开发学习实战
什么是向量嵌入？
OA数据库服务器内次异常增长问题分析
基于AI的自动化服务器管理
AI技术发展趋势及其在农牧食品行业的创新应用
IPS数据库日志表数据归档&表分区
国产编程模型GLM-4.6海外爆火：性能媲美ClaudeSonnet，性价比优势显著
免费开源的零代码平台 / 无代码平台，敲敲云 v2.2.0 版本
数据库原理与应用
AI发展近况分析
ORACLE ADG环境下解除ADG关系并激活备库为独立数据库
麦肯锡重磅报告：关于未来的生存指南，当57%的工作被自动化，我们如何与AI结成利益共同体
【Oracle】Cursor（游标）
ORACLE数据库在曾经的备库（路径一致）进行不完全恢复
获取执行计划的6种方法
人工智能行业的发展已进入“系统竞争”时代
2026年AI与机器人发展趋势
事务未提交导致行锁等待问题处理
MongoDB日常操作命令
2026年人工智能前沿技术趋势与应用落地分析
Doris开发
人工智能在数据库运维工作中的应用趋势与落地场景研究
帆软报表开发学习
AI 助手在 Oracle DBA 工作中的辅助能力研究报告
AI原生数据库发展趋势白皮书
OpenClaw数据库管理应用实践
Apache Doris 简介
AI在实际工作中的应用案例分享
人形机器人现状
从十大AI技术趋势，窥见“未来”
AI 在辅助投资股票方面的落地方案与实际使用价值
从原理到实践，一文读懂主流 AI 技术
AI Agent 从零开始学习路线图
AI 驱动的内容生产工作流探索 —— 基于微信公众号文章编写与视频号视频生成的实践
本文档使用「觅思文档专业版」发布
-
+
首页
获取执行计划的6种方法
一、####注意 如果SQL执行很长时间才出结果或返回不了结果，那么只能使用1 跟踪SQL最简单的方法是1，其次是2 如果想观察SQL多个执行计划的情况，只能用方法4和方法6 如果SQL中含函数，函数中又套SQL等，即存在多层调用，准确分析只能用方法5 确保看到真实的执行计划，不能使用方法1和方法2 想要获取表或访问的次数，只能使用方法3 二、####执行计划 1、explain plan for 获取 SQL> set linesize 1000 SQL> set pagesize 2000 SQL> explain plan for 2 select * 3 from xum.t_sample where object_id =20; 已解释。 SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 412526866 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 132 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_SAMPLE | 1 | 132 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=20) 已选择 14 行。 SQL> 2、set autotrace on（set autotrace traceonly） SQL> set autotrace on SQL> select * 2 from xum.t_sample where object_id =20; OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- SYS OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- ICOL$ OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- 20 2 TABLE 2017-01-26 13:52:53 OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- 2017-01-26 14:57:02 2017-01-26:13:52:53 VALID N N N 1 OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- METADATA LINK Y N OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- USING_NLS_COMP OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- N N OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- 执行计划 ---------------------------------------------------------- Plan hash value: 412526866 -------------------------------------------------------------------------------- -------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- -------------------------- | 0 | SELECT STATEMENT | | 1 | 132 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_SAMPLE | 1 | 132 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- -------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=20) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 2487 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> set autotrace traceonly SQL> select * 2 from xum.t_sample where object_id =20; 执行计划 ---------------------------------------------------------- Plan hash value: 412526866 -------------------------------------------------------------------------------- -------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- -------------------------- | 0 | SELECT STATEMENT | | 1 | 132 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_SAMPLE | 1 | 132 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- -------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=20) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 2487 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> 3、statistics_level=all SQL> alter session set statistics_level=all; 会话已更改。 SQL> select * 2 from xum.t_sample where object_id =20; OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- SYS OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- ICOL$ OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- 20 2 TABLE 2017-01-26 13:52:53 OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- 2017-01-26 14:57:02 2017-01-26:13:52:53 VALID N N N 1 OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- METADATA LINK Y N OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- USING_NLS_COMP OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- N N OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 67ay6r4srj6w7, child number 1 ------------------------------------- select * from xum.t_sample where object_id =20 Plan hash value: 412526866 -------------------------------------------------------------------------------- ------------------------------------ | Id | Operation | Name | Starts | E-Ro ws | A-Rows | A-Time | Buffers | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_SAMPLE | 1 | 1 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 | 1 |00:00:00.01 | 3 | -------------------------------------------------------------------------------- ------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=20) 已选择 19 行。 SQL> 4、dbms_xplan.display_cursor 获取 select * from table(dbms_xplan.display_cursor('&sq_id'));(该方法是从共享池获得) select * from table(dbms_xplan.display_awr('&sq_id'));(该方法从AWR性能视图获取) select * from table(dbms_xplan.display_cursor('&sq_id',0));（有多个执行计划，获取第一个执行计划） select * from table(dbms_xplan.display_cursor('&sq_id',1));（有多个执行计划，获取第二个执行计划） 5、event 10046 trace 跟踪 SQL> alter session set events '10046 trace name context forever,level 12'; 会话已更改。 SQL> select * 2 from xum.t_sample where object_id =20; OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- SYS OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- ICOL$ OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- 20 2 TABLE 2017-01-26 13:52:53 OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- 2017-01-26 14:57:02 2017-01-26:13:52:53 VALID N N N 1 OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- METADATA LINK Y N OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- USING_NLS_COMP OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- N N OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME -------------------------------------------------------------------------------- OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED ---------- -------------- ----------------------- ------------------- LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE ------------------- ------------------- ------- - - - ---------- EDITION_NAME -------------------------------------------------------------------------------- SHARING E O A ------------------ - - - DEFAULT_COLLATION -------------------------------------------------------------------------------- D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID - - ------------- ------------- -------------- -------------- SQL> alter session set events '10046 trace name context off'; 会话已更改。 SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File'; VALUE -------------------------------------------------------------------------------- /tqls_system/app/oracle/diag/rdbms/ccs/ccs/trace/ccs_ora_8162.trc SQL> [root@ccs-db-ver ~]# more /tqls_system/app/oracle/diag/rdbms/ccs/ccs/trace/ccs_ora_8162.trc Trace file /tqls_system/app/oracle/diag/rdbms/ccs/ccs/trace/ccs_ora_8162.trc Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125 ORACLE_HOME: /tqls_system/app/oracle/12c System name: Linux Node name: ccs-db-ver Release: 3.10.0-862.14.4.el7.x86_64 Version: #1 SMP Wed Sep 26 15:12:11 UTC 2018 Machine: x86_64 Instance name: ccs Redo thread mounted by this instance: 1 Oracle process number: 185 Unix process pid: 8162, image: oracle@ccs-db-ver (TNS V1-V3) *** 2025-02-13T14:20:41.867417+08:00 (CCSUAT(4)) *** SESSION ID:(198.37875) 2025-02-13T14:20:41.867469+08:00 *** CLIENT ID:() 2025-02-13T14:20:41.867474+08:00 *** SERVICE NAME:(ccsuat) 2025-02-13T14:20:41.867479+08:00 *** MODULE NAME:(sqlplus@ccs-db-ver (TNS V1-V3)) 2025-02-13T14:20:41.867483+08:00 *** ACTION NAME:() 2025-02-13T14:20:41.867487+08:00 *** CLIENT DRIVER:(SQL*PLUS) 2025-02-13T14:20:41.867491+08:00 *** CONTAINER ID:(4) 2025-02-13T14:20:41.867496+08:00 WAIT #140532267234808: nam='Disk file operations I/O' ela= 27 FileOperation=8 fileno=0 filetyp e=8 obj#=-1 tim=17122011206081 WAIT #140532267234808: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3 =0 obj#=-1 tim=17122011206435 *** 2025-02-13T14:20:49.479591+08:00 (CCSUAT(4)) WAIT #140532267234808: nam='SQL*Net message from client' ela= 7611976 driver id=1650815232 #by tes=1 p3=0 obj#=-1 tim=17122018818428 CLOSE #140532267234808:c=0,e=5,dep=0,type=1,tim=17122018818512 ===================== PARSING IN CURSOR #140532267229168 len=46 dep=0 uid=0 oct=3 lid=0 tim=17122018821336 hv=829987 719 ad='153a89ba8' sqlid='67ay6r4srj6w7' select * from xum.t_sample where object_id =20 END OF STMT PARSE #140532267229168:c=2594,e=2793,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=412526866,tim=1712 2018821334 EXEC #140532267229168:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=412526866,tim=1712201882 1422 WAIT #140532267229168: nam='Disk file operations I/O' ela= 19 FileOperation=8 fileno=0 filetyp e=8 obj#=-1 tim=17122018821459 WAIT #140532267229168: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3 =0 obj#=-1 tim=17122018821487 WAIT #140532267229168: nam='asynch descriptor resize' ela= 6 outstanding #aio=0 current aio li mit=0 new aio limit=128 obj#=100848 tim=17122018821639 WAIT #140532267229168: nam='Disk file operations I/O' ela= 68 FileOperation=2 fileno=153 filet ype=2 obj#=100848 tim=17122018821699 WAIT #140532267229168: nam='db file sequential read' ela= 20 file#=153 block#=67577 blocks=1 o bj#=100848 tim=17122018821739 WAIT #140532267229168: nam='db file sequential read' ela= 6 file#=153 block#=67578 blocks=1 ob j#=100848 tim=17122018821845 WAIT #140532267229168: nam='db file sequential read' ela= 6 file#=153 block#=67321 blocks=1 ob j#=100847 tim=17122018821932 FETCH #140532267229168:c=1001,e=558,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=412526866,tim=17122 018822065 WAIT #140532267229168: nam='SQL*Net message from client' ela= 252 driver id=1650815232 #bytes= 1 p3=0 obj#=100847 tim=17122018822348 FETCH #140532267229168:c=0,e=6,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=412526866,tim=1712201882 2374 STAT #140532267229168 id=1 cnt=1 pid=0 pos=1 obj=100847 op='TABLE ACCESS BY INDEX ROWID BATCHE D T_SAMPLE (cr=4 pr=3 pw=0 str=1 time=555 us cost=2 size=132 card=1)' STAT #140532267229168 id=2 cnt=1 pid=1 pos=1 obj=100848 op='INDEX RANGE SCAN IDX_T_SAMPLE_OBJI D (cr=3 pr=2 pw=0 str=1 time=362 us cost=1 size=0 card=1)' WAIT #140532267229168: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3 =0 obj#=100847 tim=17122018822474 *** 2025-02-13T14:21:32.790715+08:00 (CCSUAT(4)) WAIT #140532267229168: nam='SQL*Net message from client' ela= 43307066 driver id=1650815232 #b ytes=1 p3=0 obj#=100847 tim=17122062129548 CLOSE #140532267229168:c=0,e=8,dep=0,type=0,tim=17122062129651 ===================== PARSING IN CURSOR #140532267229168 len=55 dep=0 uid=0 oct=42 lid=0 tim=17122062129851 hv=22179 40283 ad='0' sqlid='06nvwn223659v' alter session set events '10046 trace name context off' END OF STMT PARSE #140532267229168:c=0,e=157,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=17122062129850 WAIT #140532267229168: nam='PGA memory operation' ela= 10 p1=65536 p2=1 p3=0 obj#=100847 tim=1 7122062130060 EXEC #140532267229168:c=475,e=310,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=17122062130212 [root@ccs-db-ver ~]# [oracle@ccs-db-ver ~]$ tkprof /tqls_system/app/oracle/diag/rdbms/ccs/ccs/trace/ccs_ora_8162.trc /home/oracle/1004601.txt TKPROF: Release 12.2.0.1.0 - Development on 星期一 2月 17 14:50:55 2025 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. [oracle@ccs-db-ver ~]$ sz /home/oracle/1004601.txt *B00000000000000 [oracle@ccs-db-ver ~]$ 6、awrsqrpt.sql @?/rdbms/admin/awrsqrpt.sql
徐铭
2026年1月27日 13:45
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
Word文件
PDF文档
PDF文档（打印）
分享
链接
类型
密码
更新密码
有效期