Drop user takes long time...

[stecal]

Thanks for dropping that pointer.

[patel_dil]

Originally posted by jmodic
Oh, it takes much more for Oracle to drop a database object than just to "drop some poiters pointing to mem locations". It takes *many* updates/deletes/inserts into data dictionary tables and/or fixed tables to drop a single table.

For example, you drop a table, and oracle has to do:

- delete one row from OBJ$
- delete one or more rows from COL$
- probably delte some rows from IND$
- delete one or more rows from SEG$
- delete one or more rows from UET$
- insert one or more row into FET$
- probably delete some rows from IDL_* tables
- ...

And all those tables are quite heavily indexed and I have named just a few dictionary tables (perhaps less than 10%) that must be taken care of just for one simple DROP TABLE statement. So you can immagine all this work might take quite a significant amount of time. Not to speak about all the work that SMON has to perform afterwards to actually clean up the space in tablespaces.

So it is much more to do than just to drop few "pointers".

Jurij, As always, Your answers are always great.

[abhaysk]

Originally posted by jmodic
Oh, it takes much more for Oracle to drop a database object than just to "drop some poiters pointing to mem locations". It takes *many* updates/deletes/inserts into data dictionary tables and/or fixed tables to drop a single table.

For example, you drop a table, and oracle has to do:

- delete one row from OBJ$
- delete one or more rows from COL$
- probably delte some rows from IND$
- delete one or more rows from SEG$
- delete one or more rows from UET$
- insert one or more row into FET$
- probably delete some rows from IDL_* tables
- ...

So it is much more to do than just to drop few "pointers".

Oh Yes, its again droping the pointers of the Mem Locs...

Wat ever u mentioned is correct as per Logical Point...
like delete one row from N number of BASE tables.....for DROPing just one TABLE/OBJECT... THIS operation dosent take more than few milli sceonds... as i mentioned, its again droping pointer of the memory location...

What do you think DELETE operration will do...do u think Oracle/OS ( combined )will try to make 1's to 0's at datafile/hard disk level?

Ahh..then it would take days to DROP a SCHEMA with 10000 objects.

Delete is nothing but droping the pointer pointing to the MEM location where the record exist.

We would have been very clear if ORACLE would have had given source code on how DELETE, DROP all goes about.

Ofcource it will take some time for SMON to clean up / reorganise some DATA blocks in Tablespace...

But not as much the POSTER posted that it taking hours together to do a DROP operation...

Well in such case, i would assume that OS/Harware Config/System Config is not good enough to handle MEM operations...Or some where the PROCESS is struck.

Or there is really some problem with the ORACLE server in droping some of the objects or rather a particular object.

Abhay.

[jmodic]

Originally posted by abhaysk
Oh Yes, its again droping the pointers of the Mem Locs...

Wat ever u mentioned is correct as per Logical Point...
like delete one row from N number of BASE tables.....for DROPing just one TABLE/OBJECT... THIS operation dosent take more than few milli sceonds... as i mentioned, its again droping pointer of the memory location...

First we have some terminology isues here. When you say "pointers of the Mem Locs" - what exactly do you mean here? Pointers in RAM (SGA) that point to some other locations in RAM (SGA)? As this is what usually is understood as "pointers of the Mem Locs". And what has droping tables to do with SGA and pointers?

Or do you mean memory loactions that hold some addresses to certain storage (disk) locations?

Or do you mean storage (disk) loactions that hold some addresses to other storage (disk) locations?

Can you be more specific?

What do you think DELETE operration will do...do u think Oracle/OS ( combined )will try to make 1's to 0's at datafile/hard disk level?

Hm, and what do you think Oracle will do? Do some changes in SGA and that's it? You have some ideas about how transactions are processed in oracle, right? So you must know there must be something done at datafile/hard disk level, right? DROP table *is a transaction* and it can't be commited untill the changes are written to disks (redo log files at least) - is there any doubt about that?

