Delete query takes too long

There could be several explanations as to why your query takes a long time:

  1. You could be blocked by another session (most likely). Before you delete you should make sure noone else is locking the rows, eg: SELECT NULL FROM tablename WHERE colname=:value FOR UPDATE NOWAIT,
  2. There could be a ON DELETE TRIGGER that does additional work,
  3. Check for UNINDEXED REFERENCE CONSTRAINTS pointing to this table (there is a script from AskTom that will help you determine if such unindexed foreign keys exist).

— script to find unindexed columns 1

set pagesize 5000
set linesize 350
column status        format a10
column table_name    format a30
column fk_name       format a30
column fk_columns    format a30
column index_name    format a30
column index_columns format a30

SELECT
CASE
WHEN b.table_name IS NULL THEN ‘unindexed’
ELSE ‘indexed’
END AS status,
a.table_name AS table_name,
a.constraint_name AS fk_name,
a.fk_columns AS fk_columns,
b.index_name AS index_name,
b.index_columns AS index_columns
FROM
(
SELECT
a.table_name,
a.constraint_name,
listagg(
a.column_name,
‘,’
) WITHIN GROUP(
ORDER BY
a.position
) fk_columns
FROM
dba_cons_columns a,
dba_constraints b
WHERE
a.constraint_name = b.constraint_name
AND b.constraint_type = ‘R’
AND a.owner = ‘&&schemaname’
AND a.owner = b.owner
GROUP BY
a.table_name,
a.constraint_name
) a,
(
SELECT
table_name,
index_name,
listagg(
c.column_name,
‘,’
) WITHIN GROUP(
ORDER BY
c.column_position
) index_columns
FROM
dba_ind_columns c
WHERE
c.index_owner = ‘&&schemaname’
GROUP BY
table_name,
index_name
) b
WHERE
a.table_name = b.table_name(+)
AND b.index_columns(+) LIKE a.fk_columns || ‘%’
ORDER BY
1 DESC,
2;

—- script to find unindexed columns

SELECT
table_name,
constraint_name,
cname1 || NVL2( cname2, ‘,’ || cname2, NULL )|| NVL2( cname3, ‘,’ || cname3, NULL )|| NVL2( cname4, ‘,’ || cname4, NULL )|| NVL2( cname5, ‘,’ || cname5, NULL )|| NVL2( cname6, ‘,’ || cname6, NULL )|| NVL2( cname7, ‘,’ || cname7, NULL )|| NVL2( cname8, ‘,’ || cname8, NULL ) columns
FROM
(
SELECT
b.table_name,
b.constraint_name,
MAX( DECODE( POSITION, 1, column_name, NULL )) cname1,
MAX( DECODE( POSITION, 2, column_name, NULL )) cname2,
MAX( DECODE( POSITION, 3, column_name, NULL )) cname3,
MAX( DECODE( POSITION, 4, column_name, NULL )) cname4,
MAX( DECODE( POSITION, 5, column_name, NULL )) cname5,
MAX( DECODE( POSITION, 6, column_name, NULL )) cname6,
MAX( DECODE( POSITION, 7, column_name, NULL )) cname7,
MAX( DECODE( POSITION, 8, column_name, NULL )) cname8,
COUNT(*) col_cnt
FROM
(
SELECT
SUBSTR( table_name, 1, 30 ) table_name,
SUBSTR( constraint_name, 1, 30 ) constraint_name,
SUBSTR( column_name, 1, 30 ) column_name,
POSITION
FROM
user_cons_columns
) a,
user_constraints b
WHERE
a.constraint_name = b.constraint_name
AND b.constraint_type = ‘R’
GROUP BY
b.table_name,
b.constraint_name
) cons
WHERE
col_cnt > ALL(
SELECT
COUNT(*)
FROM
user_ind_columns i
WHERE
i.table_name = cons.table_name
AND i.column_name IN(
cname1,
cname2,
cname3,
cname4,
cname5,
cname6,
cname7,
cname8
)
AND i.column_position <= cons.col_cnt
GROUP BY
i.index_name
);

Oracles principle is NOT (most definitely NOT) that you should ‘delay’ a commit. Not at all. The only principle is that a transaction should be:

a) as short as it can be
b) AS LONG AS IT NEEDS

that is, do not commit a partial transaction “because it is easier on the database” — that would be a really bad idea (but a common practice in many other databases — a very very bad practice). You commit immediately after executing the last statement in the series of statements that constitute your transaction. Not a moment before

original: https://stackoverflow.com/questions/1327190/oracle-delete-query-taking-too-much-time

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s