| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: truncating snapshots
I tried doing a trace and the results are a bit confusing. For the test I put my master and snapshot on the same database (Scott/tiger user. I created a snapshot emp_test which was a subset of emp). Unfortunately this is a 7.3.4 database so it is a bit out of date. I might try 8.1 a little later. These are the steps I followed:
Insert one row into emp. Update one row of emp. (check mlog$_emp. The
entries exist)
turn on trace (level 12)
manually refresh the snapshot (execute
dbms_snapshot.refresh(list=>'emp_test')
turn off trace.
I wanted to see if the inserts into SNAP$_EMP_TEST and the deletes from MLOG$_EMP were in the same transaction. These are some excerpts from the trace file
update "SCOTT"."MLOG$_EMP" set snaptime$$ = :1 where snaptime$$ is NULL or snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')
END OF STMT
PARSE #7:c=0,e=0,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=1659386
BINDS #7:
bind 0: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=08
bfp=0088109c bln=07 avl=07 flg=0d
value="9/21/2001 11:41:12"
EXEC #7:c=0,e=0,p=0,cr=4,cu=4,mis=0,r=2,dep=2,og=4,tim=1659386
=====================
PARSING IN CURSOR #16 len=104 dep=1 uid=11 oct=2 lid=11 tim=1659401
hv=238238631 ad='8026f240'
insert into "SCOTT"."SNAP$_EMP_TEST" ( "EMPNO", "ENAME", "DEPTNO",
"M_ROW$$") values ( :1, :2, :3, :4)
END OF STMT
PARSE #16:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1659401
EXEC #16:c=0,e=1,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,tim=1659404
=====================
PARSING IN CURSOR #5 len=67 dep=1 uid=0 oct=6 lid=0 tim=1659404
hv=1979925439 ad='8026bf34'
update sys.snap$ set snaptime = :1 where vname = :2 and sowner = :3
END OF STMT
PARSE #5:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1659404
BINDS #5:
bind 0: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=08
bfp=008991a4 bln=07 avl=07 flg=0d
value="9/21/2001 11:41:12"
bind 1: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=08
bfp=0089918c bln=08 avl=08 flg=0d
value="EMP_TEST"
bind 2: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=08
bfp=008997ec bln=05 avl=05 flg=0d
value="SCOTT"
EXEC #5:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=1659404
XCTEND rlbk=0, rd_only=0
STAT #5 id=1 cnt=1 pid=0 pos=0 obj=120 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #5 len=82 dep=1 uid=11 oct=47 lid=11 tim=1659405
hv=1039323329 ad='8026ae9c'
begin sys.dbms_snapshot.wrap_up( :mowner, :master, :snapshot,
:snaptime); end;
END OF STMT
=====================
PARSING IN CURSOR #13 len=54 dep=2 uid=0 oct=7 lid=0 tim=1659414
hv=3795289681 ad='8025da64'
delete from "SCOTT"."MLOG$_EMP" where snaptime$$ <= :1
END OF STMT
PARSE #13:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=1659414
BINDS #13:
bind 0: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=08
bfp=008b127c bln=07 avl=07 flg=0d
value="9/21/2001 11:41:12"
EXEC
#13:c=0,e=0,p=0,cr=4,cu=4,mis=0,r=2,dep=2,og=4,tim=1659414==================
===
PARSING IN CURSOR #13 len=62 dep=2 uid=0 oct=6 lid=0 tim=1659414
hv=3644628630 ad='8025bfec'
update mlog$ set oldest = :1 where master = :3 and mowner = :2
END OF STMT
PARSE #13:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=1659414
=====================
BINDS #13:
bind 0: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=08
bfp=008b129c bln=07 avl=07 flg=0d
value="9/21/2001 11:41:12"
bind 1: dty=1 mxl=32(03) mal=00 scl=00 pre=00 oacflg=08
bfp=008adc34 bln=03 avl=03 flg=0d
value="EMP"
bind 2: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=08
bfp=008b127c bln=05 avl=05 flg=0d
value="SCOTT"
EXEC #13:c=1,e=1,p=0,cr=8,cu=1,mis=0,r=1,dep=2,og=4,tim=1659415
STAT #13 id=1 cnt=1 pid=0 pos=0 obj=116 op='TABLE ACCESS CLUSTER
MLOG$ '
STAT #13 id=2 cnt=1 pid=1 pos=1 obj=115 op='INDEX UNIQUE SCAN '
STAT #10 id=1 cnt=1 pid=0 pos=0 obj=117 op='TABLE ACCESS CLUSTER
SLOG$ '
STAT #10 id=2 cnt=1 pid=1 pos=1 obj=115 op='INDEX UNIQUE SCAN '
EXEC #5:c=7,e=10,p=1,cr=89,cu=16,mis=0,r=1,dep=1,og=4,tim=1659415
XCTEND rlbk=0, rd_only=1
EXEC #1:c=58,e=69,p=87,cr=821,cu=44,mis=0,r=1,dep=0,og=4,tim=1659415
WAIT #1: nam='log file sync' ela= 1 p1=8 p2=0 p3=0
The main point is that it appears to insert into the snapshot table, end the transaction (commit), delete from the master log table, end the transaction (read only????). So it looks like you could actually update the snapshot table without updating the master log. Also, why read only (XCTEND rlbk=0, rd_only=1)?
Sorry for the confusing e-mail, but it's all I have time for right now.
Henry
-----Original Message-----
Sent: Thursday, September 20, 2001 2:41 PM
To: Multiple recipients of list ORACLE-L
Shailesh,
Thanks. My thoughts on #2 are that if the snapshot refresh and the delete
from the master log comprise a single transaction (2 phase commit), then I
shouldn't have a problem. A full refresh might be necessary at some point,
but I could deal with that with some specific, infrequently used code. Of
course I still need to confirm that this is a single transaction. Not quite
sure how to do this. Maybe try a trace, or limit available space on snapshot
so inserts will error. I'll see what happens.
Henry
-----Original Message-----
Sent: Thursday, September 20, 2001 1:56 PM
To: Multiple recipients of list ORACLE-L
Henry,
I think Anita's concerns are valid however in my experience of doing the
refreshes like you plan to do I have not encountered the problem# 2. For
problem# 1 what we do here is check the table you are going to populate the
changes into for existance of that primary key. If found then update the
record with new values else insert into the table.
The real catch here would be the deletions. Try to get the deletes before
you do the refresh from the MLOG$xxx at the master site and then use those
primary keys to perform the deletion.
We have about 100 snapshots that are used to perform the aforementioned
task for over a year without any problems. Let me know if you have any
problem.
HTH,
Shailesh
-----Original Message-----
Sent: Tuesday, September 18, 2001 11:52 AM
To: Multiple recipients of list ORACLE-L
Henry,
OK, I think I see what you're trying to accomplish. By truncating the snapshot between refreshes, the refresh is essentially populating the snapshot with only the changed rows from the master site.
Probably not supported, but I can't see that it would cause any real problems. The way the refresh mechanism works, the missing rows on the snapshot site shouldn't be a problem.
I see a couple of potential problems, however.
It all depends on how critical the numbers are for the developers, because I can assure you it's only a matter of time before someone updates rows on a table that should only have inserts ;)
2. If a fast refresh fails this requires that the next refresh is a complete refresh, or the snapshot is recreated, so you will not have a way of getting just the set of changed rows. Your procedure will need to be able to detect this and perform the joins against the entire table again.
In the long run you're probably much better off developing your own trigger to populate another table or setting a flag, as you mentioned. Just because it works today doesn't mean that it will work in a newer release if they change the refresh mechanism.
HTH,
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Sep 21 2001 - 23:39:34 CDT
 |
 |