Once the statistics have been collected and saved in the tables, they can be
queried for analysis purposes. See for
details.
Note that spells
XPLAIN without the initial 'E'. This is helps distinguish the
functionality from the
explain functionality that you might be familiar with from commercial DBMS
products. The current XPLAIN implementation is optimized for ad-hoc queries and
tool support. Furthermore, the explain data is quite extensive to analyze.
tries to implement a
compromise between detailed explain information (which is almost unreadable by
human users and has to be evaluated with the help of a tool) and a compact
version of explain data which is only applicable for rough investigations but
is still browseable by human users. The information in the XPLAIN system tables
is sufficiently detailed to be powerful, but still simple enough to provide
useful information to ad-hoc querying during interactive use.
To use XPLAIN style, first turn on RUNTIMESTATISTICS using the
SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS() system procedure.
Optionally, turn on statistics timing using the
SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING system procedure. If you
do not turn on statistics timing, you will see the statement execution plan
only, and not the timing information.
Next, to activate XPLAIN style, use the
SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA() system procedure:
-- Turn on RUNTIMESTATISTICS for connection:
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
-- Indicate that statistics information should be captured into
-- database tables in the MYSCHEMA schema:
CALL SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA('MYSCHEMA');
-- Execute queries, step through result sets, perform application
-- processing ...
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
Although the syntax is different, the basic steps for working with XPLAIN
style are the same in a Java program.
XPLAIN style is further refined by the use of XPLAIN-only mode. By default,
XPLAIN-only mode is off, which means that statements are compiled and executed
normally. When XPLAIN-only mode is on, statements are compiled, but not
executed. This is useful for investigating what query plan has been selected for
a statement without actually executing the statement. To activate XPLAIN-only
mode, use the SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE() system
procedure:
CALL SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE(1);
Examples
Some examples of usage follow.
Retrieve the text of statements which were captured, in order by the time
when the statistics were captured:
select stmt_text, xplain_time
from myschema.sysxplain_statements
order by xplain_time
Retrieve the text of statements which were captured, showing the statements
which took the longest time to execute first:
select s.stmt_text, st.execute_time
from myschema.sysxplain_statements s,
myschema.sysxplain_statement_timings st
where s.timing_id = st.timing_id
order by st.execute_time desc
Show the statements that were executed, together with the result sets that
each statement required:
select st.stmt_text, rs.op_identifier
from myschema.sysxplain_statements st
join myschema.sysxplain_resultsets rs
on st.stmt_id = rs.stmt_id
Find statements which resulted in an external sort being performed:
select s.stmt_text, s.stmt_id, rs.op_identifier, srt.no_input_rows
from myschema.sysxplain_sort_props srt,
myschema.sysxplain_resultsets rs,
myschema.sysxplain_statements s
where rs.stmt_id = s.stmt_id and
rs.sort_rs_id = srt.sort_rs_id and
srt.sort_type = 'EX'
Show statements which resulted in a sort, sorted by the number of rows which
were sorted by that statement.
select s.stmt_text, s.stmt_id, rs.op_identifier, srt.no_input_rows
from myschema.sysxplain_sort_props srt,
myschema.sysxplain_resultsets rs,
myschema.sysxplain_statements s
where rs.stmt_id = s.stmt_id and
rs.sort_rs_id = srt.sort_rs_id
order by srt.no_input_rows desc
Find statements which resulted in a table scan of the COUNTRIES table, and
display the number of pages and rows that were visited by each scan:
select st.stmt_text, sp.no_visited_pages, sp.no_visited_rows
from myschema.sysxplain_scan_props sp,
myschema.sysxplain_resultsets rs,
myschema.sysxplain_statements st
where st.stmt_id = rs.stmt_id and
rs.scan_rs_id = sp.scan_rs_id and
rs.op_identifier = 'TABLESCAN' and
sp.scan_object_name = 'COUNTRIES'