RE: Size of segment
Shreepad, There is no problem with a segment having more than 1 extent. It is a common misconception (another of those Myths, if you please) that all tables and indexes should reside in 1 extent, or less than 10 extents even. There is a paper available from the web - that many say is the definitive word on fragmentation of extents, called "How to stop defragmenting and start living", written by Juan Loaiza (sp?) which a google search should easily find.. The basics of this paper are thus: You should have a set of three tablespaces (for data and indexes) that each deal with a UNIFORM extent size, ranging through 160k, 5120k and 160M at the tablespace level. When creating a new object (table or index), you should determine how large the table/index is going to be, and create it in the appropriate tablespace, without defining specific storage clauses at the table level - therefore inheriting the Tablespace storage clauses as a default (gaining the uniform extent size for all objects within the tablespace). Of course, if you are already using LMTs (Locally Managed Tablespaces) with uniform extent allocation, then the need to do this has gone away. Once a table/index reaches 1024 extents (much more than the myth of 1-10 extents), then the object should be considered for a move up to a larger extent sized tablespace (and will still, by all accounts, take up 32 extents from the start of it's life in the new tablespace anyway - if moved straight away). I would again recommend reading the paper above - it's been a while since I had the chance to read it, and I'm not sure if it's been updated at all (anybody?), but it has an absolute wealth of information! HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -Original Message- [EMAIL PROTECTED] Sent: 30 April 2002 12:04 To: Multiple recipients of list ORACLE-L Hi, Thanks to all those who replied. Just clearing the structure. I have Hp LVM and the tables and indexes are on different tablespaces and different disks . Since the growth pattern has changed drastically , the no of extents have increased tremendously . Hence the need to rebuild the indexes . We plan to rebuild them into contiguous extents (only question being 1 extent or say 4-5 extents to take care of growth:--- 1 extent every 2 months ) Will the performance differ if they are on 1 extent or 5 extents ( both being contiguous) ? thanks shreepad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
Oh Shreepad, you've opened another can of worms there. =) You shouldn't see any performance difference between 1 extent and 5 extents or more. The best option for extent sizing in my opinion is to keep all extents within a tablespace the same size. This increases the manageability of the objects within it and eliminates honeycomb fragmentation. Regards, Mike Hately -Original Message- Sent: Tuesday, April 30, 2002 12:04 PM To: Multiple recipients of list ORACLE-L Hi, Thanks to all those who replied. Just clearing the structure. I have Hp LVM and the tables and indexes are on different tablespaces and different disks . Since the growth pattern has changed drastically , the no of extents have increased tremendously . Hence the need to rebuild the indexes . We plan to rebuild them into contiguous extents (only question being 1 extent or say 4-5 extents to take care of growth:--- 1 extent every 2 months ) Will the performance differ if they are on 1 extent or 5 extents ( both being contiguous) ? thanks shreepad This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
Hi, Thanks to all those who replied. Just clearing the structure. I have Hp LVM and the tables and indexes are on different tablespaces and different disks . Since the growth pattern has changed drastically , the no of extents have increased tremendously . Hence the need to rebuild the indexes . We plan to rebuild them into contiguous extents (only question being 1 extent or say 4-5 extents to take care of growth:--- 1 extent every 2 months ) Will the performance differ if they are on 1 extent or 5 extents ( both being contiguous) ? thanks shreepad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
Heh, never claimed it was a good idea, just that it's possible =) Mike -Original Message- Sent: Monday, April 29, 2002 7:33 PM To: Multiple recipients of list ORACLE-L On Mon, Apr 29, 2002 at 09:29:31AM -0800, Hately Mike wrote: > create tablespace disks_and index >datafile '/disk1/disks_and index01.dbf' size 51m, > '/disk2/disks_and index01.dbf' size 51m; > > create table mike01 ( > x varchar(10) > ) > storage (initial 50m) > tablespace disks_and index; > > > create table mike01_ix on mike01(x) > storage (initial 50m) > tablespace disks_and index; "I'm speachless, I am without speach." === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size of segment
On Mon, Apr 29, 2002 at 09:29:31AM -0800, Hately Mike wrote: > create tablespace disks_and index >datafile '/disk1/disks_and index01.dbf' size 51m, > '/disk2/disks_and index01.dbf' size 51m; > > create table mike01 ( > x varchar(10) > ) > storage (initial 50m) > tablespace disks_and index; > > > create table mike01_ix on mike01(x) > storage (initial 50m) > tablespace disks_and index; "I'm speachless, I am without speach." === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
Damn! Spot the typo. Blame it on the 4am start today. I meant : create tablespace disks_and index datafile '/disk1/disks_and index01.dbf' size 51m, '/disk2/disks_and index01.dbf' size 51m; create table mike01 ( x varchar(10) ) storage (initial 50m) tablespace disks_and index; create table mike01_ix on mike01(x) storage (initial 50m) tablespace disks_and index; Mike -Original Message- Sent: Monday, April 29, 2002 6:05 PM To: Multiple recipients of list ORACLE-L Ray, The 2 things aren't at all synonymous; it's quite possible to split objects across tablespaces while keeping them on one disk. You mean that one requires the other and on the whole you're right. I agree that sensibly you need to split them across tablespaces to achieve disk separation but the point I was making was that the disk placement is the important point rather than the tablespace name. BTW This is how could you control the location of the index forcing it onto a different disk without having a separate ts/datafile? Mind you, I'll agree you'd have to be mad to do it. =) create tablespace disks_and index datafile '/disk1/disks_and index01.dbf' size 51m, '/disk1/disks_and index01.dbf' size 51m; create table mike01 ( x varchar(10) ) storage (initial 50m) tablespace disks_and index; create table mike01_ix on mike01(x) storage (initial 50m) tablespace disks_and index; Regards, Mike Hately -Original Message- Sent: Monday, April 29, 2002 5:20 PM To: Multiple recipients of list ORACLE-L On Mon, Apr 29, 2002 at 07:29:01AM -0800, Hately Mike wrote: > Charlie, > > I think conventional wisdom suggests splitting data and index onto separate > disks rather than separate tablespaces. Aren't the two things synonymous? How could you control the location of the index forcing it onto a different disk without having a seperate ts/datafile? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -
RE: Size of segment
Ray, The 2 things aren't at all synonymous; it's quite possible to split objects across tablespaces while keeping them on one disk. You mean that one requires the other and on the whole you're right. I agree that sensibly you need to split them across tablespaces to achieve disk separation but the point I was making was that the disk placement is the important point rather than the tablespace name. BTW This is how could you control the location of the index forcing it onto a different disk without having a separate ts/datafile? Mind you, I'll agree you'd have to be mad to do it. =) create tablespace disks_and index datafile '/disk1/disks_and index01.dbf' size 51m, '/disk1/disks_and index01.dbf' size 51m; create table mike01 ( x varchar(10) ) storage (initial 50m) tablespace disks_and index; create table mike01_ix on mike01(x) storage (initial 50m) tablespace disks_and index; Regards, Mike Hately -Original Message- Sent: Monday, April 29, 2002 5:20 PM To: Multiple recipients of list ORACLE-L On Mon, Apr 29, 2002 at 07:29:01AM -0800, Hately Mike wrote: > Charlie, > > I think conventional wisdom suggests splitting data and index onto separate > disks rather than separate tablespaces. Aren't the two things synonymous? How could you control the location of the index forcing it onto a different disk without having a seperate ts/datafile? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size of segment
On Mon, Apr 29, 2002 at 07:29:01AM -0800, Hately Mike wrote: > Charlie, > > I think conventional wisdom suggests splitting data and index onto separate > disks rather than separate tablespaces. Aren't the two things synonymous? How could you control the location of the index forcing it onto a different disk without having a seperate ts/datafile? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size of segment
On Mon, Apr 29, 2002 at 06:53:39AM -0800, Charlie Mengler wrote: > > > Ray Stell wrote: > > > > On Mon, Apr 29, 2002 at 05:13:22AM -0800, Ganesh Raja wrote: > > > My Suggestion would be to give Uniform sized Extents and move the table and > > > Index into that tablespace. > > -- > > > > You are taking a preformance hit having the indexes and tables > > in the same tablespace, are you not? > > There is/was a recent and active discussion of this topic in the > Oracle Server Usenet forum. > > While it has long been "recommended" that tables & indexes be placed > in separate tablespaces, the current conventional wisdom disputes the > claim that performance can/will be enhanced by separating data and > indexes into different tablespaces. > > If anybody can produce recent benchmark results which shows that > separating index & data into different tablespaces improves performance, > I'd like to see the details behind the benchmark and the results. > -- Cool! Would love to see this data, also, because it doesn't make any logical sense to me, unless the index and table ts are on the same disk, I suppose. I love it when I open can-o-worms. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
The improvement is more about putting them in different tablespaces that reside on different physical disks, therefore sharing the I/O between the disks, and having the disk heads searching concurrently, rather than thrashing back and forth.. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -Original Message- Mengler Sent: 29 April 2002 15:54 To: Multiple recipients of list ORACLE-L Ray Stell wrote: > > On Mon, Apr 29, 2002 at 05:13:22AM -0800, Ganesh Raja wrote: > > My Suggestion would be to give Uniform sized Extents and move the table and > > Index into that tablespace. > -- > > You are taking a preformance hit having the indexes and tables > in the same tablespace, are you not? There is/was a recent and active discussion of this topic in the Oracle Server Usenet forum. While it has long been "recommended" that tables & indexes be placed in separate tablespaces, the current conventional wisdom disputes the claim that performance can/will be enhanced by separating data and indexes into different tablespaces. If anybody can produce recent benchmark results which shows that separating index & data into different tablespaces improves performance, I'd like to see the details behind the benchmark and the results. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
Charlie, I think conventional wisdom suggests splitting data and index onto separate disks rather than separate tablespaces. Just to start the unsubstantiated anecdotal evidence I've seen this help on more occasions than I care to recall. I'm the first to agree though that separating data and indexes into separate tablespaces won't help if they're on the same disks. It will make them easier to split in future though. Regards, Mike Hately -Original Message- Sent: Monday, April 29, 2002 3:54 PM To: Multiple recipients of list ORACLE-L Ray Stell wrote: > > On Mon, Apr 29, 2002 at 05:13:22AM -0800, Ganesh Raja wrote: > > My Suggestion would be to give Uniform sized Extents and move the table and > > Index into that tablespace. > -- > > You are taking a preformance hit having the indexes and tables > in the same tablespace, are you not? There is/was a recent and active discussion of this topic in the Oracle Server Usenet forum. While it has long been "recommended" that tables & indexes be placed in separate tablespaces, the current conventional wisdom disputes the claim that performance can/will be enhanced by separating data and indexes into different tablespaces. If anybody can produce recent benchmark results which shows that separating index & data into different tablespaces improves performance, I'd like to see the details behind the benchmark and the results. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
Hey and Another Thing there is No Performance Benift By Putting them on different Tablespaces. So For Easier Management the Original Poster can Put it in Different TBS and not For any performance Reasons. Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Monday, April 29, 2002 6:23 PM To: Multiple recipients of list ORACLE-L On Mon, Apr 29, 2002 at 05:13:22AM -0800, Ganesh Raja wrote: > My Suggestion would be to give Uniform sized Extents and move the > table and Index into that tablespace. -- You are taking a preformance hit having the indexes and tables in the same tablespace, are you not? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size of segment
Ray Stell wrote: > > On Mon, Apr 29, 2002 at 05:13:22AM -0800, Ganesh Raja wrote: > > My Suggestion would be to give Uniform sized Extents and move the table and > > Index into that tablespace. > -- > > You are taking a preformance hit having the indexes and tables > in the same tablespace, are you not? There is/was a recent and active discussion of this topic in the Oracle Server Usenet forum. While it has long been "recommended" that tables & indexes be placed in separate tablespaces, the current conventional wisdom disputes the claim that performance can/will be enhanced by separating data and indexes into different tablespaces. If anybody can produce recent benchmark results which shows that separating index & data into different tablespaces improves performance, I'd like to see the details behind the benchmark and the results. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
Sorry that I was not explicit .. If you are experiencing high Transactions You need to put the Tables and Indexes on Different Tablespaces [ again Uniformly Sized] Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Monday, April 29, 2002 6:23 PM To: Multiple recipients of list ORACLE-L On Mon, Apr 29, 2002 at 05:13:22AM -0800, Ganesh Raja wrote: > My Suggestion would be to give Uniform sized Extents and move the > table and Index into that tablespace. -- You are taking a preformance hit having the indexes and tables in the same tablespace, are you not? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size of segment
On Mon, Apr 29, 2002 at 05:13:22AM -0800, Ganesh Raja wrote: > My Suggestion would be to give Uniform sized Extents and move the table and > Index into that tablespace. -- You are taking a preformance hit having the indexes and tables in the same tablespace, are you not? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Size of segment
Physically No Extents, No Blocks are Contigous.. There is No Use in Bringing the Entire Table 1 or 10 Extents. If you have Used PCTIncrease in your TBS then Over Time Your Tables and Indexwes will be fragmented. My Suggestion would be to give Uniform sized Extents and move the table and Index into that tablespace. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Monday, April 29, 2002 4:43 PM To: Multiple recipients of list ORACLE-L Hi, I have segments (Tables and indexes) which go into 500 MB -- 1 GB . I intend to do a rebuild since they are fragmented. Is it beneficial to have a single extent of 500 Mb or multiple extents are ok ? (maybe 4 --5) My contention is that so long as the extents are contiguous ( I could do a rebuild) the number do not matter so long as they are not too high. Any suggestions ? Thanks shreepad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
