Example 1: Enabling and disabling database system debug flags
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX
SQL>
SQL> SET FLAGS 'TRACE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,TRACE
SQL>
SQL> SET FLAGS 'STRATEGY';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
STRATEGY,PREFIX,TRACE
SQL>
SQL> SET FLAGS 'NOTRACE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
STRATEGY,PREFIX
SQL>
SQL> SET NOFLAGS;
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX
SQL>
Example 2: Using the PREFIX keyword
SQL> ATTACH 'FILENAME mf_personnel';
SQL> --
SQL> -- Show that the PREFIX keyword is enabled by default
SQL> --
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX
SQL> --
SQL> -- Enable TRACE
SQL> --
SQL> SET FLAGS 'TRACE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,TRACE
SQL> --
SQL> -- Show that the prefix is displayed
SQL> --
SQL> BEGIN
cont> TRACE 'AAA';
cont> END;
~Xt: AAA
SQL> --
SQL> -- Turn off the prefix
SQL> --
SQL> SET FLAGS 'NOPREFIX';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
TRACE
SQL> --
SQL> -- Show that the prefix is no longer displayed
SQL> --
SQL> BEGIN
cont> TRACE 'AAA';
cont> END;
AAA
Example 3: Using Host Variables in Interactive SQL
The example also demonstrates using literal strings with multiple
options to enable and disable flags.
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX
SQL> -- declare a host variable to be used with SET FLAGS
SQL> declare :hv char(40);
SQL> -- assign a value to the variable
SQL> begin
cont> set :hv = 'strategy, outline';
cont> end;
SQL> -- use the host variable to enable or disable flags
SQL> set flags :hv;
SQL> show flags
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
STRATEGY,PREFIX,OUTLINE
SQL> -- use a string literal directly with the SET FLAGS statement
SQL> set flags 'noprefix,execution(10)';
SQL> show flags
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
STRATEGY,OUTLINE,EXECUTION(10)
Example 4: Using the MODE(n) Flag
SQL> SET FLAGS 'MODE(10), OUTLINE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,OUTLINE,MODE(10)
SQL> SELECT COUNT(*) FROM EMPLOYEES;
-- Rdb Generated Outline : 30-MAY-1997 16:35
create outline QO_B3F54F772CC05435_0000000A
id 'B3F54F772CC054350B2B454D95537995'
mode 10
as (
query (
-- For loop
subquery (
subquery (
EMPLOYEES 0 access path index EMP_EMPLOYEE_ID
)
)
)
)
compliance optional ;
100
1 row selected
Example 5: Using the WARN_INVALID Debug Flag
SQL> SET FLAGS 'WARN_INVALID';
SQL> SHOW FLAGS;
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,WARN_INVALID
SQL> -- warning because of dependencies
SQL> DROP TABLE T1 CASCADE;
~Xw: Routine "P3" marked invalid
~Xw: Routine "P2" marked invalid
~Xw: Routine "P1" marked invalid
SQL>
SQL> -- Create an outline that references an INDEX.
SQL> CREATE TABLE T1 (A INTEGER, B INTEGER);
SQL> CREATE INDEX I1 ON T1 (A);
SQL> CREATE OUTLINE QO1
cont> ID '19412AB61A7FE1FA6053F43F8F01EE6D'
cont> MODE 0
cont> AS (
cont> QUERY (
cont> SUBQUERY (
cont> T1 0 ACCESS PATH INDEX I1
cont> )
cont> )
cont> )
cont> COMPLIANCE OPTIONAL;
SQL>
SQL> -- Warning because of disabled index
SQL> ALTER INDEX I1
cont> MAINTENANCE IS DISABLED;
~Xw: Outline "QO1" marked invalid (index "I1" disabled)
SQL> SHOW OUTLINE QO1;
QO1
Object has been marked INVALID
Source:
CREATE OUTLINE QO1
ID '19412AB61A7FE1FA6053F43F8F01EE6D'
MODE 0
AS (
QUERY (
SUBQUERY (
T1 0 ACCESS PATH INDEX I1
)
)
)
COMPLIANCE OPTIONAL;
Example 6: Using the INTERNAL Keyword to Display Trigger Actions
SQL> -- The following code shows the strategy used by the trigger
SQL> -- actions on the AFTER DELETE trigger on EMPLOYEES
SQL> SET FLAGS 'STRATEGY, INTERNALS, REQUEST_NAMES';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
INTERNALS,STRATEGY,PREFIX,REQUEST_NAMES
SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = '00164';
~S: Trigger name EMPLOYEE_ID_CASCADE_DELETE
Get Temporary relation Retrieval by index of relation DEGREES
Index name DEG_EMP_ID [1:1]
~S: Trigger name EMPLOYEE_ID_CASCADE_DELETE
Get Temporary relation Retrieval by index of relation JOB_HISTORY
Index name JOB_HISTORY_HASH [1:1]
~S: Trigger name EMPLOYEE_ID_CASCADE_DELETE
Get Temporary relation Retrieval by index of relation SALARY_HISTORY
Index name SH_EMPLOYEE_ID [1:1]
~S: Trigger name EMPLOYEE_ID_CASCADE_DELETE
Conjunct Get Retrieval by index of relation DEPARTMENTS
Index name DEPARTMENTS_INDEX [0:0]
Temporary relation Get Retrieval by index of relation EMPLOYEES
Index name EMPLOYEES_HASH [1:1] Direct lookup
1 row deleted
Example 7: Using the INDEX_COLUMN_GROUP Keyword
SQL> -- The table STUDENTS has an index on the two columns
SQL> -- STU_NUM and COURSE_NUM. When the INDEX_COLUMN_GROUP
SQL> -- keyword is not set, the optimizer uses a fixed
SQL> -- proportion of the table cardinality based on the equality
SQL> -- with the STU_NUM column. In this example, 5134 rows are expected,
SQL> -- when in reality, only 9 are returned by the query.
SQL> CREATE INDEX STUDENT_NDX ON STUDENTS (STU_NUM,COURSE_NUM DESC);
SQL> --
SQL> SELECT STU_NUM FROM STUDENTS
cont> WHERE STU_NUM = 191270771
cont> ORDER BY OTHER_COLUMN;
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 4.5644922E+03
Cardinality of chosen solution 5.1342500E+03
~O: Physical statistics used
Sort
SortId# 7., # Keys 2
Item# 1, Dtype: 2, Order: 0, Off: 0, Len: 1
Item# 2, Dtype: 35, Order: 0, Off: 1, Len: 8
LRL: 32, NoDups:0, Blks:327, EqlKey:0, WkFls: 2
Leaf#01 BgrOnly STUDENTS Card=164296
BgrNdx1 STUDENT_NDX [1:1] Fan=14
191270771
191270771
191270771
191270771
191270771
191270771
191270771
191270771
SORT(9) SortId# 7, --------------------- Version: V5-000
Records Input: 9 Sorted: 9 Output: 0
LogRecLen Input: 32 Intern: 32 Output: 32
Nodes in SoTree: 5234 Init Dispersion Runs: 0
Max Merge Order: 0 Numb.of Merge passes: 0
Work File Alloc: 0
MBC for Input: 0 MBC for Output: 0
MBF for Input: 0 MBF for Output: 0
Big Allocated Chunk: 4606464 busy
191270771
9 rows selected
SQL> --
SQL> -- When you use the SET FLAGS statement to set the
SQL> -- INDEX_COLUMN_GROUP keyword, it activates the optimizer
SQL> -- to consider the index segment columns as a workload column
SQL> -- group, compute the statistics for duplicity factor and null
SQL> -- factor dynamically, and then apply them in estimating the
SQL> -- cardinality of the solution.
SQL> --
SQL> SET FLAGS 'INDEX_COLUMN_GROUP';
SQL> -- The following is the optimizer cost estimate and sort output trace
SQL> -- for the previous query with INDEX_COLUMN_GROUP enabled. The optimizer
SQL> -- now estimates a lower cardinality of about 8 rows.
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.8118614E+01
Cardinality of chosen solution 8.3961573E+00
~O: Workload and Physical statistics used
Sort
SortId# 2., # Keys 2
Item# 1, Dtype: 2, Order: 0, Off: 0, Len: 1
Item# 2, Dtype: 35, Order: 0, Off: 1, Len: 8
LRL: 32, NoDups:0, Blks:7, EqlKey:0, WkFls: 2
Leaf#01 BgrOnly STUDENTS Card=164296
BgrNdx1 STUDENT_NDX [1:1] Fan=14
191270771
191270771
191270771
191270771
191270771
191270771
191270771
191270771
SORT(2) SortId# 2, --------------------- Version: V5-000
Records Input: 9 Sorted: 9 Output: 0
LogRecLen Input: 32 Intern: 32 Output: 32
Nodes in SoTree: 114 Init Dispersion Runs: 0
Max Merge Order: 0 Numb.of Merge passes: 0
Work File Alloc: 0
MBC for Input: 0 MBC for Output: 0
MBF for Input: 0 MBF for Output: 0
Big Allocated Chunk: 87552 idle
191270771
9 rows selected
Example 8: Using the AUTO_OVERRIDE Keyword
SQL> -- Suppose that after year 2000 testing was performed on a
SQL> -- production system, the system date and time were not reset
SQL> -- to the correct date. This was not noticed until
SQL> -- after transactions for a full day had been stored. To
SQL> -- correct this problem, the database administrator overrides
SQL> -- the READ ONLY characteristic of the AUTOMATIC column and
SQL> -- adjusts the date and time.
SQL> SELECT * FROM ACCOUNTS
cont> WHERE LAST_UPDATE > DATE'2001-1-1';
ACCOUNT_NO LAST_NAME LAST_UPDATE CURRENT_BALANCE
NULL Smith 2001-06-02 100000.000
1 row selected
SQL> -- Attempts to fix the date and time fail because the
SQL> -- column is AUTOMATIC.
SQL> UPDATE ACCOUNTS
cont> SET LAST_UPDATE = LAST_UPDATE - INTERVAL'1' YEAR
cont> WHERE LAST_UPDATE > DATE'2000-1-1';
%RDB-E-READ_ONLY_FIELD, attempt to update the read-only field LAST_UPDATE
SQL> --
SQL> SET FLAGS 'AUTO_OVERRIDE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,AUTO_OVERRIDE
SQL>--
SQL> -- Fix the date and time.
SQL> UPDATE ACCOUNTS
cont> SET LAST_UPDATE = LAST_UPDATE - INTERVAL'1' YEAR
cont> WHERE LAST_UPDATE > DATE'2000-1-1';
1 row updated
SQL>
SQL> SELECT * FROM ACCOUNTS;
ACCOUNT_NO LAST_NAME LAST_UPDATE CURRENT_BALANCE
NULL Smith 1999-06-02 100000.000
1 row selected
SQL>
SQL> SET FLAGS 'NOAUTO_OVERRIDE';
Example 9: Using the AUTO_INDEX option
SQL> set dialect 'SQL92';
SQL> set flags 'AUTO_INDEX,INDEX_STATS';
SQL> create table PERSON
cont> (employee_id integer primary key,
cont> manager_id integer references PERSON (employee_id),
cont> last_name char(30),
cont> first_name char(30),
cont> unique (last_name, first_name));
~Ai create index "PERSON_PRIMARY_EMPLOYEE_ID"
~Ai larea length is 430
~Ai storage area (default) larea=57
~Ai create sorted index, ikey_len=5
Sort Get Retrieval sequentially of relation PERSON
~Ai create index partition, node=430 %fill=0
~Ai create index "PERSON_FOREIGN1"
~Ai larea length is 215
~Ai storage area is shared: larea=57
~Ai create sorted index, ikey_len=5
Sort Get Retrieval sequentially of relation PERSON
~Ai create index partition, node=0 %fill=0
~Ai create index "PERSON_UNIQUE1"
~Ai larea length is 215
~Ai storage area is shared: larea=57
~Ai create sorted index, ikey_len=62
Sort Get Retrieval sequentially of relation PERSON
~Ai create index partition, node=0 %fill=0
SQL>
SQL> show table (index) person
Information for table PERSON
Indexes on table PERSON:
PERSON_FOREIGN1 with column MANAGER_ID
Duplicates are allowed
Type is Sorted
Key suffix compression is DISABLED
PERSON_PRIMARY_EMPLOYEE_ID with column EMPLOYEE_ID
No Duplicates allowed
Type is Sorted
Key suffix compression is DISABLED
Node size 430
PERSON_UNIQUE1 with column LAST_NAME
and column FIRST_NAME
Duplicates are allowed
Type is Sorted
Key suffix compression is DISABLED
SQL>
Example 10: Using the WATCH_CALL option
This example shows the output of WATCH_CALL for an INSERT
statement which causes an AFTER INSERT trigger (AFTER_INSERT)
to be executed which calls an SQL function WRITE_TEXT to trace
the input data. It then traces a query named using OPTIMIZE AS
clause.
SQL> insert into SAMPLE_T values ('Fred');
~Xa: routine "(unnamed)", user=SMITH
~Xa: routine "AFTER_INSERT", user=SMITH
~Xa: routine "WRITE_TEXT", user=SMITH
~Xt: Fred
1 row inserted
SQL> select * from SAMPLE_T
cont> optimize as LOOKUP_SAMPLE_T;
~Xa: routine "LOOKUP_SAMPLE_T", user=SMITH
NEW_NAME
Fred
1 row selected
Example 11: Using the WATCH_OPEN option
This example shows the output of WATCH_OPEN for the same INSERT
statement as seen in example 10.
SQL> insert into SAMPLE_T values ('Fred');
~Xo: Start Request B667E51E3625026EB7FFF3F4D3A16DC3 (unnamed)
~Xo: Start Request A8568053FE5A1A0852A1BE83A884016F "AFTER_INSERT" (query)
~Xo: Start Request 08AE59062657299B4768F6C2DFB6928E "WRITE_TEXT" (stored)
~Xt: Fred
1 row inserted
SQL>
SQL> select * from SAMPLE_T
cont> optimize as LOOKUP_SAMPLE_T;
~Xo: Start Request F6025FAB1DD36B0DE0E52F3A9641BC5F "LOOKUP_SAMPLE_T" (query)
NEW_NAME
Fred
Fred
2 rows selected
Example 12: Using SET FLAGS from an application program
The SET FLAGS statement can be executed from Dynamic SQL using
one of two methods.
o The first method is immediate execution by passing a string
literal. The string literal argument to SET FLAGS requires
that the single quote marks be doubled for correct inclusion
in the string literal argument to EXECUTE IMMEDIATE.
o The second method is to pass the entire SET FLAGS statement in
a parameter to EXECUTE IMMEDIATE
exec sql
execute immediate 'set flags ''strategy''';
The entire SET FLAGS statement could be in a parameter to EXECUTE
IMMEDIATE
exec sql
execute immediate :set_flags_text;
If SET FLAGS is executed multiple times it can be prepared as
a dynamic statement (PREPARE) and then the statement name used
for multiple executions. The input marker (?) is substituted on
different calls to EXECUTE the previously prepared statement.
#include <string.h>
#include <sql_rdb_headers.h>
void main ()
{
int SQLCODE;
char myflags[40];
exec sql
prepare set_flags_stmt from 'set flags ?';
if (SQLCODE != 0)
sql_signal ();
strcpy (myflags, "transaction,item_list");
exec sql
execute set_flags_stmt using :myflags;
if (SQLCODE != 0)
sql_signal ();
exec sql
start transaction;
if (SQLCODE != 0)
sql_signal ();
strcpy (myflags, "notransaction,noitem_list");
exec sql
execute set_flags_stmt using :myflags;
if (SQLCODE != 0)
sql_signal ();
exec sql
rollback;
if (SQLCODE != 0)
sql_signal ();
}
Example 13: Using the CHRONO_FLAG option
SQL> set flags 'chrono_fla(2),transaction';
SQL> start transaction;
ATTACH #1, 29-NOV-2003 10:08:37.51
~T Compile transaction (1) on db: 1
~T Transaction Parameter Block: (len=2)
0000 (00000) TPB$K_VERSION = 1
0001 (00001) TPB$K_WRITE (read write)
ATTACH #1, 29-NOV-2003 10:08:37.58
~T Start_transaction (1) on db: 1, db count=1
SQL> rollback;
ATTACH #1, 29-NOV-2003 10:08:46.74
~T Rollback_transaction (1) on db: 1
SQL> rollback;
ATTACH #1, 29-NOV-2003 10:08:46.74
~T Rollback_transaction (1) on db: 1
SQL>
Example 14: Using the REBUILD_SPAM_PAGES option
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.
The following example shows a simple change to the EMPLOYEES
table (mapped in this example to set of UNIFORM areas). The flag
STOMAP_STATS is used to enable more trace information from the
ALTER and COMMIT statements.
SQL> set transaction read write;
SQL>
SQL> set flags 'stomap_stats';
SQL>
SQL> alter table EMPLOYEES
cont> add column MANAGERS_COMMENTS varchar(300);
~As: reads: async 0 synch 94, writes: async 18 synch 1
SQL>
SQL> alter storage map EMPLOYEES_MAP
cont> store
cont> using (EMPLOYEE_ID)
cont> in EMPIDS_LOW
cont> (thresholds (34,76,90))
cont> with limit of ('00200')
cont> in EMPIDS_MID
cont> (thresholds (34,76,90))
cont> with limit of ('00400')
cont> otherwise in EMPIDS_OVER
cont> (thresholds (34,76,90));
~As locking table "EMPLOYEES" (PR -> PU)
~As: removing superseded routine EMPLOYEES_MAP
~As: creating storage mapping routine EMPLOYEES_MAP (columns=1)
~As: reads: async 0 synch 117, writes: async 56 synch 0
SQL>
SQL> set flags 'rebuild_spam_pages';
SQL>
SQL> commit;
%RDMS-I-LOGMODVAL, modified record length to 423
%RDMS-I-LOGMODVAL, modified space management thresholds to (34%, 76%, 90%)
%RDMS-I-LOGMODVAL, modified record length to 423
%RDMS-I-LOGMODVAL, modified space management thresholds to (34%, 76%, 90%)
%RDMS-I-LOGMODVAL, modified record length to 423
%RDMS-I-LOGMODVAL, modified space management thresholds to (34%, 76%, 90%)
SQL>
The message LOGMODVAL will appear for each logical area in the
storage map, one per partition.
This rebuild action only applies to UNIFORM storage areas and may
incur significant I/O as SPAM pages and data pages are read to
allow the SPAM page to be rebuilt.
Example 15: Using the OPTIMIZATION_LEVEL flag
The following example shows how the behavior of a query changes
using the dynamic optimizer with the OPTIMIZATION_LEVEL flag set.
SQL> -- show with default behavior (FFirst tactic used)
SQL> select *
cont> from xtest
cont> where col2 between 999980 and 1000000
cont> and col1 > 0
cont> ;
Tables:
0 = XTEST
Leaf#01 FFirst 0:XTEST Card=10
Bool: (0.COL2 >= 999980) AND (0.COL2 <= 1000000) AND (0.COL1 > 0)
BgrNdx1 XTEST_IDX [1:0] Fan=17
Keys: 0.COL1 > 0
0 rows selected
SQL>
SQL> -- use SET FLAGS
SQL> set flags 'optimization_level(total_time)';
SQL>
SQL> -- show that BgrOnly is used for TOTAL TIME
SQL> select *
cont> from xtest
cont> where col2 between 999980 and 1000000
cont> and col1 > 0
cont> ;
Tables:
0 = XTEST
Leaf#01 BgrOnly 0:XTEST Card=10
Bool: (0.COL2 >= 999980) AND (0.COL2 <= 1000000) AND (0.COL1 > 0)
BgrNdx1 XTEST_IDX [1:0] Fan=17
Keys: 0.COL1 > 0
0 rows selected
SQL>
Example 16: Using the ON ALIAS Clause
The default behavior for SET FLAGS is to establish the flag
settings on all currently attached databases. This clause
will allow the database administrator to set flags on just one
database alias.
The following example shows a case where the enabling of AUTO_
OVERRIDE required DBADM privilege on the target database but not
on the source database. It may be that the current user does not
have (or really need) DBADM privilege on that database.
SQL> -- Now enable AUTO_OVERRIDE on only one database
SQL> set flags (on alias abc_a) 'auto_override';
SQL> set flags (on alias abc_b) 'none';
SQL> insert into abc_a.SAMPLE_TABLE select * from abc_b.SAMPLE_SOURCE;
SQL> commit;
Example 17: Using the NOREWRITE keyword
SQL> set line length 70
SQL> show flags;
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,WARN_DDL,INDEX_COLUMN_GROUP,MAX_SOLUTION,MAX_RECURSION(100)
,REWRITE(CONTAINING),REWRITE(LIKE),REWRITE(STARTING_WITH)
,REFINE_ESTIMATES(127),NOBITMAPPED_SCAN
SQL>
SQL> set flags 'norewrite';
SQL> show flags;
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,WARN_DDL,INDEX_COLUMN_GROUP,MAX_SOLUTION,MAX_RECURSION(100)
,REFINE_ESTIMATES(127),NOBITMAPPED_SCAN
SQL>