20,000 row delete takes 30 minutes! Why?

[abhaysk]

Originally posted by DaPi
That way you can do the delete on the child table with a FTS too.

yes ofcourse, but while deleting parent oracle still has to look up for the existance of record in the child..(index look up)

[marist89]

Originally posted by pando
so you stil not gonna post explain plan?

exactly. At this point, we're just guessing...

[Mr.Hanky]

As long as we are guessing I still say SHOOT THE DEVELOPERS!!.


What the heck, it's Friday!!



MH

[DaPi]

Originally posted by Mr.Hanky
As long as we are guessing I still say SHOOT THE DEVELOPERS!!.

I'm working on Sondheims's "Sweeney Todd" - so I hope to have some new techniques available soon.

[sbasak1]

I still couldn't find out what the cause of the problem. However, to
work with, I exported the schema, dropped the tablespace and re-created tablespace, imported schema. Now it works fine.

However, for those who asked about explain plan & trace file when
problem occurred, I present the same.

Database was Oracle 9.2 running on WinXP Pro in archive log mode. The machine had 80GB HDD with 128MB RAM & P3 800MHz CPU.

----------------------
EXPLAIN PLAN
SET STATEMENT_ID = 'PRODUCT DELETE'
FOR
DELETE FROM PRODUCT
WHERE PRODCODE LIKE '99%'
AND LENGTH(PRODCODE)=7
/

SELECT LPAD(' ',4*(LEVEL-2)) || OPERATION || ' ' || OPTIONS || ' ' ||
OBJECT_NAME "EXECUTION_PLAN", IO_COST, CPU_COST,TEMP_SPACE
FROM PLAN_TABLE
START WITH ID = 0 CONNECT BY PRIOR ID = PARENT_ID
AND STATEMENT_ID = 'PRODUCT DELETE'
/

EXECUTION_PLAN IO_COST CPU_COST

TEMP_SPACE
-------------------------------------------------- ---------- ----------

----------
DELETE STATEMENT 1
DELETE PRODUCT
INDEX RANGE SCAN PK_PRODUCT 1


*************** some lines from user trace file ***************

Instance name: mdb

Redo thread mounted by this instance: 1

Oracle process number: 15

Windows thread id: 3868, image: ORACLE.EXE


*** 2004-06-22 21:22:16.000
*** SESSION ID:(16.20175) 2004-06-22 21:22:16.000
FATAL ERROR IN TWO-TASK SERVER: error = 12571
*** 2004-06-22 21:22:17.000
ksedmp: internal or fatal error
ORA-01089: immediate shutdown in progress - no operations are permitted
Current SQL statement for this session:
DELETE FROM MKM.PRODUCT WHERE PRODCODE LIKE '99%' AND

LENGTH(PRODCODE)=7
----- Call Stack Trace -----
calling call entry argument values in

hex
location type point (? means dubious

value)
-------------------- -------- --------------------

----------------------------
_ksedmp+147 CALLrel _ksedst+0
__VInfreq__opitsk+3 CALLrel _ksedmp+0 2 25C34D4 311B
eb
_opiino+5fc CALLrel _opitsk+0 0 0 5639320 5C95FCC

E2 0
_opiodr+4cd CALLreg 00000000 3C 4 5C1FBD4
_opidrv+233 CALLrel _opiodr+0 3C 4 5C1FBD4 0
_sou2o+19 CALLrel _opidrv+0
_opimai+10a CALLrel _sou2o+0
_OracleThreadStart@ CALLrel _opimai+0
4+35c
77E802EA CALLreg 00000000

--------------------- Binary Stack Dump ---------------------

========== FRAME [1] (_ksedmp+147 -> _ksedst+0) ==========
Dump of memory from 0x05C1E69C to 0x05C1E714
5C1E690 05C1E714 [....]
5C1E6A0 00691AB4 00000000 00000000 00000000 [..i.............]
5C1E6B0 00000000 00000000 05633048 6111978F [........H0c....a]
5C1E6C0 00000000 00000000 00000000 025B43F0 [.............C[.]
5C1E6D0 0000002E 0000002E FA3E1D54 0000002D [........T.>.-...]

.....................

col 7: *NULL*
col 8: [ 1] 80
col 9: [ 1] 80
col 10: *NULL*
col 11: *NULL*
col 12: *NULL*
col 13: *NULL*
col 14: *NULL*
col 15: *NULL*
col 16: *NULL*
col 17: *NULL*
col 18: *NULL*
col 19: *NULL*
col 20: [ 3] 4d 4b 4d
col 21: [ 7] 78 68 06 16 15 0a 33
col 22: [ 1] 80
tab 0, row 37, @0x7b6
tl: 53 fb: --H-FL-- lb: 0x0 cc: 23
col 0: [ 7] 39 39 35 30 30 32 31
col 1: [

........................

*-----------------------------
* Rec #0x18 slt: 0x17 objn: 10420(0x000028b4) objd: 10420 tblspc:

8(0x00000008)
* Layer: 10 (Index) opc: 22 rci 0x17
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0005.01d.00000159 uba: 0x00800458.0059.0c
flg: C--- lkc: 0 scn: 0x0000.00214ef0
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=92562256 indexid=0x200028b

block=0x01c00413
purge leaf row
key :(8): 07 39 39 36 39 38 31 36
...........................

row#207[7844] flag: -----, lock: 0, data:(6): 02 00 02 84 00 2d
col 0; len 7; (7): 39 39 35 30 31 34 38
row#208[7860] flag: -----, lock: 0, data:(6): 02 00 02 84 00 2e
col 0; len 7; (7): 39 39 35 30 31 34 39
row#209[7876] flag: -----, lock: 0, data:(6): 02 00 02 84 00 2f
col 0; len 7; (7): 39 39 35 30 31 35 30
row#210[7892] flag: -----, lock: 0, data:(6): 02 00 02 84 00 30
col 0; len 7; (7): 39 39 35 30 31 35 31
row#211[7908] flag: -----, lock: 0, data:(6): 02 00 02 84 00 31
col 0; len 7; (7): 39 39 35 30 31 35 32
row#212[7924] flag: -----, lock: 0, data:(6): 02 00 02 84 00 32
col 0; len 7; (7): 39 39 35 30 31 35 33

****************************** end of trace **************

[DaPi]

(guess I lose my 5¢ )

INDEX RANGE SCAN PK_PRODUCT 1

No stats, using RBO? What does it use after recreating the schema?

[marist89]

Originally posted by JMac
Oh ye of little faith. (starts holding breath)

exhale.

[CaoCao]

Originally posted by sbasak1

Database was Oracle 9.2 running on WinXP Pro in archive log mode. The machine had 80GB HDD with 128MB RAM & P3 800MHz CPU.

128MB or RAM it is way too little. Should have at least 512MB for Oracle DB. Even my test machine's with fast CPU and HD is choking with 256MB of RAM running WinXP.