Isn't it true that we can do ResultSet pre-fetching when executing SQL directly from JDBC but *can not* when getting a REF CURSOR from PL/SQL? Won't this mean additional round-trips for the latter which will make it even less performant?

I'm doing something very similar, but the results aren't so close. The ad hoc SQL executes in about 300ms, while calling the function and iterating thru the results, even from sql*plus, takes over 2 seconds. The results are the same (300ms and > 2sec) when I execute the ad hoc SQL or the function from Java over the JDBC thin driver.

We added some debugging output to the Java code that showed that an inordinate amount of time is spent in oracle.jdbc.driver.OracleCallableStatement.getCursor( int ). Does that method just create the ref cursor, send it over the wire, and cast it to a Java ResultSet? Or does it also execute the SQL up to the first returned row?

The SQL is the same in both cases, except that the function uses parameterized variables in the SQL, instead of changing constants. Thus this approach is far more scalable because it's using bind variables. But overall it's no more scalable, because of the performance hit of the ref cursor.

We're using a function that returns a ref cursor, instead of a procedure that takes a ref cursor variable as an OUT parameter. I don't think this will make any difference, but I'll try it that way to be sure.

I can understand that the PL/SQL can't execute SQL faster than the SQL itself. But it seems like there is substantial overhead with ref cursors, and it's not caused by the JDBC thin driver or the network.

Are we missing something here? Are there other approaches (like returning a PL/SQL collection) that would verify that the ref cursor is the cause of the performance hit? If not, are there any workarounds to this?

Thanks for your time.

Thanx for a great web site – I am a big fan of yours!

You keep suggesting that if you can do it in sql, do it instead of using procedural (pl/sql) code – and I agree.

However, I am wondering whether the readability of code decreases once the select stmt becomes too complex and we start hitting a point of diminishing returns. For example, do you think the following query in the procedure (performance wise it is fine) should be rewritten in procedural code (which would do it step by step and presumably would be more readable)?

Is the burden on the developer to understand the query (it is reasonably simple if you break it in steps but it does take considerably more effort than procedural code )?
What are your thoughts?

---the example query --
procedure get_multi_hosts_comp_summary ( p_job_guid raw,
p_execution_guid raw, p_cursor out refCursor)
is
begin
open p_cursor for
select delta_comp_guid, lhs_host_name, rhs_host_name,
decode ( delta_comp_guid, null, 'BEING_COMPUTED',
decode ( instr ( hw_result||os_result || oracle_result ||
os_registered_sw_result, 'ERROR'), 0,
decode
( instr(hw_result||os_result || oracle_result ||
os_registered_sw_result, 'COW_RESULT_DIFFERENT'),
0,'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT'
),
'ERROR_IN_COMPARISON'
)
) result,
decode( delta_comp_guid, null, 'BEING_COMPUTED',
decode( hw_result, 'ERROR', 'HW_COMPARISON_ERROR',
hw_result ) ) hw_result,
decode( delta_comp_guid, null, 'BEING_COMPUTED',
decode( os_result, 'ERROR', 'OS_COMPARISON_ERROR',
os_result ) ) os_result,
decode( delta_comp_guid, null, 'BEING_COMPUTED',
decode( oracle_result, 'ERROR', 'OS_COMPARISON_ERROR',
oracle_result ) ) oracle_result,
decode( delta_comp_guid, null, 'BEING_COMPUTED',
decode( os_registered_sw_result, 'ERROR', 'OS_COMPARISON_ERROR',
os_registered_sw_result ) ) os_registered_sw_result
from
(
select delta_comp_guid, lhs_host_name, rhs_host_name,
max(hw_result) hw_result,
max(os_result) os_result,
max(oracle_result) oracle_result,
max(os_registered_sw_result) os_registered_sw_result
from
(
with common_columns as
(
select a.job_guid job_guid, map.delta_comp_guid delta_comp_guid,
a.lhs_host_name lhs_host_name, a.rhs_host_name rhs_host_name
from
(
select distinct j.job_id job_guid, jp.scalar_value lhs_host_name,
t.target_name rhs_host_name
from mgmt_job j, mgmt_job_exec_summary exs,
mgmt_job_target jt, mgmt_targets t,
mgmt_job_parameter jp
where j.job_id = p_job_guid
and j.job_id = exs.job_id
and exs.execution_id = p_execution_guid
and jt.job_id = j.job_id
and jt.target_guid = t.target_guid
and jp.job_id = j.job_id
and jp.execution_id = exs.execution_id
and jp.parameter_name = 'firstCompareTarget'
) a,
mgmt_comp_id_to_job_id_map map
where a.job_guid = map.job_guid (+)
and a.rhs_host_name = map.rhs_host_name(+)
)
select c.delta_comp_guid, c.lhs_host_name, c.rhs_host_name,
decode(max(error_count), 0,
decode(max(s.different_count+s.left_count+s.right_count), 0,
'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT'),
'ERROR'
) hw_result,
null os_result,
null oracle_result,
null os_registered_sw_result
from common_columns c, mgmt_delta_comp_summaries s
where c.delta_comp_guid = s.delta_comp_guid(+)
and instr(HOST_CONFIG_HW_VIEW_LIST, s.collection_type) != 0
group by c.delta_comp_guid, c.lhs_host_name, c.rhs_host_name
union
select c.delta_comp_guid, c.lhs_host_name, c.rhs_host_name,
null hw_result,
decode(max(error_count), 0,
decode(max(s.different_count+s.left_count+s.right_count), 0,
'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT'),
'ERROR'
) os_result,
null oracle_result,
null os_registered_sw_result
from common_columns c, mgmt_delta_comp_summaries s
where c.delta_comp_guid = s.delta_comp_guid(+)
and instr('HOST_CONFIG_OS_VIEW_LIST', s.collection_type) != 0
group by c.delta_comp_guid, c.lhs_host_name, c.rhs_host_name
union
select c.delta_comp_guid, c.lhs_host_name, c.rhs_host_name,
null hw_result,
null os_result,
decode(max(error_count), 0,
decode(max(s.different_count+s.left_count+s.right_count), 0,
'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT'),
'ERROR'
) oracle_result,
null os_registered_sw_result
from common_columns c, mgmt_delta_comp_summaries s
where c.delta_comp_guid = s.delta_comp_guid(+)
and instr(ORACLE_TOPLEVEL_VIEW_NAME, s.collection_type) != 0
group by c.delta_comp_guid, c.lhs_host_name, c.rhs_host_name
union
select c.delta_comp_guid, c.lhs_host_name, c.rhs_host_name,
null hw_result,
null os_result,
null oracle_result,
decode(max(error_count), 0,
decode(max(s.different_count+s.left_count+s.right_count), 0,
'COW_RESULT_SAME', 'COW_RESULT_DIFFERENT'),
'ERROR') os_registered_sw_result
from common_columns c, mgmt_delta_comp_summaries s
where c.delta_comp_guid = s.delta_comp_guid(+)
and instr('ECM$ORACLE_TOPLEVEL', s.collection_type) != 0
group by c.delta_comp_guid, c.lhs_host_name, c.rhs_host_name
) diff_qry
group by delta_comp_guid, lhs_host_name, rhs_host_name
) diff_qry1;