Now of course DBWR will not try to zero-out every bit of the disk space table has occupied. But I have allready told you in previous reply what changes it will have to do to the datadictionary tables. And those changes ARE VERY PHISICAL, they must be performed in buffer cache and then they must be written to disks before the DROP TABLE can complete.

Ahh..then it would take days to DROP a SCHEMA with 10000 objects.

Well yes, if each of those tables would consist of hundreds or thousands of dictionary managed extents, then it actually takes days to drop the schema! Even if each table has only one extent it will take quite some time to drop schema with 10000 segments - have you tried something like this yet? I did. It certanly was taking much more than it would take Oracle to "drop some memory pointers".

Delete is nothing but droping the pointer pointing to the MEM location where the record exist.

Ah, here we are again. Where are those pointers? In memory? No. On disk? Yes, of course. More specificaly, in the header of database blocks where the records are stored. So what do you mean by "droping the pointer"? It has to perform write operation to database block holding that row. And there is nothing mystical about that - the modification has to be performed physicaly, on disks, no other way around this. Of course changes are made in buffer cache first (blocks need to be read into cache first if they are not there yet), but then those changes need to be written to disks. No other way.

We would have been very clear if ORACLE would have had given source code on how DELETE, DROP all goes about.

Why would you need a source code for that? The whole thing is crystally clear, documented and described in many manuals and books.

Ofcource it will take some time for SMON to clean up / reorganise some DATA blocks in Tablespace...

But not as much the POSTER posted that it taking hours together to do a DROP operation...

Well in such case, i would assume that OS/Harware Config/System Config is not good enough to handle MEM operations...Or some where the PROCESS is struck.

Or there is really some problem with the ORACLE server in droping some of the objects or rather a particular object.

Abhay.

It is so easy to perform a demonstration on any database where dropping a single table can take minutes or even hours to complete. It has been discused and demonstrated many times in this forum too, so you could try it yourself.

In any case, I recomend you to read Note:68836.1 (How To Efficiently Drop A Table With Many Extents) on Metalink. And some Concept Manual rereading would be beneficial, too.

[pando]

what has SMON to do with all this?!

[thomasp]

That was a really good explanation Jurij.

[Mr.Hanky]

Sure, I nail it with a simple explaination and Jurij gets all the credit! Ha, such is life.
Let's show a little love for the uneducated Juniors guess.

MH

[abhaysk]

Jurij:

PS see below demo to prove that the time taken for Modifying the BASE TABLES (DATA DICT) is not much. And yes time taken for DROPING segement will be more if it has many extents.

===========
USER 1
===========

TEST:SYS> ed
Wrote file afiedt.buf

  1  create tablespace DROP_USER datafile 'C:\oracle\oradata\test\Drop_User.dbf' size 100M autoextend on next 10M maxsize 200M
  2  extent management DICTIONARY
  3* default storage (initial 1K next 1K pctincrease 0 maxextents UNLIMITED)
TEST:SYS> /

Tablespace created.

TEST:SYS> ed
Wrote file afiedt.buf

  1  create user DROPING_USER identified by DROPING
  2* default tablespace DROP_USER
TEST:SYS> /

User created.

Elapsed: 00:00:01.03
TEST:SYS> grant dba,connect,resource to DROPING_USER;

Grant succeeded.

Elapsed: 00:00:02.02



 After creation I create 100 Segments in above user with only 1 segment having extents greater than 500.

TEST:SYS> conn DROPING_USER/DROPING
Connected.
TEST:SYS> set sqlprompt TEST:DROPING_USER>
TEST:DROPING_USER>ed
Wrote file afiedt.buf

  1  begin
  2  for i in 1..100 loop
  3  execute immediate ('CREATE TABLE TAB' || i ||' (ID number) tablespace drop_user');
  4  end Loop;
  5* end;
TEST:DROPING_USER>/

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.09

TEST:DROPING_USER>create table  tab101 (ID number) tablespace drop_user
  2  storage (initial 512 next 512 maxextents unlimited);

Table created.

TEST:DROPING_USER>begin
  2  for i in 1..1000000 loop
  3  insert into TAB101 values(i);
  4   end Loop;
  5   end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:02:17.01
