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/).
# 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.