How to extract "SQL Query" that is internally run by a dataflow in OAC-Autonomous

Chethana Kumari-Oracle

Summary

I want to extract "SQL Query" that is internally run by a dataflow

Content

Hello,

I am using OAC-Autonomous and am running dataflows to create my visualizations. One of my data flows takes a long time to run (around 10min) and all I am doing in this dataflow is a join between two tables. Ideally the dataflow should not take less than a minute to complete. I want to extract the "SQL Query" that is internally run by this dataflow, in order to root cause, this delay in its execution.

In order to extract the "SQL Query", I enabled Session Cache in OAC and checked the logs for this dataflow run. It does'nt give the query details (though it lists the steps used in dataflow, datasets (tables) used here and also the column names in the datasets). Please let me know, if anyone knows, "how to fetch the SQL query for this dataflow?".

Note that, I am running this dataflow on a 36Core OAC instance. Number of rows returned by my dataflow join is around 3.9 lakhs. 

Thanks in advance,

Chethana

FPonte

Hi Chethana.

Questions:

Assuming that you want to see the query the data flow is running, I imagine that you want to verify the performance against the database (Autonomou DW) right?

Are the tables in the same database?

Is the join between columns with the same data type?

Are the columns indexed?

Please, give us a little more detail about the data flow.

Cheers

Fernando

 

 

Chethana Kumari-Oracle

Hello Fernando,

Please see my answers below

1)Assuming that you want to see the query the data flow is running, I imagine that you want to verify the performance against the database (Autonomou DW) right?

[chethana]: This is correct

2)Are the tables in the same database?

[chethana]: Yes, they are in the same database (ADW)

3)Is the join between columns with the same data type?

Yes, the join between columns is with the same data type.

4)Are the columns indexed?

The columns are not indexed as they are external tables

 

Thanks,

Chethana

FPonte

Hi Chethana.

Thanks for your reply.

Again, assuming that you are not doing any complex transformation in the Data Flow. What happens if you run a query in the database with the same join in the data flow? Are you able to run that and see the time it takes to return all rows?

My suggestion is to instead of losing more time trying to get the Query generated by the data flow is to try (not sure if possible) to mimic what the data flow is doing.

On another note. If you have access to the database console you can find the SQL running against the database while the data flow runs.

Example attached.

Cheers

Fernando

 

Chethana Kumari-Oracle

Hi Fernando,

Thanks for sharing this information. Will check this out and let you know.

Regards,

Chethana