SQL Developer vs TOAD - query performance question
Somebody made me notice same queries are executing slower in SQL Developer than in TOAD. I'm rather curious about this issue, since I understand Java is "slow" but I can't find any other thread about this point. I don't use TOAD, so I can't compare... Can this be related to the amount of data being returned by the query ? What could be the other reasons of SQL Dev running slower with an identical query ? Thanks, Attila
Attila_K wrote: Somebody made me notice same queries are executing slower in SQL Developer than in TOAD. I'm rather curious about this issue, since I understand Java is "slow" but I can't find any other thread about this point. I don't use TOAD, so I can't compare... Can this be related to the amount of data being returned by the query ? What could be the other reasons of SQL Dev running slower with an identical query ? Thanks, AttilaThe implementation (Java) could play a role. More likely is the amount of system resources the tool uses - you can check these with Windows task manager.
It also occurs to me that TOAD always uses the equivalent of the JDBC "thick" driver. SQL Developer can use either the "thin" driver or the "thick" driver, but connections are usually configured with the "thin" driver, since you need an Oracle client to use the "thick" driver. The difference is that "thin" drivers are written entirely in Java, but "thick" drivers are written with only a little Java that calls the native executable (hence you need an Oracle client) to do most of the work. Theoretically, a thick driver is faster because the object code doesn't need to be interpreted by the JVM. However, I've heard that the difference in performance is not that large. The only way to know for sure is to configure a connection in SQL Developer to use the thick driver, and see if it is faster (I'd use a stop-watch). Someone correct me if I'm wrong, but I think that if you use "TNS" as your connection type, SQL Developer will use the thick driver, while the default, "Basic" connection type uses the thin driver. Otherwise, you're going to have to use the "Advanced" connection type and type in the Custom JDBC URL for the thick driver.
Thanks for the OCI/thick driver suggestion jflack. There's antoher issue I'm facing with this solution : with current version of SQL Dev (2.1.63.x), activating OCI/thick driver causes an error : no ocijdbc11 in java.library.path. In other words, it turns out that a 11g client must be installed in order to use thick driver option. Well, with no better option I'll give it a try and will keep everyone posted here.
I confirm using OCI is the solution to slow answering queries. OCI/thick driver makes queries run & return data MUCH faster. Tanks for your time riedelme & jflack.
Sorry ... totally lost on the thick and thin driver. Where can I read more about it? From what I gather from this thread: thick - Connection Type: Advanced; and fill in the Custom JDBC URL?? thin - Connection Type: Basic; doesn't matter SID or Service Name?? Is that correct??
The thick driver comes with any installed Oracle client (Instant or full) and uses OCI to communicate with the DB. The thin driver comes with e.g. sqldev and uses JDBC to communicate with the DB. Unless you have the OCI preference set, sqldev will use the shipped JDBC. Have fun, K.
Oracle SQL Developer 2.1 comes with an option in Tools, Preferences, Advanced to "Use OCI/Thick driver" Am I correct in that ticking this box will use the thick driver? I only have Oracle SQL Developer 2.1 installed (via the unzipped app that has the JRE implemented too) and use TNS and I presumed that this will use thick connectivity and not thin? Have I got things correct?
Still waiting on CVS extension for 2.1
How to create a sql server database in SQL DEVELOPER.
3.0EA1/2.1: Recent Objects "which object" confusion
2.1EA2: Package Decomposition function tooltip issue [fixed in 2.1.0 prod]
2.1EA2(maybe): Testing connections on startup?
SQL Dev Report Type Confusion
404 on download link for Newest version 2.1 EA 2
2.1 EA1/EA2/RC1: Case change quirk from Formatter
SQL Developer does not connect on VPN, LAN works
2.1EA2: Pinning Query Results and changing Connections [fixed in 2.1.0]
2.1EA2: Bind value issue
3.0EA1/2.1: Package Decomposition issues with exception declarations
2.1PROD/EA2: Show Errors problems
2.1 EA2/RC1/PROD: Select Connection dialog no longer used if no connection
2.1EA2: Table filter and use of sys.external_tab$ [fixed in 2.1 prod]