Tom, the author, in Part-1 of the article, under #3"Shared SQL Statement",

</code> http://www.dbasupport.com/oracle/ora9i/performance/tuning-1.shtml#truncate <code>

stated
"Unfortunately, the cache buffering is applied only to simple tables; multiple table queries and joins are never cached."

Is this true ?

also he said, which I find odd...

"By default, ORACLE uses CHOOSE optimizer mode. To reduce the potential for unplanned full table scans, you should avoid using the CHOOSE option; either use the RBO or the CBO throughout your database."

Please clear the smoke for us
Thanks

:-) Thanx Tom for making sense out of that nonesense article and correcting the incorrect information. Those who come to your web-site are truly the lucky ones. You ARE AWESOME! :-) Please keep up the good work, and helping out the otherwise lost souls in this vast realm of database.

Hi, Mr.Toracle,

It is so surprise to read that frequent commit does not acturally enhance the performance of db such as locking,redo log...etc.

So, what is your recommendation towards commit frequency problem when designing the application?

Tom, thanks for making it all clear. At the same time I will suggest that DbaSupport.com must verify the content of articles before publishing them, otherwise, this site will lose its credibility.

Your description makes total sense..
However; there's one issue that would like to have your thoughts on ...

What do you propose on a large transaction running out of rollback segment space ? It seems like the only way around is to have some level of commit frequency ?

Thanks.

</code> http://www.dbasupport.com/oracle/ora9i/performance/tuning-9.shtml <code>

"49.GROUP BY and Predicate Clauses" completely misses the point of the HAVING clause, and is trivial.
"51.Use Explicit Cursors" seems misguided
"53.Table and Index Splitting" is downright wrong

Those articles are riddled with error and urban legend, it seems

Tom, Can you please post those commets (about the whole article) so it is beneficial to everyone. Thanks for the priceless service you are doing to the Oracle community!

Tom,
Saw the message posted on your site's front page and thus went through the threads of discussion.
On my applications, both PL/SQL and Pro*C, we do a COMMIT after every 1000 records and changes status flag for the processed records. Thus next time when the program runs (if previous run was aborted due to error), it does not pick up the processed records.
Thats the scenario. Do you feel that what we are doing is BAD and that COMMIT should be only at the end of the process. The number of records to be processed are in millions.

regards

Hey Tom,

Mission Accomplished!! - those pages are gone !!

As one who has experienced the dreaded ora-01555 "snapshot
too old" error message when updating large number of rows..
I stopped the practice of committing 'x' number of records
during the Loop-fetch cycle.

I know many say that if Rollback Segments are sized adequately and there are enough of them then you should
be able to process large numbers of rows(I'm speaking of
millions here...) with no problems.

What has worked for us is I have created one huge massive
Rollback Segment that I keep offline. When I know I have
a large update to run after business hours..I will take
the regular RS offline and switch on the massive RS and
run my script,query or procedure..it works everytime and
I never have a problem completing the task. When the entire
process is done...I COMMIT;

Why using frequent commits? Just tear-off the transaction processing capabilities from oracle! No transactions - no overhead. Ha. Ha. Ha.

One of the 3 basics of an transaction is _completeness_. Synonym of _integrity_

Tom,
Here is the situation, I'm doing an insert/select statement that would insert about 1.5 million records and I keep on hitting cannot extend rollback segment, but our dba told me to break my statement into a small records and do a commit on each statement. Below is what he says "
If you are really inserting 1.5 million records in one transaction, that is not recommended. If all these records are going to the same table, then each record represents a transaction. The concept of a transaction is that, related information in "different" tables should be managed as a group. When a set of related records has been inserted then a "commit" should occur to make the inserts permanent." Can you guide me here? Thank you very much and more power.

Tom,

In the rollback section of the 9iR2 admin guide (assuming you are still using rollback), it says:

Approximate Rollback Segment Sizes
with regards to snapshot too old error:

This error occurs because the rollback entries needed for read consistency are overwritten as other update entries wrap around the rollback segment. Consider this issue when designing an application's transactions, and make them short atomic units of work so that you can avoid this problem.
-----
Doesn't this "short atomic unit of work" hint at the commit frequently mentally? Should this instead say, "when the transaction is done".. which isn't unclear at all.

