Not to burst anyone's bubble, but check out what Tom Kyte has to say about the multiple extents causing performance problems being a bogus myth: http://asktom.oracle.com/pls/ask/f?p=4950:8:7253435856844271710::NO::F4950_P 8_DISPLAYID,F4950_P8_CRITERIA:1907660089219, In this post Tom writes, "... I've had alot of it on this site (about multiple extents). It is a myth. Many extents is OK." Another reference: http://asktom.oracle.com/pls/ask/f?p=4950:8:7253435856844271710::NO::F4950_P 8_DISPLAYID,F4950_P8_CRITERIA:2876787802154, Here Tom writes, "The correct answer is -- set max extents unlimited because the number of extents an object has does not have a material effect on performance and this is a provable fact! Then, go about doing infinitely more useful things like helping developers do their job." In the above link, he also lists this link: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:730289259 844 More important is matching extent sizes to multiples of db_file_multiblock_read_count, the operating system's IO chunk size, and if applicable, your striping software's stripe/interleave size. If this is not the case, IO requests must be split, which in addition to the excessive context switch issue, causes many times more actual IO requests than necessary. > -----Original Message----- > From: Nisar Tareen [mailto:ntareen@xxxxxxxxx] > Sent: Tuesday, November 09, 2004 8:40 AM > To: askdba@xxxxxxxxxxxxx > Subject: [askdba] Re: Need help with Interview Questions > > > Irfan, > > Thanks for the clearification . > > Nisar Tareen > > Irfan Khan <irfan.khan@xxxxxxxxxxxx> wrote: > Hi Nisar > > Let me put things to rest here. Though i still stand to be corrected. > > This was ur comment in my mail "do you think the > parallelision of disk = > multiple extents is in DBA's hand." > > Ganesh merely tried to get more explanation on your understanding. > > And again, i believe, u need to know if it is 25extents x 4MB > each or = > 1extent of 100MB , the cost for FTS will still remain the same. > > Unless the table grew 100MB and was emptied by delete > statement, then = > yes we are making oracle work more though not required, Hence ur = > statement below is incorrect. > > "I will stand on my ground that multiple extents (as I said extra = > ordinary) will affect the performance for full table scan, as > the disk = > head had to reposition itself" > > Also Compare the 2 statements below (read over and over again > to get the = > gist of it) > > Ganesh says:- > Extent has always been a Logical Grouping of Blocks and not a > Physicall Grouping. > > You said :- > Plus, extents are Physical blocks not logical. eg. 1 block of > 10 giga = > bytes or 10 blocks of 1 biga bytes.=20 > > > All you are doing is twisting words and trying to prove someone else = > wrong. > > > Also my statement about benefit of multiple extent is not my > own, Its in = > the book of renowned Writer "GAJA VAIDYANTHA" in Oracle Performance = > Tuning 101. U can verify my claim, I have already > communicated the same = > to Ganesh. > > Also my argument and what i tried to convey can be supported by this = > article and this statement therein :- > > "If you have only one extent for SALES, you will not be able to = > effectively use the Parallel Query option (PQO) for queries > against the = > table. In the PQO, multiple processes concurrently perform > the work of a = > query. If the data queried by the multiple processes is all > located on = > the same disk, using the PQO for queries of the data may > create an I/O = > bottleneck on the disk!" > > Regards > Irfan Khan > > PS :- No hard feelings, but i dont doubt and neither i expect > u to prove = > me that u hv handled multiple terabyte database. > > > > > -----Original Message----- > From: Nisar Tareen [mailto:ntareen@xxxxxxxxx] > Sent: Tuesday, November 09, 2004 5:42 AM > To: askdba@xxxxxxxxxxxxx > Cc: dba Group > Subject: [askdba] Re: Need help with Interview Questions > > > Ganesh,=20 > > =20 > > I did not recall of mentioning Parallelism what I rote to Irfan, My = > answer was about multiple extents.=20 > > =20 > > Yes, I will stand on my ground that multiple extents (as I > said extra = > ordinary) will affect the performance for full table scan, as > the disk = > head had to reposition itself > > To make extra hits (I/O) for read and write/update, and extra block = > addresses in SGA for the records read. > > =20 > > My understanding my logic, Ganesh most of the time I write > from the top = > of my mind, After doing this work for so long, I do not go > through the = > technical manual, My idea is not to write technical manual for every = > email or query, Email should give the major guide line on the > issue and = > each one of us the user should go the extra 9 yards according to our = > needs. =20 > > =20 > > Believe me I ran Tara byte database and I don't have to prove, I had = > done my proving, Few extents are better that many.=20 > > =20 > > I am not an academic and I am running a shop and the time I > have I enjoy = > answering emails and some time for fun add my logic. But > Oracle is in = > the business of proving yes I can be wrong then you just have > to say, = > Nisar here you are wrong. I will and you can give the > understanding on = > an issue, its okay with me.=20 > > =20 > > Plus, extents are Physical blocks not logical. eg. 1 block of > 10 giga = > bytes or 10 blocks of 1 biga bytes.=20 > > =20 > > Have Fun.=20 > > =20 > Nisar Tareen=20 > > Ganesh Raja wrote: > Nisar, > > Did not Understand too much from your Post .. But How does all the > things that you have talked about affect the way parallelism works .. > Parallelism is CPU Bound and not disk bound ... U dictate if you want > to Use Parallelism and then The ur Query/DML is Split across multiple > processess that works out how to fetch data from the database and > processes it. > > I am disagreeing here with irfhan also when he says that Multiple > extents will give u chances of better parallelism. U can write a small > testcase with a One Extent Table and See what happens .. U will still > get Parallel Reads. > > The Only thing that has some effect on performance with Multiple > Extent is the MBRC value since an Multi Block read cannot be done over > multiple extents. [ U will never encounter this has a Problem] > > Apart from that i will never say that if you have 1000 Extents ur > Query is Going to Perform badly and if you have 1 Extent it will > perform any better. I need a test case from u to prove that the > otherway. > > Extent has always been a Logical Grouping of Blocks and not a > Physicall Grouping. > > My $0.02 and not my logic ... :) > > Rgds > Ganesh > > > =09 > --------------------------------- > Do you Yahoo!? > Check out the new Yahoo! Front Page. www.yahoo.com > > > > > > --------------------------------- > Do you Yahoo!? > Check out the new Yahoo! Front Page. www.yahoo.com > > This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.