end;

well, since procedural code is done in steps
it is more readable than a huge sql select -
(kinda like a huge function versus one that is split
up into many) - at least that is my opinion.

On the same topic, from the book “Oracle Performance Planning”, we get:
"
When implementing data access (queries) or data
changes (DML), use SQL. This rule is specific to the business logic modules of code where procedural code is mixed with data access (non-procedural SQL)
code. There is great temptation to put procedural logic into the SQL access. This
tends to result in poor SQL that is resource-intensive. SQL statements with
DECODE case statements are very often candidates for optimization, as are
statements with a large amount of OR predicates or set operators, such as UNION
and MINUS."

Again this seems to suggest that we should not have huge
selects..

hi tom
I will stick to the select statement way of doing
things (as per your suggestion) - you are probably
right- the procedural code also may be complex.
thanx!:)

It seems a truism to say o, but whether it's procedural code or SQL you make it easier to maintain by adding descriptive comments, right?

It seems too often that a perfectly rational developer can pepper PL/SQL with expplanatory comments but has some kind of a mental block on commenting within the sql itself.

Consider the complex SQL example given. What would be wrong with providing in-line commentary on the real meaning of some of the complex decodes, or the individual data sets that are union'ed together? I'd bet that if it was broken down into procedural code it would get commented. Or am I wrong?

I actually find that my SQL is more and more procedural when I look at it.  Especially with inline views.

I tend to build queries from the inside out -- they are like subroutines to me actually (oh, that might put a date on me -- let me call them "methods")

Here is a cool little "program" that creates a golfing schedule for 8 player over 7 weeks, randomly assigning them to different groups:

ops$tkyte@ORA920> with players as
  2  ( select 'P'||rownum username
  3      from all_objects
  4     where rownum <= 8),
  5  weeks as
  6  ( select rownum week
  7      from all_objects
  8     where rownum <= 7 )
  9  select week,
 10         max(decode(rn,1,username,null)) u1,
 11         max(decode(rn,2,username,null)) u2,
 12         max(decode(rn,3,username,null)) u3,
 13         max(decode(rn,4,username,null)) u4,
 14         max(decode(rn,5,username,null)) u5,
 15         max(decode(rn,6,username,null)) u6,
 16         max(decode(rn,7,username,null)) u7,
 17         max(decode(rn,8,username,null)) u8
 18   from ( select username,
 19                 week,
 20                 row_number() over (partition by week order by rnd) rn
 21            from ( select username, week, dbms_random.random rnd
 22                     from players, weeks
 23                 )
 24       )
 25   group by week
 26  /


      WEEK U1 U2 U3 U4 U5 U6 U7 U8
---------- -- -- -- -- -- -- -- --
         1 P5 P4 P7 P1 P3 P2 P6 P8
         2 P3 P6 P8 P7 P1 P5 P4 P2
         3 P8 P1 P7 P6 P5 P4 P3 P2
         4 P3 P5 P8 P4 P7 P2 P1 P6
         5 P8 P7 P1 P4 P3 P6 P5 P2
         6 P3 P2 P6 P1 P4 P8 P7 P5
         7 P1 P2 P4 P8 P7 P5 P6 P3

7 rows selected.


U1..U4 is one group, U5..U8 is another....  when asked to do this for a friend (come up with random assignments over 7 weeks for 8 players), this was actually the first approach that popped into my head...

comments might be:

o create a set of 7 weeks (weeks)

o create a set of 8 players (players)

o cartesian product weeks with players, so we end up with 56 rows -- each player
  in each week.  Assign Random number to each row in this set

o Now, for each week, sort the players by that random number -- and assign the
  number 1, 2, 3, 4, .. 8 to them after sorting (that is the row_number() )

o then use a standard pivot technique to pivot the results by week.


If you use that in a create table Assignments as <select>, you can use:

ops$tkyte@ORA920> with players as
  2  ( select 'P'||rownum username
  3      from all_objects
  4     where rownum <= 8)
  5  select u1,
  6        max( decode( rn, 1, decode(u2,u1,to_number(null),plays), null )) p1,
  7        max( decode( rn, 2, decode(u2,u1,to_number(null),plays), null )) p2,
  8        max( decode( rn, 3, decode(u2,u1,to_number(null),plays), null )) p3,
  9        max( decode( rn, 4, decode(u2,u1,to_number(null),plays), null )) p4,
 10        max( decode( rn, 5, decode(u2,u1,to_number(null),plays), null )) p5,
 11        max( decode( rn, 6, decode(u2,u1,to_number(null),plays), null )) p6,
 12        max( decode( rn, 7, decode(u2,u1,to_number(null),plays), null )) p7,
 13        max( decode( rn, 8, decode(u2,u1,to_number(null),plays), null )) p8
 14   from ( select u1, u2, plays,
 15                 row_number() over ( partition by u1 order by u2) rn
 16            from ( select a.username u1, b.username u2,
 17                         (select count(*)
 18                            from assignments
 19                           where (select a.username from dual) in (u1,u2,u3,u4)
 20                             AND (select b.username from dual) in (u1,u2,u3,u4)
 21                         ) +
 22                         (select count(*)
 23                            from assignments
 24                           where (select a.username from dual) in (u5,u6,u7,u8)
 25                             AND (select b.username from dual) in (u5,u6,u7,u8)
 26                         ) plays
 27                    from players a,
 28                         players b
 29                 )
 30        )
 31  group by u1
 32  /


U1  P1  P2  P3  P4  P5  P6  P7  P8
-- --- --- --- --- --- --- --- ---
P1       5   3   1   3   4   2   3
P2   5       1   3   3   4   2   3
P3   3   1       3   3   4   4   3
P4   1   3   3       3   2   6   3
P5   3   3   3   3       2   4   3
P6   4   4   4   2   2       1   4
P7   2   2   4   6   4   1       2
P8   3   3   3   3   3   4   2

8 rows selected.


to see how "fair" the assignments were -- that shows P1 plays with P2 5 times, P3, 3 times and so on.

I'll let you figure that one out -- it would obviously need comments in real code -- but I think you'll find it almost procedural in nature if you start from the inside and work your way out.
 

yes inline comments would make it much more readable.
would do that for my code - thanx!:)

Since you are using bind variables when you invoke PL/SQL from Java side. I wonder if the whole PL/SQL gets parsed during the first invocation from java.

