Oracle | SQL Trace and TKPROF

Oracle SQL Trace and TKPROF Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose TKPROF stands for Transient Kernel Profiler

General Note: Use "dbms_monitor.session_trace_enable()" instead of "alter session set events ..." and you will see the columns sys.gv_$session updated

Events Query set serveroutput on set trimspool on set trim on DECLARE err_msg VARCHAR2(256); BEGIN dbms_output.enable (1000000); FOR err_num IN 10000..20999 LOOP err_msg := SQLERRM (-err_num); IF err_msg NOT LIKE '%Message '|| err_num || ' not found%' THEN dbms_output.put_line(err_msg); END IF; END LOOP; END; / DECLARE err_msg VARCHAR2(384); BEGIN dbms_output.enable (1000000); FOR err_num IN 10000..19999 LOOP err_msg := SQLERRM (-err_num); IF LOWER(err_msg) LIKE '% trac%' THEN dbms_output.put_line(err_msg); END IF; END LOOP; END; / DECLARE err_msg VARCHAR2(384); BEGIN dbms_output.enable (1000000); FOR err_num IN 10000..19999 LOOP err_msg := SQLERRM (-err_num); IF LOWER(err_msg) LIKE '% dump %' THEN dbms_output.put_line(err_msg); END IF; END LOOP; END; /

Event Definitions

