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

Question

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

Many thanks in advance,
MZ

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

Answer

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;

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

Relevanter Inhalt