Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> delete ... where not exists ... performance
PPJava
post Feb 28 2012, 10:28 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 28-February 12
Member No.: 46,849



Hi there!

I've got some performance issues with an "delete ... where not exists" statement. The statement is executed via JDBC.

The statement looks like this:
CODE
DELETE FROM table_inputjob
WHERE  inputfilestate =?
       AND indexfilestate =?
       AND NOT EXISTS(SELECT *
                      FROM   table_job
                      WHERE  table_job.inputid = table_inputjob.inputid)


Each table contains about 4500000 rows. The above statement took 115 seconds to delete 1098 rows from TABLE_INPUTJOB. A smaller run with 50 rows deleted took about 46 seconds. Another run was about to delete 2000000 rows, which I've canceled after several hours have passed. I need to delete this 2000000 rows in an acceptable amount of time.

Do you have any ideas to boost this statement?
Thanks in advance!

Here is the result from EXPLAIN PLAN (when the statement should delete 1036 rows):
CODE
Plan hash value: 3615700051

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |                |  7872 |   615K|       | 56776   (1)| 00:11:22 |
|   1 |  DELETE                | TABLE_INPUTJOB |       |       |       |            |          |
|*  2 |   HASH JOIN ANTI       |                |  7872 |   615K|    41M| 56776   (1)| 00:11:22 |
|*  3 |    TABLE ACCESS FULL   | TABLE_INPUTJOB |   787K|    32M|       | 25398   (1)| 00:05:05 |
|   4 |    INDEX FAST FULL SCAN| IDX_TJ_IIDJS   |  4651K|   164M|       | 18477   (1)| 00:03:42 |
-------------------------------------------------------------------------------------------------

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

   2 - access("TABLE_JOB"."INPUTID"="TABLE_INPUTJOB"."INPUTID")
   3 - filter("INDEXFILESTATE"=TO_NUMBER(:2) AND "INPUTFILESTATE"=TO_NUMBER(:1))


Table definition looks like this:
CODE
CREATE TABLE TABLE_INPUTJOB (
  TASKNAMES VARCHAR2(4000),
  INPUTID VARCHAR2(100) NOT NULL,
  INPUTFILESTATE NUMBER(10),
  EXECUTIONQUEUED DATE,
  EXECUTIONSTART DATE,
  EXECUTIONEND DATE,
  PRIORITY NUMBER(10),
  SERVERNAME VARCHAR2(100),
  INDEXFILESTATE NUMBER(10),
  OBSERVER VARCHAR2(50),
  ALLJOBS NUMBER(10),
    CONSTRAINT PK_TABLE_INPUTJOB PRIMARY KEY (INPUTID)
);

CREATE TABLE "TABLE_JOB" (
  "JOBSTATE" NUMBER(10),
  "DATETIME" DATE,
  "USERNAME" VARCHAR2(50),
  "DDONAME" VARCHAR2(1024) NOT NULL,
  "TEXTSEARCH" VARCHAR2(4000),
  "PROCESSED" NUMBER(10),
  "ARCHIVED" NUMBER(1),
  "ENABLED" NUMBER(1) NOT NULL,
  "QUEUENAME" VARCHAR2(100),
  "DOCTYPE" VARCHAR2(100),
  "PAGECOUNT" NUMBER(10) NOT NULL,
  "SHEETCOUNT" NUMBER(10) NOT NULL,
  "COPY" NUMBER(10),
  "ERRORDESCRIPTION" VARCHAR2(255),
  "JOB_ID" VARCHAR2(36) UNIQUE NOT NULL,
  "INPUTID" VARCHAR2(100) NOT NULL,
  "JOBID" VARCHAR2(100) UNIQUE,
  "LOCKED" NUMBER(10),
  "LAST_OUTPUT" DATE,
  "DOCTYPENR" NUMBER(10),
  "EVENT_ID" VARCHAR2(36),
    CONSTRAINT "PK_TABLE_JOB" PRIMARY KEY ("JOB_ID", "INPUTID")
);