If this is true, does that mean that there is no need to use dynamic PL/SQL for parsing individual SQL statement in the PL/SQL package? Because the whole package has been parsed during the first invocation.

Thanks,

Tony

Thanks you so very much for your quick reply and wonderful advice! My experiment just proved your statement.

Many Thanks!!!

Hi Tom
I know you are in favor of moving sql statements
from Java to plsql for various reasons that you already
have mentioned. I was trying to set up a benchmark
to see if there were any performance related
differences between the two cases.

I was not expecting any performance differences.
Anyways, what puzzled me that if I have a code as:
create the callable statement:

for (int i=0; i < 10; i ++)
{
bind the callable statement
execute the callable statement (returns a ref cursor)
..
}
then the callable statement was getting soft parsed
10 times. I would have expected it to get parsed
only once. If I put the same select in Java
it got soft parsed only once (since I prepared
the statement only once across the iteration.)

Is there a way to ensure that the same can be
true for plsql case as well? Does this have something
to do with the fact that we are passing an
anonymous block to execute a procedure (there is
no other way ) I set the session_cached_cursors to
100 and still see the same behavior

Menon:)

This is the second time - I forgot about the ref
cursors!!!
Anyways, the only reason I was looping was
to execute the same query again and again so that the
measurement times are big enough to average out.


I do agree - we will never need to do the looping
at the client side in real life. I did realize it
but as I said I was trying to get more reliable benchmark
results.

I guess I have to select a really big table
to do the benchmark then?


Thanx!
Menon:)

Hi tom
i am greatful for your site. I need a solution for inserting a collection from java to a table using PL/SQL procedure. I want to write a procedure to do this and call it from java. The parameter of the procedure may be refcursor/array or anything like that. I want to get the collection and want to insert by loop in the procedue. It will be helpfull if you show me an example.
best regards.

"depends on what exactly it is you are trying to measure."

Well, I was trying to measure the differences in
two cases:
1. I use a select statement in Java.
2. I use an anonymous block that calls a stored
procedure from Java doing the same thing that the
select statement does.

I didn't really expect any performance differences
in the two but wanted to benchmark it anyways.
I "solved" the issue by changing the ref cursor
issue in the select to return a count instead :)
But somehow my results are fairly random!
In some runs they are close to each other in
terms of run time and in terms of latch consumption.
In other runs the plsql case was around 50% slower!!
The database is newly installed (9.2.0.1.0)
Could you kindly take a look at the benchmarks and
see if there is something wrong? I use your
DbmsOutput.java and your runstats_pkg apart from
the following files.

Thanx a ton!
Menon:)

---schema.sql--
rop table t1;
create table t1 as
select sys_guid() key1, owner, object_id, object_type
from all_objects;

commit;
insert /*+APPEND */ into t1
select sys_guid() key1, 'SYS', object_id, object_type from all_objects;

commit;
insert /*+APPEND */ into t1
select sys_guid() key1, 'SYS', object_id, object_type from all_objects;

commit;
insert /*+APPEND */ into t1
select sys_guid() key1, 'SYS', object_id, object_type from all_objects;

alter table t1 add constraint t1_pk primary key ( key1 );
create index t1_idx1 on t1 ( owner );
analyze table t1 compute statistics;
------
------Benchmark.java - uses DbmsOutput.java and
------- your runstats package to compare
------- two approaches - effectively runs
-------your runstats from java and prints results

import java.sql.*;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleCallableStatement;

public class Benchmark
{
public static void setSessionCachedCursors ( OracleConnection connection,
int sessionCachedCursors )
throws SQLException
{
String stmtStr = "alter session set session_cached_cursors=" +
sessionCachedCursors ;
Statement stmt = null;
try
{
stmt = connection.createStatement();
stmt.execute( stmtStr );
}
finally
{
stmt.close();
}
}

public static void startTrace ( OracleConnection connection )
throws SQLException
{
String setTimedStatisticsStmt = "alter session set timed_statistics=true";
String setTraceStmt =
"alter session set events '10046 trace name context forever, level 12'";
Statement stmt = null;
try
{
stmt = connection.createStatement();
stmt.execute( setTimedStatisticsStmt );
stmt.execute( setTraceStmt );
}
finally
{
stmt.close();
}
}

public static void markStart( OracleConnection connection )
throws SQLException
{
_benchmarkStatementArray[BENCHMARK_START_INDEX].execute();
}

public static void markMiddle( OracleConnection connection )
throws SQLException
{
_benchmarkStatementArray[BENCHMARK_MIDDLE_INDEX].execute();
}

public static void markEnd( OracleConnection connection )
throws SQLException
{
_benchmarkStatementArray[BENCHMARK_STOP_INDEX].execute();
}

public static void closeBenchmarkStatements (
OracleConnection connection ) throws SQLException
{
for( int i=0; i < _benchmarkStatementArray.length; i++)
{
_benchmarkStatementArray[i].close();
}
}

public static void prepareBenchmarkStatements (
OracleConnection connection ) throws SQLException
{
_benchmarkStatementArray[BENCHMARK_START_INDEX]=
(OracleCallableStatement) connection.prepareCall( BENCHMARK_START );

_benchmarkStatementArray[BENCHMARK_MIDDLE_INDEX]=
(OracleCallableStatement) connection.prepareCall( BENCHMARK_MIDDLE );

_benchmarkStatementArray[BENCHMARK_STOP_INDEX]=
(OracleCallableStatement) connection.prepareCall( BENCHMARK_STOP );
_benchmarkStatementArray[BENCHMARK_STOP_INDEX].setInt(1,
BENCHMARK_DIFFERENCE_THRESHOLD);
_dbmsOutput = new DbmsOutput ( connection );
_dbmsOutput.enable ( DBMS_OUTPUT_BUFFER_SIZE );
}

public static void printBenchmarkResults()
throws SQLException
{
_dbmsOutput.show();
_dbmsOutput.close();
}

private static String BENCHMARK_START = "begin runstats_pkg.rs_start; end;";
private static String BENCHMARK_MIDDLE = "begin runstats_pkg.rs_middle; end;";
private static String BENCHMARK_STOP = "begin runstats_pkg.rs_stop(?); end;";

private static OracleCallableStatement[] _benchmarkStatementArray = new OracleCallableStatement[3];

private static DbmsOutput _dbmsOutput;
private static final int DBMS_OUTPUT_BUFFER_SIZE = 1000000;

private static final int BENCHMARK_START_INDEX = 0;
private static final int BENCHMARK_MIDDLE_INDEX = 1;
private static final int BENCHMARK_STOP_INDEX = 2;
private static final int BENCHMARK_DIFFERENCE_THRESHOLD = 200;
}
----------TestSqlInJavaOrPlsql.java -----
import java.sql.*;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleCallableStatement;

