Home » RDBMS Server » Performance Tuning » DELETE with Parallel - Parallel is not working
DELETE with Parallel - Parallel is not working [message #241213] Mon, 28 May 2007 20:50 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
HI,

I have got a below delete statement



ALTER SESSION ENABLE PARALLEL DML;


DELETE /*+ PARALLEL(ra 4) */ ira  ra
WHERE id_cde LIKE 'QP%'
  AND edte    > TO_DATE('01-APR-2007', 'DD-MON-YYYY')
  AND sdte >= TO_DATE('31-DEC-2006', 'DD-MON-YYYY')
/

COMMIT;

Oracle 9.2.0.7, Sun Solaris, Unix OS, 1 index on tab



I have got 10 millions records, and it has already taken 23 hours, and still is doing..

Parallel hint is not working, can anyone suggest, what's wrong..

Thanks

[Updated on: Mon, 28 May 2007 21:25]

Report message to a moderator

Re: DELETE with Parallel - Parallel is not working [message #241219 is a reply to message #241213] Mon, 28 May 2007 22:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'n not entirely certain you can do parallel delete (but I'm not sure).

Even if you could, the bottleneck wouldn't be in identifying the rows to delete (which would be parallel) it would be in deleting them (which I *think* would be serialised).

Best way to delete more than 10% of the table is to rebuild it under a new table with the unwanted rows missing.

CREATE TABLE new_ira AS
SELECT * FROM ira
WHERE id_cde LIKE 'QP%'
  AND edte    > TO_DATE('01-APR-2007', 'DD-MON-YYYY')
  AND sdte >= TO_DATE('31-DEC-2006', 'DD-MON-YYYY');

CREATE INDEX ...;

DROP TABLE ira;
RENAME new_ira TO ira;


Ross Leishman
Re: DELETE with Parallel - Parallel is not working [message #241343 is a reply to message #241219] Tue, 29 May 2007 05:27 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Thanks Ross,


Here is my observation

i have noticed 4 parallel slave process at one particular time
v$session_longops view

sql was deleting rows on the table, at the same time another table inserted was rows into the same table simultanelously.

question:
how and where can i see the number of parallel slaves running for particular sql with PARALLEL HINT

How come oracle allow table is deleting rows at the same time inserting rows into the same table by another insert statement.



Thanks

[Updated on: Tue, 29 May 2007 05:28]

Report message to a moderator

Re: DELETE with Parallel - Parallel is not working [message #241536 is a reply to message #241343] Tue, 29 May 2007 22:22 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If Oracle is going to use Parallel Query, you will see extra PQ steps in the Explain Plan. In older versions (certainly 7.3, maybe 8.0 and 8i, but certainly no higher) PQ infor was found in the LONG datatype column of the Explain Plan PLAN_TABLE (think the column is called "OTHER").

In the absence of either of these, it will not use PQ. There are other ways at runtime using the V$ views, but I don't have a foolproof method I could describe easily.

As you've seen it is possible for different sessions to DELETE and INSERT at the same time. Why not? Obviously you cannot DELETE something that is not yet committed, but otherwise why should this sort of thing be wrong or disallowed?

Ross Leishman
Re: DELETE with Parallel - Parallel is not working [message #241611 is a reply to message #241536] Wed, 30 May 2007 03:06 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Even if Oracle will use Parallel Execution feature, it might get even worse then before.

You should monitor and verify your hardware/os configuration.
Maybe consider striping the tablespaces over multiple devices, so that the parallel slave processes can all access data from different disks.
Re: DELETE with Parallel - Parallel is not working [message #242168 is a reply to message #241611] Thu, 31 May 2007 17:53 Go to previous messageGo to next message
ramakris
Messages: 21
Registered: July 2006
Location: Delhi
Junior Member
Hi-

Check for the wait events, sometimes whenever u work with parallel slaves, they start blocking each other.

Thanks
Rama.
Re: DELETE with Parallel - Parallel is not working [message #242169 is a reply to message #241213] Thu, 31 May 2007 17:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Check for the wait events, sometimes whenever u work with parallel slaves, they start blocking each other.

EXACTLY how does one determine when "they start blocking each other"?

What metric & what value indicates parallel slave blocking?

Re: DELETE with Parallel - Parallel is not working [message #244212 is a reply to message #242169] Tue, 12 June 2007 00:42 Go to previous message
amol_umbarkar
Messages: 3
Registered: June 2007
Junior Member
>EXACTLY how does one determine when "they start blocking each >other"?

>What metric & what value indicates parallel slave blocking?

I dont believe that parallel slaves will be blocking each other much.Unless the data is not distributed over multiple datafiles residing on same disk drive (could cause I/O contention).

You can take a look at following SQL to find out total waits for latches.

SELECT event,sum(time_waited) wt_in_cs,sum(total_waits) wc
FROM V$SESSION_EVENT
WHERE SID IN (:p_sid1,:p_sid2) -- SID of parallel slaves
AND name like '%latch%'
group by event


Regds,
AMol

Previous Topic: 10g and Index Fast Full scans
Next Topic: quick tuning
Goto Forum:
  


Current Time: Sat Dec 10 05:29:08 CST 2016

Total time taken to generate the page: 0.08978 seconds