数据库
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开发
本文档使用「觅思文档专业版」发布
-
+
首页
获取执行计划的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文档（打印）
分享
链接
类型
密码
更新密码
有效期