I/O Tracing
SET IO_TRACE prints out information about disk I/O during the life of each query.
The following command turns I/O trace on as the default ING_SET level:
Windows:
SET ING_SET=set io_trace
UNIX:
C Shell:
setenv ING_SET "set io_trace"
Bourne Shell:
ING_SET = "set io_trace"
export ING_SET
VMS:
DEFINE ING_SET "SET IO_TRACE"
For example, given the query:
select * from iirelation;
Here is a sample output from the I/O trace. The counts are the number of pages read/written:
***************************************************************
I/O READ File: aaaaaaac.t00 (iidbdb, iirel_idx, 13) count:1
***************************************************************
***************************************************************
I/O READ File: aaaaaaab.t00 (iidbdb, iirelation, 0) count:8
***************************************************************
***************************************************************
I/O READ File: aaaaaaab.t00 (iidbdb, iirelation, 8) count:8
***************************************************************
I/O READ File: aaaaaaab.t00 (iidbdb, iirelation, 16) count:7
Note: When tracing the I/O or the locks of a parallel query (using SET IO_TRACE or SET LOCK_TRACE with SET PARALLEL n), the trace messages from child threads of the QEP are logged to the II_DBMS_LOG. The trace messages for the main thread are sent to the user session in the normal manner.