Home / Adult private sexcam / Updating an oracle sequence via plsql

Updating an oracle sequence via plsql drake rihanna dating again

The issue came to light when investigating a report performance issue on an Oracle 11.2.0.4 non-RAC database.

updating an oracle sequence via plsql-63updating an oracle sequence via plsql-33

From the developer’s viewpoint the report was essentially just executing a single query.

Wait Total Waited ---------------------------------------- Waited ---------- ------------ latch free 1 0.00 0.00 ******************************************************************************** SQL ID: 4m7m0t6fjcs5x Plan Hash: 1935744642 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 250000 71.81 81.15 0 250003 507165 250000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 250000 71.81 81.15 0 250003 507165 250000 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Elapsed times include waiting on following events: Event waited on Times Max.

Wait Total Waited ---------------------------------------- Waited ---------- ------------ Disk file operations I/O 1 0.00 0.00 log file switch (checkpoint incomplete) 1 0.19 0.19 log file switch completion 4 0.20 0.75 ******************************************************************************** So clearly we can see a lot of additional overhead when performing a SQL trace of the many calls to the sequence NEXTVAL function.

SELECTing a sequence value using the NEXTVAL function is supposed to be a fairly lightweight process.

The sequence’s last value is stored in memory and a certain definable number of values are pre-fetched and cached in memory (default is CACHE=20). The resulting SQL_ID and statement for this recursive SQL is: In our case it was the #1 top statement in terms of cumulative CPU.

722 comments

  1. P_seq_name '.nextval into 1 from dual; end;'; execute immediate v_sqlstr using out v_desired_val; v_sqlstr = 'alter sequence ' p_seq_owner '.' p_seq_name case when v_save_cache 1 then ' cache ' to_charv_save_cache else ' nocache' end ' increment by ' to_charv_last_increment; execute immediate.

Leave a Reply

Your email address will not be published. Required fields are marked *

*