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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to project the time a delete will take

RE: How to project the time a delete will take

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sat, 31 Jan 2004 10:44:34 +0800
Message-Id: <5.1.1.6.0.20040131104352.00ab9b40@pop.singnet.com.sg>

Have you tried querying V$SESSION_LONGOPS ? {It requires TIMED_STATISTICS=TRUE and the table having been analyzed}

Hemant
At 04:42 PM 30-01-04 +0200, you wrote:
>Dear Nelson,
>
>Oracle release 8.1.7.4, solaris 8=20
>The entries that will be deleted are ~ 200 million . The customer is
>pushing for delete although we have pointed out that a partition will be
>wiser (we are using truncate now). The customer asked us to prove that
>the delete is not efficient although we have explained that there are a
>number of issues such as redo and undo etc. There are 3 indexes defined
>against the table.
>I am doing the following experiment
>a) get a reading from dbms_utility.get_time
>b) execute a delete for a row
>c) get a second reading from dbms_utility.get_time
>d) Check the diff between a and c which is 732 centiceconds (if I
>convert it well is 7,32 secs)
>
>Kind Regards,
>
>
>Hatzistavrou Yannis
>
>
>
>-----Original Message-----
>From: nelson.petersen_at_homehardware.ca
>[mailto:nelson.petersen_at_homehardware.ca]=20
>Sent: Friday, January 30, 2004 4:25 PM
>To: oracle-l_at_freelists.org
>Subject: RE: How to project the time a delete will take
>
>Hi John (Yannis?),
>
>Assumptions:
>
>1. You are deleting from one table.
>2. There are no referential constraint issues. (You have disabled or
>dropped them.)
>3. You are deleting 80% or more of the data from this table. =20
> (Anything less makes my suggestion less appropriate.)
>
>I don't have a way for you to estimate how long the delete will take.
>I have a suggestion, though.
>
>What version of Oracle are you running?
>
>If you're on Oracle 8.1.5 or higher (I think EXPORT has the QUERY option
>as
>of 8.1.5)
>you might consider the following:
>
>EXPORT ... QUERY=3D"WHERE ..." file=3Ddata_to_be_left.dmp etc.
> >>> The WHERE clause should select the data that you do *NOT* want to
>delete.
>
>Truncate the table.
>IMPORT file=3Ddata_to_be_left.dmp ... etc.
>
>This is a quick way to delete large amounts of data.
>REDO generated by IMPORT might be less than that generated by DELETE.
>
>As always, test on a test database before trusting this in production!
>If you are on earlier versions of Oracle, there are ways to do something
>similar
>using CREATE TABLE AS SELECT * FROM table1 WHERE
>data_col=3D'data_you_need_to_keep';
>
>Of course, it all depends on how much data you need to save.
>You don't say if this table has 101 million rows or 10,000 million rows.
>
>HTH,
>Nelson Petersen
>Database Administrator
>Home Hardware Stores Limited.
>Nelson.Petersen_at_homehardware.ca
>
>
>
>-----Original Message-----
>From: Hatzistavrou John [mailto:John.Hatzistavrou.sema_at_mail.tellas.gr]
>Sent: Friday, January 30, 2004 7:48 AM
>To: oracle-l_at_freelists.org
>Subject: How to project the time a delete will take
>
>
>Dear All,
>=20
>
>Is there a formula that can give an estimation on how long a delete
>operation will take on a tables to delete 100 million rows?
>
>=20
>
>Kind Regards,
>
>=20
>
>=20
>
>Hatzistavrou Yannis
>
>=20
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 24-Jan-04}



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Jan 30 2004 - 20:44:34 CST

Original text of this message

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