Oracle programmer's blog

I support this blog for my own fun. It is dedicated to the Oracle programming aspects I believe worth to publish and keep in memory.

Blog Archive

Wednesday, January 30, 2008

NEXTVAL and CURRVAL in Oracle 11G

Oracle 11G documentation states what the direct access to NEXTVAL and CURRVAL improves the performance impact. But seems this change is a feature to minimize coder's work, no more.
The trace results are below:

declare
x int;
begin
for i in 1..100000 loop
x := sq_test.nextval;
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 12.76 13.69 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 12.76 13.69 0 0 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 20

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

SQL ID : 3uvw0xca08wut
Select SQ_TEST.NEXTVAL
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.08 0 0 0 0
Execute 99999 3.64 3.59 0 0 0 0
Fetch 99999 2.51 2.61 0 0 0 99999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 199999 6.17 6.29 0 0 0 99999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWSParsing user id: 20 (recursive depth: 1)


It looks like x := sq_test.nextval transforms implicitly into select sq_test.nextval into x from dual. :)

No comments: