| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Statements parsing from stored procedures
This is actually interesting, because I too, wonder why soft parses are
occuring for certain statements. I wonder if it is not the SQL*PLUS
software. See below for what I mean...
SQL> create or replace package p0411 as
2 type mycursor is ref cursor;
3 procedure get_cursor (p_cur in out mycursor);
4 end p0411;
5 /
Package created.
SQL>
SQL> create or replace package body p0411 as
2 procedure get_cursor (p_cur in out mycursor) as
3 begin
4 open p_cur for select username from dba_users;
5 end;
6 end;
7 /
Package body created.
SQL> variable p refcursor
SQL> exec p0411.get_cursor(:p);
PL/SQL procedure successfully completed.
SQL> exec p0411.get_cursor(:p);
PL/SQL procedure successfully completed.
SQL> exec p0411.get_cursor(:p);
PL/SQL procedure successfully completed.
SQL>
/****************************************************************/
Trace output...
BEGIN p0411.get_cursor(:p); END;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0
0
Execute 3 0.00 0.00 0 0 0
3
Fetch 0 0.00 0.00 0 0 0
0
total 6 0.00 0.00 0 0 0
3
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 45
SELECT USERNAME
FROM
DBA_USERS
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0
0
Execute 3 0.01 0.00 0 0 0
0
Fetch 2 0.01 0.02 0 50 0
19
total 8 0.02 0.02 0 50 0
19
/****************************************************************/
So even though this statement should obviously be cached, it is being soft parsed (I verified by looking in v$sesstat for this session and looking at stats like '%parse%'). However, when I execute this from JDBC, it reuses the cursor with no soft parse.
Source code below can be compiled an run with any standard JDK 1.4 and above...
/****************************************************************/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class softParse {
public static void main(String args[]) throws java.sql.SQLException { try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/test10g",
"rep",
"rep");
int j = 1;
CallableStatement cstmt = conn.prepareCall("{call
p0411.get_cursor(?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
for (j = 1; j <= 10; j++) {
cstmt.executeUpdate();
}
Statement stmStats = conn.createStatement();
ResultSet rstStats = stmStats.executeQuery("select parse_calls "
+
"from v$sql " +
"where sql_text like
'BEGIN p0411.get_cursor%'");
while (rstStats.next()) {
System.out.println("After executing the \"bad\" cursor " + (j -
1) + " times, we have " + rstStats.getString("parse_calls") + " parse
calls.");
}
}
catch(Exception e) {
System.out.println(e);
}
}
}
/********************************************************/
Below is the output of the JDBC above...
C:\SCRIPTS\java>java softParse
After executing the "bad" cursor 10 times, we have 1 parse calls.
C:\SCRIPTS\java>
/****************************************************************/
So, JDBC only soft parsed once, whereas SQL*PLUS did it for every execution. No answer, but at least it works when using a different driver/tool for running the code...
Regards,
Steve Received on Mon Apr 17 2006 - 11:36:31 CDT
 |
 |