1 – FLAGS
Specifies whether or not a database system debug flag is set.
The following table shows the available keywords that can be
specified.
Table 1-7 Debug Flag Keywords
Debug
Flags
Keyword Equivalent Comment
ALTERNATE_ None The optimizer discards literal values
OUTLINE_ID when producing an outline identifer.
AUTO_INDEX Xq Allows CREATE TABLE and ALTER TABLE
to create indices for any PRIMARY
KEY, FOREIGN KEY or UNIQUE constraint
added to the table.
AUTO_OVERRIDE None Allows a user with the DBADM
(administrator) privilege to insert
or update a column defined as
AUTOMATIC.
BITMAPPED_SCAN None Enables use of in-memory compressed
DBkey bitmaps for index AND and OR
operations in the dynamic optimizer.
BLR B Displays the binary language (BLR)
representation request for the query
CARDINALITY K Shows cardinality updates
CARTESIAN_LIMIT None Limits the number of small tables
that are allowed to be placed
anywhere in the join order.
CHRONO_FLAG(n) Xc Forces timestamp-before-dump display.
The value of n can be 0, 1, or 2, or
n can be omitted. CHRONO_FLAG(0) and
NOCHRONO_FLAG are equivalent. If you
specify CHRONO_FLAG but omit n, the
default is CHRONO_FLAG(1). CHRONO_
FLAG(1) enables an additional trace
message that includes the attach
number and the current time. CHRONO_
FLAG(2) enables an additional trace
message that includes the attach
number and the current date and time.
If you supply a value for n that is
greater than 2, it is ignored, and a
value of 1 is used.
CONTROL_BITS Bc Displays a decoding of the BLR$K_
CONTROL_BITS semantic flags when used
with the BLR keyword.
COSTING Oc Displays traces on optimizer costing.
COUNT_SCAN None Enables count scan optimization
on sorted ranked indexes, where
the optimizer will use cardinality
information from the sorted ranked
index to determine the count of rows
that satisfy the query.
CURSOR_STATS Og Displays general cursor statistics
for the optimizer.
DATABASE_ P Displays the database parameter
PARAMETERS buffer during ATTACH, CREATE
DATABASE, ALTER DATABASE, IMPORT
DATABASE, and DISCONNECT statements.
DDL_BLR D Displays the binary language (BLR)
representation of expressions
within data definitions, such as
the expression for a computed column
within a table definition.
DETAIL_LEVEL None A debug flag used with other debug
flags to enable additional detailed
information in the debug output. The
DETAIL_LEVEL keyword can be followed
by a numeric value in parentheses.
For those debug flags that support
it, this indicates the degree of
aditional detail to be shown.
ESTIMATES O Displays the optimizer estimates.
EXECUTION E Displays an execution trace from the
dynamic optimizer. For a sequential
retrieval from a table that is
strictly partitioned, this includes
a count and a list of the selected
partitions each time the query
executes.
The EXECUTION keyword can be
followed by a numeric value in
parentheses. This represents the
number of lines to display before
stopping the execution trace for
the query execution. There can be no
spaces between the keyword and the
parameter. The default is 100.
IGNORE_OUTLINE None Ignores outlines defined in the
database. The IGNORE_OUTLINE keyword
has the same action as setting the
RDMS$BIND_OUTLINE_FLAGS logical name
to 1.
INDEX_COLUMN_ None Enables leading index columns as
GROUP workload column groups. This may
increase solution cardinality
accuracy.
INDEX_DEFER_ Xb When this flag is set and an index
ROOT is created, the index root node is
created in the database only when
there is data in the table. If the
table is empty, creation of the index
root node is deferred until rows are
inserted into the table.
INDEX_ Si Displays index partitioning
PARTITIONS information as part of a dynamic
execution trace.
INDEX_STATS Ai Enables debug flags output for the
progress of an ALTER, CREATE, or DROP
INDEX statement.
INTERNALS I Enables debug flags output for
internal queries such as constraints
and triggers. It can be used in
conjunction with other keywords such
as STRATEGY, BLR, and EXECUTION.
ITEM_LIST H Displays item list information passed
in for the database queries and as
compile-time query options
LAREA_READY Xr This flag can be used to investigate
table and index locking behavior.
This flag is disabled by default.
MAX_RECURSION None Sets the maximum number of recursions
that can be performed when executing
a match strategy. This prevents
excessive recursion in the processing
of the match strategy. The default
value is 100. The equivalent logical
name is RDMS$BIND_MAX_RECURSION.
MAX_SOLUTION None Enables maximum search space for
possible retrieval solutions. If
enabled, the optimizer will try
more solutions based on each leading
segment of the index, and thus may
create more solutions than before,
but may find more efficient solutions
applying multiple segments in index
retrieval. The equivalent logical
name is RDMS$DISABLE_MAX_SOLUTION.
Default is enabled.
MAX_STABILITY None Enables maximum stability; the
dynamic optimizer is not allowed.
The MAX_STABILITY keyword has the
same action as the RDMS$MAX_STABILITY
logical name.
MBLR M Displays the metadata binary language
representation request for the data
definition language statement
MODE(n) Allows you to specify which query
outline should be used by specifying
the mode value of that query outline.
The value of n can be any positive
or negative integer, or n can be
omitted. If you specify MODE but
omit n, the default is MODE(1). If
you specify MODE(0) or NOMODE, it
disables the display of the mode
in the SHOW FLAGS statement output.
MODE(0) is the default for Oracle Rdb
generated outlines.
NONE Not Used to turn off all currently
Applicable defined keywords. Equivalent to SET
NOFLAGS.
OBLR So Displays query outline in Binary
Language Representation (BLR).
OLD_COST_MODEL None Enables the old cost model. The
OLD_COST_MODEL keyword has the same
action as the RDMS$USE_OLD_COST_MODEL
logical name.
OPTIMIZATION_ None Used to change the default
LEVEL optimization level for a query.
If the query explicitly uses the
OPTIMIZE FOR clause, or is compiled
within an environment which overrides
the default using a method such
as SET OPTIMIZATION LEVEL, then no
change will occur. If the query uses
the default optimization level, then
the optimization will be modified by
this flag. With no option specified
or an empty options list, this will
default to TOTAL TIME. The flag
NOOPTIMIZATION_LEVEL will revert
to the default Rdb behavior.
OPTIMIZATION_ None Sets FAST FIRST as the default
LEVEL(FAST_ optimization level for queries in
FIRST) all sessions.
OPTIMIZATION_ None Sets TOTAL TIME as the default
LEVEL(TOTAL_ optimization level for queries in
TIME) all sessions.
OUTLINE Ss Displays query outline for this
query (can be used without STRATEGY
keyword)
PREFIX Bn Used with BLR keyword to inhibit
offset numbering and other formatting
of binary language representation
display.
REBUILD_SPAM_ None The flag REBUILD_SPAM_PAGES is
PAGES for use in conjunction with the
DDL commands ALTER TABLE, ALTER
STORAGE MAP, and ALTER INDEX. When
changing the row length or THRESHOLDS
clause for a table or index, the
corresponding SPAM pages for the
logical area may require rebuilding.
By default, these DDL commands
update the AIP and set a flag to
indicate that the SPAM pages should
be rebuilt. However, this flag may be
set prior to executing a COMMIT for
the transaction and the rebuild will
take place within this transaction.
Use SET FLAGS 'NOREBUILD_SPAM_PAGES'
to negate this flag.
REWRITE None When no parameters are provided,
all query rewrite optimizations are
enabled.
NOREWRITE None When no parameters are provided,
all query rewrite optimizations are
disabled.
REWRITE None Specifying the CONTAINING keyword
(CONTAINING) will enable only the CONTAINING
predicate rewrite optimization.
NOREWRITE None Specifying the CONTAINING keyword
(CONTAINING) will disable only the CONTAINING
predicate rewrite optimization.
REWRITE(LIKE) None Specifying the LIKE keyword will
enable only the LIKE predicate
rewrite optimization.
NOREWRITE(LIKE) None Specifying the LIKE keyword will
disable only the LIKE predicate
rewrite optimization.
REWRITE None Specifying the STARTING_WITH keyword
(STARTING_ will enable only the STARTING WITH
WITH) predicate rewrite optimization.
NOREWRITE None Specifying the STARTING_WITH keyword
(STARTING_ will disable only the STARTING WITH
WITH) predicate rewrite optimization.
REQUEST_NAMES Sn Displays the names of user requests,
triggers, and constraints
REVERSE_SCAN None Enables the reverse index scan
strategy. The NOREVERSE_SCAN
keyword has the same action as the
RDMS$DISABLE_REVERSE_SCAN logical
name.
SCROLL_ L Disables scrolling for old-style LIST
EMULATION OF BYTE VARYING (segmented string)
format. The SCROLL_EMULATION flag
has the same action as setting the
RDMS$DIAG_FLAGS logical name to L.
SELECTIVITY None Refers to the methods by which the
static optimizer estimates predicate
selectivity. This flag takes a
numeric value in parentheses from
0 to 3.
0 = standard (non-aggressive, non-
sampled) selectivity
1 = aggressive + non-sampled
selectivity
2 = sampled + non-aggressive
selectivity
3 = sampled + aggressive selectivity.
By default the flag is disabled,
which is the equivalent of setting
its value to 0.
SEQ_CACHE(n) None Adjusts the sequence cache size
for the process issuing the SET
FLAGS statement. The value n must
be a numeric value greater than
2. (Specifying a value of 1 is
equivalent to specifying NOSEQ_
CACHE.) Use SEQ_CACHE to override
the CACHE setting for all sequences
subsequently referenced by the
application. The new cache size
does not affect any sequence that
has already been referenced, or any
sequence defined as NOCACHE.
SOLUTIONS OsS Displays traces on optimizer
solutions.
SORTKEY_EXT S Reports if ORDER BY (or SORTED BY) is
referencing only external (constant)
value. The SORTKEY_EXT flag has the
same action as setting the RDMS$DIAG_
FLAGS logical name to S.
SORT_STATISTICS R Displays sort statistics during
execution.
STOMAP_STATS As Displays the processing of storage
maps for any tables that refer to the
dropped storage area. The output is
prefixed with "~As".
STRATEGY S Shows the optimizer strategy. If
a table is strictly partitioned,
the text "(partitioned scan#nn)"
appears after the table name, where
nn indicates the leaf number for a
sequential scan (there may be several
within a single query).
TEST_SYSTEM None This flag is used by the Oracle Rdb
testing environment to modify the
output of various functions, trace
and debugging displays. It is used
to eliminate data in test output
that would normally cause differences
between test executions.
TRACE Xt Enables output from TRACE statement
TRANSACTION_ T Displays the transaction parameter
PARAMETERS buffer during SET TRANSACTION,
COMMIT, and ROLLBACK and during
stored procedure compilation
TRANSITIVITY None Enables transitivity between
selections and join predicates.
The NOTRANSITIVITY keyword has the
same action as the RDMS$DISABLE_
TRANSITIVITY logical name.
VALIDATE_ None Enables revalidation of an
ROUTINE invalidated stored procedure or
stored function. The VALIDATE_ROUTINE
keyword has the same action as the
RDMS$VALIDATE_ROUTINE logical name.
VARIANCE_DOF(n) None Sets the default degree of freedom
(DOF) for calculation of the mean
(average) in small samples (instead
of using the VARIANCE function). Only
the values 0 and 1 are allowed.
WARN_DDL Xd Sometimes legal data definitions
can have side effects, this flag
allows these warning to be enabled
and disabled. This flag is is enabled
by default, with the exception for
when attached by PATHNAME. The data
definition statement still succeeds
even with the reported warnings. The
database administrator may choose to
rollback the statement based on this
feedback.
WARN_INVALID Xw Reports invalidated objects during
the ALTER INDEX, DROP INDEX, DROP
TABLE, and DROP MODULE statements.
WATCH_CALL Xa Traces the execution of queries,
triggers and stored functions and
procedures. The output includes
the name of the trigger, function
or procedure or "unnamed" for an
anonymous query. In most cases a
query can be named using the OPTIMIZE
AS clause. It also includes the value
of CURRENT_USER during the execution
of that routine. CURRENT_USER may be
inherited from any module that uses
the AUTHORIZATION clause.
WATCH_OPEN Xo Traces all queries executed on the
database. This may include SQL
runtime system queries to lookup
table names, etc as well as queries
executed by the application. The
output includes the 32 digit hex
identifier, the same as used by the
CREATE OUTLINE statement. This value
uniquely identifies the query being
executed.
If a query is a stored routine
(function or procedure) then the
notation "(stored)" is appended, if
the query is named then it will be
classified as "(query)", otherwise it
will be designated as "(unnamed)".
ZIGZAG_MATCH None Enables zigzag key skip on both
outer and inner match loops. When
you specify the ZIGZAG_MATCH keyword
with the NOZIGZAG_OUTER keyword, it
disables zigzag key skip on the outer
loop (and has the same action as
setting the RDMS$DISABLE_ZIGZAG_MATCH
logical name to 1). The NOZIGZAG_
MATCH keyword disables zigzag key
skip on both outer and inner match
loops (and has the same action as
setting the RDMS$DISABLE_ZIGZAG_MATCH
logical name to 2).
ZIGZAG_OUTER None Enables zigzag key skip on the outer
loop. See the entry for ZIGZAG_MATCH
for information on the action taken
when you specify ZIGZAG_OUTER and
ZIGZAG_MATCH together.
2 – NOFLAGS
The SET NOFLAGS statement disables all currently enabled flags.
It is equivalent to SET FLAGS 'NONE'. NOFLAGS is only permitted
in Interactive SQL.
3 – ON ALIAS alias-name
Using the ON ALIAS clause allows the database administrator to
set flags on just one database alias instead of on all currently
attached databases. Use the name of an alias as declared by the
ATTACH or CONNECT statement. If no ALIAS clause is used then the
alias name will default to RDB$DBHANDLE.