That same section discusses the "use different rbs for different types of transactions" scenario.

When database systems applications concurrently issue a mix of very short and very long transactions, performance can be optimized if transactions are explicitly assigned to a rollback segment based on the transaction/rollback segment size. You can minimize dynamic extent allocation and truncation for rollback segments. This is not required for most systems and is intended for extremely large or small transactions.

To optimize performance when issuing a mix of extremely small and large transactions, make a number of rollback segments of appropriate size for each type of transaction (such as small, medium, and large). Most rollback segments should correspond to the typical transactions, with a fewer number of rollback segments for the atypical transactions. Then set OPTIMAL for each such rollback segment so that the rollback segment returns to its intended size if it has to grow.

You should tell users about the different sets of rollback segments that correspond to the different types of transactions.
**Often, it is not beneficial to assign a transaction explicitly to a specific rollback segment.**

However, you can assign an atypical transaction to an appropriate rollback segment created for such transactions. For example, you can assign a transaction that contains a large batch job to a large rollback segment.

Tom,
I think, when doing bulk updates, the required rollback behaviour is also important in defining "Transaction".
different scenarios:
1)insert into A select * from B; (Either do the whole thing or rollback completely)
2)for c in select * from B loop
Begin insert into a (1,2..) values (c.1,c.2..);
exception when others then null
end;--(continue with inserting other records..)
3)We cal also use Bulk insert if thats the desired rollback behaviour.
What i wanted to say is that we rollback behaviour also affects how we define transaction.

Tom,

9.2 Utilities guide on Import says

"Specifying COMMIT=Y prevents rollback segments from growing inordinantly and improves performance of large imports"...

Ok, rollback/undo doesn't grow inordinality with these frequent commits, but "improve performance"? Must be a doc bug?

Tom,

This article cleared many of our myths regarding frequent commits.

Mine too .

Tom,

Why does Frequent commit use MORE redo?

I can see the Redo will have AFTER image and BEFORE image (from undo) from an update statement for example, but the volume of data would probably be the same if I commit once per row or after a transaction?

I am assuming volume is same because its the same AFTER and BEFORE images independent of frequency of commit.

I can understand clearly about extra CPU work and extra consistent gets that could be consumed from frequent commits but not why extra REDO. I think you have a clear test case in your first book.

Thanks

Ravi

drop table t;

create table t ( x int );

set termout off
set heading off
set feedback off
spool x.sql
select 'insert into t values ( 1 );' from (select level l from dual connect by level <= 1000 );
spool off
set heading on
set feedback on
set termout on

@mystat "redo size"
set autocommit off
set termout off
@x.sql
set termout on
commit;
@mystat2

truncate table t;

@mystat "redo size"
set autocommit on
set termout off
@x.sql
set termout on
@mystat2
set autocommit off

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set termout off
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "redo size"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size                   11216

1 row selected.

ops$tkyte%ORA10GR2> set autocommit off
ops$tkyte%ORA10GR2> set termout off
ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                  256956          245,740

1 row selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> truncate table t;

Table truncated.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "redo size"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size                  262324

1 row selected.

ops$tkyte%ORA10GR2> set autocommit on
ops$tkyte%ORA10GR2> set termout off
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                  803072          540,748

1 row selected.

ops$tkyte%ORA10GR2> set autocommit off

After reading this thread and recreating the demos in Chapter 9 of Expert Oracle Database Architecture, I am a convert to the Frequent-Commits-Are-Bad school.

But...

Recently a client asked me to investigate a deadlock issue. The root of the problem was a piece of code that looped over a (large, but not huge) number of inserts into a table containing several bitmap indexes and then committed at the bottom of the loop. I advised the client to commit more frequently, to lessen the likelihood of deadlock.

So I'm wondering:

1. Am I correct to believe that the longer the transaction on the bitmap-indexed table stayed open, the greater the likelihood of the session deadlocking itself?

