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.