Cool 10G performance improvement

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Sat, 07 Feb 2004 23:14:04 GMT
Message-ID: <0jeVb.114307$U%5.591694@attbi_s03>

Saw this on asktom.oracle.com(very cool performance improvement, easy to use)

Reviewer: Zoran Martic from Dublin, Ireland

Hi Tom,

Now I found why is good sometimes folowing your path.

You said using constructs like this is the best and very easy to use:

for i in (select * from table_x) loop

In 10g Oracle finally optimizes this thing to use some sort of prefetching and
it went 10x faster in my simple test then in 9i or 8i. I was thinking why and how, but basically statistics showed me that on 8i and
9i, both explicit and implicit cursors are doing 1 db block read per one fetch
(disaster that Oracle is doing this when you have in OCI prefetching from XX release of Oracle). In 10g they are finally optimizing fetches but only for implicit cursors :)

That means who ever coded for loops with Select inside will benefit immediately
with upgrade to 10g.

In 9i you need to opt to BULK COLLECT to be fast as 10g normal for i in (select
....

Again, explicit cursors are working bad as in 8i, 9i story. That is a shame for Oracle.
No reason that new smart optimizer cannot find out that you are going to loop in
both cases: implicit or explicit.

Did not put tests and results because they are too obvious (10 lines of code).
Just loop through some big table and check that you are not hitting funny disks
to make your results pointless.

In any case I wrote a lot of code your way with putting SQL inside the for loop
and I am happy now, because upgrade to 10g will automatically benefit me :)

It is very strange that I realize now, after many years that this thing (looping
through cursors) in PL/SQL was that bad. Without prefetching (I used from Oracle
7.3) in PL/SQL in 8i, 9i !!!!!

Regards,
Zoran

      Followup:
oh, that is cool -- I did not even realize that was happening :)

It is trivial to show, just:

begin

   for x in ( select * from all_objects where rownum < 10000)    loop
    null;
   end loop;
end;

and in 9i, you'll see:

select *
from
 all_objects where rownum < 10000

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.03 0.27 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 10000 3.40 3.78 11 81153 0 9999
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 10002 3.44 4.05 11 81153 0 9999

and in 10g, you'll see:

SELECT *
FROM
 ALL_OBJECTS WHERE ROWNUM < 10000

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.10 0.24 0 121 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 100 1.13 1.18 0 15756 0 9999
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 102 1.24 1.42 0 15877 0 9999

they are using a 100 (my favorite) row arraysize....

:)

-- 
Jim Kennedy

It is unnecessary to repeat the same old mistake when you can make so many
interesting new ones. - Bertrand Russell

Received on Sat Feb 07 2004 - 17:14:04 CST