2. Would it have been better advice to insist that bitmap indexes were inappropriate for this table, and to advise replacing them with btrees?
(Seems obvious the more I think about it, but maybe I'm just looking for a dope-slap from Tom this morning).

Fred, you're on the mark with the 2nd point - bitmap indexes are less than ideal when dealing with the typical OLTP table that is CRUD concurrently.

Never mind the locking issue (remember that a single bitmap index entry covers a range of rows), there is the growth issue to consider. Frequent updates makes a bitmap index looks bigger and uglier than Jabba the Hut.

Ecellent article Tom. It only proves i have so much more to learn.

I first bumped into this issue with DELETEs. I would just like to ask for verification here.

If a TABLE has a three million records in it, and i must DELETE one million records based on an INDEX, it has been my experience that DELETEing ten thousand records at a time (with a COMMIT) seems to go much faster than DELETEing them all at once.

I thought (incorrectly) that it took so long because it had to store the redo with became geometrically harder with each new record.

Is the DELETE normally faster in chunks for the same reason, that the redo logs were not big enough?

So, my boss asks me to add some code to a procedure I've never touched before. I go into it and take a look. "13 commits in one back end procedure" My mouth dropped, I asked why. The other developers response, "Well, next time you run it and fix the errors, that will run." My response, "Do you even have concept of what a transaction is?"

Not a question, just a gripe. This makes me want to rewrite our entire EDI system!!!

-Scott

I'm using PL/SQL to load Multimedia LOBs into the database over the Web. I have no idea ahead of time if a user will load one file or 100 files. I've found that PL/SQL is the most consistently accurate way to load files and it works like a charm in a single-user environment. It works good when 5 users a simultaneously uploading also. But when I start getting 20+ users all uploading at the same time, the COMMIT frequency goes through the roof. This is because each LOB insert does a "SELECT ...FOR UPDATE" and has to issue a commit before running again for the next file.

IS there a "BULK COLLECT" type PL/SQL trick to use with LOBs (besides SQL*Loader) or am I stuck with committing after each LOB insert?

Thanks Tom for your reply.

I noticed several wait events over the past few days on "commit" during a heavy load period (about 12 users simultaneously uploading files). I/O is a problem as well, like you said, and I'll be looking into that also.

I think I can address the commit Wait Events by taking out the explicit commit; in my procedure and just call it several times per session. I'll hit the test machine this afternoon and give it a whirl.

The way I'm doing it now just seems to go against everything this thread has taught me so far - and issuing a commit; after each LOB insert doesn't seem scalable or performant at all. I'm hoping to improve the process if possible.

I also read in your Expert Database Architecture book, on page 552 (last paragraph under Cache Clause heading), "...you can make excellent use of the Keep and Recycle Pool here..." (in reference to enabling caching of LOBs during a large initial BLOB load)

Can you please elaborate a bit to show how (and if) the RECYCLE POOL could be utilized to write LOBs to the cache first instead of directly to disk? Is it just in the LOB storage clause, specifying BUFFER_POOL RECYCLE. Or can we only use the DEFAULT POOL for writing to during a load?

Thanks Tom,
Zoe

Thanks Tom.

I have observed the following from the OEM Database Control Console:

 Details 
 Jan 11, 2008 2:01:06 AM Metrics "Database Time Spent Waiting (%)" is at 97.74354 for event class "Commit" - 
 Dec 31, 2007 8:01:08 PM Metrics "Database Time Spent Waiting (%)" is at 94.14906 for event class "Commit" - 
 Dec 23, 2007 11:01:06 PM Metrics "Database Time Spent Waiting (%)" is at 93.8445 for event class "Commit" - 
 Dec 24, 2007 11:01:29 PM Metrics "Database Time Spent Waiting (%)" is at 93.39415 for event class "Commit" - 
 Jan 11, 2008 9:01:12 AM Metrics "Database Time Spent Waiting (%)" is at 93.37386 for event class "Commit" - 
 Jan 8, 2008 5:02:00 AM Metrics "Database Time Spent Waiting (%)" is at 92.55847 for event class "Commit" - 
 Jan 19, 2008 4:01:11 AM Metrics "Database Time Spent Waiting (%)" is at 91.88583 for event class "Commit" - 
 Dec 24, 2007 2:01:20 PM Metrics "Database Time Spent Waiting (%)" is at 91.44676 for event class "Commit" - 

... and drops by about 1% all the way down to 

 Dec 25, 2007 7:08:49 PM Metrics "Database Time Spent Waiting (%)" is at .18437 for event class "Commit" 

I've done some tests and whenever I do a multi-user loading run (12+ users continuously loading files) for about 5-7 minutes, I notice the Waiting % usually hits between 80% and 97%. Of course, my I/O Waits are horrible too because I'm in RAID5 for everything and using NOCACHE. Maybe this is what's causing the Wait on Commit. I'll look deeper into that.

I suppose I could reset the threshold to make this go away; but not sure if that'd be sweeping it under the carpet.

Thanks again for your valuable input,

Zoe

Hey Tom:

    I am reading expert one on one recently. There a samll test on the book about commit frequently and commit once. the time for frequent commit is 21 hsecs. the time for commit once is 7hsecs. I set up my own test as following, but get the similar time. am I doing something wrong?

set timing on
spool commit_frequent.log
truncate table test;

insert into test(da_id, source_code)
select da_id, source_code
from mydata;

commit;

DECLARE
        v_new_da_id             number := 5;
        v_err_msg               varchar2(4000);
        v_err_ctr               number := 0;
        v_ctr                   number := 0;
        l_start                 number default dbms_utility.get_time;
        CURSOR cur IS
        select * from test;

BEGIN
        FOR x in cur LOOP
        BEGIN
        v_new_da_id := 0;
        v_err_msg := null;
        v_err_ctr := 0;
        v_ctr := 0;

       if x.source_code = 'BOOK'
        then
          v_new_da_id := 1;
        else
          v_new_da_id  := 0;
        end if;


        update test
               set da_id = v_new_da_id
        where  da_id = x.da_id;

        commit;

        EXCEPTION
        when others then
        v_err_msg := sqlerrm;
        v_err_ctr := v_err_ctr + 1;
        dbms_output.put_line('ERROR ON: '||x.da_id||' ERR MSG => '||v_err_msg);
        
        commit;

        END;
        v_ctr := v_ctr + 1;
        END LOOP;
dbms_output.put_line(dbms_utility.get_time-l_start || 'hsecs ');
END;
/

***********************************************
SQL> @commit_frequent.sql > commit_frequent.log

Table truncated.

Elapsed: 00:00:00.09

10000 rows created.

Elapsed: 00:00:00.03

Commit complete.

Elapsed: 00:00:00.04
1525hsecs

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.27
*****************************

set timing on
spool commit_once.log
truncate table test;

insert into test(da_id, source_code)
select da_id, source_code
from mydata;

commit;

DECLARE
        v_new_da_id             number := 5;
        v_err_msg               varchar2(4000);
        v_err_ctr               number := 0;
        v_ctr                   number := 0;
        l_start                 number default dbms_utility.get_time;
        CURSOR cur IS
        select * from test;

BEGIN
        FOR x in cur LOOP
        BEGIN
        v_new_da_id := 0;
        v_err_msg := null;
        v_err_ctr := 0;
        v_ctr := 0;

       if x.source_code = 'BOOK'
        then
          v_new_da_id := 1;
        else
          v_new_da_id  := 0;
        end if;


        update test
               set da_id = v_new_da_id
        where  da_id = x.da_id;

        

        EXCEPTION
        when others then
        v_err_msg := sqlerrm;
        v_err_ctr := v_err_ctr + 1;
        dbms_output.put_line('ERROR ON: '||x.da_id||' ERR MSG => '||v_err_msg);
        
        commit;

        END;
        v_ctr := v_ctr + 1;
        END LOOP;
dbms_output.put_line(dbms_utility.get_time-l_start || 'hsecs ');

comit;

END;
/
******************************************
SQL>  @commit_once.sql > commit_once.log

Table truncated.

Elapsed: 00:00:00.10

10000 rows created.

Elapsed: 00:00:00.03

Commit complete.

Elapsed: 00:00:00.04
1469hsecs

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.71
********************************************

ops$tkyte%ORA10GR2> begin
  2          for x in ( select rowid rid from t )
  3          loop
  4                  update t set object_name = reverse(object_name) where rowid = x.rid;
  5          end loop;
  6          commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.67
ops$tkyte%ORA10GR2> begin
  2          for x in ( select rowid rid from t )
  3          loop
  4                  update t set object_name = reverse(object_name) where rowid = x.rid;
  5                  commit work write immediate wait;
  6          end loop;
  7  end;
  8  /


PL/SQL procedure successfully completed.

Elapsed: 00:03:43.49
ops$tkyte%ORA10GR2>

Tom, thank you very much for your quick response!

I have some questions about ur reply:
(I hate your exception block by the way, I hope you do NOTHING remotely similar to that in real life...)

what does this mean? many of our production scripts are in this format(the format I used). But normally, we run them directly on the server. Is this good or not? If this is not appropriate, what kind of format we should use. We run scripts to load data, and update the existing data. And what does the remote mean?

sorry too much question :P. Thank you!

        EXCEPTION
        when others then
        v_err_msg := sqlerrm;
        v_err_ctr := v_err_ctr + 1;
        dbms_output.put_line('ERROR ON: '||x.da_id||' ERR MSG => '||v_err_msg);

"You just continue on...", if you're lucky.

Yesterday, someone ran some PL/SQL from a terminal. It included

EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;

The code failed, but the transaction did not end because the exception was caught and not re-RAISED.

So the transaction is still there, holding on to a bunch of UNDO. And guess what? No one else can update the database because there is no UNDO free!

So because of this coding practice, no one can "continue on".

True story, happening right now.

it is you + r = your, I use "u" for you, so ur for your...
sorry to make you confused.

and since we update many data every day. most of the time, the new data we need to do the update is available, but still miss some of them. so what we do is record the numbers of error we have, if it is big, we will roll all the way back, and reload it after data is ready. if just couple of errors, we will update the data for specific da_id later when that data is ready. there should be another output for number of error. that is the logic.

for out circumstance, do we need to change the script to commit once or leave it along?(as you said before, the plsql has a commit time optimization that pretty much makes it not useful to demonstrate this. )

and thank you, Stew Ashton, I will keep your case in mind.

I once had a intervue 4 a company who gave me this snippet ov code (or somefink similar - it was a long time ago!), and asked me wot was wrong wiv it:

DECLARE
  A := 50;
  B := 10;
  --
BEGIN
  C := A + B
  --
EXCEPTION
  WHEN OTHERS THEN NULL;
END;

There answer was that a semi-colon was missing from da middle line. "Ah-ha. U didn't spot that, did u?", 2 witch I replied, "Well I wood nomally let the compiler find doze kinds of mis-takes, but why have u got a 'WHEN OTHERS THEN NULL' in da expection handler?".
"It captures all the errors", they said.


D'oh! I exclaimed. But I got da job. :-)

Again Tom share's his invaluable knowledge :)

Hi,

we have an app here that is an off the shelf package and we cant change it. it does 1.5m inserts all as individual transactions and commits after each insert. clearly this isnt efficient for all the reason above.
but...
we cant change it and its taking hours.... so what can we do to the database config so that IF this type of thing must happen it happens as fast as possible??

absolutely correct, 100% agree, the developer of this poor code have to fix that.

( by the way, I hope, since Oracle now owns java, that JDBC-default "autocommit on" will disappear soon )

Though, there might be one thing Laurence from UK could do, Oracle has implemented some magic:
asynchronous commit

He could set up a logon trigger which does a
alter session set commit_write='BATCH,NOWAIT'
( or even set this system-wide )

Of course he must be aware that a COMMIT then has much another semantics than before, but probably he wants to do this.

I just did a quick test which showed a performance gain of factor 7:

SQL> !cat /tmp/c.sql 
spool /tmp/t.sql
select 'variable s number' from dual;
select 'variable t number' from dual;
select 'exec :s:=dbms_utility.get_time' from dual;
select q'<insert into temp values(lpad('a', 2000));>'||chr(10)||'commit;'
from dual
connect by level<=1E4;
select 'exec :t:=dbms_utility.get_time' from dual;
spool off

SQL> set pages 0 lines 200 trimspool on feedback off termout off
SQL> sta /tmp/c
SQL> create table temp(x char(2000));
SQL> set echo off serverout on
SQL> sta /tmp/t
SQL> exec dbms_output.put_line(round((:t - :s)/100,2) || ' seconds.')
49.33 seconds.
SQL> drop table temp purge;
SQL> create table temp(x char(2000));
SQL> alter session set commit_write='BATCH,NOWAIT';
SQL> sta /tmp/t
SQL> exec dbms_output.put_line(round((:t - :s)/100,2) || ' seconds.')
7.52 seconds.
SQL> 

I repeat: a COMMIT now has another semantics than before, one must be aware of that !

but:
the designers of Oracle decided to not only to support

commit work write batch nowait;

but also

alter session set commit_write='BATCH,NOWAIT';

why did they do this ?
I think, in order to help people like Laurence from UK

As long as you know it'll change the way the program behaves in the event of a failure, it's ok, isn't it ?

If you use this approach, it's your responsibility then to decide if you can live with this different behaviour or not.

loop
   read up to 1,000 records
   insert them
   insert into log table ('processed records ' X ' thru ' X+records-read-1 )
   commit batch,nowait
   X = X+records read
   exit with EOF
end loop

a developper who codes the application does not need

alter session set commit_write='BATCH,NOWAIT';

the only thing he needs to support bulk loading is

commit work write batch nowait;

but also a parameter is supported to change the default commit behaviour

a) many people trust what they read
b) many people really trust what they read here
c) if I say once "x is OK" then many people will generalize that to "he said X is ok", even if I said "x is ok under these precise, specific, rare conditions: ....."


