Drop user takes long time...

[abhaysk]

Originally posted by adewri
And if im not wrong then in the second case extents that you have created are all empty extents. So whats the big deal in showing how fast you can drop them. That will be fast anyway. And BTW you dropped the tablespaces before dropping the user, so while dropping the user you were just removing the user entries from Data Dictionary tables.

PS: Didn't read the whole thread, its sooooo confusing, if i read it completly then i think ill loose my mind or loose my concepts.

No extents are FULL...in second case as well...but jus didnt include all that code beacuse while submiting it prompted for only 100000 letters or so to be used.

Well had you read compelete post, then you would have known, Why in second case i droped Tablespace to see good response.

Point is.. as Jurij Said...

While DROPING tables will fire all DICT BASE tables to be modified and will take considerable time.

I said, it aint take much...
So when i DROPED the TS including contents, all Tables will also needed to be dropped right ( i mean DICT tables should be updated )...
Well had you done with DROP USER than to it needs to DROP all the TABLES ( I mean Modify DICT tables )...
Compare the effective times is what i said.

Abhay.

[abhaysk]

Originally posted by pando
not sure if yu know what you are talking about, segments has headers (well otherwise where are the freelists stored)?

The steps Oracle process DMLs:

Processing DML
A data manipulation language (DML) statement requires only two phases of processing,
1. Parse is the same as the parse phase used for processing a query.
2. Execute requires additional processing to make data changes.

DML Execute Phase
To execute a DML statement:
1. If the data and rollback blocks are not already in the buffer cache, the server process reads them from the data files into the buffer cache.
2. The server process places locks on the rows that are to be modified. The rollback block is used to store the before-image of the data, so that the DML statements can be rolled back if necessary.
3. The data blocks record the new values of the data.
4. The server process records the before image to the undo block and updates the data block. Both of these changes are done in the database buffer cache. Any changed blocks in the buffer cache are marked as dirty buffers. That is, buffers that are not the same as the corresponding blocks on the disk.
The processing of a DELETE or INSERT command uses similar steps. The before image for a DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.

Are you assumming all or you know how Oracle works internally?

This is all fine..and aint take much time is what i was trying to prove..( BASE table Modification )....but..forget it.

[adewri]

But dropping tablespaces is not an appropriate solution. A tablespace can host more than one schema objects.

So this solution would be specific.

[thomasp]

Abhay,

I can understand what you are trying to say, but to drop a user or schema , I dont think dropping the corresponding tablespace would be the best solution taking into consideration different database environments.

[abhaysk]

Originally posted by adewri
But dropping tablespaces is not an appropriate solution. A tablespace can host more than one schema objects.

So this solution would be specific.

Exactly..I also mentioned ( if you have read that LONG post )

Rather if we would have DROPED the tablespace which contains these segments it would be much faster.
So one need to degin there DB as to have all USER objects in a set of Tablespaces.

Set being Distinct for DIFFERNT USERS/SCHEMAS.

I think you are getting me now..i mean what really i wanted to convey...I was being LOGICAL in my approach.

Abhay.

[abhaysk]

Originally posted by thomasp
Abhay,

I can understand what you are trying to say, but to drop a user or schema , I dont think dropping the corresponding tablespace would be the best solution taking into consideration different database environments.

I am assuming it to be DW type DB.

Ofcourse you cant have N set of TS for N users in OLTP type of DBs..Well in such DBs i dont think there will necessity of having 10000 objects for particular schema.

Abhay.

[slimdave]

Originally posted by abhaysk
I am assuming it to be DW type DB.

Ofcourse you cant have N set of TS for N users in OLTP type of DBs..Well in such DBs i dont think there will necessity of having 10000 objects for particular schema.

Abhay.

This is a peoplesoft db, unless I'm much mistaken.


And why would dropping the tablespace be an improvement? You still have to drop the objects from the data dictionary, and then you have to recreate the TS anyway. I don't see the advantage.

Dropping a user with all those objects is going to take time. My advice is to just suck it up.

[abhaysk]

Originally posted by slimdave
And why would dropping the tablespace be an improvement? You still have to drop the objects from the data dictionary, and then you have to recreate the TS anyway. I don't see the advantage.

PS my posts on page 2.

[TomazZ]

abhaysk,
i retried your experiment with few modifications.

As you can see from the results:

METHOD 1:

SQL> drop user DROPING_USER cascade;

User dropped.

Elapsed: 00:00:49.02
SQL> drop tablespace DROP_USER;

Tablespace dropped.

Elapsed: 00:00:37.05

METHOD 2:

SQL> drop tablespace DROP_USER including contents;

Tablespace dropped.

Elapsed: 00:01:19.09
SQL> drop user DROPING_USER;

User dropped.

Elapsed: 00:00:08.03

Both methods take same amount of time!!!

Modifications used to even the experiments:
1. For both methods I used objects from you method 1 - the only difference from your method one is the dropping.
2. As you can see, in method 1 I included "drop tablespace" after dropping user to have the same job done - without this METHOD 1 IS EVEN FASTER!!!
3. Both times, after creating objects before dropping I restarted the database not to let caching to mess with my results.

Few other things:
- i was the only user on machine;
- it is solaris, not nt;
- INTERESTINGLY with your "inserting code" I got 3065 extents in TAB101, not 800.


I think you have to give more consideration in creating the same environment for experiments you do.