Home » RDBMS Server » Performance Tuning » striping
striping [message #65004] Sun, 28 March 2004 03:34 Go to next message
ashokmote
Messages: 56
Registered: December 2003
Member
how can i implement striping on tablespace.by using striping can i improve my database performance.
Re: striping [message #65009 is a reply to message #65004] Mon, 29 March 2004 06:32 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Place the tablespace datafiles on a striped volume(Raid 0,5,1+0,0+1). Yes you will improve performance by striping.

You can also implement manual striping by placing the datafiles in separate physical disks ,but its not a scalable soln and is certainly tedious.
Re: striping [message #65019 is a reply to message #65009] Tue, 30 March 2004 00:45 Go to previous messageGo to next message
ashokmote
Messages: 56
Registered: December 2003
Member
can u give me example how to implement striping using sql commands.plz
or if there is any material can u send the link.
Re: striping [message #65022 is a reply to message #65019] Tue, 30 March 2004 10:16 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
its the same command you use to create a tablespace.

for eg)


SQL> create tablespace ts_striped datafile '/oradb01/dbatest/ts_stripe1.dbf' size 10m,
  2  '/oradb03/dbatest/ts_stripe2.dbf' size 10m;

Tablespace created.

where /oradb01 is a filesystem on say Disk1 and /oradb03 is a filesystem on say Disk3 . Now , you can say that this tablespace is striped across two disks.

-- Now lets create a table in this striped tablespace

SQL> drop table t;

Table dropped.

SQL> create table t tablespace ts_striped as select * from all_objects ;

Table created.

-- as seen below, this table is striped across two datafiles(and hence two disks(if thats actually the case))

SQL> select distinct e.file_id,f.file_name from dba_extents e,dba_data_files f where e.file_id=f.file_id and
  2  e.owner='THIRU' and e.segment_name='T';

   FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
        14
/oradb01/dbatest/ts_stripe1.dbf

        15
/oradb03/dbatest/ts_stripe2.dbf

-- This is an example of manual striping and is not the ideal or very scalable of doing striping. Almost always some form of RAID is used to implement striping.



-Thiru
Re: striping [message #65029 is a reply to message #65022] Wed, 31 March 2004 23:19 Go to previous messageGo to next message
ashokmote
Messages: 56
Registered: December 2003
Member
how it store.i mean one extent in one file and other extent in another file or block wise .how it stores when u give tablespace in striping.
Re: striping [message #65032 is a reply to message #65029] Thu, 01 April 2004 10:17 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
as you saw, one extent was in one file and the 2nd extent on the other file. Again,this is just an example.

An extent is a contigous (supposedly) allocation of blocks. If the underlying filesystem consists of multiple physical disks(which is usally the case with RAID), they are spread across multiple disks.
Previous Topic: Performance Tuning a Query
Next Topic: Information on Statistics generation for Partition Tables
Goto Forum:
  


Current Time: Tue Nov 26 11:35:10 CST 2024