public class TestSqlInJavaOrPlsql
{

public static void main(String[] args) throws SQLException
{
long startTime=0, middleTime=0, endTime=0;

DriverManager.registerDriver( new OracleDriver());
_checkUsage( args );

OracleConnection connection = _getOracleConnection();
Benchmark.setSessionCachedCursors ( connection, 100 );
Benchmark.prepareBenchmarkStatements ( connection );

_prepareJavaBasedSqlStmt( connection );
_preparePlsqlBasedSqlStmt( connection );

// first execute through Java based statement
try
{
Benchmark.startTrace( connection );
//startTime = System.currentTimeMillis();
Benchmark.markStart ( connection );
_executePlsqlBasedCode ( connection );

//middleTime = System.currentTimeMillis();
Benchmark.markMiddle ( connection );
_executeJavaBasedCode ( connection );
Benchmark.markEnd ( connection );
//endTime = System.currentTimeMillis();

long timeTakenForJava = (middleTime - startTime);
long timeTakenForPlsql = (endTime - middleTime);
Benchmark.printBenchmarkResults ();
}
finally
{
_closeAll( connection );
}
}

private static void _closeAll ( OracleConnection connection )
{
try
{
_javaBasedStmt.close();
_plsqlBasedStmt.close();
Benchmark.closeBenchmarkStatements ( connection );
connection.close();
}
catch ( SQLException e)
{
//ignore;
}
}

private static void _checkUsage (String[] args)
{
int argc = args.length;
if( argc > 1)
{
System.out.println( "usage: java TestSqlInJavaOrPlsql num_of_iterations");
System.out.flush();
System.exit(1);
}

if (argc == 1)
{
_numOfIterations = Integer.parseInt( args[0] );
}
}

private static OracleConnection _getOracleConnection ()
throws SQLException
{
String connectionURL = "jdbc:oracle:thin:@usunrat24:1521:ora92i";
OracleConnection connection = (OracleConnection)
DriverManager.getConnection ( connectionURL, "benchmark", "benchmark");

connection.setAutoCommit ( false) ;
return connection;
}

private static void _prepareJavaBasedSqlStmt( OracleConnection connection )
throws SQLException
{
String javaBasedSQLStmtString =
"select /* from java */ count(*) from t1 " +
" where owner = ?";
_javaBasedStmt = (OraclePreparedStatement)
connection.prepareStatement ( javaBasedSQLStmtString );
}

private static void _preparePlsqlBasedSqlStmt( OracleConnection connection )
throws SQLException
{
String plsqlBasedSQLStmtString = "begin ? := test_pkg.get_count_data(?); end;";
_plsqlBasedStmt = (OracleCallableStatement)
connection.prepareCall ( plsqlBasedSQLStmtString );
}

private static void _executeJavaBasedCode( OracleConnection connection)
throws SQLException
{
ResultSet rset = null;
int count = 0;
try
{
for( int i=0; i < BENCHMARK_ITERATION_COUNT; i++)
{
_javaBasedStmt.setString(1, "SYS");

rset = _javaBasedStmt.executeQuery();
int j=0;
if ( rset.next() )
{
count = rset.getInt(1);
}
if( i == 0)
System.out.println ( "Java : result" + count );
}
}
finally
{
if ( rset != null )
{
try
{
rset.close();
}
catch (Exception e) {} // ignore;
}
}

}

private static void _executePlsqlBasedCode( OracleConnection connection)
throws SQLException
{
int count = 0;
try
{
for( int i=0; i < BENCHMARK_ITERATION_COUNT; i++)
{
_plsqlBasedStmt.setString( 2, "SYS" );
_plsqlBasedStmt.registerOutParameter( 1, oracle.jdbc.OracleTypes.NUMBER);
_plsqlBasedStmt.execute();
count = ( int) _plsqlBasedStmt.getInt(1);
if( i == 0)
System.out.println ( "Plsql : result" + count );
}
}
finally
{
;
/*
if ( rset != null )
{
try
{
rset.close();
}
catch (Exception e) {} // ignore;
}
*/
}

}

private static OracleCallableStatement _plsqlBasedStmt;
private static OraclePreparedStatement _javaBasedStmt;
private static int _numOfIterations = 1;
private static final int BENCHMARK_ITERATION_COUNT = 200;
}
-----

---test_pkg.sql
create or replace package test_pkg as
function get_data ( p_owner_name in varchar2 )
return sys_refcursor;
function get_count_data ( p_owner_name in varchar2 )
return number;
end;
/
show errors
/
create or replace package body test_pkg as
function get_data ( p_owner_name in varchar2 )
return sys_refcursor
is
l_cursor sys_refcursor;
begin
open l_cursor for
select /* from plsql */ owner, object_id, object_type
from t1
where owner = p_owner_name;

return l_cursor;
end;

function get_count_data ( p_owner_name in varchar2 )
return number
is
l_count number;
begin
select /* from plsql */ count(*)
into l_count
from t1
where owner = p_owner_name;

return l_count;
end;
end;
/
show errors
/
----
Menon:)

Hi Tom,
This link you posted is broken :-(
</code> http://asktom.oracle.com/~tkyte/tkprof.html <code>
Is it possible for you to fix it?
That will really help. Thank you.

The problem is the page that is linked to redirects to a non-existant page. But, really, how hard would it be to look up the documentation with having the link?

- Mark

"way too much code for me to read, especially written in a foreign language like
that. "

No problems - I understand - if I find anything amiss
I will post it here. If anyone wants to take a crack
at reviewing the code - you are more than welcome:)

Menon:)

Well, I don't think I will take a crack at the code, but I can say that I just read Cary Millsap's new book (Optimizing Oracle Performance) over the weekend and I highly, highly recommend it for doing performance investigation. It gets very technical and detailed and is at points rigorous (which is what makes it good), especially the chapter on Queueing Theory, but it is important stuff nonetheless IMO. (Not that my opinion should carry much weight mind you)...

- Mark

"Well, I don't think I will take a crack at the code, but I can say that I just
read Cary Millsap's new book (Optimizing Oracle Performance) over the weekend
and I highly, highly recommend it for doing performance investigation. It ..."

You read it over the weekend?!:) That is amazing - it
must be really good. Well, mine is on its way already!
I hope to utilize it in my own product soon.

Thanx anyways:)
Menon:)

I have not read it fully yet - but it is really an awesome
book! He has taken the usage of sql trace to the next
level and has given scientific proof for all his statements. Once you read it, it all seems common sense
and that is the sign of a truely great writer!

Tom - do you think his method will become the defacto
performance diagnotic method instead of the ususal
method being followed currently?

Thanx!

but I think his idea of narrowing constraining
the data diagnotic collection process accurately and
also his rigor of focusing on the business objective
is not being followed currently in majority of tuning
projects. This also comes back to your idea of instrumenting your code. Lots of similarities between
your approach and his approach.

