SET Options for Tracing Queries
SET options of special interest in query troubleshooting include:
• SERVER_TRACE ON | OFF—Turn query tracing on or off for all sessions.
• SESSION_TRACE ON | OFF—Turn query tracing on or off for the session.
• [NO]PRINTQRY—Set query display on or off.
• [NO]QEP—Set query execution plan display on or off.
• JOINOP [NO]TIMEOUT—Set optimizer timeout on or off.
• [NO]TRACE POINT OP207—Turn display of X100 algebra on or off.
The SET SERVER_TRACE and SET SESSION_TRACE Options
The SET SERVER_TRACE ON statement enables query tracing for all sessions on the local server. Use WITH options to filter the information that is traced. For example, the following command enables query tracing and provides QEPs for each query.
SET SESSION_TRACE ON WITH QEP;
The SET SESSION_TRACE ON statement enables query tracing for the session.
For details, see the SQL Reference Guide.
The SET PRINTQRY Option
The command SET PRINTQRY prints out the query before it is optimized and executed. This is especially useful when evaluating queries for performance tuning or troubleshooting.
Example: Turn Query Display On Using ING_SET
The following command turns query display on:
Windows:
SET ING_SET=set printqry
Linux:
ING_SET = "set printqry"
export ING_SET
As an example, assume that QBF is started up. The following is displayed:
Query Buffer:
set Autocommit on
Query Parameters:
Query Buffer:
select cap_value, cap_capability from iidbcapabilities
Query Parameters:
Query Buffer:
select user_name, dba_name from iidbconstants
Query Parameters:
Example: Turn Query Display On Using II_EMBED_SET
The following command turns query display on through II_EMBED_SET. This is similar to the ING_SET environment variable. All information is gathered in the client application and printed to the log by the library routines linked to the client application:
Windows:
SET II_EMBED_SET=printqry
Linux:
II_EMBED_SET = "printqry"
export II_EMBED_SET
The query output with query timings is placed into a file called iiprtqry.log. Here is a sample output:
Query text:
select * from iirelation
Query Send Time: Thu Mar 26 17:20:43 2001
Query Response Time: Thu Mar 26 17:20:44 2001
Response CPU Time: 2630
Qry End CPU Time: 3370
This type of query monitoring can be useful for spotting slow-running queries in applications.
The SET QEP Option
The SET QEP option provides a display of the optimizer’s query execution plan (QEP) for the query after it has been optimized but before it is executed.
The following command turns QEP display on as the default ING_SET level:
Windows:
SET ING_SET=set qep
Linux:
ING_SET = "set qep"
export ING_SET
The Query Optimizer and QEPs are described in detail in the Database Administrator Guide.
The SET JOINOP NOTIMEOUT Option
The SET JOINOP NOTIMEOUT option can be used in tracing query performance. This statement turns the optimizer timeout feature off. With timeout on, the optimizer stops checking for further query execution plans when it believes that the best plan it has found takes less time to execute than the amount of time already spent searching for a plan. If you issue a SET JOINOP NOTIMEOUT statement, the optimizer continues searching query plans. This option is often used with the SET QEP option to ensure that the optimizer is picking the best possible query plan.
The SET TRACE POINT OP207 Option
The SET TRACE POINT OP207 statement displays the X100 algebra text that is passed to the X100 Engine for execution. X100 algebra is not easily interpreted by users, in general, but Actian Support may request it when troubleshooting an issue. Use SET NOTRACE POINT OP207 to turn off the display. The trace point privilege is required to use this option.
Last modified date: 04/03/2024