Event #	Level	Description
`8186`	`-`	ALTER SESSION SET EVENTS '8186 trace name context OFF' - VERSIONS BETWEEN query
`10005`	`-`	Trace latch operations for debugging
`10013`	`1`	Monitor Transaction Recovery
`10015`	`1`	Dump Undo Segment Headers
`10015`	`3`	Identify corrupt undo segments
`10021`	`-`	KST event to trace control file header writes and reads
`10022`	`-`	Trace ktsgsp
`10027`	`2`
`10027`	`2`	Specify Deadlock Trace Information to be Dumped
`10027`	`2`	`ALTER SYSTEM SET EVENTS '10027 TRACE NAME CONTEXT FOREVER, LEVEL 2';`
`10028`	`-`	Dump trace information during lock / resource latch cleanup
`10032`	`4`	Dump Sort Statistics
`10031`	`-`	Sort Debug Events
`10032`	`10`	Dump Sort Statistics
`10033`	`4`	Dump Sort Large Run Statistics (blocks dumped to disk and read back) - poss. no levels.
`10033`	`10`	Dump Sort Intermediate Run Statistics
`10038`	`-`	Dump row source tree (QBADRV)
`10041`	`-`	Dump undo records skipped
`10045`	`1`	Trace Free List Management Operations
`10046`	`0`	No statistics generated
`10046`	`1`	Standard trace output including parsing, executes and fetches plus more
`10046`	`2`	Same as Level 1
`10046`	`4`	Level 1 + Bind Variables
`10046`	`8`	Level 1 + Waits
`10046`	`12`	Level 1 + Bind Variables & Waits
`10047`	`-`	Trace Session Switching
`10050`	`-`	Sniper Trace
`10053`	`1`	CBO ENABLE Optimizer Trace: Dump Optimizer Statistics and Computations
`10053`	`2`	CBO ENABLE Optimizer Trace: Dump Optimizer Computations only
`10054`	`-`	CBO Enable optimizer trace for recursive statement (RPI)
`10056`	`-`	Dump analyze stats (kdg)
`10059`	`-`	Simulate error in logfile create/clear
`10060`	`1`	Traces a SQL statement ensuring rows column will be populated. See Metalink Note 246821.1
`10065`	`1`	Restrict Library Cache Output for State Object Dumps - Library Objects Only
`10065`	`2`	Restrict Library Cache Output for State Object Dumps + library object lock details
`10065`	`3`	Restrict Library Cache Output for State Object Dumps + library object handle and library object
`10072`	`-`	Local tempspace tracing - recovery layer
`10073`	`-`	Latch Cleanup Tracing
`10074`	`-`	Default trace function mask for kst
`10079`	`2`	Trace data sent/received via SQLNet: Dump SQLNet Statistics
`10080`	`-`	Dump a block on a segment list which cannot be exchanged
`10081`	`1`	Dump High Water Mark Changes
`10083`	`-`	Trace IMCDT in LIMITED mode
`10095`	`-`	Dump debugger commands to the trace file
`10104`	`10`	Dump Hash Join Statistics to the trace file
`10128`	0x0001	Dump Partition Pruning Information
`10128`	0x0002	Dump partition iterators
`10128`	0x0004	Dump optimizer decisions about partition-wise joins
`10128`	0x0008	Dump ROWID range scan pruning information
`10132`	`12`	Dumps SQL statements along with the actual, after compilation, execution plan
`10134`	`-`	Trace SQL Memory Management for session
`10146`	`-`	Enable Oracle TRACE collection
`10148`	`-`	Quarantine trace event
`10166`	`-`	Trace long operation statistics updates
`10200`	`1`	Dump Consistent Read Buffer Status
`10201`	`1`	Dump Consistent Read Undo Application
`10209`	`-`	Enable simulated error on Control File. Enables event 10214 to signal an error for the control file identified by the Level number. A Level larger than the maximum signals all files.
`10210`	`10`	Table Integrity Check
`10211`	`10`	Index Integrity Check
`10213`	-	Crash after controlfile write
`10214`	-	Simulate a Control File write error. Level 1-9 simulates write errors on physical blocks 1-9. If Level 10 every Control File write will force an error. Requires event 10209 first to work.
`10215`	-	Simulate read errors on Control File. A level of 1-9 simulates write errors on physical blocks 1-9. Level 1 for header, 2 for shared info, >2 for any other block
`10216`	`-`	Dump the control file header
`10218`	`-`	Dump the UBA of applied undo
`10220`	`1`	Dump Changes to Undo Header
`10221`	`7`	Dump Undo Changes
`10224`	`1`	Dump Index Block Splits / Deletes Trace
`10225`	`1`	Dump Changes to Dictionary Managed Extents
`10226`	`-`	Trace CR applications of undo for data operations
`10228`	`-`	Trace application of redo by kcocbk
`10229`	`-`	Simulate datafile I/O error
`10231`	`10`	Causes full table scans to skip corrupt blocks, rather than fail with an ORA-01578 error
`10231`	`10`	`ALTER SESSION SET EVENTS='10231 trace name context forever';`
`10232`	`10`	Dump corrupted blocks symbolically when kcbgotten to the process trace file
`10233`	`10`	Traces memory usage statistics
`10235`	`1`	Check memory manager internal structures (use with ORA-600/ORA-7445 errors)
`10236`	`-`	Dump redo on object no. or block type mismatch errors 1410/8103
`10241`	`1`	Dump Remote SQL Execution Tracing/Validation
`10246`	`1`	Trace PMON Process actions to the trace file
`10247`	`-`	Turn on scgcmn tracing (VMS ONLY)
`10248`	`10`	Trace Dispatcher Processes
`10249`	`10`	Trace Shared Server (MTS) Processes (multi-stated servers)
`10250`	`-`	Trace all allocate and free calls to the topmost SGA heap
`10252`	`-`	Simulate write eror to data file header
`1053`	`-`	Simulate write error to redo log
`10254`	`-`	Trace cross-instance calls
`10257`	`-`	Trace shared server load balancing
`10266`	`-`	Trace OSD stack usage
`10270`	`10`	Debug Shared Cursors
`10285`	`-`	Simulate Control File header corruption with the next header verification
`10286`	`-`	Simulate Control File open error with next Control File open event
`10287`	`-`	Simulate archiver error with next archive log switch
`10289`	`1`	Dump a database block in hexadecimal rather than formatted
`10290`	`-`	Internal sequence tracing event
`10292`	`-`	Hang analysis trace event
`10293`	`-`	Trace log switch media recovery checkpoints
`10294`	`-`	Trace ksrpc
`10297`	`-`	Trace shared I/O pool
`10298`	`-`	Trace ksfd I/O
`10299`	`1`	Trace prefetch tracking decisions made by CKPT
`10300`	`-`	Distributed transaction tracing
`10302`	`-`	Trace create or drop internal trigger
`10303`	`-`	Trace loading of library cache for internal triggers
`10304`	`-`	Trace replication trigger
`10305`	`-`	Trace updatable materialized view trigger
`10306`	`-`	Trace materialized view log trigger
`10307`	`-`	Trace RepCat execution
`10310`	`-`	Trace synchronous change table trigger
`10318`	`-`	Trace extensible transaction header movements
`10319`	`-`	Trace PGA statistics maintenance
`10320`	`-`	Enable data layer (kdtgrs) tracing of space management calls
`10325`	`-`	Trace control file record section expand and shrink operations
`10333`	`-`	Dump MTTR statistics each time it is updated
`10335`	`-`	Trace database open status
`10347`	`-`	Dump active checkpoint entries and checkpoint buffers
`10357`	`1`	Debug Direct Path
`10366`	`-`	kgnfs tracing
`10367`	`-`	kgodm tracing
`10384`	`-`	parallel dataflow scheduler tracing
`10390`	`0x0001`	Dump Parallel Execution Slave Statistics - Slave-side execution messages
`10390`	`0x0002`	Dump Parallel Execution Slave Statistics - Coordinator-side execution messages
`10390`	`0x0004`	Dump Parallel Execution Slave Statistics - Slave context state changes
`10390`	`0x0008`	Dump Parallel Execution Slave Statistics - Slave ROWID range bind variables and xty
`10390`	`0x0010`	Dump Parallel Execution Slave Statistics - Slave fetched rows as enqueued to TQ
`10390`	`0x0020`	Dump Parallel Execution Slave Statistics - Coordinator wait reply handling
`10390`	`0x0040`	Dump Parallel Execution Slave Statistics - Coordinator wait message buffering
`10390`	`0x0080`	Dump Parallel Execution Slave Statistics - Slave dump timing
`10390`	`0x0100`	Dump Parallel Execution Slave Statistics - Coordinator dump timing
`10390`	`0x0200`	Dump Parallel Execution Slave Statistics - Slave dump allocation file number
`10390`	`0x0400`	Dump Parallel Execution Slave Statistics - Terse format for debug dumps
`10390`	`0x0800`	Dump Parallel Execution Slave Statistics - Trace CRI random sampling
`10390`	`0x01000`	Dump Parallel Execution Slave Statistics - Trace signals
`10390`	`0x02000`	Dump Parallel Execution Slave Statistics - Trace parallel execution granule operations
`10390`	`0x04000`	Dump Parallel Execution Slave Statistics - Force compilation by slave 0
`10391`	`0x0001`	Dump Parallel Execution Granule Allocation - Summary of each object scanned in parallel
`10391`	`0x0002`	Dump Parallel Execution Granule Allocation - Full dump of each object except extent map
`10391`	`0x0004`	Dump Parallel Execution Granule Allocation - Full dump of each object including extent map
`10391`	`0x0010`	Dump Parallel Execution Granule Allocation - Summary of each granule generators
`10391`	`0x0020`	Dump Parallel Execution Granule Allocation - Granule generators except granule instances
`10391`	`0x0040`	Dump Parallel Execution Granule Allocation - Granule generators including granule instances
`10391`	`0x0080`	Dump Parallel Execution Granule Allocation - System information
`10391`	`0x0100`	Dump Parallel Execution Granule Allocation - Reference object for the query
`10391`	`0x0200`	Dump Parallel Execution Granule Allocation - kxfralo timing
`10391`	`0x0400`	Dump Parallel Execution Granule Allocation - Affinity module
`10391`	`0x0800`	Dump Parallel Execution Granule Allocation - Granule allocation during query execution
`10391`	`0x1000`	Dump Parallel Execution Granule Allocation - Object flush
`10393`	`1`	Dump Parallel Execution Statistics
`10399`	`-`	Trace buffer allocation
`10401`	`1`	KSXP tracing: for RAC kernel
`10402`	`1`	SKGXP tracing: for RAC kernel minimal tracing
`10402`	`127`	SKGXP tracing: for RAC kernel maximum tracing
`10412`	`-`	Dump the call stack if an error is signaled
`10420`	`-`	Trace KSO OS-process operations
`10421`	`-`	Enable dump from ksbwco if there is no reply
`10423`	`-`	dump the call stack if the specified error is cleared
`10425`	`-`	Enable global enqueue operations event trace
`10426`	`-`	Enable ges/gcs reconfiguration event trace
`10427`	`-`	Enable global enqueue service traffic controller event trace
`10428`	`-`	Enable tracing of global enqueue service cached resource
`10429`	`-`	Enable tracing of global enqueue service IPC calls
`10430`	`-`	Enable ges/gcs dynamic remastering event trace
`10432`	`-`	Enable tracing of global cache service fusion calls
`10434`	`-`	Enable tracing of global enqueue service multiple LMS
`10435`	`-`	Enable tracing of global enqueue service deadlock detetction
`10437`	`-`	Enable trace of global enqueue service S optimized resources
`10439`	`-`	Enable tracing of global cache service fusion calls - part 2
`10440`	`-`	Enable global enqueue service inquire resource modes trace
`10441`	`-`	Enable diagnosibility daemon (DIAG) trace
`10442`	`-`	Enable trace of kst for ORA-01555 diagnostics
`10445`	`-`	Enable tracing of LMS priority management
`10448`	`-`	Dump trace for OLTP partial compression features diagnostics
`10449`	`-`	Enable trace of kst for undo manageability features diagnostics
`10453`	`-`	Dump compression statistics to trace file
`10472`	`-`	Dump reading log buffer hit ratio histogram to LGWR trace file
`10473`	`-`	Enable BRR tracing
`10474`	`-`	Controlfile time tracing
`10478`	`-`	DBW0 file identification trace event
`10484`	`-`	Enable tracing for online data file move operation
`10487`	`-`	Dump redo memory protection information
`10488`	`-`	Dump block headers read for media recovery
`10490`	`-`	Trace OSM misc. events
`10491`	`-`	Trace OSM messaging (KFN) events
`10492`	`-`	Trace OSM metadata events
`10494`	`-`	Trace OSM metadata events
`10495`	`-`	Trace OSM metadata events
`10497`	`-`	Trace OSM metadata events
`10499`	`-`	Trace OSM metadata events
`10500`	`1`	Trace SMON Process
`10505`	`-`	CBO enable dynamic sampling dump to table
`10507`	`-`	Trace bind equivalence logic
`10254`	`-`	CMON connection pool trace event
`10526`	`-`	Enable lightweight thread tracing
`10527`	`-`	Enable SGA allocation tracing
`10528`	`-`	Enable ksmg tracing
`10529`	`-`	Enables SGA range protection tracing
`10542`	`-`	Enable tracing for block compare
`10556`	`-`	Enable tracing for multi instance Redo Apply
`10569`	`-`	Trace datafile header writes
`10601`	`32`	Forces the database to consider SQL*Plus a foreign SQL application
`10604`	`-`	Trace parallel create index
`10606`	`-`	Trace parallel create index
`10607`	`-`	Trace index rowid partition scan
`10608`	`10`	Trace Bitmap Index Creation
`10609`	`-`	Trace for array index insertion
`10610`	`-`	Trace create index pseudo optimizer
`10622`	`-`	Test or trace online index build or rebuild
`10625`	`-`	Turn off redo log dump for the index when OERI 12700
`10627`	`-`	Dump the content of the index leaf block
`10639`	`-`	Dump library cache during reparse loops
`10690`	`-`	Set shadow process core file dump type (Unix only)
`10692`	`-`	Trace preserving buffer across standby role transition
`10701`	`-`	Dump direct loader index keys
`10704`	`1`	Trace Enqueues
`10706`	`1`	Trace Global Enqueue Manipulation
`10708`	`10`	Print out trace information from the RAC buffer cache
`10710`	`1`	Trace Bitmap Index Access
`10711`	`1`	Trace Bitmap Index Merge Operation
`10712`	`1`	Trace Bitmap Index OR Operation
`10713`	`1`	Trace Bitmap Index AND Operation
`10714`	`1`	Trace Bitmap Index MINUS Operation
`10715`	`1`	Trace Bitmap Index Conversion to ROWIDs
`10716`	`1`	Trace Bitmap Index Compress / Decompress
`10717`	`1`	Trace Bitmap Index Compaction for Index Creation
`10719`	`1`	Trace Bitmap Index DML
`10720`	`-`	Trace database scheduling
`10722`	`-`	Trace server level database scheduling
`10724`	`-`	Trace cross-instance broadcast
`10730`	`1`	Trace Fine Grained Access Policy Predicates
`10731`	`1`	Dump SQL for CURSOR expressions
`10731`	`2`	Dump SQL for CURSOR expressions
`10741`	`-`	Trace missing BRR generation
`10742`	`-`	Dump process state on flush buffer_cache
`10743`	`-`	Define the misbehaved vt dump thresholds
`10744`	`-`	Disable LMD stale cvak traces and dumps
`10756`	`-`	Event to enable svpt/autotxn tracing
`10757`	`-`	Event to control tracing row source: rows to dump per rowset
`10758`	`-`	Event to control tracing row source: start row number
`10759`	`-`	Event to control tracing row source: end row number
`10760`	`-`	Event to control tracing row source: maximum encoded operands to check
`10761`	`-`	Event to control tracing row source: size of dictionary
`10780`	`-`	LogMiner API trace event
`10781`	`-`	LogMiner reader trace event
`10782`	`-`	LogMiner preparer trace event
`10783`	`-`	LogMiner builder trace event
`10784`	`-`	LogMiner dictionary trace event
`10785`	`-`	LogMiner trace event
`10787`	`-`	Trace intra-instance broadcast
`10788`	`-`	Trace call stacks
`10790`	`-`	LogMiner trace event
`10791`	`-`	Logical Standby swithover/failover trace event
`10792`	`-`	Logical Standby XDAT trace event
`10793`	`-`	Logical Standby trace event
`10794`	`-`	Logical Standby trace event
`10795`	`-`	Time Drift (VKTM) Detection Process Trace Event
`10796`	`-`	Elevate Scheduler Priority trace event
`10800`	-	Disable Smart Disk Scans
`10801`	-	Enable Smart Disk Trace
`10805`	-	Trace state object operations
`10809`	-	Trace state object allocate / free history
`10810`	-	Trace snapshot too old
`10811`	-	Trace block cleanouts
`10812`	-	Trace Consistent Reads
`10826`	-	Enable upgrade/downgrade error message trace
`10830`	-	Trace group by sort row source
`10831`	-	Trace group by rollup row source
`10832`	-	Trace approximate NDV row source
`10839`	-	Trace / debug caching module (qesca.c)
`10840`	-	Trace / debug pl/sql caching module (kkxmInitCache)
`10842`	-	Event for OCI Tracing and Statistics Info
`10843`	-	Event for client result cache tracing
`10845`	-	Enable Director tracing
`10847`	-	OCI dump action is being invoked for internal error ORA-
`10850`	-	Enable time manager tracing
`10852`	-	Enable dumping of the AQ statistics hash table, Enable tracing for Enqueue Dequeue Operations
`10853`	-	Enable tracing for Replicating AQ Operations
`10865`	-	Control tracing of notification operations
`10871`	-	Dump file open/close timestamp during media recovery
`10880`	-	Trace Java VM execution
`10883`	-	Event for Fast Application Notification tracing
`10893`	-	TTC trace event
`10907`	-	Trace extent management events
`10908`	-	Trace temp tablespace events
`10909`	-	Trace free list events
`10924`	-	Import Storage Parse Error Ignore Event
`10925`	-	Trace PL/SQL Execution
`10926`	-	Trace name context forever
`10927`	-	Trace name context forever
`10928`	`1`	Trace PL/SQL Execution
`10929`	`-`	Trace name context forever
`10930`	`-`	Trace name context forever
`10931`	`-`	Trace name context forever
`10932`	`-`	Trace name context forever
`10933`	`-`	Trace name context forever
`10934`	`-`	Trace name context forever
`10936`	`-`	Trace name context forever
`10937`	`-`	Trace name context forever
`10938`	`1`	Trace PL/SQL Execution Statistics
`10939`	`-`	Trace name context forever
`10940`	`-`	Trace name context forever
`10941`	`-`	Trace name context forever
`10943`	`-`	Trace name context forever
`10944`	`-`	Trace name context forever
`10945`	`-`	Trace name context forever
`10946`	`-`	Trace name context forever
`10947`	`-`	Trace name context forever
`10948`	`-`	Trace name context forever
`10960`	`-`	AQ tracing event
`10961`	`-`	Enable tracing for Scheduler subscriber cleanup
`10975`	`-`	Trace execution of parallel propagation
`10976`	`-`	Internal package related tracing
`10977`	`-`	Trace event for RepAPI
`10979`	`-`	Trace flags for join index implementation
`10990`	`-`	Dump spreadsheet info
`12500`	`10`	The SMON time mapping is mainly for flashback type queries to map a time to an SCN so it is probably simplest to copy the content to a holding table then delete ALL rows, then recycle the instance. SMON should start to populate the table with new time / SCN pairs from the time that the instance is started. Setting the 12500 event at system level should stop SMON from updating the SMON_SCN_TIME table.
`12760`	`-`	ACMS tracing event
`16300`	`-`	LSBY architected tracing event
`16499`	`-`	Dump redo transport information
`16605`	`-`	Event to control fast-start failover diagnostic tracing
`16634`	`-`	Event for Data Guard NetSlave tracing
`16752`	`-`	Dump System State
`16756`	`-`	Dump Hang Analysis
`22829`	`-`	Enables lateral queries using the undocumented LATERAL() operator
`22829`	`-`	`ALTER SESSION SET EVENTS '22829 TRACE NAME CONTEXT FOREVER';`
`28400`		Enable TDE Tracing
`28401`	`1`	Disables the sleep time for logon failures caused by row cache locks leaving the system more vulnerable to brute force password attack
`28402`		Disable TDE Tracing
`44410`	`0`	xmlagg() with a GROUP BY can fail with ORA-22813 if the result is too large. No statistics
`44410`	`1`	Basic statistics CURSOR, PARSE, EXEC, FETCH ERROR, SORT UMAP, ERROR, UMAP, STATS and XCTEND.
`44410`	`2`	Same as level 1
`44410`	`4`	Level 1 plus binds
`44410`	`8`	Level 1 plus wait events
`44410`	`12`	Combine levels 4 and 8