Menon:)

Hi Tom
Thanks for your reply.Its great! Your online help is most useful.
Best regards

Tom, you wrote above,
"i would never rewrite working, fast sql as procedural code."

I definitely agree, however it seems that Steven Feuerstein tends to disagree on this topic.

See the "2. Break Your SQL Addiction" section at:
</code> https://asktom.oracle.com/Misc/oramag/on-constraints-case-and-cursors.html <code>

I'm curious to hear your thoughts on this.
Thanks.

In this article, SF is advocating a "modularised" approach to using SQL. Maybe Tom would agree with the purpose but not with the methods?

For instance, the example he gives is using a function to hide the expression last_name||','||first_name, to protect the application from requirements changes such as using a space instead of a comma.

An alternative would be to create a view including:

..., last_name||','||first_name as full_name, ...

That achieves the same objective without invoking PL/SQL.

Tom, I've never seen above type of SQL could you please refer me to a document where I can find more information regarding this or perhaps can you explain?
I mean the [WITH player AS (select ...] part of it. It looks as a new 9i feature.

Thanks

This is what I am looking for. Calling stored procedure from JSP.

Gr8.... help.....

hi tom
Are the sql comments
retained in the "top sql" that one gets from
statspack?

thanx!

In your Java sample above (in response to the second or third posting), it doesn't look like you close the db connection object. Where are you doing this? The Java mantra is to ensure that we close this or else we run the risk of hosing the system.

Hello Tom,

you use static declarations of the CallableStatement in several examples througout AskTom and I think I remember that you mentioned somewhere that this helps to reduce parsing.

I just wonder if this works on a J2EE application server as well. Will the static declaration cause any problems when used several times in parallel with different variables binded to it?

Best regards,
Matthias Schulz

P.S.: Your seminar in Munich was fabulous!

Hello Tom,

thank you for your quick reply.

The idea is to put all SQL in the database as PL/SQL procedures and call these from the J2EE server with CallableStatement's.
So to me there is a big chance that some of these calls will be processed n parallel.
I am especially worried about the ones that deliver opened reference cursors, because there will be more than one reference cursor used by the same java class opened at the same time.

Hello Tom,

what I tried to express with "parallel" is my concern about what will happen, when I declare a CallableStatement as static in a java class and:

1) several clients call the same class at the same time (could be 2 could be 2500 instances of that java class; all with different bind variable values; hostest on up to four J2EE-Servers running in parallel)

2) client A uses a reference cursor that was delivered by an instance of java class "OraRefCursor" and now client B creates an instance of the same class with different bind variable values and tries to use his delivered cursor too.

In short: Is it dangerous to declare CallableStatement or PreparedStatement as static in a java class, when I can't exclude (real) parallel use of that class (meaning several instances created and used simultanously)?

And if the aswers is yes, what is the next best alternative? Declare CallableStatement or PreparedStatement not as static, but as private?

Thank you very much in advance.

Best regards
Matthias Schulz

Hello Tom,

sorry for the delay, but here is an example that doesn't work with the PreparedStatement declared as static, but works well when the PreparedStatement is not declared static:

package jdbcprepstmt;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import oracle.jdbc.pool.OracleDataSource;

public class OraclePrepStmt{
String jobquery;
static PreparedStatement prepStmt; //

public OraclePrepStmt(Connection conn) throws SQLException {
jobquery = "SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER = upper(?) AND OBJECT_TYPE = upper(?)";
prepStmt = conn.prepareStatement(jobquery);
}


public void setSelectObjectOwner(String objectOwner) throws SQLException{
prepStmt.setString(1, objectOwner);
}


public void setSelectObjectType(String objectType) throws SQLException{
prepStmt.setString(2, objectType);
}


public void printAllObjects() throws SQLException {
// get the result set
ResultSet rset = prepStmt.executeQuery();

// determine the number of columns in each row of the result set
ResultSetMetaData rsetMeta = rset.getMetaData();
int count = rsetMeta.getColumnCount();

// print the results, all the columns in each row
int rows = 0;
while (rset.next()) {
rows++;
String rsetRow = " ";
for (int i = 1; i <= count; i++) {
rsetRow = rsetRow + " - " + rset.getString(i);
}
System.out.println(rsetRow);
}

// close the result set
rset.close();
}


public static void main(String[] args)throws SQLException {
// connect to a local XE database
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:tech/tech@localhost:1521/XE");
Connection conn = ods.getConnection();
conn.setAutoCommit(false);

// use the prepared statement class
OraclePrepStmt ps1 = new OraclePrepStmt(conn);
ps1.setSelectObjectOwner("SYS");
ps1.setSelectObjectType("TABLE");

OraclePrepStmt ps2 = new OraclePrepStmt(conn);
ps2.setSelectObjectOwner("SYSTEM");
ps2.setSelectObjectType("VIEW");

OraclePrepStmt ps3 = new OraclePrepStmt(conn);
ps3.setSelectObjectOwner("SYSTEM");
ps3.setSelectObjectType("TABLE");

ps1.printAllObjects();
ps2.printAllObjects();
ps3.printAllObjects();
ps1.printAllObjects();


// close database connections
conn.close();
ods.close();
}
}
------------------------------------------------------------
Here is the result with the PreparedStatement declared as static (which is obviously wrong):
- SYSTEM - TABLE - DEF$_TEMP$LOB
- SYSTEM - TABLE - OL$
- SYSTEM - TABLE - OL$HINTS
- SYSTEM - TABLE - OL$NODES
- SYSTEM - TABLE - HELP
- SYSTEM - TABLE - DEF$_TEMP$LOB
- SYSTEM - TABLE - OL$
- SYSTEM - TABLE - OL$HINTS
- SYSTEM - TABLE - OL$NODES
- SYSTEM - TABLE - HELP
- SYSTEM - TABLE - DEF$_TEMP$LOB
- SYSTEM - TABLE - OL$
- SYSTEM - TABLE - OL$HINTS
- SYSTEM - TABLE - OL$NODES
- SYSTEM - TABLE - HELP
- SYSTEM - TABLE - DEF$_TEMP$LOB
- SYSTEM - TABLE - OL$
- SYSTEM - TABLE - OL$HINTS
- SYSTEM - TABLE - OL$NODES
- SYSTEM - TABLE - HELP

