Speed of execution of a query for oracle dataset

Hello everyone, locally on my computer i have dremio installed and i’m connecting to an elasticsearch cluster and an oracle database to be able to make queries about both data sets. When i make a simple query (just retrieve some columns) in Dremio to the elasticsearch cluster, for example an index that has 20 million records, the query takes approximately 4 minutes to complete. On the other hand, when I make a very simple query in Dremio (very similar to the previous one) in my oracle table which has only two million records the query took about an hour and a half to return the results.

What could be the reason that for a set of data it takes so little time but for the other it takes so much time?

What can I do to improve the response time when consulting my Oracle data set? (apart from the reflections)

I appreciate any help you can give me,
Thanks in advance!

@gaston_guerra

Can you attach the profiles for the slow runs ?

@Venugopal_Menda

Hi Venugopal_Menda, this is the profile from de slow query:

36e45253-2225-49b0-986e-696a45f10f95.zip (7,8 KB)

Why will it run so slowly?

Thanks for any help!

@gaston_guerra

From the profile, I see the time spent in process time, also the query was pushed down on to the source to execute, meaning the oracle execution is taking time.

Can you do one check run the same query again from Dremio and observer the time and immediately run the same query in Oracle directly and see the time difference.
Here is the query:
{code}
Jdbc(sql=[SELECT “id_usuario”
FROM “PTDI_SSO”.“sso_logs”])
{code}

Thanks
@Venugopal_Menda

Thanks for your response!

I use dbeaver to work with oracle, and that query took 2 seconds to complete.

Is dbeaver fetching all rows or only a subset? I believe by default it fetches a small amount.