Oracle plugin Taking more time to execute the queries

[surendraGaddam99]

I used this oracle plugin to create oracle connector for presto db. But i am facing performance issues. When i try to execute queries using presto cli 0.147 taking 40 to 45 seconds to display results . Any suggestions to decrease execution time ?

prestodb/presto#5790

Thanks
Surendra

[marcelopaesrech]

Hi Surendra,

Give me more info. What is the volume of the oracle DB you are trying to
query (number of rows, columns, etc)?

How big is the cluster you are using?

What is the query you are trying to execute?

Regards.

2016-08-04 3:33 GMT-03:00 surendraGaddam99 [email protected]:

I used this oracle plugin to create oracle connector for presto db. But i
am facing performance issues. When i try to execute queries using presto
cli 0.147 taking 40 to 45 seconds to display results . Any suggestions to
decrease execution time ?

Thanks
Surendra

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#4, or mute the
thread
https://github.com/notifications/unsubscribe-auth/AENmR9dQVSzwJ1mlYDtgWQ2yNb7gB-R-ks5qcYfAgaJpZM4JcYbn
.

Atenciosamente, Marcelo Paes Rech.
E-mail: [email protected]
Blog: http://marcelopaesrech.blogspot.com

[surendraGaddam99]

Hi Marcelo,

Thanks for the reply.

I tried to run a simple sql query which has 5 to 10 rows that took 45 secs to execute on presto server.
Below shown are the list of sql queries executed by me.

I am using below configurations in config file .

Logs from server.log which shows 40 secs time spent in retrieving getColumns method and looped 3 times.

One more issue i am facing is while trying to get columns for a table from presto .
cvl_in_color table has only 3 columns but the query is getting struck without returning anything.
From server logs i can see that oracle plugin is trying to get columns from all the tables available in the schema for eg: 100 tables . So its looping over all the tables for getting columns one by one and got struck.
getColumns() method in oracle plugin is called multiple times when below query is fired.

server.log file

Please let me know if you have any other suggestions to resolve these issues.

Thanks
Surendra

[marcelopaesrech]

Is there any firewall between the servers(Oracle and Presto)?

That is weird, because the plugin just call the Oracle Driver directly. It
make me think it could be something around the connection, oracle
version/JDBC driver version, firewall, or something like that.

I will try to make a test here, but if you get some news about it let me
know.

Regrards.

2016-08-15 4:29 GMT-03:00 surendraGaddam99 [email protected]:

Hi Marcelo,

Thanks for the reply.

I tried to run a simple sql query which has 5 to 10 rows that took 45 secs
to execute on presto server.
Below shown are the list of sql queries executed by me.

[image: image]
https://cloud.githubusercontent.com/assets/19906954/17657468/35557fb0-62e1-11e6-87a1-2935ba2a21fa.png
[image: image]
https://cloud.githubusercontent.com/assets/19906954/17657517/bc041436-62e1-11e6-9bd5-4727203acb47.png

I am using below configurations in config file .
[image: image]
https://cloud.githubusercontent.com/assets/19906954/17657620/840ab548-62e2-11e6-97a7-c4157215ba43.png

Logs from server.log which shows 40 secs time spent in retrieving
getColumns method and looped 3 times.
[image: image]
https://cloud.githubusercontent.com/assets/19906954/17657705/13deeca2-62e3-11e6-8ea0-050ed27d0d3c.png

One more issue i am facing is while trying to get columns for a table from
presto .
cvl_in_color table has only 3 columns but the query is getting struck
without returning anything.
From server logs i can see that oracle plugin is trying to get columns
from all the tables available in the schema for eg: 100 tables . So its
looping over all the tables for getting columns one by one and got struck.
getColumns() method in oracle plugin is called multiple times when below
query is fired.
[image: image]
https://cloud.githubusercontent.com/assets/19906954/17658025/d3dcaf56-62e5-11e6-8fa4-1e806e8fc6d9.png

server.log file
[image: image]
https://cloud.githubusercontent.com/assets/19906954/17658212/b10cb924-62e7-11e6-974a-d89369fea344.png

Please let me know if you have any other suggestions to resolve these
issues.

Thanks
Surendra

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#4 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AENmR3j2N6iaGe64S9d_wCXZljhiVRxsks5qgBU9gaJpZM4JcYbn
.

Atenciosamente, Marcelo Paes Rech.
E-mail: [email protected]
Blog: http://marcelopaesrech.blogspot.com

[surendraGaddam99]

Hi Marcelo,

There is no firewall present between presto and oracle.
There might be some problem with oracle connection code .Need to check if the connections to be declared globally or any static declaration of maps needed.

My presto sql query :
show columns from oracle.af360st1.cvl_in_color.
Any idea why the OracleClient.getColumns() is called multiple times for above query .

For my case i have 100 tables in the list .So above query trying to get columns from all the tables in the schema and getting struck. Actually it should get columns from only cvl_in_color table.
Not sure why its looping over all the tables available in schema af360st1.

