Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, daniesh.

Asked: February 17, 2008 - 3:21 pm UTC

Last updated: February 18, 2008 - 10:01 am UTC

Version: 10.2.3

Viewed 1000+ times

You Asked

HI Tom,
I had gone for an interview some days before.The interviewer asked me that if a tablespace has 30 extents is it good or bad?? I said it depends on the size of the tablespace. could you please explain me its good or bad??
if its why?? and not good???
based on this question i didnt get selected. could you please explain me its good or bad, why????


Thanks in advance,
Daniesh

and Tom said...

It is neither good nor bad.

It is what it is.

30 is a teeny tiny number. A tablespace with only 30 extents allocated it in it probably a tablespace dying to get a BUNCH more data (you typically do not talk of tablespaces having "extents" inasmuch as you would a SEGMENT like a table or index having them).

All you could say is:

that is either a dictionary managed tablespace with 30 free extents that have not been coalesced by SMON yet.

Or, it is a tablespace with 100% of space allocated and 30 segments with one extent each.

or it could be a tablespace with 1 segment - 29 extents allocated to it and on big free one left.

Or it could be anywhere between 1 and 30 segments with between 1 and 30 extents allocated to it.


In any case - there are other cases that could happen - but the bottom line would be "insufficient data to say anything about anything, and I hope - really hope you were not looking for me to say 'oh gosh - you need to reorganize that object to have a single extent, since those are best' - we learned 20 years ago that isn't true, wasn't true and won't be true"

They probably were though, so in a way, consider yourself lucky. You dodged a bullet there.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

need more understanding

daniesh shaikh, February 18, 2008 - 9:43 am UTC

Hi Tom,
could you please explain me the more specificially.
you mean to say if we have lots of extents then reorganization problem will be more

Thanks,
Danish
Tom Kyte
February 18, 2008 - 10:01 am UTC

you missed the point entirely.


the question was nonsensical, meaningless...


Suffice to say - the number of extents will not readily affect the runtime performance of

a) an index access (cannot, we read a rowid and go to that row - we do not care if there is 1 or 1,000,000,000 extents - the results would be the same)

b) a full scan - since the vast preponderance of the time is spent actually doing physical IO - the amount of time we'd spend managing our way through 1 or thousands/millions of extents would be meaningless.


So, I think the person asking the question was trying to see if you would do something about a segment in 30 extents.

I would do nothing, other than say "job well done, lets get on with it now"

daniesh shaikh, February 18, 2008 - 10:18 am UTC

ok Tom
you mean he was trying to check me. i will keep in next time when i will go for interviews.anyways thanks for clearing my doubts.