Hi
so to be absolutely clear
in the same session, you ran an update, commit and select, and the
select returned an earlier value than the committed update?
Things like
ALTER SESSION set ISOLATION_LEVEL = SERIALIZABLE;
will cause a session to NOT see commits from other sessions, but in
Oracle one always sees one updates in ones transactions. ( assuming no
other session makes a change of course )
So are you sure that
1 come other session hasn''t mucked with the value between the commit
and
the select in your session.
2 some DB trigger is doing this perhaps, ie setting some default value?
In my experience with DB''s, triggers are the root of all evil.
Enda
On 15/10/2010 14:36, Gerry Bragg wrote:> A customer is running ZFS version15 on Solaris SPARC 10/08 supporting
> Oracle 10.2.0.3 databases in a dev and production test environment. We
> have come across some cache inconsistencies with one of the Oracle
> databases where fetching a record displays a ''historical
value'' (that
> has been changed and committed many times). This is an isolated
> occurance and is not always consistent. I can''t replicate it to
other
> tables. I''ll also be posting a note to the ZFS discussion list.
>
> Is it possible for a read to bybpass the write cache and fetch from disk
> before the flush of the cache to disk occurs? This is a large system
> that is infrequently busy. The Oracle SGA size is minimized to 1GB per
> instance and we rely more on the ZFS cache, allowing us to fit ?more
> instances? (many of which are cloned snapshots). We?ve been running this
> setup for 2 years. The filesystems are set with compression on,
> blocksize 8k for oracle datafiles, 128k for redologs.
>
> Here are the details of the scenerio:
>
> 1. Update statement re-setting existing value. At this point the
> previous value was actually set to -643 prior to the update. It was
> originally set to 3 before today?s session:
>
> SQL> update [name deleted] set status_cd = 1 where id = 65;
>
> 1 row updated.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select rowid, id, status_cd from [table name deleted]
>
> SQL> where id = 65;
>
> ROWID ID STATUS_CD
>
> ------------------ ------------------ ----------
>
> AAAq/DAAAAAAERlAAM 65 3
>
> Note that when retrieved the status_cd reverts to the old original value
> of 3, not the previous value of -643.
>
> 2. Oracle trace file proves that the update was issued and committed:
>
> ====================>
> PARSING IN CURSOR #1 len=70 dep=0 uid=110 oct=6 lid=110
> tim=17554807027344 hv=3512595279 ad=''fd211878''
>
> update [table deleted] set status_cd = 1 where id = 65 END OF STMT PARSE
> #1:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554807027340
>
> BINDS #1:
>
> EXEC #1:c=0,e=257,p=0,cr=3,cu=3,mis=0,r=1,dep=0,og=2,tim=17554807027737
>
> WAIT #1: nam=''SQL*Net message to client'' ela= 2 driver
id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=17554807027803 WAIT #1: nam=''SQL*Net
message
> from client'' ela= 2999139 driver id=1413697536 #bytes=1 p3=0
obj#=-1
> tim=17554810026992 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op=''UPDATE
> [TABLE DELETED] (cr=3 pr=0 pw=0 time=144 us)''
>
> STAT #1 id=2 cnt=1 pid=1 pos=1 obj=177738 op=''INDEX UNIQUE SCAN
> [TABLE_DELETED]_XPK (cr=3 pr=0 pw=0 time=19 us)''
>
> PARSE #2:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=17554810027367
>
> XCTEND rlbk=0, rd_only=0
>
> EXEC #2:c=0,e=226,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=17554810027630
>
> WAIT #2: nam=''log file sync'' ela= 833 buffer#=9408 p2=0
p3=0 obj#=-1
> tim=17554810028507 WAIT #2: nam=''SQL*Net message to
client'' ela= 2
> driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554810028578 WAIT #2:
> nam=''SQL*Net message from client'' ela= 1825185 driver
id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=17554811853812 ===================== PARSING
> IN CURSOR #1 len=67 dep=0 uid=110 oct=3 lid=110 tim=17554811854015
> hv=1593702413 ad=''fd713640''
>
> select status_cd from [table_deleted] where id = 65 END OF STMT PARSE
> #1:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554811854010
>
> BINDS #1:
>
> EXEC #1:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554811854273
>
> WAIT #1: nam=''SQL*Net message to client'' ela= 1 driver
id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=17554811854327 FETCH
> #1:c=0,e=64,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=2,tim=17554811854436
>
> WAIT #1: nam=''SQL*Net message from client'' ela= 780
driver id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=17554811855291 FETCH
> #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=17554811855331
>
> WAIT #1: nam=''SQL*Net message to client'' ela= 0 driver
id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=17554811855366
>
> There are no Oracle or Solaris error messages indicating any issue with
> this update. Haas anyone seen this behavoir?
>
> The features of ZFS (snapshots/clones/compression) save us a ton of time
> on this platform and we have certainly benefited from it. Just want to
> understand how something like this could occur and determine how we can
> prevent it in the future.
>
> =========>
> Gerry Bragg
>
> Sr. Developer
>
> Altarum Institute
>
> (734) 516-0825
>
> gerry.bragg at altarum.org <mailto:gerry.bragg at altarum.org>
>
> www.altarum.org <http://www.altarum.org/>
>
> "Systems Research For Better Health"
>
>
>
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss