Migrating to SYS monitoring views
When you migrate your Amazon Redshift provisioned cluster to Amazon Redshift Serverless, your monitoring or diagnostic queries might reference system views that are only available on provisioned clusters. You can update your queries to use the SYS monitoring views. This page provides provisioned to SYS view mappings for you to reference when updating your queries.
Topics
- Use cases for migrating to SYS monitoring views
- Improving query identifier tracking using the SYS monitoring views
- SYS_QUERY_HISTORY
- SYS_QUERY_DETAIL
- SYS_RESTORE_LOG
- SYS_RESTORE_STATE
- SYS_TRANSACTION_HISTORY
- SYS_QUERY_TEXT
- SYS_CONNECTION_LOG
- SYS_SESSION_HISTORY
- SYS_LOAD_DETAIL
- SYS_LOAD_HISTORY
- SYS_LOAD_ERROR_DETAIL
- SYS_UNLOAD_HISTORY
- SYS_UNLOAD_DETAIL
- SYS_COPY_REPLACEMENTS
- SYS_DATASHARE_USAGE_CONSUMER
- SYS_DATASHARE_USAGE_PRODUCER
- SYS_DATASHARE_CROSS_REGION_USAGE
- SYS_DATASHARE_CHANGE_LOG
- SYS_EXTERNAL_QUERY_DETAIL
- SYS_EXTERNAL_QUERY_ERROR
- SYS_VACUUM_HISTORY
- SYS_ANALYZE_HISTORY
- SYS_ANALYZE_COMPRESSION_HISTORY
- SYS_MV_REFRESH_HISTORY
- SYS_MV_STATE
- SYS_PROCEDURE_CALL
- SYS_PROCEDURE_MESSAGES
- SYS_UDF_LOG
- SYS_USERLOG
- SYS_SCHEMA_QUOTA_VIOLATIONS
- SYS_SPATIAL_SIMPLIFY
- Example
Use cases for migrating to SYS monitoring views
Migrating from provisioned clusters to Amazon Redshift Serverless
If you're migrating a provisioned cluster to Amazon Redshift Serverless, you may have queries using the following system views, which are only available on provisioned clusters.
All STL views
All STV views
All SVCS views
All SVL views
Some SVV views
For a list of SVV views unsupported in Amazon Redshift Serverless, see the list at the bottom of Monitoring queries and workloads with Amazon Redshift Serverless in the Amazon Redshift Management Guide.
To keep using your queries, refit them to use columns defined in the SYS monitoring views that correspond to the columns in your provisioned-only views.
Updating queries while staying on a provisioned cluster
If you're not migrating to Amazon Redshift Serverless, you might still want to update your existing queries. The SYS monitoring views are designed for ease of use and reduced complexity, providing a complete array of metrics for effective monitoring and troubleshooting. Using SYS views such as SYS_QUERY_HISTORY and SYS_QUERY_DETAIL that consolidate the information of multiple provisioned-only views, you can streamline your queries.
Improving query identifier tracking using the SYS monitoring views
SYS monitoring views such as such as SYS_QUERY_HISTORY and SYS_QUERY_DETAIL contain the query_id column, which holds the identifier for users’ queries. Similarly, provisioned-only views such as STL_QUERY and SVL_QLOG contain the query column, which also holds the query identifiers. However, the query identifiers recorded in the SYS system views are different from those recorded in the provisioned-only views.
The difference between the SYS views’ query_id column values and the provisioned-only views’ query column values is as follows:
In SYS views, the query_id column records user-submitted queries in their original form. The Amazon Redshift optimizer might break them down into child queries for improved performance, but a single query you run will still only have a single row in SYS_QUERY_HISTORY. If you want to see the individual child queries, you can find them in SYS_QUERY_DETAIL.
In provisioned-only views, the query column records queries at the child query level. If the Amazon Redshift optimizer rewrites your original query into multiple child queries, there will be multiple rows in STL_QUERY with differing query identifier values for a single query you run.
When you migrate your monitoring and diagnostic queries from provisioned-only views to SYS views, consider this difference and edit your queries accordingly. For more information on how Amazon Redshift processes queries, see Query planning and execution workflow.
SYS_QUERY_HISTORY
Some or all of the columns in the following tables are also defined in SYS_QUERY_HISTORY.
SYS_QUERY_DETAIL
Some or all of the columns in the following tables are also defined in SYS_QUERY_DETAIL.
SYS_RESTORE_LOG
Some or all of the columns in the following table are also defined in SYS_RESTORE_LOG.
SYS_RESTORE_STATE
Some or all of the columns in the following table are also defined in SYS_RESTORE_STATE.
SYS_TRANSACTION_HISTORY
Some or all of the columns in the following tables are also defined in SYS_TRANSACTION_HISTORY.
SYS_QUERY_TEXT
Some or all of the columns in the following table are also defined in SYS_QUERY_TEXT.
SYS_CONNECTION_LOG
Some or all of the columns in the following table are also defined in SYS_CONNECTION_LOG.
SYS_SESSION_HISTORY
Some or all of the columns in the following tables are also defined in SYS_SESSION_HISTORY.
SYS_LOAD_DETAIL
Some or all of the columns in the following table are also defined in SYS_LOAD_DETAIL.
SYS_LOAD_HISTORY
Some or all of the columns in the following table are also defined in SYS_LOAD_HISTORY.
SYS_LOAD_ERROR_DETAIL
Some or all of the columns in the following tables are also defined in SYS_LOAD_ERROR_DETAIL.
SYS_UNLOAD_HISTORY
Some or all of the columns in the following table are also defined in SYS_UNLOAD_HISTORY.
SYS_UNLOAD_DETAIL
Some or all of the columns in the following table are also defined in SYS_UNLOAD_DETAIL.
SYS_COPY_REPLACEMENTS
Some or all of the columns in the following table are also defined in SYS_COPY_REPLACEMENTS.
SYS_DATASHARE_USAGE_CONSUMER
Some or all of the columns in the following table are also defined in SYS_DATASHARE_USAGE_CONSUMER.
SYS_DATASHARE_USAGE_PRODUCER
Some or all of the columns in the following table are also defined in SYS_DATASHARE_USAGE_PRODUCER.
SYS_DATASHARE_CROSS_REGION_USAGE
Some or all of the columns in the following table are also defined in SYS_DATASHARE_CROSS_REGION_USAGE.
SYS_DATASHARE_CHANGE_LOG
Some or all of the columns in the following table are also defined in SYS_DATASHARE_CHANGE_LOG.
SYS_EXTERNAL_QUERY_DETAIL
Some or all of the columns in the following tables are also defined in SYS_EXTERNAL_QUERY_DETAIL.
SYS_EXTERNAL_QUERY_ERROR
Some or all of the columns in the following tables are also defined in SYS_EXTERNAL_QUERY_ERROR.
SYS_VACUUM_HISTORY
Some or all of the columns in the following tables are also defined in SYS_VACUUM_HISTORY.
SYS_ANALYZE_HISTORY
Some or all of the columns in the following tables are also defined in SYS_ANALYZE_HISTORY.
SYS_ANALYZE_COMPRESSION_HISTORY
Some or all of the columns in the following tables are also defined in SYS_ANALYZE_COMPRESSION_HISTORY.
SYS_MV_REFRESH_HISTORY
Some or all of the columns in the following tables are also defined in SYS_MV_REFRESH_HISTORY.
SYS_MV_STATE
Some or all of the columns in the following tables are also defined in SYS_MV_STATE.
SYS_PROCEDURE_CALL
Some or all of the columns in the following tables are also defined in SYS_PROCEDURE_CALL.
SYS_PROCEDURE_MESSAGES
Some or all of the columns in the following tables are also defined in SYS_PROCEDURE_MESSAGES.
SYS_UDF_LOG
Some or all of the columns in the following tables are also defined in SYS_UDF_LOG.
SYS_USERLOG
Some or all of the columns in the following tables are also defined in SYS_USERLOG.
SYS_SCHEMA_QUOTA_VIOLATIONS
Some or all of the columns in the following tables are also defined in SYS_SCHEMA_QUOTA_VIOLATIONS.
SYS_SPATIAL_SIMPLIFY
Some or all of the columns in the following tables are also defined in SYS_SPATIAL_SIMPLIFY.
Example
Showing how Amazon Redshift records queries differently in provisioned-only and SYS monitoring views
See the following sample query. This is the query written as you would run it in Amazon Redshift.
SELECT s_name , COUNT(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.l_suppkey AND o_orderkey = l1.l_orderkey AND o_orderstatus = 'F' AND l1.l_receiptdate > l1.l_commitdate AND EXISTS (SELECT * FROM lineitem l2 WHERE l2.l_orderkey = l1.l_orderkey AND l2.l_suppkey <> l1.l_suppkey ) AND NOT EXISTS (SELECT * FROM lineitem l3 WHERE l3.l_orderkey = l1.l_orderkey AND l3.l_suppkey <> l1.l_suppkey AND l3.l_receiptdate > l3.l_commitdate ) AND s_nationkey = n_nationkey AND n_name = 'UNITED STATES' GROUP BY s_name ORDER BY numwait DESC , s_name LIMIT 100;
Under the hood the Amazon Redshift query optimizer rewrites the above user-submitted query into 5 child queries.
The first child query creates a temporary table to materialize a subquery.
CREATE TEMP TABLE volt_tt_606590308b512(l_orderkey , l_suppkey , s_name ) AS SELECT l1.l_orderkey , l1.l_suppkey , public.supplier.s_name FROM public.lineitem AS l1, public.nation, public.orders, public.supplier WHERE l1.l_commitdate < l1.l_receiptdate AND l1.l_orderkey = public.orders.o_orderkey AND l1.l_suppkey = public.supplier.s_suppkey AND public.nation.n_name = 'UNITED STATES'::CHAR(8) AND public.nation.n_nationkey = public.supplier.s_nationkey AND public.orders.o_orderstatus = 'F'::CHAR(1);
The second child query collects statistics from the temporary table.
padb_fetch_sample: select count(*) from volt_tt_606590308b512;
The third child query creates another temporary table to materialize another subquery, referencing the temporary table created above.
CREATE TEMP TABLE volt_tt_606590308c2ef(l_orderkey , l_suppkey) AS (SELECT volt_tt_606590308b512.l_orderkey , volt_tt_606590308b512.l_suppkey FROM public.lineitem AS l2, volt_tt_606590308b512 WHERE l2.l_suppkey <> volt_tt_606590308b512.l_suppkey AND l2.l_orderkey = volt_tt_606590308b512.l_orderkey) EXCEPT distinct (SELECT volt_tt_606590308b512.l_orderkey, volt_tt_606590308b512.l_suppkey FROM public.lineitem AS l3, volt_tt_606590308b512 WHERE l3.l_commitdate < l3.l_receiptdate AND l3.l_suppkey <> volt_tt_606590308b512.l_suppkey AND l3.l_orderkey = volt_tt_606590308b512.l_orderkey);
The fourth child query again collects the temporary table’s statistics.
padb_fetch_sample: select count(*) from volt_tt_606590308c2ef
The last child query uses the temporary tables created above to generate the output.
SELECT volt_tt_606590308b512.s_name AS s_name , COUNT(*) AS numwait FROM volt_tt_606590308b512, volt_tt_606590308c2ef WHERE volt_tt_606590308b512.l_orderkey = volt_tt_606590308c2ef.l_orderkey AND volt_tt_606590308b512.l_suppkey = volt_tt_606590308c2ef.l_suppkey GROUP BY 1 ORDER BY 2 DESC , 1 ASC LIMIT 100;
In the provisioned-only system view STL_QUERY, Amazon Redshift records five rows at the child query level, as follows:
SELECT userid, xid, pid, query, querytxt::varchar(100); FROM stl_query WHERE xid = 48237350 ORDER BY xid, starttime;userid | xid | pid | query | querytxt --------+----------+------------+----------+------------------------------------------------------------------------------------------------------ 101 | 48237350 | 1073840810 | 12058151 | CREATE TEMP TABLE volt_tt_606590308b512(l_orderkey, l_suppkey, s_name) AS SELECT l1.l_orderkey, l1.l 101 | 48237350 | 1073840810 | 12058152 | padb_fetch_sample: select count(*) from volt_tt_606590308b512 101 | 48237350 | 1073840810 | 12058156 | CREATE TEMP TABLE volt_tt_606590308c2ef(l_orderkey, l_suppkey) AS (SELECT volt_tt_606590308b512.l_or 101 | 48237350 | 1073840810 | 12058168 | padb_fetch_sample: select count(*) from volt_tt_606590308c2ef 101 | 48237350 | 1073840810 | 12058170 | SELECT s_name , COUNT(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1. (5 rows)
In the SYS monitoring view SYS_QUERY_HISTORY, Amazon Redshift records the query as follows:
SELECT user_id, transaction_id, session_id, query_id, query_text::varchar(100) FROM sys_query_history WHERE transaction_id = 48237350 ORDER BY start_time;user_id | transaction_id | session_id | query_id | query_text ---------+----------------+------------+----------+------------------------------------------------------------------------------------------------------ 101 | 48237350 | 1073840810 | 12058149 | SELECT s_name , COUNT(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.
In SYS_QUERY_DETAIL, you can find child query-level details using the query_id value from SYS_QUERY_HISTORY. The child_query_sequence column shows the order the child queries are executed in. For more information on the columns in SYS_QUERY_DETAIL, see SYS_QUERY_DETAIL.
select user_id, query_id, child_query_sequence, stream_id, segment_id, step_id, start_time, end_time, duration, blocks_read, blocks_write, local_read_io, remote_read_io, data_skewness, time_skewness, is_active, spilled_block_local_disk, spilled_block_remote_disk from sys_query_detail where query_id = 12058149 and step_id = -1 order by query_id, child_query_sequence, stream_id, segment_id, step_id;user_id | query_id | child_query_sequence | stream_id | segment_id | step_id | start_time | end_time | duration | blocks_read | blocks_write | local_read_io | remote_read_io | data_skewness | time_skewness | is_active | spilled_block_local_disk | spilled_block_remote_disk ---------+----------+----------------------+-----------+------------+---------+----------------------------+----------------------------+----------+-------------+--------------+---------------+----------------+---------------+---------------+-----------+--------------------------+--------------------------- 101 | 12058149 | 1 | 0 | 0 | -1 | 2023-09-27 15:40:38.512415 | 2023-09-27 15:40:38.533333 | 20918 | 0 | 0 | 0 | 0 | 0 | 44 | f | 0 | 0 101 | 12058149 | 1 | 1 | 1 | -1 | 2023-09-27 15:40:39.931437 | 2023-09-27 15:40:39.972826 | 41389 | 12 | 0 | 12 | 0 | 0 | 77 | f | 0 | 0 101 | 12058149 | 1 | 2 | 2 | -1 | 2023-09-27 15:40:40.584412 | 2023-09-27 15:40:40.613982 | 29570 | 32 | 0 | 32 | 0 | 0 | 25 | f | 0 | 0 101 | 12058149 | 1 | 2 | 3 | -1 | 2023-09-27 15:40:40.582038 | 2023-09-27 15:40:40.615758 | 33720 | 0 | 0 | 0 | 0 | 0 | 1 | f | 0 | 0 101 | 12058149 | 1 | 3 | 4 | -1 | 2023-09-27 15:40:46.668766 | 2023-09-27 15:40:46.705456 | 36690 | 24 | 0 | 15 | 0 | 0 | 17 | f | 0 | 0 101 | 12058149 | 1 | 4 | 5 | -1 | 2023-09-27 15:40:46.707209 | 2023-09-27 15:40:46.709176 | 1967 | 0 | 0 | 0 | 0 | 0 | 18 | f | 0 | 0 101 | 12058149 | 1 | 4 | 6 | -1 | 2023-09-27 15:40:46.70656 | 2023-09-27 15:40:46.71289 | 6330 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0 101 | 12058149 | 1 | 5 | 7 | -1 | 2023-09-27 15:40:46.71405 | 2023-09-27 15:40:46.714343 | 293 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0 101 | 12058149 | 2 | 0 | 0 | -1 | 2023-09-27 15:40:52.083907 | 2023-09-27 15:40:52.087854 | 3947 | 0 | 0 | 0 | 0 | 0 | 35 | f | 0 | 0 101 | 12058149 | 2 | 1 | 1 | -1 | 2023-09-27 15:40:52.089632 | 2023-09-27 15:40:52.091129 | 1497 | 0 | 0 | 0 | 0 | 0 | 11 | f | 0 | 0 101 | 12058149 | 2 | 1 | 2 | -1 | 2023-09-27 15:40:52.089008 | 2023-09-27 15:40:52.091306 | 2298 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0 101 | 12058149 | 3 | 0 | 0 | -1 | 2023-09-27 15:40:56.882013 | 2023-09-27 15:40:56.897282 | 15269 | 0 | 0 | 0 | 0 | 0 | 29 | f | 0 | 0 101 | 12058149 | 3 | 1 | 1 | -1 | 2023-09-27 15:40:59.718554 | 2023-09-27 15:40:59.722789 | 4235 | 0 | 0 | 0 | 0 | 0 | 13 | f | 0 | 0 101 | 12058149 | 3 | 2 | 2 | -1 | 2023-09-27 15:40:59.800382 | 2023-09-27 15:40:59.807388 | 7006 | 0 | 0 | 0 | 0 | 0 | 58 | f | 0 | 0 101 | 12058149 | 3 | 3 | 3 | -1 | 2023-09-27 15:41:06.488685 | 2023-09-27 15:41:06.493825 | 5140 | 0 | 0 | 0 | 0 | 0 | 56 | f | 0 | 0 101 | 12058149 | 3 | 3 | 4 | -1 | 2023-09-27 15:41:06.486206 | 2023-09-27 15:41:06.497756 | 11550 | 0 | 0 | 0 | 0 | 0 | 2 | f | 0 | 0 101 | 12058149 | 3 | 4 | 5 | -1 | 2023-09-27 15:41:06.499201 | 2023-09-27 15:41:06.500851 | 1650 | 0 | 0 | 0 | 0 | 0 | 15 | f | 0 | 0 101 | 12058149 | 3 | 4 | 6 | -1 | 2023-09-27 15:41:06.498609 | 2023-09-27 15:41:06.500949 | 2340 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0 101 | 12058149 | 3 | 5 | 7 | -1 | 2023-09-27 15:41:06.502945 | 2023-09-27 15:41:06.503282 | 337 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0 101 | 12058149 | 4 | 0 | 0 | -1 | 2023-09-27 15:41:06.62899 | 2023-09-27 15:41:06.631452 | 2462 | 0 | 0 | 0 | 0 | 0 | 22 | f | 0 | 0 101 | 12058149 | 4 | 1 | 1 | -1 | 2023-09-27 15:41:06.632313 | 2023-09-27 15:41:06.63391 | 1597 | 0 | 0 | 0 | 0 | 0 | 20 | f | 0 | 0 101 | 12058149 | 4 | 1 | 2 | -1 | 2023-09-27 15:41:06.631726 | 2023-09-27 15:41:06.633813 | 2087 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0 101 | 12058149 | 5 | 0 | 0 | -1 | 2023-09-27 15:41:12.571974 | 2023-09-27 15:41:12.584234 | 12260 | 0 | 0 | 0 | 0 | 0 | 39 | f | 0 | 0 101 | 12058149 | 5 | 0 | 1 | -1 | 2023-09-27 15:41:12.569815 | 2023-09-27 15:41:12.585391 | 15576 | 0 | 0 | 0 | 0 | 0 | 4 | f | 0 | 0 101 | 12058149 | 5 | 1 | 2 | -1 | 2023-09-27 15:41:13.758513 | 2023-09-27 15:41:13.76401 | 5497 | 0 | 0 | 0 | 0 | 0 | 39 | f | 0 | 0 101 | 12058149 | 5 | 1 | 3 | -1 | 2023-09-27 15:41:13.749 | 2023-09-27 15:41:13.772987 | 23987 | 0 | 0 | 0 | 0 | 0 | 32 | f | 0 | 0 101 | 12058149 | 5 | 2 | 4 | -1 | 2023-09-27 15:41:13.799526 | 2023-09-27 15:41:13.813506 | 13980 | 0 | 0 | 0 | 0 | 0 | 62 | f | 0 | 0 101 | 12058149 | 5 | 2 | 5 | -1 | 2023-09-27 15:41:13.798823 | 2023-09-27 15:41:13.813651 | 14828 | 0 | 0 | 0 | 0 | 0 | 0 | f | 0 | 0 (28 rows)