OraFAQ Forum: Server Administration » db_file_multiblock_read_count

Home » RDBMS Server » Server Administration » db_file_multiblock_read_count - reading too less blocks at a time

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

db_file_multiblock_read_count - reading too less blocks at a time [message #244618]

Wed, 13 June 2007 09:08

Brayan
Messages: 315
Registered: June 2002

Senior Member

Hi,

OS - HP Unix
Ver - 9.2.0.7
Appln: DSS System

SQL>  select name, value from v$parameter where name = 'db_file_multiblock_read_count';

NAME                           VALUE
------------------------------ ------------------------------
db_file_multiblock_read_count  32

SQL>

But, in the trace file I notice following,

PARSE #8:c=20000,e=16022,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=3458967830261
EXEC #8:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3458967830821
WAIT #8: nam='db file scattered read' ela= 8927 p1=23 p2=195144 p3=5
WAIT #8: nam='db file scattered read' ela= 14401 p1=24 p2=146849 p3=5
WAIT #8: nam='db file scattered read' ela= 4205 p1=76 p2=644179 p3=5
WAIT #8: nam='db file scattered read' ela= 8167 p1=80 p2=295674 p3=5
WAIT #8: nam='db file scattered read' ela= 6471 p1=82 p2=236309 p3=5
WAIT #8: nam='db file scattered read' ela= 8813 p1=83 p2=236279 p3=5
WAIT #8: nam='db file scattered read' ela= 5709 p1=84 p2=202659 p3=5
WAIT #8: nam='db file scattered read' ela= 14241 p1=86 p2=4574 p3=5
WAIT #8: nam='db file scattered read' ela= 5573 p1=4 p2=13289 p3=5
WAIT #8: nam='db file scattered read' ela= 1084 p1=23 p2=195149 p3=5

I just wonder, why it is reading only 5 blocks. Is my observation is correct? If, what could be the reason it is reading so less blocks.

Brayan.

Report message to a moderator

Re: db_file_multiblock_read_count - reading too less blocks at a time [message #244621 is a reply to message #244618]

Wed, 13 June 2007 09:18

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Because either:
- your extent size is 5 blocks (preferred reason as it is constant)
- the next block is already in cache

Regards
Michel

Report message to a moderator

Re: db_file_multiblock_read_count - reading too less blocks at a time [message #244743 is a reply to message #244618]

Thu, 14 June 2007 00:47

Brayan
Messages: 315
Registered: June 2002

Senior Member

Hi Michael,

Quote:

- your extent size is 5 blocks (preferred reason as it is constant)

Yes, My table's extent size is 5 blocs (i.e 8192*5). It has inherited from tablespace(UNIFORM) parameters.

That mean to say that, it can read max 1 extent at a time.

In my query it was a Materialized view, do u suggest me alter the storage parameters and reresh complete?

Regards,
Brayan.

Report message to a moderator

Re: db_file_multiblock_read_count - reading too less blocks at a time [message #244774 is a reply to message #244743]

Thu, 14 June 2007 01:40

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

it can read max 1 extent at a time.

Yes because extents may not be contiguous.

Quote:

do u suggest me alter the storage parameters

I suggest you rebuild the whole tablespace with AUTOALLOCATE parameter instead of UNIFORM with so small extent size.

Regards
Michel

Report message to a moderator

Previous Topic:	A bit confused with the constraints
Next Topic:	Process in V$SESSION

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 23:14:23 CDT 2024