doing oracle queries through checkpoint firewall

[ajji]

Hello,

I have to migrate an oracle database to a mysql database the oracle database can be accessed only by vpn so all the querries are sent this way.
The issue is that the querries take more time to be executed when they are asked through vpn, it is not so much visible for one of them, but as there are many of them its end up by being more than 10 times slower!!!
I think this might be each query being sent through the vpn which take times and then the execution of the queries is done as fast as usual on the oracle server. Is there something wrong in the firewall setting or is there a way to keep one connection open so that each request can go faster to the oracle server?

Thank you in advance for your help...

 

[rn4it]

We some systems that access a DB through a FW and VPN with no unknown issues. There are a few things that it could be, you mention that if there's only 1 querry it's not so noticeable,but when there is multiple querries it becomes very noticable. Depending on the app the amount of data will very. How does this app work? Does the querry download all the data of the querry to the PC or Does it download 1 screens worth? If it downloads the entire querry, how much data is that? and what is the connection on the client side to the internet (I'm presuming here since your using VPN). If for example you have a T1 to the internet, and the querries average 10MB of data, you are more likely saturating the line. VPN encryption will add some overhead, but it only becomes apparent when you're on average coming close to maxing out your lines.

 

[ajji]

Thank you for your reply,

yes you are right this going over an intenet connection.
The connection speeed should be a T1 coonection (not dedicated so other apps might use it as well but not too heavily at the time I run my tests).
The queries are not sending back a lots of data, the overall size of the mysql database creted after the migration is 60MB
and each query retrieve only few rows to be be migrated at a time, but their are many queries...

I have done a test with only queries being :
select count(table_id) from very_small_table;
and doing this query 10 000 times in one script makes alreay the script(php) being very slow, doing the same but accessing an Oracle database in the same network (without vpn) was almost immediate...

So I don't think that the data transfer of the result of the query is what take most time (a count on a table represent few bytes only) . this is more the time to send the query information over the oracle connection, and the time for the result even if it is very small to go back.

this is the reason why I think the firewall/vpn might be responsible of the issue. What do you think ?

 

[stooo]

What model of firewall are you using? What type of encryption? It may be the firewall is underspecced, and using a less intensive encryption method will help things.

Stu

 

[ajji]

All I know is that it is goinf through checkpoint VPN, I will try find more information and try to change the encryption or update the software.
I am not responsible for this firewall so I can't do this change on my own...

If any of you have an example of many queries performed through a firewall/VPN to an Oracle database without delay when connecting and sending the query to the Oracle, this would be great!

Thank you.

 

[rn4it]

There's a big difference between a T1 and an ethernet connection. If your HO has a T1 internet connection, what's it's normal utilization? as well what's the branch side's normal utilization and internet connection.

I Agree that there's an issue with latency on the querries via VPN. I'm just trying to rule out everything else. The FW admin could alway setup a bidirectional logfile tracking from a VPN office to the oracle server and monitor what's happening, making sure there's no ports being blocked, but this would usually just stop the app from working.

good luck