Ofline
92809 days on xHamster
2421M profile views
66366K subscribers
28419 comments left

Updating an oracle sequence via plsql

(Note, no sequence values are lost when the database is shutdown cleanly.) However in this case, since the sequence is just being used as a surrogate key this was not a problem for the application.

Changing the sequences CACHE setting to 100 completely eliminated the problem, increased the overall report performance, and removed SQL_ID 4m7m0t6fjcs5x from the list of top SQL in AWR reports.

Of course the overhead is due to recursive SQL and the synchronous write of the trace file.

It just wasn’t obvious that a simple query could generate that much recursive DML and trace data.

The question was then: could this really be due to the overhead of tracing or something else?

And should all of those recursive SQL update statements materialize as CPU time in the AWR reports?

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

The next question is what is the effect of the CACHE setting for the sequence as well as the different between a LEVEL 8 and LEVEL 12 trace.

Using a similar PL/SQL test block but with only 100,000 executions on a lab database showed the following results measuring CPU time (in seconds): Hence we can see that with even an extremely high CACHE setting for the sequence, the 10046 trace adds roughly 300% to 400% overhead for this one particular statement.

The reality is that it was slightly more complicated than that as the top level query accessed a view.

Still the view was not overly complex and hence the developer believed that the report was query intensive.

The report was instrumented with: The tracing made the report run over six times longer.

Please or register to post comments
If spammers comment on your content, only you can see and manage such comments Delete all
Apr 2, 2013. Do more with Oracle PL/SQL! Learn about Oracle bulk binds, PL/SQL collections, and bulk loading with FORALL inserts, updates, and merges. Before doing the example we will insert more rows into the CUSTOMERS table using a new sequence to provide CUST_IDs and update every 7th row in. 
29-Jan-2019 17:04
Reply
As discussed in the documentation, a sequence in Oracle prevents duplicate values from being created simultaneously because multiple users are effectively forced to “take turns” before each sequential item. For the purposes of creating a unique primary key for a new table, first we must CREATE the table we'll be using. 
29-Jan-2019 17:06
Reply
Jan 18, 2009. Sequence created. SQL DECLARE 2 l_new_cd cds%ROWTYPE; 3 BEGIN 4 l_new_= cds_seq.nextval; 5 l_new_cd.album_name = 'The Future';. 7 UPDATE cds 8 SET ROW = l_updated_cd 9 WHERE album_name = 'The Future'; 10* END; SQL /. PL/SQL procedure successfully completed. 
29-Jan-2019 17:09
Reply
Oct 23, 2016. We are using sequences so far in primary database so that users can generate their unique integers, Even in 11gR2 Active Data Guard there are many. After inserting rows, when updating the column QID with the session sequence next value, in case of global temporary tables the series started from 21. 
29-Jan-2019 17:13
Reply
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. 
29-Jan-2019 17:15
Reply
Apr 27, 2005. Oracle uses PL/SQL, while SQL Server uses Transact SQL. This chapter focuses on application code that may need to be modified for use with SQL Server because of these extensions. This chapter should be used in conjunction with Chapters 12 through 17. These chapters provide information for. 
29-Jan-2019 17:17
Reply
Jun 12, 2012. MERGE was introduced in Oracle 9i. Since then it has gone through a revamp to accommodate new features, most importantly MERGE in 10G supports insert-only and update-only operations. Its 9i predecessor required both WHEN MATCHED and WHEN NOT MATCHED to be present in the statement. 
29-Jan-2019 17:21
Reply
Overview of SQL Support in PL/SQL. Managing Cursors in PL/SQL. Querying Data with PL/SQL. Using Subqueries. Using Cursor Variables REF CURSORs. Using. To manipulate Oracle data you can include DML operations, such as INSERT, UPDATE, and DELETE statements, directly in PL/SQL programs, without any. 
29-Jan-2019 17:25
Reply
GitHub is where people build software. More than 27 million people use GitHub to discover, fork, and contribute to over 80 million projects. 
29-Jan-2019 17:30
Reply

Updating an oracle sequence via plsql introduction

Updating an oracle sequence via plsql

Recent posts

30-Jan-2019 00:11
30-Jan-2019 02:05
30-Jan-2019 09:59
30-Jan-2019 19:36