Oracle TKPROF & SQL_TRACE（=10046 level 1）

最新推荐文章于 2021-04-16 05:20:29 发布
最新推荐文章于 2021-04-16 05:20:29 发布
阅读量896 收藏
点赞数
分类专栏： oracle-dump-跟踪-体系基本概念 oracle常用实验或管理脚本 oracle转载博文未看
Oracle TKPROF & SQL_TRACE
Version 11.1
 
General Information
Note: Use "dbms_monitor.session_trace_enable()" instead of "alter session set events ..." and you will see the columns sys.gv_$session updated

TKPROF stands for TransientKernel Profiler

Event Numbering Range10000 - 10999

Event Definitions
Event #LevelDescription
8186-

ALTER SESSION SET EVENTS '8186 trace name context OFF' - VERSIONS BETWEEN query

100131Monitor Transaction Recovery
100151Dump Undo Segment Headers
100153Identify corrupt undo segments
100324Dump Sort Statistics
10031 Sort Debug Events
1003210Dump Sort Statistics
100334Dump Sort Large Run Statistics (blocks dumped to disk and read back)- poss. no levels.
1003310Dump Sort Intermediate Run Statistics
100451Trace Free List Management Operations
100460No statistics generated
100461Standard trace output including parsing, executes and fetches plus more
100462Same as Level 1
100464Level 1 + Bind Variables
100468Level 1 + Waits
1004612Level 1 + Bind Variables & Waits
1004616Added in 11g to generate STAT line dumps for each execution. STAT dumping has been amended in 11g so that they are not aggregated across all executions but are dumped after execution.
100531Dump Optimizer Statistics and Computations
100532Dump Optimizer Computations only
100601Dump Predicates
100651Restrict Library Cache Output for State Object Dumps - Library Objects Only
100652Restrict Library Cache Output for State Object Dumps + library object lock details
100653Restrict Library Cache Output for State Object Dumps + library object handle and library object
100792Dump SQL*Net Statistics
100811Dump High Water Mark Changes
1010410Dump Hash Join Statistics
101281Dump Partition Pruning Information
101282Dump partition iterators
101284Dump optimizer decisions about partition-wise joins
101288Dump ROWID range scan pruning information
1013212Dumps SQL statements into UDUMP along with the actual execution plan
102001Dump Consistent Reads
102011Dump Consistent Read Undo Application
1021010Table Integrity Check
1021110Index Integrity Check
102201Dump Changes to Undo Header
102217Dump Undo Changes
102241Dump Index Block Splits / Deletes
102251Dump Changes to Dictionary Managed Extents
1023110Causes full table scans to skip corrupt blocks, rather than fail with an ORA-1578 error
1023210Causes corrupt blocks to be dumped to the process trace file
1023310Traces memory usage statistics
102351Check memory manager internal structures (use with ORA-600/ORA-7445 errors)
102411Dump Remote SQL Execution
102461Trace PMON Process
1024810Trace Dispatcher Processes
1024910Trace Shared Server (MTS) Processes
1027010Debug Shared Cursors
102891Dump a database block in hexadecimal
102991Debug Prefetch
103571Debug Direct Path
103591Stops writes to the control file during NOLOGGING operations: May substantially improve performance
103900x0001Dump Parallel Execution Slave Statistics -  Slave-side execution messages
103900x0002Dump Parallel Execution Slave Statistics -  Coordinator-side execution messages
103900x0004Dump Parallel Execution Slave Statistics -  Slave context state changes
103900x0008Dump Parallel Execution Slave Statistics -  Slave ROWID range bind variables and xty
103900x0010Dump Parallel Execution Slave Statistics -  Slave fetched rows as enqueued to TQ
103900x0020Dump Parallel Execution Slave Statistics -  Coordinator wait reply handling
103900x0040Dump Parallel Execution Slave Statistics -  Coordinator wait message buffering
103900x0080Dump Parallel Execution Slave Statistics -  Slave dump timing
103900x0100Dump Parallel Execution Slave Statistics -  Coordinator dump timing
103900x0200Dump Parallel Execution Slave Statistics -  Slave dump allocation file number
103900x0400Dump Parallel Execution Slave Statistics -  Terse format for debug dumps
103900x0800Dump Parallel Execution Slave Statistics -  Trace CRI random sampling
103900x1000Dump Parallel Execution Slave Statistics -  Trace signals
103900x2000Dump Parallel Execution Slave Statistics -  Trace parallel execution granule operations
103900x4000Dump Parallel Execution Slave Statistics -  Force compilation by slave 0
103910x0001Dump Parallel Execution Granule Allocation - Summary of each object scanned in parallel
103910x0002Dump Parallel Execution Granule Allocation - Full dump of each object except extent map
103910x0004Dump Parallel Execution Granule Allocation - Full dump of each object including extent map
103910x0010Dump Parallel Execution Granule Allocation - Summary of each granule generators
103910x0020Dump Parallel Execution Granule Allocation - Granule generators except granule instances
103910x0040Dump Parallel Execution Granule Allocation - Granule generators including granule instances
103910x0080Dump Parallel Execution Granule Allocation - System information
103910x0100Dump Parallel Execution Granule Allocation - Reference object for the query
103910x0200Dump Parallel Execution Granule Allocation - kxfralo timing
103910x0400Dump Parallel Execution Granule Allocation - Affinity module
103910x0800Dump Parallel Execution Granule Allocation - Granule allocation during query execution
103910x1000Dump Parallel Execution Granule Allocation - Object flush
103931Dump Parallel Execution Statistics
104011KSXP tracing - event 10401 trace name context forever, level 1 (for RAC kernel)
104021SKGXP tracing - event 10402 trace name context forever, level 1 (for RAC kernel) minimal tracing
10402127SKGXP tracing - event 10402 trace name context forever, level 1 (for RAC kernel) maximum tracing
105001Trace SMON Process
1060810Trace Bitmap Index Creation
107041Trace Enqueues
107061Trace Global Enqueue Manipulation
1070810Trace RAC Buffer Cache
107101Trace Bitmap Index Access
107111Trace Bitmap Index Merge Operation
107121Trace Bitmap Index OR Operation
107131Trace Bitmap Index AND Operation
107141Trace Bitmap Index MINUS Operation
107151Trace Bitmap Index Conversion to ROWIDs
107161Trace Bitmap Index Compress / Decompress
107171Trace Bitmap Index Compaction
10719110719 - Trace Bitmap Index DML
107301Trace Fine Grained Access Predicates
107311Trace CURSOR Statements
107312Trace CURSOR Statements
108512Disables error 24005 when attempting to manually drop a queue table
10852 Enable dumping of the AQ statistics hash table
10853 Event for AQ statistics latch cleanup testing
10856 Disable AQ propagator from using streaming
10857 Force AQ propagator to use two-phase commit
10858 Crash the AQ propagator at different stages of commit
10859 Disable updates of message retry count
10860 Event for AQ admin disable new name parser
10861 Disable storing extended message properties
108621Resolve default queue owner to current user in enqueue/dequeue
10901 Disable extent trimming
109281Trace PL/SQL Execution
109381Trace PL/SQL Execution Statistics
29700 Enable the collection of DLM lock conversion statistics to query GV$DLM_CONVERT_LOCAL and GV$DLM_CONVERT_REMOTE
444100xmlagg() with a GROUP BY can fail withORA-22813 if the result is too large. No statistics
444101Basic statistics CURSOR, PARSE, EXEC, FETCH ERROR, SORT UMAP, ERROR, UMAP, STATS and XCTEND. Same as setting sql_trace=true.
444102Same as level 1
444104Level 1 plus binds
444108Level 1 plus wait events
4441012Combine levels 4 and 8
 
