Re: selecting rowid slows down query

"Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> wrote in message news:b5qccp$2c9rou$1_at_ID-82536.news.dfncis.de...
> Hello
>
>
> Oracle 9i, Windows and Solaris.
>
> If I do a repeated full table scan and query that table's rowid, each
> query is slower than the preceding:

Not in my case (9.2.0.3 on Solaris):
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Mar 25 22:04:53 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: ********
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

SQL> create table slow_rowid as
  2 select
  3 a.object_name || b.object_name foo, rownum bar   4 from
  5 all_objects a cross join all_objects b   6 where
  7 rownum <= 100000;

Table created.

SQL> create procedure p_slow_rowid as
  2 begin
  3 for r in (select rowid, foo, bar from slow_rowid) loop   4 null;
  5 end loop;
  6 end;
  7 /

Procedure created.

SQL> set timing on
SQL> exec p_slow_rowid

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.28
SQL> exec p_slow_rowid

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.23
SQL> exec p_slow_rowid

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.21
SQL> exec p_slow_rowid

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.18

In your case ... try setting event 10046 level 8 and see what is it waiting on? If you see this problem persistently, contact oracle support for their views on this.

Anurag Received on Tue Mar 25 2003 - 21:10:15 CST