Re: select rowid help for oracle 9.2.0.2.0

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