Redshift でクエリが Scaling Cluster で実行されなかった理由を調べる
- STL_QUERY.concurrency_scaling_status でクエリが Scaling Cluster を利用しなかった理由を確認できる（数値）
- 数値 の意味は SVL_QUERY_CONCURRENCY_SCALING_STATUS の定義の WHEN (sq.concurrency_scaling_status = x ) THEN の後のテキストでわかる
- -
show view svl_query_concurrency_scaling_status; SELECT sq.userid, sq.query, sq.xid, sq.pid, sr.source_query, sq.concurrency_scaling_status, CASE WHEN (sq.userid = 1) THEN 'Concurrency Scaling ineligible query - Bootstrap user query' :: text WHEN (sq.concurrency_scaling_status = 1) TH EN 'Ran on a Concurrency Scaling cluster' :: text WHEN ( (sq.concurrency_scaling_status = 0) AND (sr.source_query IS NOT NULL) ) THEN 'Ran on the main cluster - Cache hit' :: text WHEN ( (sq.concurrency_scaling_status = 0) AND (sw.service_cla ss = 14) ) THEN 'Ran on the main cluster - SQA' :: text WHEN ( (sq.concurrency_scaling_status = 32) AND (sw.service_class = 14) ) THEN 'Ran on the main cluster - Concurrency scaling is not enabled in SQA' :: text WHEN ( ( (sq.concurrency_scalin g_status = 0) AND ( sq.query IN ( SELECT stl_burst_prepare.query FROM stl_burst_prepare ) ) ) AND ( NOT ( sq.query IN ( SELECT stl_burst_prepare.query FROM stl_burst_prepare WHERE (stl_burst_prepare.code = 0) ) ) ) ) THEN 'Concurrency Scaling elig ible query - Failed to prepare cluster ' :: text WHEN ( ( (sq.concurrency_scaling_status = 0) AND ( sq.query IN ( SELECT stl_burst_async_mark.query FROM stl_burst_async_mark WHERE (stl_burst_async_mark.event ~~ '%Mark%' :: text) ) ) ) AND ( sq.que ry IN ( SELECT stl_burst_async_mark.query FROM stl_burst_async_mark WHERE (stl_burst_async_mark.event ~~ '%Unmark%' :: text) ) ) ) THEN 'Concurrency Scaling eligible query - Cluster was not prepared in time ' :: text WHEN (sq.concurrency_scali ng_status = 0) THEN 'Ran on the main cluster' :: text WHEN (sq.concurrency_scaling_status = 2) THEN 'Concurrency Scaling not enabled' :: text WHEN (sq.concurrency_scaling_status = 4) THEN 'Concurrency Scaling ineligible query - System temp orary table accessed' :: text WHEN (sq.concurrency_scaling_status = 5) THEN 'Concurrency Scaling ineligible query - User temporary table accessed' :: text WHEN (sq.concurrency_scaling_status = 6) THEN 'Concurrency Scaling ineligible query - System table accessed' :: text WHEN (sq.concurrency_scaling_status = 3) THEN 'Concurrency Scaling ineligible query - Query is an Unsupported DML' :: text WHEN (sq.concurrency_scaling_status = 7) THEN 'Concurrency Scaling ineligible query - No backup table accessed' :: text WHEN (sq.concurrency_scaling_status = 8) THEN 'Concurrency Scaling ineligible query - Zindex table accessed' :: text WHEN (sq.concurrency_scaling_status = 9) THEN 'Concurrency Scaling ineligible query - Query uses UDF' :: text WHEN (sq.concurrency_scaling_status = 10) THEN 'Concurrency Scaling ineligible query - Catalog tables accessed' :: text WHEN (sq.concurrency_scaling_status = 11) THEN 'Concurrency Scaling ineligible query - Dirty t able accessed' :: text WHEN (sq.concurrency_scaling_status = 12) THEN 'Concurrency Scaling ineligible query - Direct dispatched query' :: text WHEN (sq.concurrency_scaling_status = 16) THEN 'Concurrency Scaling ineligible query - No tables accessed' :: text WHEN (sq.concurrency_scaling_status = 17) THEN 'Concurrency Scaling ineligible query - Spectrum queries are disabled' :: text WHEN (sq.concurrency_scaling_status = 18) THEN 'Concurrency Scaling ineligible query - Functio n not supported ' :: text WHEN (sq.concurrency_scaling_status = 19) THEN 'Concurrency Scaling ineligible query - Instance type not supported ' :: text WHEN (sq.concurrency_scaling_status = 20) THEN 'Concurrency Scaling ineligible query - B urst temporarily disabled ' :: text WHEN (sq.concurrency_scaling_status = 21) THEN 'Concurrency Scaling ineligible query - Unload queries are disabled ' :: text WHEN (sq.concurrency_scaling_status = 22) THEN 'Concurrency Scaling ineligible query - Unsupported unload type ' :: text WHEN (sq.concurrency_scaling_status = 23) THEN 'Concurrency Scaling ineligible query - Non VPC clusters cannot burst ' :: text WHEN (sq.concurrency_scaling_status = 24) THEN 'Concurrency Scaling i neligible query - VPCE not setup ' :: text WHEN (sq.concurrency_scaling_status = 25) THEN 'Concurrency Scaling failed query - Ineligible to rerun on main cluster due to failure handling not enabled' :: text WHEN (sq.concurrency_scaling_sta tus = 26) THEN 'Concurrency Scaling failed query - Ineligible to rerun on main cluster due to concurrency scaling not auto' :: text WHEN (sq.concurrency_scaling_status = 27) THEN 'Concurrency Scaling failed query - Ineligible to rerun on main cluster due to results already returning ' :: text WHEN (sq.concurrency_scaling_status = 28) THEN 'Concurrency Scaling failed query - Ineligible to rerun on main cluster due to non retriable error ' :: text WHEN (sq.concurrency_scali ng_status = 29) THEN 'Concurrency Scaling failed query - eligible to rerun on main cluster ' :: text WHEN (sq.concurrency_scaling_status = 30) THEN 'Concurrency Scaling ineligible query - Cumulative time not met ' :: text WHEN (sq.concurre ncy_scaling_status = 31) THEN 'Concurrency Scaling ineligible query - Paused query ' :: text WHEN (sq.concurrency_scaling_status = 32) THEN 'Query assigned to non Concurrency Scaling queue ' :: text WHEN (sq.concurrency_scaling_status = 33) THEN 'Concurrency Scaling ineligible query - Query has state on Main cluster ' :: text WHEN (sq.concurrency_scaling_status = 34) THEN 'Concurrency Scaling ineligible query - Query is ineligible for bursting Volt CTAS ' :: text WHEN (sq.c oncurrency_scaling_status = 35) THEN 'Concurrency Scaling ineligible query - Resource blacklisted ' :: text WHEN (sq.concurrency_scaling_status = 36) THEN 'Concurrency Scaling ineligible query - Non-retryable VoltTT queries are blacklist ed ' :: text WHEN (sq.concurrency_scaling_status = 37) THEN 'Concurrency Scaling ineligible query - Query is retrying on Main cluster ' :: text WHEN (sq.concurrency_scaling_status = 38) THEN 'Concurrency Scaling ineligible query - Cannot b urst Volt-created CTAS using cursors ' :: text WHEN (sq.concurrency_scaling_status = 39) THEN 'Concurrency Scaling usage limit reached ' :: text WHEN (sq.concurrency_scaling_status = 40) THEN 'Concurrency Scaling ineligible query - Unsuppo rted VoltTT Utility query ' :: text WHEN (sq.concurrency_scaling_status = 41) THEN 'Concurrency Scaling ineligible query - Write query generating Volt TTs ' :: text WHEN (sq.concurrency_scaling_status = 42) THEN 'Concurrency Scaling inelig ible query - VoltTT query with invalid state ' :: text WHEN (sq.concurrency_scaling_status = 43) THEN 'Concurrency Scaling ineligible query - Explain query generating Volt TTs ' :: text WHEN (sq.concurrency_scaling_status = 44) THEN 'Concu rrency Scaling ineligible query - Bursting Volt-generated queries is disabled ' :: text WHEN (sq.concurrency_scaling_status = 45) THEN 'Concurrency Scaling ineligible query - Resource of VoltTT UNLOAD is blacklisted ' :: text WHEN (sq.conc urrency_scaling_status = 46) THEN 'Concurrency Scaling ineligible query - Multiple pre-Volt query trees ' :: text WHEN (sq.concurrency_scaling_status = 48) THEN 'Concurrency Scaling ineligible query - Target table is DistAll/DistAutoAll ' :: text WHEN (sq.concurrency_scaling_status = 49) THEN 'Concurrency Scaling ineligible query - Table that has diststyle changed in current txn accessed ' :: text WHEN (sq.concurrency_scaling_status = 50) THEN 'Concurrency Scaling ineligi ble query - Cannot burst spectrum copy ' :: text WHEN (sq.concurrency_scaling_status = 51) THEN 'Concurrency Scaling ineligible query - Dirty transaction tables accessed ' :: text WHEN (sq.concurrency_scaling_status = 52) THEN 'Concurrenc y Scaling ineligible query - Table that has identity column as a target table ' :: text WHEN (sq.concurrency_scaling_status = 53) THEN 'Concurrency Scaling ineligible query - Datasharing remote tables accessed' :: text WHEN (sq.concurrency _scaling_status = 54) THEN 'Concurrency Scaling ineligible query - Target table with comp update' :: text WHEN (sq.concurrency_scaling_status = 55) THEN 'Concurrency Scaling ineligible query - Nested tables accessed' :: text WHEN (sq.concu rrency_scaling_status = 56) THEN 'Concurrency Scaling ineligible query - Copy from EMR ' :: text WHEN (sq.concurrency_scaling_status = 59) THEN 'Concurrency Scaling ineligible query - Table that has column encode changed in current txn a ccessed ' :: text WHEN (sq.concurrency_scaling_status = 60) THEN 'Concurrency Scaling ineligible query - MV refresh disabled ' :: text WHEN (sq.concurrency_scaling_status = 61) THEN 'Concurrency Scaling ineligible query - Too many concurre nt writes ' :: text WHEN (sq.concurrency_scaling_status = 62) THEN 'Concurrency Scaling ineligible query - Main cluster too big for writes ' :: text WHEN (sq.concurrency_scaling_status = 63) THEN 'Concurrency Scaling ineligible query - Dat asharing VoltTT ' :: text WHEN (sq.concurrency_scaling_status = 64) THEN 'Concurrency Scaling ineligible query - Target table has super/geo column ' :: text WHEN (sq.concurrency_scaling_status = 65) THEN 'Concurrency Scaling rejected query - Ineligible to queue on burst cluster, eligible to rerun on any cluster ' :: text WHEN (sq.concurrency_scaling_status = 66) THEN 'Concurrency Scaling ineligible query - Datasharing with burst-write' :: text WHEN (sq.concurrency_scaling_s tatus = 67) THEN 'Concurrency Scaling ineligible query - CTAS with burst-write' :: text WHEN (sq.concurrency_scaling_status = 68) THEN 'Concurrency Scaling ineligible query - COPY on tables with identity columns' :: text WHEN (sq.concurren cy_scaling_status = 69) THEN 'Concurrency Scaling ineligible query - Spectrum query with EVR enabled' :: text WHEN (sq.concurrency_scaling_status = 70) THEN 'Concurrency Scaling ineligible query - Datasharing query with cursor' :: text WHE N (sq.concurrency_scaling_status = 71) THEN 'Ran on a Multi-AZ secondary cluster' :: text WHEN (sq.concurrency_scaling_status = 72) THEN 'Concurrency Scaling ineligible query - Burst MERGE is disabled' :: text WHEN (sq.concurrency_scaling_ status = 73) THEN 'Concurrency Scaling ineligible query - Redshift Table via Lake Formation accessed' :: text WHEN (sq.concurrency_scaling_status = 74) THEN 'Concurrency Scaling ineligible query - COPY on table with default value column' :: text WHEN (sq.concurrency_scaling_status = 75) THEN 'Concurrency Scaling ineligible query - Write query on faster classic resize cluster' :: text WHEN (sq.concurrency_scaling_status = 76) THEN 'Concurrency Scaling ineligible query - Ic eberg tables accessed' :: text WHEN (sq.concurrency_scaling_status = 77) THEN 'Concurrency Scaling ineligible query - Alter Diststyle Query Disabled' :: text WHEN (sq.concurrency_scaling_status = 78) THEN 'Concurrency Scaling ineligible qu ery - User Temp CTAS with burst-write' :: text WHEN (sq.concurrency_scaling_status = 79) THEN 'Concurrency Scaling ineligible query - CREATE MV AS with burst-write' :: text WHEN (sq.concurrency_scaling_status = 80) THEN 'Concurrency Scalin g ineligible query - Interleaved Sortkey CTAS with burst-write' :: text WHEN (sq.concurrency_scaling_status = 81) THEN 'Concurrency Scaling ineligible query - Large Iceberg Table Accessed' :: text WHEN (sq.concurrency_scaling_status = 82) THEN 'Concurrency Scaling ineligible query - User temp table as burst write target' :: text WHEN (sq.concurrency_scaling_status = 83) THEN 'Concurrency Scaling ineligible query - Unsupported Datasharing write query' :: text WHEN (sq.concur rency_scaling_status = 86) THEN 'Concurrency Scaling ineligible query - Cannot burst Iceberg table in VoltTT queries' :: text WHEN (sq.concurrency_scaling_status = 87) THEN 'Concurrency Scaling failed query - WAM deletion on remote faile d' :: text WHEN (sq.concurrency_scaling_status = 89) THEN 'Concurrency Scaling ineligible query - USEd DB is set and writable transaction context acquired' :: text WHEN (sq.concurrency_scaling_status = 90) THEN 'Concurrency Scaling snapin failed - Query failed snapin validation on main' :: text WHEN (sq.concurrency_scaling_status = 91) THEN 'Concurrency Scaling validation failed - Query failed backend state validation on main' :: text WHEN (sq.concurrency_scaling_status = 9 2) THEN 'Concurrency Scaling validation failed - Query failed table version validation on main' :: text WHEN (sq.concurrency_scaling_status = 93) THEN 'Concurrency Scaling ineligible query - Kinesis MV refresh disabled' :: text WHEN (sq.co ncurrency_scaling_status = 94) THEN 'Concurrency Scaling ineligible query - Kafka MV refresh disabled' :: text WHEN (sq.concurrency_scaling_status = 95) THEN 'Concurrency Scaling ineligible query - Cannot burst mddl conjunct task queries ' :: text WHEN (sq.concurrency_scaling_status = 96) THEN 'Concurrency Scaling in flush and release mode' :: text WHEN (sq.concurrency_scaling_status = 97) THEN 'Concurrency Scaling ineligible query - Cannot burst concurrent writes with rem ote vacuum' :: text WHEN (sq.concurrency_scaling_status = 98) THEN 'Concurrency Scaling ineligible query - Cannot burst query writing to Iceberg table' :: text WHEN (sq.concurrency_scaling_status = 99) THEN 'Concurrency Scaling ineligible query - Query failed xen lock release validation on main' :: text WHEN (sq.concurrency_scaling_status = 100) THEN 'Concurrency Scaling ineligible query - Cross account restored cluster requires atleast one backup' :: text ELSE 'Concurrency Scaling ineligible query - Unknown status' :: text END AS concurrency_scaling_status_txt, sq.starttime, sq.endtime FROM ( ( stl_query sq LEFT JOIN stl_result_cache_history sr ON ((sq.query = sr.cache_hit_query)) ) LEFT JOIN stl_wlm_query s w ON ( ( ( (sq.query = sw.query) AND (sq.userid = sw.userid) ) AND (sq.xid = sw.xid) ) ) );