ok, I agree

1. Should SGA sizing for DEV/TEST instances be similar to PROD instance? DEV/TEST have much lower tranasction volume.

2. Is it normal/healthy or good practice to bounce the instance once per week - it seems that resets all the SGA stats collected by oracle.

I agree that a commit should only be issued when a logical transaction is completed.

However, I am seeing some weird behavior with multi-row updates during a long-running transaction.

I have a fairly simple update statement (UPDATE t SET col1 = ..., col2 = ... WHERE col3 = <some value>).

If I run the update statement in SQL Developer or TOAD, it completes in 5-6 seconds (for about 130,000 rows).

When I run the same update statement as part of a longer process where the same table has been inserted to and/or updated before, the update now takes 2-3 minutes.

The weird thing is if I stick a COMMIT in the middle of the process, before this update statement, the time is reduced to around 30 seconds.

Is there any logical explanation for this? Do you have any pointers as to how I can discover why it is taking so much longer inside the PL/SQL program than outside (or inside with the extra commit)?

I'd like to remove the commit from the middle of my logical transaction, but I want it to perform well, too! :-)

Hi Tom

I have statements like those above, an I am not sure just exactly it is tellimg me in this database (10.2.0.5.1) running on windows 2003 64-bit platform, other than it is waiting to commit
Can you assist me on just what these mean and what I should look at to solve the problem ?

