What happens if updating cursor did not fetch any records

nam='gc cr disk read' ela= 633 p1=6 p2=176 p3=43 obj#=0 tim=1103292048 nam='db file sequential read' ela= 662 file#=6 block#=176 blocks=1 obj#=0 tim=1103292843 -- RS: Following read is for undo block itself to rollback the transaction changes.

nam='gc cr disk read' ela= 483 p1=6 p2=955 p3=44 obj#=0 tim=1103293699 nam='db file sequential read' ela= 569 file#=6 block#=955 blocks=1 obj#=0 tim=1103294355 nam='library cache pin' ela= 1045 handle address=2043988208 pin address=1969440144 100*mode namespace=48820893384706 obj#=0 tim=1103295827 FETCH :c=0,e=8033,p=5,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=479790187,tim=1103296065 WAIT : nam='SQL*Net message from client' ela= 323 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1103296522 FETCH :c=0,e=24,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=479790187,tim=1103296586 STAT id=1 cnt=1 pid=0 pos=1 obj=75154 op='TABLE ACCESS BY INDEX ROWID T_1000 (cr=7 pr=5 pw=0 time=6505 us cost=2 size=261 card=1)' STAT id=2 cnt=1 pid=1 pos=1 obj=85844 op='INDEX RANGE SCAN T_1000_N1 (cr=3 pr=2 pw=0 time=2494 us cost=1 size=0 card=1)' WAIT : nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1103296728 Let’s review few lines from the trace file. After reading that block, a ‘gc cr disk read’ wait event is encountered.

gv$cr_block_server can be used to review the number of down converts, Light works etc.

what happens if updating cursor did not fetch any records-87what happens if updating cursor did not fetch any records-12

obj#=0 indicates that this is an undo block or undo header block.Test case Of course, a test case would be nice, Just any regular table will do and my table structure have just two columns number, varchar2(255) with 1000 rows or so.PARSING IN CURSOR #18446741324873694136 len=162 dep=0 uid=0 oct=3 lid=0 tim=1103269602 hv=361365006 ad='73fba918' sqlid='7cmy6msasmzhf' select dbms_rowid.rowid_relative_fno (rowid) fno, dbms_rowid.rowid_block_number(rowid) block, dbms_rowid.rowid_object(rowid) obj, v1 from rs.t_1000 where n1=100 END OF STMT c=0,e=17012,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=479790187,tim=1103269601 c=0,e=12393,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=479790187,tim=1103282129 nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=603 tim=1103288000 nam='Disk file operations I/O' ela= 7 File Operation=2 fileno=4 filetype=2 obj#=603 tim=1103288120 nam='db file sequential read' ela= 786 file#=4 block#=2891 blocks=1 obj#=85844 tim=1103289701 nam='db file sequential read' ela= 560 file#=4 block#=2892 blocks=1 obj#=85844 tim=1103290450 nam='Disk file operations I/O' ela= 4 File Operation=2 fileno=7 filetype=2 obj#=85844 tim=1103290546 nam='db file sequential read' ela= 542 file#=7 block#=2758 blocks=1 obj#=75154 tim=1103291149 -- RS: Following is for undo header block to find the transaction.nam='db file sequential read' ela= 542 file#=7 block#=2758 blocks=1 obj#=75154 tim=1103291149 nam='gc cr disk read' ela= 633 p1=6 p2=176 p3=43 obj#=0 tim=1103292048 nam='db file sequential read' ela= 662 file#=6 block#=176 blocks=1 obj#=0 tim=1103292843 Commit Cleanout Commit cleanouts is another reason why you would encounter this wait event.When a session commits, that session will revisit the modified blocks to clean out the ITL entries in the modified blocks.Then the sequence of operation is(simplified): LMS is a light weight process Global cache operations must complete quickly, in the order of milli-seconds, to maintain the overall performance of RAC database.LMS is a critical process and does not do heavy lifting tasks such as disk I/O etc.Now, the Foreground process might not find the undo blocks in the local cache as the transactions happened in the remote cache.A request is sent to remote LMS process to access undo block.Prior to 11g, you could still differentiate waits for single block grants for undo using ASH data or trace files.But, you will have to use the obj# field for this differentiation and obj# is set to 0 or -1 in the case of undo blocks/undo header blocks.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “what happens if updating cursor did not fetch any records”