Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# System monitoring (provisioned only)
The following system tables and views can be queried on provisioned clusters. STL and STV tables and views contain a subset of data found in several of the system tables. These provide quicker and easier access to commonly queried data found in those tables.
SVCS views provide details about queries on both the main and concurrency scaling clusters. SVL views provide information only for queries run on the main cluster, with the exception of SVL\$1STATEMENTTEXT. SVL\$1STATEMENTTEXT can contain information for queries run on concurrency scaling clusters as well as the main cluster.
**Topics**
+ [STL views for logging](c_intro_STL_tables.md)
+ [STV tables for snapshot data](c_intro_STV_tables.md)
+ [SVCS views for main and concurrency scaling clusters](svcs_views.md)
+ [SVL views for main cluster](svl_views.md)
# STL views for logging
STL system views are generated from Amazon Redshift log files to provide a history of the system.
These files reside on every node in the data warehouse cluster. The STL views take the information from the logs and format them into usable views for system administrators.
**Log retention** – STL system views retain seven days of log history. Log retention is guaranteed for all cluster sizes and node types, and it isn't affected by changes in cluster workload. Log retention also isn't affected by cluster status, such as when the cluster is paused. You have less than seven days of log history only in the case where the cluster is new. You don't need to take any action to retain logs, but you have to periodically copy log data to other tables or unload it to Amazon S3 to keep log data that's more than 7 days old.
**Topics**
+ [STL\$1AGGR](r_STL_AGGR.md)
+ [STL\$1ALERT\$1EVENT\$1LOG](r_STL_ALERT_EVENT_LOG.md)
+ [STL\$1ANALYZE](r_STL_ANALYZE.md)
+ [STL\$1ANALYZE\$1COMPRESSION](r_STL_ANALYZE_COMPRESSION.md)
+ [STL\$1BCAST](r_STL_BCAST.md)
+ [STL\$1COMMIT\$1STATS](r_STL_COMMIT_STATS.md)
+ [STL\$1CONNECTION\$1LOG](r_STL_CONNECTION_LOG.md)
+ [STL\$1DDLTEXT](r_STL_DDLTEXT.md)
+ [STL\$1DELETE](r_STL_DELETE.md)
+ [STL\$1DISK\$1FULL\$1DIAG](r_STL_DISK_FULL_DIAG.md)
+ [STL\$1DIST](r_STL_DIST.md)
+ [STL\$1ERROR](r_STL_ERROR.md)
+ [STL\$1EXPLAIN](r_STL_EXPLAIN.md)
+ [STL\$1FILE\$1SCAN](r_STL_FILE_SCAN.md)
+ [STL\$1HASH](r_STL_HASH.md)
+ [STL\$1HASHJOIN](r_STL_HASHJOIN.md)
+ [STL\$1INSERT](r_STL_INSERT.md)
+ [STL\$1LIMIT](r_STL_LIMIT.md)
+ [STL\$1LOAD\$1COMMITS](r_STL_LOAD_COMMITS.md)
+ [STL\$1LOAD\$1ERRORS](r_STL_LOAD_ERRORS.md)
+ [STL\$1LOADERROR\$1DETAIL](r_STL_LOADERROR_DETAIL.md)
+ [STL\$1MERGE](r_STL_MERGE.md)
+ [STL\$1MERGEJOIN](r_STL_MERGEJOIN.md)
+ [STL\$1MV\$1STATE](r_STL_MV_STATE.md)
+ [STL\$1NESTLOOP](r_STL_NESTLOOP.md)
+ [STL\$1PARSE](r_STL_PARSE.md)
+ [STL\$1PLAN\$1INFO](r_STL_PLAN_INFO.md)
+ [STL\$1PROJECT](r_STL_PROJECT.md)
+ [STL\$1QUERY](r_STL_QUERY.md)
+ [STL\$1QUERY\$1METRICS](r_STL_QUERY_METRICS.md)
+ [STL\$1QUERYTEXT](r_STL_QUERYTEXT.md)
+ [STL\$1REPLACEMENTS](r_STL_REPLACEMENTS.md)
+ [STL\$1RESTARTED\$1SESSIONS](r_STL_RESTARTED_SESSIONS.md)
+ [STL\$1RETURN](r_STL_RETURN.md)
+ [STL\$1S3CLIENT](r_STL_S3CLIENT.md)
+ [STL\$1S3CLIENT\$1ERROR](r_STL_S3CLIENT_ERROR.md)
+ [STL\$1SAVE](r_STL_SAVE.md)
+ [STL\$1SCAN](r_STL_SCAN.md)
+ [STL\$1SCHEMA\$1QUOTA\$1VIOLATIONS](r_STL_SCHEMA_QUOTA_VIOLATIONS.md)
+ [STL\$1SESSIONS](r_STL_SESSIONS.md)
+ [STL\$1SORT](r_STL_SORT.md)
+ [STL\$1SSHCLIENT\$1ERROR](r_STL_SSHCLIENT_ERROR.md)
+ [STL\$1STREAM\$1SEGS](r_STL_STREAM_SEGS.md)
+ [STL\$1TR\$1CONFLICT](r_STL_TR_CONFLICT.md)
+ [STL\$1UNDONE](r_STL_UNDONE.md)
+ [STL\$1UNIQUE](r_STL_UNIQUE.md)
+ [STL\$1UNLOAD\$1LOG](r_STL_UNLOAD_LOG.md)
+ [STL\$1USAGE\$1CONTROL](r_STL_USAGE_CONTROL.md)
+ [STL\$1USERLOG](r_STL_USERLOG.md)
+ [STL\$1UTILITYTEXT](r_STL_UTILITYTEXT.md)
+ [STL\$1VACUUM](r_STL_VACUUM.md)
+ [STL\$1WINDOW](r_STL_WINDOW.md)
+ [STL\$1WLM\$1ERROR](r_STL_WLM_ERROR.md)
+ [STL\$1WLM\$1RULE\$1ACTION](r_STL_WLM_RULE_ACTION.md)
+ [STL\$1WLM\$1QUERY](r_STL_WLM_QUERY.md)
# STL\$1AGGR
Analyzes aggregate execution steps for queries. These steps occur during execution of aggregate functions and GROUP BY clauses.
STL\$1AGGR 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1AGGR only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_AGGR.html)
## Sample queries
Returns information about aggregate execution steps for SLICE 1 and TBL 239.
```
select query, segment, bytes, slots, occupied, maxlength, is_diskbased, workmem, type
from stl_aggr where slice=1 and tbl=239
order by rows
limit 10;
```
```
query | segment | bytes | slots | occupied | maxlength | is_diskbased | workmem | type
-------+---------+-------+---------+----------+-----------+--------------+-----------+--------
562 | 1 | 0 | 4194304 | 0 | 0 | f | 383385600 | HASHED
616 | 1 | 0 | 4194304 | 0 | 0 | f | 383385600 | HASHED
546 | 1 | 0 | 4194304 | 0 | 0 | f | 383385600 | HASHED
547 | 0 | 8 | 0 | 0 | 0 | f | 0 | PLAIN
685 | 1 | 32 | 4194304 | 1 | 0 | f | 383385600 | HASHED
652 | 0 | 8 | 0 | 0 | 0 | f | 0 | PLAIN
680 | 0 | 8 | 0 | 0 | 0 | f | 0 | PLAIN
658 | 0 | 8 | 0 | 0 | 0 | f | 0 | PLAIN
686 | 0 | 8 | 0 | 0 | 0 | f | 0 | PLAIN
695 | 1 | 32 | 4194304 | 1 | 0 | f | 383385600 | HASHED
(10 rows)
```
# STL\$1ALERT\$1EVENT\$1LOG
Records an alert when the query optimizer identifies conditions that might indicate performance issues. Use the STL\$1ALERT\$1EVENT\$1LOG view to identify opportunities to improve query performance.
A query consists of multiple segments, and each segment consists of one or more steps. For more information, see [Query processing](c-query-processing.md).
STL\$1ALERT\$1EVENT\$1LOG 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1ALERT\$1EVENT\$1LOG only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_ALERT_EVENT_LOG.html)
## Usage notes
You can use the STL\$1ALERT\$1EVENT\$1LOG to identify potential issues in your queries, then follow the practices in [Query performance tuning](c-optimizing-query-performance.md) to optimize your database design and rewrite your queries. STL\$1ALERT\$1EVENT\$1LOG records the following alerts:
+ **Missing statistics**
Statistics are missing. Run ANALYZE following data loads or significant updates and use STATUPDATE with COPY operations. For more information, see [Amazon Redshift best practices for designing queries](c_designing-queries-best-practices.md).
+ **Nested loop **
A nested loop is usually a Cartesian product. Evaluate your query to ensure that all participating tables are joined efficiently.
+ **Very selective filter**
The ratio of rows returned to rows scanned is less than 0.05. Rows scanned is the value of `rows_pre_user_filter` and rows returned is the value of rows in the [STL\$1SCAN](r_STL_SCAN.md) system view. Indicates that the query is scanning an unusually large number of rows to determine the result set. This can be caused by missing or incorrect sort keys. For more information, see [Sort keys](t_Sorting_data.md).
+ **Excessive ghost rows **
A scan skipped a relatively large number of rows that are marked as deleted but not vacuumed, or rows that have been inserted but not committed. For more information, see [Vacuuming tables](t_Reclaiming_storage_space202.md).
+ **Large distribution **
More than 1,000,000 rows were redistributed for hash join or aggregation. For more information, see [Data distribution for query optimization](t_Distributing_data.md).
+ **Large broadcast **
More than 1,000,000 rows were broadcast for hash join. For more information, see [Data distribution for query optimization](t_Distributing_data.md).
+ **Serial execution **
A DS\$1DIST\$1ALL\$1INNER redistribution style was indicated in the query plan, which forces serial execution because the entire inner table was redistributed to a single node. For more information, see [Data distribution for query optimization](t_Distributing_data.md).
## Sample queries
The following query shows alert events for four queries.
```
SELECT query, substring(event,0,25) as event,
substring(solution,0,25) as solution,
trim(event_time) as event_time from stl_alert_event_log order by query;
query | event | solution | event_time
-------+-------------------------------+------------------------------+---------------------
6567 | Missing query planner statist | Run the ANALYZE command | 2014-01-03 18:20:58
7450 | Scanned a large number of del | Run the VACUUM command to rec| 2014-01-03 21:19:31
8406 | Nested Loop Join in the query | Review the join predicates to| 2014-01-04 00:34:22
29512 | Very selective query filter:r | Review the choice of sort key| 2014-01-06 22:00:00
(4 rows)
```
# STL\$1ANALYZE
Records details for [ANALYZE](r_ANALYZE.md) operations.
STL\$1ANALYZE is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1ANALYZE\$1HISTORY](SYS_ANALYZE_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_ANALYZE.html)
## Sample queries
The following example joins STV\$1TBL\$1PERM to show the table name and execution details.
```
select distinct a.xid, trim(t.name) as name, a.status, a.rows, a.modified_rows, a.starttime, a.endtime
from stl_analyze a
join stv_tbl_perm t on t.id=a.table_id
where name = 'users'
order by starttime;
xid | name | status | rows | modified_rows | starttime | endtime
-------+-------+-----------------+-------+---------------+---------------------+--------------------
1582 | users | Full | 49990 | 49990 | 2016-09-22 22:02:23 | 2016-09-22 22:02:28
244287 | users | Full | 24992 | 74988 | 2016-10-04 22:50:58 | 2016-10-04 22:51:01
244712 | users | Full | 49984 | 24992 | 2016-10-04 22:56:07 | 2016-10-04 22:56:07
245071 | users | Skipped | 49984 | 0 | 2016-10-04 22:58:17 | 2016-10-04 22:58:17
245439 | users | Skipped | 49984 | 1982 | 2016-10-04 23:00:13 | 2016-10-04 23:00:13
(5 rows)
```
# STL\$1ANALYZE\$1COMPRESSION
Records details for compression analysis operations during COPY or ANALYZE COMPRESSION commands.
STL\$1ANALYZE\$1COMPRESSION 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1ANALYZE\$1COMPRESSION\$1HISTORY](r_SYS_ANALYZE_COMPRESSION_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_ANALYZE_COMPRESSION.html)
## Sample queries
The following example inspects the details of compression analysis on the `lineitem` table by the last COPY command run in the same session.
```
select xid, tbl, btrim(tablename) as tablename, col, old_encoding, new_encoding, best_compression_encoding, mode
from stl_analyze_compression
where xid = (select xid from stl_query where query = pg_last_copy_id()) order by col;
xid | tbl | tablename | col | old_encoding | new_encoding | best_compression_encoding | mode
------+--------+-----------+-----+-----------------+-----------------+---------------------------+----------------
5308 | 158961 | $lineitem | 0 | mostly32 | az64 | delta | ON
5308 | 158961 | $lineitem | 1 | mostly32 | az64 | az64 | ON
5308 | 158961 | $lineitem | 2 | lzo | az64 | az64 | ON
5308 | 158961 | $lineitem | 3 | delta | az64 | az64 | ON
5308 | 158961 | $lineitem | 4 | bytedict | az64 | bytedict | ON
5308 | 158961 | $lineitem | 5 | mostly32 | az64 | az64 | ON
5308 | 158961 | $lineitem | 6 | delta | az64 | az64 | ON
5308 | 158961 | $lineitem | 7 | delta | az64 | az64 | ON
5308 | 158961 | $lineitem | 8 | lzo | lzo | lzo | ON
5308 | 158961 | $lineitem | 9 | runlength | runlength | runlength | ON
5308 | 158961 | $lineitem | 10 | delta | az64 | az64 | ON
5308 | 158961 | $lineitem | 11 | delta | az64 | az64 | ON
5308 | 158961 | $lineitem | 12 | delta | az64 | az64 | ON
5308 | 158961 | $lineitem | 13 | bytedict | bytedict | bytedict | ON
5308 | 158961 | $lineitem | 14 | bytedict | bytedict | bytedict | ON
5308 | 158961 | $lineitem | 15 | text255 | text255 | text255 | ON
(16 rows)
```
# STL\$1BCAST
Logs information about network activity during execution of query steps that broadcast data. Network traffic is captured by numbers of rows, bytes, and packets that are sent over the network during a given step on a given slice. The duration of the step is the difference between the logged start and end times.
To identify broadcast steps in a query, look for bcast labels in the SVL\$1QUERY\$1SUMMARY view or run the EXPLAIN command and then look for step attributes that include bcast.
STL\$1BCAST 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1BCAST only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_BCAST.html)
### Sample queries
The following example returns broadcast information for the queries where there are one or more packets, and the difference between the start and end of the query was one second or more.
```
select query, slice, step, rows, bytes, packets, datediff(seconds, starttime, endtime)
from stl_bcast
where packets>0 and datediff(seconds, starttime, endtime)>0;
```
```
query | slice | step | rows | bytes | packets | date_diff
-------+-------+------+------+-------+---------+-----------
453 | 2 | 5 | 1 | 264 | 1 | 1
798 | 2 | 5 | 1 | 264 | 1 | 1
1408 | 2 | 5 | 1 | 264 | 1 | 1
2993 | 0 | 5 | 1 | 264 | 1 | 1
5045 | 3 | 5 | 1 | 264 | 1 | 1
8073 | 3 | 5 | 1 | 264 | 1 | 1
8163 | 3 | 5 | 1 | 264 | 1 | 1
9212 | 1 | 5 | 1 | 264 | 1 | 1
9873 | 1 | 5 | 1 | 264 | 1 | 1
(9 rows)
```
# STL\$1COMMIT\$1STATS
Provides metrics related to commit performance, including the timing of the various stages of commit and the number of blocks committed. Query STL\$1COMMIT\$1STATS to determine what portion of a transaction was spent on commit and how much queuing is occurring.
STL\$1COMMIT\$1STATS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1TRANSACTION\$1HISTORY](SYS_TRANSACTION_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_COMMIT_STATS.html)
## Sample query
```
select node, datediff(ms,startqueue,startwork) as queue_time,
datediff(ms, startwork, endtime) as commit_time, queuelen
from stl_commit_stats
where xid = 2574
order by node;
node | queue_time | commit_time | queuelen
-----+--------------+-------------+---------
-1 | 0 | 617 | 0
0 | 444950725641 | 616 | 0
1 | 444950725636 | 616 | 0
```
# STL\$1CONNECTION\$1LOG
Logs authentication attempts and connections and disconnections.
STL\$1CONNECTION\$1LOG is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1CONNECTION\$1LOG](SYS_CONNECTION_LOG.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_CONNECTION_LOG.html)
## Sample queries
To view the details for open connections, run the following query.
```
select recordtime, username, dbname, remotehost, remoteport
from stl_connection_log
where event = 'initiating session'
and pid not in
(select pid from stl_connection_log
where event = 'disconnecting session')
order by 1 desc;
recordtime | username | dbname | remotehost | remoteport
--------------------+-------------+------------+---------------+------------
2014-11-06 20:30:06 | rdsdb | dev | [local] |
2014-11-06 20:29:37 | test001 | test | 10.49.42.138 | 11111
2014-11-05 20:30:29 | rdsdb | dev | 10.49.42.138 | 33333
2014-11-05 20:28:35 | rdsdb | dev | [local] |
(4 rows)
```
The following example reflects a failed authentication attempt and a successful connection and disconnection.
```
select event, recordtime, remotehost, username
from stl_connection_log order by recordtime;
event | recordtime | remotehost | username
-----------------------+---------------------------+--------------+---------
authentication failure | 2012-10-25 14:41:56.96391 | 10.49.42.138 | john
authenticated | 2012-10-25 14:42:10.87613 | 10.49.42.138 | john
initiating session | 2012-10-25 14:42:10.87638 | 10.49.42.138 | john
disconnecting session | 2012-10-25 14:42:19.95992 | 10.49.42.138 | john
(4 rows)
```
The following example shows the version of the ODBC driver, the operating system on the client machine, and the plugin used to connect to the Amazon Redshift cluster. In this example, the plugin used is for standard ODBC driver authentication using a login name and password.
```
select driver_version, os_version, plugin_name from stl_connection_log;
driver_version | os_version | plugin_name
----------------------------------------+-----------------------------------+--------------------
Amazon Redshift ODBC Driver 1.4.15.0001 | Darwin 18.7.0 x86_64 | none
Amazon Redshift ODBC Driver 1.4.15.0001 | Linux 4.15.0-101-generic x86_64 | none
```
The following example shows the version of the operating system on the client machine, the driver version, and the protocol version.
```
select os_version, driver_version, protocol_version from stl_connection_log;
os_version | driver_version | protocol_version
--------------------------------+------------------------------+--------------------
Linux 4.15.0-101-generic x86_64 | Redshift JDBC Driver 2.0.0.0 | 2
Linux 4.15.0-101-generic x86_64 | Redshift JDBC Driver 2.0.0.0 | 2
Linux 4.15.0-101-generic x86_64 | Redshift JDBC Driver 2.0.0.0 | 2
```
# STL\$1DDLTEXT
Captures the following DDL statements that were run on the system.
These DDL statements include the following queries and objects:
+ CREATE SCHEMA, TABLE, VIEW
+ DROP SCHEMA, TABLE, VIEW
+ ALTER SCHEMA, TABLE
See also [STL\$1QUERYTEXT](r_STL_QUERYTEXT.md), [STL\$1UTILITYTEXT](r_STL_UTILITYTEXT.md), and [SVL\$1STATEMENTTEXT](r_SVL_STATEMENTTEXT.md). These views provide a timeline of the SQL commands that are run on the system; this history is useful for troubleshooting and for creating an audit trail of all system activities.
Use the STARTTIME and ENDTIME columns to find out which statements were logged during a given time period. Long blocks of SQL text are broken into lines 200 characters long; the SEQUENCE column identifies fragments of text that belong to a single statement.
STL\$1DDLTEXT 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_DDLTEXT.html)
## Sample queries
The following query returns records that include previously run DDL statements.
```
select xid, starttime, sequence, substring(text,1,40) as text
from stl_ddltext order by xid desc, sequence;
```
The following is sample output that shows four CREATE TABLE statements. The DDL statements appear in the `text` column, which is truncated for readability.
```
xid | starttime | sequence | text
------+----------------------------+----------+------------------------------------------
1806 | 2013-10-23 00:11:14.709851 | 0 | CREATE TABLE supplier ( s_suppkey int4 N
1806 | 2013-10-23 00:11:14.709851 | 1 | s_comment varchar(101) NOT NULL )
1805 | 2013-10-23 00:11:14.496153 | 0 | CREATE TABLE region ( r_regionkey int4 N
1804 | 2013-10-23 00:11:14.285986 | 0 | CREATE TABLE partsupp ( ps_partkey int8
1803 | 2013-10-23 00:11:14.056901 | 0 | CREATE TABLE part ( p_partkey int8 NOT N
1803 | 2013-10-23 00:11:14.056901 | 1 | ner char(10) NOT NULL , p_retailprice nu
(6 rows)
```
### Reconstructing Stored SQL
The following SQL lists rows stored in the `text` column of STL\$1DDLTEXT. The rows are ordered by `xid` and `sequence`. If the original SQL was longer than 200 characters multiple rows, STL\$1DDLTEXT can contain multiple rows by `sequence`.
```
SELECT xid, sequence, LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END, '') WITHIN GROUP (ORDER BY sequence) as query_statement
FROM stl_ddltext GROUP BY xid, sequence ORDER BY xid, sequence;
```
```
xid | sequence | query_statement
--------+-----------+-----------------
7886671 0 create external schema schema_spectrum_uddh\nfrom data catalog\ndatabase 'spectrum_db_uddh'\niam_role ''\ncreate external database if not exists;
7886752 0 CREATE EXTERNAL TABLE schema_spectrum_uddh.soccer_league\n(\n league_rank smallint,\n prev_rank smallint,\n club_name varchar(15),\n league_name varchar(20),\n league_off decimal(6,2),\n le
7886752 1 ague_def decimal(6,2),\n league_spi decimal(6,2),\n league_nspi smallint\n)\nROW FORMAT DELIMITED \n FIELDS TERMINATED BY ',' \n LINES TERMINATED BY '\\n\\l'\nstored as textfile\nLOCATION 's
7886752 2 3://mybucket-spectrum-uddh/'\ntable properties ('skip.header.line.count'='1');
...
```
To reconstruct the SQL stored in the `text` column of STL\$1DDLTEXT, run the following SQL statement. It puts together DDL statements from one or more segments in the `text` column. Before running the reconstructed SQL, replace any (`\n`) special characters with a new line in your SQL client. The results of the following SELECT statement puts together three rows in sequence order to reconstruct the SQL, in the `query_statement` field.
```
SELECT LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) as query_statement
FROM stl_ddltext GROUP BY xid, endtime order by xid, endtime;
```
```
query_statement
--------------
create external schema schema_spectrum_uddh\nfrom data catalog\ndatabase 'spectrum_db_uddh'\niam_role ''\ncreate external database if not exists;
CREATE EXTERNAL TABLE schema_spectrum_uddh.soccer_league\n(\n league_rank smallint,\n prev_rank smallint,\n club_name varchar(15),\n league_name varchar(20),\n league_off decimal(6,2),\n league_def decimal(6,2),\n league_spi decimal(6,2),\n league_nspi smallint\n)\nROW FORMAT DELIMITED \n FIELDS TERMINATED BY ',' \n LINES TERMINATED BY '\\n\\l'\nstored as textfile\nLOCATION 's3://mybucket-spectrum-uddh/'\ntable properties ('skip.header.line.count'='1');
```
# STL\$1DELETE
Analyzes delete execution steps for queries.
STL\$1DELETE 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1DELETE only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_DELETE.html)
## Sample queries
In order to create a row in STL\$1DELETE, the following example inserts a row into the EVENT table and then deletes it.
First, insert a row into the EVENT table and verify that it was inserted.
```
insert into event(eventid,venueid,catid,dateid,eventname)
values ((select max(eventid)+1 from event),95,9,1857,'Lollapalooza');
```
```
select * from event
where eventname='Lollapalooza'
order by eventid;
```
```
eventid | venueid | catid | dateid | eventname | starttime
---------+---------+-------+--------+--------------+---------------------
4274 | 102 | 9 | 1965 | Lollapalooza | 2008-05-01 19:00:00
4684 | 114 | 9 | 2105 | Lollapalooza | 2008-10-06 14:00:00
5673 | 128 | 9 | 1973 | Lollapalooza | 2008-05-01 15:00:00
5740 | 51 | 9 | 1933 | Lollapalooza | 2008-04-17 15:00:00
5856 | 119 | 9 | 1831 | Lollapalooza | 2008-01-05 14:00:00
6040 | 126 | 9 | 2145 | Lollapalooza | 2008-11-15 15:00:00
7972 | 92 | 9 | 2026 | Lollapalooza | 2008-07-19 19:30:00
8046 | 65 | 9 | 1840 | Lollapalooza | 2008-01-14 15:00:00
8518 | 48 | 9 | 1904 | Lollapalooza | 2008-03-19 15:00:00
8799 | 95 | 9 | 1857 | Lollapalooza |
(10 rows)
```
Now, delete the row that you added to the EVENT table and verify that it was deleted.
```
delete from event
where eventname='Lollapalooza' and eventid=(select max(eventid) from event);
```
```
select * from event
where eventname='Lollapalooza'
order by eventid;
```
```
eventid | venueid | catid | dateid | eventname | starttime
---------+---------+-------+--------+--------------+---------------------
4274 | 102 | 9 | 1965 | Lollapalooza | 2008-05-01 19:00:00
4684 | 114 | 9 | 2105 | Lollapalooza | 2008-10-06 14:00:00
5673 | 128 | 9 | 1973 | Lollapalooza | 2008-05-01 15:00:00
5740 | 51 | 9 | 1933 | Lollapalooza | 2008-04-17 15:00:00
5856 | 119 | 9 | 1831 | Lollapalooza | 2008-01-05 14:00:00
6040 | 126 | 9 | 2145 | Lollapalooza | 2008-11-15 15:00:00
7972 | 92 | 9 | 2026 | Lollapalooza | 2008-07-19 19:30:00
8046 | 65 | 9 | 1840 | Lollapalooza | 2008-01-14 15:00:00
8518 | 48 | 9 | 1904 | Lollapalooza | 2008-03-19 15:00:00
(9 rows)
```
Then query stl\$1delete to see the execution steps for the deletion. In this example, the query returned over 300 rows, so the output below is shortened for display purposes.
```
select query, slice, segment, step, tasknum, rows, tbl from stl_delete order by query;
```
```
query | slice | segment | step | tasknum | rows | tbl
-------+-------+---------+------+---------+------+--------
7 | 0 | 0 | 1 | 0 | 0 | 100000
7 | 1 | 0 | 1 | 0 | 0 | 100000
8 | 0 | 0 | 1 | 2 | 0 | 100001
8 | 1 | 0 | 1 | 2 | 0 | 100001
9 | 0 | 0 | 1 | 4 | 0 | 100002
9 | 1 | 0 | 1 | 4 | 0 | 100002
10 | 0 | 0 | 1 | 6 | 0 | 100003
10 | 1 | 0 | 1 | 6 | 0 | 100003
11 | 0 | 0 | 1 | 8 | 0 | 100253
11 | 1 | 0 | 1 | 8 | 0 | 100253
12 | 0 | 0 | 1 | 0 | 0 | 100255
12 | 1 | 0 | 1 | 0 | 0 | 100255
13 | 0 | 0 | 1 | 2 | 0 | 100257
13 | 1 | 0 | 1 | 2 | 0 | 100257
14 | 0 | 0 | 1 | 4 | 0 | 100259
14 | 1 | 0 | 1 | 4 | 0 | 100259
...
```
# STL\$1DISK\$1FULL\$1DIAG
Logs information about errors recorded when the disk is full.
STL\$1DISK\$1FULL\$1DIAG is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_DISK_FULL_DIAG.html)
## Sample queries
The following example returns details about the data stored when there is a disk-full error.
```
select * from stl_disk_full_diag
```
The following example converts the `currenttime` to a timestamp.
```
select '2000-01-01'::timestamp + (currenttime/1000000.0)* interval '1 second' as currenttime,node_num,query_id,temp_blocks from pg_catalog.stl_disk_full_diag;
```
```
currenttime | node_num | query_id | temp_blocks
----------------------------+----------+----------+-------------
2019-05-18 19:19:18.609338 | 0 | 569399 | 70982
2019-05-18 19:37:44.755548 | 0 | 569580 | 70982
2019-05-20 13:37:20.566916 | 0 | 597424 | 70869
```
# STL\$1DIST
Logs information about network activity during execution of query steps that distribute data. Network traffic is captured by numbers of rows, bytes, and packets that are sent over the network during a given step on a given slice. The duration of the step is the difference between the logged start and end times.
To identify distribution steps in a query, look for dist labels in the QUERY\$1SUMMARY view or run the EXPLAIN command and then look for step attributes that include dist.
STL\$1DIST 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1DIST only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_DIST.html)
## Sample queries
The following example returns distribution information for queries with one or more packets and duration greater than zero.
```
select query, slice, step, rows, bytes, packets,
datediff(seconds, starttime, endtime) as duration
from stl_dist
where packets>0 and datediff(seconds, starttime, endtime)>0
order by query
limit 10;
```
```
query | slice | step | rows | bytes | packets | duration
--------+-------+------+--------+---------+---------+-----------
567 | 1 | 4 | 49990 | 6249564 | 707 | 1
630 | 0 | 5 | 8798 | 408404 | 46 | 2
645 | 1 | 4 | 8798 | 408404 | 46 | 1
651 | 1 | 5 | 192497 | 9226320 | 1039 | 6
669 | 1 | 4 | 192497 | 9226320 | 1039 | 4
675 | 1 | 5 | 3766 | 194656 | 22 | 1
696 | 0 | 4 | 3766 | 194656 | 22 | 1
705 | 0 | 4 | 930 | 44400 | 5 | 1
111525 | 0 | 3 | 68 | 17408 | 2 | 1
(9 rows)
```
# STL\$1ERROR
Records internal processing errors generated by the Amazon Redshift database engine. STL\$1ERROR does not record SQL errors or messages. The information in STL\$1ERROR is useful for troubleshooting certain errors. An AWS support engineer might ask you to provide this information as part of the troubleshooting process.
STL\$1ERROR 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
For a list of error codes that can be generated while loading data with the Copy command, see [Load error reference](r_Load_Error_Reference.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_ERROR.html)
## Sample queries
The following example retrieves the error information from STL\$1ERROR.
```
select process, errcode, linenum as line,
trim(error) as err
from stl_error;
process | errcode | line | err
--------------+---------+------+------------------------------------------------------------------
padbmaster | 8001 | 194 | Path prefix: s3://redshift-downloads/testnulls/venue.txt*
padbmaster | 8001 | 529 | Listing bucket=redshift-downloads prefix=tests/category-csv-quotes
padbmaster | 2 | 190 | database "template0" is not currently accepting connections
padbmaster | 32 | 1956 | pq_flush: could not send data to client: Broken pipe
(4 rows)
```
# STL\$1EXPLAIN
Displays the EXPLAIN plan for a query that has been submitted for execution.
STL\$1EXPLAIN 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1EXPLAIN only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1EXPLAIN](SYS_QUERY_EXPLAIN.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_EXPLAIN.html)
## Sample queries
Consider the following EXPLAIN output for an aggregate join query:
```
explain select avg(datediff(day, listtime, saletime)) as avgwait
from sales, listing where sales.listid = listing.listid;
QUERY PLAN
------------------------------------------------------------------------------
XN Aggregate (cost=6350.30..6350.31 rows=1 width=16)
-> XN Hash Join DS_DIST_NONE (cost=47.08..6340.89 rows=3766 width=16)
Hash Cond: ("outer".listid = "inner".listid)
-> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=12)
-> XN Hash (cost=37.66..37.66 rows=3766 width=12)
-> XN Seq Scan on sales (cost=0.00..37.66 rows=3766 width=12)
(6 rows)
```
If you run this query and its query ID is 10, you can use the STL\$1EXPLAIN table to see the same kind of information that the EXPLAIN command returns:
```
select query,nodeid,parentid,substring(plannode from 1 for 30),
substring(info from 1 for 20) from stl_explain
where query=10 order by 1,2;
query| nodeid |parentid| substring | substring
-----+--------+--------+--------------------------------+-------------------
10 | 1 | 0 |XN Aggregate (cost=6717.61..6 |
10 | 2 | 1 | -> XN Merge Join DS_DIST_NO | Merge Cond:("outer"
10 | 3 | 2 | -> XN Seq Scan on lis |
10 | 4 | 2 | -> XN Seq Scan on sal |
(4 rows)
```
Consider the following query:
```
select event.eventid, sum(pricepaid)
from event, sales
where event.eventid=sales.eventid
group by event.eventid order by 2 desc;
eventid | sum
--------+----------
289 | 51846.00
7895 | 51049.00
1602 | 50301.00
851 | 49956.00
7315 | 49823.00
...
```
If this query's ID is 15, the following system view query returns the plan nodes that were completed. In this case, the order of the nodes is reversed to show the actual order of execution:
```
select query,nodeid,parentid,substring(plannode from 1 for 56)
from stl_explain where query=15 order by 1, 2 desc;
query|nodeid|parentid| substring
-----+------+--------+--------------------------------------------------------
15 | 8 | 7 | -> XN Seq Scan on eve
15 | 7 | 5 | -> XN Hash(cost=87.98..87.9
15 | 6 | 5 | -> XN Seq Scan on sales(cos
15 | 5 | 4 | -> XN Hash Join DS_DIST_OUTER(cos
15 | 4 | 3 | -> XN HashAggregate(cost=862286577.07..
15 | 3 | 2 | -> XN Sort(cost=1000862287175.47..10008622871
15 | 2 | 1 | -> XN Network(cost=1000862287175.47..1000862287197.
15 | 1 | 0 |XN Merge(cost=1000862287175.47..1000862287197.46 rows=87
(8 rows)
```
The following query retrieves the query IDs for any query plans that contain a window function:
```
select query, trim(plannode) from stl_explain
where plannode like '%Window%';
query| btrim
-----+------------------------------------------------------------------------
26 | -> XN Window(cost=1000985348268.57..1000985351256.98 rows=170 width=33)
27 | -> XN Window(cost=1000985348268.57..1000985351256.98 rows=170 width=33)
(2 rows)
```
# STL\$1FILE\$1SCAN
Returns the files that Amazon Redshift read while loading data by using the COPY command.
Querying this view can help troubleshoot data load errors. STL\$1FILE\$1SCAN can be particularly helpful with pinpointing issues in parallel data loads, because parallel data loads typically load many files with a single COPY command.
STL\$1FILE\$1SCAN 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1FILE\$1SCAN only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1LOAD\$1DETAIL](SYS_LOAD_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_FILE_SCAN.html)
## Sample queries
The following query retrieves the names and load times of any files that took over 1,000,000 microseconds for Amazon Redshift to read.
```
select trim(name)as name, loadtime from stl_file_scan
where loadtime > 1000000;
```
This query returns the following example output.
```
name | loadtime
---------------------------+----------
listings_pipe.txt | 9458354
allusers_pipe.txt | 2963761
allevents_pipe.txt | 1409135
tickit/listings_pipe.txt | 7071087
tickit/allevents_pipe.txt | 1237364
tickit/allusers_pipe.txt | 2535138
listings_pipe.txt | 6706370
allusers_pipe.txt | 3579461
allevents_pipe.txt | 1313195
tickit/allusers_pipe.txt | 3236060
tickit/listings_pipe.txt | 4980108
(11 rows)
```
# STL\$1HASH
Analyzes hash execution steps for queries.
STL\$1HASH 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1HASH only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_HASH.html)
## Sample queries
The following example returns information about the number of partitions that were used in a hash for query 720, and indicates that none of the steps ran on disk.
```
select slice, rows, bytes, occupied, workmem, num_parts, est_rows, num_blocks_permitted, is_diskbased
from stl_hash
where query=720 and segment=5
order by slice;
```
```
slice | rows | bytes | occupied | workmem | num_parts | est_rows | num_blocks_permitted | is_diskbased
-------+------+--------+----------+----------+-----------+----------+----------------------+--------------
0 | 145 | 585800 | 1 | 88866816 | 16 | 1 | 52 f
1 | 0 | 0 | 0 | 0 | 16 | 1 | 52 f
(2 rows)
```
# STL\$1HASHJOIN
Analyzes hash join execution steps for queries.
STL\$1HASHJOIN 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1HASHJOIN only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_HASHJOIN.html)
## Sample queries
The following example returns the number of partitions used in a hash join for query 720.
```
select query, slice, tbl, num_parts
from stl_hashjoin
where query=720 limit 10;
```
```
query | slice | tbl | num_parts
-------+-------+-----+-----------
720 | 0 | 243 | 1
720 | 1 | 243 | 1
(2 rows)
```
# STL\$1INSERT
Analyzes insert execution steps for queries.
STL\$1INSERT 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1INSERT only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_INSERT.html)
## Sample queries
The following example returns insert execution steps for the most recent query.
```
select slice, segment, step, tasknum, rows, tbl
from stl_insert
where query=pg_last_query_id();
```
```
slice | segment | step | tasknum | rows | tbl
-------+---------+------+---------+-------+--------
0 | 2 | 2 | 15 | 24958 | 100548
1 | 2 | 2 | 15 | 25032 | 100548
(2 rows)
```
# STL\$1LIMIT
Analyzes the execution steps that occur when a LIMIT clause is used in a SELECT query.
STL\$1LIMIT 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1LIMIT only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_LIMIT.html)
## Sample queries
In order to generate a row in STL\$1LIMIT, this example first runs the following query against the VENUE table using the LIMIT clause.
```
select * from venue
order by 1
limit 10;
```
```
venueid | venuename | venuecity | venuestate | venueseats
---------+----------------------------+-----------------+------------+------------
1 | Toyota Park | Bridgeview | IL | 0
2 | Columbus Crew Stadium | Columbus | OH | 0
3 | RFK Stadium | Washington | DC | 0
4 | CommunityAmerica Ballpark | Kansas City | KS | 0
5 | Gillette Stadium | Foxborough | MA | 68756
6 | New York Giants Stadium | East Rutherford | NJ | 80242
7 | BMO Field | Toronto | ON | 0
8 | The Home Depot Center | Carson | CA | 0
9 | Dick's Sporting Goods Park | Commerce City | CO | 0
10 | Pizza Hut Park | Frisco | TX | 0
(10 rows)
```
Next, run the following query to find the query ID of the last query you ran against the VENUE table.
```
select max(query)
from stl_query;
```
```
max
--------
127128
(1 row)
```
Optionally, you can run the following query to verify that the query ID corresponds to the LIMIT query you previously ran.
```
select query, trim(querytxt)
from stl_query
where query=127128;
```
```
query | btrim
--------+------------------------------------------
127128 | select * from venue order by 1 limit 10;
(1 row)
```
Finally, run the following query to return information about the LIMIT query from the STL\$1LIMIT table.
```
select slice, segment, step, starttime, endtime, tasknum
from stl_limit
where query=127128
order by starttime, endtime;
```
```
slice | segment | step | starttime | endtime | tasknum
-------+---------+------+----------------------------+----------------------------+---------
1 | 1 | 3 | 2013-09-06 22:56:43.608114 | 2013-09-06 22:56:43.609383 | 15
0 | 1 | 3 | 2013-09-06 22:56:43.608708 | 2013-09-06 22:56:43.609521 | 15
10000 | 2 | 2 | 2013-09-06 22:56:43.612506 | 2013-09-06 22:56:43.612668 | 0
(3 rows)
```
# STL\$1LOAD\$1COMMITS
Returns information to track or troubleshoot a data load.
This view records the progress of each data file as it is loaded into a database table.
STL\$1LOAD\$1COMMITS 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1LOAD\$1COMMITS only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1LOAD\$1DETAIL](SYS_LOAD_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_COMMITS.html)
## Sample queries
The following example returns details for the last COPY operation.
```
select query, trim(filename) as file, curtime as updated
from stl_load_commits
where query = pg_last_copy_id();
query | file | updated
-------+----------------------------------+----------------------------
28554 | s3://dw-tickit/category_pipe.txt | 2013-11-01 17:14:52.648486
(1 row)
```
The following query contains entries for a fresh load of the tables in the TICKIT database:
```
select query, trim(filename), curtime
from stl_load_commits
where filename like '%tickit%' order by query;
```
```
query | btrim | curtime
-------+---------------------------+----------------------------
22475 | tickit/allusers_pipe.txt | 2013-02-08 20:58:23.274186
22478 | tickit/venue_pipe.txt | 2013-02-08 20:58:25.070604
22480 | tickit/category_pipe.txt | 2013-02-08 20:58:27.333472
22482 | tickit/date2008_pipe.txt | 2013-02-08 20:58:28.608305
22485 | tickit/allevents_pipe.txt | 2013-02-08 20:58:29.99489
22487 | tickit/listings_pipe.txt | 2013-02-08 20:58:37.632939
22593 | tickit/allusers_pipe.txt | 2013-02-08 21:04:08.400491
22596 | tickit/venue_pipe.txt | 2013-02-08 21:04:10.056055
22598 | tickit/category_pipe.txt | 2013-02-08 21:04:11.465049
22600 | tickit/date2008_pipe.txt | 2013-02-08 21:04:12.461502
22603 | tickit/allevents_pipe.txt | 2013-02-08 21:04:14.785124
22605 | tickit/listings_pipe.txt | 2013-02-08 21:04:20.170594
(12 rows)
```
The fact that a record is written to the log file for this system view does not mean that the load committed successfully as part of its containing transaction. To verify load commits, query the STL\$1UTILITYTEXT view and look for the COMMIT record that corresponds with a COPY transaction. For example, this query joins STL\$1LOAD\$1COMMITS and STL\$1QUERY based on a subquery against STL\$1UTILITYTEXT:
```
select l.query,rtrim(l.filename),q.xid
from stl_load_commits l, stl_query q
where l.query=q.query
and exists
(select xid from stl_utilitytext where xid=q.xid and rtrim("text")='COMMIT');
query | rtrim | xid
-------+---------------------------+-------
22600 | tickit/date2008_pipe.txt | 68311
22480 | tickit/category_pipe.txt | 68066
7508 | allusers_pipe.txt | 23365
7552 | category_pipe.txt | 23415
7576 | allevents_pipe.txt | 23429
7516 | venue_pipe.txt | 23390
7604 | listings_pipe.txt | 23445
22596 | tickit/venue_pipe.txt | 68309
22605 | tickit/listings_pipe.txt | 68316
22593 | tickit/allusers_pipe.txt | 68305
22485 | tickit/allevents_pipe.txt | 68071
7561 | allevents_pipe.txt | 23429
7541 | category_pipe.txt | 23415
7558 | date2008_pipe.txt | 23428
22478 | tickit/venue_pipe.txt | 68065
526 | date2008_pipe.txt | 2572
7466 | allusers_pipe.txt | 23365
22482 | tickit/date2008_pipe.txt | 68067
22598 | tickit/category_pipe.txt | 68310
22603 | tickit/allevents_pipe.txt | 68315
22475 | tickit/allusers_pipe.txt | 68061
547 | date2008_pipe.txt | 2572
22487 | tickit/listings_pipe.txt | 68072
7531 | venue_pipe.txt | 23390
7583 | listings_pipe.txt | 23445
(25 rows)
```
The following examples highlight is\$1partial and start\$1offset column values.
```
-- Single large file copy without scan range
SELECT count(*) FROM stl_load_commits WHERE query = pg_last_copy_id();
1
-- Single large uncompressed, delimited file copy with scan range
SELECT count(*) FROM stl_load_commits WHERE query = pg_last_copy_id();
16
-- Scan range offset logging in the file at 64MB boundary.
SELECT start_offset FROM stl_load_commits
WHERE query = pg_last_copy_id() ORDER BY start_offset;
0
67108864
134217728
201326592
268435456
335544320
402653184
469762048
536870912
603979776
671088640
738197504
805306368
872415232
939524096
1006632960
```
# STL\$1LOAD\$1ERRORS
Displays the records of all Amazon Redshift load errors.
STL\$1LOAD\$1ERRORS contains a history of all Amazon Redshift load errors. See [Load error reference](r_Load_Error_Reference.md) for a comprehensive list of possible load errors and explanations.
Query [STL\$1LOADERROR\$1DETAIL](r_STL_LOADERROR_DETAIL.md) for additional details, such as the exact data row and column where a parse error occurred, after you query STL\$1LOAD\$1ERRORS to find out general information about the error.
STL\$1LOAD\$1ERRORS 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1LOAD\$1ERRORS only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1LOAD\$1ERROR\$1DETAIL](SYS_LOAD_ERROR_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html)
## Sample queries
The following query joins STL\$1LOAD\$1ERRORS to STL\$1LOADERROR\$1DETAIL to view the details errors that occurred during the most recent load.
```
select d.query, substring(d.filename,14,20),
d.line_number as line,
substring(d.value,1,16) as value,
substring(le.err_reason,1,48) as err_reason
from stl_loaderror_detail d, stl_load_errors le
where d.query = le.query
and d.query = pg_last_copy_id();
query | substring | line | value | err_reason
-------+-------------------+------+----------+----------------------------
558| allusers_pipe.txt | 251 | 251 | String contains invalid or
unsupported UTF8 code
558| allusers_pipe.txt | 251 | ZRU29FGR | String contains invalid or
unsupported UTF8 code
558| allusers_pipe.txt | 251 | Kaitlin | String contains invalid or
unsupported UTF8 code
558| allusers_pipe.txt | 251 | Walter | String contains invalid or
unsupported UTF8 code
```
The following example uses STL\$1LOAD\$1ERRORS with STV\$1TBL\$1PERM to create a new view, and then uses that view to determine what errors occurred while loading data into the EVENT table:
```
create view loadview as
(select distinct tbl, trim(name) as table_name, query, starttime,
trim(filename) as input, line_number, colname, err_code,
trim(err_reason) as reason
from stl_load_errors sl, stv_tbl_perm sp
where sl.tbl = sp.id);
```
Next, the following query actually returns the last error that occurred while loading the EVENT table:
```
select table_name, query, line_number, colname, starttime,
trim(reason) as error
from loadview
where table_name ='event'
order by line_number limit 1;
```
The query returns the last load error that occurred for the EVENT table. If no load errors occurred, the query returns zero rows. In this example, the query returns a single error:
```
table_name | query | line_number | colname | error | starttime
------+-----+----+----+--------------------------------------------------------+----------------------
event | 309 | 0 | 5 | Error in Timestamp value or format [%Y-%m-%d %H:%M:%S] | 2014-04-22 15:12:44
(1 row)
```
In cases where the COPY command automatically splits large, uncompressed, text-delimited file data to facilitate parallelism, the *line\$1number*, *is\$1partial*, and *start\$1offset* columns show information pertaining to splits. (The line number can be unknown in cases where the line number from the original file is unavailable.)
```
--scan ranges information
SELECT line_number, POSITION, btrim(raw_line), btrim(raw_field_value),
btrim(err_reason), is_partial, start_offset FROM stl_load_errors
WHERE query = pg_last_copy_id();
--result
-1,51,"1008771|13463413|463414|2|28.00|38520.72|0.06|0.07|NO|1998-08-30|1998-09-25|1998-09-04|TAKE BACK RETURN|RAIL|ans cajole sly","NO","Char length exceeds DDL length",1,67108864
```
# STL\$1LOADERROR\$1DETAIL
Displays a log of data parse errors that occurred while using a COPY command to load tables. To conserve disk space, a maximum of 20 errors per node slice are logged for each load operation.
A parse error occurs when Amazon Redshift cannot parse a field in a data row while loading it into a table. For example, if a table column is expecting an integer data type and the data file contains a string of letters in that field, it causes a parse error.
Query STL\$1LOADERROR\$1DETAIL for additional details, such as the exact data row and column where a parse error occurred, after you query [STL\$1LOAD\$1ERRORS](r_STL_LOAD_ERRORS.md) to find out general information about the error.
The STL\$1LOADERROR\$1DETAIL view contains all data columns including and prior to the column where the parse error occurred. Use the VALUE field to see the data value that was actually parsed in this column, including the columns that parsed correctly up to the error.
This view 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1LOADERROR\$1DETAIL only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1LOAD\$1ERROR\$1DETAIL](SYS_LOAD_ERROR_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOADERROR_DETAIL.html)
## Sample query
The following query joins STL\$1LOAD\$1ERRORS to STL\$1LOADERROR\$1DETAIL to view the details of a parse error that occurred while loading the EVENT table, which has a table ID of 100133:
```
select d.query, d.line_number, d.value,
le.raw_line, le.err_reason
from stl_loaderror_detail d, stl_load_errors le
where
d.query = le.query
and tbl = 100133;
```
The following sample output shows the columns that loaded successfully, including the column with the error. In this example, two columns successfully loaded before the parse error occurred in the third column, where a character string was incorrectly parsed for a field expecting an integer. Because the field expected an integer, it parsed the string "aaa", which is uninitialized data, as a null and generated a parse error. The output shows the raw value, parsed value, and error reason:
```
query | line_number | value | raw_line | err_reason
-------+-------------+-------+----------+----------------
4 | 3 | 1201 | 1201 | Invalid digit
4 | 3 | 126 | 126 | Invalid digit
4 | 3 | | aaa | Invalid digit
(3 rows)
```
When a query joins STL\$1LOAD\$1ERRORS and STL\$1LOADERROR\$1DETAIL, it displays an error reason for each column in the data row, which simply means that an error occurred in that row. The last row in the results is the actual column where the parse error occurred.
# STL\$1MERGE
Analyzes merge execution steps for queries. These steps occur when the results of parallel operations (such as sorts and joins) are merged for subsequent processing.
STL\$1MERGE 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1MERGE only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_MERGE.html)
## Sample queries
The following example returns 10 merge execution results.
```
select query, step, starttime, endtime, tasknum, rows
from stl_merge
limit 10;
```
```
query | step | starttime | endtime | tasknum | rows
-------+------+---------------------+---------------------+---------+------
9 | 0 | 2013-08-12 20:08:14 | 2013-08-12 20:08:14 | 0 | 0
12 | 0 | 2013-08-12 20:09:10 | 2013-08-12 20:09:10 | 0 | 0
15 | 0 | 2013-08-12 20:10:24 | 2013-08-12 20:10:24 | 0 | 0
20 | 0 | 2013-08-12 20:11:27 | 2013-08-12 20:11:27 | 0 | 0
26 | 0 | 2013-08-12 20:12:28 | 2013-08-12 20:12:28 | 0 | 0
32 | 0 | 2013-08-12 20:14:33 | 2013-08-12 20:14:33 | 0 | 0
38 | 0 | 2013-08-12 20:16:43 | 2013-08-12 20:16:43 | 0 | 0
44 | 0 | 2013-08-12 20:17:05 | 2013-08-12 20:17:05 | 0 | 0
50 | 0 | 2013-08-12 20:18:48 | 2013-08-12 20:18:48 | 0 | 0
56 | 0 | 2013-08-12 20:20:48 | 2013-08-12 20:20:48 | 0 | 0
(10 rows)
```
# STL\$1MERGEJOIN
Analyzes merge join execution steps for queries.
STL\$1MERGEJOIN 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1MERGEJOIN only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_MERGEJOIN.html)
## Sample queries
The following example returns merge join results for the most recent query.
```
select sum(s.qtysold), e.eventname
from event e, listing l, sales s
where e.eventid=l.eventid
and l.listid= s.listid
group by e.eventname;
select * from stl_mergejoin where query=pg_last_query_id();
```
```
userid | query | slice | segment | step | starttime | endtime | tasknum | rows | tbl
--------+-------+-------+---------+------+---------------------+---------------------+---------+------+-----
100 | 27399 | 3 | 4 | 4 | 2013-10-02 16:30:41 | 2013-10-02 16:30:41 | 19 |43428 | 240
100 | 27399 | 0 | 4 | 4 | 2013-10-02 16:30:41 | 2013-10-02 16:30:41 | 19 |43159 | 240
100 | 27399 | 2 | 4 | 4 | 2013-10-02 16:30:41 | 2013-10-02 16:30:41 | 19 |42778 | 240
100 | 27399 | 1 | 4 | 4 | 2013-10-02 16:30:41 | 2013-10-02 16:30:41 | 19 |43091 | 240
```
# STL\$1MV\$1STATE
The STL\$1MV\$1STATE view contains a row for every state transition of a materialized view.
For more information about materialized views, see [Materialized views in Amazon Redshift](materialized-view-overview.md).
STL\$1MV\$1STATE 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1MV\$1STATE](SYS_MV_STATE.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_MV_STATE.html)
The following table shows example combinations of `event_desc` and `state`.
```
event_desc | state
-------------------------+---------------
TRUNCATE | Recompute
TRUNCATE | Recompute
Small table conversion | Recompute
Vacuum | Recompute
Column was renamed | Unrefreshable
Column was dropped | Unrefreshable
Table was renamed | Unrefreshable
Column type was changed | Unrefreshable
Schema name was changed | Unrefreshable
```
## Sample query
To view the log of state transitions of materialized views, run the following query.
```
select * from stl_mv_state;
```
This query returns the following sample output:
```
userid | starttime | xid | event_desc | db_name | base_table_schema | base_table_name | mv_schema | mv_name | state
--------+----------------------------+------+-----------------------------+---------+----------------------+----------------------+----------------------+---------------+---------------
138 | 2020-02-14 02:21:25.578885 | 5180 | TRUNCATE | dev | public | mv_base_table | public | mv_test | Recompute
138 | 2020-02-14 02:21:56.846774 | 5275 | Column was dropped | dev | | mv_base_table | public | mv_test | Unrefreshable
100 | 2020-02-13 22:09:53.041228 | 1794 | Column was renamed | dev | | mv_base_table | public | mv_test | Unrefreshable
1 | 2020-02-13 22:10:23.630914 | 1893 | ALTER TABLE ALTER SORTKEY | dev | public | mv_base_table_sorted | public | mv_test | Recompute
1 | 2020-02-17 22:57:22.497989 | 8455 | ALTER TABLE ALTER DISTSTYLE | dev | public | mv_base_table | public | mv_test | Recompute
173 | 2020-02-17 22:57:23.591434 | 8504 | Table was renamed | dev | | mv_base_table | public | mv_test | Unrefreshable
173 | 2020-02-17 22:57:27.229423 | 8592 | Column type was changed | dev | | mv_base_table | public | mv_test | Unrefreshable
197 | 2020-02-17 22:59:06.212569 | 9668 | TRUNCATE | dev | schemaf796e415850f4f | mv_base_table | schemaf796e415850f4f | mv_test | Recompute
138 | 2020-02-14 02:21:55.705655 | 5226 | Column was renamed | dev | | mv_base_table | public | mv_test | Unrefreshable
1 | 2020-02-14 02:22:26.292434 | 5325 | ALTER TABLE ALTER SORTKEY | dev | public | mv_base_table_sorted | public | mv_test | Recompute
```
# STL\$1NESTLOOP
Analyzes nested-loop join execution steps for queries.
STL\$1NESTLOOP 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1NESTLOOP only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_NESTLOOP.html)
## Sample queries
Because the following query neglects to join the CATEGORY table, it produces a partial Cartesian product, which is not recommended. It is shown here to illustrate a nested loop.
```
select count(event.eventname), event.eventname, category.catname, date.caldate
from event, category, date
where event.dateid = date.dateid
group by event.eventname, category.catname, date.caldate;
```
The following query shows the results from the previous query in the STL\$1NESTLOOP view.
```
select query, slice, segment as seg, step,
datediff(msec, starttime, endtime) as duration, tasknum, rows, tbl
from stl_nestloop
where query = pg_last_query_id();
```
```
query | slice | seg | step | duration | tasknum | rows | tbl
-------+-------+-----+------+----------+---------+-------+-----
6028 | 0 | 4 | 5 | 41 | 22 | 24277 | 240
6028 | 1 | 4 | 5 | 26 | 23 | 24189 | 240
6028 | 3 | 4 | 5 | 25 | 23 | 24376 | 240
6028 | 2 | 4 | 5 | 54 | 22 | 23936 | 240
```
# STL\$1PARSE
Analyzes query steps that parse strings into binary values for loading.
STL\$1PARSE 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1PARSE only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_PARSE.html)
## Sample queries
The following example returns all query step results for slice 1 and segment 0 where strings were parsed into binary values.
```
select query, step, starttime, endtime, tasknum, rows
from stl_parse
where slice=1 and segment=0;
```
```
query | step | starttime | endtime | tasknum | rows
-------+------+---------------------+---------------------+---------+--------
669 | 1 | 2013-08-12 22:35:13 | 2013-08-12 22:35:17 | 32 | 192497
696 | 1 | 2013-08-12 22:35:49 | 2013-08-12 22:35:49 | 32 | 0
525 | 1 | 2013-08-12 22:32:03 | 2013-08-12 22:32:03 | 13 | 49990
585 | 1 | 2013-08-12 22:33:18 | 2013-08-12 22:33:19 | 13 | 202
621 | 1 | 2013-08-12 22:34:03 | 2013-08-12 22:34:03 | 27 | 365
651 | 1 | 2013-08-12 22:34:47 | 2013-08-12 22:34:53 | 35 | 192497
590 | 1 | 2013-08-12 22:33:28 | 2013-08-12 22:33:28 | 19 | 0
599 | 1 | 2013-08-12 22:33:39 | 2013-08-12 22:33:39 | 31 | 11
675 | 1 | 2013-08-12 22:35:26 | 2013-08-12 22:35:27 | 38 | 3766
567 | 1 | 2013-08-12 22:32:47 | 2013-08-12 22:32:48 | 23 | 49990
630 | 1 | 2013-08-12 22:34:17 | 2013-08-12 22:34:17 | 36 | 0
572 | 1 | 2013-08-12 22:33:04 | 2013-08-12 22:33:04 | 29 | 0
645 | 1 | 2013-08-12 22:34:37 | 2013-08-12 22:34:38 | 29 | 8798
604 | 1 | 2013-08-12 22:33:47 | 2013-08-12 22:33:47 | 37 | 0
(14 rows)
```
# STL\$1PLAN\$1INFO
Use the STL\$1PLAN\$1INFO view to look at the EXPLAIN output for a query in terms of a set of rows. This is an alternative way to look at query plans.
STL\$1PLAN\$1INFO 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1PLAN\$1INFO only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_PLAN_INFO.html)
## Sample queries
The following examples compare the query plans for a simple SELECT query returned by using the EXPLAIN command and by querying the STL\$1PLAN\$1INFO view.
```
explain select * from category;
QUERY PLAN
-------------------------------------------------------------
XN Seq Scan on category (cost=0.00..0.11 rows=11 width=49)
(1 row)
select * from category;
catid | catgroup | catname | catdesc
-------+----------+-----------+--------------------------------------------
1 | Sports | MLB | Major League Baseball
3 | Sports | NFL | National Football League
5 | Sports | MLS | Major League Soccer
...
select * from stl_plan_info where query=256;
query | nodeid | segment | step | locus | plannode | startupcost | totalcost
| rows | bytes
-------+--------+---------+------+-------+----------+-------------+-----------+------+-------
256 | 1 | 0 | 1 | 0 | 104 | 0 | 0.11 | 11 | 539
256 | 1 | 0 | 0 | 0 | 104 | 0 | 0.11 | 11 | 539
(2 rows)
```
In this example, PLANNODE 104 refers to the sequential scan of the CATEGORY table.
```
select distinct eventname from event order by 1;
eventname
------------------------------------------------------------------------
.38 Special
3 Doors Down
70s Soul Jam
A Bronx Tale
...
explain select distinct eventname from event order by 1;
QUERY PLAN
-------------------------------------------------------------------------------------
XN Merge (cost=1000000000136.38..1000000000137.82 rows=576 width=17)
Merge Key: eventname
-> XN Network (cost=1000000000136.38..1000000000137.82 rows=576
width=17)
Send to leader
-> XN Sort (cost=1000000000136.38..1000000000137.82 rows=576
width=17)
Sort Key: eventname
-> XN Unique (cost=0.00..109.98 rows=576 width=17)
-> XN Seq Scan on event (cost=0.00..87.98 rows=8798
width=17)
(8 rows)
select * from stl_plan_info where query=240 order by nodeid desc;
query | nodeid | segment | step | locus | plannode | startupcost |
totalcost | rows | bytes
-------+--------+---------+------+-------+----------+------------------+------------------+------+--------
240 | 5 | 0 | 0 | 0 | 104 | 0 | 87.98 | 8798 | 149566
240 | 5 | 0 | 1 | 0 | 104 | 0 | 87.98 | 8798 | 149566
240 | 4 | 0 | 2 | 0 | 117 | 0 | 109.975 | 576 | 9792
240 | 4 | 0 | 3 | 0 | 117 | 0 | 109.975 | 576 | 9792
240 | 4 | 1 | 0 | 0 | 117 | 0 | 109.975 | 576 | 9792
240 | 4 | 1 | 1 | 0 | 117 | 0 | 109.975 | 576 | 9792
240 | 3 | 1 | 2 | 0 | 114 | 1000000000136.38 | 1000000000137.82 | 576 | 9792
240 | 3 | 2 | 0 | 0 | 114 | 1000000000136.38 | 1000000000137.82 | 576 | 9792
240 | 2 | 2 | 1 | 0 | 123 | 1000000000136.38 | 1000000000137.82 | 576 | 9792
240 | 1 | 3 | 0 | 0 | 122 | 1000000000136.38 | 1000000000137.82 | 576 | 9792
(10 rows)
```
# STL\$1PROJECT
Contains rows for query steps that are used to evaluate expressions.
STL\$1PROJECT 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1PROJECT only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_PROJECT.html)
## Sample queries
The following example returns all rows for query steps that were used to evaluate expressions for slice 0 and segment 1.
```
select query, step, starttime, endtime, tasknum, rows
from stl_project
where slice=0 and segment=1;
```
```
query | step | starttime | endtime | tasknum | rows
--------+------+---------------------+---------------------+---------+------
86399 | 2 | 2013-08-29 22:01:21 | 2013-08-29 22:01:21 | 25 | -1
86399 | 3 | 2013-08-29 22:01:21 | 2013-08-29 22:01:21 | 25 | -1
719 | 1 | 2013-08-12 22:38:33 | 2013-08-12 22:38:33 | 7 | -1
86383 | 1 | 2013-08-29 21:58:35 | 2013-08-29 21:58:35 | 7 | -1
714 | 1 | 2013-08-12 22:38:17 | 2013-08-12 22:38:17 | 2 | -1
86375 | 1 | 2013-08-29 21:57:59 | 2013-08-29 21:57:59 | 2 | -1
86397 | 2 | 2013-08-29 22:01:20 | 2013-08-29 22:01:20 | 19 | -1
627 | 1 | 2013-08-12 22:34:13 | 2013-08-12 22:34:13 | 34 | -1
86326 | 2 | 2013-08-29 21:45:28 | 2013-08-29 21:45:28 | 34 | -1
86326 | 3 | 2013-08-29 21:45:28 | 2013-08-29 21:45:28 | 34 | -1
86325 | 2 | 2013-08-29 21:45:27 | 2013-08-29 21:45:27 | 28 | -1
86371 | 1 | 2013-08-29 21:57:42 | 2013-08-29 21:57:42 | 4 | -1
111100 | 2 | 2013-09-03 19:04:45 | 2013-09-03 19:04:45 | 12 | -1
704 | 2 | 2013-08-12 22:36:34 | 2013-08-12 22:36:34 | 37 | -1
649 | 2 | 2013-08-12 22:34:47 | 2013-08-12 22:34:47 | 38 | -1
649 | 3 | 2013-08-12 22:34:47 | 2013-08-12 22:34:47 | 38 | -1
632 | 2 | 2013-08-12 22:34:22 | 2013-08-12 22:34:22 | 13 | -1
705 | 2 | 2013-08-12 22:36:48 | 2013-08-12 22:36:49 | 13 | -1
705 | 3 | 2013-08-12 22:36:48 | 2013-08-12 22:36:49 | 13 | -1
3 | 1 | 2013-08-12 20:07:40 | 2013-08-12 20:07:40 | 3 | -1
86373 | 1 | 2013-08-29 21:57:58 | 2013-08-29 21:57:58 | 3 | -1
107976 | 1 | 2013-09-03 04:05:12 | 2013-09-03 04:05:12 | 3 | -1
86381 | 1 | 2013-08-29 21:58:35 | 2013-08-29 21:58:35 | 8 | -1
86396 | 1 | 2013-08-29 22:01:20 | 2013-08-29 22:01:20 | 15 | -1
711 | 1 | 2013-08-12 22:37:10 | 2013-08-12 22:37:10 | 20 | -1
86324 | 1 | 2013-08-29 21:45:27 | 2013-08-29 21:45:27 | 24 | -1
(26 rows)
```
# STL\$1QUERY
Returns execution information about a database query.
**Note**
The STL\$1QUERY and STL\$1QUERYTEXT views only contain information about queries, not other utility and DDL commands. For a listing and information on all statements run by Amazon Redshift, you can also query the STL\$1DDLTEXT and STL\$1UTILITYTEXT views. For a complete listing of all statements run by Amazon Redshift, you can query the SVL\$1STATEMENTTEXT view.
STL\$1QUERY 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
Note that when your query text length is over 4000 characters, STL\$1QUERY only displays truncated data. To get the full query text, you can use UNION on the query text across rows.
**Note**
To verify whether a transaction containing the executed query was successfully committed, you need to perform a join operation between system tables and the `sys_transaction_history` table. For example:
```
SELECT
stlq.xid AS transaction_id,
stlq.query AS query_id,
TRIM(stlq.querytxt) AS query_text,
th.status AS transaction_status
FROM
stl_query stlq
LEFT JOIN
sys_transaction_history th ON stlq.xid = th.transaction_id;
```
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERY.html)
## Sample queries
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-16 00:00'
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)
```
The following query shows the queue time and execution time for queries. Queries with `concurrency_scaling_status = 1` ran on a concurrency scaling cluster. All other queries ran on the main cluster.
```
SELECT w.service_class AS queue
, q.concurrency_scaling_status
, COUNT( * ) AS queries
, SUM( q.aborted ) AS aborted
, SUM( ROUND( total_queue_time::NUMERIC / 1000000,2 ) ) AS queue_secs
, SUM( ROUND( total_exec_time::NUMERIC / 1000000,2 ) ) AS exec_secs
FROM stl_query q
JOIN stl_wlm_query w
USING (userid,query)
WHERE q.userid > 1
AND service_class > 5
AND q.starttime > '2019-03-01 16:38:00'
AND q.endtime < '2019-03-01 17:40:00'
GROUP BY 1,2
ORDER BY 1,2;
```
# STL\$1QUERY\$1METRICS
Contains metrics information, such as the number of rows processed, CPU usage, input/output, and disk use, for queries that have completed running in user-defined query queues (service classes). To view metrics for active queries that are currently running, see the [STV\$1QUERY\$1METRICS](r_STV_QUERY_METRICS.md) system view.
Query metrics are sampled at one second intervals. As a result, different runs of the same query might return slightly different times. Also, query segments that run in less than one second might not be recorded.
STL\$1QUERY\$1METRICS tracks and aggregates metrics at the query, segment, and step level. For information about query segments and steps, see [Query planning and execution workflow](c-query-planning.md). Many metrics (such as `max_rows`, `cpu_time`, and so on) are summed across node slices. For more information about node slices, see [Data warehouse system architecture](c_high_level_system_architecture.md).
To determine the level at which the row reports metrics, examine the `segment` and `step_type` columns.
+ If both `segment` and `step_type` are `-1`, then the row reports metrics at the query level.
+ If `segment` is not `-1` and `step_type` is `-1`, then the row reports metrics at the segment level.
+ If both `segment` and `step_type` are not `-1`, then the row reports metrics at the step level.
The [SVL\$1QUERY\$1METRICS](r_SVL_QUERY_METRICS.md) view and the [SVL\$1QUERY\$1METRICS\$1SUMMARY](r_SVL_QUERY_METRICS_SUMMARY.md) view aggregate the data in this view and present the information in a more accessible form.
STL\$1QUERY\$1METRICS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERY_METRICS.html)
## Sample query
To find queries with high CPU time (more the 1,000 seconds), run the following query.
```
Select query, cpu_time / 1000000 as cpu_seconds
from stl_query_metrics where segment = -1 and cpu_time > 1000000000
order by cpu_time;
query | cpu_seconds
------+------------
25775 | 9540
```
To find active queries with a nested loop join that returned more than one million rows, run the following query.
```
select query, rows
from stl_query_metrics
where step_type = 15 and rows > 1000000
order by rows;
query | rows
------+-----------
25775 | 2621562702
```
To find active queries that have run for more than 60 seconds and have used less than 10 seconds of CPU time, run the following query.
```
select query, run_time/1000000 as run_time_seconds
from stl_query_metrics
where segment = -1 and run_time > 60000000 and cpu_time < 10000000;
query | run_time_seconds
------+-----------------
25775 | 114
```
# STL\$1QUERYTEXT
Captures the query text for SQL commands.
Query the STL\$1QUERYTEXT view to capture the SQL that was logged for the following statements:
+ SELECT, SELECT INTO
+ INSERT, UPDATE, DELETE
+ COPY
+ UNLOAD
+ The queries generated by running VACUUM and ANALYZE
+ CREATE TABLE AS (CTAS)
To query activity for these statements over a given time period, join the STL\$1QUERYTEXT and STL\$1QUERY views.
**Note**
The STL\$1QUERY and STL\$1QUERYTEXT views only contain information about queries, not other utility and DDL commands. For a listing and information on all statements run by Amazon Redshift, you can also query the STL\$1DDLTEXT and STL\$1UTILITYTEXT views. For a complete listing of all statements run by Amazon Redshift, you can query the SVL\$1STATEMENTTEXT view.
See also [STL\$1DDLTEXT](r_STL_DDLTEXT.md), [STL\$1UTILITYTEXT](r_STL_UTILITYTEXT.md), and [SVL\$1STATEMENTTEXT](r_SVL_STATEMENTTEXT.md).
STL\$1QUERYTEXT 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1TEXT](SYS_QUERY_TEXT.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
Note that when your query text length is over 4000 characters, STL\$1QUERYTEXT only displays truncated data. To get the full query text, you can use UNION on the query text across rows.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERYTEXT.html)
## Sample queries
You can use the PG\$1BACKEND\$1PID() function to retrieve information for the current session. For example, the following query returns the query ID and a portion of the query text for queries completed in the current session.
```
select query, substring(text,1,60)
from stl_querytext
where pid = pg_backend_pid()
order by query desc;
query | substring
-------+--------------------------------------------------------------
28262 | select query, substring(text,1,80) from stl_querytext where
28252 | select query, substring(path,0,80) as path from stl_unload_l
28248 | copy category from 's3://dw-tickit/manifest/category/1030_ma
28247 | Count rows in target table
28245 | unload ('select * from category') to 's3://dw-tickit/manifes
28240 | select query, substring(text,1,40) from stl_querytext where
(6 rows)
```
### Reconstructing stored SQL
To reconstruct the SQL stored in the `text` column of STL\$1QUERYTEXT, run a SELECT statement to create SQL from 1 or more parts in the `text` column. Before running the reconstructed SQL, replace any (`\n`) special characters with a new line. The result of the following SELECT statement is rows of reconstructed SQL in the `query_statement` field.
```
SELECT query, LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) as query_statement, COUNT(*) as row_count
FROM stl_querytext GROUP BY query ORDER BY query desc;
```
For example, the following query selects 3 columns. The query itself is longer than 200 characters and is stored in parts in STL\$1QUERYTEXT.
```
select
1 AS a0123456789012345678901234567890123456789012345678901234567890,
2 AS b0123456789012345678901234567890123456789012345678901234567890,
3 AS b012345678901234567890123456789012345678901234
FROM stl_querytext;
```
In this example, the query is stored in 2 parts (rows) in the `text` column of STL\$1QUERYTEXT.
```
select query, sequence, text
from stl_querytext where query=pg_last_query_id() order by query desc, sequence limit 10;
```
```
query | sequence | text
-------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
45 | 0 | select\n1 AS a0123456789012345678901234567890123456789012345678901234567890,\n2 AS b0123456789012345678901234567890123456789012345678901234567890,\n3 AS b012345678901234567890123456789012345678901234
45 | 1 | \nFROM stl_querytext;
```
To reconstruct the SQL stored in STL\$1QUERYTEXT, run the following SQL.
```
select LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END, '') within group (order by sequence) AS text
from stl_querytext where query=pg_last_query_id();
```
To use the resulting reconstructed SQL in your client, replace any (`\n`) special characters with a new line.
```
text
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select\n1 AS a0123456789012345678901234567890123456789012345678901234567890,\n2 AS b0123456789012345678901234567890123456789012345678901234567890,\n3 AS b012345678901234567890123456789012345678901234\nFROM stl_querytext;
```
# STL\$1REPLACEMENTS
Displays a log that records when invalid UTF-8 characters were replaced by the [COPY](r_COPY.md) command with the ACCEPTINVCHARS option. A log entry is added to STL\$1REPLACEMENTS for each of the first 100 rows on each node slice that required at least one replacement.
STL\$1REPLACEMENTS 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1NESTLOOP only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1COPY\$1REPLACEMENTS](SYS_COPY_REPLACEMENTS.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_REPLACEMENTS.html)
## Sample queries
The following example returns replacements for the most recent COPY operation.
```
select query, session, filename, line_number, colname
from stl_replacements
where query = pg_last_copy_id();
query | session | filename | line_number | colname
------+---------+---------------------------------------------+-------------+--------
96 | 6314 | s3://DOC-EXAMPLE-BUCKET/allusers_pipe.txt | 251 | city
96 | 6314 | s3://DOC-EXAMPLE-BUCKET/allusers_pipe.txt | 317 | city
96 | 6314 | s3://DOC-EXAMPLE-BUCKET/allusers_pipe.txt | 569 | city
96 | 6314 | s3://DOC-EXAMPLE-BUCKET/allusers_pipe.txt | 623 | city
96 | 6314 | s3://DOC-EXAMPLE-BUCKET/allusers_pipe.txt | 694 | city
...
```
# STL\$1RESTARTED\$1SESSIONS
To maintain continuous availability following certain internal events, Amazon Redshift might restart an active session with a new process ID (PID). When Amazon Redshift restarts a session, STL\$1RESTARTED\$1SESSIONS records the new PID and the old PID.
For more information, see the examples following in this section.
STL\$1RESTARTED\$1SESSIONS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1SESSION\$1HISTORY](SYS_SESSION_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_RESTARTED_SESSIONS.html)
## Sample queries
The following example joins STL\$1RESTARTED\$1SESSIONS with STL\$1SESSIONS to show user names for sessions that have been restarted.
```
select process, stl_restarted_sessions.newpid, user_name
from stl_sessions
inner join stl_restarted_sessions on stl_sessions.process = stl_restarted_sessions.oldpid
order by process;
...
```
# STL\$1RETURN
Contains details for *return* steps in queries. A return step returns the results of queries completed on the compute nodes to the leader node. The leader node then merges the data and returns the results to the requesting client. For queries completed on the leader node, a return step returns results to the client.
A query consists of multiple segments, and each segment consists of one or more steps. For more information, see [Query processing](c-query-processing.md).
STL\$1RETURN 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1RETURN only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_RETURN.html)
## Sample queries
The following query shows which steps in the most recent query were performed on each slice.
```
SELECT query, slice, segment, step, endtime, rows, packets
from stl_return where query = pg_last_query_id();
query | slice | segment | step | endtime | rows | packets
-------+--------+---------+------+----------------------------+------+---------
4 | 2 | 3 | 2 | 2013-12-27 01:43:21.469043 | 3 | 0
4 | 3 | 3 | 2 | 2013-12-27 01:43:21.473321 | 0 | 0
4 | 0 | 3 | 2 | 2013-12-27 01:43:21.469118 | 2 | 0
4 | 1 | 3 | 2 | 2013-12-27 01:43:21.474196 | 0 | 0
4 | 4 | 3 | 2 | 2013-12-27 01:43:21.47704 | 2 | 0
4 | 5 | 3 | 2 | 2013-12-27 01:43:21.478593 | 0 | 0
4 | 12811| 4 | 1 | 2013-12-27 01:43:21.480755 | 0 | 0
(7 rows)
```
# STL\$1S3CLIENT
Records transfer time and other performance metrics.
Use the STL\$1S3CLIENT table to find the time spent transferring data from Amazon S3.
STL\$1S3CLIENT 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_S3CLIENT.html)
## Sample query
The following query returns the time taken to load files using a COPY command.
```
select slice, key, transfer_time
from stl_s3client
where query = pg_last_copy_id();
```
Result
```
slice | key | transfer_time
------+-----------------------------+---------------
0 | listing10M0003_part_00 | 16626716
1 | listing10M0001_part_00 | 12894494
2 | listing10M0002_part_00 | 14320978
3 | listing10M0000_part_00 | 11293439
3371 | prefix=listing10M;marker= | 99395
```
The following query converts the `start_time` and `end_time` to a timestamp.
```
select userid,query,slice,pid,recordtime,start_time,end_time,
'2000-01-01'::timestamp + (start_time/1000000.0)* interval '1 second' as start_ts,
'2000-01-01'::timestamp + (end_time/1000000.0)* interval '1 second' as end_ts
from stl_s3client where query> -1 limit 5;
```
```
userid | query | slice | pid | recordtime | start_time | end_time | start_ts | end_ts
--------+-------+-------+-------+----------------------------+-----------------+-----------------+----------------------------+----------------------------
0 | 0 | 0 | 23449 | 2019-07-14 16:27:17.207839 | 616436837154256 | 616436837207838 | 2019-07-14 16:27:17.154256 | 2019-07-14 16:27:17.207838
0 | 0 | 0 | 23449 | 2019-07-14 16:27:17.252521 | 616436837208208 | 616436837252520 | 2019-07-14 16:27:17.208208 | 2019-07-14 16:27:17.25252
0 | 0 | 0 | 23449 | 2019-07-14 16:27:17.284376 | 616436837208460 | 616436837284374 | 2019-07-14 16:27:17.20846 | 2019-07-14 16:27:17.284374
0 | 0 | 0 | 23449 | 2019-07-14 16:27:17.285307 | 616436837208980 | 616436837285306 | 2019-07-14 16:27:17.20898 | 2019-07-14 16:27:17.285306
0 | 0 | 0 | 23449 | 2019-07-14 16:27:17.353853 | 616436837302216 | 616436837353851 | 2019-07-14 16:27:17.302216 | 2019-07-14 16:27:17.353851
```
# STL\$1S3CLIENT\$1ERROR
Records errors encountered by a slice while loading a file from Amazon S3.
Use the STL\$1S3CLIENT\$1ERROR to find details for errors encountered while transferring data from Amazon S3 as part of a COPY command.
STL\$1S3CLIENT\$1ERROR 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_S3CLIENT_ERROR.html)
## Usage notes
If you see multiple errors with "Connection timed out", you might have a networking issue. If you're using Enhanced VPC Routing, verify that you have a valid network path between your cluster's VPC and your data resources. For more information, see [Amazon Redshift Enhanced VPC Routing](https://docs.aws.amazon.com/redshift/latest/mgmt/enhanced-vpc-routing.html).
## Sample query
The following query returns the errors from COPY commands completed during the current session.
```
select query, sliceid, substring(key from 1 for 20) as file,
substring(error from 1 for 35) as error
from stl_s3client_error
where pid = pg_backend_pid()
order by query desc;
```
Result
```
query | sliceid | file | error
--------+---------+--------------------+------------------------------------
362228 | 12 | part.tbl.25.159.gz | transfer closed with 1947655 bytes
362228 | 24 | part.tbl.15.577.gz | transfer closed with 1881910 bytes
362228 | 7 | part.tbl.22.600.gz | transfer closed with 700143 bytes r
362228 | 22 | part.tbl.3.34.gz | transfer closed with 2334528 bytes
362228 | 11 | part.tbl.30.274.gz | transfer closed with 699031 bytes r
362228 | 30 | part.tbl.5.509.gz | Unknown SSL protocol error in conne
361999 | 10 | part.tbl.23.305.gz | transfer closed with 698959 bytes r
361999 | 19 | part.tbl.26.582.gz | transfer closed with 1881458 bytes
361999 | 4 | part.tbl.15.629.gz | transfer closed with 2275907 bytes
361999 | 20 | part.tbl.6.456.gz | transfer closed with 692162 bytes r
(10 rows)
```
# STL\$1SAVE
Contains details for *save* steps in queries. A save step saves the input stream to a transient table. A transient table is a temporary table that stores intermediate results during query execution.
A query consists of multiple segments, and each segment consists of one or more steps. For more information, see [Query processing](c-query-processing.md).
STL\$1SAVE 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1SAVE only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_SAVE.html)
## Sample queries
The following query shows which save steps in the most recent query were performed on each slice.
```
select query, slice, segment, step, tasknum, rows, tbl
from stl_save where query = pg_last_query_id();
query | slice | segment | step | tasknum | rows | tbl
-------+-------+---------+------+---------+------+-----
52236 | 3 | 0 | 2 | 21 | 0 | 239
52236 | 2 | 0 | 2 | 20 | 0 | 239
52236 | 2 | 2 | 2 | 20 | 0 | 239
52236 | 3 | 2 | 2 | 21 | 0 | 239
52236 | 1 | 0 | 2 | 21 | 0 | 239
52236 | 0 | 0 | 2 | 20 | 0 | 239
52236 | 0 | 2 | 2 | 20 | 0 | 239
52236 | 1 | 2 | 2 | 21 | 0 | 239
(8 rows)
```
# STL\$1SCAN
Analyzes table scan steps for queries. The step number for rows in this table is always 0 because a scan is the first step in a segment.
STL\$1SCAN 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1SCAN only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_SCAN.html)
## Scan types
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_SCAN.html)
## Usage notes
Ideally `rows` should be relatively close to `rows_pre_filter`. A large difference between `rows` and `rows_pre_filter` is an indication that the execution engine is scanning rows that are later discarded, which is inefficient. The difference between `rows_pre_filter` and `rows_pre_user_filter` is the number of ghost rows in the scan. Run a VACUUM to remove rows marked for deletion. The difference between `rows` and `rows_pre_user_filter` is the number of rows filtered by the query. If a lot of rows are discarded by the user filter, review your choice of sort column or, if this is due to a large unsorted region, run a vacuum.
## Sample queries
The following example shows that `rows_pre_filter` is larger than `rows_pre_user_filter` because the table has deleted rows that have not been vacuumed (ghost rows).
```
SELECT query, slice, segment,step,rows, rows_pre_filter, rows_pre_user_filter
from stl_scan where query = pg_last_query_id();
query | slice | segment | step | rows | rows_pre_filter | rows_pre_user_filter
-------+--------+---------+------+-------+-----------------+----------------------
42915 | 0 | 0 | 0 | 43159 | 86318 | 43159
42915 | 0 | 1 | 0 | 1 | 0 | 0
42915 | 1 | 0 | 0 | 43091 | 86182 | 43091
42915 | 1 | 1 | 0 | 1 | 0 | 0
42915 | 2 | 0 | 0 | 42778 | 85556 | 42778
42915 | 2 | 1 | 0 | 1 | 0 | 0
42915 | 3 | 0 | 0 | 43428 | 86856 | 43428
42915 | 3 | 1 | 0 | 1 | 0 | 0
42915 | 10000 | 2 | 0 | 4 | 0 | 0
(9 rows)
```
# STL\$1SCHEMA\$1QUOTA\$1VIOLATIONS
Records the occurrence, timestamp, XID, and other useful information when a schema quota is exceeded.
STL\$1SCHEMA\$1QUOTA\$1VIOLATIONS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1SCHEMA\$1QUOTA\$1VIOLATIONS](r_SYS_SCHEMA_QUOTA_VIOLATIONS.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_SCHEMA_QUOTA_VIOLATIONS.html)
## Sample queries
The following query shows the result of quota violation:
```
SELECT userid, TRIM(SCHEMA_NAME) "schema_name", quota, disk_usage, disk_usage_pct, timestamp FROM
stl_schema_quota_violations WHERE SCHEMA_NAME = 'sales_schema' ORDER BY timestamp DESC;
```
This query returns the following sample output for the specified schema:
```
userid | schema_name | quota | disk_usage | disk_usage_pct |timestamp
-------+--------------+-------+------------+----------------+----------------------------
104 | sales_schema | 2048 | 2798 | 136.62 | 2020-04-20 20:09:25.494723
(1 row)
```
# STL\$1SESSIONS
Returns information about user session history.
STL\$1SESSIONS differs from STV\$1SESSIONS in that STL\$1SESSIONS contains session history, where STV\$1SESSIONS contains the current active sessions.
STL\$1SESSIONS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1SESSION\$1HISTORY](SYS_SESSION_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_SESSIONS.html)
## Sample queries
To view session history for the TICKIT database, type the following query:
```
select starttime, process, user_name, timeout_sec, timed_out
from stl_sessions
where db_name='tickit' order by starttime;
```
This query returns the following sample output:
```
starttime | process | user_name | timeout_sec | timed_out
---------------------------+---------+------------------------+-------------+-------------
2008-09-15 09:54:06.746705 | 32358 | dwuser | 120 | 1
2008-09-15 09:56:34.30275 | 32744 | dwuser | 60 | 1
2008-09-15 11:20:34.694837 | 14906 | dwuser | 0 | 0
2008-09-15 11:22:16.749818 | 15148 | dwuser | 0 | 0
2008-09-15 14:32:44.66112 | 14031 | dwuser | 0 | 0
2008-09-15 14:56:30.22161 | 18380 | dwuser | 0 | 0
2008-09-15 15:28:32.509354 | 24344 | dwuser | 0 | 0
2008-09-15 16:01:00.557326 | 30153 | dwuser | 120 | 1
2008-09-15 17:28:21.419858 | 12805 | dwuser | 0 | 0
2008-09-15 20:58:37.601937 | 14951 | dwuser | 60 | 1
2008-09-16 11:12:30.960564 | 27437 | dwuser | 60 | 1
2008-09-16 14:11:37.639092 | 23790 | dwuser | 3600 | 1
2008-09-16 15:13:46.02195 | 1355 | dwuser | 120 | 1
2008-09-16 15:22:36.515106 | 2878 | dwuser | 120 | 1
2008-09-16 15:44:39.194579 | 6470 | dwuser | 120 | 1
2008-09-16 16:50:27.02138 | 17254 | dwuser | 120 | 1
2008-09-17 12:05:02.157208 | 8439 | dwuser | 3600 | 0
(17 rows)
```
# STL\$1SORT
Displays sort execution steps for queries, such as steps that use ORDER BY processing.
STL\$1SORT 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1SORT only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_SORT.html)
## Sample queries
The following example returns sort results for slice 0 and segment 1.
```
select query, bytes, tbl, is_diskbased, workmem
from stl_sort
where slice=0 and segment=1;
```
```
query | bytes | tbl | is_diskbased | workmem
-------+---------+-----+--------------+-----------
567 | 3126968 | 241 | f | 383385600
604 | 5292 | 242 | f | 383385600
675 | 104776 | 251 | f | 383385600
525 | 3126968 | 251 | f | 383385600
585 | 5068 | 241 | f | 383385600
630 | 204808 | 266 | f | 383385600
704 | 0 | 242 | f | 0
669 | 4606416 | 241 | f | 383385600
696 | 104776 | 241 | f | 383385600
651 | 4606416 | 254 | f | 383385600
632 | 0 | 256 | f | 0
599 | 396 | 241 | f | 383385600
86397 | 0 | 242 | f | 0
621 | 5292 | 241 | f | 383385600
86325 | 0 | 242 | f | 0
572 | 5068 | 242 | f | 383385600
645 | 204808 | 241 | f | 383385600
590 | 396 | 242 | f | 383385600
(18 rows)
```
# STL\$1SSHCLIENT\$1ERROR
Records all errors seen by the SSH client.
STL\$1SSHCLIENT\$1ERROR 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_SSHCLIENT_ERROR.html)
# STL\$1STREAM\$1SEGS
Lists the relationship between streams and concurrent segments.
Streams in this context are Amazon Redshift streams. This system view doesn't pertain to [Streaming ingestion to a materialized view](materialized-view-streaming-ingestion.md).
STL\$1STREAM\$1SEGS 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1STREAM\$1SEGS only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_STREAM_SEGS.html)
## Sample queries
To view the relationship between streams and concurrent segments for the most recent query, type the following query:
```
select *
from stl_stream_segs
where query = pg_last_query_id();
query | stream | segment
-------+--------+---------
10 | 1 | 2
10 | 0 | 0
10 | 2 | 4
10 | 1 | 3
10 | 0 | 1
(5 rows)
```
# STL\$1TR\$1CONFLICT
Displays information to identify and resolve transaction conflicts with database tables.
A transaction conflict occurs when two or more users are querying and modifying data rows from tables such that their transactions cannot be serialized. The transaction that runs a statement that would break serializability is stopped and rolled back. Every time a transaction conflict occurs, Amazon Redshift writes a data row to the STL\$1TR\$1CONFLICT system table containing details about the canceled transaction. For more information, see [Isolation levels in Amazon Redshift](c_serial_isolation.md).
STL\$1TR\$1CONFLICT is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1TRANSACTION\$1HISTORY](SYS_TRANSACTION_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_TR_CONFLICT.html)
## Sample query
To return information about conflicts that involved a particular table, run a query that specifies the table ID:
```
select * from stl_tr_conflict where table_id=100234
order by xact_start_ts;
xact_id|process_| xact_start_ts | abort_time |table_
|id | | |id
-------+--------+--------------------------+--------------------------+------
1876 | 8551 |2010-03-30 09:19:15.852326|2010-03-30 09:20:17.582499|100234
1928 | 15034 |2010-03-30 13:20:00.636045|2010-03-30 13:20:47.766817|100234
1991 | 23753 |2010-04-01 13:05:01.220059|2010-04-01 13:06:06.94098 |100234
2002 | 23679 |2010-04-01 13:17:05.173473|2010-04-01 13:18:27.898655|100234
(4 rows)
```
You can get the table ID from the DETAIL section of the error message for serializability violations (error 1023).
# STL\$1UNDONE
Displays information about transactions that have been undone.
STL\$1UNDONE 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1TRANSACTION\$1HISTORY](SYS_TRANSACTION_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_UNDONE.html)
## Sample query
To view a concise log of all undone transactions, type the following command:
```
select xact_id, xact_id_undone, table_id from stl_undone;
```
This command returns the following sample output:
```
xact_id | xact_id_undone | table_id
---------+----------------+----------
1344 | 1344 | 100192
1326 | 1326 | 100192
1551 | 1551 | 100192
(3 rows)
```
# STL\$1UNIQUE
Analyzes execution steps that occur when a DISTINCT function is used in the SELECT list or when duplicates are removed in a UNION or INTERSECT query.
STL\$1UNIQUE 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1UNIQUE only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_UNIQUE.html)
## Sample queries
Suppose you run the following query:
```
select distinct eventname
from event order by 1;
```
Assuming the ID for the previous query is 6313, the following example shows the number of rows produced by the unique step for each slice in segments 0 and 1.
```
select query, slice, segment, step, datediff(msec, starttime, endtime) as msec, tasknum, rows
from stl_unique where query = 6313
order by query desc, slice, segment, step;
```
```
query | slice | segment | step | msec | tasknum | rows
-------+-------+---------+------+------+---------+------
6313 | 0 | 0 | 2 | 0 | 22 | 550
6313 | 0 | 1 | 1 | 256 | 20 | 145
6313 | 1 | 0 | 2 | 1 | 23 | 540
6313 | 1 | 1 | 1 | 42 | 21 | 127
6313 | 2 | 0 | 2 | 1 | 22 | 540
6313 | 2 | 1 | 1 | 255 | 20 | 158
6313 | 3 | 0 | 2 | 1 | 23 | 542
6313 | 3 | 1 | 1 | 38 | 21 | 146
(8 rows)
```
# STL\$1UNLOAD\$1LOG
Records the details for an unload operation.
STL\$1UNLOAD\$1LOG records one row for each file created by an UNLOAD statement. For example, if an UNLOAD creates 12 files, STL\$1UNLOAD\$1LOG will contain 12 corresponding rows.
STL\$1UNLOAD\$1LOG 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1UNLOAD\$1LOG only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1UNLOAD\$1HISTORY](SYS_UNLOAD_HISTORY.md) and [SYS\$1UNLOAD\$1DETAIL](SYS_UNLOAD_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_UNLOAD_LOG.html)
## Sample query
To get a list of the files that were written to Amazon S3 by an UNLOAD command, you can call an Amazon S3 list operation after the UNLOAD completes. You can also query STL\$1UNLOAD\$1LOG.
The following query returns the pathname for files that were created by an UNLOAD for the last query completed:
```
select query, substring(path,0,40) as path
from stl_unload_log
where query = pg_last_query_id()
order by path;
```
This command returns the following sample output:
```
query | path
-------+--------------------------------------
2320 | s3://amzn-s3-demo-bucket/venue0000_part_00
2320 | s3://amzn-s3-demo-bucket/venue0001_part_00
2320 | s3://amzn-s3-demo-bucket/venue0002_part_00
2320 | s3://amzn-s3-demo-bucket/venue0003_part_00
(4 rows)
```
# STL\$1USAGE\$1CONTROL
The STL\$1USAGE\$1CONTROL view contains information that is logged when a usage limit is reached. For more information about usage limits, see [Managing Usage Limits](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-cluster-usage-limits.html) in the *Amazon Redshift Management Guide*.
STL\$1USAGE\$1CONTROL is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_USAGE_CONTROL.html)
## Sample query
The following SQL example returns some of the information logged when a usage limit is reached.
```
select query, pid, eventtime, feature_type
from stl_usage_control
order by eventtime desc
limit 5;
```
# STL\$1USERLOG
Records details for the following changes to a database user:
+ Create user
+ Drop user
+ Alter user (rename)
+ Alter user (alter properties)
STL\$1USERLOG is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1USERLOG](SYS_USERLOG.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_USERLOG.html)
## Sample queries
The following example performs four user actions, then queries the STL\$1USERLOG view.
```
create user userlog1 password 'Userlog1';
alter user userlog1 createdb createuser;
alter user userlog1 rename to userlog2;
drop user userlog2;
select userid, username, oldusername, action, usecreatedb, usesuper from stl_userlog order by recordtime desc;
```
```
userid | username | oldusername | action | usecreatedb | usesuper
--------+-----------+-------------+---------+-------------+----------
108 | userlog2 | | drop | 1 | 1
108 | userlog2 | userlog1 | rename | 1 | 1
108 | userlog1 | | alter | 1 | 1
108 | userlog1 | | create | 0 | 0
(4 rows)
```
# STL\$1UTILITYTEXT
Captures the text of non-SELECT SQL commands run on the database.
Query the STL\$1UTILITYTEXT view to capture the following subset of SQL statements that were run on the system:
+ ABORT, BEGIN, COMMIT, END, ROLLBACK
+ ANALYZE
+ CALL
+ CANCEL
+ COMMENT
+ CREATE, ALTER, DROP DATABASE
+ CREATE, ALTER, DROP USER
+ EXPLAIN
+ GRANT, REVOKE
+ LOCK
+ RESET
+ SET
+ SHOW
+ TRUNCATE
See also [STL\$1DDLTEXT](r_STL_DDLTEXT.md), [STL\$1QUERYTEXT](r_STL_QUERYTEXT.md), and [SVL\$1STATEMENTTEXT](r_SVL_STATEMENTTEXT.md).
Use the STARTTIME and ENDTIME columns to find out which statements were logged during a given time period. Long blocks of SQL text are broken into lines 200 characters long; the SEQUENCE column identifies fragments of text that belong to a single statement.
The STL\$1UTILITYTEXT system table supports both MANUAL and AUTO REFRESH operations of materialized views. To identify AUTO REFRESH on materialized views, find the column `label`. All AUTO REFRESH queries have a label with the value `maintenance`.
STL\$1UTILITYTEXT 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_UTILITYTEXT.html)
## Sample queries
The following query returns the text for "utility" commands that were run on January 26th, 2012. In this case, some SET commands and a SHOW ALL command were run:
```
select starttime, sequence, rtrim(text)
from stl_utilitytext
where starttime like '2012-01-26%'
order by starttime, sequence;
starttime | sequence | rtrim
---------------------------+-----+----------------------------------
2012-01-26 13:05:52.529235 | 0 | show all;
2012-01-26 13:20:31.660255 | 0 | SET query_group to ''
2012-01-26 13:20:54.956131 | 0 | SET query_group to 'soldunsold.sql'
...
```
### Reconstructing Stored SQL
To reconstruct the SQL stored in the `text` column of STL\$1UTILITYTEXT, run a SELECT statement to create SQL from 1 or more parts in the `text` column. Before running the reconstructed SQL, replace any (`\n`) special characters with a new line. The result of the following SELECT statement is rows of reconstructed SQL in the `query_statement` field.
```
SELECT LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END) WITHIN GROUP (ORDER BY sequence) as query_statement
FROM stl_utilitytext GROUP BY xid order by xid;
```
For example, the following query sets the query\$1group to a string of zeros. The query itself is longer than 200 characters and is stored in parts in STL\$1UTILITYTEXT.
```
set query_group to '00000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000000000 000000';
```
In this example, the query is stored in 2 parts (rows) in the `text` column of STL\$1UTILITYTEXT.
```
select query, sequence, text
from stl_utilitytext where query=pg_last_query_id() order by query desc, sequence limit 10;
```
```
starttime | sequence | text
----------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2019-07-23 22:55:34.926198 | 0 | set query_group to '00000000000000000000000000000000000000000000000000000000000000000000000000000000\n0000000000000000000000000000000000000000000000000000000000000000000000000000000000
2019-07-23 22:55:34.926198 | 1 | 000000';
```
To reconstruct the SQL stored in STL\$1UTILITYTEXT, run the following SQL.
```
select LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END, '') within group (order by sequence) AS query_statement
from stl_utilitytext where query=pg_last_query_id();
```
To use the resulting reconstructed SQL in your client, replace any (`\n`) special characters with a new line.
```
query_statement
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
set query_group to '00000000000000000000000000000000000000000000000000000000000000000000000000000000\n0000000000000000000000000000000000000000000000000000000000000000000000000000000000 000000';
```
# STL\$1VACUUM
Displays row and block statistics for tables that have been vacuumed.
The view shows information specific to when each vacuum operation started and finished, and demonstrates the benefits of running the operation. For information about the requirements for running this command, see the [VACUUM](r_VACUUM_command.md) command description.
STL\$1VACUUM is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1VACUUM\$1HISTORY](SYS_VACUUM_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_VACUUM.html)
## Sample queries
The following query reports vacuum statistics for table 108313. The table was vacuumed following a series of inserts and deletes.
```
select xid, table_id, status, rows, sortedrows, blocks, eventtime,
reclaimable_rows, reclaimable_space_mb
from stl_vacuum where table_id=108313 order by eventtime;
xid | table_id | status | rows | sortedrows | blocks | eventtime | reclaimable_rows | reclaimable_space_mb
-------+----------+-------------------------+------+------------+--------+----------------------+------------------+----------------------
14294 | 108313 | Started | 1950 | 408 | 28 | 2016-05-19 17:36:01 | 984 | 17
14294 | 108313 | Finished | 966 | 966 | 11 | 2016-05-19 18:26:13 | 0 | 0
15126 | 108313 | Skipped(sorted>=95%) | 966 | 966 | 11 | 2016-05-19 18:26:38 | 0 | 0
```
At the start of the VACUUM, the table contained 1,950 rows stored in 28 1 MB blocks. Amazon Redshift estimated it could reclaim 984, or 17 blocks of disk space, with a vacuum operation.
In the row for the Finished status, the ROWS column shows a value of 966, and the BLOCKS column value is 11, down from 28. The vacuum reclaimed the estimated amount of disk space, with no reclaimable rows or space remaining after the vacuum operation completed.
In the sort phase (transaction 15126), the vacuum was able to skip the table because the rows were inserted in sort key order.
The following example shows the statistics for a SORT ONLY vacuum on the SALES table (table 110116 in this example) after a large INSERT operation:
```
vacuum sort only sales;
select xid, table_id, status, rows, sortedrows, blocks, eventtime
from stl_vacuum order by xid, table_id, eventtime;
xid |table_id| status | rows |sortedrows|blocks| eventtime
----+--------+-----------------+-------+----------+------+--------------------
...
2925| 110116 |Started Sort Only|1379648| 172456 | 132 | 2011-02-24 16:25:21...
2925| 110116 |Finished |1379648| 1379648 | 132 | 2011-02-24 16:26:28...
```
# STL\$1WINDOW
Analyzes query steps that perform window functions.
STL\$1WINDOW 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STL\$1WINDOW only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_WINDOW.html)
## Sample queries
The following example returns window function results for slice 0 and segment 3.
```
select query, tasknum, rows, is_diskbased, workmem
from stl_window
where slice=0 and segment=3;
```
```
query | tasknum | rows | is_diskbased | workmem
-------+---------+------+--------------+----------
86326 | 36 | 1857 | f | 95256616
705 | 15 | 1857 | f | 95256616
86399 | 27 | 1857 | f | 95256616
649 | 10 | 0 | f | 95256616
(4 rows)
```
# STL\$1WLM\$1ERROR
Records all WLM-related errors as they occur.
STL\$1WLM\$1ERROR 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_WLM_ERROR.html)
# STL\$1WLM\$1RULE\$1ACTION
Records details about actions resulting from WLM query monitoring rules associated with user-defined queues. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).
STL\$1WLM\$1RULE\$1ACTION 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_WLM_RULE_ACTION.html)
## Sample queries
The following example finds queries that were stopped by a query monitoring rule.
```
Select query, rule
from stl_wlm_rule_action
where action = 'abort'
order by query;
```
# STL\$1WLM\$1QUERY
Contains a record of each attempted execution of a query in a service class handled by WLM.
STL\$1WLM\$1QUERY 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STL_WLM_QUERY.html)
## Sample queries
**View average query Time in queues and executing**
The following queries display the current configuration for service classes greater than 4. For a list of service class IDs, see [WLM service class IDs](cm-c-wlm-system-tables-and-views.md#wlm-service-class-ids).
The following query returns the average time (in microseconds) that each query spent in query queues and executing for each service class.
```
select service_class as svc_class, count(*),
avg(datediff(microseconds, queue_start_time, queue_end_time)) as avg_queue_time,
avg(datediff(microseconds, exec_start_time, exec_end_time )) as avg_exec_time
from stl_wlm_query
where service_class > 4
group by service_class
order by service_class;
```
This query returns the following sample output:
```
svc_class | count | avg_queue_time | avg_exec_time
-----------+-------+----------------+---------------
5 | 20103 | 0 | 80415
5 | 3421 | 34015 | 234015
6 | 42 | 0 | 944266
7 | 196 | 6439 | 1364399
(4 rows)
```
**View maximum query time in queues and executing**
The following query returns the maximum amount of time (in microseconds) that a query spent in any query queue and executing for each service class.
```
select service_class as svc_class, count(*),
max(datediff(microseconds, queue_start_time, queue_end_time)) as max_queue_time,
max(datediff(microseconds, exec_start_time, exec_end_time )) as max_exec_time
from stl_wlm_query
where svc_class > 5
group by service_class
order by service_class;
```
```
svc_class | count | max_queue_time | max_exec_time
-----------+-------+----------------+---------------
6 | 42 | 0 | 3775896
7 | 197 | 37947 | 16379473
(4 rows)
```
# STV tables for snapshot data
STV tables are virtual system tables that contain snapshots of the current system data.
**Topics**
+ [STV\$1ACTIVE\$1CURSORS](r_STV_ACTIVE_CURSORS.md)
+ [STV\$1BLOCKLIST](r_STV_BLOCKLIST.md)
+ [STV\$1CURSOR\$1CONFIGURATION](r_STV_CURSOR_CONFIGURATION.md)
+ [STV\$1DB\$1ISOLATION\$1LEVEL](r_STV_DB_ISOLATION_LEVEL.md)
+ [STV\$1EXEC\$1STATE](r_STV_EXEC_STATE.md)
+ [STV\$1INFLIGHT](r_STV_INFLIGHT.md)
+ [STV\$1LOAD\$1STATE](r_STV_LOAD_STATE.md)
+ [STV\$1LOCKS](r_STV_LOCKS.md)
+ [STV\$1ML\$1MODEL\$1INFO](r_STV_ML_MODEL_INFO.md)
+ [STV\$1MV\$1DEPS](r_STV_MV_DEPS.md)
+ [STV\$1MV\$1INFO](r_STV_MV_INFO.md)
+ [STV\$1NODE\$1STORAGE\$1CAPACITY](r_STV_NODE_STORAGE_CAPACITY.md)
+ [STV\$1PARTITIONS](r_STV_PARTITIONS.md)
+ [STV\$1QUERY\$1METRICS](r_STV_QUERY_METRICS.md)
+ [STV\$1RECENTS](r_STV_RECENTS.md)
+ [STV\$1SESSIONS](r_STV_SESSIONS.md)
+ [STV\$1SLICES](r_STV_SLICES.md)
+ [STV\$1STARTUP\$1RECOVERY\$1STATE](r_STV_STARTUP_RECOVERY_STATE.md)
+ [STV\$1TBL\$1PERM](r_STV_TBL_PERM.md)
+ [STV\$1TBL\$1TRANS](r_STV_TBL_TRANS.md)
+ [STV\$1WLM\$1CLASSIFICATION\$1CONFIG](r_STV_WLM_CLASSIFICATION_CONFIG.md)
+ [STV\$1WLM\$1QMR\$1CONFIG](r_STV_WLM_QMR_CONFIG.md)
+ [STV\$1WLM\$1QUERY\$1QUEUE\$1STATE](r_STV_WLM_QUERY_QUEUE_STATE.md)
+ [STV\$1WLM\$1QUERY\$1STATE](r_STV_WLM_QUERY_STATE.md)
+ [STV\$1WLM\$1QUERY\$1TASK\$1STATE](r_STV_WLM_QUERY_TASK_STATE.md)
+ [STV\$1WLM\$1SERVICE\$1CLASS\$1CONFIG](r_STV_WLM_SERVICE_CLASS_CONFIG.md)
+ [STV\$1WLM\$1SERVICE\$1CLASS\$1STATE](r_STV_WLM_SERVICE_CLASS_STATE.md)
+ [STV\$1XRESTORE\$1ALTER\$1QUEUE\$1STATE](r_STV_XRESTORE_ALTER_QUEUE_STATE.md)
# STV\$1ACTIVE\$1CURSORS
STV\$1ACTIVE\$1CURSORS displays details for currently open cursors. For more information, see [DECLARE](declare.md).
STV\$1ACTIVE\$1CURSORS 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](cm_chap_system-tables.md#c_visibility-of-data). A user can only view cursors opened by that user. A superuser can view all cursors.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_ACTIVE_CURSORS.html)
# STV\$1BLOCKLIST
STV\$1BLOCKLIST contains the number of 1 MB disk blocks that are used by each slice, table, or column in a database.
Use aggregate queries with STV\$1BLOCKLIST, as the following examples show, to determine the number of 1 MB disk blocks allocated per database, table, slice, or column. You can also use [STV\$1PARTITIONS](r_STV_PARTITIONS.md) to view summary information about disk utilization.
STV\$1BLOCKLIST is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
STV\$1BLOCKLIST only records blocks owned by the provisioned cluster or serverless namespace. If the database includes blocks shared from a datashare producer, those blocks aren’t included in STV\$1BLOCKLIST. For more information about datashares, go to [Data sharing in Amazon Redshift](datashare-overview.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_BLOCKLIST.html)
## Sample queries
STV\$1BLOCKLIST contains one row per allocated disk block, so a query that selects all the rows potentially returns a very large number of rows. We recommend using only aggregate queries with STV\$1BLOCKLIST.
The [SVV\$1DISKUSAGE](r_SVV_DISKUSAGE.md) view provides similar information in a more user-friendly format; however, the following example demonstrates one use of the STV\$1BLOCKLIST table.
To determine the number of 1 MB blocks used by each column in the VENUE table, type the following query:
```
select col, count(*)
from stv_blocklist, stv_tbl_perm
where stv_blocklist.tbl = stv_tbl_perm.id
and stv_blocklist.slice = stv_tbl_perm.slice
and stv_tbl_perm.name = 'venue'
group by col
order by col;
```
This query returns the number of 1 MB blocks allocated to each column in the VENUE table, shown by the following sample data:
```
col | count
-----+-------
0 | 4
1 | 4
2 | 4
3 | 4
4 | 4
5 | 4
7 | 4
8 | 4
(8 rows)
```
The following query shows whether or not table data is actually distributed over all slices:
```
select trim(name) as table, stv_blocklist.slice, stv_tbl_perm.rows
from stv_blocklist,stv_tbl_perm
where stv_blocklist.tbl=stv_tbl_perm.id
and stv_tbl_perm.slice=stv_blocklist.slice
and stv_blocklist.id > 10000 and name not like '%#m%'
and name not like 'systable%'
group by name, stv_blocklist.slice, stv_tbl_perm.rows
order by 3 desc;
```
This query produces the following sample output, showing the even data distribution for the table with the most rows:
```
table | slice | rows
----------+-------+-------
listing | 13 | 10527
listing | 14 | 10526
listing | 8 | 10526
listing | 9 | 10526
listing | 7 | 10525
listing | 4 | 10525
listing | 17 | 10525
listing | 11 | 10525
listing | 5 | 10525
listing | 18 | 10525
listing | 12 | 10525
listing | 3 | 10525
listing | 10 | 10525
listing | 2 | 10524
listing | 15 | 10524
listing | 16 | 10524
listing | 6 | 10524
listing | 19 | 10524
listing | 1 | 10523
listing | 0 | 10521
...
(180 rows)
```
The following query determines whether any tombstoned blocks were committed to disk:
```
select slice, col, tbl, blocknum, newblock
from stv_blocklist
where tombstone > 0;
slice | col | tbl | blocknum | newblock
-------+-----+--------+----------+----------
4 | 0 | 101285 | 0 | 1
4 | 2 | 101285 | 0 | 1
4 | 4 | 101285 | 1 | 1
5 | 2 | 101285 | 0 | 1
5 | 0 | 101285 | 0 | 1
5 | 1 | 101285 | 0 | 1
5 | 4 | 101285 | 1 | 1
...
(24 rows)
```
# STV\$1CURSOR\$1CONFIGURATION
STV\$1CURSOR\$1CONFIGURATION displays cursor configuration constraints. For more information, see [Cursor constraints](declare.md#declare-constraints).
STV\$1CURSOR\$1CONFIGURATION is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_CURSOR_CONFIGURATION.html)
# STV\$1DB\$1ISOLATION\$1LEVEL
STV\$1DB\$1ISOLATION\$1LEVEL displays the current isolation level for databases. For more information about isolation levels, see [Isolation levels in Amazon Redshift](c_serial_isolation.md).
STV\$1DB\$1ISOLATION\$1LEVEL 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_DB_ISOLATION_LEVEL.html)
# STV\$1EXEC\$1STATE
Use the STV\$1EXEC\$1STATE table to find out information about queries and query steps that are actively running on compute nodes.
This information is usually used only to troubleshoot engineering issues. The views SVV\$1QUERY\$1STATE and SVL\$1QUERY\$1SUMMARY extract their information from STV\$1EXEC\$1STATE.
STV\$1EXEC\$1STATE 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_EXEC_STATE.html)
## Sample queries
Rather than querying STV\$1EXEC\$1STATE directly, Amazon Redshift recommends querying SVL\$1QUERY\$1SUMMARY or SVV\$1QUERY\$1STATE to obtain the information in STV\$1EXEC\$1STATE in a more user-friendly format. See the [SVL\$1QUERY\$1SUMMARY](r_SVL_QUERY_SUMMARY.md) or [SVV\$1QUERY\$1STATE](r_SVV_QUERY_STATE.md) table documentation for more details.
# STV\$1INFLIGHT
Use the STV\$1INFLIGHT table to determine what queries are currently running on the cluster. If you're troubleshooting, it's helpful for checking the status of long-running queries.
STV\$1INFLIGHT does not show leader-node only queries. For more information, see [Leader node–only functions](c_SQL_functions_leader_node_only.md). STV\$1INFLIGHT 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Troubleshooting with STV\$1INFLIGHT
If you use STV\$1INFLIGHT to troubleshoot performance for a query, or a collection of queries, note the following:
+ Long-running open transactions generally increase load. These open transactions can result in longer running times for other queries.
+ Long-running COPY and ETL jobs can affect other queries running on the cluster, if they're taking a lot of compute resources. In most cases, moving these long-running jobs to times of low use increases performance for reporting or analytics workloads.
+ There are views that provide related information to STV\$1INFLIGHT. These include [STL\$1QUERYTEXT](r_STL_QUERYTEXT.md), which captures the query text for SQL commands, and [SVV\$1QUERY\$1INFLIGHT](r_SVV_QUERY_INFLIGHT.md), which joins STV\$1INFLIGHT to STL\$1QUERYTEXT. You can also use [STV\$1RECENTS](r_STV_RECENTS.md) with STV\$1INFLIGHT for troubleshooting. For example, STV\$1RECENTS can indicate if specific queries are in a *Running* or *Done* state. Combining this information with results from STV\$1INFLIGHT can give you more information about a query's properties and compute-resource impact.
You can also monitor running queries using the Amazon Redshift console.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_INFLIGHT.html)
## Sample queries
To view all active queries currently running on the database, type the following query:
```
select * from stv_inflight;
```
The sample output below shows two queries currently running, including the STV\$1INFLIGHT query itself and a query that was run from a script called `avgwait.sql`:
```
select slice, query, trim(label) querylabel, pid,
starttime, substring(text,1,20) querytext
from stv_inflight;
slice|query|querylabel | pid | starttime | querytext
-----+-----+-----------+-----+--------------------------+--------------------
1011 | 21 | | 646 |2012-01-26 13:23:15.645503|select slice, query,
1011 | 20 |avgwait.sql| 499 |2012-01-26 13:23:14.159912|select avg(datediff(
(2 rows)
```
The following query selects several columns, including concurrency\$1scaling\$1status. This column indicates whether queries are being sent to the concurrency-scaling cluster. If the value is `1` for some results, it's an indication that concurrency-scaling compute resources are being used. For more information, see [Concurrency scaling](concurrency-scaling.md).
```
select userid,
query,
pid,
starttime,
text,
suspended,
concurrency_scaling_status
from STV_INFLIGHT;
```
The sample output shows one query being sent to the concurrency scaling cluster.
```
query | pid | starttime | text | suspended | concurrency_scaling_status
--------+---------+----------------------------|------------------------|---------------|-------------------------------
1234567 | 123456 | 2012-01-26 13:23:15.645503 | select userid, query... 0 1
2345678 | 234567 | 2012-01-26 13:23:14.159912 | select avg(datediff(... 0 0
(2 rows)
```
For more tips on troubleshooting query performance, see [Query troubleshooting](queries-troubleshooting.md).
# STV\$1LOAD\$1STATE
Use the STV\$1LOAD\$1STATE table to find information about current state of ongoing COPY statements.
The COPY command updates this table after every million records are loaded.
STV\$1LOAD\$1STATE 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_LOAD_STATE.html)
## Sample query
To view the progress of each slice for a COPY command, type the following query. This example uses the PG\$1LAST\$1COPY\$1ID() function to retrieve information for the last COPY command.
```
select slice , bytes_loaded, bytes_to_load , pct_complete from stv_load_state where query = pg_last_copy_id();
slice | bytes_loaded | bytes_to_load | pct_complete
-------+--------------+---------------+--------------
2 | 0 | 0 | 0
3 | 12840898 | 39104640 | 32
(2 rows)
```
# STV\$1LOCKS
Use the STV\$1LOCKS table to view any current updates on tables in the database.
Amazon Redshift locks tables to prevent two users from updating the same table at the same time. While the STV\$1LOCKS table shows all current table updates, query the [STL\$1TR\$1CONFLICT](r_STL_TR_CONFLICT.md) table to see a log of lock conflicts. Use the [SVV\$1TRANSACTIONS](r_SVV_TRANSACTIONS.md) view to identify open transactions and lock contention issues.
STV\$1LOCKS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_LOCKS.html)
## Sample query
To view all locks taking place in current transactions, type the following command:
```
select table_id, last_update, lock_owner, lock_owner_pid from stv_locks;
```
This query returns the following sample output, which displays three locks currently in effect:
```
table_id | last_update | lock_owner | lock_owner_pid
----------+----------------------------+------------+----------------
100004 | 2008-12-23 10:08:48.882319 | 1043 | 5656
100003 | 2008-12-23 10:08:48.779543 | 1043 | 5656
100140 | 2008-12-23 10:08:48.021576 | 1043 | 5656
(3 rows)
```
# STV\$1ML\$1MODEL\$1INFO
State information about the current state of the machine learning model.
STV\$1ML\$1MODEL\$1INFO 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_ML_MODEL_INFO.html)
## Sample query
The following query displays the current state of machine learning models.
```
SELECT schema_name, model_name, model_state
FROM stv_ml_model_info;
schema_name | model_name | model_state
-------------+------------------------------+--------------------------------------
public | customer_churn_auto_model | Train Model On SageMaker In Progress
public | customer_churn_xgboost_model | Model is Ready
(2 row)
```
# STV\$1MV\$1DEPS
The STV\$1MV\$1DEPS table shows the dependencies of materialized views on other materialized views within Amazon Redshift.
For more information about materialized views, see [Materialized views in Amazon Redshift](materialized-view-overview.md).
STV\$1MV\$1DEPS is visible to all users. Superusers can see all rows; regular users can only list materialized views residing in schemas they have access to. For more information, see [Visibility of data insystem tables and views](https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_system-tables.html#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_MV_DEPS.html)
## Sample query
The following query returns an output row that indicates that the materialized view `mv_over_foo` uses the materialized view `mv_foo` in its definition as a dependency.
```
CREATE SCHEMA test_ivm_setup;
CREATE TABLE test_ivm_setup.foo(a INT);
CREATE MATERIALIZED VIEW test_ivm_setup.mv_foo AS SELECT * FROM test_ivm_setup.foo;
CREATE MATERIALIZED VIEW test_ivm_setup.mv_over_foo AS SELECT * FROM test_ivm_setup.mv_foo;
SELECT * FROM stv_mv_deps;
db_name | schema | name | ref_schema | ref_name | ref_database_name
---------+-----------------+-------------+----------------+----------+------------------
dev | test_ivm_setup | mv_over_foo | test_ivm_setup | mv_foo | dev
```
# STV\$1MV\$1INFO
The STV\$1MV\$1INFO table contains a row for every materialized view, whether the data is stale, and state information.
For more information about materialized views, see [Materialized views in Amazon Redshift](materialized-view-overview.md).
STV\$1MV\$1INFO is visible to all users. Superusers can see all rows; regular users can only list materialized views residing in schemas they have access to. For more information, see [Visibility of data insystem tables and views](https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_system-tables.html#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_MV_INFO.html)
## Sample query
To view the state of all materialized views, run the following query.
```
select * from stv_mv_info;
```
This query returns the following sample output.
```
db_name | schema | name | updated_upto_xid | is_stale | owner_user_name | state | autorefresh | autorewrite
---------+--------------------+---------+------------------+----------+-----------------+-------+-------------+------------
dev | test_ivm_setup | mv | 1031 | f | catch-22 | 1 | 1 | 0
dev | test_ivm_setup | old_mv | 988 | t | lotr | 1 | 0 | 1
```
# STV\$1NODE\$1STORAGE\$1CAPACITY
The STV\$1NODE\$1STORAGE\$1CAPACITY table shows details of total storage capacity and total used capacity for each node in a cluster. It contains a row for each node.
STV\$1NODE\$1STORAGE\$1CAPACITY is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_NODE_STORAGE_CAPACITY.html)
## Sample queries
**Note**
The results of the following examples vary based on the node specifications of your cluster. Add column `capacity` to your SQL SELECT to retrieve the capacity of your cluster.
The following query returns used space and total capacity in 1 MB disk blocks. This example ran on a two-node dc2.8xlarge cluster.
```
select node, used from stv_node_storage_capacity order by node;
```
This query returns the following sample output.
```
node | used
------+-------
0 | 30597
1 | 27089
```
The following query returns used space and total capacity in 1 MB disk blocks. This example ran on a two-node ra3.16xlarge cluster.
```
select node, used from stv_node_storage_capacity order by node;
```
This query returns the following sample output.
```
node | used
------+-------
0 | 30591
1 | 27103
```
# STV\$1PARTITIONS
Use the STV\$1PARTITIONS table to find out the disk speed performance and disk utilization for Amazon Redshift.
STV\$1PARTITIONS contains one row per node per logical disk volume.
STV\$1PARTITIONS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_PARTITIONS.html)
## Sample query
The following query returns the disk space used and capacity, in 1 MB disk blocks, and calculates disk utilization as a percentage of raw disk space. The raw disk space includes space that is reserved by Amazon Redshift for internal use, so it is larger than the nominal disk capacity, which is the amount of disk space available to the user. The **Percentage of Disk Space Used** metric on the **Performance** tab of the Amazon Redshift Management Console reports the percentage of nominal disk capacity used by your cluster. We recommend that you monitor the **Percentage of Disk Space Used** metric to maintain your usage within your cluster's nominal disk capacity.
**Important**
We strongly recommend that you do not exceed your cluster's nominal disk capacity. While it might be technically possible under certain circumstances, exceeding your nominal disk capacity decreases your cluster's fault tolerance and increases your risk of losing data.
This example was run on a two-node cluster with six logical disk partitions per node. Space is being used very evenly across the disks, with approximately 25% of each disk in use.
```
select owner, host, diskno, used, capacity,
(used-tossed)/capacity::numeric *100 as pctused
from stv_partitions order by owner;
owner | host | diskno | used | capacity | pctused
-------+------+--------+--------+----------+---------
0 | 0 | 0 | 236480 | 949954 | 24.9
0 | 0 | 1 | 236420 | 949954 | 24.9
0 | 0 | 2 | 236440 | 949954 | 24.9
0 | 1 | 2 | 235150 | 949954 | 24.8
0 | 1 | 1 | 237100 | 949954 | 25.0
0 | 1 | 0 | 237090 | 949954 | 25.0
1 | 1 | 0 | 236310 | 949954 | 24.9
1 | 1 | 1 | 236300 | 949954 | 24.9
1 | 1 | 2 | 236320 | 949954 | 24.9
1 | 0 | 2 | 237910 | 949954 | 25.0
1 | 0 | 1 | 235640 | 949954 | 24.8
1 | 0 | 0 | 235380 | 949954 | 24.8
(12 rows)
```
# STV\$1QUERY\$1METRICS
Contains metrics information, such as the number of rows processed, CPU usage, input/output, and disk use, for active queries running in user-defined query queues (service classes). To view metrics for queries that have completed, see the [STL\$1QUERY\$1METRICS](r_STL_QUERY_METRICS.md) system table.
Query metrics are sampled at one second intervals. As a result, different runs of the same query might return slightly different times. Also, query segments that run in less than 1 second might not be recorded.
STV\$1QUERY\$1METRICS tracks and aggregates metrics at the query, segment, and step level. For information about query segments and steps, see [Query planning and execution workflow](c-query-planning.md). Many metrics (such as `max_rows`, `cpu_time`, and so on) are summed across node slices. For more information about node slices, see [Data warehouse system architecture](c_high_level_system_architecture.md).
To determine the level at which the row reports metrics, examine the `segment` and `step_type` columns:
+ If both `segment` and `step_type` are `-1`, then the row reports metrics at the query level.
+ If `segment` is not `-1` and `step_type` is `-1`, then the row reports metrics at the segment level.
+ If both `segment` and `step_type` are not `-1`, then the row reports metrics at the step level.
STV\$1QUERY\$1METRICS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_QUERY_METRICS.html)
## Step types
The following table lists step types relevant to database users. The table doesn't list step types that are for internal use only. If step type is -1, the metric is not reported at the step level.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_QUERY_METRICS.html)
## Sample query
To find active queries with high CPU time (more the 1,000 seconds), run the following query.
```
select query, cpu_time / 1000000 as cpu_seconds
from stv_query_metrics where segment = -1 and cpu_time > 1000000000
order by cpu_time;
query | cpu_seconds
------+------------
25775 | 9540
```
To find active queries with a nested loop join that returned more than one million rows, run the following query.
```
select query, rows
from stv_query_metrics
where step_type = 15 and rows > 1000000
order by rows;
query | rows
------+-----------
25775 | 1580225854
```
To find active queries that have run for more than 60 seconds and have used less than 10 seconds of CPU time, run the following query.
```
select query, run_time/1000000 as run_time_seconds
from stv_query_metrics
where segment = -1 and run_time > 60000000 and cpu_time < 10000000;
query | run_time_seconds
------+-----------------
25775 | 114
```
# STV\$1RECENTS
Use the STV\$1RECENTS table to find out information about the currently active and recently run queries against a database.
STV\$1RECENTS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Troubleshooting with STV\$1RECENTS
STV\$1RECENTS is particularly helpful for determining if a query or collection of queries is currently running or done. It also shows the duration a query has been running. This is helpful for getting a sense for which queries are long running.
You can join STV\$1RECENTS to other system views, such as [STV\$1INFLIGHT](r_STV_INFLIGHT.md), to gather additional metadata about running queries. (There's an example that shows how to do this in the sample queries section.) You can also use returned records from this view along with the monitoring features in the Amazon Redshift console for troubleshooting in real time.
System views that compliment STV\$1RECENTS include [STL\$1QUERYTEXT](r_STL_QUERYTEXT.md), which retrieves the query text for SQL commands, and [SVV\$1QUERY\$1INFLIGHT](r_SVV_QUERY_INFLIGHT.md), which joins STV\$1INFLIGHT to STL\$1QUERYTEXT.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_RECENTS.html)
## Sample queries
To determine which queries are currently running against the database, run the following query:
```
select user_name, db_name, pid, query
from stv_recents
where status = 'Running';
```
The sample output below shows a single query running on the TICKIT database:
```
user_name | db_name | pid | query
----------+---------+---------+-------------
dwuser | tickit | 19996 |select venuename, venueseats from
venue where venueseats > 50000 order by venueseats desc;
```
The following example returns a list of queries (if any) that are running or waiting in a queue to run:
```
select * from stv_recents where status<>'Done';
status | starttime | duration |user_name|db_name| query | pid
-------+---------------------+----------+---------+-------+-----------+------
Running| 2010-04-21 16:11... | 281566454| dwuser |tickit | select ...| 23347
```
This query does not return results unless you are running a number of concurrent queries and some of those queries are in a queue.
The following example extends the previous example. In this case, queries that are truly "in flight" (running, not waiting) are excluded from the result:
```
select * from stv_recents where status<>'Done'
and pid not in (select pid from stv_inflight);
...
```
For more tips on troubleshooting query performance, see [Query troubleshooting](queries-troubleshooting.md).
# STV\$1SESSIONS
Use the STV\$1SESSIONS table to view information about the active user sessions for Amazon Redshift.
To view the session history, use the [STL\$1SESSIONS](r_STL_SESSIONS.md) table, rather than STV\$1SESSIONS.
STV\$1SESSIONS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1SESSION\$1HISTORY](SYS_SESSION_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_SESSIONS.html)
## Sample queries
To perform a quick check to see if any other users are currently logged into Amazon Redshift, type the following query:
```
select count(*)
from stv_sessions;
```
If the result is greater than one, then at least one other user is currently logged in to the database.
To view all active sessions for Amazon Redshift, type the following query:
```
select *
from stv_sessions;
```
The following result shows four active sessions running on Amazon Redshift:
```
starttime | process |user_name | db_name | timeout_sec
-------------------------+---------+----------------------------+----------------------------+-------------
2018-08-06 08:44:07.50 | 13779 | IAMA:aws_admin:admin_grp | dev | 0
2008-08-06 08:54:20.50 | 19829 | dwuser | dev | 120
2008-08-06 08:56:34.50 | 20279 | dwuser | dev | 120
2008-08-06 08:55:00.50 | 19996 | dwuser | tickit | 0
(3 rows)
```
The user name prefixed with IAMA indicates that the user signed on using federated single sign-on. For more information, see [Using IAM authentication to generate database user credentials](https://docs.aws.amazon.com/redshift/latest/mgmt/generating-user-credentials.html).
# STV\$1SLICES
Use the STV\$1SLICES table to view the current mapping of a slice to a node.
The information in STV\$1SLICES is used mainly for investigation purposes.
STV\$1SLICES 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_SLICES.html)
## Sample query
To view which cluster nodes are managing which slices, type the following query:
```
select node, slice from stv_slices;
```
This query returns the following sample output:
```
node | slice
------+-------
0 | 2
0 | 3
0 | 1
0 | 0
(4 rows)
```
# STV\$1STARTUP\$1RECOVERY\$1STATE
Records the state of tables that are temporarily locked during cluster restart operations. Amazon Redshift places a temporary lock on tables while they are being processed to resolve stale transactions following a cluster restart.
STV\$1STARTUP\$1RECOVERY\$1STATE 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_STARTUP_RECOVERY_STATE.html)
## Sample queries
To monitor which tables are temporarily locked, run the following query after a cluster restart.
```
select * from STV_STARTUP_RECOVERY_STATE;
db_id | tbl_id | table_name
--------+--------+------------
100044 | 100058 | lineorder
100044 | 100068 | part
100044 | 100072 | customer
100044 | 100192 | supplier
(4 rows)
```
# STV\$1TBL\$1PERM
The STV\$1TBL\$1PERM table contains information about the permanent tables in Amazon Redshift, including temporary tables created by a user for the current session. STV\$1TBL\$1PERM contains information for all tables in all databases.
This table differs from [STV\$1TBL\$1TRANS](r_STV_TBL_TRANS.md), which contains information about transient database tables that the system creates during query processing.
STV\$1TBL\$1PERM is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_TBL_PERM.html)
## Sample queries
The following query returns a list of distinct table IDs and names:
```
select distinct id, name
from stv_tbl_perm order by name;
id | name
--------+-------------------------
100571 | category
100575 | date
100580 | event
100596 | listing
100003 | padb_config_harvest
100612 | sales
...
```
Other system tables use table IDs, so knowing which table ID corresponds to a certain table can be very useful. In this example, SELECT DISTINCT is used to remove the duplicates (tables are distributed across multiple slices).
To determine the number of blocks used by each column in the VENUE table, type the following query:
```
select col, count(*)
from stv_blocklist, stv_tbl_perm
where stv_blocklist.tbl = stv_tbl_perm.id
and stv_blocklist.slice = stv_tbl_perm.slice
and stv_tbl_perm.name = 'venue'
group by col
order by col;
col | count
-----+-------
0 | 8
1 | 8
2 | 8
3 | 8
4 | 8
5 | 8
6 | 8
7 | 8
(8 rows)
```
## Usage notes
The ROWS column includes counts of deleted rows that have not been vacuumed (or have been vacuumed but with the SORT ONLY option). Therefore, the SUM of the ROWS column in the STV\$1TBL\$1PERM table might not match the COUNT(\$1) result when you query a given table directly. For example, if 2 rows are deleted from VENUE, the COUNT(\$1) result is 200 but the SUM(ROWS) result is still 202:
```
delete from venue
where venueid in (1,2);
select count(*) from venue;
count
-------
200
(1 row)
select trim(name) tablename, sum(rows)
from stv_tbl_perm where name='venue' group by name;
tablename | sum
-----------+-----
venue | 202
(1 row)
```
To synchronize the data in STV\$1TBL\$1PERM, run a full vacuum the VENUE table.
```
vacuum venue;
select trim(name) tablename, sum(rows)
from stv_tbl_perm
where name='venue'
group by name;
tablename | sum
-----------+-----
venue | 200
(1 row)
```
# STV\$1TBL\$1TRANS
Use the STV\$1TBL\$1TRANS table to find out information about the transient database tables that are currently in memory.
Transient tables are typically temporary row sets that are used as intermediate results while a query runs. STV\$1TBL\$1TRANS differs from [STV\$1TBL\$1PERM](r_STV_TBL_PERM.md) in that STV\$1TBL\$1PERM contains information about permanent database tables.
STV\$1TBL\$1TRANS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_TBL_TRANS.html)
## Sample queries
To view transient table information for a query with a query ID of 90, type the following command:
```
select slice, id, rows, size, query_id, ref_cnt
from stv_tbl_trans
where query_id = 90;
```
This query returns the transient table information for query 90, as shown in the following sample output:
```
slice | id | rows | size | query_ | ref_ | from_ | prep_
| | | | id | cnt | suspended | swap
------+----+------+------+--------+------+-----------+-------
1013 | 95 | 0 | 0 | 90 | 4 | 0 | 0
7 | 96 | 0 | 0 | 90 | 4 | 0 | 0
10 | 96 | 0 | 0 | 90 | 4 | 0 | 0
17 | 96 | 0 | 0 | 90 | 4 | 0 | 0
14 | 96 | 0 | 0 | 90 | 4 | 0 | 0
3 | 96 | 0 | 0 | 90 | 4 | 0 | 0
1013 | 99 | 0 | 0 | 90 | 4 | 0 | 0
9 | 96 | 0 | 0 | 90 | 4 | 0 | 0
5 | 96 | 0 | 0 | 90 | 4 | 0 | 0
19 | 96 | 0 | 0 | 90 | 4 | 0 | 0
2 | 96 | 0 | 0 | 90 | 4 | 0 | 0
1013 | 98 | 0 | 0 | 90 | 4 | 0 | 0
13 | 96 | 0 | 0 | 90 | 4 | 0 | 0
1 | 96 | 0 | 0 | 90 | 4 | 0 | 0
1013 | 96 | 0 | 0 | 90 | 4 | 0 | 0
6 | 96 | 0 | 0 | 90 | 4 | 0 | 0
11 | 96 | 0 | 0 | 90 | 4 | 0 | 0
15 | 96 | 0 | 0 | 90 | 4 | 0 | 0
18 | 96 | 0 | 0 | 90 | 4 | 0 | 0
```
In this example, you can see that the query data involves tables 95, 96, and 98. Because zero bytes are allocated to this table, this query can run in memory.
# STV\$1WLM\$1CLASSIFICATION\$1CONFIG
Contains the current classification rules for WLM.
STV\$1WLM\$1CLASSIFICATION\$1CONFIG is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_WLM_CLASSIFICATION_CONFIG.html)
## Sample query
```
select * from STV_WLM_CLASSIFICATION_CONFIG;
id | condition | action_seq | action | action_service_class
---+---------------------------------------------+------------+--------+---------------------
1 | (system user) and (query group: health) | 0 | assign | 1
2 | (system user) and (query group: metrics) | 0 | assign | 2
3 | (system user) and (query group: cmstats) | 0 | assign | 3
4 | (system user) | 0 | assign | 4
5 | (super user) and (query group: superuser) | 0 | assign | 5
6 | (query group: querygroup1) | 0 | assign | 6
7 | (user group: usergroup1) | 0 | assign | 6
8 | (user group: usergroup2) | 0 | assign | 7
9 | (query group: querygroup3) | 0 | assign | 8
10 | (query group: querygroup4) | 0 | assign | 9
11 | (user group: usergroup4) | 0 | assign | 9
12 | (query group: querygroup*) | 0 | assign | 10
13 | (user group: usergroup*) | 0 | assign | 10
14 | (querytype: any) | 0 | assign | 11
(4 rows)
```
# STV\$1WLM\$1QMR\$1CONFIG
Records the configuration for WLM query monitoring rules (QMR). For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).
STV\$1WLM\$1QMR\$1CONFIG is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_WLM_QMR_CONFIG.html)
## Sample query
To view the QMR rule definitions for all service classes greater than 5 (which includes user-defined queues), run the following query. For a list of service class IDs, see [WLM service class IDs](cm-c-wlm-system-tables-and-views.md#wlm-service-class-ids).
```
Select *
from stv_wlm_qmr_config
where service_class > 5
order by service_class;
```
# STV\$1WLM\$1QUERY\$1QUEUE\$1STATE
Records the current state of the query queues for the service classes.
STV\$1WLM\$1QUERY\$1QUEUE\$1STATE 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_WLM_QUERY_QUEUE_STATE.html)
## Sample query
The following query shows the queries in the queue for service classes greater than 4.
```
select * from stv_wlm_query_queue_state
where service_class > 4
order by service_class;
```
This query returns the following sample output.
```
service_class | position | task | query | slot_count | start_time | queue_time
---------------+----------+------+-------+------------+----------------------------+------------
5 | 0 | 455 | 476 | 5 | 2010-10-06 13:18:24.065838 | 20937257
6 | 1 | 456 | 478 | 5 | 2010-10-06 13:18:26.652906 | 18350191
(2 rows)
```
# STV\$1WLM\$1QUERY\$1STATE
Records the current state of queries being tracked by WLM.
STV\$1WLM\$1QUERY\$1STATE 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_WLM_QUERY_STATE.html)
## Sample query
The following query displays all currently executing queries in service classes greater than 4. For a list of service class IDs, see [WLM service class IDs](cm-c-wlm-system-tables-and-views.md#wlm-service-class-ids).
```
select xid, query, trim(state) as state, queue_time, exec_time
from stv_wlm_query_state
where service_class > 4;
```
This query returns the following sample output:
```
xid | query | state | queue_time | exec_time
-------+-------+---------+------------+-----------
100813 | 25942 | Running | 0 | 1369029
100074 | 25775 | Running | 0 | 2221589242
```
# STV\$1WLM\$1QUERY\$1TASK\$1STATE
Contains the current state of service class query tasks.
STV\$1WLM\$1QUERY\$1TASK\$1STATE 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_WLM_QUERY_TASK_STATE.html)
## Sample query
The following query displays the current state of queries in service classes greater than 4. For a list of service class IDs, see [WLM service class IDs](cm-c-wlm-system-tables-and-views.md#wlm-service-class-ids).
```
select * from stv_wlm_query_task_state
where service_class > 4;
```
This query returns the following sample output:
```
service_class | task | query | start_time | exec_time
--------------+------+-------+----------------------------+-----------
5 | 466 | 491 | 2010-10-06 13:29:23.063787 | 357618748
(1 row)
```
# STV\$1WLM\$1SERVICE\$1CLASS\$1CONFIG
Records the service class configurations for WLM.
STV\$1WLM\$1SERVICE\$1CLASS\$1CONFIG is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_WLM_SERVICE_CLASS_CONFIG.html)
## Sample query
The first user-defined service class is service class 6, which is named Service class \$11. The following query displays the current configuration for service classes greater than 4. For a list of service class IDs, see [WLM service class IDs](cm-c-wlm-system-tables-and-views.md#wlm-service-class-ids).
```
select rtrim(name) as name,
num_query_tasks as slots,
query_working_mem as mem,
max_execution_time as max_time,
user_group_wild_card as user_wildcard,
query_group_wild_card as query_wildcard
from stv_wlm_service_class_config
where service_class > 4;
name | slots | mem | max_time | user_wildcard | query_wildcard
-----------------------------+-------+-----+----------+---------------+---------------
Service class for super user | 1 | 535 | 0 | false | false
Queue 1 | 5 | 125 | 0 | false | false
Queue 2 | 5 | 125 | 0 | false | false
Queue 3 | 5 | 125 | 0 | false | false
Queue 4 | 5 | 627 | 0 | false | false
Queue 5 | 5 | 125 | 0 | true | true
Default queue | 5 | 125 | 0 | false | false
```
The following query shows the status of a dynamic WLM transition. While the transition is in process, `num_query_tasks` and `target_query_working_mem` are updated until they equal the target values. For more information, see [WLM dynamic and static configuration properties](cm-c-wlm-dynamic-properties.md).
```
select rtrim(name) as name,
num_query_tasks as slots,
target_num_query_tasks as target_slots,
query_working_mem as memory,
target_query_working_mem as target_memory
from stv_wlm_service_class_config
where num_query_tasks > target_num_query_tasks
or query_working_mem > target_query_working_mem
and service_class > 5;
name | slots | target_slots | memory | target_mem
------------------+-------+--------------+--------+------------
Queue 3 | 5 | 15 | 125 | 375
Queue 5 | 10 | 5 | 250 | 125
(2 rows)
```
# STV\$1WLM\$1SERVICE\$1CLASS\$1STATE
Contains the current state of the service classes.
STV\$1WLM\$1SERVICE\$1CLASS\$1STATE is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_WLM_SERVICE_CLASS_STATE.html)
## Sample query
The following query displays the state for service classes greater than 5. For a list of service class IDs, see [WLM service class IDs](cm-c-wlm-system-tables-and-views.md#wlm-service-class-ids).
```
select service_class, num_executing_queries,
num_executed_queries
from stv_wlm_service_class_state
where service_class > 5
order by service_class;
```
```
service_class | num_executing_queries | num_executed_queries
---------------+-----------------------+----------------------
6 | 1 | 222
7 | 0 | 135
8 | 1 | 39
(3 rows)
```
# STV\$1XRESTORE\$1ALTER\$1QUEUE\$1STATE
Use STV\$1XRESTORE\$1ALTER\$1QUEUE\$1STATE to monitor the migration progress of each table during a classic resize. This is specifically applicable when the target node type is RA3. For more information about classic resize to RA3 nodes, go to [Classic resize](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-cluster-operations.html#classic-resize-faster).
STV\$1XRESTORE\$1ALTER\$1QUEUE\$1STATE is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1RESTORE\$1STATE](SYS_RESTORE_STATE.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_STV_XRESTORE_ALTER_QUEUE_STATE.html)
## Sample query
The following query shows the number of tables in a database that are waiting to be resized, are currently being resized, and are finished resizing.
```
select db_id, status, count(*)
from stv_xrestore_alter_queue_state
group by 1,2 order by 3 desc
db_id | status | count
-------+------------+------
694325 | Waiting | 323
694325 | Finished | 60
694325 | Applying | 1
```
# SVCS views for main and concurrency scaling clusters
SVCS system views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the tables with the prefix STL except that the STL tables provide information only for queries run on the main cluster.
**Topics**
+ [SVCS\$1ALERT\$1EVENT\$1LOG](r_SVCS_ALERT_EVENT_LOG.md)
+ [SVCS\$1COMPILE](r_SVCS_COMPILE.md)
+ [SVCS\$1CONCURRENCY\$1SCALING\$1USAGE](r_SVCS_CONCURRENCY_SCALING_USAGE.md)
+ [SVCS\$1EXPLAIN](r_SVCS_EXPLAIN.md)
+ [SVCS\$1PLAN\$1INFO](r_SVCS_PLAN_INFO.md)
+ [SVCS\$1QUERY\$1SUMMARY](r_SVCS_QUERY_SUMMARY.md)
+ [SVCS\$1S3LIST](r_SVCS_S3LIST.md)
+ [SVCS\$1S3LOG](r_SVCS_S3LOG.md)
+ [SVCS\$1S3PARTITION\$1SUMMARY](r_SVCS_S3PARTITION_SUMMARY.md)
+ [SVCS\$1S3QUERY\$1SUMMARY](r_SVCS_S3QUERY_SUMMARY.md)
+ [SVCS\$1STREAM\$1SEGS](r_SVCS_STREAM_SEGS.md)
+ [SVCS\$1UNLOAD\$1LOG](r_SVCS_UNLOAD_LOG.md)
# SVCS\$1ALERT\$1EVENT\$1LOG
Records an alert when the query optimizer identifies conditions that might indicate performance issues. This view is derived from the STL\$1ALERT\$1EVENT\$1LOG system table but doesn't show slice-level for queries run on a concurrency scaling cluster. Use the SVCS\$1ALERT\$1EVENT\$1LOG table to identify opportunities to improve query performance.
A query consists of multiple segments, and each segment consists of one or more steps. For more information, see [Query processing](c-query-processing.md).
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the tables with the prefix STL except that the STL tables provide information only for queries run on the main cluster.
SVCS\$1ALERT\$1EVENT\$1LOG 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_ALERT_EVENT_LOG.html)
## Usage notes
You can use the SVCS\$1ALERT\$1EVENT\$1LOG to identify potential issues in your queries, then follow the practices in [Query performance tuning](c-optimizing-query-performance.md) to optimize your database design and rewrite your queries. SVCS\$1ALERT\$1EVENT\$1LOG records the following alerts:
+ **Missing statistics**
Statistics are missing. Run ANALYZE following data loads or significant updates and use STATUPDATE with COPY operations. For more information, see [Amazon Redshift best practices for designing queries](c_designing-queries-best-practices.md).
+ **Nested loop **
A nested loop is usually a Cartesian product. Evaluate your query to ensure that all participating tables are joined efficiently.
+ **Very selective filter**
The ratio of rows returned to rows scanned is less than 0.05. Rows scanned is the value of `rows_pre_user_filter `and rows returned is the value of rows in the [STL\$1SCAN](r_STL_SCAN.md) system table. Indicates that the query is scanning an unusually large number of rows to determine the result set. This can be caused by missing or incorrect sort keys. For more information, see [Sort keys](t_Sorting_data.md).
+ **Excessive ghost rows **
A scan skipped a relatively large number of rows that are marked as deleted but not vacuumed, or rows that have been inserted but not committed. For more information, see [Vacuuming tables](t_Reclaiming_storage_space202.md).
+ **Large distribution **
More than 1,000,000 rows were redistributed for hash join or aggregation. For more information, see [Data distribution for query optimization](t_Distributing_data.md).
+ **Large broadcast **
More than 1,000,000 rows were broadcast for hash join. For more information, see [Data distribution for query optimization](t_Distributing_data.md).
+ **Serial execution **
A DS\$1DIST\$1ALL\$1INNER redistribution style was indicated in the query plan, which forces serial execution because the entire inner table was redistributed to a single node. For more information, see [Data distribution for query optimization](t_Distributing_data.md).
## Sample queries
The following query shows alert events for four queries.
```
SELECT query, substring(event,0,25) as event,
substring(solution,0,25) as solution,
trim(event_time) as event_time from svcs_alert_event_log order by query;
query | event | solution | event_time
-------+-------------------------------+------------------------------+---------------------
6567 | Missing query planner statist | Run the ANALYZE command | 2014-01-03 18:20:58
7450 | Scanned a large number of del | Run the VACUUM command to rec| 2014-01-03 21:19:31
8406 | Nested Loop Join in the query | Review the join predicates to| 2014-01-04 00:34:22
29512 | Very selective query filter:r | Review the choice of sort key| 2014-01-06 22:00:00
(4 rows)
```
# SVCS\$1COMPILE
Records compile time and location for each query segment of queries, including queries run on a scaling cluster as well as queries run on the main cluster.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the views with the prefix SVL except that the SVL views provide information only for queries run on the main cluster.
SVCS\$1COMPILE 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](cm_chap_system-tables.md#c_visibility-of-data).
For information about SCL\$1COMPILE, see [SVL\$1COMPILE](r_SVL_COMPILE.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_COMPILE.html)
## Sample queries
In this example, queries 35878 and 35879 ran the same SQL statement. The compile column for query 35878 shows `1` for four query segments, which indicates that the segments were compiled. Query 35879 shows `0` in the compile column for every segment, indicating that the segments did not need to be compiled again.
```
select userid, xid, pid, query, segment, locus,
datediff(ms, starttime, endtime) as duration, compile
from svcs_compile
where query = 35878 or query = 35879
order by query, segment;
userid | xid | pid | query | segment | locus | duration | compile
--------+--------+-------+-------+---------+-------+----------+---------
100 | 112780 | 23028 | 35878 | 0 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 1 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 2 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 3 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 4 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 5 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 6 | 1 | 1380 | 1
100 | 112780 | 23028 | 35878 | 7 | 1 | 1085 | 1
100 | 112780 | 23028 | 35878 | 8 | 1 | 1197 | 1
100 | 112780 | 23028 | 35878 | 9 | 2 | 905 | 1
100 | 112782 | 23028 | 35879 | 0 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 1 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 2 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 3 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 4 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 5 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 6 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 7 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 8 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 9 | 2 | 0 | 0
(20 rows)
```
# SVCS\$1CONCURRENCY\$1SCALING\$1USAGE
Records the usage periods for concurrency scaling. Each usage period is a consecutive duration where an individual concurrency scaling cluster is actively processing queries.
SVCS\$1CONCURRENCY\$1SCALING\$1USAGE This table is visible to superusers. The database's superuser can choose to open it up to all users.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_CONCURRENCY_SCALING_USAGE.html)
## Sample queries
To view the usage duration in seconds for a specific period, enter the following query:
```
select * from svcs_concurrency_scaling_usage order by start_time;
start_time | end_time | queries | usage_in_seconds
----------------------------+----------------------------+---------+------------------
2019-02-14 18:43:53.01063 | 2019-02-14 19:16:49.781649 | 48 | 1977
```
# SVCS\$1EXPLAIN
Displays the EXPLAIN plan for a query that has been submitted for execution.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the tables with the prefix STL except that the STL tables provide information only for queries run on the main cluster.
SVCS\$1EXPLAIN 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_EXPLAIN.html)
## Sample queries
Consider the following EXPLAIN output for an aggregate join query:
```
explain select avg(datediff(day, listtime, saletime)) as avgwait
from sales, listing where sales.listid = listing.listid;
QUERY PLAN
------------------------------------------------------------------------------
XN Aggregate (cost=6350.30..6350.31 rows=1 width=16)
-> XN Hash Join DS_DIST_NONE (cost=47.08..6340.89 rows=3766 width=16)
Hash Cond: ("outer".listid = "inner".listid)
-> XN Seq Scan on listing (cost=0.00..1924.97 rows=192497 width=12)
-> XN Hash (cost=37.66..37.66 rows=3766 width=12)
-> XN Seq Scan on sales (cost=0.00..37.66 rows=3766 width=12)
(6 rows)
```
If you run this query and its query ID is 10, you can use the SVCS\$1EXPLAIN table to see the same kind of information that the EXPLAIN command returns:
```
select query,nodeid,parentid,substring(plannode from 1 for 30),
substring(info from 1 for 20) from svcs_explain
where query=10 order by 1,2;
query| nodeid |parentid| substring | substring
-----+--------+--------+--------------------------------+-------------------
10 | 1 | 0 |XN Aggregate (cost=6717.61..6 |
10 | 2 | 1 | -> XN Merge Join DS_DIST_NO| Merge Cond:("outer"
10 | 3 | 2 | -> XN Seq Scan on lis |
10 | 4 | 2 | -> XN Seq Scan on sal |
(4 rows)
```
Consider the following query:
```
select event.eventid, sum(pricepaid)
from event, sales
where event.eventid=sales.eventid
group by event.eventid order by 2 desc;
eventid | sum
--------+----------
289 | 51846.00
7895 | 51049.00
1602 | 50301.00
851 | 49956.00
7315 | 49823.00
...
```
If this query's ID is 15, the following system table query returns the plan nodes that were performed. In this case, the order of the nodes is reversed to show the actual order of execution:
```
select query,nodeid,parentid,substring(plannode from 1 for 56)
from svcs_explain where query=15 order by 1, 2 desc;
query|nodeid|parentid| substring
-----+------+--------+--------------------------------------------------------
15 | 8 | 7 | -> XN Seq Scan on eve
15 | 7 | 5 | -> XN Hash(cost=87.98..87.9
15 | 6 | 5 | -> XN Seq Scan on sales(cos
15 | 5 | 4 | -> XN Hash Join DS_DIST_OUTER(cos
15 | 4 | 3 | -> XN HashAggregate(cost=862286577.07..
15 | 3 | 2 | -> XN Sort(cost=1000862287175.47..10008622871
15 | 2 | 1 | -> XN Network(cost=1000862287175.47..1000862287197.
15 | 1 | 0 |XN Merge(cost=1000862287175.47..1000862287197.46 rows=87
(8 rows)
```
The following query retrieves the query IDs for any query plans that contain a window function:
```
select query, trim(plannode) from svcs_explain
where plannode like '%Window%';
query| btrim
-----+------------------------------------------------------------------------
26 | -> XN Window(cost=1000985348268.57..1000985351256.98 rows=170 width=33)
27 | -> XN Window(cost=1000985348268.57..1000985351256.98 rows=170 width=33)
(2 rows)
```
# SVCS\$1PLAN\$1INFO
Use the SVCS\$1PLAN\$1INFO table to look at the EXPLAIN output for a query in terms of a set of rows. This is an alternative way to look at query plans.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the tables with the prefix STL except that the STL tables provide information only for queries run on the main cluster.
SVCS\$1PLAN\$1INFO 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_PLAN_INFO.html)
## Sample queries
The following examples compare the query plans for a simple SELECT query returned by using the EXPLAIN command and by querying the SVCS\$1PLAN\$1INFO table.
```
explain select * from category;
QUERY PLAN
-------------------------------------------------------------
XN Seq Scan on category (cost=0.00..0.11 rows=11 width=49)
(1 row)
select * from category;
catid | catgroup | catname | catdesc
-------+----------+-----------+--------------------------------------------
1 | Sports | MLB | Major League Baseball
3 | Sports | NFL | National Football League
5 | Sports | MLS | Major League Soccer
...
select * from svcs_plan_info where query=256;
query | nodeid | segment | step | locus | plannode | startupcost | totalcost
| rows | bytes
-------+--------+---------+------+-------+----------+-------------+-----------+------+-------
256 | 1 | 0 | 1 | 0 | 104 | 0 | 0.11 | 11 | 539
256 | 1 | 0 | 0 | 0 | 104 | 0 | 0.11 | 11 | 539
(2 rows)
```
In this example, PLANNODE 104 refers to the sequential scan of the CATEGORY table.
```
select distinct eventname from event order by 1;
eventname
------------------------------------------------------------------------
.38 Special
3 Doors Down
70s Soul Jam
A Bronx Tale
...
explain select distinct eventname from event order by 1;
QUERY PLAN
-------------------------------------------------------------------------------------
XN Merge (cost=1000000000136.38..1000000000137.82 rows=576 width=17)
Merge Key: eventname
-> XN Network (cost=1000000000136.38..1000000000137.82 rows=576
width=17)
Send to leader
-> XN Sort (cost=1000000000136.38..1000000000137.82 rows=576
width=17)
Sort Key: eventname
-> XN Unique (cost=0.00..109.98 rows=576 width=17)
-> XN Seq Scan on event (cost=0.00..87.98 rows=8798
width=17)
(8 rows)
select * from svcs_plan_info where query=240 order by nodeid desc;
query | nodeid | segment | step | locus | plannode | startupcost |
totalcost | rows | bytes
-------+--------+---------+------+-------+----------+------------------+------------------+------+--------
240 | 5 | 0 | 0 | 0 | 104 | 0 | 87.98 | 8798 | 149566
240 | 5 | 0 | 1 | 0 | 104 | 0 | 87.98 | 8798 | 149566
240 | 4 | 0 | 2 | 0 | 117 | 0 | 109.975 | 576 | 9792
240 | 4 | 0 | 3 | 0 | 117 | 0 | 109.975 | 576 | 9792
240 | 4 | 1 | 0 | 0 | 117 | 0 | 109.975 | 576 | 9792
240 | 4 | 1 | 1 | 0 | 117 | 0 | 109.975 | 576 | 9792
240 | 3 | 1 | 2 | 0 | 114 | 1000000000136.38 | 1000000000137.82 | 576 | 9792
240 | 3 | 2 | 0 | 0 | 114 | 1000000000136.38 | 1000000000137.82 | 576 | 9792
240 | 2 | 2 | 1 | 0 | 123 | 1000000000136.38 | 1000000000137.82 | 576 | 9792
240 | 1 | 3 | 0 | 0 | 122 | 1000000000136.38 | 1000000000137.82 | 576 | 9792
(10 rows)
```
# SVCS\$1QUERY\$1SUMMARY
Use the SVCS\$1QUERY\$1SUMMARY view to find general information about the execution of a query.
Note that the information in SVCS\$1QUERY\$1SUMMARY is aggregated from all nodes.
**Note**
The SVCS\$1QUERY\$1SUMMARY view only contains information about queries completed by Amazon Redshift, not other utility and DDL commands. For a complete listing and information on all statements completed by Amazon Redshift, including DDL and utility commands, you can query the SVL\$1STATEMENTTEXT view.
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the views with the prefix SVL except that the SVL views provide information only for queries run on the main cluster.
SVCS\$1QUERY\$1SUMMARY 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
For information about SVL\$1QUERY\$1SUMMARY, see [SVL\$1QUERY\$1SUMMARY](r_SVL_QUERY_SUMMARY.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_QUERY_SUMMARY.html)
## Sample queries
**Viewing processing information for a query step**
The following query shows basic processing information for each step of query 87:
```
select query, stm, seg, step, rows, bytes
from svcs_query_summary
where query = 87
order by query, seg, step;
```
This query retrieves the processing information about query 87, as shown in the following sample output:
```
query | stm | seg | step | rows | bytes
-------+-----+-----+------+--------+---------
87 | 0 | 0 | 0 | 90 | 1890
87 | 0 | 0 | 2 | 90 | 360
87 | 0 | 1 | 0 | 90 | 360
87 | 0 | 1 | 2 | 90 | 1440
87 | 1 | 2 | 0 | 210494 | 4209880
87 | 1 | 2 | 3 | 89500 | 0
87 | 1 | 2 | 6 | 4 | 96
87 | 2 | 3 | 0 | 4 | 96
87 | 2 | 3 | 1 | 4 | 96
87 | 2 | 4 | 0 | 4 | 96
87 | 2 | 4 | 1 | 1 | 24
87 | 3 | 5 | 0 | 1 | 24
87 | 3 | 5 | 4 | 0 | 0
(13 rows)
```
**Determining whether query steps spilled to disk**
The following query shows whether or not any of the steps for the query with query ID 1025 (see the [SVL\$1QLOG](r_SVL_QLOG.md) view to learn how to obtain the query ID for a query) spilled to disk or if the query ran entirely in-memory:
```
select query, step, rows, workmem, label, is_diskbased
from svcs_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| 141557760 |scan tbl=9 | f
1025 | 2 |16000000| 135266304 |hash tbl=142 | t
1025 | 0 |16000000| 128974848 |scan tbl=116536| f
1025 | 2 |16000000| 122683392 |dist | f
(4 rows)
```
By scanning the values for IS\$1DISKBASED, you can see which query steps went to disk. For query 1025, the hash step ran on disk. Steps might run on disk include hash, aggr, and sort steps. To view only disk-based query steps, add **and is\$1diskbased = 't'** clause to the SQL statement in the above example.
# SVCS\$1S3LIST
Use the SVCS\$1S3LIST view to get details about Amazon Redshift Spectrum queries at the segment level. One segment can perform one external table scan. This view is derived from the SVL\$1S3LIST system view but doesn't show slice-level for queries run on a concurrency scaling cluster.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the views with the prefix SVL except that the SVL views provide information only for queries run on the main cluster.
SVCS\$1S3LIST 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](cm_chap_system-tables.md#c_visibility-of-data).
For information about SVL\$1S3LIST, see [SVL\$1S3LIST](r_SVL_S3LIST.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_S3LIST.html)
## Sample query
The following example queries SVCS\$1S3LIST for the last query performed.
```
select *
from svcs_s3list
where query = pg_last_query_id()
order by query,segment;
```
# SVCS\$1S3LOG
Use the SVCS\$1S3LOG view to get troubleshooting details about Redshift Spectrum queries at the segment level. One segment can perform one external table scan. This view is derived from the SVL\$1S3LOG system view but doesn't show slice-level for queries run on a concurrency scaling cluster.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the views with the prefix SVL except that the SVL views provide information only for queries run on the main cluster.
SVCS\$1S3LOG 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](cm_chap_system-tables.md#c_visibility-of-data).
For information about SVL\$1S3LOG, see [SVL\$1S3LOG](r_SVL_S3LOG.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_S3LOG.html)
## Sample query
The following example queries SVCS\$1S3LOG for the last query that ran.
```
select *
from svcs_s3log
where query = pg_last_query_id()
order by query,segment;
```
# SVCS\$1S3PARTITION\$1SUMMARY
Use the SVCS\$1S3PARTITION\$1SUMMARY view to get a summary of Redshift Spectrum queries partition processing at the segment level. One segment can perform one external table scan.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the views with the prefix SVL except that the SVL views provide information only for queries run on the main cluster.
SVCS\$1S3PARTITION\$1SUMMARY 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](cm_chap_system-tables.md#c_visibility-of-data).
For information about SVL\$1S3PARTITION, see [SVL\$1S3PARTITION](r_SVL_S3PARTITION.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_S3PARTITION_SUMMARY.html)
## Sample query
The following example gets the partition scan details for the last query performed.
```
select query, segment, assignment, min_starttime, max_endtime, min_duration, avg_duration
from svcs_s3partition_summary
where query = pg_last_query_id()
order by query,segment;
```
# SVCS\$1S3QUERY\$1SUMMARY
Use the SVCS\$1S3QUERY\$1SUMMARY view to get a summary of all Redshift Spectrum queries (S3 queries) that have been run on the system. One segment can perform one external table scan.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the views with the prefix SVL except that the SVL views provide information only for queries run on the main cluster.
SVCS\$1S3QUERY\$1SUMMARY 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](cm_chap_system-tables.md#c_visibility-of-data).
For information about SVL\$1S3QUERY, see [SVL\$1S3QUERY](r_SVL_S3QUERY.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_S3QUERY_SUMMARY.html)
## Sample query
The following example gets the scan step details for the last query run.
```
select query, segment, elapsed, s3_scanned_rows, s3_scanned_bytes, s3query_returned_rows, s3query_returned_bytes, files
from svcs_s3query_summary
where query = pg_last_query_id()
order by query,segment;
```
```
query | segment | elapsed | s3_scanned_rows | s3_scanned_bytes | s3query_returned_rows | s3query_returned_bytes | files
------+---------+---------+-----------------+------------------+-----------------------+------------------------+------
4587 | 2 | 67811 | 0 | 0 | 0 | 0 | 0
4587 | 2 | 591568 | 172462 | 11260097 | 8513 | 170260 | 1
4587 | 2 | 216849 | 0 | 0 | 0 | 0 | 0
4587 | 2 | 216671 | 0 | 0 | 0 | 0 | 0
```
# SVCS\$1STREAM\$1SEGS
Lists the relationship between streams and concurrent segments.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the tables with the prefix STL except that the STL tables provide information only for queries run on the main cluster.
SVCS\$1STREAM\$1SEGS 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_STREAM_SEGS.html)
## Sample queries
To view the relationship between streams and concurrent segments for the most recent query, type the following query:
```
select *
from svcs_stream_segs
where query = pg_last_query_id();
query | stream | segment
-------+--------+---------
10 | 1 | 2
10 | 0 | 0
10 | 2 | 4
10 | 1 | 3
10 | 0 | 1
(5 rows)
```
# SVCS\$1UNLOAD\$1LOG
Use the SVCS\$1UNLOAD\$1LOG to get details of UNLOAD operations.
SVCS\$1UNLOAD\$1LOG records one row for each file created by an UNLOAD statement. For example, if an UNLOAD creates 12 files, SVCS\$1UNLOAD\$1LOG contains 12 corresponding rows. This view is derived from the STL\$1UNLOAD\$1LOG system table but doesn't show slice-level for queries run on a concurrency scaling cluster.
**Note**
System views with the prefix SVCS provide details about queries on both the main and concurrency scaling clusters. The views are similar to the tables with the prefix STL except that the STL tables provide information only for queries run on the main cluster.
SVCS\$1UNLOAD\$1LOG 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_UNLOAD_LOG.html)
## Sample query
To get a list of the files that were written to Amazon S3 by an UNLOAD command, you can call an Amazon S3 list operation after the UNLOAD completes; however, depending on how quickly you issue the call, the list might be incomplete because an Amazon S3 list operation is eventually consistent. To get a complete, authoritative list immediately, query SVCS\$1UNLOAD\$1LOG.
The following query returns the path name for files that were created by an UNLOAD for the last query completed:
```
select query, substring(path,0,40) as path
from svcs_unload_log
where query = pg_last_query_id()
order by path;
```
This command returns the following sample output:
```
query | path
------+---------------------------------------------
2320 | s3://amzn-s3-demo-bucket/venue0000_part_00
2320 | s3://amzn-s3-demo-bucket/venue0001_part_00
2320 | s3://amzn-s3-demo-bucket/venue0002_part_00
2320 | s3://amzn-s3-demo-bucket/venue0003_part_00
(4 rows)
```
# SVL views for main cluster
SVL views are system views in Amazon Redshift that contain references to STL tables and logs for more detailed information.
These views provide quicker and easier access to commonly queried data found in those tables.
**Note**
The SVL\$1QUERY\$1SUMMARY view only contains information about queries run by Amazon Redshift, not other utility and DDL commands. For a complete listing and information on all statements run by Amazon Redshift, including DDL and utility commands, you can query the SVL\$1STATEMENTTEXT view.
**Topics**
+ [SVL\$1AUTO\$1WORKER\$1ACTION](r_SVL_AUTO_WORKER_ACTION.md)
+ [SVL\$1COMPILE](r_SVL_COMPILE.md)
+ [SVL\$1DATASHARE\$1CHANGE\$1LOG](r_SVL_DATASHARE_CHANGE_LOG.md)
+ [SVL\$1DATASHARE\$1CROSS\$1REGION\$1USAGE](r_SVL_DATASHARE_CROSS_REGION_USAGE.md)
+ [SVL\$1DATASHARE\$1USAGE\$1CONSUMER](r_SVL_DATASHARE_USAGE_CONSUMER.md)
+ [SVL\$1DATASHARE\$1USAGE\$1PRODUCER](r_SVL_DATASHARE_USAGE_PRODUCER.md)
+ [SVL\$1FEDERATED\$1QUERY](r_SVL_FEDERATED_QUERY.md)
+ [SVL\$1MULTI\$1STATEMENT\$1VIOLATIONS](r_SVL_MULTI_STATEMENT_VIOLATIONS.md)
+ [SVL\$1MV\$1REFRESH\$1STATUS](r_SVL_MV_REFRESH_STATUS.md)
+ [SVL\$1QERROR](r_SVL_QERROR.md)
+ [SVL\$1QLOG](r_SVL_QLOG.md)
+ [SVL\$1QUERY\$1METRICS](r_SVL_QUERY_METRICS.md)
+ [SVL\$1QUERY\$1METRICS\$1SUMMARY](r_SVL_QUERY_METRICS_SUMMARY.md)
+ [SVL\$1QUERY\$1QUEUE\$1INFO](r_SVL_QUERY_QUEUE_INFO.md)
+ [SVL\$1QUERY\$1REPORT](r_SVL_QUERY_REPORT.md)
+ [SVL\$1QUERY\$1SUMMARY](r_SVL_QUERY_SUMMARY.md)
+ [SVL\$1RESTORE\$1ALTER\$1TABLE\$1PROGRESS](r_SVL_RESTORE_ALTER_TABLE_PROGRESS.md)
+ [SVL\$1S3LIST](r_SVL_S3LIST.md)
+ [SVL\$1S3LOG](r_SVL_S3LOG.md)
+ [SVL\$1S3PARTITION](r_SVL_S3PARTITION.md)
+ [SVL\$1S3PARTITION\$1SUMMARY](r_SVL_S3PARTITION_SUMMARY.md)
+ [SVL\$1S3QUERY](r_SVL_S3QUERY.md)
+ [SVL\$1S3QUERY\$1SUMMARY](r_SVL_S3QUERY_SUMMARY.md)
+ [SVL\$1S3RETRIES](r_SVL_S3RETRIES.md)
+ [SVL\$1SPATIAL\$1SIMPLIFY](r_SVL_SPATIAL_SIMPLIFY.md)
+ [SVL\$1SPECTRUM\$1SCAN\$1ERROR](r_SVL_SPECTRUM_SCAN_ERROR.md)
+ [SVL\$1STATEMENTTEXT](r_SVL_STATEMENTTEXT.md)
+ [SVL\$1STORED\$1PROC\$1CALL](r_SVL_STORED_PROC_CALL.md)
+ [SVL\$1STORED\$1PROC\$1MESSAGES](r_SVL_STORED_PROC_MESSAGES.md)
+ [SVL\$1TERMINATE](r_SVL_TERMINATE.md)
+ [SVL\$1UDF\$1LOG](r_SVL_UDF_LOG.md)
+ [SVL\$1USER\$1INFO](r_SVL_USER_INFO.md)
+ [SVL\$1VACUUM\$1PERCENTAGE](r_SVL_VACUUM_PERCENTAGE.md)
# SVL\$1AUTO\$1WORKER\$1ACTION
Records automated actions taken by Amazon Redshift on tables defined for automatic optimization.
SVL\$1AUTO\$1WORKER\$1ACTION is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_AUTO_WORKER_ACTION.html)
Some examples of values of the `status` column are as follows:
+ Skipped:Table not found.
+ Skipped:Recommendation is empty.
+ Skipped:Apply sortkey recommendation is disabled.
+ Skipped:Retry exceeds the maximum limit for a table.
+ Skipped:Table column has changed.
+ Abort:This table is not AUTO.
+ Abort:This table has been recently converted.
+ Abort:This table exceeds table size threshold.
+ Abort:This table is already the recommended style.
+ Checkpoint: progress *21.9963*%.
## Sample queries
In the following example, the rows in the result show actions taken by Amazon Redshift.
```
select table_id, type, status, eventtime, sequence, previous_state
from SVL_AUTO_WORKER_ACTION;
```
```
table_id | type | status | eventtime | sequence | previous_state
----------+---------+------------------------------------------------------+----------------------------+----------+----------------
118082 | sortkey | Start | 2020-08-22 19:42:20.727049 | 0 |
118078 | sortkey | Start | 2020-08-22 19:43:54.728819 | 0 |
118082 | sortkey | Start | 2020-08-22 19:42:52.690264 | 0 |
118072 | sortkey | Start | 2020-08-22 19:44:14.793572 | 0 |
118082 | sortkey | Failed | 2020-08-22 19:42:20.728917 | 0 |
118078 | sortkey | Complete | 2020-08-22 19:43:54.792705 | 0 | SORTKEY: None;
118086 | sortkey | Complete | 2020-08-22 19:42:00.72635 | 0 | SORTKEY: None;
118082 | sortkey | Complete | 2020-08-22 19:43:34.728144 | 0 | SORTKEY: None;
118072 | sortkey | Skipped:Retry exceeds the maximum limit for a table. | 2020-08-22 19:44:46.706155 | 0 |
118086 | sortkey | Start | 2020-08-22 19:42:00.685255 | 0 |
118082 | sortkey | Start | 2020-08-22 19:43:34.69531 | 0 |
118072 | sortkey | Start | 2020-08-22 19:44:46.703331 | 0 |
118082 | sortkey | Checkpoint: progress 14.755079% | 2020-08-22 19:42:52.692828 | 0 |
118072 | sortkey | Failed | 2020-08-22 19:44:14.796071 | 0 |
116723 | sortkey | Abort:This table is not AUTO. | 2020-10-28 05:12:58.479233 | 0 |
110203 | distkey | Abort:This table is not AUTO. | 2020-10-28 05:45:54.67259 | 0 |
```
# SVL\$1COMPILE
Records compile time and location for each query segment of queries.
SVL\$1COMPILE 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
SVL\$1COMPILE only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
For information about SVCS\$1COMPILE, see [SVCS\$1COMPILE](r_SVCS_COMPILE.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_COMPILE.html)
## Sample queries
In this example, queries 35878 and 35879 ran the same SQL statement. The compile column for query 35878 shows `1` for four query segments, which indicates that the segments were compiled. Query 35879 shows `0` in the compile column for every segment, indicating that the segments did not need to be compiled again.
```
select userid, xid, pid, query, segment, locus,
datediff(ms, starttime, endtime) as duration, compile
from svl_compile
where query = 35878 or query = 35879
order by query, segment;
userid | xid | pid | query | segment | locus | duration | compile
--------+--------+-------+-------+---------+-------+----------+---------
100 | 112780 | 23028 | 35878 | 0 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 1 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 2 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 3 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 4 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 5 | 1 | 0 | 0
100 | 112780 | 23028 | 35878 | 6 | 1 | 1380 | 1
100 | 112780 | 23028 | 35878 | 7 | 1 | 1085 | 1
100 | 112780 | 23028 | 35878 | 8 | 1 | 1197 | 1
100 | 112780 | 23028 | 35878 | 9 | 2 | 905 | 1
100 | 112782 | 23028 | 35879 | 0 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 1 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 2 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 3 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 4 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 5 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 6 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 7 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 8 | 1 | 0 | 0
100 | 112782 | 23028 | 35879 | 9 | 2 | 0 | 0
(20 rows)
```
# SVL\$1DATASHARE\$1CHANGE\$1LOG
Records the consolidated view for tracking changes to datashares on both producer and consumer clusters.
SVL\$1DATASHARE\$1CHANGE\$1LOG 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1DATASHARE\$1CHANGE\$1LOG](SYS_DATASHARE_CHANGE_LOG.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_DATASHARE_CHANGE_LOG.html)
## Sample queries
The following example shows a SVL\$1DATASHARE\$1CHANGE\$1LOG view.
```
SELECT DISTINCT action
FROM svl_datashare_change_log
WHERE share_object_name LIKE 'tickit%';
action
-----------------------
"ALTER DATASHARE ADD"
```
# SVL\$1DATASHARE\$1CROSS\$1REGION\$1USAGE
Use the SVL\$1DATASHARE\$1CROSS\$1REGION\$1USAGE view to get a summary of cross-Region data transferred usage caused by cross-Region datasharing query. SVL\$1DATASHARE\$1CROSS\$1REGION\$1USAGE aggregates details at the segment level.
SVL\$1DATASHARE\$1CROSS\$1REGION\$1USAGE 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1DATASHARE\$1CROSS\$1REGION\$1USAGE](r_SYS_DATASHARE_CROSS_REGION_USAGE.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_DATASHARE_CROSS_REGION_USAGE.html)
## Sample queries
The following example shows a SVL\$1DATASHARE\$1CROSS\$1REGION\$1USAGE view.
```
SELECT query, segment, transferred_data, source_region
from svl_datashare_cross_region_usage
where query = pg_last_query_id()
order by query,segment;
query | segment | transferred_data | source_region
--------+---------+------------------+---------------
200048 | 2 | 4194304 | us-west-1
200048 | 2 | 4194304 | us-east-2
```
# SVL\$1DATASHARE\$1USAGE\$1CONSUMER
Records the activity and usage of datashares. This view is only relevant on the consumer cluster.
SVL\$1DATASHARE\$1USAGE\$1CONSUMER 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1DATASHARE\$1USAGE\$1CONSUMER](SYS_DATASHARE_USAGE_CONSUMER.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_DATASHARE_USAGE_CONSUMER.html)
## Sample queries
The following example shows a SVL\$1DATASHARE\$1USAGE\$1CONSUMER view.
```
SELECT request_type, status, trim(error) AS error
FROM svl_datashare_usage_consumer
request_type | status | error
----------------+--------+--------
"GET RELATION" | 0 |
```
# SVL\$1DATASHARE\$1USAGE\$1PRODUCER
Records the activity and usage of datashares. This view is only relevant on the producer cluster.
SVL\$1DATASHARE\$1USAGE\$1PRODUCER 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1DATASHARE\$1USAGE\$1PRODUCER](SYS_DATASHARE_USAGE_PRODUCER.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_DATASHARE_USAGE_PRODUCER.html)
## Sample queries
The following example shows a SVL\$1DATASHARE\$1USAGE\$1PRODUCER view.
```
SELECT DISTINCT request_type
FROM svl_datashare_usage_producer
WHERE object_name LIKE 'tickit%';
request_type
------------------
"GET RELATION"
```
# SVL\$1FEDERATED\$1QUERY
Use the SVL\$1FEDERATED\$1QUERY view to view information about a federated query call.
SVL\$1FEDERATED\$1QUERY 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1EXTERNAL\$1QUERY\$1DETAIL](SYS_EXTERNAL_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_FEDERATED_QUERY.html)
## Sample queries
To show information about federated query calls, run the following query.
```
select query, trim(sourcetype) as type, recordtime, trim(querytext) as "PG Subquery" from svl_federated_query where query = 4292;
query | type | recordtime | pg subquery
-------+------+----------------------------+---------------------------------------------------------------
4292 | PG | 2020-03-27 04:29:58.485126 | SELECT "level" FROM functional.employees WHERE ("level" >= 6)
(1 row)
```
# SVL\$1MULTI\$1STATEMENT\$1VIOLATIONS
Use the SVL\$1MULTI\$1STATEMENT\$1VIOLATIONS view to get a complete record of all of the SQL commands run on the system that violates transaction block restrictions.
Violations occur when you run any of the following SQL commands that Amazon Redshift restricts inside a transaction block or multi-statement requests:
+ [CREATE DATABASE](r_CREATE_DATABASE.md)
+ [DROP DATABASE](r_DROP_DATABASE.md)
+ [ALTER TABLE APPEND](r_ALTER_TABLE_APPEND.md)
+ [CREATE EXTERNAL TABLE](r_CREATE_EXTERNAL_TABLE.md)
+ DROP EXTERNAL TABLE
+ RENAME EXTERNAL TABLE
+ ALTER EXTERNAL TABLE
+ CREATE TABLESPACE
+ DROP TABLESPACE
+ [CREATE LIBRARY](r_CREATE_LIBRARY.md)
+ [DROP LIBRARY](r_DROP_LIBRARY.md)
+ REBUILDCAT
+ INDEXCAT
+ REINDEX DATABASE
+ [VACUUM](r_VACUUM_command.md)
+ [GRANT](r_GRANT.md)
+ [COPY](r_COPY.md)
**Note**
If there are any entries in this view, then change your corresponding applications and SQL scripts. We recommend changing your application code to move the use of these restricted SQL commands outside of the transaction block. If you need further assistance, contact AWS Support.
SVL\$1MULTI\$1STATEMENT\$1VIOLATIONS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_MULTI_STATEMENT_VIOLATIONS.html)
## Sample query
The following query returns multiple statements that have violations.
```
select * from svl_multi_statement_violations order by starttime asc;
userid | database | cmdname | xid | pid | label | starttime | endtime | sequence | type | text
==============================================================================================================================
1 | dev | CREATE DATABASE | 1034 | 5729 |label1 | ********* | ******* | 0 | DDL | create table c(b int);
1 | dev | CREATE DATABASE | 1034 | 5729 |label1 | ********* | ******* | 0 | UTILITY | create database b;
1 | dev | CREATE DATABASE | 1034 | 5729 |label1 | ********* | ******* | 0 | UTILITY | COMMIT
...
```
# SVL\$1MV\$1REFRESH\$1STATUS
The SVL\$1MV\$1REFRESH\$1STATUS view contains a row for the refresh activity of materialized views.
For more information about materialized views, see [Materialized views in Amazon Redshift](materialized-view-overview.md).
SVL\$1MV\$1REFRESH\$1STATUS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1MV\$1REFRESH\$1HISTORY](SYS_MV_REFRESH_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_MV_REFRESH_STATUS.html)
## Sample query
To view the refresh status of materialized views, run the following query.
```
select * from svl_mv_refresh_status;
```
This query returns the following sample output:
```
db_name | userid | schema | name | xid | starttime | endtime | status | refresh_type | consumer_account | consumer_region | consumer_namespace
---------+--------+-----------+---------+-------+----------------------------+----------------------------+-------------------------------------------------------------------------------------+---------------+------------------+-----------------+------------------------------------
dev | 169 | mv_schema | mv_test | 6640 | 2020-02-14 02:26:53.497935 | 2020-02-14 02:26:53.556156 | Refresh successfully recomputed MV from scratch | Manual | | |
dev | 166 | mv_schema | mv_test | 6517 | 2020-02-14 02:26:39.287438 | 2020-02-14 02:26:39.349539 | Refresh successfully updated MV incrementally | Auto | | |
ext_db | 162 | producer_schema | producer_mv | 6388 | 2020-02-14 02:26:27.863426 | 2020-02-14 02:26:27.918307 | Refresh successfully updated MV incrementally | Manual | 0123456789 | us-east-1 | 623d8ff2-4391-4381-83d7-177caa6767af
dev | 161 | mv_schema | mv_test | 6323 | 2020-02-14 02:26:20.020717 | 2020-02-14 02:26:20.080002 | Refresh successfully updated MV incrementally | Auto | | |
dev | 161 | mv_schema | mv_test | 6301 | 2020-02-14 02:26:05.796146 | 2020-02-14 02:26:07.853986 | Refresh successfully recomputed MV from scratch | Manual | | |
dev | 153 | mv_schema | mv_test | 6024 | 2020-02-14 02:25:18.762335 | 2020-02-14 02:25:20.043462 | MV was already updated | Manual | | |
dev | 143 | mv_schema | mv_test | 5557 | 2020-02-14 02:24:23.100601 | 2020-02-14 02:24:23.100633 | MV was already updated | Manual
dev | 141 | mv_schema | mv_test | 5447 | 2020-02-14 02:23:54.102837 | 2020-02-14 02:24:00.310166 | Refresh successfully updated MV incrementally | Auto
dev | 1 | mv_schema | mv_test | 5329 | 2020-02-14 02:22:26.328481 | 2020-02-14 02:22:28.369217 | Refresh successfully recomputed MV from scratch | Auto
dev | 138 | mv_schema | mv_test | 5290 | 2020-02-14 02:21:56.885093 | 2020-02-14 02:21:56.885098 | Refresh failed. MV was not found | Manual
dev | 138 | mv_schema | mv_test | 5100 | 2020-02-14 02:20:33.123445 | 2020-02-14 02:20:33.123445 | Cascade refresh failed because materialized view mv_schema.child was not refreshed. | Manual
dev | 138 | mv_schema | child | 5100 | 2020-02-14 02:20:33.123445 | 2020-02-14 02:20:33.123445 | Refresh failed due to an internal error. | Manual
dev | 138 | mv_schema | mv_test | 5099 | 2020-02-14 02:10:23.492344 | 2020-02-14 02:10:23.492344 | Cascade refresh skipped because materialized view mv_schema.child was not refreshed.| Manual
dev | 138 | mv_schema | child | 5099 | 2020-02-14 02:10:23.492344 | 2020-02-14 02:10:23.492344 | Refresh failed due to an internal error. | Manual
```
# SVL\$1QERROR
The SVL\$1QERROR view is deprecated.
# SVL\$1QLOG
The SVL\$1QLOG view contains a log of all queries run against the database.
Amazon Redshift creates the SVL\$1QLOG view as a readable subset of information from the [STL\$1QUERY](r_STL_QUERY.md) table. Use this table to find the query ID for a recently run query or to see how long it took a query to complete.
SVL\$1QLOG 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QLOG.html)
## Sample queries
The following example returns the query ID, execution time, and truncated query text for the five most recent database queries run by the user with `userid = 100`.
```
select query, pid, elapsed, substring from svl_qlog
where userid = 100
order by starttime desc
limit 5;
query | pid | elapsed | substring
--------+-------+----------+-----------------------------------------------
187752 | 18921 | 18465685 | select query, elapsed, substring from svl_...
204168 | 5117 | 59603 | insert into testtable values (100);
187561 | 17046 | 1003052 | select * from pg_table_def where tablename...
187549 | 17046 | 1108584 | select * from STV_WLM_SERVICE_CLASS_CONFIG
187468 | 17046 | 5670661 | select * from pg_table_def where schemaname...
(5 rows)
```
The following example returns the SQL script name (LABEL column) and elapsed time for a query that was cancelled (**aborted=1**):
```
select query, elapsed, trim(label) querylabel
from svl_qlog where aborted=1;
query | elapsed | querylabel
-------+----------+-------------------------
16 | 6935292 | alltickittablesjoin.sql
(1 row)
```
# SVL\$1QUERY\$1METRICS
The SVL\$1QUERY\$1METRICS view shows the metrics for completed queries. This view is derived from the [STL\$1QUERY\$1METRICS](r_STL_QUERY_METRICS.md) system table. Use the values in this view as an aid to determine threshold values for defining query monitoring rules. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).
SVL\$1QUERY\$1METRICS 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QUERY_METRICS.html)
# SVL\$1QUERY\$1METRICS\$1SUMMARY
The SVL\$1QUERY\$1METRICS\$1SUMMARY view shows the maximum values of metrics for completed queries. This view is derived from the [STL\$1QUERY\$1METRICS](r_STL_QUERY_METRICS.md) system table. Use the values in this view as an aid to determine threshold values for defining query monitoring rules. For more information about rules and metrics for query monitoring for Amazon Redshift, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).
SVL\$1QUERY\$1METRICS\$1SUMMARY 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QUERY_METRICS_SUMMARY.html)
# SVL\$1QUERY\$1QUEUE\$1INFO
Summarizes details for queries that spent time in a workload management (WLM) query queue or a commit queue.
The SVL\$1QUERY\$1QUEUE\$1INFO view filters queries performed by the system and shows only queries performed by a user.
The SVL\$1QUERY\$1QUEUE\$1INFO view summarizes information from the [STL\$1QUERY](r_STL_QUERY.md), [STL\$1WLM\$1QUERY](r_STL_WLM_QUERY.md), and [STL\$1COMMIT\$1STATS](r_STL_COMMIT_STATS.md) system tables.
SVL\$1QUERY\$1QUEUE\$1INFO is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QUERY_QUEUE_INFO.html)
## Sample queries
The following example shows the time that queries spent in WLM queues.
```
select query, service_class, queue_elapsed, exec_elapsed, wlm_total_elapsed
from svl_query_queue_info
where wlm_total_elapsed > 0;
query | service_class | queue_elapsed | exec_elapsed | wlm_total_elapsed
---------+---------------+---------------+--------------+-------------------
2742669 | 6 | 2 | 916 | 918
2742668 | 6 | 4 | 197 | 201
(2 rows)
```
# SVL\$1QUERY\$1REPORT
Amazon Redshift creates the SVL\$1QUERY\$1REPORT view from a UNION of a number of Amazon Redshift STL system tables to provide information about completed query steps.
This view breaks down the information about completed queries by slice and by step, which can help with troubleshooting node and slice issues in the Amazon Redshift cluster.
SVL\$1QUERY\$1REPORT 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QUERY_REPORT.html)
## Sample queries
The following query demonstrates the data skew of the returned rows for the query with query ID 279. Use this query to determine if database data is evenly distributed over the slices in the data warehouse cluster:
```
select query, segment, step, max(rows), min(rows),
case when sum(rows) > 0
then ((cast(max(rows) -min(rows) as float)*count(rows))/sum(rows))
else 0 end
from svl_query_report
where query = 279
group by query, segment, step
order by segment, step;
```
This query should return data similar to the following sample output:
```
query | segment | step | max | min | case
------+---------+------+----------+----------+----------------------
279 | 0 | 0 | 19721687 | 19721687 | 0
279 | 0 | 1 | 19721687 | 19721687 | 0
279 | 1 | 0 | 986085 | 986084 | 1.01411202804304e-06
279 | 1 | 1 | 986085 | 986084 | 1.01411202804304e-06
279 | 1 | 4 | 986085 | 986084 | 1.01411202804304e-06
279 | 2 | 0 | 1775517 | 788460 | 1.00098637606408
279 | 2 | 2 | 1775517 | 788460 | 1.00098637606408
279 | 3 | 0 | 1775517 | 788460 | 1.00098637606408
279 | 3 | 2 | 1775517 | 788460 | 1.00098637606408
279 | 3 | 3 | 1775517 | 788460 | 1.00098637606408
279 | 4 | 0 | 1775517 | 788460 | 1.00098637606408
279 | 4 | 1 | 1775517 | 788460 | 1.00098637606408
279 | 4 | 2 | 1 | 1 | 0
279 | 5 | 0 | 1 | 1 | 0
279 | 5 | 1 | 1 | 1 | 0
279 | 6 | 0 | 20 | 20 | 0
279 | 6 | 1 | 1 | 1 | 0
279 | 7 | 0 | 1 | 1 | 0
279 | 7 | 1 | 0 | 0 | 0
(19 rows)
```
# SVL\$1QUERY\$1SUMMARY
Use the SVL\$1QUERY\$1SUMMARY view to find general information about the execution of a query.
The SVL\$1QUERY\$1SUMMARY view contains a subset of data from the SVL\$1QUERY\$1REPORT view. Note that the information in SVL\$1QUERY\$1SUMMARY is aggregated from all nodes.
**Note**
The SVL\$1QUERY\$1SUMMARY view only contains information about queries performed by Amazon Redshift, not other utility and DDL commands. For a complete listing and information on all statements performed by Amazon Redshift, including DDL and utility commands, you can query the SVL\$1STATEMENTTEXT view.
SVL\$1QUERY\$1SUMMARY 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1DETAIL](SYS_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
For information about SVCS\$1QUERY\$1SUMMARY, see [SVCS\$1QUERY\$1SUMMARY](r_SVCS_QUERY_SUMMARY.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QUERY_SUMMARY.html)
## Sample queries
**Viewing processing information for a query step**
The following query shows basic processing information for each step of query 87:
```
select query, stm, seg, step, rows, bytes
from svl_query_summary
where query = 87
order by query, seg, step;
```
This query retrieves the processing information about query 87, as shown in the following sample output:
```
query | stm | seg | step | rows | bytes
-------+-----+-----+------+--------+---------
87 | 0 | 0 | 0 | 90 | 1890
87 | 0 | 0 | 2 | 90 | 360
87 | 0 | 1 | 0 | 90 | 360
87 | 0 | 1 | 2 | 90 | 1440
87 | 1 | 2 | 0 | 210494 | 4209880
87 | 1 | 2 | 3 | 89500 | 0
87 | 1 | 2 | 6 | 4 | 96
87 | 2 | 3 | 0 | 4 | 96
87 | 2 | 3 | 1 | 4 | 96
87 | 2 | 4 | 0 | 4 | 96
87 | 2 | 4 | 1 | 1 | 24
87 | 3 | 5 | 0 | 1 | 24
87 | 3 | 5 | 4 | 0 | 0
(13 rows)
```
**Determining whether query steps spilled to disk**
The following query shows whether or not any of the steps for the query with query ID 1025 (see the [SVL\$1QLOG](r_SVL_QLOG.md) view to learn how to obtain the query ID for a query) spilled to disk or if the query ran entirely in-memory:
```
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| 141557760 |scan tbl=9 | f
1025 | 2 |16000000| 135266304 |hash tbl=142 | t
1025 | 0 |16000000| 128974848 |scan tbl=116536| f
1025 | 2 |16000000| 122683392 |dist | f
(4 rows)
```
By scanning the values for IS\$1DISKBASED, you can see which query steps went to disk. For query 1025, the hash step ran on disk. Steps might run on disk include hash, aggr, and sort steps. To view only disk-based query steps, add **and is\$1diskbased = 't'** clause to the SQL statement in the above example.
# SVL\$1RESTORE\$1ALTER\$1TABLE\$1PROGRESS
Use SVL\$1RESTORE\$1ALTER\$1TABLE\$1PROGRESS to monitor the migration progress of each table in the cluster during a classic resize to RA3 nodes. It captures the historic throughput of data migration during the resize operation. For more information about classic resize to RA3 nodes, go to [Classic resize](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-cluster-operations.html#classic-resize-faster).
SVL\$1RESTORE\$1ALTER\$1TABLE\$1PROGRESS is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1RESTORE\$1LOG](SYS_RESTORE_LOG.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
**Note**
Rows with a progress of `100.00%` or `ABORTED` are deleted after 7 days. Rows for tables dropped during or after a classic resize can still appear in SVL\$1RESTORE\$1ALTER\$1TABLE\$1PROGRESS.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_RESTORE_ALTER_TABLE_PROGRESS.html)
## Sample query
The following query returns running and queued queries.
```
select * from svl_restore_alter_table_progress;
tbl | progress | message
--------+----------+-----------------------------------------------------------
105614 | ABORTED | Abort:Table no longer contains the prior dist key column.
105610 | ABORTED | Abort:Table no longer contains the prior dist key column.
105594 | 0.00% | Table waiting for alter diststyle conversion.
105602 | ABORTED | Abort:Table no longer contains the prior dist key column.
105606 | ABORTED | Abort:Table no longer contains the prior dist key column.
105598 | 100.00% | Restored to distkey successfully.
```
# SVL\$1S3LIST
Use the SVL\$1S3LIST view to get details about Amazon Redshift Spectrum queries at the segment level.
SVL\$1S3LIST 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
SVL\$1S3LIST only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1EXTERNAL\$1QUERY\$1DETAIL](SYS_EXTERNAL_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_S3LIST.html)
## Sample query
The following example queries SVL\$1S3LIST for the last query to run.
```
select *
from svl_s3list
where query = pg_last_query_id()
order by query,segment;
```
# SVL\$1S3LOG
Use the SVL\$1S3LOG view to get details about Amazon Redshift Spectrum queries at the segment and node slice level.
SVL\$1S3LOG 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
SVL\$1S3LOG only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1EXTERNAL\$1QUERY\$1DETAIL](SYS_EXTERNAL_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_S3LOG.html)
## Sample query
The following example queries SVL\$1S3LOG for the last query that ran.
```
select *
from svl_s3log
where query = pg_last_query_id()
order by query,segment,slice;
```
# SVL\$1S3PARTITION
Use the SVL\$1S3PARTITION view to get details about Amazon Redshift Spectrum partitions at the segment and node slice level.
SVL\$1S3PARTITION 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
SVL\$1S3PARTITION only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1EXTERNAL\$1QUERY\$1DETAIL](SYS_EXTERNAL_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_S3PARTITION.html)
## Sample query
The following example gets the partition details for the last query completed.
```
SELECT query, segment,
MIN(starttime) AS starttime,
MAX(endtime) AS endtime,
datediff(ms,MIN(starttime),MAX(endtime)) AS dur_ms,
MAX(total_partitions) AS total_partitions,
MAX(qualified_partitions) AS qualified_partitions,
MAX(assignment) as assignment_type
FROM svl_s3partition
WHERE query=pg_last_query_id()
GROUP BY query, segment
```
```
query | segment | starttime | endtime | dur_ms| total_partitions | qualified_partitions | assignment_type
------+---------+-------------------------------+-----------------------------+-------+------------------+----------------------+----------------
99232 | 0 | 2018-04-17 22:43:50.201515 | 2018-04-17 22:43:54.674595 | 4473 | 2526 | 334 | p
```
# SVL\$1S3PARTITION\$1SUMMARY
Use the SVL\$1S3PARTITION\$1SUMMARY view to get a summary of Redshift Spectrum queries partition processing at the segment level.
SVL\$1S3PARTITION\$1SUMMARY 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](cm_chap_system-tables.md#c_visibility-of-data).
For information about SVCS\$1S3PARTITION, see [SVCS\$1S3PARTITION\$1SUMMARY](r_SVCS_S3PARTITION_SUMMARY.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_S3PARTITION_SUMMARY.html)
## Sample query
The following example gets the partition scan details for the last query completed.
```
select query, segment, assignment, min_starttime, max_endtime, min_duration, avg_duration
from svl_s3partition_summary
where query = pg_last_query_id()
order by query,segment;
```
# SVL\$1S3QUERY
Use the SVL\$1S3QUERY view to get details about Amazon Redshift Spectrum queries at the segment and node slice level.
SVL\$1S3QUERY 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](cm_chap_system-tables.md#c_visibility-of-data).
**Note**
SVL\$1S3QUERY only contains queries run on main provisioned clusters. It doesn't contain queries run on concurrency scaling clusters or on serverless namespaces. To access explain plans for queries run on both main clusters, concurrency scaling clusters, and serverless namespaces, we recommend that you use the SYS monitoring view [SYS\$1EXTERNAL\$1QUERY\$1DETAIL](SYS_EXTERNAL_QUERY_DETAIL.md) . The data in the SYS monitoring view is formatted to be easier to use and understand.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_S3QUERY.html)
## Sample query
The following example gets the scan step details for the last query completed.
```
select query, segment, slice, elapsed, s3_scanned_rows, s3_scanned_bytes, s3query_returned_rows, s3query_returned_bytes, files
from svl_s3query
where query = pg_last_query_id()
order by query,segment,slice;
```
```
query | segment | slice | elapsed | s3_scanned_rows | s3_scanned_bytes | s3query_returned_rows | s3query_returned_bytes | files
------+---------+-------+---------+-----------------+------------------+-----------------------+------------------------+------
4587 | 2 | 0 | 67811 | 0 | 0 | 0 | 0 | 0
4587 | 2 | 1 | 591568 | 172462 | 11260097 | 8513 | 170260 | 1
4587 | 2 | 2 | 216849 | 0 | 0 | 0 | 0 | 0
4587 | 2 | 3 | 216671 | 0 | 0 | 0 | 0 | 0
```
# SVL\$1S3QUERY\$1SUMMARY
Use the SVL\$1S3QUERY\$1SUMMARY view to get a summary of all Amazon Redshift Spectrum queries (S3 queries) that have been run on the system. SVL\$1S3QUERY\$1SUMMARY aggregates detail from SVL\$1S3QUERY at the segment level.
SVL\$1S3QUERY\$1SUMMARY 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1EXTERNAL\$1QUERY\$1DETAIL](SYS_EXTERNAL_QUERY_DETAIL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
For SVCS\$1S3QUERY\$1SUMMARY, see [SVCS\$1S3QUERY\$1SUMMARY](r_SVCS_S3QUERY_SUMMARY.md).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_S3QUERY_SUMMARY.html)
## Sample query
The following example gets the scan step details for the last query completed.
```
select query, segment, elapsed, s3_scanned_rows, s3_scanned_bytes, s3query_returned_rows, s3query_returned_bytes, files
from svl_s3query_summary
where query = pg_last_query_id()
order by query,segment;
```
```
query | segment | elapsed | s3_scanned_rows | s3_scanned_bytes | s3query_returned_rows | s3query_returned_bytes | files
------+---------+---------+-----------------+------------------+-----------------------+------------------------+------
4587 | 2 | 67811 | 0 | 0 | 0 | 0 | 0
4587 | 2 | 591568 | 172462 | 11260097 | 8513 | 170260 | 1
4587 | 2 | 216849 | 0 | 0 | 0 | 0 | 0
4587 | 2 | 216671 | 0 | 0 | 0 | 0 | 0
```
# SVL\$1S3RETRIES
Use the SVL\$1S3RETRIES view to get information about why an Amazon Redshift Spectrum query based on Amazon S3 has failed.
SVL\$1S3RETRIES 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](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_S3RETRIES.html)
## Sample query
The following example retrieves data about failed S3 queries.
```
SELECT svl_s3retries.query, svl_s3retries.segment, svl_s3retries.node, svl_s3retries.slice, svl_s3retries.eventtime, svl_s3retries.retries,
svl_s3retries.successful_fetches, svl_s3retries.file_size, btrim((svl_s3retries."location")::text) AS "location", btrim((svl_s3retries.message)::text)
AS message FROM svl_s3retries;
```
# SVL\$1SPATIAL\$1SIMPLIFY
You can query the system view SVL\$1SPATIAL\$1SIMPLIFY to get information about simplified spatial geometry objects using the COPY command. When you use COPY on a shapefile, you can specify SIMPLIFY `tolerance`, SIMPLIFY AUTO, and SIMPLIFY AUTO `max_tolerance` ingestion options. The result of the simplification is summarized in SVL\$1SPATIAL\$1SIMPLIFY system view.
When SIMPLIFY AUTO `max_tolerance` is set, this view contains a row for each geometry that exceeded the maximum size. When SIMPLIFY `tolerance` is set, then one row for the entire COPY operation is stored. This row references the COPY query ID and the specified simplification tolerance.
SVL\$1SPATIAL\$1SIMPLIFY 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1SPATIAL\$1SIMPLIFY](SYS_SPATIAL_SIMPLIFY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_SPATIAL_SIMPLIFY.html)
## Sample query
The following query returns the list of records that COPY simplified.
```
SELECT * FROM svl_spatial_simplify WHERE query = pg_last_copy_id();
query | line_number | maximum_tolerance | initial_size | simplified | final_size | final_tolerance
-------+-------------+-------------------+--------------+------------+------------+----------------------
20 | 1184704 | -1 | 1513736 | t | 1008808 | 1.276386653895e-05
20 | 1664115 | -1 | 1233456 | t | 1023584 | 6.11707814796635e-06
```
# SVL\$1SPECTRUM\$1SCAN\$1ERROR
You can query the system view SVL\$1SPECTRUM\$1SCAN\$1ERROR to get information about Redshift Spectrum scan errors.
SVL\$1SPECTRUM\$1SCAN\$1ERROR 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1EXTERNAL\$1QUERY\$1ERROR](SYS_EXTERNAL_QUERY_ERROR.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
Displays a sample of logged errors. The default is 10 entries per query.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_SPECTRUM_SCAN_ERROR.html)
## Sample query
The following query returns the list of rows for which data handling operations were performed.
```
SELECT * FROM svl_spectrum_scan_error;
```
The query returns results similar to the following.
```
userid query location rowid colname original_value modified_value trigger action action_valueerror_code
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:0 league_name Barclays Premier League Barclays Premier Lea UNSPECIFIED TRUNCATE 156
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:0 league_nspi 34595 32767 UNSPECIFIED OVERFLOW_VALUE 199
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:1 league_nspi 34151 32767 UNSPECIFIED OVERFLOW_VALUE 199
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:2 league_name Barclays Premier League Barclays Premier Lea UNSPECIFIED TRUNCATE 156
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:2 league_nspi 33223 32767 UNSPECIFIED OVERFLOW_VALUE 199
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:3 league_name Barclays Premier League Barclays Premier Lea UNSPECIFIED TRUNCATE 156
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:3 league_nspi 32808 32767 UNSPECIFIED OVERFLOW_VALUE 199
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:4 league_nspi 32790 32767 UNSPECIFIED OVERFLOW_VALUE 199
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:5 league_name Spanish Primera Division Spanish Primera Divi UNSPECIFIED TRUNCATE 156
100 1574007 s3://spectrum-uddh/league/spi_global_rankings.0:6 league_name Spanish Primera Division Spanish Primera Divi UNSPECIFIED TRUNCATE 156
```
# SVL\$1STATEMENTTEXT
Use the SVL\$1STATEMENTTEXT view to get a complete record of all of the SQL commands that have been run on the system.
The SVL\$1STATEMENTTEXT view contains the union of all of the rows in the [STL\$1DDLTEXT](r_STL_DDLTEXT.md), [STL\$1QUERYTEXT](r_STL_QUERYTEXT.md), and [STL\$1UTILITYTEXT](r_STL_UTILITYTEXT.md) tables. This view also includes a join to the STL\$1QUERY table.
SVL\$1STATEMENTTEXT 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_STATEMENTTEXT.html)
## Sample query
The following query returns DDL statements that were run on June 16th, 2009:
```
select starttime, type, rtrim(text) from svl_statementtext
where starttime like '2009-06-16%' and type='DDL' order by starttime asc;
starttime | type | rtrim
---------------------------|------|--------------------------------
2009-06-16 10:36:50.625097 | DDL | create table ddltest(c1 int);
2009-06-16 15:02:16.006341 | DDL | drop view alltickitjoin;
2009-06-16 15:02:23.65285 | DDL | drop table sales;
2009-06-16 15:02:24.548928 | DDL | drop table listing;
2009-06-16 15:02:25.536655 | DDL | drop table event;
...
```
### Reconstructing stored SQL
To reconstruct the SQL stored in the `text` column of SVL\$1STATEMENTTEXT, run a SELECT statement to create SQL from 1 or more parts in the `text` column. Before running the reconstructed SQL, replace any (`\n`) special characters with a new line. The result of the following SELECT statement is rows of reconstructed SQL in the `query_statement` field.
```
select LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END, '') within group (order by sequence) AS query_statement
from SVL_STATEMENTTEXT where pid=pg_backend_pid();
```
For example, the following query selects 3 columns. The query itself is longer than 200 characters and is stored in parts in SVL\$1STATEMENTTEXT.
```
select
1 AS a0123456789012345678901234567890123456789012345678901234567890,
2 AS b0123456789012345678901234567890123456789012345678901234567890,
3 AS b012345678901234567890123456789012345678901234
FROM stl_querytext;
```
In this example, the query is stored in 2 parts (rows) in the `text` column of SVL\$1STATEMENTTEXT.
```
select sequence, text from SVL_STATEMENTTEXT where pid = pg_backend_pid() order by starttime, sequence;
```
```
sequence | text
----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 | select\n1 AS a0123456789012345678901234567890123456789012345678901234567890,\n2 AS b0123456789012345678901234567890123456789012345678901234567890,\n3 AS b012345678901234567890123456789012345678901234
1 | \nFROM stl_querytext;
```
To reconstruct the SQL stored in STL\$1STATEMENTTEXT, run the following SQL.
```
select LISTAGG(CASE WHEN LEN(RTRIM(text)) = 0 THEN text ELSE RTRIM(text) END, '') within group (order by sequence) AS text
from SVL_STATEMENTTEXT where pid=pg_backend_pid();
```
To use the resulting reconstructed SQL in your client, replace any (`\n`) special characters with a new line.
```
text
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select\n1 AS a0123456789012345678901234567890123456789012345678901234567890,\n2 AS b0123456789012345678901234567890123456789012345678901234567890,\n3 AS b012345678901234567890123456789012345678901234\nFROM stl_querytext;
```
# SVL\$1STORED\$1PROC\$1CALL
You can query the system view SVL\$1STORED\$1PROC\$1CALL to get information about stored procedure calls, including start time, end time, and whether a call is canceled. Each stored procedure call receives a query ID.
SVL\$1STORED\$1PROC\$1CALL 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1PROCEDURE\$1CALL](SYS_PROCEDURE_CALL.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_STORED_PROC_CALL.html)
## Sample query
The following query returns the elapsed time in descending order and the completion status for stored procedure calls in the past day.
```
select query, datediff(seconds, starttime, endtime) as elapsed_time, aborted, trim(querytxt) as call from svl_stored_proc_call where starttime >= getdate() - interval '1 day' order by 2 desc;
query | elapsed_time | aborted | call
--------+--------------+---------+-----------------------------------------------------------------------------------
4166 | 7 | 0 | call search_batch_status(35,'succeeded');
2433 | 3 | 0 | call test_batch (123456)
1810 | 1 | 0 | call prod_benchmark (123456)
1836 | 1 | 0 | call prod_testing (123456)
1808 | 1 | 0 | call prod_portfolio ('N', 123456)
1816 | 1 | 1 | call prod_portfolio ('Y', 123456)
```
# SVL\$1STORED\$1PROC\$1MESSAGES
You can query the system view SVL\$1STORED\$1PROC\$1MESSAGES to get information about stored procedure messages. Raised messages are logged even if the stored procedure call is canceled. Each stored procedure call receives a query ID. For more information about how to set the minimum level for logged messages, see stored\$1proc\$1log\$1min\$1messages.
SVL\$1STORED\$1PROC\$1MESSAGES 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1PROCEDURE\$1MESSAGES](SYS_PROCEDURE_MESSAGES.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_STORED_PROC_MESSAGES.html)
## Sample query
The following SQL statements show how to use SVL\$1STORED\$1PROC\$1MESSAGES to review raised messages.
```
-- Create and run a stored procedure
CREATE OR REPLACE PROCEDURE test_proc1(f1 int) AS
$$
BEGIN
RAISE INFO 'Log Level: Input f1 is %',f1;
RAISE NOTICE 'Notice Level: Input f1 is %',f1;
EXECUTE 'select invalid';
RAISE NOTICE 'Should not print this';
EXCEPTION WHEN OTHERS THEN
raise exception 'EXCEPTION level: Exception Handling';
END;
$$ LANGUAGE plpgsql;
-- Call this stored procedure
CALL test_proc1(2);
-- Show raised messages with level higher than INFO
SELECT query, recordtime, loglevel, loglevel_text, trim(message) as message, aborted FROM svl_stored_proc_messages
WHERE loglevel > 30 AND query = 193 ORDER BY recordtime;
query | recordtime | loglevel | loglevel_text | message | aborted
-------+----------------------------+----------+---------------+-------------------------------------+---------
193 | 2020-03-17 23:57:18.277196 | 40 | NOTICE | Notice Level: Input f1 is 2 | 1
193 | 2020-03-17 23:57:18.277987 | 60 | EXCEPTION | EXCEPTION level: Exception Handling | 1
(2 rows)
-- Show raised messages at EXCEPTION level
SELECT query, recordtime, loglevel, loglevel_text, trim(message) as message, aborted FROM svl_stored_proc_messages
WHERE loglevel_text = 'EXCEPTION' AND query = 193 ORDER BY recordtime;
query | recordtime | loglevel | loglevel_text | message | aborted
-------+----------------------------+----------+---------------+-------------------------------------+---------
193 | 2020-03-17 23:57:18.277987 | 60 | EXCEPTION | EXCEPTION level: Exception Handling | 1
```
The following SQL statements show how to use SVL\$1STORED\$1PROC\$1MESSAGES to review raised messages with the SET option when creating a stored procedure. Because test\$1proc() has a minimum log level of NOTICE, only NOTICE, WARNING, and EXCEPTION level messages are logged in SVL\$1STORED\$1PROC\$1MESSAGES.
```
-- Create a stored procedure with minimum log level of NOTICE
CREATE OR REPLACE PROCEDURE test_proc() AS
$$
BEGIN
RAISE LOG 'Raise LOG messages';
RAISE INFO 'Raise INFO messages';
RAISE NOTICE 'Raise NOTICE messages';
RAISE WARNING 'Raise WARNING messages';
RAISE EXCEPTION 'Raise EXCEPTION messages';
RAISE WARNING 'Raise WARNING messages again'; -- not reachable
END;
$$ LANGUAGE plpgsql SET stored_proc_log_min_messages = NOTICE;
-- Call this stored procedure
CALL test_proc();
-- Show the raised messages
SELECT query, recordtime, loglevel_text, trim(message) as message, aborted FROM svl_stored_proc_messages
WHERE query = 149 ORDER BY recordtime;
query | recordtime | loglevel_text | message | aborted
-------+----------------------------+---------------+--------------------------+---------
149 | 2020-03-16 21:51:54.847627 | NOTICE | Raise NOTICE messages | 1
149 | 2020-03-16 21:51:54.84766 | WARNING | Raise WARNING messages | 1
149 | 2020-03-16 21:51:54.847668 | EXCEPTION | Raise EXCEPTION messages | 1
(3 rows)
```
# SVL\$1TERMINATE
Records the time when a user cancels or terminates a process.
SELECT PG\$1TERMINATE\$1BACKEND(*pid*), SELECT PG\$1CANCEL\$1BACKEND(*pid*), and CANCEL *pid* creates a log entry in SVL\$1TERMINATE.
SVL\$1TERMINATE is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_TERMINATE.html)
The following command shows the latest cancelled query.
```
select * from svl_terminate order by eventtime desc limit 1;
pid | eventtime | userid | type
------+----------------------------+--------+--------
8324 | 2020-03-24 09:42:07.298937 | 1 | CANCEL
(1 row)
```
# SVL\$1UDF\$1LOG
Records system-defined error and warning messages generating during user-defined function (UDF) execution.
SVL\$1UDF\$1LOG 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](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1UDF\$1LOG](SYS_UDF_LOG.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_UDF_LOG.html)
## Sample queries
The following example shows how UDFs handle system-defined errors. The first block shows the definition for a UDF function that returns the inverse of an argument. When you run the function and provide a 0 argument, as the second block shows, the function returns an error. The third statement reads the error message that is logged in SVL\$1UDF\$1LOG
```
-- Create a function to find the inverse of a number
CREATE OR REPLACE FUNCTION f_udf_inv(a int)
RETURNS float IMMUTABLE
AS $$
return 1/a
$$ LANGUAGE plpythonu;
-- Run the function with a 0 argument to create an error
Select f_udf_inv(0) from sales;
-- Query SVL_UDF_LOG to view the message
Select query, created, message::varchar
from svl_udf_log;
query | created | message
-------+----------------------------+---------------------------------------------------------
2211 | 2015-08-22 00:11:12.04819 | ZeroDivisionError: long division or modulo by zero\nNone
```
The following example adds logging and a warning message to the UDF so that a divide by zero operation results in a warning message instead of stopping with an error message.
```
-- Create a function to find the inverse of a number and log a warning
CREATE OR REPLACE FUNCTION f_udf_inv_log(a int)
RETURNS float IMMUTABLE
AS $$
import logging
logger = logging.getLogger() #get root logger
if a==0:
logger.warning('You attempted to divide by zero.\nReturning zero instead of error.\n')
return 0
else:
return 1/a
$$ LANGUAGE plpythonu;
```
The following example runs the function, then queries SVL\$1UDF\$1LOG to view the message.
```
-- Run the function with a 0 argument to trigger the warning
Select f_udf_inv_log(0) from sales;
-- Query SVL_UDF_LOG to view the message
Select query, created, message::varchar
from svl_udf_log;
query | created | message
------+----------------------------+----------------------------------
0 | 2015-08-22 00:11:12.04819 | You attempted to divide by zero.
Returning zero instead of error.
```
# SVL\$1USER\$1INFO
You can retrieve data about Amazon Redshift database users with the SVL\$1USER\$1INFO view.
SVL\$1USER\$1INFO is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_USER_INFO.html)
## Sample queries
The following command retrieves user information from SVL\$1USER\$1INFO.
```
SELECT * FROM SVL_USER_INFO;
```
# SVL\$1VACUUM\$1PERCENTAGE
The SVL\$1VACUUM\$1PERCENTAGE view reports the percentage of data blocks allocated to a table after performing a vacuum. This percentage number shows how much disk space was reclaimed. See the [VACUUM](r_VACUUM_command.md) command for more information about the vacuum utility.
SVL\$1VACUUM\$1PERCENTAGE is visible only to superusers. For more information, see [Visibility of data in system tables and views](cm_chap_system-tables.md#c_visibility-of-data).
Some or all of the data in this table can also be found in the SYS monitoring view [SYS\$1VACUUM\$1HISTORY](SYS_VACUUM_HISTORY.md). The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
## Table columns
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_SVL_VACUUM_PERCENTAGE.html)
## Sample query
The following query displays the percentage for a specific operation on table 100238:
```
select * from svl_vacuum_percentage
where table_id=100238 and xid=2200;
xid | table_id | percentage
-----+----------+------------
1337 | 100238 | 60
(1 row)
```
After this vacuum operation, the table contained 60 percent of the original blocks.