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. :)
Wednesday, January 30, 2008
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment