Skip to main content
Question

Top 10 Re-fetches in Depot

  • November 24, 2021
  • 2 replies
  • 6 views

atomix
Forum|alt.badge.img+1
  • Participating Frequently

Hi,

The MC has a great utility for determine that top 10 tables with the most refetches in a given period:
Top 10 Re-fetches in Depot

How can I get the same list with a query?
Thanks for any pointers!

2 replies

Jim_Knicely
Forum|alt.badge.img+2
  • Participating Frequently
  • November 24, 2021

Should be this one:

SELECT COUNT(*) AS refetches,
       pr.projection_schema AS table_schema,
       pr.anchor_table_name AS table_name
  FROM v_internal.dc_depot_fetches f
  JOIN storage_containers s 
    ON s.sal_storage_id = f.storageid
  JOIN projections pr
    ON pr.projection_id = s.projection_id
 WHERE time > to_timestamp_tz({from_time}) AND time <= to_timestamp_tz({to_time})
 GROUP BY 2,3
HAVING COUNT(*) > 1
 ORDER BY refetches, pr.projection_schema, pr.anchor_table_name DESC
 LIMIT 10;

atomix
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • November 30, 2021

This is great, thank you Jim!