ShowDoc

10060 Event

10128 Event

CREATE TABLE kkoipt_table (
c1 INTEGER,
c2 VARCHAR2(80);		CREATE TABLE kkpap_pruning (
partition_count  NUMBER,
iterator         VARCHAR2(32),
partition_level  VARCHAR2(32),
order_pt         VARCHAR2(12),
call_time        VARCHAR2(12),
part#            NUMBER,
subp#            NUMBER,
abs#             NUMBER);
 
Trace File Locations$ORACLE_BASE/admin/sid_name/

bdump ... alert logs and system generated trace files
cdump ... core dumps
udump ... user generated trace files
Grant public access to all views used by TKPROF with verbose=y option.$ORACLE_HOME/rdbms/admin/utltkprf.sql
Add timed statistics to trace fileconn / as sysdba

set linesize 121
col name format a40
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'timed%';

ALTER SYSTEM SET timed_statistics=TRUE;
System Privilege To Enable and Disable TracingGRANT alter session TO <user_name>;
GRANT alter session TO uwclass;

REVOKE alter session FROM uwclass;
 
PFile Entries vs. Alter Session
init.ora Parameter Exampleevent='1401 trace name errorstack, level 12';
 
ALTER SESSION Commands
Start and Stop Session TracingALTER SESSION SET sql_trace = <TRUE | FALSE>;
ALTER SESSION SET sql_trace = TRUE;

ALTER SESSION SET sql_trace = FALSE;
 
Event Tracing Examples
Active Session Historyalter session set events 'immediate trace name ashdump level 10';
Control File Dumpalter session set events 'immediate trace name CONTROLF level 10';
Error Stack (Exception) Tracealter session set events '<error_number_without_leading_zeros> trace name errorstack level 10';
alter session set events '60 trace name errorstack level 10';
File Header Dumpalter session set events 'immediate trace name FILE_HDRS level 10';
Library Cache Dumpalter session set events 'immediate trace name LIBRARY_CACHE level 10';
Process State Dumpalter session set events 'immediate trace name PROCESSSTATE LEVEL 10';
Redo Log Headers Dumpalter session set events 'immediate trace name REDOHDR LEVEL 10';
System State Dumpalter session set events 'immediate trace name SYSTEMSTATE LEVEL 10';
 
Tracing Demo
Go through a 10053 output to find why Oracle ignores a hint or thinks it is smarter than you. One little trick is to grep for lines starting "Join order" and "Best so far." The join order lists tables by table name, so you can see when an order you expected did not survive (or did not reach) the CBO's processing. The "Best so far" appear only when the CBO has decided that the most recent join order has a plan better than the previous best, so you can quickly check the evolution of the plan.
ALTER SESSION SETtracefile_identifier = 'test_plan1';

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SELECT srvr_id
FROM servers
WHERE srvr_id IN (
   SELECT srvr_id
   FROM serv_inst);

SELECT srvr_id
FROM servers
WHERE srvr_id IN (
   SELECT i.srvr_id
   FROM serv_inst i, servers s
   WHERE i.srvr_id = s.srvr_id);

ALTER SESSION SET EVENTS '10053 trace name context OFF';
ALTER SESSION SET EVENTS '10046 trace name context OFF';
or
ALTER SESSION SET SQL_TRACE=FALSE;

review the trace file in $ORACLE_BASE/diag/orabase/orabase/trace
 
TKPROF

Generate TKPROF Output 		TKPROF <trace_file_name> <output_file_name>
[explain=<user/password> [table=<schema.table_name>]]
[print=<integer>]    -- list only the first nth SQL statements
[aggregate=<yes|no>
[insert=<file_name>] -- list SQL statements and data inside INSERT statements
[sys=<no>]           -- TKPROF does not list SQL statements run as user SYS
[record=<file_name>] -- record non-recursive statements in the trace file
[waits=<yes|no>]     -- record summary of for wait events in the trace file
[sort=<sort_options_list>] -- zero or more of the listed sort options
Sort OptionDescription
execntnumber of execute was called
execpucpu time spent executing
execunumber of buffers for current read during execute
exedsknumber of disk reads during execute
exeelaelapsed time executing
exemisnumber of library cache misses during execute
exeqrynumber of buffers for consistent read during execute
exerownumber of rows processed during execute
fchcntnumber of times fetch was called
fchcpucpu time spent fetching
fchcunumber of buffers for current read during fetch
fchdsknumber of disk reads during fetch
fchelaelapsed time fetching
fchqrynumber of buffers for consistent read during fetch
fchrownumber of rows fetched
prscntnumber of times parse was called
prscpucpu time parsing
prscunumber of buffers for current read during parse
prsdsknumber of disk reads during parse
prselaelapsed time parsing
prsmisnumber of misses in library cache during parse
prsqrynumber of buffers for consistent read during parse
useriduserid of user that parsed the cursor
TKPROF c: emp\orabase_ora_1492.trc   c: emp race_out.txt
 
PLSHPROF

Generate PLSHPROF Output 		PLSHPROF [<options>] -output <input_file1> [<input_file2>]
 
Note: "output" refers to the output from DBMS_HPROF not the output from PLSHPROF.
OptionsDescriptionDefault
-trace <symbol>Specifies function name of tree rootN/A
-skip <count>Skips first count calls. Use only with -trace symbol0
-collect <count>Collects information for count calls. Use only with -trace symbol1
-output <filename>Specifies name of output fileN/A
-summaryPrints only elapsed timeN/A
plshprof -trace -output hprof_trace.html hprof.trc
 
Related Topics
ASH
DBMS_HPROF
DBMS_MONITOR
DBMS_PROFILER
DBMS_SUPPORT
DBMS_SYSTEM
DBMS_TRACE
Explain Plan
haiross
关注
专栏目录
在Oracle中利用SQL_TRACE跟踪SQL的执行
交换一个思想，能得到俩思想
09-14 7981
当你在执行一条SQL语句非常慢的时候,你是不是想问Oracle怎么执行这条语句的呢? Oracle提供的SQL_TRACE工具可以让你知道你执行的SQL究竟做了什么.执行的过程会被 输出到trace文件中. 下面用例子来跟踪一个SQL语句的执行情况: SQL> create table t as select rownum as id,object_name from dba_object
使用sql_trace/10046事件进行数据库诊断
记录创业路上的一些想法
07-27 3804
sql_trace/10046事件是oracle提供的用于进行sql跟踪的手段，其内容包括sql的解析过程、sql的执行计划、绑定变量的使用、会话发生的等待事件。         在使用sql_trace/10046事件前，有两个参数需要注意： sys@ORCL> show parameter max_dump_file_size --对trace文件的大小限制 NA
Oracle Events & Level Dictionary
数据库天地
09-24 140
Sample: alter session set events '10046 trace name context forever, level 12'; Event # Level Description 8186 - ALTER SESSION SET EVENTS '8186 trace name context OFF' - VERSIONS BETW...
利用tkprof查看trace文件(原创)
czmmiao的专栏
04-22 253
生成trace文件 通过查看Oracle中的trace文件，往往有助于我们去分析数据库dump原因，执行计划的内部过程，绑定变量的使用情况，以及产生死锁的原因等等。我们可以通过如下3种方式手工生产trace文件开启trace跟踪会话级 SQL&gt; alter session set sql_trace=true;SQL&gt; alter session set events '1...
SQL优化及性能诊断(10046事件/tkprof)
浪缘的专栏
03-23 1012
<br />1.与之上次使用到的SQL_TRACE功能相比，我们这次将使用更高级的trace命令（10046）来做演示。我们先启用“高级”的10046事件生成一份待格式化的trace文件<br />1）连接到普通用户sec<br />sys@ora10g> conn sec/sec<br />Connected.<br /><br />2）先将timed_statistics参数设置为true，这样可以使TKPROF工具能提供更多的有意义的信息，方便性能诊断<br />sec@ora10g> alter s
10046事件与tkprof命令
Watson的专栏
12-13 3000
在ORACLE数据库里，我们通常可以使用如下这些方法(包括但不限于)得到目标SQL的执行计划： 1，explain plan 2，DBMS_XPLAN包 3，SQLPLUS中的AUTOTRACE开关 4，10046事件 5，10053事件 6，AWR报告或者STATSPACK报告 前面的四种方法比较普遍。   下面我来介绍下第四种即10046事件如何得到目标SQL的执行计划 1
Oracle开发：AWR与SQL_TRACE深度解析
4. **Tkprof工具**: Tkprof是Oracle提供的一个分析工具，用于解析和格式化由SQL_TRACE或10046事件生成的跟踪文件。通过`tkprof tracefile outputfile [options]`命令，可以将跟踪文件转换为易于分析的格式。在找出...
<<Oracle数据库性能优化艺术（第五期）>> 第11周 sql_trace及10046
t0nsha's Blog
02-16 1377
1.演示使用SQL_TRACE和10046事件对其它回话进行跟踪，并给出trace结果。SQL_TRACE演示SQL> select value from v$diag_info where name like 'Default Trace File';VALUE--------------------------------------------------------------------
oracle sql 跟踪
12-20
Oracle SQL跟踪是数据库管理员和开发人员用来诊断性能问题、调试SQL语句以及优化数据库操作的强大工具。在Oracle数据库系统中，SQL跟踪可以帮助我们获取到应用程序与数据库交互时执行的SQL语句及其详细信息，包括...
Oracle 10046事件 介绍(二) ---tkprof
weixin_34284188的博客
02-17 102
之前简单的写了10046事件介绍的文章http://hbxztc.blog.51cto.com/1587495/1898624，当然收集10046 trace不是最终的目的，能够读懂并且通过分析10046 trace进而分析相应sql的性能问题才是10046 trace真正发挥作用的地方。但是10046事件所产生的原始trace文件习惯称之为裸trace文件(raw ...
Oracle SQL Trace、Tkprof和10046事件
weixin_33739523的博客
01-08 129
一、SQL TRACE       当SQL语句出现性能问题时，我们可以用SQL_TRACE来跟踪SQL的执行情况，通过跟踪，我们可以了解一条SQL或者PL/SQL包的运行情况，SQL_TRACE命令会将SQL执行的整个过程输出到一个trace文件中，我们可以读这个trace文件来了解在这个SQL执行过程中Oracle都做了哪些操作。       可以通过sql命令启动SQL_TRACE,或者...
Oracle Trace tkprof
Jane Chiu
10-31 1014
tkprof主要是用来解释trace文件内容，把原始的trace文件转化为容易理解的文件。tkprof实例分析，视图换基表。
利用tkprof查看trace文件
haiyang_tian20140616的专栏
07-08 399
一 SQL TRACE 使用方法： 1.初始化sql trace 参数： timed_statistics=true 允许sql trace 和其他的一些动态性能视图收集与时间有关的参数、 SQL>alter session set titimed_statistics=true max_dump_file_size=500 指定跟踪文件的大小 SQL> alter system se
tkprof分析trace文件
gguxxing008的专栏
09-27 754
tkprof 文件含义：call : 表示sql语句执行的每个阶段，每个sql语句的活动被分成以下3部分：     Parse: 语句被解析的次数，主要是生成执行计划。包含hard parse与soft parse。需要做的工作：权限检查，表、列、引用的表的存在性检查;比较执行计划，选出最好的一个等等。     Execute: 真正执行语句的时间，对于DML语句，在此阶段中修改数据;对于select语句，这步只是标识出查询出的行。     Fetch : 只对select语句有效，DML语句的执行并没有该
Oracle tkprof工具格式化 10046 event trace文件
06-29 1183
Oracle tkprof工具格式化 10046 event trace文件
10046事件和tkprof命令
热爱生活，努力工作
11-25 664
在ORACLE数据库里，我们通常可以使用如下这些方法(包括但不限于)得到目标SQL的执行计划： 1，explain plan 2，DBMS_XPLAN包 3，SQLPLUS中的AUTOTRACE开关 4，10046事件 5，10053事件 6，AWR报告或者STATSPACK报告 前面的四种方法比较普遍。   下面我来介绍下第四种即10046事
解读tkprof格式化后的trace跟踪文件！
事后诸葛亮的程序人生（微信：zq9017197）
11-01 1972
下面先生成一个执行计划： SQL> select spid from v$process where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum = 1)); SPID ------------ 3679 SQL> alter session set sql_
oracle 读懂10046视频,Oracle 11g新SQL Trace 10046方法
weixin_36277197的博客
04-16 138
10046是每一个研究Oracle、进行SQL调优的朋友非常熟悉的工具。10046和10053两个诊断事件，可以方便的帮助我们了解Oracle CBO优化10046是每一个研究Oracle、进行SQL调优的朋友非常熟悉的工具。10046和10053两个诊断事件，可以方便的帮助我们了解Oracle CBO优化器行为和SQL执行行为。在商业非开源的Oracle情况下，我们很多的Internal知识都是...
Oracle SQL_TRACE详解：10046事件跟踪指南
"这篇文档是关于Oracle数据库中的10046事件跟踪的详细指南，主要介绍了如何使用SQL_TRACE功能来诊断和解决数据库问题。SQL_TRACE是一个强大的工具，允许跟踪SQL语句的执行过程，包括后台进程的活动，对理解和优化SQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值