Problem: For a Oracle (backend) – Java (frontend) system, a query  returning about 500 K rows was running very slow and is taking about 45 mins.  Oracle database is hosted on server (A), has a separate application server (B), and client can access database from client machine (C), using SQLPLUS.  Query is running slow from the clients machine (C) and from the java front end (B).

Basic Checks on dbase:

Make sure to check following with respects to the tables / database in question:

• Latest Database statistics exists for the tables in question.
• Indexes
• Degree of Parallelism at Table Level:
• Optimizer mode

Database configuration parameters  – sga_max_size,  pga_aggregate_target , memory_target, processes Read more…

While calling a stored procedure from my java class; I was getting the following  error-

java.sql.SQLException: “Output parameter not allowed as argument list prevents use of RPC.”

After doing a little research; I found that When calling a stored procedure that has output parameters, the driver has to call the procedure using a remote procedure call (RPC). Stored procedures should be invoked using the special JDBC call escape syntax.

For example, {call stored_Proc1(?,?,?,?)}.

In this case the driver will be able to use an RPC successfully as all the parameters are represented by parameter markers (?). If however parameters are supplied as a mixture of parameter markers and literals,

for example {call stored_Proc1(?,’ ‘,?,0)}

Then the driver is unable to use an RPC and therefore cannot return output parameters. In these circumstances the driver raises the above exception and execution fails.

It is possible to use mixed parameter lists to call stored procedures that do not have output parameters. In this case the driver will substitute the parameters locally and use a normal “execute procedure” SQL call; however, this mode of execution is less efficient than an RPC.

Using Spring with JDBC is not a very common usage I have seen but sometimes you have applications with very few tables and simple CRUD operation and you do not need full fledged ORM framework like hibernate .

JDBC API provides connectivity between the database and java program. It provides support for wide range of databases from complex SQL – Based databases to tabular data sources like spread sheets.  With JDBC – enabled driver one can connect to database and run the queries, fetch data etc in a java program.

JDBC API is widely used to access db in java applications. Often hibernate and JDBC gets compared and Hibernate looks better than JDBC API. But Hibernate it has a big learning curve. People who are aware of JDBC sometime want their projects to be completed fast and might not want to switch to ORM.  In this case one can use JDBC with Spring. Read more…