General

Trace File Locations

Param	Name	Location
`ADUMP`	Audit Dump	$ORACLE_BASE/admin/<db_name>/adump
`BDUMP`	Background Dump	$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace
`CDUMP`	Core Dump	$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/cdump
`DPDUMP`	Data Pump Dump	$ORACLE_BASE/admin/<db_name>/dpdump
`UDUMP`	User Dump	$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace

$ORACLE_BASE/admin/sid_name/

Grant access to all views used by TKPROF with the verbose=Y option $ORACLE_HOME/rdbms/admin/utltkprf.sql

Add timed statistics to the trace file conn / 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 Tracing GRANT alter session TO <user_name>;

GRANT alter session TO uwclass;

      

      REVOKE alter session FROM uwclass;

PFILE/SPFILE Entry

Startup Parameter Parameter Example event='1401 trace name errorstack, level 12';

ALTER SESSION Syntax

Start and Stop Session Tracing ALTER SESSION SET sql_trace = <TRUE | FALSE>;

ALTER SESSION SET sql_trace = TRUE;

      

      ALTER SESSION SET sql_trace = FALSE;

Event Tracing Examples

Active Session History ALTER SESSION SET EVENTS 'immediate trace name ashdump level 10';

Block Dump ALTER SESSION SET EVENTS '10289 trace name context forever';