ALTER TABLE "TABLE_JOB" ADD CONSTRAINT "FK_JOB_INPUTJOB" FOREIGN KEY (
  "INPUTID"
)
REFERENCES "TABLE_INPUTJOB" (
  "INPUTID"
) ON DELETE CASCADE;

CREATE INDEX IDX_TABLE_JOB
  ON TABLE_JOB (INPUTID, JOB_ID) NOLOGGING;

CREATE INDEX IDX_TJ_IIDJS
  ON TABLE_JOB (INPUTID, JOBSTATE) NOLOGGING;
Go to the top of the page
 
+Quote Post
burleson
post Feb 28 2012, 05:58 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



Hi,

>> I've got some performance issues with an "delete ... where not exists" statement.

The WHERE NOT EXISTS subquery, in most cases, this type of subquery can be re-written with a MINUS operator:

http://www.dba-oracle.com/oracle_news/2005...s_minus_sql.htm



See here, how to tune "not exists" subqueries:

http://www.dba-oracle.com/t_cbo_existence_queries.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
rastl
post Mar 1 2012, 03:38 AM
Post #3


Advanced Member
***

Group: Members
Posts: 56
Joined: 12-October 06
Member No.: 5,309



Hello PPJava,

interesting case.

The performance/runtime/resource consumption of DML operations depend roughly on to two factors (excluding topics
as data order/clustering,read consistency issues or lgwr/dbwr bottlenecks)

1) performance of the query defining the result set and
2) performance of executing the data manipulation (DELETE of a row in your case).

Your HASH ANTI JOIN on FULL SCAN row sources should perform almost independent from the size of the result set,
but the effort and runtime of DELETING will increase linearly with the number of rows to delete.

According to your numbers (43 seconds for the query phase and about 66 milliseconds for DELETION of one row)
I would estimate a runtime of about 131722 seconds for deletion of 2000000 rows !

q+1098*d=115 (statement took 115 seconds to delete 1098)
q+50*d=46 (50 rows deletion took about 46 seconds)

q = 22379/524 (about 43 seconds)
d = 69/1048 (about 66 milliseconds)

131722=round(22379/524+2000000*69/1048)

Hence I would wonder why deleting one row takes about 66 milliseconds.
Bear in mind that deletion involves index maintainence and FOREIGN KEY constraint checking, so Oracle has to read
the index PK_TABLE_INPUTJOB and one of the TABLE_INPUTJOB.INPUTID indexes for every row to delete. In worst case Oracle is using IDX_TABLE_JOB instead of IDX_TJ_IIDJS which has already been read by the INDEX FAST FULL SCAN step (no idea how Oracle chooses the index for FK constraint checking). This may result into a lot of additional db file sequential reads in case of a bad clustering factor.

If possible (if deletion is running exclusively) I would try to disable the FOREIGN KEY constraint FK_JOB_INPUTJOB
and even removing the index (and PK constraint) PK_TABLE_INPUTJOB for executing this DELETE.

Please provide some runtime statistics for further investigation.

Best Regards,
Rainer Stenzel
Go to the top of the page
 
+Quote Post
HelloRavi
post Mar 2 2012, 07:10 AM
Post #4


Advanced Member
***

Group: Members
Posts: 111
Joined: 25-March 08
Member No.: 19,800




The explain plan shows the table "table_inputjob" is FULL ACCESS, which is causing the delay. You may create an index for this column and try again. Ensure the Where clause field got index created in the respective field. Or already available indexed column may be used subject to the design.


Go to the top of the page
 
+Quote Post
SteveC
post Mar 2 2012, 03:06 PM
Post #5


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



Using not exists, not, and not equal - Oracle has to search everything before it knows the value is not present. An index won't help (unless the column is not null), because normal indexes do not index null values.
Go to the top of the page
 
+Quote Post
burleson
post Mar 3 2012, 02:47 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



See here, my tips for tuning a NOT EXISTS subquery:

http://www.dba-oracle.com/t_tuning_where_not_exists_sql.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 03:42 AM