Re: DELETE w/ROWID takes 6 minutes?

"ty" <tterrell_at_yahoo.com> schreef in bericht news:2362ca33.0211061205.5c85b473_at_posting.google.com...
> :::::::
> Server and DB: Solaris 7, Oracle 8.0.6
> SunOS FGSUN01 5.7 Generic_106541-14 sun4u sparc SUNW,Ultra-Enterprise
>
>
> I don't know how to interpret this trace file. Can ne1 helP?
>
> This is the fastest possible query plan for Oracle queries? This is
> very slow response time. Maybe there is something else running on the
> server that would bottleneck this query so badly. Needs further
> investigation. 6 minutes to effectively delete 0 rows is surprising.
> :::::::
>
> DELETE FROM REVENTS
> WHERE
> ROWID=:1
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.06 0.06 0 0 0
> 0
> Execute 21 388.32 414.65 363236 13068506 1328
> 0
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 22 388.38 414.71 363236 13068506 1328
> 0
>
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> Parsing user id: 18 (MP5)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 DELETE STATEMENT GOAL: RULE
> 0 DELETE OF 'REVENTS'
> 0 TABLE ACCESS GOAL: ANALYZED (BY USER ROWID) OF 'REVENTS'
This can happen if you have a big table that has a foreign key constraint to the table from which you are deleting the record, but no index on the foreign key columns. If you delete a record in the parent table, Oracle does a full table scan on the child table to check if the constraint is not violated.

HTH,
Thijs Blaauw Received on Thu Nov 14 2002 - 00:28:23 CET