Debugging JavaDB Query Performance

— turn on RUNTIMESTATISTICS for connection:

— Indicate that statistics information should be captured into
— database tables in the SomeSchema schema:

—execute  the queries:
SELECT * FROM <SomeTable> WHERE someName = ‘XYZ’ ;

–turn off runtime statistics:
–Retrieve the text of statements which were captured, in order by the time when the statistics were captured:
select stmt_text, xplain_time from SomeSchema.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 SomeSchema.sysxplain_statements s, SomeSchema.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 SomeSchema.sysxplain_statements st  join SomeSchema.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 SomeSchema.sysxplain_sort_props srt, SomeSchema.sysxplain_resultsets rs, SomeSchema.sysxplain_statements s where rs.stmt_id = s.stmt_id and rs.sort_rs_id = srt.sort_rs_id and srt.sort_type = ‘EX’

–Find statements which resulted in a tablescan:
select st.stmt_text, sp.no_visited_pages, sp.no_visited_rows from SomeSchema.sysxplain_scan_props sp, SomeSchema.sysxplain_resultsets rs, SomeSchema.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 = ‘<SomeTable>’


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s