Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

How to find concurrent queries thar are listed in EXA_USAGE_HOURLY

SusanneK
SQL-Fighter

Hey everyone,

I have a question about the statistics in EXA_USAGE_HOURLY. We noticed a huge increase of QUERIES_MAX. This is why we tried to find the queries that are causing this increase. We have enabled the SQL Audit, so we tried to search for the concurrent queries there. Unfortunatelly the data that we can reproduce with the help of the table EXA_DBA_AUDIT_SQL does not fit the data we are seeing in EXA_USAGE_HOURLY. Sometime we see more concurrecnt queries and sometimes we see less concurrent queries. Does anyone have an idea what we are missing? (Warning: the following query takes several minutes in our setup)

with concurrent_queries_per_starting_qry AS
(
select s1.START_TIME,
date_trunc('hour', s1.START_TIME) as start_hour,
s1.STMT_ID,
count(1) as cnt_concurrent_queries_at_start_time

from EXA_STATISTICS.EXA_DBA_AUDIT_SQL s1

left join EXA_STATISTICS.EXA_DBA_AUDIT_SQL s2
on s1.START_TIME between s2.START_TIME AND s2.STOP_TIME

where s1.START_TIME like '2022-01-06 14%'
group by 1, 2, 3
)

select
start_hour,
h.QUERIES_MAX as queries_max_from_exa_usage_hourly,
max(c.cnt_concurrent_queries_at_start_time) as queries_max_self_computed

from concurrent_queries_per_starting_qry c

left join EXA_STATISTICS.EXA_USAGE_HOURLY h
on h.INTERVAL_START = c.start_hour
group by 1,2
;

We do not understand what the metric USERS_MAX should tell us. The EXA_USAGE_HOURLY says that we have most of the time 100-200 users, but we have only 60 users registered at all in the whole Exasol DB. What could be counted for the statistics table?

We appreciate any help.

Thanks and kind regards

Susanne

13 REPLIES 13

mwellbro
Xpert

Hi @SusanneK ,

just wanted to check back to see if you do have any news on this topic ?

SusanneK
SQL-Fighter

Hi @mwellbro , we are still looking into this issue. In the mean time I took two weeks off, which made this thing a bit slow. But the Exasol Support is very involved, I hope, we will find the solution soon. I will keep you updated.

Kind regards

Susanne

SusanneK
SQL-Fighter

Hi @mwellbro it turned out that this is a bug in the current Exasol Version. It is included in the following roadmap ticket:

https://www.exasol.com/support/browse/EXASOL-3084

Maybe you would like to vote for it ;).

Kind regards

Susanne

mwellbro
Xpert

Hi @SusanneK ,

thanks for following up on this one 👍
I´ll probably won´t vote for it since as long as exa_dba_audit_sql is correct I´m alright with knowing that the EXA_USAGE_* might be off - not pretty, but not life threating from my perspective 😉
Considering other topics on their road map, I´d think this isn´t among the ones I´d wish to see them take care of with priority
(yes @exa-Matze , you´ll probably know what I´m talking about 😉 ).

Anyways, thanks again for giving this thread some closure !

Cheers,
Malte