Return to KB Computer Home

Attention!
Read This!

Trace points are Officially Unsupported by Actian Corp. While most trace points are relatively harmless, and output gibberish useful only to Ingres development and Tech Support, some trace points are actively dangerous. In addition, Actian reserves the right to change trace points at any time, and a previously innocuous trace point may change to a dangerous one.

Do not use trace points 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 trace points are "well known", relatively useful, and are unlikely to change. They are just as officially unsupported as any other trace point, 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" trace points are:

DM32, DM420, DM421, DM801, DM1305, DM1440
OP165, OP173, OP188, OP255
QE88, QE90, QE99
QS501, QS506
RD10, RD11
SC906

and if you want a trace point 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 10.2. Older Ingres versions may have different meanings for some trace points; I've made a few feeble attempts to indicate same, but no guarantees. If you can demonstrate a trace point that I have omitted, or mis-described, I would be interested in hearing about it.

Note: some trace points are equivalents of SET statements. Not all such trace points have been included. (E.g. QE5 is "SET NORULES".) I have also omitted a few trace points 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 SXF
















No interesting AD (ADF) trace points.


DMF trace points from 01 to 99 are session specific. Everything over 99 is server wide, unless noted otherwise.
DM10equivalent to SET IO_TRACE
DM11equivalent to SET LOG_TRACE
DM12When used with DM11, turns on LSN display in log trace
DM32Turn off Replicator data capturing for the current session
DM33Row-lock protocol double-checking for session
DM34Output page size/page type xref
DM35Produce E_DM9066_LOCK_BUSY messages with information about blocked resources with TIMEOUT = NOWAIT.
DM36 nnSet DMF parallelism to nn threads for MODIFY. Equivalent to degree_of_parallelism config parameter.
DM37Turn on double-check for row lock during delete or update when using MVCC.
DM101Dump DMF memory to II_DBMS_LOG
DM102Turn off Replicator for the current database
DM103Fill DMF memory with pad character when allocated (detects missing initialization)
DM104Fill DMF memory with pad character when freed (detect dangling reference)
DM105Toggle DMF memory stats collection by type
DM302Turn on buffer manager lock tracing
DM303Send CSP request to enter debugger
DM304Lock request and list dump when escalation deadlock detected
DM305 to 308like log_esc_xx_yy config settings
DM309Turn on locklist dump (into iircp.log) when deadlock detected
DM405Trace MVCC consistent-read activity
DM406Trace MVCC CR activity from journals only
DM415Turn on buffer manager consistency checks
DM416DMF Memory dump if DM415 consistency check detects error
DM418Error return after 9C01 error
DM419DMF memory dump after 9C01 error
DM420Output DMF buffer cache stats snapshot to II_DBMS_LOG
DM421Flush and invalidate DMF buffer cache
DM422Invalidate unmodified pages from DMF buffer cache
DM426Don't do group reads
DM427 baseid pagenoPurge single (base table) page from cache
DM428 baseidPurge table's pages from cache (and its indexes too)
DM429Enable transaction queue consistency checks
DM430Print stuff if DM429 detects a loop
DM431Disable page checksumming
Warning! DM431 could make your database permanently unusable by a regular server!
DM432Make checksum error a warning/trace message
DM434Print extra control block stuff if DM415 detects error
DM435Ignore cache priorities (use TCB priority instead)
DM483Fix only one system catalog page at a time
DM501Log simple sort statistics, DMF sorts only
DM502Alternate work file striping method (unsure of details)
DM503Disable parallel sort
DM504-506Force 1, 2, 3 sort threads
DM508-510Force sort exchange buf size to 500, 5000, 25000
DM511Dump statistics after a MODIFY
DM512Prevent the use of online (concurrent_updates) MODIFY
DM513Log a lot of online MODIFY diagnostic and trace info
DM514Brief sleep at strategic points during online MODIFY while sidefile enabled (to allow concurrent updates to occur)
DM515Brief sleep during online MODIFY readlock-nolock apply (to allow concurrent updates to occur)
DM601-DM610Log BTREE Get, Del, Put, Replace, Split, Merge, DM607 notused, Allocate, Search, Build debug info to DBMS log. Can be massive
DM611Log BTREE allocate-entry-on-page
DM612Added BTREE edit checking e.g. in allocate-entry
DM612Additional output in BTREE build trace (maybe DM612 got used twice?)
DM613BTREE build call logging
DM614Log btree page if corruption detected, server wide
DM615 baseId indexIdDump btree/rtree base table or index to II_DBMS_LOG
DM616BTREE CREATE INDEX: force index compression
DM617Skip freeing of empty BTREE overflow chain
DM618Misc additional BTREE tracing: positioning, incr/decr keys, etc
DM631-635, 649As DM60x but for RTREE
DM650Trace page free list operations
DM651Build-freelist tracing
DM713 baseidPurge table control block (and all partition and index TCB's)
DM714 pagesizeSet default page size for server
DM716Force row level locking if possible
DM717Force read-committed isolation level
DM718Force repeatable read isolation level
DM719Force BTREE reposition on first GET rather than believing position info
DM720gateway call trace
DM720Also, force continue instead of rollback in dup checking
DM721 pagetypeSet default page type for server
DM722Add blob coupon validation checks for INSERT and UPDATE
DM723Debug check for proper lock protocol before logging btree-update log records
DM801DMF will skip bad rows (eg to allow modify of corrupted table)
DM802DMF will return garbage for bad rows (eg to allow deleting them)
DM803Report but ignore DMF errors while handling blobs
DM804Dump SAGG (simple aggregate) stuff
DM805Dump AES encrypt buffers to DBMS log
DM806Dump AES decrypt buffers to DBMS log
DM902Compress "old" row in REP log records
DM903Log beginning/end of rollback in II_DBMS_LOG
DM1201Call operation (see DM1205)
DM1202Call return status
DM1203Call input parameters
DM1204Call output parameters
DM1205Turns on dmd_call tracing (other DM12xx things)
Also causes hard spin if DMF parameter error (to allow e.g. pstack)
DM1210Call timing enable (VMS only)
DM1211Call timing display (only really useful on VMS?)
DM1220Perform memory check at dmd_call
NOTE: For rcp, acp, etc set DM13xx via II_DMF_TRACE=nnn,nnn,nnn ...
DM1301Log fast-commit thread stats
DM1302Log write-behind thread stats
DM1303Log consistency point EOF stats
DM1305Force a consistency point
DM1306Set verbosity in the rcp
DM1309Even more verbosity in rcp, needs DM1306
DM1310Trace page header too if DM1315
DM1311Trace page contents too if DM1315
DM1312Turn OFF log file dumping if inconsistency error in rcp
DM1313Triggers verbosity in the archiver
DM1314Force an archiver cycle
DM1315Turn on extra tracing in redo/journal
DM1316Inhibit dmd_check memory dump
DM1320Enable parallel secondary index updating (experimental)
DM1419Crash the logging system
DM1434Force log buffers to disk (might be useful before forced crash)
DM1440Show work (ie, sort) locations
DM1442Toggle optimized TX log writes
DM1443Toggle optimized TX log write tracing (to DBMS log)
DM1444 nnSet last table-id assigned in the DB config file.


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)
OP130Turn off nonessential consistency checks, which at present seem to be just the "histogram increasing" checks
OP131Disable join-time partition qualification
OP132SET NOFLATTEN
OP133Flatten 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.
OP134Turn off NOT IN/NOT EXISTS -> outer join transformation
OP144Write basic OPF stats to DBMS log after each query
OP145Appears to be the same as SET QEP
OP146Dump query trees at start of aggregate processing
OP147Trace aggregate combining processing
OP148Suppress presort of K/T-join outers
OP149Show overall QP tree outline
OP150Detailed QP dump: QP plus result of OPC code gen
OP151Turn off query compilation, useful to see if crash-problem is in OPF or OPC
OP152like SET NOTIMEOUT
OP153An abbreviated joinop table output (shorter than OP156)
OP156Print all joinop tables
OP157Apparently does a couple things: prints outer join "stuff" during/after outer join processing; prints map of equivalence classes for which histograms are needed
OP158Print parser stuff for SE join subquery
OP159Send warning messages to the user instead of suppressing them
OP160like SET OPTIMIZEONLY
OP161Dump data structures from OPF to OPC
OP162Generic "new feature" switch.
In 10.0, disables "suckrestrict" heuristic that pulls restrictions into subqueries.
OP163Prevents use of histograms in join selectivity computation, using an IBM heuristic instead
OP164Dump distributed query text for each CO tree node (STAR only)
OP165Don't use any statistics
OP166Enable a fake gateway optimizer
OP167Don't pick key joins (use merge or hash joins instead)
OP169Treat all STAR queries as multi-site queries
OP170Modified parse tree display (for QEP, also happens at "interesting" points like NOT EXISTS transform, CNF normalization, etc)
OP171Send constants as ~V (for STAR)
OP172Suppress use of RTREE indexes
OP173Enable the building of the print-row CXs (for QE99, QE1nn tracing)
OP174change behaviour of control C to exit with best query plan
OP175Check text segments to make sure everything is printable (STAR)
OP176Simply continue from float exceptions in the optimizer, don't recover
OP177Attempt to always use keying strategies (doesn't work too well)
OP178Default the "complete" flag to TRUE
OP180Look 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.
OP181Look at reduced search space given heuristic estimates of index usage; tries to avoid full enumeration.
OP182Force old CNF algorithm (transform everything to CNF), turns off MS-Access transformation
OP183Don't do the CNF transformation (overrides everything else)
OP187Turn off card-prod keying optimization
OP188Print QEP's as new better subplans are discovered. Related to OP215 and OP216.
OP189Assume some correlation between attributes (on proj-rest) so that selectivities do not become small as rapidly as normal
OP190Use old 6.4 join cardinality estimation formulae
OP191Do 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)
OP192Revert to pre-6.4 keying estimates
OP194Disable heuristic that tries to guess at useful indexes for complex queries (more than 4 range vars), and tries to look at those plans first
OP195Disables "global base array" compilation, which reduces data movement in the compiled query plan.
(Prior to 9.1.0, op195 ENables global basearrays.)
OP196Use 6.4 flattening algorithms instead of new ones
OP197Look at all plans regardless of cost estimate, typically used in conjunction with OP255
OP199Print QEP without cost estimates
OP201Change stats/nostats estimate from join-histo-to-itself, to use instead a 2-cell histo with min/max for data type.
OP202Turn 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
OP203Display stuff (Tups, Pages, D, C) as float in QEP instead of int
OP205Same as config parameter opf_stats_nostats_max
OP206single-query disable of opf_stats_nostats_max (see Doug Inkster's c.d.i posting of 22-Dec-00)
OP207Print generated X100 algebra (Vector queries only; does not display algebra that is generated outside of OPF
OP209Dump enumeration memory structures, useful in greedy enumeration debugging
OP210Enable extended CO-tree dump, more than SET QEP shows
OP211Use old reverse order when adding useful secondary indexes
OP212Turn on 'table_auto_structure' dynamically
OP213Report attributes in WHERE and ON clauses that are missing statistics
OP214Use with OP170 for much briefer op146 style tree output
OP215Like OP188 but prints fragments which are rejected for non-cost reasons.
OP216Like OP188 but only prints best-so-far complete QEP's, not fragments
OP218Ignore opf_pq_threshold when placing EXCH nodes. (A built-in minimum threshold of 50, MINEXCH, is still enforced.)
OP219Disables aggregate pushing (moving aggs before joins)
OP241 n1 n2Various opa_analyze_views dot traces
OP246 nnGreedy enumeration cost fudge by NN percent
OP247 nnForce new (greedy) enumeration if more than NN tables+indexes in a query
OP248 nnReduce 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 nnAdjust 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 trace points <128 are global, >=128 are session. Doesn't appear to be any global ones turned on normally...
PS129Same as SET PRINTQRY
PS131Trace grant checking (partially xDEBUG)
PS132Print messages about sharing a REPEATED query (or not)
PS133Trace grantable-procedure checking (some is xDEBUG)
PS144Print query tree after parse (partially xDEBUG)
PS145Print query tree after parse (different format)
PS147Unicode normalization diagnostics in the SQL scanner
PS148Trace REGISTER actions
PS150Allow "DROP SCHEMA" which is not officially supported
PS151Enable 6.4 ambiguous-replace compatibility, like the ambig_replace_64compat parameter.
Also enables "define query open cursor" for repeated cursors, an unsupported feature of old vintage and uncertain status.
PS152Trace some cache_dynamic processing
PS175Print constraint list at CREATE/ALTER verify time
PS176Generate dummy not-null constraints for all NOT NULL columns in CREATE AS SELECT - some sort of ANSI compliance thing
PS201Turns 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.
PS202Equivalent to turning on psf_vch_prec in the config. This resolves varchar/char exprs to varchar instead of char.
PS203Dump PSF memory pool to DBMS log
PS251Disallow single-quoted identifiers (a deprecated feature)


QE06Show RETURN value (from dbproc)
QE08Generate outer-join rule for WITH CHECK OPTION instead of subselect rule. Also requires QE10.
QE09Show "execute immediate" text, e.g. from constraints
QE10Generate statement level rule for WITH CHECK OPTION instead of row level
QE11 NNRun query to completion, but suppress all but the first NN rows of output. (Designed for select query timing.)
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
QE60Suppress simple aggregate optimization (sagg -> origagg)
QE70Suppress parallel index building, do 'em one at a time
QE88Abbreviated QE90 showing actual and estimate rowcount at each QP node
QE89Hash join/agg debug output (to DBMS log) (includes qeehash reservation trace as well as execution time trace)
QE90Show diff between estimated and actual tuples, disk, cpu at each query execution node (best used with set qep or with op149)
QE91Print a short execution time summary (cpu, dio, et)
QE92use 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...
QE93Print hash join/agg statistics summary
QE94Don't sort in QEF, dump straight to DMF sorter
QE95Trace exchange node operations (parallel query)
QE96Trace CUT buffer statistics for exchange nodes
QE97Detailed (and voluminous) CUT trace for parallel query
QE99Watch rows move thru the QP (also needs OP173)
QE1nnWatch rows emerge from node NN of the QEP (also needs OP173)


QS501Show QSF pool memleft, total, %used
QS502Show QSF pool objects & buckets summary
QS503Show QSF hash buckets
QS504Show QSF sessions
QS505Show LRU queue info
QS506Invalidate the QSF pool & procedure cache
QS507Show largest QSF pool allocated/requested
QS508Dump LRU queue to DBMS log
QS510Outputs QSF ULM map to II_DBMS_LOG
QS511Outputs QSF object and ULM pool dump to II_DBMS_LOG


RD01Invalidate relation cache
RD02Invalidate relation & QTREE cache
RD03Invalidate LDB cache (STAR)
RD04All of the above
RD05Invalidate defaults cache
RD10Invalidate everything in RDF
RD11RDF cache usage summary, to II_DBMS_LOG
RD12RDF cache stats, same as shutdown summary, to II_DBMS_LOG
RD20Don't look for synonyms
RD21(STAR) Don't update iidd_ddb_tableinfo timestamp
RD22Compute and use checksums to detect RDF memory corruption
RD23(STAR) Print RDF queries to LDB


SC0Print query text - use SET PRINTQRY
SC1Trace unusual/error SCE events
SC2Print user alert events - use SET PRINTDBEVENTS
SC3Log user alert events - use SET LOGDBEVENTS
SC4 Log SCE requests (from QEF)
SC5Trace alert notifications to terminal
SC6 Trace alert state transitions to terminal
SC902Write error messages to trace log: all errmsgs including user level messages, to errlog too, server-wide.
SC903Send all logged messages to the client (the "other half" of sc902, send errlog msgs to user)
SC904Trace memory allocations to trace file
SC906Log all session startup and shutdown
SC907Allocate guard pages around each server memory expansion
SC908Crash the server (my, how useful)
SC909Dump server statistics to dbms log
SC910Suppress trace messages to client, send to DBMS log instead
SC911Dump server memory info to dbms log
SC914Start a replicator qman thread, Useful when rep config error has shut it down.
SC916 N MMemory diags to DBMS log: no args gives help.
SC916 1: Print sc0m pools
SC916 2: Print sc0m pools with audit values
SC916 3: List ULM pools and their index numbers
SC916 4 P: ULM Pool P map
SC916 5 P: ULM Pool P free/allocated chain dump
SC916 6 P: ULM Pool P run consistency check
SC921Dump local server event memory
SC922Dump cross server event memory
SC923 NNAlter SCE event processing, nn = 1000 for help
SC924 NNLog query causing errors to error log; to log for a specific error number, use optional decimal NN.
SC925 X [Y]Log queries running >X (and <Y) seconds
SC930 NN Query recording for playback, see Ingres wiki
SC1000SCF trace point help


SX2Dump SXF stats
SX3Dump SXF session info
SX5Dump SXF shared memory info


Return to KB Computer Home

Last updated: 10-Mar-2015