Methods are called as shown below.
Logs .
getTableHandle()
getColumns()
getTableHandle()
getColumns()
getTableHandle()
getColumns()

Thanks
Surendra

[silence-liu]

the question solved ?

[surendraGaddam99]

No , issue is not yet resolved .
Any suggestions ?

Thanks
Surendra

[marcelopaesrech]

I will try to solve this till 20th September.

[surendraGaddam99]

Hi Marcelo,

Any updates ?

Thanks
Surendra

[marcelopaesrech]

Not yet. I didn't have enough time debug, next tuesday I think will have
more time for that.

In the older versions of presto it wasn't an issue. Maybe in the new
releases it hás changed the of calling the plugin.

Sorry for time I am taking to see this, last weeks are being very busy.

Regards.

Em 21 de set de 2016 11:49 PM, "surendraGaddam99" [email protected]
escreveu:

Hi Marcelo,

Any updates ?

Thanks
Surendra

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#4 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AENmR9E7pn3pPvNyeYneRSaj3aWgoF7gks5qseypgaJpZM4JcYbn
.

[surendraGaddam99]

I tried to execute queries using Presto 0.147 version and Presto 0.77 version with Oracle plugin.
For both these versions i faced same performance issues. If the issue gets fixed for 0.77 version iam fine.

Thanks
Surendra

[marcelopaesrech]

Hi @surendraGaddam99, I have made debug over Oracle plugin and MySQL Plugin and de behaviour you mentioned about multiple calls to getColumns is the same to both (problably to all JDBC plugins). I think in this case an issue in presto Jdbc Base Plugin is needed.

I also made few tests use an Oracle database here and nothing about 44s to select small tables as you can see (even with a lot getColumns calls it took 744ms):

Could you give me more details about the issue? To me it seems more like environment than the actually plugin issue. If it is the case, maybe I can help too.

Regards.

[surendraGaddam99]

Hi Marcelo,

As mentioned before it is taking more time in getColumns() method as shown below.

So i followed below link to get this resolved.
http://www.theserverside.com/news/1365579/Using-Database-MetaData-methods-appropriately

Avoid using getColumns() to determine characteristics about a table. Instead, use a dummy query with getMetadata().

Case 2: GetMetadata Method

// prepare dummy query
PreparedStatement WSps = WSc.prepareStatement
("SELECT * from UnknownTable WHERE 1 = 0");
// query is never executed on the server - only prepared
ResultSetMetaData WSsmd=WSps.getMetaData();
int numcols = WSrsmd.getColumnCount();
...
int ctype = WSrsmd.getColumnType(n)
...
// result column information has now been obtained

After using this code now query execution taking only 2 to 3 sec .

Thanks
Surendra

[marcelopaesrech]

What version of the Oracle Drivers are you using? And what is your Oracle
DB version?

As I said before I did queries the same way you did, it really call
getColumn many times, but it took only 0.7 seconds.

Regards.

Em 4 de out de 2016 3:00 PM, "surendraGaddam99" [email protected]
escreveu:

Hi Marcelo,

As mentioned before it is taking more time in getColumns() method as shown
below.
[image: image]
https://cloud.githubusercontent.com/assets/19906954/19085837/74a93a42-8a89-11e6-8b89-78d7ff7a2c34.png

So i followed below link to get this resolved.
http://www.theserverside.com/news/1365579/Using-Database-
MetaData-methods-appropriately

Avoid using getColumns() to determine characteristics about a table.
Instead, use a dummy query with getMetadata().

Case 2: GetMetadata Method

// prepare dummy query
PreparedStatement WSps = WSc.prepareStatement
("SELECT * from UnknownTable WHERE 1 = 0");
// query is never executed on the server - only prepared
ResultSetMetaData WSsmd=WSps.getMetaData();
int numcols = WSrsmd.getColumnCount();
...
int ctype = WSrsmd.getColumnType(n)
...
// result column information has now been obtained

After using this code now query execution taking only 2 to 3 sec .

Thanks
Surendra

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#4 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AENmR2mx4Zs_QbwyVBsuxbJeLl-ep104ks5qwpQ5gaJpZM4JcYbn
.

[surendraGaddam99]

Oracle DB Version : Release 12.1.0.1.0
Oracle Driver version : Specification-Version: 4.0 using ojdbc6.jar

One more thing i want to mention here is my oracle db to which i am connected having hundreds of schemas and all of them getting connected once presto server started.

Thanks'
Surendra

[marcelopaesrech]

Hi @surendraGaddam99,

Could you share your oracle-plugin.properties (with the jdbc url, etc)?

Another question is have your Oracle user grants to all schemas or just the one you are trying to access?

I'm trying to understand what is different to help you or fix the plugin.

Regards.

[surendraGaddam99]

Each oracle user has access to one assigned schema no grants to all schemas.

Thanks
Surendra

[insac]

Hi @surendraGaddam99, in your database there are also synonyms to the tables? I've seen mentions of a performance issue on get columns with synonyms (http://stackoverflow.com/a/2068713/5077154).