Tablespace and Table storage options

From: Laimutis Nedzinskas <Laimutis.Nedzinskas_at_landsbanki.is>
Date: Wed, 11 Oct 2006 10:51:28 -0000
Message-ID: <5A8896FB2AFC5445A7DCFC5903CCA6B06FEAFD@W03856.li01r1d.lais.net>

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

Received on Wed Oct 11 2006 - 05:51:28 CDT