Return to KB Computer Home
Attention!
Read This!
Tracepoints are Officially Unsupported by Ingres Corp. While most tracepoints
are relatively harmless, and output gibberish useful only to Ingres
development and Tech Support, some tracepoints are actively dangerous.
In addition, Ingres Corp reserves the right to change tracepoints at any time,
and a previously innocuous tracepoint may change to a dangerous one.
Do not use tracepoints on a production system unless you are
either instructed to do so by Tech Support, or are very careful and
willing to accept the consequences.
Some tracepoints are "well known", relatively useful, and
are unlikely to change. They are just as officially unsupported as
any other tracepoint, but they have been around for a long time.
Used responsibly, they can assist in debugging or tuning an Ingres
installation.
My idea of "well known" tracepoints are:
DM32, DM420, DM421, DM801, DM1305, DM1440
OP165, OP173, OP188, OP255
QE90, QE99
QS501, QS506
RD10, RD11
SC906
and if you want a tracepoint to show off and impress your co-workers,
I like OP150. :-)
Please note that this list is my opinion. Others may have different
ideas.
The following list is believed to be reasonably accurate for
Ingres r3 (3.0.3) and Ingres 2006 (9.0.4). I have a few of the 9.1.0
trace points, but I haven't yet made an attempt to collect all
of them. If you can
demonstrate a trace point that I have omitted, or mis-described,
I would be interested in hearing about
it.
Note: some tracepoints are equivalents of SET statements. Not all such
tracepoints have been included. (E.g. QE5 is "SET NORULES".)
I have also omitted a few tracepoints that I happened upon, that appear
to do nothing useful except perhaps in the Ingres QA environment.
Jump to: DMF OPF PSF QEF QSF RDF SCF
| | No interesting AD (ADF) tracepoints. |
|
|
| | DMF tracepoints from 01 to 99 are session specific.
Everything over 99 is server wide, unless noted otherwise.
|
| |
| DM10 | equivalent to SET IO_TRACE |
| DM11 | equivalent to SET LOG_TRACE |
| DM12 | When used with DM11, turns on LSN display in log trace |
| DM32 | Turn off Replicator data capturing for the current session |
| DM33 | Row-lock protocol double-checking for session |
| DM34 | Output page size/page type xref |
| DM36 nn | Set DMF parallelism to nn threads for MODIFY.
Equivalent to degree_of_parallelism config parameter. |
| DM101 | Dump DMF memory to II_DBMS_LOG |
| DM102 | Turn off Replicator for the current database |
| DM103 | Fill DMF memory with pad character for debugging |
| DM105 | Toggle DMF memory stats collection by type |
| DM302 | Turn on buffer manager lock tracing |
| DM303 | Send CSP request to enter debugger |
| DM304 | Lock request and list dump when escalation deadlock detected |
| DM305 to 308 | like log_esc_xx_yy config settings |
| DM309 | Turn on locklist dump (into iircp.log) when deadlock detected |
| DM415 | Turn on buffer manager consistency checks |
| DM416 | DMF Memory dump if DM415 consistency check detects error |
| DM418 | Error return after 9C01 error |
| DM419 | DMF memory dump after 9C01 error |
| DM420 | Output DMF buffer cache stats snapshot to II_DBMS_LOG |
| DM421 | Flush and invalidate DMF buffer cache |
| DM422 | Invalidate unmodified pages from DMF buffer cache |
| DM426 | Don't do group reads |
| DM427 baseid pageno | Purge single (base table) page from cache |
| DM428 baseid | Purge table's pages from cache (and its indexes
too) |
| DM429 | Enable transaction queue consistency checks |
| DM430 | Print stuff if DM429 detects a loop |
| DM431 | Disable page checksumming
Warning! DM431 could make your database permanently unusable
by a regular server! |
| DM432 | Make checksum error a warning/trace message |
| DM434 | Print extra control block stuff if DM415 detects error |
| DM435 | Ignore cache priorities (use TCB priority instead) |
| DM483 | Fix only one system catalog page at a time |
| DM501 | Log simple sort statistics, DMF sorts only |
| DM502 | Alternate work file striping method (unsure of details) |
| DM503 | Disable parallel sort |
| DM504-506 | Force 1, 2, 3 sort threads |
| DM508-510 | Force sort exchange buf size to 500, 5000, 25000 |
| DM511 | Dump statistics after a MODIFY |
| DM601-DM610 | Log BTREE Get, Del, Put, Replace, Split, Merge,
DM607 notused, Allocate, Search, Build debug info to DBMS log. Can be massive |
| DM611 | Log BTREE allocate-entry-on-page |
| DM612 | Added BTREE edit checking e.g. in allocate-entry |
| DM612 | Additional output in BTREE build trace (maybe DM612 got used twice?) |
| DM613 | BTREE build call logging |
| DM614 | Log btree page if corruption detected, server wide |
| DM615 baseId indexId | Dump btree base table or index to
II_DBMS_LOG |
| DM616 | BTREE CREATE INDEX: force index compression |
| DM617 | Skip freeing of empty BTREE overflow chain |
| DM650 | Trace page free list operations |
| DM651 | Build-freelist tracing |
| DM713 baseid | Purge table control block (and index TCB's) |
| DM714 pagesize | Set default page size for server |
| DM716 | Force row level locking if possible |
| DM717 | Force read-committed isolation level |
| DM718 | Force repeatable read isolation level |
| DM720 | gateway call trace |
| DM720 | Also, force continue instead of rollback in dup checking |
| DM721 pagetype | Set default page type for server |
| DM723 | Debug check for proper lock protocol before logging btree-update
log records |
| DM801 | DMF will skip bad rows (eg to allow modify of corrupted table) |
| DM802 | DMF will return garbage for bad rows (eg to allow deleting them) |
| DM803 | Report but ignore DMF errors while handling blobs |
| DM804 | Dump SAGG (simple aggregate) stuff |
| DM902 | Compress "old" row in REP log records |
| DM903 | Log beginning/end of rollback in II_DBMS_LOG |
| DM1002 nrows | Set size of parallel-index exchange buffer |
| DM1003 nbufs | Set number of parallel-index exchange buffers |
| DM1201 | Call operation (see DM1205) |
| DM1202 | Call return status |
| DM1203 | Call input parameters |
| DM1204 | Call output parameters |
| DM1205 | Turns on dmd_call tracing (other DM12xx things)
Also causes hard spin if DMF parameter error (to allow e.g. pstack) |
| DM1210 | Call timing enable (VMS only) |
| DM1211 | Call timing display (only really useful on VMS?) |
| DM1220 | Perform memory check at dmd_call |
| | NOTE: For rcp, acp, etc set DM13xx via II_DMF_TRACE=nnn,nnn,nnn ... |
| DM1301 | Log fast-commit thread stats |
| DM1302 | Log write-behind thread stats |
| DM1303 | Log consistency point EOF stats |
| DM1305 | Force a consistency point |
| DM1306 | Set verbosity in the rcp |
| DM1309 | Even more verbosity in rcp, needs DM1306 |
| DM1310 | Trace page header too if DM1315 |
| DM1311 | Trace page contents too if DM1315 |
| DM1312 | Turn OFF log file dumping if inconsistency error in rcp |
| DM1313 | Triggers verbosity in the archiver |
| DM1314 | Force an archiver cycle |
| DM1315 | Turn on extra tracing in redo/journal |
| DM1316 | Inhibit dmd_check memory dump |
| DM1320 | Enable parallel secondary index updating (experimental) |
| DM1419 | Crash the logging system |
| DM1434 | Force log buffers to disk (might be useful before forced crash) |
| DM1440 | Show work (ie, sort) locations |
| DM1442 | Toggle optimized TX log writes |
| DM1443 | Toggle optimized TX log write tracing (to DBMS log) |
|
|
| | OPF trace flags: in theory, through OP128 are global, above 128 are session.
But it doesn't seem that the global versions are implemented. |
| OP129 | (reserved for upcoming insert-via-LOAD optimization) |
| OP130 | Turn off nonessential consistency checks,
which at present seem to be just the "histogram increasing" checks |
| OP131 | (reserved: will disable join-time partition qualification) |
| OP132 | SET NOFLATTEN |
| OP133 | Flatten queries even if the parser suggests not to; flattening can
still be suppressed by SET NOFLATTEN, or the query_flattening parameter
OFF, or the qflatten_aggregate parameter OFF with correlated aggrs.
Note that OP133 is not the same as SET FLATTEN. |
| OP134 | Turn off NOT IN/NOT EXISTS -> outer join transformation |
| OP144 | Write basic OPF stats to DBMS log after each query |
| OP145 | Appears to be the same as SET QEP |
| OP146 | Dump query trees at start of aggregate processing |
| OP147 | Trace aggregate combining processing |
| OP148 | Suppress presort of K/T-join outers |
| OP149 | Show overall QP tree outline |
| OP150 | Detailed QP dump: QP plus result of OPC code gen |
| OP151 | Turn off query compilation, useful to see if crash-problem is in
OPF or OPC |
| OP152 | like SET NOTIMEOUT |
| OP153 | An abbreviated joinop table output (shorter than OP156) |
| OP156 | Print all joinop tables |
| OP157 | Apparently does a couple things:
prints outer join "stuff" during/after outer join processing;
prints map of equivalence classes for which histograms are needed |
| OP158 | Print parser stuff for SE join subquery |
| OP159 | Send warning messages to the user instead of suppressing them |
| OP160 | like SET OPTIMIZEONLY |
| OP161 | Dump data structures from OPF to OPC |
| OP162 | Generic "new feature" switch, uninteresting in 3.0.x |
| OP163 | Prevents use of histograms in join selectivity computation, using an IBM heuristic instead |
| OP164 | Dump distributed query text for each CO tree node (STAR only) |
| OP165 | Don't use any statistics |
| OP166 | Enable a fake gateway optimizer |
| OP167 | Don't pick key joins (use merge or hash joins instead) |
| OP169 | Treat all STAR queries as multi-site queries |
| OP170 | Modified parse tree display (for QEP, also happens at "interesting"
points like NOT EXISTS transform, CNF normalization, etc) |
| OP171 | Send constants as ~V (for STAR) |
| OP172 | Suppress use of RTREE indexes |
| OP173 | Enable the building of the print-row CXs (for QE99, QE1nn tracing) |
| OP174 | change behaviour of control C to exit with best query plan |
| OP175 | Check text segments to make sure everything is printable (STAR) |
| OP176 | Simply continue from float exceptions in the optimizer, don't recover |
| OP177 | Attempt to always use keying strategies (doesn't work too well) |
| OP178 | Default the "complete" flag to TRUE |
| OP180 | Look at cartesian product search space.
Useful for multi-attribute keying in which cart-prod of small
tables could be used to key into the large table.
Allows cart-prod below top node when generated by OPF (as opposed to
required by the form of the user query); normally such generated qep's
are rejected. |
| OP181 | Look at reduced search space given heuristic estimates of index usage;
tries to avoid full enumeration. |
| OP182 | Force old CNF algorithm (transform everything to CNF), turns off
MS-Access transformation |
| OP183 | Don't do the CNF transformation (overrides everything else) |
| OP187 | Turn off card-prod keying optimization |
| OP188 | Print QEP's as new better subplans are discovered |
| OP189 | Assume some correlation between attributes (on proj-rest) so that
selectivities do not become small as rapidly as normal |
| OP190 | Use old 6.4 join cardinality estimation formulae |
| OP191 | Do not assume some correlation between attributes when doing multi
equivalence class boolean factors (so that estimates tend to get small
more rapidly in this case) |
| OP192 | Revert to pre-6.4 keying estimates |
| OP194 | Disable heuristic that tries to guess at useful indexes for complex
queries (more than 4 range vars), and tries to look at those plans first |
| OP195 | Enables "global base array" compilation, which reduces data movement in the compiled query plan. (On by default from 9.1.0 on) |
| OP196 | Use 6.4 flattening algorithms instead of new ones |
| OP197 | Look at all plans regardless of cost estimate, typically used in
conjunction with OP255 |
| OP199 | Print QEP without cost estimates |
| OP201 | Change stats/nostats estimate from join-histo-to-itself, to use instead a
2-cell histo with min/max for data type. |
| OP202 | Turn on union view optimization for simple aggregates
This was meant for STAR to push aggregate processing lower into the LDB's
to reduce row traffic, but is apparently not STAR specific |
| OP203 | Display stuff (Tups, Pages, D, C) as float in QEP instead of int |
| OP205 | Same as config parameter opf_stats_nostats_max |
| OP206 | single-query disable of opf_stats_nostats_max
(see Doug Inkster's c.d.i posting of 22-Dec-00) |
| OP207 | Print extra stuff (cost numbers, flags) when doing QEP
Requires SET QEP or OP145 |
| OP209 | Dump enumeration memory structures, useful in greedy enumeration debugging |
| OP210 | Enable extended CO-tree dump, more than SET QEP shows |
| OP211 | Use old reverse order when adding useful secondary indexes |
| OP247 nn | Force new (greedy) enumeration if more than NN tables+indexes
in a query |
| OP248 nn | Reduce K-join cost estimates by dividing by nn. |
| OP251 nn | Set DIO cost for scan (default is 1 DIO per 8 pages)
To be used for bug catching, not tuning. |
| OP253 nn | Adjust conversion factor used to make time units out of
cost units. 100 means use default, 150 means look for better plans
50% longer, etc. |
| OP254 num1 [num2] | Set Star data movement cost factors: num1 is cross-node
and num2 is LDB-to-LDB within node |
| OP255 num1 [num2] | timeout after plan #num1 and sub-plan #num2 evaluated |
|
|
| | PSF tracepoints <128 are global, >=128 are session.
Doesn't appear to be any global ones turned on normally... |
| PS129 | Same as SET PRINTQRY |
| PS131 | Trace grant checking (partially xDEBUG) |
| PS132 | Print messages about sharing a REPEATED query (or not) |
| PS133 | Trace grantable-procedure checking (some is xDEBUG) |
| PS144 | Print query tree after parse (partially xDEBUG) |
| PS145 | Print query tree after parse (different format) |
| PS148 | Trace REGISTER actions |
| PS150 | Allow "DROP SCHEMA" which is not officially supported |
| PS151 | Allow "DEFINE CURSOR" for repeated cursors |
| PS175 | Print constraint list at CREATE/ALTER verify time |
| PS176 | Generate dummy not-null constraints for all NOT NULL columns in
CREATE AS SELECT - some sort of ANSI compliance thing |
| PS201 | Turns off error indication from "duplicate key on insert/update".
Causes instead 0 rowcount to be returned with no error. Was
originally put in for CA-Masterpiece so that cursor would not
be closed. |
| PS202 | Equivalent to turning on psf_vch_prec in the config. This resolves
varchar/char exprs to varchar instead of char. |
| PS251 | Disallow single-quoted identifiers (a deprecated feature) |
|
|
| QE06 | Show RETURN value (from dbproc) |
| QE08 | Generate outer-join rule for WITH CHECK OPTION instead of subselect rule.
Also requires QE10. |
| QE09 | Show "execute immediate" text, e.g. from constraints |
| QE10 | Generate statement level rule for WITH CHECK OPTION instead of row level |
| QE11 NN | Run query to completion, but suppress all but the first NN rows of
output. (Designed for select query timing.)
Note: only available in very latest 3.0.3's and beyond. |
| QE51 | (STAR) Validate query plan |
| QE55 | (STAR) Trace STAR queries |
| QE56 | (STAR) Trace DDL |
| QE58 | (STAR) Trace QEF calls |
| QE59 | (STAR) Trace queries in error |
| QE60 | Suppress simple aggregate optimization (sagg -> origagg) |
| QE70 | Suppress parallel index building, do 'em one at a time |
| QE90 | Show diff between estimated and actual tuples, disk, cpu at each
query execution node (best used with set qep) |
| QE91 | Print a short execution time summary (cpu, dio, et) |
| QE92 | use old-style insertion sort for QEF.
The old sort eliminated dups better than the heapsort, and may work
better for randomized but very heavily duplicated input. Or not... |
| QE93 | Print hash-join statistics summary |
| QE94 | (reserved: detailed hash join/agg execution info to DBMS log, for debugging) |
| QE95 | Trace exchange node operations (parallel query) |
| QE96 | Trace CUT buffer statistics for exchange nodes |
| QE97 | Detailed (and voluminous) CUT trace for parallel query |
| QE99 | Watch rows move thru the QEP (also needs OP173) |
| QE1nn | Watch rows emerge from node NN of the QEP (also needs OP173) |
|
|
| QS501 | Show QSF pool memleft, total, %used |
| QS502 | Show QSF pool objects & buckets summary |
| QS503 | Show QSF hash buckets |
| QS504 | Show QSF sessions |
| QS505 | Show LRU queue info |
| QS506 | Invalidate the QSF pool & procedure cache |
| QS507 | Show largest QSF pool allocated/requested |
| QS510 | Outputs QSF ULM map to II_DBMS_LOG
(works in 2.6 SP1, may be debug-only in some versions) |
|
|
| RD01 | Invalidate relation cache |
| RD02 | Invalidate relation & QTREE cache |
| RD03 | Invalidate LDB cache (STAR) |
| RD04 | All of the above |
| RD05 | Invalidate defaults cache |
| RD10 | Invalidate everything in RDF |
| RD11 | RDF cache usage summary, to II_DBMS_LOG |
| RD12 | RDF cache stats, same as shutdown summary, to II_DBMS_LOG |
| RD13 | Run a memory consistency check and dump to II_DBMS_LOG |
| RD20 | Don't look for synonyms |
| RD21 | (STAR) Don't update iidd_ddb_tableinfo timestamp |
| RD22 | Compute and use checksums to detect RDF memory corruption |
| RD23 | (STAR) Print RDF queries to LDB |
|
|
| SC0 | Print query text - use SET PRINTQRY |
| SC1 | Trace unusual/error SCE events |
| SC2 | Print user alert events - use SET PRINTDBEVENTS |
| SC3 | Log user alert events - use SET LOGDBEVENTS |
| SC4 | Log SCE requests (from QEF) |
| SC5 | Trace alert notifications to terminal |
| SC6 | Trace alert state transitions to terminal |
| SC902 | Write error messages to trace log: all errmsgs
including user level messages, to errlog
too, server-wide. |
| SC903 | Send all logged messages to the client
(the "other half" of sc902, send errlog msgs to user) |
| SC904 | Trace memory allocations to trace file |
| SC906 | Log all session startup and shutdown |
| SC907 | Allocate guard pages around each server memory expansion |
| SC908 | Crash the server (my, how useful) |
| SC909 | Dump server statistics to dbms log |
| SC910 | Suppress trace messages to client |
| SC911 | Dump server memory info to dbms log |
| SC912 | (temporary?) Disable return of byref results |
| SC914 | Start a replicator qman thread,
Useful when rep config error has shut it down. |
| SC921 | Dump local server event memory |
| SC922 | Dump cross server event memory |
| SC923 NN | Alter SCE event processing, nn = 1000 for help |
| SC924 NN | Log query causing errors to error log; to log for a specific
error number, use optional decimal NN. |
| SC1000 | SCF trace point help |