Returns execution information about a database query.
The STL_QUERY and STL_QUERYTEXT tables only contain information about queries, not other utility and DDL commands. For a listing and information on all statements executed by Amazon Redshift, you can also query the STL_DDLTEXT and STL_UTILITYTEXT tables. For a complete listing of all statements executed by Amazon Redshift, you can query the SVL_STATEMENTTEXT view.
To manage disk space, the STL log tables only retain approximately two to five days of log history, depending on log usage and available disk space. If you want to retain the log data, you will need to periodically copy it to other tables or unload it to Amazon S3.
This table is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of Data in System Tables and Views.
|Column Name||Data Type||Description|
|userid||integer||ID of the user who generated the entry.|
|query||integer||Query ID. The query column can be used to join other system tables and views.|
|label||character(15)||Either the name of the file used to run the
query or a label defined with a SET QUERY_GROUP command. If the
query is not file-based or the QUERY_GROUP parameter is not set,
this field value is
|pid||integer||Process ID. Normally, all of the queries in a session are run in the same process, so this value usually remains constant if you run a series of queries in the same session. Following certain internal events, Amazon Redshift might restart an active session and assign a new PID. For more information, see STL_RESTARTED_SESSIONS.|
|database||character(32)||The name of the database the user was connected to when the query was issued.|
|querytxt||character(4000)||Actual query text for the query.|
|starttime||timestamp||Time in UTC that the query started executing, with 6 digits of precision for fractional seconds. For example:
|endtime||timestamp||Time in UTC that the query finished executing, with 6 digits of precision for fractional seconds. For example:
|aborted||integer||If a query was aborted by the system or
canceled by the user, this column contains
|insert_pristine||integer||Whether write queries are/were able to run while the current query is/was running. 1 = no write queries allowed. 0 = write queries allowed. This column is intended for use in debugging.|
The following query lists the five most recent queries.
select query, trim(querytxt) as sqlquery from stl_query order by query desc limit 5; query | sqlquery ------+-------------------------------------------------- 129 | select query, trim(querytxt) from stl_query order by query; 128 | select node from stv_disk_read_speeds; 127 | select system_status from stv_gui_status 126 | select * from systable_topology order by slice 125 | load global dict registry (5 rows)
The following query returns the time elapsed in descending order for queries that ran on February 15, 2013.
select query, datediff(seconds, starttime, endtime), trim(querytxt) as sqlquery from stl_query where starttime >= '2013-02-15 00:00' and endtime < '2013-02-15 23:59' order by date_diff desc; query | date_diff | sqlquery -------+-----------+------------------------------------------- 55 | 119 | padb_fetch_sample: select count(*) from category 121 | 9 | select * from svl_query_summary; 181 | 6 | select * from svl_query_summary where query in(179,178); 172 | 5 | select * from svl_query_summary where query=148; ... (189 rows)