Oracle Database Discussions

We are running the following query...

delete gemdev.lu_messagecode where mess_code ='SSY'

and it takes way too long as there is only 1 record in this table with SSY as the mess_code.

SQL> set timing on;
SQL> delete gemdev.lu_messagecode where mess_code ='SSY';

1 row deleted

Executed in 293.469 seconds

The table structure is very simple as you can see below.

CREATE TABLE GEMDEV.LU_MESSAGECODE
(
MESS_CODE VARCHAR2(3) NOT NULL,
ROUTE_CODE VARCHAR2(4) NULL,
REPORT_CES_MNEMONIC VARCHAR2(3) NULL,
CONSTRAINT SYS_IOT_TOP_52662
PRIMARY KEY (MESS_CODE)
VALIDATE
)
ORGANIZATION INDEX
NOCOMPRESS
TABLESPACE IWORKS_IOT
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
PCTTHRESHOLD 50
NOPARALLEL
/
ALTER TABLE GEMDEV.LU_MESSAGECODE
ADD CONSTRAINT LU_ROUTECODE_FK3
FOREIGN KEY (ROUTE_CODE)
REFERENCES GEMDEV.LU_ROUTECODE (ROUTE_CODE)
ENABLE
/
ALTER TABLE GEMDEV.LU_MESSAGECODE
ADD CONSTRAINT MSGCODE_FK_CESMNEMONIC
FOREIGN KEY (REPORT_CES_MNEMONIC)
REFERENCES GEMDEV.SYS_CESMNEMONIC (CES_MNEMONIC)
ENABLE
/

My explain reads as follows.
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 1 (100)| |
| 1 | DELETE | LU_MESSAGECODE | | | | |
| 2 | INDEX UNIQUE SCAN| SYS_IOT_TOP_52662 | 1 | 133 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Also in my AWR Sql Report I see this as well
Plan Statistics DB/Inst: IWORKSDB/iworksdb Snaps: 778-780
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 521,102 N/A 12.0
CPU Time (ms) 73,922 N/A 5.1
Executions 0 N/A N/A
Buffer Gets 2,892,144 N/A 3.4
Disk Reads 2,847,609 N/A 8.6
Parse Calls 1 N/A 0.0
Rows 0 N/A N/A
User I/O Wait Time (ms) 475,882 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 2 N/A N/A
Invalidations 1 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 45 N/A N/A
-------------------------------------------------------------

Now, since the table only has 150 rows, and I am only try to delete 1 row, why is there so much disk read and why does it take 5 minutes to delete? This just weird. Does this have something to do with the Child tables?