Re: Currval and buffer gets

The 5 buffer gets are 4 CURRENT on the
segment header block and one consistent
on the data block. It's a (relatively minor) bug in the internal code for scans.

This changes in 9.0.1 to 2 Consistent on the segment header and one consistent
on the data block in version 9.

The large number of selects suggests
(though not it's not always the case) that you have some pl/sql code which is not
using sequences to best effect.

Look for code like:

select xxx.nextval into m_var from dual; insert into parent table values(

    m_var, etc...
);

select xxx.currval into m_var from dual; insert into child table values(

    m_var, ....
);

If this sort of thing is happening, look for the opportunity to re-write it using

declare

    m_var number;
begin

    insert into parent values (

        xxx.nextval, etc.
    )
    returning seq_col into m_var;

end;

The option for inserting the NEXTVAL
directly, and the RETURNING clause
to find out what the inserted value was
can reduce calls to SQL from PL/SQL
quite dramatically.

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 21 April 2002 17:59

|I did two statspack snapshots, one hour and forty minutes apart.
|Then I generated a report and loaded it into oraperf.com.
|In the report I saw that the two SQL statements that where executed
the most
|times where:
|
|Select xxxx.currval from dual;
|
|Select xxxx.nextval from dual;.
|
|Each one was executed about 90,000 times with 5 buffer gets per
execution.
|The net result was about 950,000 buffer get for nextval and currval.
|
|My question is:
|Why should there be about 5 buffer gets per execution?
|
|Yechiel Adar
|Mehish

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).