Home » RDBMS Server » Performance Tuning » Tuning data deletion that is taking long time (11.2.0.4 on Linux)
Tuning data deletion that is taking long time [message #639604] Sat, 11 July 2015 09:46 Go to next message
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

Re: Tuning data deletion that is taking long time [message #639605 is a reply to message #639604] Sat, 11 July 2015 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
I wonder how much of the time is spent on the actual DELETE FROM USERS_GROUP_CROSS_RELATION & how much is spent doing INDEX maintenance?

I wonder what is impact on elapsed time if the number of INDEX on USERS_GROUP_CROSS_RELATION was reduced to 3 total indexes.

CREATE INDEX USERS_GROUP_CROSS_REL_IDX01 ON USERS_GROUP_CROSS_RELATION (INCLUDED);
CREATE INDEX USERS_GROUP_CROSS_REL_IDX02 ON USERS_GROUP_CROSS_RELATION (GROUPID);
CREATE UNIQUE INDEX PK_USERS_GROUP_CROSS_REL ON USERS_GROUP_CROSS_RELATION (CLIENTID, USERID, GROUPID);
Re: Tuning data deletion that is taking long time [message #639606 is a reply to message #639605] Sat, 11 July 2015 11:25 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you BlackSwan!! I think you are absolutely right...most time must be getting spend on those indexes but even so when I tested, I found that after keeping just those 3 indexes, the time actually increased ...it increased from 1.47 minute to 3.01 minutes as below. To reconfirm I ran it a second time and then it came to 2.18 minutes - may be memory related effect -not sure.

Here is the revised plan (after keeping just those 3 indexes that you pointed out)

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                             |      1 |        |      0 |00:03:01.36 |    3130K|  34669 |       |       |          |
|   1 |  DELETE               | USERS_GROUP_CROSS_RELATION  |      1 |        |      0 |00:03:01.36 |    3130K|  34669 |       |       |          |
|*  2 |   HASH JOIN RIGHT ANTI|                             |      1 |  14201 |    214K|00:00:23.35 |   29396 |  29384 |  3907K|  1303K| 4203K (0)|
|*  3 |    INDEX RANGE SCAN   | PK_USERS_RULE_CROSS_REL     |      1 |   1048 |  34240 |00:00:00.42 |     281 |    281 |       |       |          |
|*  4 |    TABLE ACCESS FULL  | USERS_GROUP_CROSS_RELATION  |      1 |  15253 |    248K|00:00:22.11 |   29115 |  29103 |       |       |          |

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

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

   2 - access("UGRX"."CLIENT_OID"="UGX"."CLIENT_OID" AND "UGRX"."GROUP_OID"="UGX"."GROUP_OID" AND "UGRX"."USER_OID"="UGX"."USER_OID")
   3 - access("UGRX"."CLIENT_OID"='G41J1CN8ERS000M1' AND "UGRX"."GROUP_OID"=1204408493)
   4 - filter(("GROUPID"=1204408493 AND "CLIENTID"='G41J1CN8ERS000M1' AND "IS_ADDED"=0 AND "INCLUDED"=1))



Note however that may be this is because the original time that I got was optimistic - may be some memeory effect was there and so originally when I get 1.47 minutes that was itself not the representative time.

But anyway, here is the important restriction I have - I will not be able to drop the indexes...since they must remain else several queries will get into issues...

So what I am thinking is to convert this sql into another sql that operates on say five or more batches or groups of data (same delete but that will now happen when java code calls it in parallel to process it in batches)...I don't see an alternative to it...

Thanks a lot !!

[Updated on: Sat, 11 July 2015 11:44]

Report message to a moderator

Re: Tuning data deletion that is taking long time [message #639608 is a reply to message #639606] Sun, 12 July 2015 01:41 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
Quote:
But anyway, here is the important restriction I have - I will not be able to drop the indexes
...since they must remain else several queries will get into issues...

This is not correct. You should certainly drop two of the indexes. These two,
CREATE INDEX USERS_GROUP_CROSS_REL_IDX01 ON USERS_GROUP_CROSS_RELATION
(CLIENTID, USERID);

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

are subsumed within this,
CREATE INDEX USERS_GROUP_CROSS_REL_IDX02 ON USERS_GROUP_CROSS_RELATION
(CLIENTID, USERID, GROUPID, INCLUDED);

which is all you need for any queries and the constraint. THis is a change you should make right now.
Since most of the is time spent on the row deletion, not the row selection, dropping the indexes
should have a sigificant effect.

Your object statistics are clearly way wrong, but that probably does not matter: the plan looks reasonable.

If you trace the statement and format the trace with tkprof, it should become clear where the time is being spent
and then perhaps a solution will become clear.





Re: Tuning data deletion that is taking long time [message #639640 is a reply to message #639608] Mon, 13 July 2015 07:11 Go to previous messageGo to next message
cookiemonster
Messages: 13333
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since when is 1 minute 47 seconds to delete 214,000 rows slow?
How long do you expect it to take?
Re: Tuning data deletion that is taking long time [message #639645 is a reply to message #639608] Mon, 13 July 2015 10:07 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thanks John...it is taking me some time to get tracefile and tkprof output...I don't have server access etc. so by tomorrow....but again I have to say that this is more like a given fact:
we have some reasons for which at least for a month without proper testing and approvals we will not be able to drop any indexes. this is just something I can't change. please take it as a given restriction. Now the thing is , with this restriction what best could be done? -the target is to make this happen in 50 seconds as one minute is our application level time out setting and we leave 10 seconds for any other stuff.

I think we slice the delete in several concurrent non-overlapping deletes to manage this? like create 5 range of data that will be deleted and let application create five sessions simultaneously and do this work...
Thanks,
Re: Tuning data deletion that is taking long time [message #639646 is a reply to message #639640] Mon, 13 July 2015 10:08 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Yes but it is just that we have some clients that have huge data and we hit this restriction for them...our application times out after 60 seconds and we need to manage within that period.
Re: Tuning data deletion that is taking long time [message #639654 is a reply to message #639646] Mon, 13 July 2015 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
Why is no index used for SQL below?

SELECT Count(*)
FROM users_rule_cross_relation urcr
WHERE urcr.clientid = ugcr.clientid
AND urcr.groupid = ugcr.groupid
AND urcr.userid = ugcr.userid;

post results from SQL above & below

SELECT Count(*)
FROM users_rule_cross_relation urcr ;
Re: Tuning data deletion that is taking long time [message #639660 is a reply to message #639654] Mon, 13 July 2015 14:01 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi BlackSwan,

Here is the output for the first sql:

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                             |      1 |        |      1 |00:00:12.50 |   24796 |  22941 |       |       |          |
|   1 |  SORT AGGREGATE        |                             |      1 |      1 |      1 |00:00:12.50 |   24796 |  22941 |       |       |          |
|*  2 |   HASH JOIN            |                             |      1 |   1014K|   1015K|00:00:12.40 |   24796 |  22941 |    80M|  5623K|   88M (0)|
|   3 |    TABLE ACCESS FULL   | USERS_RULE_CROSS_RELATION   |      1 |   1014K|   1015K|00:00:02.35 |    7468 |   7465 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| USERS_GROUP_CROSS_REL_IDX02 |      1 |   2244K|   2244K|00:00:06.90 |   17328 |  15476 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------

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

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

   2 - access("URCR"."CLIENTID"="UGCR"."CLIENTID" AND "URCR"."GROUPID"="UGCR"."GROUPID" AND "URCR"."USERID"="UGCR"."USERID")




Here is the output for the second sql:
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                           |      1 |        |      1 |00:00:05.16 |    7468 |   7465 |
|   1 |  SORT AGGREGATE    |                           |      1 |      1 |      1 |00:00:05.16 |    7468 |   7465 |
|   2 |   TABLE ACCESS FULL| USERS_RULE_CROSS_RELATION |      1 |   1014K|   1015K|00:00:05.08 |    7468 |   7465 |
-------------------------------------------------------------------------------------------------------------------

But note that most of time is spend on deletes...and I don't have freedom in short term to drop index and we are looking for a solution in both short term and long term (and long term fix will very likely have index drop for the 2 index that John pointed out.)

[Updated on: Mon, 13 July 2015 14:02]

Report message to a moderator

Re: Tuning data deletion that is taking long time [message #639724 is a reply to message #639660] Tue, 14 July 2015 12:07 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Just as FYI, for John's point on getting the trace file and tkprof, here is the tkprof output...it shows that most of the time is spent on delete.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     75.27     104.66      27518      29461    2838223      214567
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     75.28     104.66      27518      29461    2838223      214567

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 156  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  USERS_GROUP_CROSS_RELATION (cr=29461 pr=27518 pw=0 time=104660596 us)
 214567   HASH JOIN RIGHT ANTI (cr=29396 pr=26877 pw=0 time=3802925 us cost=8026 size=1122352 card=12754)
  34240    INDEX RANGE SCAN PK_USERS_GROUP_CROSS_REL (cr=281 pr=281 pw=0 time=545970 us cost=17 size=42968 card=1048)(object id 135379)
 248807    TABLE ACCESS FULL USERS_GROUP_CROSS_RELATION (cr=29115 pr=26596 pw=0 time=25112406 us cost=8008 size=648882 card=13806)


Re: Tuning data deletion that is taking long time [message #639725 is a reply to message #639724] Tue, 14 July 2015 12:14 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
Can you run tkprof against te trace with waits=yes
sorry I forgot to specify that.
Re: Tuning data deletion that is taking long time [message #639726 is a reply to message #639725] Tue, 14 July 2015 12:58 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Sure and thanks for getting back John...hope I will get this info in a day...I will update once I get the details. Thanks again.
Re: Tuning data deletion that is taking long time [message #639814 is a reply to message #639726] Thu, 16 July 2015 15:19 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi John,

I obtained the trace file, it has the following info...I don't know why a single delete should show the sql net event that the tracefile shows!


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     61.85      82.59      26426      29458    2838187      214567
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     61.85      82.59      26426      29458    2838187      214567

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 156  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  ACS_USER_GROUP_XREF$ (cr=29458 pr=26426 pw=0 time=82593062 us)
 214567   HASH JOIN RIGHT ANTI (cr=29396 pr=26426 pw=0 time=21828756 us cost=8026 size=1218096 card=13842)
  34240    INDEX RANGE SCAN PK_USERS_GROUP_CROSS_REL (cr=281 pr=0 pw=0 time=6818 us cost=17 size=42968 card=1048)(object id 135379)
 248807    TABLE ACCESS FULL USERS_GROUP_CROSS_RELATION (cr=29115 pr=26426 pw=0 time=21236560 us cost=8009 size=700018 card=14894)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                       22        0.00          0.00
  db file scattered read                        601        0.26         20.12
  db file sequential read                        14        0.02          0.11
  log file switch completion                      1        0.18          0.18
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       17.47         17.47
********************************************************************************



Thanks,
OrauserN
Re: Tuning data deletion that is taking long time [message #639815 is a reply to message #639814] Thu, 16 July 2015 15:31 Go to previous messageGo to next message
BlackSwan
Messages: 26274
Registered: January 2009
Location: SoCal
Senior Member
61.85+20.12=81.97 which is just under the reported total elapsed time of 82.59.
so DELETE consumed just under 62 seconds while on the CPU & waited just over 20 seconds for requested I/O operations to complete.
My interpretation is that to significantly reduce elapsed time both CPU & disk subsystem would have to be measurably FASTER.
Re: Tuning data deletion that is taking long time [message #639816 is a reply to message #639814] Thu, 16 July 2015 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I don't know why a single delete should show the sql net event that the tracefile shows!


There are only 2 events: one for you to send the DELETE and one for Oracle to send the acknowledge.

Re: Tuning data deletion that is taking long time [message #639819 is a reply to message #639814] Fri, 17 July 2015 01:12 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
Not many options that I can see. Faster harder; drop the indexes; or parallelism might help.
Try dbms_parallel_execute, perhaps.
Re: Tuning data deletion that is taking long time [message #639828 is a reply to message #639819] Fri, 17 July 2015 05:59 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you BlackSwan, Mike and John. I am feeling very hopeful on dbms_parallel_execute. Thank you John for thinking of that!...I was about to write my own way of doing in parallel. I will try out the dbms_parall...approach and update if it helps.
Previous Topic: Drop Unused columns
Next Topic: TKPROF output analysis
Goto Forum:
  


Current Time: Wed Nov 14 11:20:44 CST 2018