Seeing Which Events Are Set in a Session

Oracle Tips by Burleson

For ALTER SESSION commands that set events, you can use the undocumented dbms_system.read_ev procedure. For example:

set serveroutput on
declare
event_level number;
begin
for i in 10000..10999 loop
     sys.dbms_system.read_ev(i,event_level);
     if (event_level > 0) then
        dbms_output.put_line('Event '||to_char(i)||' set at level '||
                             to_char(event_level));
     end if;
end loop;
end;
/

To demonstrate how the above PL/SQL can be used, create the script check_param.sql. Note that setting the init<sid>.ora parameter sql_trace sets the event 10046. Other parameters such as timed_statistics, optimizer_mode do not set events.

Within the session issue:

SQL> @check_param
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=true;
SQL> alter session set events '10015 trace name context forever, level 3';
SQL> @check_param
Event 10015 set at level 12
Event 10046 set at level 1
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> @check_param
Event 10015 set at level 12
Event 10046 set at level 12
PL/SQL procedure successfully completed.
SQL> alter session set sql_trace=false;
SQL> alter session set events '10015 trace name context off';
SQL> @check_param
PL/SQL procedure successfully completed.

Using DBMS_SYSTEM.SET_EV and DBMS_SYSTEM.READ_EV

The DBMS_SYSTEM package contains two useful procedures related to events: SET_EV, used to set a specific event, and READ_EV, used to see the current status of an event. The procedures are defined as follows:

DBMS_SYSTEM.SET_EV(

SI     Binary_integer,
SE     Binary_integer,
EV     Binary_integer,
LE     Binary_integer,
NM     Binary_integer);

where:

SI is the Oracle SID value.
SE is the Oracle serial number.
EV is the Oracle event to set.
LE is the event level.
NM is the name.

For example:

EXECUTE SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,level,'');

The DBMS_SYSTEM.READ_EV has the following syntax:

DBMS_SYSTEM.READ_EV(
IEV binary_integer,
OEV binary_integer);

where:

IEV is the Oracle event (in value).
OEV is the Oracle event setting (out value).

For example:

EXECUTE sys.dbms_system.read_ev(i,event_level);

Depending on the analyst, day of the week, and phase of the moon, Oracle support will either tell you to use or not to use DBMS_SYSTEM SET_EV and READ_EV.

A list of the Oracle event codes is shown in Table 2.6.

Table 2.6 Oracle Event Codes

