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;

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  	 	      	             	 

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;

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

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

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

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;