Determining whether a query is writing to disk

Issue the following query to determine the query ID for the query being investigated:

select query, elapsed, substring
from svl_qlog
order by query
desc limit 5;

This query displays the query ID, execution time, and truncated query text for the last five queries to run against the database tables, as shown in the following sample output:

query| elapsed |                            substring
-----+---------+-----------------------------------------------------
1026 | 9574270 | select s.seg, s.maxtime, s.label,
                 s.is_diskbased from query_
1025 | 18672594| select t1.c1 x1, t2.c2 x2 from tbig t1, tbig
                 t2 where t1.c1
1024 | 84266   | select count(*) as underrepped from ( select
                 count(*) as a f
1023 | 83217   | select system_status from stv_gui_status
1022 | 39236   | select * from stv_sessions
(5 rows)

Examine the output to determine the query ID that matches the query that you are investigating.

Using the query ID, issue the following query to determine whether any steps for this query wrote to the disk. The following example uses query ID 1025:

select query, step, rows, workmem, label, is_diskbased
from svl_query_summary
where query = 1025 order by workmem desc;

This query returns the following sample output:

query| step|  rows  |  workmem  |  label        | is_diskbased
-----+-----+--------+-----------+---------------+--------------
1025 |  0  |16000000|  43205240 |scan tbl=9     | f
1025 |  2  |16000000|  43205240 |hash tbl=142   | t
1025 |  0  |16000000|  55248    |scan tbl=116536| f
1025 |  2  |16000000|  55248    |dist           | f
(4 rows)

Look through the output. If IS_DISKBASED is true ("t") for any step, then that step wrote data to disk. In the previous example, the hash step intermediate results were written to disk.