Metrics "Database Time Spent Waiting (%)" is at 60.82248 for event class "Commit" -
Nov 8, 2010 10:10:56 AM Metrics "Database Time Spent Waiting (%)" is at 40.01814 for event class "Commit" -
Nov 8, 2010 10:09:56 AM Metrics "Database Time Spent Waiting (%)" is at 54.40708 for event class "Commit" -
Nov 8, 2010 10:05:44 AM Metrics "Database Time Spent Waiting (%)" is at 35.36435 for event class "Commit" -
Nov 8, 2010 9:35:17 AM Metrics "Database Time Spent Waiting (%)" is at 72.99695 for event class "Commit" -
Nov 8, 2010 9:34:14 AM Metrics "Database Time Spent Waiting (%)" is at 35.65332 for event class "Commit" -
Nov 8, 2010 9:16:23 AM Metrics "Database Time Spent Waiting (%)" is at 73.98091 for event class "Commit" -
Nov 8, 2010 9:15:20 AM Metrics "Database Time Spent Waiting (%)" is at 38.06514 for event class "Commit" -
Nov 8, 2010 7:08:22 AM Metrics "Database Time Spent Waiting (%)" is at 78.3981 for event class "Commit" -
Nov 8, 2010 7:07:19 AM Metrics "Database Time Spent Waiting (%)" is at 41.08747 for event class "Commit"


