| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle-managed RAID-like tablespaces
Hi Roobaron,
Output from a table I created in a LMT that has two datafiles associated with it. I made sure the table would require multiple extents when created.
SQL> col segment_name format a15
SQL> select segment_name, file_id from dba_extents where segment_name =
'STRIPE';
SEGMENT_NAME FILE_ID
--------------- ----------
STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 SEGMENT_NAME FILE_ID --------------- ---------- STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 SEGMENT_NAME FILE_ID --------------- ---------- STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 SEGMENT_NAME FILE_ID --------------- ---------- STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11
40 rows selected.
There seems to be a pattern occurring !!
Then issued the following:
SQL> alter table stripe allocate extent;
Table altered.
SQL> / Table altered.
SQL> / Table altered.
SQL> / Table altered.
SQL> select segment_name, file_id from dba_extents where segment_name =
'STRIPE';
SEGMENT_NAME FILE_ID
--------------- ----------
STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 SEGMENT_NAME FILE_ID --------------- ---------- STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 SEGMENT_NAME FILE_ID --------------- ---------- STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 SEGMENT_NAME FILE_ID --------------- ---------- STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11 STRIPE 9 STRIPE 11
44 rows selected.
And the pattern continues !!
If you re-read the horses mouth, it says "by first determining a candidate datafile in the tablespace" which is indeed in a round robin fashion (and has been for a long long time).
Now I agree with previous comments that such physical layout should preferable be performed at the O/S level, but at least Oracle does have a reasonable method of manual striping that could be useful in distributing data across a desirable number of devices.
Hopes this clarifies
Richard
"roobaron" <member_at_dbforums.com> wrote in message
news:3d4533a6$1_at_news.usenetgateway.com...
> Had a quick look at the doco (concepts manual) for 8.1.7 doesn't mention
> that data is written in a round robin fashion, the algorithm will write
> to the same datafile as long as there is contiguous space, only looking
> at the other datafile if there is not enough space.
>
> Here it is from the horse's mouth
>
> "Allocating Extents in Locally-Managed Tablespaces In locally-managed
> tablespaces, Oracle looks for free space to allocate to a new extent by
> first determining a candidate datafile in the tablespace and then
> searching the datafile's bitmap for the required number of adjacent free
> blocks. If that datafile does not have enough adjacent free space,
> Oracle looks in another datafile."
>
> What you are trying to achieve is striping for reads... we are talking
> somewhere in the vicinity of 8k reads per I/O depending on the disk I/O
> setup. To stripe the data exactly so that your app gets the multiple
> disk heads retrieving via Oracle is not worth the effort.
>
> Use OS striping or a decent LVM as mentioned.
>
> Hope this helps save time
>
>
>
> --
> An Oracle DBAs undocumented init.ora parameters
> _OVERCOME_POOR_DESIGN = TRUE
> _DISABLE_BAD_PERFORMANCE = TRUE
>
> Posted via dBforums
> http://dbforums.com
Received on Mon Jul 29 2002 - 08:14:02 CDT
 |
 |