|Pivotal Greenplum (GPDB)||4.3.x|
This article helps users to track progress of analyze during gpdbrestore utility.
Long running analyze during gpdbrestore left customer in dilemma as there is no way today to track the progress.
As part of gpdbrestore, system will perform analyze on entire database or list of tables unless --noanalyze option is specified.
For large databases, system will take time to do analyze during gpdbrestore if the tables were never had statistics collected or database is restored to new cluster. To identify progress during analyze step in such scenarios, following steps should to be performed:
a. Connect to Database
b. Enable GUC to get invisible rows
c. Run following SQL
select distinct Table_Analyzed from (select c.nspname ||'.'|| b.relname Table_Analyzed
from pg_stat_last_operation a, pg_class b ,pg_namespace c
where a.objid=b.oid and b.relnamespace = c.oid
and a.statime >= (select max(query_start) from pg_stat_activity where current_query='analyze;') and a.staactionname='ANALYZE'
and nspname not in ('gp_toolkit','pg_toast','pg_bitmapindex','pg_catalog','information_schema','pg_aoseg') order by a.statime desc) result;
- This sql will work only when analyze process is still in progress during gpdbrestore
- Data will be visible only when GUC gp_select_invisible is on. Please enable this GUC at session level and for this SQL only.
SQL can be modified according to user requirement for additional attribute visibility.