RE: Tablespace and Table storage options

Not many details here....
What plaform and O/S? What storage platform? Is this raw, or filesystem? If filesystem, is it buffered or direct I/O?

Now, I was going to say that one thing that may be affecting your timings is the (relatively small) size of your extents. Note that multiblock reads (the db file scattered read event) cannot span extent boundaries. But, in this case, it's even worse than that. You said you were on an 8k block size, and the largest read I see from the reads below is 8 blocks (see the P3 value). So, you're only reading 64k at a time. So, something is limiting your multiblock reads to 64k. Likely, this is an O/S or filesystem limitation. Depending on your platform and/or O/S, there's probably a way to increase your multiblock read to 1MB. (1MB is a common limit on most platforms.) But, note that if you limit extent size to 160k, you'll never exceed 20 blocks per read. (8k*20 = 160k)

Hope that helps,

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laimutis Nedzinskas
Sent: Wednesday, October 11, 2006 6:51 AM
To: oracle-l_at_freelists.org
Subject: Tablespace and Table storage options

Once upon a time setting table's storage (initial X next Y) was quite an
issue. We used to rebuild tables to fit into one extent, etc.

Then it was realized that in concurrent environment striping is a better
choice ("more spindles, please" as far as I understand it.) Which made
"one extent" goal irrelevant.

Now we have ample of RAM in both storage devices and OS level. Which
makes "read ahead" quite efficient. For example I measured full table
scans at 120-100mb/sec *physical reads* rate on some machines. 

However it seems if it again became quite important to minimize the
number of extents and/or make them adjacent to fully exploit the "read
ahead", doesn't it? 
I am going to check more on this myself but my initial data shows quite
a difference. 
At least on one of machines I got just 1-2Mb/sec full table scan
throughput under those conditions:
- database in read only mode
- one user
- block size 8k
- locally managed tablespace, uniform extent allocation, 160K extent
size(20 blocks)
- 14 or so datafiles in the tablespace
- Typical wait times from the full scan trace are presented bellow.
Please note ample of wait times of 15, 20 and more ms. It seems if read
ahead is quite lost because of many non-adjacent extents and/or many
datafiles. At least I have no other explanation for 1-2 mb/sec
throughput. Please note too that our unix admin demonstrated
50-100mb/sec throughput of reading a single file using kind of "cat
file1> /dev/null" on the same machine. 

WAIT #1: nam='db file scattered read' ela= 24428 p1=3 p2=27777 p3=8 WAIT


#1: nam='db file scattered read' ela= 15237 p1=3 p2=27785 p3=4


WAIT #1: nam='db file scattered read' ela= 21012 p1=4 p2=26910 p3=8 WAIT


#1: nam='db file scattered read' ela= 23571 p1=4 p2=26918 p3=8 WAIT #1:

nam='db file scattered read' ela= 990 p1=4 p2=26926 p3=3

WAIT #1: nam='db file scattered read' ela= 455 p1=9 p2=172269 p3=8 WAIT


#1: nam='db file scattered read' ela= 2159 p1=9 p2=172277 p3=8 WAIT #1:

nam='db file scattered read' ela= 1543 p1=9 p2=172285 p3=4

WAIT #1: nam='db file scattered read' ela= 25435 p1=10 p2=191909 p3=8
WAIT #1: nam='db file scattered read' ela= 1677 p1=10 p2=191917 p3=8
WAIT #1: nam='db file scattered read' ela= 247 p1=10 p2=191925 p3=4

WAIT #1: nam='db file scattered read' ela= 4847 p1=12 p2=40890 p3=8 WAIT


#1: nam='db file scattered read' ela= 24200 p1=12 p2=40898 p3=8 WAIT #1:

nam='db file scattered read' ela= 1501 p1=12 p2=40906 p3=3

WAIT #1: nam='db file scattered read' ela= 474 p1=13 p2=35889 p3=8 WAIT


#1: nam='db file scattered read' ela= 445 p1=13 p2=35897 p3=8 WAIT #1:

nam='db file scattered read' ela= 2744 p1=13 p2=35905 p3=4

WAIT #1: nam='db file scattered read' ela= 20278 p1=3 p2=27789 p3=8 WAIT


#1: nam='db file scattered read' ela= 23263 p1=3 p2=27797 p3=8 WAIT #1:

nam='db file scattered read' ela= 8412 p1=3 p2=27805 p3=4

WAIT #1: nam='db file scattered read' ela= 362 p1=4 p2=26930 p3=8 WAIT


#1: nam='db file scattered read' ela= 11520 p1=4 p2=26938 p3=8 WAIT #1:

nam='db file scattered read' ela= 17659 p1=4 p2=26946 p3=3

WAIT #1: nam='db file scattered read' ela= 23675 p1=9 p2=172289 p3=8
WAIT #1: nam='db file scattered read' ela= 25685 p1=9 p2=172297 p3=8
WAIT #1: nam='db file scattered read' ela= 15471 p1=9 p2=172305 p3=4

WAIT #1: nam='db file scattered read' ela= 344 p1=10 p2=191929 p3=8 WAIT


#1: nam='db file scattered read' ela= 3410 p1=10 p2=191937 p3=8 WAIT #1:

nam='db file scattered read' ela= 21429 p1=10 p2=191945 p3=4


Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l