Home » RDBMS Server » Performance Tuning » delete is very slow (10.2.0.4)
delete is very slow [message #583173] Fri, 26 April 2013 08:54 Go to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Hi All,

I am running one simple delete statement in one table with rownum<10000 but it is taking nearly 10 to 15 mins.Table doesn't have any child table rows and triggers.

Please advice how to check that.

Thanks
Re: delete is very slow [message #583176 is a reply to message #583173] Fri, 26 April 2013 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And any index?

Regards
Michel
Re: delete is very slow [message #583178 is a reply to message #583176] Fri, 26 April 2013 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: delete is very slow [message #583179 is a reply to message #583176] Fri, 26 April 2013 09:12 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
No index. only primary key index and child table does not any rows.simple condition but it is taking nearly more time.

statement like

delete from <table> where rownum<10000;
Re: delete is very slow [message #583183 is a reply to message #583179] Fri, 26 April 2013 09:17 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member

CREATE TABLE ORDER_COMMODITY
(
  COMMODITY_ID        VARCHAR2(20 BYTE)         NOT NULL,
  ORDER_NUMBER        VARCHAR2(6 BYTE)          NOT NULL,
  ORDER_OFFLINE_DATE  DATE                      NOT NULL,
  DIFFERENCE_TYPE     NUMBER,
  PLANT_CODE          VARCHAR2(1 BYTE)          NOT NULL,
  K_SPEC_BATCH_NO     VARCHAR2(8 BYTE)
)
TABLESPACE SCS_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE PUBLIC SYNONYM ORDER_COMMODITY FOR ORDER_COMMODITY;


ALTER TABLE ORDER_COMMODITY ADD (
  PRIMARY KEY (COMMODITY_ID, ORDER_NUMBER)
    USING INDEX 
    TABLESPACE SCS_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));


ALTER TABLE ORDER_COMMODITY ADD (
  CONSTRAINT FK_ORD_DIFF_CMDTY_PLNT FOREIGN KEY (PLANT_CODE) 
    REFERENCES SCS_PLANT (PLANT_CODE));

ALTER TABLE ORDER_COMMODITY ADD (
  CONSTRAINT FK_ORD_DIFF_CMDTY_CMDTY FOREIGN KEY (COMMODITY_ID) 
    REFERENCES COMMODITY (COMMODITY_ID));


GRANT DELETE, INSERT, SELECT, UPDATE ON  ORDER_COMMODITY TO SCS_USER;



Explain Plan


Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart PStop

DELETE STATEMENT Optimizer Mode=ALL_ROWS		9 K	 	473  	 	      	             	 
  DELETE	SCS_OWNER.ORDER_COMMODITY	  	 	 	 	      	             	 
    COUNT STOPKEY		  	 	 	 	      	             	 
      TABLE ACCESS FULL	SCS_OWNER.ORDER_COMMODITY	366 K	4 M	473  	 	      	             	 



I could not able to provide the trace file.

Please advice.
Re: delete is very slow [message #583184 is a reply to message #583179] Fri, 26 April 2013 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
only primary key index


Which is an index.

Bad IO accesses, too much commit... many reasons can apply, run Statspack/AWR report, check statistics of the session, check its wait events, activate a trace on it...

Regards
Michel
Re: delete is very slow [message #583185 is a reply to message #583184] Fri, 26 April 2013 09:27 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
No statements are running except delete statement. I am checking the active select statements by using the following query.

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece;




I am getting only one delete statement.
Re: delete is very slow [message #583187 is a reply to message #583185] Fri, 26 April 2013 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has no relation with what I said.

Regards
Michel
Re: delete is very slow [message #583189 is a reply to message #583187] Fri, 26 April 2013 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
>delete from <table> where rownum<10000;

why not just TRUNCATE <TABLE>?
Re: delete is very slow [message #583190 is a reply to message #583189] Fri, 26 April 2013 10:12 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
User doesn't have privileges of truncate. Granted the privileges like select,insert,delete,update only.

I will provide the awr report soon.
Re: delete is very slow [message #583191 is a reply to message #583190] Fri, 26 April 2013 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the privilege is needed then it should be granted.

Regards
Michel
Re: delete is very slow [message #583222 is a reply to message #583191] Sat, 27 April 2013 23:21 Go to previous messageGo to next message
Kevin Meade
Messages: 1943
Registered: December 1999
Location: Connecticut USA
Senior Member
The simple fact is, DELETE can take a long time. If there are no other conflicting SQL running while you are doing this delete than you may just be seeing the limitations of your hardware and software.

Still, given that on today's hardware, Oracle can load a million rows/minute without breaking a sweat or using any advanced features, it seems odd that it would take 15 minutes to delete 10K rows. It may pay to do some research. Try the following:
make a copy of the table without indexes and try deleting
make a copy of the table with indexes and try deleting
compare these to your current timings

choose a column value that maps to about 10,000 rows and then repeat the above steps using the column=value instead of rownum<=10000.

turn off logging and repeat all the above


These tests will give you something to think about.

Kevin
Re: delete is very slow [message #583422 is a reply to message #583173] Tue, 30 April 2013 11:38 Go to previous messageGo to next message
grpatwari
Messages: 198
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you.

I will try this.
Re: delete is very slow [message #584193 is a reply to message #583422] Thu, 09 May 2013 16:53 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Here are some Ideas that I have implemented to reduce delete times.
1) Delete as many indexes as possible from the table you are deleting from.
2) Disable all triggers from this table.
3) Disable foreign keys from this table to other tables.
3) Do the deletes on one RAC node to reduce contention.
4) Look at your event waits to make sure you are not locking on something else like "latch free" or "log buffer".
I have had corrupt indexes show up as "Controlfile sequential read" and corrupt undo segments slow deletes. Unless you do this steps you cannot know for sure what is slowing the system up.
You can just rebuild the indexes on this table to insure that they are not corrupt. I had this problem more in 10g than in 11g.
ECSCDAP1P > @active
SEC_WAIT USERNAME  EVENT
-------- --------- ---------------------------------------
       0 CDA_RO_US enq: FB - contention
       0 CDA_RO_US enq: FB - contention
       0 CDA_RO_US gc buffer busy acquire
       0 CDA_RO_US gc buffer busy release
       0 CDA_RO_US gc current multi block request
       0 CDA_RO_US gc current multi block request
       0 CDA_RO_US gc current multi block request
       0 CDA_RO_US gc current request
       0 CDA_RO_US latch: cache buffers chains
       0 CDA_RO_US latch: cache buffers chains
       0 CDA_RO_US PX Deq: reap credit
       0 CDA_RO_US SQL*Net more data from client
       0 CDA_RO_US SQL*Net more data from client
       0 CDA_RO_US SQL*Net more data from client
       0 ECSCDAP1P DFS lock handle
       0 ECSCDAP1P enq: CR - block range reuse ckpt
       0 ECSCDAP1P gc current multi block request
       0 ECSCDAP1P row cache lock
       0 SYS       ASM file metadata operation
       1 CDA_RO_US gc current request
       1 CDA_RO_US gc current request
       1 CDA_RO_US log file switch (checkpoint incomplete)
       5 CDA_RO_US gc current request
       6 CDA_RO_US gc current request
       6 CDA_RO_US gc current request
ECSCDAP1P > list
  1  SELECT sid,
  2         serial#,
  3         machine,
  4         To_char(s.logon_time, 'DD-MON-RR HH24:MI') login,
  5         i.instance_name    db,
  6         s.seconds_in_wait   sec_wait,
  7         s.username,
  8         s.event,
  9         s.status,
 10         s.program,
 11         s.machine,
 12         s.MODULE,
 13         s.terminal
 14  FROM   gv$session s,
 15         gv$instance i
 16  WHERE  i.inst_id = s.inst_id
 17         AND s.status = 'ACTIVE'
 18         AND s.username IS NOT NULL
 19         AND s.wait_class <> 'Idle'
 20* ORDER  BY seconds_in_wait

5) In 11g Turn result cache off on the table you are deleting from. "alter table scott.emp result_cache (mode manual);"
6) Cache the table and its indexes into the keep buffer pool to reduce physical reads.
7) Look at what is hogging memory because these other objects will cause the table to get LRU and cause deletes to lock more.
ENWEBP1P > SELECT
2  o.object_type,i.instance_name db,COUNT(*)*8192/1024/1024 meg_in_memory,
3  o.owner||'.'||o.OBJECT_NAME Object_in_Memory
4       FROM DBA_OBJECTS o, gV$BH bh, gv$instance i
5      WHERE o.DATA_OBJECT_ID = bh.OBJD
6      and bh.status<>'free'
7      and bh.inst_id = i.inst_id
8      GROUP BY o.owner||'.'||o.OBJECT_NAME,o.object_type,i.instance_name
9      having count(*)>1024*100
10      ORDER BY COUNT(*);

OBJECT_TYPE DB       MEG_IN_MEMORY OBJECT_IN_MEMORY
----------- -------- ------------- -----------------------------------------
TABLE       NWEBP2             854 ALFC.NFL_ABSTRACT_CONTENT
TABLE       NWEBP3             856 ALFC.NFL_ABSTRACT_CONTENT
TABLE       NWEBP4             858 ALFC.NFL_ABSTRACT_CONTENT
TABLE       NWEBP4            1416 CONTENT.NFL_DOCBASE_PROPERTIES1_S
TABLE       NWEBP2            1435 CONTENT.NFL_DOCBASE_PROPERTIES1_S
TABLE       NWEBP1            1533 ALFC.NFL_ABSTRACT_CONTENT
TABLE       NWEBP1            1536 CONTENT.NFL_DOCBASE_PROPERTIES1_S
TABLE       NWEBP1            2813 PROFILE.REGISTRATIONS
TABLE       NWEBP4            2813 PROFILE.REGISTRATIONS
TABLE       NWEBP3            3184 PROFILE.REGISTRATIONS
TABLE       NWEBP2            3632 PROFILE.REGISTRATIONS

8) See if any other sessions are doing writes. I look at the active undo segments with the following sql. If no segments are active, then no other session is doing inserts or updates or deletes.
ECSCDAP1P > @r

  SID SERIAL# USERNAME         UndoBLKS TERMINAL        OSUSER   START_TIME        NAME       STATUS
----- ------- -------------- ---------- --------------- -------- ----------------- ---------- ------
 8769   14823 ECSCDAP1P             587 KENDALLA-LAL7   alan.ken 05/09/13 14:47:45 _SYSSMU10_ ACTIVE

ECSCDAP1P > list
select s.sid,s.serial#,username,t.used_ublk "UndoBLKS", terminal, osuser,
       t.start_time, r.name,
       decode(t.space, 'YES', 'SPACE TX',
          decode(t.recursive, 'YES', 'RECURSIVE TX',
             decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
       )) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
  and t.ses_addr = s.saddr;

Many times you can purge a table that is hogging memory or better index it to relieve the impact on your system and more important tables.


[EDITED by LF: disabled smilies in this message]

[Updated on: Fri, 10 May 2013 02:04] by Moderator

Report message to a moderator

Previous Topic: Query performance tuning for ETL
Next Topic: why this query is utilizing my undo so rapidly?
Goto Forum:
  


Current Time: Thu Oct 23 07:27:24 CDT 2014

Total time taken to generate the page: 0.06137 seconds