Hi List,
I've made two corresponding dumps and ... I must admit I have much less
understanding than I had before.
Please look at them.
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=32 oct=3 lid=32 tim=219058914
hv=168660475 ad='aa69f79c'
select /*+ INDEX(documents pk_documents) */ count(*)
from documents
END OF STMT
PARSE #1:c=12,e=29,p=27,cr=322,cu=6,mis=1,r=0,dep=0,og=4,tim=219058914
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=219058914
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=108810 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=109485 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=108811 p3=1
WAIT #1: nam='db file scattered read' ela= 1 p1=22 p2=108812 p3=11
[skip]
WAIT #1: nam='db file parallel read' ela= 1 p1=1 p2=11 p3=11
WAIT #1: nam='db file parallel read' ela= 0 p1=1 p2=11 p3=11
[skip]
WAIT #1: nam='db file scattered read' ela= 0 p1=22 p2=109472 p3=11
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=109483 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=109486 p3=1
WAIT #1: nam='db file parallel read' ela= 0 p1=1 p2=11 p3=11
=====================
PARSING IN CURSOR #1 len=72 dep=0 uid=32 oct=3 lid=32 tim=219108177
hv=3669603672 ad='aa6a4f84'
select /*+ INDEX_FFS(documents pk_documents) */
count(*)
from documents
END OF STMT
PARSE #1:c=11,e=13,p=27,cr=322,cu=6,mis=1,r=0,dep=0,og=4,tim=219108177
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=219108177
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=108809 p3=1
WAIT #1: nam='db file scattered read' ela= 2 p1=22 p2=108810 p3=8
WAIT #1: nam='db file scattered read' ela= 0 p1=22 p2=108818 p3=8
[skip, all next lines are the same]
My questions are
1. Why I get 'db file scattered read' waits for index full scan ?
2. Why p3 for this event is 11 even though my db_file_multiblock_read_count
= 8 ?
3. What does mean 'db file parallel read' event?
Anjo Kolk's paper doesn't describe it (guess the wait is Oracle8-specific).
Oracle 8i Reference says:
<blockquote> This happens during recovery. Database blocks that need to be
changed as
part of recovery are read in parallel from the database.</blockquote>
But it seems not my case.
4. Finally, what is the difference between the two index paths in that case
?
I'm on 8.1.7.0 Solaris, file-based system, LMT
Thanks in advance,
Ed
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shevtsov, Eduard
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
