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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 30, 2003 10:49 AM

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).