------------------------------------------------------------
Here is the result with the PreparedStatement not declared as static (which works as desired):
- SYS - TABLE - DUAL
- SYS - TABLE - SYSTEM_PRIVILEGE_MAP
- SYS - TABLE - TABLE_PRIVILEGE_MAP
- SYS - TABLE - STMT_AUDIT_OPTION_MAP
- SYS - TABLE - WRI$_ADV_ASA_RECO_DATA
- SYS - TABLE - KU$NOEXP_TAB
- SYS - TABLE - IMPDP_STATS
- SYS - TABLE - PLAN_TABLE$
- SYS - TABLE - AUDIT_ACTIONS
- SYS - TABLE - PSTUBTBL
- SYS - TABLE - ODCI_SECOBJ$
- SYS - TABLE - ODCI_WARNINGS$
- SYSTEM - VIEW - PRODUCT_PRIVS
- SYSTEM - TABLE - DEF$_TEMP$LOB
- SYSTEM - TABLE - OL$
- SYSTEM - TABLE - OL$HINTS
- SYSTEM - TABLE - OL$NODES
- SYSTEM - TABLE - HELP
- SYS - TABLE - DUAL
- SYS - TABLE - SYSTEM_PRIVILEGE_MAP
- SYS - TABLE - TABLE_PRIVILEGE_MAP
- SYS - TABLE - STMT_AUDIT_OPTION_MAP
- SYS - TABLE - WRI$_ADV_ASA_RECO_DATA
- SYS - TABLE - KU$NOEXP_TAB
- SYS - TABLE - IMPDP_STATS
- SYS - TABLE - PLAN_TABLE$
- SYS - TABLE - AUDIT_ACTIONS
- SYS - TABLE - PSTUBTBL
- SYS - TABLE - ODCI_SECOBJ$
- SYS - TABLE - ODCI_WARNINGS$
------------------------------------------------------------

So, this begs the question:
How to declare the PreparedStatement right and performant?

Thank you for your answer,
best regards,

Matthias Schulz

I have been reading the posts regarding SQL vs. PL/SQL performance as a part of tuning some queries that are used as in-line SQL in a Java frontend and I am somewhat confused. I need to develop some recommendations about how we should be doing queries in our Java frontend, both from a performance POV, security POV and best practices POV. And I need to be able to prove those recommendations to our Java developers primarily in terms of performance timings as that seems to carry more weight than either the security or best practices POVs. I took one of my re-written queries and ran it as both a SQL query and a procedure that could return the data to the Java frontend. Initially, I assumed that the PL/SQL would be faster but from reading the posts it appears that the SQL is faster. But when I ran my test case the PL/SQL procedure is faster and would appear to scale much better according to Runstats. I have included the TKPROF output and Runstats comparison. This is a 10g system, I was the only user for my testing of the whole process. I flushed the shared pool before turning on tracing and ran the SQL query twice and then the procedure. I then exited tracing, re-opened SQL Plus and ran the Runstats. There are a number of things that were puzzling me from this. I have bolded what I think is the relevant output in the TKPROF. My questions are as follows:

1. There were many queries that appeared in the TKPROF that I removed so things could fit in this post that did not seem relevant. What are they and what, if anything, do they signify?
2. I actually ran the SQL query twice so that the trace would reflect both the first time and a subsequent run but that does not show up in the TKPROF output. Am I missing something or did I do something wrong?
3. The security predicate does not show up in the TKPROF output for the PL/SQL procedure. Is it just buried or is there something wrong?
4. It appears the PL/SQL does better all around on this. Does PL/SQL perform better for something like this or was this just a fluke? Should I do this again? The Runstats comparison seems incredible.
5. Should I be concerned about the “SQL net message from client” results particularly for the PL/SQL procedure?
6. We are transitioning to using Hibernate to execute our Oracle queries. Can Hibernate run an Oracle stored procedure and take a result set from it?

SQL Query

select priv, priv_class, a.sid, a.type_code, a.subtype_code,
a.title, a.subtype_desc, a.create_by, a.create_by_name, a.create_date,
a.status_desc, a.last_update_date, a.dtg
from dciis_owner.activity_access_vw_lisa a where 1 = 1 and status in ('OP', 'CL', 'DR');

PL/SQL Procedure Code

declare
p_data dciis_owner.myActivityTableType;
begin
dciis_owner.pass_activity_new(p_data);
end;

create or replace procedure pass_activity_new (p_data out myActivityTableType)
as
l_data myActivityTableType;
cursor c_data
is
select myActivityType( priv, priv_class, sid, type_code, subtype_code, title,
subtype_desc, create_by, create_by_name, create_date,
status_desc, last_update_date, dtg )
from dciis_owner.activity_access_vw_lisa
where 1=1 and status in ('OP', 'CL', 'DR');
begin
open c_data;
loop
fetch c_data bulk collect into l_data limit 100;
dbms_output.put_line ('Looping, c_data%rowcount = ' || c_data%rowcount);
exit when c_data%notfound;
end loop;
dbms_output.put_line('out of loop, c_data%rowcount = ' || c_data%rowcount);
close c_data;
end;

Output

TKPROF: Release 10.1.0.2.0 - Production on Thu Jun 29 07:30:28 2006

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Trace file: prod24_ora_3390.trc
Sort options: default
.
.
.
begin :con := DCIIS_TABLE_SECURITY.SELECT_ACT_VW_LISA_PREDICATE(:sn, :on);
end;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.00 0.00 0 0 0 0
Execute 6 0.04 0.02 0 0 0 6
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.04 0.03 0 0 0 6

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77 (recursive depth: 1)
********************************************************************************
.
.
.
SELECT ROLE_CODE
FROM
ALL_ASSIGNMENT_ROLE_TYPE WHERE FORM_TYPE = :B3 AND TYPE_CODE = :B2 AND
(SUBTYPE_CODE = :B1 OR :B1 IS NULL)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6 0.04 0.03 0 0 0 0
Fetch 6 0.00 0.00 0 12 0 36
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.04 0.03 0 12 0 36

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77 (recursive depth: 3)

Rows Row Source Operation
------- ---------------------------------------------------
36 INDEX RANGE SCAN ALL_ASSIROLETYPE_UK (cr=12 pr=0 pw=0 time=572 us)(object id 34198)

********************************************************************************
.
.
.
select priv, priv_class, a.sid, a.type_code, a.subtype_code,
a.title, a.subtype_desc, a.create_by, a.create_by_name, a.create_date,
a.status_desc, a.last_update_date, a.dtg
from dciis_owner.activity_access_vw_lisa a where 1 = 1 and status in ('OP', 'CL', 'DR')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.37 0.38 0 94 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 578 3.02 2.91 101 103624 0 8640
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 582 3.39 3.30 101 103718 0 8640

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 11515

