Oracle 10046

最新推荐文章于 2025-06-21 17:13:43 发布

转载最新推荐文章于 2025-06-21 17:13:43 发布 · 104 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/wwjd365/p/9300786.html

文章标签：

#数据库

本文介绍如何使用10046跟踪SQL来收集详细的执行计划和统计信息，以便进行SQL性能调优。文章包括设置跟踪、运行SQL、关闭跟踪及使用tkprof工具分析trace文件的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

【10046 SQL】
conn username/password
alter session set tracefile_identifier = 'id_10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set "_rowsource_execution_statistics" = TRUE;
alter session set events '10046 trace name context forever, level 12';
<Run your SQL here;>
rollback;
alter session set events '10046 trace name context off';

【10046 sql_id】
alter session set tracefile_identifier = 'id_10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set "_rowsource_execution_statistics" = TRUE;
alter system set events 'sql_trace [sql:21jthqydpgzy9] level 12';
alter system set events 'sql_trace [sql:21jthqydpgzy9] off';

--查询trc文件路径
select value from v$diag_info where name='Default Trace File';

--格式化trace
$tkprof DB2_ora_45941130_id_10046.trc DB2_ora_45941130_id_10046.log
TKPROF: Release 11.2.0.4.0 - Development on 星期四 7月 12 17:13:36 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

$ls -ltr *log
-rw-r----- 1 oracle oinstall 46076 Jul 12 17:13 DB2_ora_45941130_id_10046.log

转载于:https://www.cnblogs.com/wwjd365/p/9300786.html