Statistics on how many times Schema tables were queried and when was last query date

munnelor
Padawan

Hi, 

To evaluate which tables can be decommissioned from our Schema, we'd like to identify how many times the tables were queried and when was the last query date. 

I have looked at Satistical System Tables but was unable to find which command to generate below output. 

TABLE NAME   QUERY_COUNTLAST_QUERY_DATE
CUSTOMER_PROFILES 30011-08-2021
POC_PROFILES214-03-2019 
EXCHANGE_RATES_DECOM1001-01-2018 

 

Is it possible to get such output?  

Thanks in advance for your support it is very much appreciated. 

Br, Lorenna 

1 ACCEPTED SOLUTION

mwellbro
Xpert

update: added code snipped for clarity
Hi @munnelor ,

I´d go for the exa_dba_audit_sql in order to get the output table you provided - I´m not aware of any provided EXA_STATISTICS-table that would cater to this level of granularity.

Only "problem" with the exa_dba_audit_sql is a) you´d have to have it activated ( which is a good thing to have in any case ) and you´d have to parse the SQL_TEXT column for your TABLE_NAMEs - other than that it would be count(*) for the QUERY_COUNT ( executions , not unique queries ) and max(START_TIME) for the LAST_QUERY_DATE.

select 
table_name as TABLE_NAME,count(*) as QUERY_COUNT,CAST(max(start_time) as DATE) as LAST_QUERY_DATE
 from exa_dba_audit_sql a inner join exa_dba_tables b on INSTR(upper(SQL_TEXT),b.table_name)>0
 where start_time>=CURRENT_DATE - 365
group by table_name
;

Hope this helps for your case.

Cheers,
Malte

View solution in original post

2 REPLIES 2

mwellbro
Xpert

update: added code snipped for clarity
Hi @munnelor ,

I´d go for the exa_dba_audit_sql in order to get the output table you provided - I´m not aware of any provided EXA_STATISTICS-table that would cater to this level of granularity.

Only "problem" with the exa_dba_audit_sql is a) you´d have to have it activated ( which is a good thing to have in any case ) and you´d have to parse the SQL_TEXT column for your TABLE_NAMEs - other than that it would be count(*) for the QUERY_COUNT ( executions , not unique queries ) and max(START_TIME) for the LAST_QUERY_DATE.

select 
table_name as TABLE_NAME,count(*) as QUERY_COUNT,CAST(max(start_time) as DATE) as LAST_QUERY_DATE
 from exa_dba_audit_sql a inner join exa_dba_tables b on INSTR(upper(SQL_TEXT),b.table_name)>0
 where start_time>=CURRENT_DATE - 365
group by table_name
;

Hope this helps for your case.

Cheers,
Malte

View solution in original post

exa-Gabi
Team Exasol
Team Exasol

Just to confirm: indeed, there is no system table (EXA_STATISTICS or SYS) to provide a list of the queried objects. At the moment the only solution is the one provided by Malte.

 

Regards,

Gabi

EXA-Dude, database developer, history buff. Member of Team Exasol.