Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DROP TABLE takes ages - why?
In our last gripping episode "Andy Kent"
<andykent.bristol.DontSpamMe_at_virgin.net> wrote:
> Why does it take so long to drop a table when it's fully populated?
Is there
> some way I can reduce the time?
>
> I do know that some of my tables have lots of extents - could this
have be
> of any relevance?
>
> Thanks
>
> -Andy Kent-
> Remove '.DontSpamMe' from return address to reply by email.
>
>
The greater the number of extents a table has increases the time required to drop that table.
To decrease, even slightly, the time required to drop a fully loaded table try the solution TurkBear suggested -- truncate the table prior to dropping it. Truncate seems to work faster than drop, especially on large tables, and the resulting empty table and single extent will drop much faster.
As for your second question, about taking time to create tables with large initial extents, large extents take time to initialize and, as such, the larger the extent the more time it will require to prepare the extent for use. I have not had any problems with excessive time on table creates or drops, even large, fully populated tables. I have noticed such problems on systems where the shared pool was undersized for the load. You might wish to verify that your shared pool is large enough.
rem "$Header: /usr/users/davidf/tuning/sga/RCS/shared_pool.sql,v 1.1
2000/03/24 15:55:54 davidf Exp $"
rem shared_pool.sql
rem
rem Estimates the required SGA for the current database instance
rem
rem Estimates include a 30% increase in calculated values
rem Ask for a user name and the number of concurrent logins
rem
rem To obtain a fairly "padded" value, select a user who uses a large
rem allocation of memory, and is currently occupying one user session;
rem this can, however, backfire, as the SGA calculated could well exceed
rem the total memory of the system
rem
rem Example
rem
rem User : ims
rem # of users: 40
rem
rem Tuesday , January 13, 1998
10:38:47
rem
rem
rem
rem
accept username prompt 'User : '
accept numusers prompt '# of users: '
rem Get the users SID
set term off
col a new_value snum
create table maxmem
tablespace tools
as select sid, max(value) value
from v$sesstat
where statistic# = 16
group by sid;
select m.sid a
from v$process p, v$session s, maxmem m
where s.sid = m.sid
and p.addr = s.paddr and s.username = upper('&username') and m.value = (select max(value) from maxmem);
rem With the SID in hand, we can now obtain
rem the memory allocation for this user from
rem v$sesstat with a STATISTIC# = 16
rem which is the MAX SESSION MEMORY per user
col b new_value pumem
select value b
from v$sesstat
where statistic# = 16
and sid = &snum;
rem Get the amount of memory in the shared pool rem currently in use
col c new_value spl
select sum(sharable_mem) c
from v$sqlarea;
rem Calculate optimal shared pool size
rem
rem optimal size = 1.3 * ((per_user_memory * # of users) + size of sql
in pool)
col d new_value size1
col e new_value size2
select ((&pumem*&numusers)+&spl) d,
((&pumem*&numusers)+&spl) + 3/10 *((&pumem * &numusers) + &spl) e from dual;
rem Output results
rem
rem Also spool to a report file
col pmem form 999,999,990
col nu like pmem
col sss like pmem
col tmu like pmem
col s1 like pmem
col s2 like pmem
set term on
set echo off verify off feedback off pagesize 0
spool $ORACLE_HOME/tuning/reports/shared_pool.rpt
select to_char(sysdate, 'Day, Month DD, IYYY HH24:MI:SS')
from dual;
prompt
prompt
prompt
prompt
drop table maxmem;
exit
-- David Fitzjarrell Oracle Certified DBA Sent via Deja.com http://www.deja.com/ Before you buy.Received on Wed Nov 01 2000 - 10:22:00 CST