| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query on Number of Parses and Executions(for Selects from Views)
Hi Cary,
Thank your highlighting the same. I agree with you on calling a parse = once and reusing the parsed cursor is the best approach. But the = mentioned select statement in my case is a ref cursor and not a dynamic = statement.
I am wondering why a cursor like this should call a parse every time?. = Additional information here is that the cursor is defined in a function = (say function A) within a package and this function is called repeatedly = for various values of col1,col2,col3 from another procedure (Procedure = B) within the same package.
The pseudo code of the routine would be like
package B is
type cur_type is ref cursor;
cx_curtype cur_type;
Procedure B(some params)
for I in <some other cursor>
loop
call function A(col1,col2);
end loop;
end procedureB;
function A(col1, col2)
if <some condition> -- This condition would be true for all the =
calls in this run and hence only this cursor would be opened
then
open cursor cx_curtype=20
for select <column_name list>,rowid
from <view>
where col1=3D:b1 and col2 =3D:b2 and =
col3=3D'N'
order by col5;
else
open cursor cx_curtype=20
for select <column_name list>,rowid
from <view>
where col1=3D:b1 and col3=3D'N'
order by col5;
end if;
end function A;
=20
end package;
Since the same SQL statement is executed, I would expect it to reuse the = parsed statement but in this case it doesn't. Is this a expected = behavior with ref cursors?
Thanks again
Best Regards
Sriram Kumar
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap
Sent: Tuesday, April 06, 2004 12:24 PM
To: oracle-l_at_freelists.org
Subject: RE: Query on Number of Parses and Executions(for Selects from =
Views)
Sriram,
How many parse *calls* get made is determined entirely by the application. The application is simply telling the database to parse the same statement 2,079 times. The Oracle kernel is smart enough to know that only the first requires a "hard parse," but once the app tells the db to execute a parse call, the db counts it as a parse call. ...And it costs a lot of end-user response time to process all these parse calls: lots of CPU and lots of network latency that shows up as time spent in "SQL*Net message from client" timed events.
To fix the problem, pull the parse call outside of whatever loop it's in within the application source code. If it's PL/SQL, then use the DBMS_SQL package to extract the parse call from the loop so that you have this:
/* good */
dbms_system.parse(...)
loop
dbms_sql.execute_and_fetch(...)
end
...And NOT this:
/* bad */
loop
execute immediate ...
end
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 4/6 Seattle, 5/7 Dallas, 5/18 New Jersey - SQL Optimization 101: 4/19 Denver, 5/3 Boston, 5/24 San Diego - Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
k.sriramkumar_at_iflexsolutions.com
Sent: Tuesday, April 06, 2004 1:23 AM
To: oracle-l_at_freelists.org
Subject: Query on Number of Parses and Executions(for Selects from
Views)
Hi Guru's
=20
I have a query like this
=20
select <column_name list>,rowid
from <view>
where col1=3D:b1 and col2 =3D:b2 and col3=3D'N'
order by col5
=20
Sriram Kumar=20
DISCLAIMER:
This message contains privileged and confidential information and is =
intended only for the individual named.If you are not the intended =
recipient you should not disseminate,distribute,store,print, copy or =
deliver this message.Please notify the sender immediately by e-mail if =
you have received this e-mail by mistake and delete this e-mail from =
your system.E-mail transmission cannot be guaranteed to be secure or =
error-free as information could be =
intercepted,corrupted,lost,destroyed,arrive late or incomplete or =
contain viruses.The sender therefore does not accept liability for any =
errors or omissions in the contents of this message which arise as a =
result of e-mail transmission. If verification is required please =
request a hard-copy version.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Apr 06 2004 - 05:08:45 CDT
 |
 |