Rows Row Source Operation
------- ---------------------------------------------------
4320 SORT AGGREGATE (cr=12960 pr=97 pw=0 time=261014 us)
4319 FIRST ROW (cr=12960 pr=97 pw=0 time=232877 us)
4319 INDEX RANGE SCAN (MIN/MAX) ALLUPDAHIST_ACTITIME_IDX (cr=12960 pr=97 pw=0 time=221992 us)(object id 149019)
4320 FILTER (cr=38852 pr=4 pw=0 time=1038748 us)
4771 HASH JOIN (cr=30722 pr=0 pw=0 time=972930 us)
4771 NESTED LOOPS (cr=24169 pr=0 pw=0 time=274941 us)
4771 NESTED LOOPS (cr=9735 pr=0 pw=0 time=160420 us)
4771 HASH JOIN (cr=193 pr=0 pw=0 time=50648 us)
3 INLIST ITERATOR (cr=9 pr=0 pw=0 time=343 us)
3 TABLE ACCESS BY INDEX ROWID REF_CODES (cr=9 pr=0 pw=0 time=291 us)
3 INDEX RANGE SCAN REFCODE_UK (cr=6 pr=0 pw=0 time=131 us)(object id 33456)
4771 TABLE ACCESS FULL ACTIVITY (cr=184 pr=0 pw=0 time=24074 us)
4771 TABLE ACCESS BY INDEX ROWID ACTIVITY_TYPE (cr=9542 pr=0 pw=0 time=100853 us)
4771 INDEX UNIQUE SCAN ACTITYPE_PK (cr=4771 pr=0 pw=0 time=56653 us)(object id 34062)
4771 TABLE ACCESS BY INDEX ROWID PERSONNEL (cr=14434 pr=0 pw=0 time=104237 us)
4771 INDEX UNIQUE SCAN PERS_PK (cr=9542 pr=0 pw=0 time=64216 us)(object id 34122)
4780 VIEW (cr=6553 pr=0 pw=0 time=639027 us)
4780 HASH GROUP BY (cr=6553 pr=0 pw=0 time=624631 us)
130997 TABLE ACCESS FULL ALL_ASSIGNMENTS (cr=6553 pr=0 pw=0 time=131257 us)
0 INLIST ITERATOR (cr=8130 pr=4 pw=0 time=89441 us)
0 TABLE ACCESS BY INDEX ROWID ALL_ASSIGNMENTS (cr=8130 pr=4 pw=0 time=79208 us)
0 INDEX RANGE SCAN ALLASSI_ACTIPERSASSIROL_INDX (cr=8130 pr=4 pw=0 time=61763 us)(object id 81151)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 578 0.00 0.00
SQL*Net message from client 578 3.36 1063.14
SQL*Net more data to client 42 0.00 0.00
db file sequential read 25 0.01 0.01
db file scattered read 23 0.00 0.00
********************************************************************************

select audit$,properties
from
type_misc$ where obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 12 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 12 0 4

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS CLUSTER TYPE_MISC$ (cr=3 pr=0 pw=0 time=82 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=33 us)(object id 3)

********************************************************************************

select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 1 14 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.01 0.01 1 14 0 2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY (cr=2 pr=1 pw=0 time=6472 us)
0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=1 pw=0 time=6388 us)
0 INDEX RANGE SCAN I_OBJAUTH2 (cr=2 pr=1 pw=0 time=6375 us)(object id 109)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
********************************************************************************

select obj#
from
oid$ where user#=:1 and oid$=:2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 7 0.01 0.00 0 0 0 0
Fetch 7 0.00 0.00 1 15 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.01 0.01 1 15 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OID$ (cr=2 pr=0 pw=0 time=50 us)
0 INDEX UNIQUE SCAN I_OID1 (cr=2 pr=0 pw=0 time=38 us)(object id 294)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 3 0.00 0.01
********************************************************************************

declare
p_data dciis_owner.myActivityTableType;
begin
dciis_owner.pass_activity_new(p_data);
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 2 21 0 0
Execute 1 0.02 0.03 1 18 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.05 0.07 3 39 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 11515

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

SELECT MYACTIVITYTYPE( PRIV, PRIV_CLASS, SID, TYPE_CODE, SUBTYPE_CODE, TITLE,
SUBTYPE_DESC, CREATE_BY, CREATE_BY_NAME, CREATE_DATE, STATUS_DESC,
LAST_UPDATE_DATE, DTG )
FROM
DCIIS_OWNER.ACTIVITY_ACCESS_VW_LISA WHERE 1=1 AND STATUS IN ('OP', 'CL',
'DR')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.18 0.19 2 90 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 44 1.47 1.42 0 51806 0 4320
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 46 1.65 1.62 2 51896 0 4320

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 7988 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
4320 SORT AGGREGATE (cr=12960 pr=0 pw=0 time=190073 us)
4319 FIRST ROW (cr=12960 pr=0 pw=0 time=164356 us)
4319 INDEX RANGE SCAN (MIN/MAX) ALLUPDAHIST_ACTITIME_IDX (cr=12960 pr=0 pw=0 time=154077 us)(object id 149019)
4320 FILTER (cr=38852 pr=0 pw=0 time=980659 us)
4771 HASH JOIN (cr=30722 pr=0 pw=0 time=948046 us)
4771 NESTED LOOPS (cr=24169 pr=0 pw=0 time=278844 us)
4771 NESTED LOOPS (cr=9735 pr=0 pw=0 time=159559 us)
4771 HASH JOIN (cr=193 pr=0 pw=0 time=45034 us)
3 INLIST ITERATOR (cr=9 pr=0 pw=0 time=283 us)
3 TABLE ACCESS BY INDEX ROWID REF_CODES (cr=9 pr=0 pw=0 time=243 us)
3 INDEX RANGE SCAN REFCODE_UK (cr=6 pr=0 pw=0 time=118 us)(object id 33456)
4771 TABLE ACCESS FULL ACTIVITY (cr=184 pr=0 pw=0 time=24021 us)
4771 TABLE ACCESS BY INDEX ROWID ACTIVITY_TYPE (cr=9542 pr=0 pw=0 time=103931 us)
4771 INDEX UNIQUE SCAN ACTITYPE_PK (cr=4771 pr=0 pw=0 time=58000 us)(object id 34062)
4771 TABLE ACCESS BY INDEX ROWID PERSONNEL (cr=14434 pr=0 pw=0 time=108091 us)
4771 INDEX UNIQUE SCAN PERS_PK (cr=9542 pr=0 pw=0 time=66178 us)(object id 34122)
4780 VIEW (cr=6553 pr=0 pw=0 time=617665 us)
4780 HASH GROUP BY (cr=6553 pr=0 pw=0 time=603262 us)
130997 TABLE ACCESS FULL ALL_ASSIGNMENTS (cr=6553 pr=0 pw=0 time=131215 us)
0 INLIST ITERATOR (cr=8130 pr=0 pw=0 time=85286 us)
0 TABLE ACCESS BY INDEX ROWID ALL_ASSIGNMENTS (cr=8130 pr=0 pw=0 time=75542 us)
0 INDEX RANGE SCAN ALLASSI_ACTIPERSASSIROL_INDX (cr=8130 pr=0 pw=0 time=59257 us)(object id 81151)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.40 0.43 2 115 0 0
Execute 9 0.11 0.13 1 321 0 6
Fetch 578 3.02 2.91 101 103624 0 8640
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 594 3.53 3.47 104 104060 0 8646

