Drop partition (big tabkes) elapsed time

[slimdave]

your partitions don't have something like 500,000 extents, do they?

[orajoe]

Thanks guys for the information (paralellism).

Truncating the partition will free all other extents but the initial, that the table and indexes has, right? If so, I would have all the initial extents wasted, but I will keep free all the others, correct?

I will try with the next partition, and I will let you know.

By the way, monitoring the system, I noticed that the smon is being saturated freeing a big bunch of temporary segments that didn't get released in another tablespace.

Is there some way to stop this? This tablespace is not being used anymore, and we were trying to drop it to reuse that space (datafiles) on other tablespace.

Thanks again, Joe.

P.S. Let me check about the number of extents for all indexes.

[orajoe]

I have checked, and it has 48 extents (50MB each) for all subpartitons of data, and +90,000 extents for all index subpartitions (2M each). This is for one partition of the big table.

Thanks.

[orajoe]

I have checked again, and we decided to rebuild and change parameters for those indexes. But, I guess that this operation will take a long too, because of the defragmentation of that tablespace.

I have heard that coalescing the indexes would help me, but I have my doubts since the smon will have to do the hard work also.

Does somebody know more less how long would it take? The tablespace of indexes has ~40GB.

Thanks, Joe.

[slimdave]

was that 90,000 extents per index, 2M total for all indexes (per partition)

[pando]

if you have 90000 extents using dictionary managed tablespace then it's quite obvious it takes long time to drop the partition. It's not the drop partition is slow, it is the operations in data dictionary that is slow

And no coalesce wont make things go faster

One thing I do when an operation is slow is look v$session_wait and look what is the waiting event

[orajoe]

Dave, the 90000 are the sum of extents of the 24 indexes of the big table (~3750 extents for each index).

Pando, I have looked into this table and it has more than 100 entries.
What info could I use here? The sessions that has more seconds in wait are Event:"SQL*Net message from client" p1text: "driver id" with more than 20K seconds waiting. Any clue that I should look for?

Thanks, Joe.