TEST:DROPING_USER>commit;

Commit complete.

Elapsed: 00:00:00.00
TEST:DROPING_USER>select count(*) from dba_extents where segment_name='TAB101';


            COUNT(*)
--------------------
                 800

Elapsed: 00:00:01.05

 Now i drop the user using CASCADE 

TEST:DROPING_USER>conn / as sysdba
Connected.
TEST:DROPING_USER>drop user DROPING_USER cascade;

User dropped.

Elapsed: 00:00:52.00

Note it took 52 Secs.

=================
User 2
=================

 Here i create 300 segments with 2 segments having extents greater than 500. 
TEST:SYS> create TABLESPACE DROPING_TABLESPACE  datafile 'C:\oracle\oradata\test\Drop_tablespace.dbf' size 100M autoextend on next 10M maxsize 200M
  2  extent management DICTIONARY
  3  default storage (initial 1K next 1K pctincrease 0 maxextents UNLIMITED);

Tablespace created.

Elapsed: 00:00:04.08
TEST:SYS> create user DROP_TABLESPACE identified by DROPING
  2  default tablespace DROP_USER;

User created.

Elapsed: 00:00:00.00
TEST:SYS>  grant dba,connect,resource to DROP_TABLESPACE;

Grant succeeded.

Elapsed: 00:00:00.00
TEST:SYS> spool off

TEST:SYS>conn DROP_TABLESPACE/DROPing
Connected.
TEST:SYS>set sqlprompt TEST:DROP_TABLESPACE>
TEST:DROP_TABLESPACE>begin
  2  for i in 1..100 loop
  3  execute immediate ('CREATE TABLE TAB' || i ||' (ID number) tablespace drop_user');
  4  end Loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.07
TEST:DROP_TABLESPACE>begin
  2  for i in 101..300 loop
  3  execute immediate ('CREATE TABLE TAB' || i ||' (ID number) tablespace DROPING_TABLESPACE ');
  4  end Loop;
  5  end;
  6  /


PL/SQL procedure successfully completed.

Elapsed: 00:00:05.00

TEST:DROP_TABLESPACE>create table tab301 (ID number) tablespace DROPING_TABLESPACE
  2  storage (initial 512 next 512 maxextents unlimited);

Table created.

Elapsed: 00:00:00.00
TEST:DROP_TABLESPACE>create table tab302  (ID number) tablespace DROPING_TABLESPACE
  2  storage (initial 128 next 64  maxextents unlimited);

Table created.

TEST:DROP_TABLESPACE>select distinct tablespace_name, count(segment_name) from dba_segments where owner='DROP_TABLESPACE'
  2  group by tablespace_name;


TABLESPACE_NAME                 COUNT(SEGMENT_NAME)
------------------------------ --------------------
DROPING_TABLESPACE                              202
DROP_USER                                       100

TEST:DROP_TABLESPACE>ed
Wrote file afiedt.buf

  1* select tablespace_name,owner,extents  from dba_segments where segment_name in ('TAB302','TAB301')
TEST:DROP_TABLESPACE>/


TABLESPACE_NAME                OWNER                                       EXTENTS
------------------------------ ------------------------------ --------------------
DROPING_TABLESPACE             DROP_TABLESPACE                                 451
DROPING_TABLESPACE             DROP_TABLESPACE                                 752

Elapsed: 00:00:05.00

TEST:DROP_TABLESPACE>drop tablespace DROPING_TABLESPACE including contents;

Tablespace dropped.

Elapsed: 00:00:30.02

TEST:DROP_TABLESPACE>drop tablespace DROP_USER  including contents;

Tablespace dropped.

Elapsed: 00:00:07.06
TEST:DROP_TABLESPACE>
TEST:DROP_TABLESPACE>conn / as sysdba
Connected.
TEST:DROP_TABLESPACE>drop user DROP_TABLESPACE;

User dropped.

Elapsed: 00:00:05.04

If we add above timings..effectively we needed just 42 secs to drop user having 302 segments as compared to direct DROP USER
that we saw previously it took 52 secs to drop only 100 segments...