Misses in library cache during parse: 4
Misses in library cache during execute: 3

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 585 0.00 0.00
SQL*Net message from client 585 381.54 1503.92
SQL*Net more data to client 42 0.00 0.00
db file sequential read 25 0.01 0.01
db file scattered read 23 0.00 0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 238 0.29 0.28 2 90 0 0
Execute 744 0.45 0.43 0 0 0 6
Fetch 1946 1.59 1.58 4 55649 0 8120
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2928 2.33 2.30 6 55739 0 8126

Misses in library cache during parse: 41
Misses in library cache during execute: 40
.
.
.
Runstats

Run1 ran in 53422 hsecs
Run2 ran in 2236 hsecs
Run1 ran in 2389.18 % of the time

Name Run1 Run2 Diff
STAT...session uga memory 65,408 72,896 7,488
LATCH.enqueues 10,378 334 -10,044
LATCH.enqueue hash chains 11,845 334 -11,511
LATCH.SQL memory manager worka 13,312 554 -12,758
LATCH.library cache lock 15,397 383 -15,014
LATCH.library cache pin 35,561 675 -34,886
LATCH.shared pool 39,023 595 -38,428
STAT...session pga memory 114,016 65,536 -48,480
STAT...Elapsed Time 53,441 2,243 -51,198
LATCH.row cache objects 60,637 4,684 -55,953
STAT...physical read bytes 57,344 0 -57,344
STAT...physical read total byt 57,344 0 -57,344
LATCH.library cache 60,496 1,210 -59,286
LATCH.cache buffers chains 187,386 84,614 -102,772
LATCH.session allocation 243,919 3,479 -240,440
STAT...bytes sent via SQL*Net 410,334 1,773 -408,561
STAT...session pga memory max 1,621,344 262,144 -1,359,200
STAT...session uga memory max 1,484,312 65,568 -1,418,744

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
717,290 99,522 -617,768 720.74%

PL/SQL procedure successfully completed.

I see that from Java calling sqlplus and pl/sql is about the same. If you are making a call to a stored procedure that returns a row set 1) from slqplus client 2) from java client what kinds of factors would cause the java client to be twice as slow as the sqlplus client -

Would the hand off from the database to the JDBC layer just inherently be slower? (talking 800ms versus 1600ms) here.

I apologize for the "genericity" of my question - so a generic followup is fine.

Hi Tom

Could you please point out the Oracle features ( Including object relational ) that could be used to improve performance of web based application using Java.

Regards

Respected Mr. Tom,

It’s my sincere request to have a look at the following snippet of code and please notice the last two lines. On reading, “Oracle9i - JDBC Developer’s Guide and Reference -Release 2 (9.2) (Part No A96654-01)”, I could not understand when I should use “executeUpdate()” and when “execute()”. I get the same results and presume both have same effect. Just to be sanguine, I ask you if there is any subtle difference between two methods. Please respond to.



String sql = "INSERT INTO " + tabName + " VALUES (?, ?)";

PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, empNo);
pstmt.setString(2, empName);

pstmt.executeUpdate();
pstmt.execute();


Regards,
Arindam Mukherjee

executeUpdate() is specifically intended for INSERT, UPDATE or DELETE statements. When appropriate, it returns the row count from the INSERT, UPDATE or DELETE. execute() is a general-purpose method that can be used with any SQL statement, so it will "work" anytime executeUpdate() does.
</code> http://java.sun.com/j2se/1.3/docs/api/java/sql/PreparedStatement.html <code>

Hi Tom,
We have a consultant who advices to use direct SQL instead of PL/SQL as we don’t context switch as it happens with PL/SQL(Going from PL/SQL engine to SQL engine).

The main SQL task is to update/insert/delete in bulks.

Our developers are coding PRO*C not JDBC but it seems to
be of the same case.

I will appreciate your answer,
Thanks very much, Oded.

Stew Ashton from Paris, France, - Please accept my heartfelt thanks.

Mr. Tom, In connection with this, I have one question on Oracle Session. When I connect oracle database from java program uploaded to oracle database through “loadjava” by the following syntax,

// Connect to the database
Connection conn = DriverManager.getConnection("jdbc:default:connection:");

Any DML statements executed by Java Stored procedure, waits for “commit” explicitly.

If I change the connection like below, any DML statement in Java Stored procedures gets committed.

// connect to Oracle using JDBC driver

OracleDataSource ods = new OracleDataSource();
String url = "jdbc:oracle:thin:@// 10.114.57.37/orcl";
ods.setURL(url);
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();


Could you kindly explain me – why? Does this new connection open a new session and before closing this new connection, it gets committed.

Every time you do getConnection() using the JDBC thin driver, add this line:

conn.setAutoCommit(false);

As Tom keeps telling us, being in auto-commit mode is BAD, but it is the default so you have to turn it off after every getConnection().

</code> http://java.sun.com/j2se/1.3/docs/api/java/sql/Connection.html <code>

import java.sql.*;
public class JDBCExample {
public static void main(String args[]) throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn=
DriverManager.getConnection("jdbc:oracle:thin:@dlsun137:5521:sol1",
" scott", "tiger");
Statement stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery("select ename, empno, sal from emp");
while(rs.next()){
String name= rs.getString(1);
int number = rs.getInt(2);
double salary = rs.getDouble(3);
System.out.println(name+" "+number+" "+salary);
}
rs.close();
conn.close();
}
}


i recieve the following error:-

JDBCExample.java:4: package oracle.jdbc.driver does not exist
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
^

regards,

Hi Tom,

I created a simple test scenario to test SQL vs PL/SQL performance.

I have a simple procedure;

create or replace procedure sp1
as
vn1 number;
begin
select count(*) into vn1
from tab3;
end sp1;

Tab3 is;
create table tab3 as select * from tab;

And I capture timing with the following SQL script.

set serveroutput on;
alter system flush shared_pool;
alter system flush buffer_cache;
select to_char(systimestamp, 'dd-mm-yyyy hh24:mi:ss.ff6') from dual;
exec sp1;
select to_char(systimestamp, 'dd-mm-yyyy hh24:mi:ss.ff6') from dual;
alter system flush shared_pool;
alter system flush buffer_cache;
select to_char(systimestamp, 'dd-mm-yyyy hh24:mi:ss.ff6') from dual;
select sysdate from dual;
select count(*) from tab3;
select to_char(systimestamp, 'dd-mm-yyyy hh24:mi:ss.ff6') from dual;

In this test, time difference of before and after the stored procedure execution is consistently much lesser than the time difference of SQL execution.

Where would be the overhead for SQL execution? Can it be just network overhead?

Thanks and Regards,

Sanjaya