DA Morgan wrote:
> Serge Rielau wrote:
>
>> DA Morgan wrote:
>>
>>> Serge Rielau wrote:
>>>
>>>> Sidenote: SEQUENCE is "dirty read" by definition and it is quite
>>>> popular in Oracle :-)
>>>>
>>>> Cheers
>>>> Serge
>>>
>>>
>>>
>>>
>>> By whose definition? Certainly none I have ever heard.
>>>
>> The freedom of double-quotes Daniel. Open your mind:
>> If you open two connections to Oracle and perform a few NEXTVALs in
>> each without cout committing. Then you roll one connections
>> transaction back the values are "lost". But the second connection was
>> very well affected by the lost values: "Dirty read".
>> SEQUENCEs don't no squat about transactions and isolation levels.
>> Note that I don't blame anyone. Sequences do exactly what they are
>> designed to do, just like a regular dirty read (no double quotes) does
>> exactly what it's designed to do.
>>
>> Cheers
>> Serge
>
>
> I can't wrap my mind around your thought that sequence NEXTVAL in any
> way relates to a dirty read. Here's why:
>
> CREATE TABLE mytable (
> mycol NUMBER);
>
> CREATE TABLE t (
> seqno NUMBER);
>
> INSERT INTO t VALUES (1);
> COMMIT;
>
> INSERT INTO mytable
> SELECT seqno
> FROM t;
>
> ROLLBACK;
>
> UPDATE t
> SET seqno = seqno+1;
>
> COMMIT;
>
> Do you see a dirty read? I don't. The fact that a transaction didn't
> take place but the sequence number was lost is not a dirty read. So
> I just don't get your thinking.
>
> Note: No double quotes.
That's OK, you don't have to.

"PK" <pk26au@yahoo.com> wrote in message
news:667bf0c6.0412150133.7e4ce123@posting.google.c om...
> Hi,
>
> As you know Informix offers 'set isolation to dirty read' - a facility
> to read dirty buffers. I believe DB2 UDB too offers it but not Oracle.
>
> What is the neccessity or justification for an RDBMS to offer such a
> feature and do applications really need it ? I was told by an Oracle
> guy that Informix and DB2 are 'forced' to offer this because of their
> architecture and this is not a 'feature' as such ! He says Oracle can
> offer it in a 'jiffy' by pointing to its undo tablespace (where before
> images of a buffer are kept before modifications) but they will not as
> this is not justified !
>
> I myself (worked with Informix for 9 yrs but now into Oracle for past
> 1 yr ) feel its quite cool. However I would like to get expert
> technical opinion. I don't intend to start a flame at all !
>
> Many thanks
> Prashant

Oracle could duplicate dirty read by showing information from the before
image buffer????? Informix dirty read returns information based on a
presumptive commit of open transactions, not information at a point in time
preceding open transactions. I'm sure the Oracle guy your talking to knows
Oracle, but he probably doesn't know Informix terms and functionality. I
don't know what read options Oracle offers, but I would certainly rephrase
the question(without using Informix read option terminology) when asking an
Oracle expert.

As for neccessity or justification...I would agree with Obnoxio's example.
Dirty read is a good option any time the user understands they are looking
at a moving target, but wants to see the most recent information possible.
Given that over 99.99% of my transactions are committed and not rolled back,
I like seeing the open transactions included instead of excluded. 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.

As for not wanting to start a flame war...Your posting on an Informix
message board and your using exclamations after every Oracle viewpoint
mentioned...seems like your looking for a flame war to me.

Dave Griffen

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.

Each product has its way, or workaround, for accomplishing just about
any required task.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----

"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