ANALYZE 命令歷史記錄 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

ANALYZE 命令歷史記錄

知道上次對資料表或資料庫執行 ANALYZE 命令的時間很實用。執行 ANALYZE 命令時,Amazon Redshift 會執行看起來類似以下的多個查詢:

padb_fetch_sample: select * from table_name

查詢 STL_ANALYZE 來檢視分析操作的記錄。如果 Amazon Redshift 使用自動分析來分析資料表,則is_background列設置為t(真)。否則會設為 f (false)。下列範例聯結 STV_TBL_PERM,以顯示資料表名稱和執行結果。

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)

或者,您可以執行更複雜的查詢,傳回在包括 ANALYZE 命令的每個完成的交易中執行的所有陳述式:

select xid, to_char(starttime, 'HH24:MM:SS.MS') as starttime, datediff(sec,starttime,endtime ) as secs, substring(text, 1, 40) from svl_statementtext where sequence = 0 and xid in (select xid from svl_statementtext s where s.text like 'padb_fetch_sample%' ) order by xid desc, starttime; xid | starttime | secs | substring -----+--------------+------+------------------------------------------ 1338 | 12:04:28.511 | 4 | Analyze date 1338 | 12:04:28.511 | 1 | padb_fetch_sample: select count(*) from 1338 | 12:04:29.443 | 2 | padb_fetch_sample: select * from date 1338 | 12:04:31.456 | 1 | padb_fetch_sample: select * from date 1337 | 12:04:24.388 | 1 | padb_fetch_sample: select count(*) from 1337 | 12:04:24.388 | 4 | Analyze sales 1337 | 12:04:25.322 | 2 | padb_fetch_sample: select * from sales 1337 | 12:04:27.363 | 1 | padb_fetch_sample: select * from sales ...