Thanks for dropping that pointer.
Printable View
Thanks for dropping that pointer.
Quote:
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.
Oh Yes, its again droping the pointers of the Mem Locs...Quote:
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".
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.
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?Quote:
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...
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?
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?Quote:
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?
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.
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".Quote:
Ahh..then it would take days to DROP a SCHEMA with 10000 objects.
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.Quote:
Delete is nothing but droping the pointer pointing to the MEM location where the record exist.
Why would you need a source code for that? The whole thing is crystally clear, documented and described in many manuals and books.Quote:
We would have been very clear if ORACLE would have had given source code on how DELETE, DROP all goes about.
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.Quote:
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.
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. ;)
what has SMON to do with all this?!
That was a really good explanation Jurij.
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
:D
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.
Code:===========
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.
My Point for all these demostration is as below..Code:=================
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...
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.
Quote:
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.
Quote:
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.
Well yes if many extents...it because..segment doesnt have header info...had it than it would have been drop of MEM loc to thisQuote:
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]
pointer rather than droping Pointer to all those extents.
If its DELETE it would be a PHYSICAL write of "0000..." to the loc where the row exist.Quote:
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]
I hope i am very clear this time.
Abhay.
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?
Abhay, i could not understand a single thing that you were trying to explain...
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" :)Quote:
Data stored at Disk Level has Mem Location ??
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. :)