OraFAQ Forum: Performance Tuning » Tuning data deletion that is taking long time

Home » RDBMS Server » Performance Tuning » Tuning data deletion that is taking long time (11.2.0.4 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning data deletion that is taking long time [message #639604]

Sat, 11 July 2015 09:46

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi,

I have a delete sql in my application that is taking huge time - it deletes about 214K records from a table after doing a NOT EXISTS check in another table and it takes 1 minute and 47 seconds. Here is the DDL to create those 2 tables and its indexes etc.

CREATE TABLE USERS_RULE_CROSS_RELATION
(
  CLIENTID  VARCHAR2(16 BYTE)                 NOT NULL,
  GROUPID   NUMBER(12)                        NOT NULL,
  RULEID    NUMBER(12)                        NOT NULL,
  USERID    VARCHAR2(16 BYTE)                 NOT NULL
);


	
CREATE TABLE USERS_GROUP_CROSS_RELATION
(
  CLIENTID      VARCHAR2(16 BYTE)             NOT NULL,
  USERID        VARCHAR2(16 BYTE)             NOT NULL,
  GROUPID       NUMBER(12)                    NOT NULL,
  INCLUDED        NUMBER(1)                     NOT NULL,
  IS_ADDED  NUMBER(1)                     NOT NULL,
  CREATED_DATE    DATE                              NULL,
  CREATED_BY      VARCHAR2(80 BYTE)                 NULL,
  MODIFIED_DATE   DATE                              NULL,
  MODIFIED_BY     VARCHAR2(80 BYTE)                 NULL,
  TRANSACTION_ID  VARCHAR2(250 BYTE)                NULL
);


CREATE INDEX USERS_GROUP_CROSS_REL_IDX01 ON USERS_GROUP_CROSS_RELATION
(CLIENTID, USERID);


CREATE INDEX USERS_GROUP_CROSS_REL_IDX02 ON USERS_GROUP_CROSS_RELATION
(CLIENTID, USERID, GROUPID, INCLUDED);


CREATE INDEX USERS_GROUP_CROSS_REL_IDX03 ON USERS_GROUP_CROSS_RELATION
(INCLUDED, USERID, GROUPID);


CREATE INDEX USERS_GROUP_CROSS_REL_IDX04 ON USERS_GROUP_CROSS_RELATION
(CLIENTID, GROUPID, USERID, INCLUDED);


CREATE INDEX USERS_GROUP_CROSS_REL_IDX05 ON USERS_GROUP_CROSS_RELATION
(GROUPID);


CREATE UNIQUE INDEX PK_USERS_GROUP_CROSS_REL ON USERS_GROUP_CROSS_RELATION
(CLIENTID, USERID, GROUPID);


ALTER TABLE USERS_GROUP_CROSS_RELATION ADD (
  CONSTRAINT PK_USERS_GROUP_CROSS_REL
  PRIMARY KEY
  (CLIENTID, USERID, GROUPID)
  USING INDEX PK_USERS_GROUP_CROSS_REL
  ENABLE VALIDATE);

Here are the row counts on two tables:

USERS_RULE_CROSS_RELATION:  1015195
USERS_GROUP_CROSS_RELATION: 2244425

This is the sql query:

DELETE FROM USERS_GROUP_CROSS_RELATION UGCR
      WHERE     UGCR.CLIENTID = 'G41J1R34SRS000M1'
            AND UGCR.GROUPID = 12042493
            AND UGCR.IS_ADDED = 0
            AND UGCR.INCLUDED = 1
            AND NOT EXISTS
                       (SELECT 1
                          FROM USERS_RULE_CROSS_RELATION URCR
                         WHERE     URCR.CLIENTID = UGCR.CLIENTID
                               AND URCR.GROUPID = UGCR.GROUPID
                               AND URCR.USERID = UGCR.USERID);

--Following is the actual execution plan of the sql:

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                             |      1 |        |      0 |00:01:48.49 |    5730K|  39700 |       |       |          |
|   1 |  DELETE               | USERS_GROUP_CROSS_RELATION  |      1 |        |      0 |00:01:48.49 |    5730K|  39700 |       |       |          |
|*  2 |   HASH JOIN RIGHT ANTI|                             |      1 |  12278 |    214K|00:00:20.94 |   29396 |  29320 |  3907K|  1303K| 4229K (0)|
|*  3 |    INDEX RANGE SCAN   | PK_USERS_RULE_CROSS_REL     |      1 |   1048 |  34240 |00:00:00.29 |     281 |    280 |       |       |          |
|*  4 |    TABLE ACCESS FULL  | USERS_GROUP_CROSS_RELATION  |      1 |  13330 |    248K|00:00:19.90 |   29115 |  29040 |       |       |          |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLIENTID"="CLIENTID" AND "GROUPID"="GROUPID" AND "USERID"="USERID")
   3 - access("CLIENTID"='G41J1R34SRS000M1' AND "GROUPID"=12042493)
   4 - filter(("GROUPID"=1204408493 AND "CLIENTID"='G41J1CN8ERS000M1' AND "IS_ADDED"=0 AND "INCLUDED"=1))

Note that I had to change the actual table names etc. to put this in forum. But except change in names, other details are ditto same. Now this query is timing out in production. Bulk of the time is going in the delete. The question is how can we make delete faster?

Another option is may be the java application can be modified such that this 214K records huge delete can be done in parallel , say in 10 sessions , with each session deleting one part of the data. Is that a way to manage this situation? I read in Tom Kyte's effective Oracle by Design that is one way to 'DIY parallelism' but example is of a SELECT sql. I wonder if a similar approach should be tried on this DELETE and if so how it could be done?

I will be thankful for suggestion.

Thanks,
OrauserN