Code	Description
10000	Controlfile debug event, name ‘control_file’.
10001	Controlfile crash event1.
10002	Controlfile crash event2.
10003	Controlfile crash event3.
10004	Controlfile crash event4.
10005	Trace latch operations for debugging.
10006	Testing; block recovery forced.
10007	Log switch debug crash after new log select, thread %s.
10008	Log switch debug crash after new log header write, thread %s.
10009	Log switch debug crash after old log header write, thread %s.
10010	Begin transaction.
10011	End transaction.
10012	Abort transaction.
10013	Instance recovery.
10014	Roll back to save point.
10015	Undo segment recovery.
10016	Undo segment extend
10017	Undo segment wrap.
10018	Data segment create.
10019	Data segment recovery.
10020	Partial link restored to linked list (KSG).
10021	Latch cleanup for state objects (KSS).
10022	Trace ktsgsp.
10023	Create save undo segment.
10024	Write to save undo.
10025	Extend save undo segment.
10026	Apply save undo.
10027	Latch cleanup for enqueue locks (KSQ).
10028	Latch cleanup for enqueue resources (KSQ).
10029	Session logon (KSU).
10030	Session logoff (KSU).
10031	Row source debug event (R*).
10032	Sort end (SOR*).
10035	Parse SQL statement (OPIPRS).
10036	Create remote row source (QKANET).
10037	Allocate remote row source (QKARWS).
10038	Dump row source tree (QBADRV).
10039	Type checking (OPITCA).
10040	Dirty cache list.
10041	Dump undo records skipped.
10042	Trap error during undo application.
10044	Freelist undo operations.
10045	Freelist update operations: ktsrsp, ktsunl.
10046	Enable SQL statement timing.
10047	Trace switching of sessions.
10048	Undo segment shrink.
10049	Protect library cache memory heaps.
10050	Sniper trace.
10051	Trace OPI calls.
10052	Don’t clean up obj$.
10053	CBO: Enable optimizer trace.
10054	Trace UNDO handling in MLS.
10055	Trace UNDO handling.
10056	Dump analyze stats (kdg).
10057	Suppress filenames in error messages.
10058	Use table scan cost in tab$.spare1.
10060	CBO: Enable predicate dump.
10061	Disable SMON from cleaning temp segment.
10062	Disable usage of OS roles in OSDS.
10063	Disable usage of DBA and OPER privileges in OSDS.
10064	Thread-enable debug crash level %s, thread %s.
10065	Limit library cache dump information for state object dump.
10066	Simulate failure to verify file.
10067	Force redo log checksum errors: block number.
10068	Force redo log checksum errors: file number.
10069	Trusted Oracle test event.
10070	Force datafile checksum errors: block number.
10071	Force datafile checksum errors: file number.
10072	Protect latch recovery memory.
10073	Have PMON dump info before latch cleanup.
10074	Default trace function mask for kst.
10075	CBO: Disable outer-join to regular join conversion.
10076	CBO: Enable Cartesian product join costing.
10077	CBO: Disable view-merging optimization for outer-joins.
10078	CBO: Disable constant predicate elimination optimization.
10080	Dump a block on a segment list that cannot be exchanged.
10081	Segment high-water mark has been advanced.
10082	Freelist head block is the same as the last block.
10083	A brand new block has been requested from space management.
10084	Freelist becomes empty.
10085	Freelists have been merged.
10086	CBO: Enable error if kko and qka disagree on oby sort.
10087	Disable repair of media corrupt data blocks.
10088	CBO: Disable new, NOT IN optimization.
10089	CBO: Disable index sorting.
10090	Invoke other events before crash recovery.
10091	CBO: Disable constant predicate merging.
10092	CBO: Disable hash join.
10093	CBO: Enable force hash joins.
10094	Before resizing a data file.
10095	Dump debugger commands to trace file.
10096	After the cross instance call when resizing a datafile.
10097	After generating redo when resizing a datafile.
10098	After the OS has increased the size of a datafile.
10099	After updating the file header with the new file size
10100	After the OS has decreased the size of a datafile.
10101	Atomic redo write recovery.
10102	Switch off anti-joins.
10103	CBO: Disable hash join swapping.
10104	Dump hash join statistics to trace file.
10105	CBO: Enable constant pred trans and MPS with WHERE clause.
10106	CBO: Disable evaluating correlation pred last for NOT IN.
10107	CBO: Always use bitmap index.
10108	CBO: Don’t use bitmap index.
10109	CBO: Disable move of negated predicates
10110	CBO: Try index rowid range scans.
10111	Bitmap index creation switch.
10112	Bitmap index creation switch.
10113	Bitmap index creation switch.
10114	Bitmap index creation switch.
10115	CBO: Bitmap optimization use maximal expression.
10116	CBO: Bitmap optimization switch.
10117	CBO: Disable new parallel cost model.
10118	CBO: Enable hash join costing.
10119	QKA: Disable GBY sort elimination.
10120	CBO: Disable index fast full scan.
10121	CBO: Don’t sort bitmap chains.
10122	CBO: Disable count(col) = count(*) transformation.
10123	QKA: Disable bitmap and-equals.
10145	Test auditing network errors.
10146	Enable Oracle TRACE collection.
10200	Block cleanout.
10201	Consistent read undo application.
10202	Consistent read block header.
10203	Consistent read buffer status.
10204	Signal recursive extend.
10205	Row cache debugging.
10206	Transaction table consistent read.
10207	Consistent read transactions’ status report.
10208	Consistent read-loop check.
10209	Enable simulated error on controlfile.
10210	Check data block integrity.
10211	Check index block integrity.
10212	Check cluster integrity.
10213	Crash after control file write.
10214	Simulate write errors on controlfile.
10215	Simulate read errors on controlfile.
10216	Dump controlfile header.
10217	Debug sequence numbers.
10218	Dump UBA of applied undo.
10219	Monitor multipass row locking.
10220	Show updates to the transaction table.
10221	Show changes done with undo.
10222	Row cache.
10223	Transaction layer: Turn on verification codes.
10226	Trace CR applications of undo for data operations.
10227	Verify (multipiece) row structure.
10228	Trace application of redo by kcocbk.
10230	Check redo generation by copying before applying.
10231	Skip corrupted blocks on _table_scans._
10232	Dump corrupted blocks symbolically when kcb gotten.
10233	Skip corrupted blocks on index operations.
10234	Trigger event after calling kcrapc to do redo N times.
10235	Check memory manager internal structures.
10236	Library cache manager.
10237	Simulate ^C (for testing purposes).
10238	Instantiation manager.
10239	Multi-instance library cache manager.
10240	Dump DBAs of blocks that we wait for.
10241	Dump SQL generated for remote execution (OPIX).
10243	Simulated error for test percentages of K2GTAB latch cleanup.
10244	Make tranids in error msgs print as 0.0.0 (for testing).
10245	Simulate lock conflict error for testing PMON.
10246	Print trace of PMON actions to trace file.
10247	Turn on scgcmn tracing (VMS ONLY).
10248	Turn on tracing for dispatchers.
10249	Turn on tracing for multistated servers.
10250	Trace all allocate and free calls to the topmost SGA heap.
10251	Check consistency of transaction table and undo block.
10252	Simulate write error to data file header.
10253	Simulate write error to redo log.
10254	Trace cross-instance calls.
10256	Turn off multithreaded server load balancing.
10257	Trace multithreaded server load balancing.
10258	Force shared servers to be chosen round-robin.
10259	Get error message text from remote using explicit call.
10260	Trace calls to SMPRSET (VMS ONLY).
10261	Limit the size of the PGA heap.
10262	Don’t check for memory leaks.
10263	Don’t free empty PGA heap extents.
10264	Collect statistics on context area usage (x$ksmcx).
10265	Keep random system-generated output out of error messages.
10266	Trace OSD stack usage.
10267	Inhibit KSEDMP for testing.
10268	Don’t do forward coalesce when deleting extents.
10269	Don’t do coalesces of free space in SMON.
10270	Debug shared cursors.
10271	Distributed transaction after COLLECT.
10272	Distributed transaction before PREPARE.
10273	Distributed transaction after PREPARE.
10274	Distributed transaction before COMMIT.
10275	Distributed transaction after COMMIT.
10276	Distributed transaction before FORGET.
10277	Cursor sharing (or not) related event (used for testing).
10281	Maximum time to wait for process creation.
10282	Inhibit signaling of other backgrounds when one dies.
10286	Simulate control file open error.
10287	Simulate archiver error.
10288	Do not check block type in ktrget.
10289	Do block dumps to trace file in hex rather than formatted.
10290	Kdnchk-- checkvalid event-- not for general-purpose use.
10291	Die in dtsdrv to test controlfile undo.
10292	Dump uet entries on a 1561 from dtsdrv.
10293	Dump debugging information when doing block recovery.
10294	Enable PERSISTENT DLM operations on noncompliant systems.
10300	Disable undo compatibility check at database open.
10301	Enable LCK timeout table consistency check.
10320	Enable data layer (kdtgrs) tracing of space management calls.
10352	Report direct path statistics.
10353	Number of slots.
10354	Turn on direct read path for parallel query.
10355	Turn on direct read path for scans.
10356	Turn on hint usage for direct read.
10357	Turn on debug information for direct path.
10374	Parallel query server interrupt (validate lock value).
10375	Turn on checks for statistics rollups.
10376	Turn on table queue statistics.
10377	Turn off load balancing.
10379	Direct read for rowid range scans (unimplemented).
10380	Kxfp latch cleanup testing event.
10381	Kxfp latch cleanup testing event.
10382	Parallel query server interrupt (reset).
10383	Auto parallelization testing event.
10384	Parallel dataflow scheduler tracing.
10385	Parallel table scan range-sampling method.
10386	Parallel SQL hash and range statistics.
10387	Parallel query server interrupt (normal).
10388	Parallel query server interrupt (failure).
10389	Parallel query server interrupt (cleanup).
10390	Trace parallel query slave execution.
10391	Trace rowid range partitioning.
10392	Parallel query debugging bits.
10393	Print parallel query statistics.
10394	Allow parallelization of small tables.
10395	Adjust sample size for range table queues.
10396	Circumvent range table queues for queries.
10397	Suppress verbose parallel coordinator error reporting.
10398	Enable timeouts in parallel query thread.s
10399	Use different internal maximum buffer size.
10400	Turn on system state dumps for shutdown debugging.
10500	Turn on traces for SMON.
10510	Turn off SMON check to offline pending offline rollback segment.
10511	Turn off SMON check to cleanup undo dictionary.
10512	Turn off SMON check to shrink rollback segments.
10600	Check cursor frame allocation.
10602	Cause an access violation (for testing purposes).
10603	Cause an error to occur during truncate (for testing purposes).
10604	Trace parallel create index.
10605	Enable parallel create index by default.
10606	Trace parallel create index.
10607	Trace index rowid partition scan.
10608	Trace create bitmap index.
10610	Trace create index pseudo optimizer.
10666	Do not get database enqueue name.
10667	Cause sppst to check for valid process IDs.
10690	Set shadow process core file dump type (UNIX only).
10691	Set background process core filetype (UNIX only).
10700	Alter access violation exception handler.
10701	Dump direct loader index keys.
10702	Enable histogram data generation.
10703	Simulate process death during enqueue get.
10704	Print out information about which enqueues are being obtained.
10706	Print out information about instance lock manipulation.
10707	Simulate process death for instance registration.
10708	Print out tracing information for skxf multi-instance comms.
10709	Enable parallel instances in create index by default.
10710	Trace bitmap index access.
10711	Trace bitmap index merge.
10712	Trace bitmap index OR.
10713	Trace bitmap index AND.
10714	Trace bitmap index minus.
10715	Trace bitmap index conversion to rowids.
10800	Disable Smart Disk scan.
10801	Enable Smart Disk trace.
10802	Reserved for Smart Disk.
10803	Write timing statistics on OPS recovery scan.
10804	Reserved for ksxb.
10805	Reserved for row source sort.
10900	Extent manager fault insertion event #%s.
10924	Import storage parse error ignore event.
10925	Trace name context forever.
10926	Trace name context forever.
10927	Trace name context forever.
10928	Trace name context forever.
10999	Do not get database enqueue name.