The only thing i see in alert log is back on 11-3:
Wed Nov 03 22:00:49 Pacific Daylight Time 2010
Thread 1 cannot allocate new log, sequence 1084
Checkpoint not complete
Current log# 3 seq# 1083 mem# 0: K:\ORACLE\ORADATA\CDRL\REDO_03A.LOG
Current log# 3 seq# 1083 mem# 1: I:\ORACLE\ORADATA\CDRL\REDO_03B.LOG


Thanks
Jim

Hi Tom,

Hope you are doing great !

I am working on mapping script to migrate data from staging tables into new tables in Oracle 11g.My mapping scripts have INSERT INTO SELECT statments mostly . Please could you let me know how can I do a commit after every 1 million records . I am asking this because , we can have a scenario where Insert into select fails after 1 million 1 record in some case and all inserted data gets rollback . So again we have to start from beginning.

Hi Tom,

I've known for a while our Java application commits way too often and the response from our developers is sadly unsurprising: changing this behavior will require too much work.

What I just noticed in log miner is that in a lot of instances the software begins a transaction with "set transaction read write" then instead of actually doing some DML it just issues a commit. I suppose the good thing is that the transaction is ended.

Is it safe to assume this behavior is just as bad as frequent commits when there is actually some change to commit? If it is just as bad, is there a less costly way to end the transaction? Getting the developers to only start a transaction when there is one to be had will probably be like pulling teeth.

