Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DROP TABLE takes ages - why?

Re: DROP TABLE takes ages - why?

From: David Fitzjarrell <oratune_at_aol.com>
Date: Wed, 01 Nov 2000 16:22:00 GMT
Message-ID: <8tpfv0$3dg$1@nnrp1.deja.com>

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




rem Per user memory requirement:
880,352
rem Number of users :
40
rem


rem Total memory for users :
35,214,080
rem Size of stuff in shared SQL:
5,872,413
rem


rem Base shared pool size :
41,086,493
rem Pool size with 30% free :
53,412,441
rem


rem
rem The final value, 53,412,441, is a calculated value based on the actual shared
rem SQL pool and the total EXPECTED memory usage for the number of users specified
rem based on the ACTUAL memory usage for the user session provided for reference
rem
rem The shared_pool_size parameter should be rounded up to the nearest whole figure,
rem i.e., 53,412,441 should be result in the following entry in the init*.ora file:
rem
rem shared_pool_size = 60000000
rem
rem The database should be restarted with this new parameter from the svrmgrl or sqldba
rem prompt, to view the messages provided by Oracle at startup. If the database won't
rem start with the new shared pool size, UNIX system parameters and actual physical
rem memory should be analyzed and adjustments made accordingly 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




select 'Per user memory requirement: ', &pumem pmem from dual;
select 'Number of users : ', &numusers nu from dual;
prompt


select 'Total memory for users : ', &numusers*&pumem tmu from dual;
select 'Size of stuff in shared SQL: ', &spl sss from dual;
prompt


select 'Base shared pool size : ', &size1 s1 from dual;
select 'Pool size with 30% free : ', &size2 s2 from dual;
prompt


spool off

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US