Source 2.7 shows an example of how to use events. As with other powerful and undocumented Oracle features, make sure someone who thoroughly understands the ramifications of using the event or events is present when you attempt usage of the event codes. Improper use of events can result in database crashes and corruption.

SOURCE 2.7 Use of SET EVENTS at the session level.

How to dump a segment header - by Don Burleson

set heading off;
spool dump_em.sql;

select
'alter session set events ''immediate trace name blockdump level '||
to_char((header_file*16777216)+header_block)||''';'
from
dba_segments
where
segment_name = 'VBAP';

spool off;

cat dump_em.sql
@dump_em

The segment header block dump will be in the user session trace file. As with the undocumented initialization parameters, events should be used only under the direction of a senior-level DBA or Oracle support.

Further DBA Reading

For further reading, the DBA should look at the following references:

Oracle Database Administrator’s Guide, Release 1, 9.0.1, Part No. A90117-01, Oracle Corporation, June 2001.

Oracle SQL Reference, Release 1, 9.0.1, Part No. A90125-01, Oracle Corporation, June 2001.

Oracle Reference, Release 1, 9.0.1, Part No. A90190-01, Oracle Corporation, June 2001.

Metalink Notes: 28446.1, 130283.1, 1051056, 134940.16, 121491.1, and 88624.999;

Steve Adam's Web site: www.ixora.com.au

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books