AWS Redshift Serverless - How to flush cache to see cold execution time, please?

0

Hi! In Oracle I could issue ALTER SYSTEM FLUSH SHARED_POOL; command to clear existing data and re-load fresh data. Is there an alternative for AWS Redshift and AWS Redshift Serverless? I searched but perhaps not using the right terminology.

Many thanks in advance, MZ

MZ
asked a year ago1843 views
2 Answers
2
Accepted Answer

Hello,

In Redshift and Serverless, the cache can be cleared at database level using “alter database db_name result cache [on|off];” and the existing results will be flushed and can be verified by system tables:

  1. Redshift Provisioned : select userid, query, elapsed, source_query from svl_qlog where userid > 1 order by query desc; column source_query will have the query id of cached result
  2. Redshift Serverless : select user_id, query_id, elapsed_time,result_cache_hit,query_text from SYS_QUERY_HISTORY where user_id > 1 order by start_time desc; column result_cache_hit will be set to true/false

Once you turned off the cache, it will flush all the cache and it won’t rebuild the cache until you set to on. Please note if you use the alter database command it won’t clear the cache at cluster level for provisioned and instance level for serverless.

You can also enable and disable cache at session level. set enable_result_cache_for_session to [on|off];

Reference: https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_QLOG.html https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_HISTORY.html https://docs.aws.amazon.com/redshift/latest/dg/r_enable_result_cache_for_session.html

Thanks

Bharath
answered a year ago
AWS
EXPERT
reviewed a year ago
-1

Saw this in a team i consulted with, Seems like you are very well to do , not to know the term vaccum ( kidding)

In Amazon Redshift and Amazon Redshift Serverless, you can use the VACUUM command to reclaim space and sort rows in tables and materialized views. This can be useful for removing deleted rows and updating distribution statistics, which can help improve query performance.

You can use the VACUUM command in the following ways:

  • VACUUM DELETE ONLY: This option reclaims space from deleted rows only, without sorting rows or updating distribution statistics.
  • VACUUM SORT ONLY: This option sorts rows only, without reclaiming space or updating distribution statistics.
  • VACUUM FULL: This option reclaims space and sorts rows, and also updates distribution statistics. This is the most resource-intensive option and should be used with caution.
  • VACUUM REINDEX: This option re-creates the sort keys and distribution keys for the table or materialized view, which can help improve query performance. *You can also use the ANALYZE command to update statistics for a table or materialized view, which can help improve query performance.

Sample code VACUUM FULL mytable; ANALYZE mytable;

SeanSi
answered a year ago
  • This is 100% irrelevant answer.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions