Re: Isolation levels in Informix vs Oracle

"DA Morgan" <damorgan@x.washington.edu> wrote in message news:41c8c7d3$1_3@127.0.0.1... > Dave Griffen wrote: > > > One of > > the things I use it for is to check the progress of batch jobs. If I know a > > program is going to insert 100,000 rows into a table, I can use dirty read > > to see how many rows it has inserted so far and can give a good estimate of > > when it will finish. If you are only allowed to see committed data, you > > wouldn't have that monitoring option. > > In Oracle one would use the DBMS_APPLICATION_INFO built-in package as it > not only tells you what percentage of a batch is done it uses the > transaction rate to an estimate of the completion time. The information > is available via OEM and by querying v$session_longops. > Sounds like a good substitute for gauging percent completion of a current statement. But, the DBMS itself will only be able to estimate completion of statements which it currently knows about. If the 100,000 records are being inserted via 1000 different statements, I doubt this would give me a good estimation of program completion time midstream. Does DBMS_APPLICATION_INFO show an insert record count or anything else which would help a user or DBA make their own estimation in such a case? Does Oracle offer any access method which gives direct visibility to uncommitted data? Dave Griffen

Dave Griffen wrote: > "DA Morgan" <damorgan@x.washington.edu> wrote in message > news:41c8c7d3$1_3@127.0.0.1... > >>Dave Griffen wrote: >> >> >>>One of >>>the things I use it for is to check the progress of batch jobs. If I > > know a > >>>program is going to insert 100,000 rows into a table, I can use dirty > > read > >>>to see how many rows it has inserted so far and can give a good estimate > > of > >>>when it will finish. If you are only allowed to see committed data, you >>>wouldn't have that monitoring option. >> >>In Oracle one would use the DBMS_APPLICATION_INFO built-in package as it >>not only tells you what percentage of a batch is done it uses the >>transaction rate to an estimate of the completion time. The information >>is available via OEM and by querying v$session_longops. >> > > > Sounds like a good substitute for gauging percent completion of a current > statement. But, the DBMS itself will only be able to estimate completion of > statements which it currently knows about. If the 100,000 records are being > inserted via 1000 different statements, I doubt this would give me a good > estimation of program completion time midstream. Does DBMS_APPLICATION_INFO > show an insert record count or anything else which would help a user or DBA > make their own estimation in such a case? Does Oracle offer any access > method which gives direct visibility to uncommitted data? > > Dave Griffen Actually it can guage completion on statements it is just seeing for the very first time. It evaluates CPU utilization, i/o, and other factors to create the initial estimate, based on system load, and then refines the estimate over the time the process runs. From experience I find that it is generally in the ballpark at the beginning and by 10% of the way through is accurate to within a very small percentage. What is nice is that as other jobs utilize resources it refines its estimate taking them into account. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

Dave Griffen wrote: > Does Oracle offer any access > method which gives direct visibility to uncommitted data? From within the same session (connection to the database), you can see uncommitted data. Outside the session, no. Is it actually good practice to do a lot of seperate DMLs (100 ?) affecting 100K rows in Informix without issuing a commit ? Does Informix have the concept of a save point (named point within a transaction you can later rollback to if required) ? FYI - the % complete estimate Daniel is referring to is the one calculated automatically. If you wanted to you could actually instrument your batch using a call to DBMS_APPLICATION_INFO at the beginning and end of your 100 DMLs, and get an overall estimate that was the sum off all the individual DMLs. Or alternatively feed the package the % complete figures yourself using the same package call during the logical operations (based, maybe, on a count of rows within a table, which you can of course query from the uncommitted data because you are now within the same session) FFYI - Oracle's isolation level will also stop you from seeing _commmited_ data was well - for instance, if the data had been changed and committed after the query was started

Mark Townsend wrote: > Dave Griffen wrote: > >> Does Oracle offer any access >> method which gives direct visibility to uncommitted data? > > > From within the same session (connection to the database), you can see > uncommitted data. Of course. >Outside the session, no. I think that was the question. > Is it actually good practice to do a lot of seperate DMLs (100 ?) > affecting 100K rows in Informix without issuing a commit ? Does Informix > have the concept of a save point (named point within a transaction you > can later rollback to if required) ? save points (or nested transactions) are a different animal. They are simply a backup of state within a session. (a level in between statement and transaction atomicity). Releasing a save point should have no semantic impact on a concurrent session. In the end of the day the transaction (or outermost transation in nested transaction lingo) has the final word. Cheers Serge