Home » RDBMS Server » Server Administration » db_file_multiblock_read_count - reading too less blocks at a time
db_file_multiblock_read_count - reading too less blocks at a time [message #244618] Wed, 13 June 2007 09:08 Go to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
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
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 Go to previous messageGo to next message
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.


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 Go to previous message
Michel Cadot
Messages: 64132
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
Previous Topic: A bit confused with the constraints
Next Topic: Process in V$SESSION
Goto Forum:
  


Current Time: Wed Dec 07 18:12:27 CST 2016

Total time taken to generate the page: 0.08995 seconds