RE: Excessive fetching

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Thu, 6 May 2004 15:17:48 -0400
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C033EE2@wgdc02.wgenhq.net>

Mike,=20
Haven't touched occi, but you can optimize the code by using the RETURNING clause:

do a trace on these two and compare, all the snippets do is put 5k worth of data into 100 clobs:

create table demo ( id number, text clob ) lob(text) store as demo_lob=20
(chunk 8k enable storage in row cache pctversion 0);

declare

    c clob;
    pos number :=3D 1;
    amt binary_integer :=3D 5120;
    buf varchar2(5120) :=3D rpad('x',5120,'x'); begin

for i in 1 .. 100=20
loop

        insert into demo values ( i, empty_clob() );
        select text into c from demo where id=3Di for update;
        dbms_lob.write (c,amt,pos,buf);

end loop;
commit;
end;
/

declare

    c clob;
    pos number :=3D 1;
    amt binary_integer :=3D 5120;
    buf varchar2(5120) :=3D rpad('x',5120,'x'); begin

for i in 1 .. 100=20
loop

        insert into demo values ( i, empty_clob() )
        returning text into c;
        dbms_lob.write (c,amt,pos,buf);

end loop;
commit;
end;
/

Since you are inserting, the row is locked anyway, so, you can use the RETURNING clause to get the clob pointer without doing another select.
No clue about those extra fetches, I thought oracle stopped doing that with implicit cursors a long time ago, maybe it's something in the occi? Some fetch or prefetch setting?

regards,

-----Original Message-----

From: Mike Chartier [mailto:mikec_at_compgen.com]=20 Sent: Thursday, May 06, 2004 1:38 PM
To: Oralce List
Subject: Excessive fetching

Hi,

I'm a software engineer working on a product which requires oracle. I know=20
this is a mainly DBA list but my company's DBA turned me onto it when he

could no longer answer my questions. I'm hoping someone here can help.

Since we're using C++ when the product was first designed OCCI didn't=20 exist (in Oracle 8) we used ocicpp. It has now been determined that this

C++ library is incomplete and in general not a great performer (of course=20
our wrappers didn't help either).

Recently a decision was made to tie us more directly to oracle to increase=20
our performance and to take advantage of some other oracle features. We're=20
now going with OCCI on Oracle 9i.

As a proof-of-concept I wrote a little test in OCCI where we could time=20 some of our DB operations and compare them with our current numbers.

One of the queries we're testing in an INSERT of a row:

INSERT INTO my_table (id) VALUES (:1)

I then lock the row to update a CLOB column:

SELECT my_clob FROM my_table WHERE id =3D :1 FOR UPDATE

Now when a run a 10046 level 12 trace on my test program (which does 1000=20
such inserts) I see that it executes the SELECT 1000 times but fetches=20 2000.

I originally thought this is because how CLOBs are stored but in the code=20
we're currently running (which we want to replace) 1000 of these inserts

result in 1000 fetches.

Even with the extra fetch per row our performance numbers are much better=20
than they were but I would like to make then as good as they can be by=20 removing this unnescessary fetch.

Any help you can give me will be greatly appriciated!

-Mike

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu May 06 2004 - 14:16:52 CDT