Thanks,

Steve

This may be the answer to a problem that has been confusing us for weeks - 'snapshot too old' etc...
We're testing it now.

Thanks very much for providing this fantastic service.
I bought your Expert Oracle Database Architecture book today so hopefully I won't be mocked on this page in the future :>

Cheers, Tony

Hi Tom,

We have an application doing frequent large number of concurrent commits to the database. Sometimes we see slowness during such bouts of commits. A query which runs within a fraction of a second may take more than 30 seconds to finish at such times. This can continue up to a couple of minutes and the slowness vanishes after the surge in commits dies down. The ASH report indicates wait on log file sync event, commits amounting to tens of times higher than cpu count, during problem period. However this behavior is not consistent. There are some instances of similar high transaction activities when the database response time is not affected even though ASH shows exactly the same kind of waits with commits. The CPU usage is below limit although the run queue length crosses the available cores.

The recommendation from ASH report is to improve I/O performance. We use SAN disks and possible I/O tuning has already been implemented in that domain.
We are on 11g RAC database.

I have following questions/comments.

1. It would be great to know possible reasons on why the slowness is not encountered every time transaction volume and subsequent commits increase by similar amounts.

2. Would it help to start another instance of RAC, considering the I/O would still be happening with the same SAN?

3. Any hints to troubleshoot and nail down the issue would be much appreciated.

Is there a simple way to get the number of commits in the instance in say the last 5 minutes via SQL? I would like to set up a warning in our monitoring suite if it climbes too high.

I can see this working with:

Statspack (would have to be deployed in our shop)
AWR tables (possible license issues)
Log Miner (also would have to be deployed)
Auditing tables (most likely spot I think but would still have to be set up)

So I'm wondering if there are any other sources of this Information.

Thanks for any help you can offer.

create table t (
  x int 
);

select sid, value from v$sesstat s
join   v$statname n
on     n.statistic# = s.statistic#
where  name like 'user commit%'
and    sid = sys_context('USERENV', 'SID');

SID  VALUE  
106  30 

begin
  for i in 1 .. 10 loop
    insert into t values (1);
    commit;
  end loop;
end;
/

select sid, value from v$sesstat s
join   v$statname n
on     n.statistic# = s.statistic#
where  name like 'user commit%'
and    sid = sys_context('USERENV', 'SID');

SID  VALUE  
106  40