My Point for all these demostration is as below..

1) DROP ofcource is a TRAN and it will be recorded in REDOs...and also many DATA DICT BASE tables have to
be modified ( most of them being DELETE ) and wont take much time.

2) WE saw above that the sencond method infact took less time then first 1 and with more segments than in first..so we can infer that DELETING ( modifying )
DATA DICT BASE tables will not take much time.

3) Since we droped TABLESPACE..it has HEADER info since its associated with the FILE level sctucts...so DROP of these MEM Locs
by OS will not be a problem at all...BUT in case of DROP USER CASCASE, oracle/OS has to first ensure that all the segments
be droped and then USER. So, we know AT SEGEMNT level we dont have HEADER info as we have at EXTENT,BLOCK level.
So when a SEGEMNT is needed to be droped...it will try to drop the MEM locations of all extents...thats time consuming
as these extents allocated are not at file level...but the are inside FILES.
Obiviously if MANY extents are there, then it will take hell lots of time.
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.

Originally posted by jmodic
First we have some terminology isues here. When you say "pointers of the Mem Locs" - what exactly do you mean here? Pointers in RAM (SGA) that point to some other locations in RAM (SGA)? As this is what usually is understood as "pointers of the Mem Locs". And what has droping tables to do with SGA and pointers?

Or do you mean memory loactions that hold some addresses to certain storage (disk) locations?

Or do you mean storage (disk) loactions that hold some addresses to other storage (disk) locations?

Can you be more specific?

I mean POINTER to MEM Location as below...

DELETE ROW -> Data stored at Disk Level has Mem Location. This Mem Loction will be dropped by OS is wat i mean.
I dunno in detail about the Mem Allocation/De-allocation by OS, but i assume there will be a Pointer pointing
to the actual DATA Mem Loc ( Pointer to Pointer ).

Drop Table -> Now here comes a bit of Problem while droping Mem Locations...Problem is like at harddisk level the Contigious
Mem allocated ( Seeming ) will be so called Extent/BLOCK in oracle. If this command is issued oracle has to indentify
all such locations and be droped by OS.

Originally posted by jmodic
Hm, and what do you think Oracle will do? Do some changes in SGA and that's it? You have some ideas about how transactions are processed in oracle, right? So you must know there must be something done at datafile/hard disk level, right? DROP table *is a transaction* and it can't be commited untill the changes are written to disks (redo log files at least) - is there any doubt about that?

Now of course DBWR will not try to zero-out every bit of the disk space table has occupied. But I have allready told you in previous reply what changes it will have to do to the datadictionary tables. And those changes ARE VERY PHISICAL, they must be performed in buffer cache and then they must be written to disks before the DROP TABLE can complete.
[/B]

Ya ofcource Data Dict tables have to be updated ( rather entry/row deleted )..
So lets say we need to delete N rows from N different BASE tables...it wont require much time to DROP mem loc...
It will be PHYSICAL only when INSERT or UPDATE on table takes place.

Originally posted by jmodic
Well yes, if each of those tables would consist of hundreds or thousands of dictionary managed extents, then it actually takes days to drop the schema! Even if each table has only one extent it will take quite some time to drop schema with 10000 segments - have you tried something like this yet? I did. It certanly was taking much more than it would take Oracle to "drop some memory pointers".
[/B]

Well yes if many extents...it because..segment doesnt have header info...had it than it would have been drop of MEM loc to this
pointer rather than droping Pointer to all those extents.

Originally posted by jmodic
modification has to be performed physicaly, on disks, no other way around this. Of course changes are made in buffer cache first [/B]

If its DELETE it would be a PHYSICAL write of "0000..." to the loc where the row exist.

I hope i am very clear this time.

Abhay.

[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?

[adewri]

Abhay, i could not understand a single thing that you were trying to explain...

Data stored at Disk Level has Mem Location ??

What are mem loc on disks ?? Never heard such term in relation to Oracle. Just found one link related to RAID5 when i searched for "Memory Location on Disk" and found no match for "mem loc on disk"

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.