Control File Dump ALTER SESSION SET EVENTS 'immediate trace name CONTROLF level 10';

Database Vault ALTER SESSION SET EVENTS 'TRACE[DV] DISK=<HIGH | LOW]'

ALTER SESSION SET EVENTS 'TRACE[DV] 
      DISK=HIGH';

Error Stack (Exception) Trace ALTER 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 Dump ALTER SESSION SET EVENTS 'immediate trace name FILE_HDRS level 10';

Library Cache Dump ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 10';

Process State Dump ALTER SESSION SET EVENTS 'immediate trace name PROCESSSTATE LEVEL 10';

Redo Log Headers Dump ALTER SESSION SET EVENTS 'immediate trace name REDOHDR LEVEL 10';

System State Dump ALTER SESSION SET EVENTS 'immediate trace name SYSTEMSTATE LEVEL 10';

Tracing Demo

Example tracing exercise using SETUP objects from Morgan's Library's UWCLASS schema 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 SET tracefile_identifier = 'test_plan1';

      

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

      

      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

Forces treating SQL*Plus as foreign SQL> ALTER SESSION SET EVENTS = �10601 trace name context forever, level 32';

SMON time mapping SQL> alter system set events '12500 trace name context forever, level 10'; SQL> alter system set events '12500 trace name context off';

