Re: select rowid help for oracle 9.2.0.2.0

Thanks.
i'll post whatever commentary oracle has to write about this.

thanks again.
-maurice
samuels_at_seas.upenn.edu

Jonathan Lewis (jonathan_at_jlcomp.demon.co.uk) wrote:

: I've just repeated the test in 9.2.0.4
: It's a bug.
: Check your memory usage (session statistics
: session uga memory
: session uga memory max
: session pga memory
: session pga memory max
: )

: the version selecting the rowid leaked about
: 20MB of memory when I ran your test with
: 200,000 rows. It also took about 20 times
: as long as the version selecting a numeric
: column.

: I haven't investigated it any further - just send
: your test case to Oracle with a simple
: create table as select from all_objects
: to start it off.

: --
: Regards

: Jonathan Lewis
: http://www.jlcomp.demon.co.uk

: The Co-operative Oracle Users' FAQ
: http://www.jlcomp.demon.co.uk/faq/ind_faq.html

: April 2004 Iceland http://www.index.is/oracleday.php
: June 2004 UK - Optimising Oracle Seminar
: July 2004 USA West Coast, Optimising Oracle Seminar
: August 2004 Charlotte NC, Optimising Oracle Seminar
: September 2004 USA East Coast, Optimising Oracle Seminar
: September2004 UK - Optimising Oracle Seminar

: <samuels_at_red.seas.upenn.edu> wrote in message
: news:c64s9t$3eaf$1_at_netnews.upenn.edu...
: > hi,
: > i'm hoping someone can help me with this.
: > here's the scenerio, i have a million row table a and i do the following:
: > begin
: > for i in (
: > select /*+ full(a) */ a_column from a) loop
: > null;
: > end loop;
: > end;
: > /
: >
: > runtime: 2 minutes
: >
: > now, i try the following:
: > begin
: > for i in (
: > select /*+ full(a) */ rowid from a) loop
: > null;
: > end loop;
: > end;
: > /
: >
: > runtime: 10+ minutes (i've never waited long enough for it to complete)
: >
: >
: > i do not know how to explain the difference in time. i believe this is a
: pl/sql bug.
: > i can do a [select /*+ full(a) */ a_column from a] or [select /*+ full(a)
: */ rowid from a]
: > in sqlplus with no difference in runtime.
: >
: > i ran into this issue after upgrading from 8.1.7.4.0 to 9.2.0.2.0.
: >
: > if anyone has any ideas or can help that would be great (before i create a
: TAR).
: > thanks in advance.
: > -maurice
: >
: > ps --> the OS is SunOS 5.8
Received on Wed Apr 21 2004 - 09:49:49 CDT