| 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: pl/sql open cursor question
Content-Type: text/plain; charset="us-ascii"
> There are times when running a test harness
> through a single pl/sql is going to give you
> a spurious result because of extra pinning
> (of data blocks and library cache material)
> may confuse the issue.
That isn't a factor, as I never use the results from the first run for that very reason.
Jared
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: ml-errors_at_fatcity.com
12/30/2003 03:29 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: pl/sql open cursor question
There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue.
Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe).
Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor.
Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Jared,
Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste.
I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's
runstats harness, but found no significant differences:
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: jonathan_at_jlcomp.demon.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --=_alternative 00618E3288256E0C_= Content-Type: text/html; charset="us-ascii" <br><font size=2 face="Courier New">> There are times when running a test harness<br> > through a single pl/sql is going to give you <br> > a spurious result because of extra pinning<br> > (of data blocks and library cache material)<br> > may confuse the issue.</font> <br> <br><font size=2 face="Courier New">That isn't a factor, as I never use the results</font> <br><font size=2 face="Courier New">from the first run for that very reason.</font> <br> <br><font size=2 face="Courier New">Jared</font> <br><font size=2 face="sans-serif"><br> </font> <br> <br> <br> <table width=100%> <tr valign=top> <td> <td><font size=1 face="sans-serif"><b>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk></b></font> <br><font size=1 face="sans-serif">Sent by: ml-errors_at_fatcity.com</font> <p><font size=1 face="sans-serif"> 12/30/2003 03:29 AM</font> <br><font size=2 face="sans-serif"> </font><font size=1 face="sans-serif">Please respond to ORACLE-L</font> <br> <td><font size=1 face="Arial"> </font> <br><font size=1 face="sans-serif"> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com></font> <br><font size=1 face="sans-serif"> cc: </font> <br><font size=1 face="sans-serif"> Subject: Re: pl/sql open cursor question</font></table> <br> <br> <br><font size=2 face="Courier New"><br> There are times when running a test harness<br> through a single pl/sql is going to give you <br> a spurious result because of extra pinning<br> (of data blocks and library cache material)<br> may confuse the issue.<br> <br> Technically, if the implicit code and the explicit<br> code were written to do exactly the same thing,<br> then the implicit code should be faster because <br> of a couple of under-cover optimisations. (This<br> has been true for several years, I believe).<br> <br> Currently (9.2.0.X-ish) there is a bug that I <br> recently found on metalink which says something<br> about the FETCHes from an implicit cursor using<br> more CPU than the FETCHes from an explicit<br> cursor.<br> <br> Bottom line - test it in the environment where you<br> are using it, and on the version you are running in<br> production. In almost all cases, the difference will<br> probably be imperceptible, anyway.<br> <br> <br> Regards<br> <br> Jonathan Lewis<br> http://www.jlcomp.demon.co.uk<br> <br> The educated person is not the person <br> who can answer the questions, but the <br> person who can question the answers -- T. Schick Jr<br> <br> <br> One-day tutorials:<br> http://www.jlcomp.demon.co.uk/tutorial.html<br> <br> <br> Three-day seminar:<br> see http://www.jlcomp.demon.co.uk/seminar.html<br> ____UK___November<br> <br> <br> The Co-operative Oracle Users' FAQ<br> http://www.jlcomp.demon.co.uk/faq/ind_faq.html<br> <br> <br> ----- Original Message ----- <br> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com><br> Sent: Tuesday, December 30, 2003 10:49 AM<br> <br> <br> Jared,<br> <br> Point taken. I should do some testing instead of publish an opinion. I <br> still do not like the constraction, but that's a matter of taste.<br> <br> I have done some testing as well, because I think you were somehow <br> comparing apples and oranges: function a uses an implicit cursor, whereas <br> function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's <br> runstats harness, but found no significant differences:<br> <br> <br> -- <br> Please see the official ORACLE-L FAQ: http://www.orafaq.net<br> -- <br> Author: Jonathan Lewis<br> INET: jonathan_at_jlcomp.demon.co.uk<br> <br> Fat City Network Services -- 858-538-5051 http://www.fatcity.com<br> San Diego, California -- Mailing list and web hosting services<br> ---------------------------------------------------------------------<br> To REMOVE yourself from this mailing list, send an E-Mail message</font> <br><font size=2 face="Courier New">to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<br> the message BODY, include a line containing: UNSUB ORACLE-L<br> (or the name of mailing list you want to be removed from). You may<br> also send the HELP command for other information (like subscribing).<br> </font> <br> <br> --=_alternative 00618E3288256E0C_=-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Dec 30 2003 - 11:49:25 CST
 |
 |