TKPROF

Generate TKPROF Output and command line switches

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 Option	Description
execnt	number of execute was called
execpu	cpu time spent executing
execu	number of buffers for current read during execute
exedsk	number of disk reads during execute
exeela	elapsed time executing
exemis	number of library cache misses during execute
exeqry	number of buffers for consistent read during execute
exerow	number of rows processed during execute
fchcnt	number of times fetch was called
fchcpu	cpu time spent fetching
fchcu	number of buffers for current read during fetch
fchdsk	number of disk reads during fetch
fchela	elapsed time fetching
fchqry	number of buffers for consistent read during fetch
fchrow	number of rows fetched
prscnt	number of times parse was called
prscpu	cpu time parsing
prscu	number of buffers for current read during parse
prsdsk	number of disk reads during parse
prsela	elapsed time parsing
prsmis	number of misses in library cache during parse
prsqry	number of buffers for consistent read during parse
userid	userid of user that parsed the cursor

TKPROF c:\temp\orabase_ora_1492.trc c:\temp\trace_out.txt

      

      TKPROF c:\temp\orabase_ora_1492.trc  c:\temp\trace_out.txt aggregate=yes waits=yes sys=yes

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.

Options	Description	Default
-trace <symbol>	Specifies function name of tree root	N/A
-skip <count>	Skips first count calls. Use only with -trace symbol	0
-collect <count>	Collects information for count calls. Use only with -trace symbol	1
-output <filename>	Specifies name of output file, by default .html	N/A
-summary	Prints only elapsed time	N/A

plshprof -trace -output hprof_trace.html hprof.trc