Home » RDBMS Server » Performance Tuning » performance tuning
performance tuning [message #289883] Wed, 26 December 2007 06:52 Go to next message
piscean_n
Messages: 36
Registered: December 2007
Member
can you suggest me some ways to do performance tuning in this.
i have to delete lakh of records in some 10 min.
now only 200 records is taking 1 min to delete.

CREATE OR REPLACE PROCEDURE purge_sample
AS
       count_x  NUMBER  := 0;
       total  NUMBER  := 0;
       

      CURSOR del_record_cur IS 
          SELECT request_id FROM RQ_ADVICES_IN_PROGRESS_DUP 
         WHERE REQUEST_CREATION_TIME<SYSDATE-7 AND STATUS IN('S') 
         AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_SERVICE_ADVICE_DETAILS_DUP)
         AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_ACCOUNT_ADVICE_DETAILS_DUP)
         AND ROWNUM<200;
         
         delrow del_record_cur%ROWTYPE;

     BEGIN
     
            
       FOR delrow IN del_record_cur LOOP
       
      
       
         DELETE /*+ parallel(req,8)*/ FROM RQ_ADVICES_IN_PROGRESS_DUP req
           WHERE REQUEST_ID = delrow.REQUEST_ID;

         total := total + 1;
         count_x := count_x + 1;

         IF (count_x >= 100) THEN
           COMMIT;
            
           count_x := 0;
         END IF;

       END LOOP;
       DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from RQ_ADVICES_IN_PROGRESS_DUP ');
       COMMIT;
       
     END;
Re: performance tuning [message #289887 is a reply to message #289883] Wed, 26 December 2007 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you'd post this in Performances forum, you'd see How to Identify Performance Problem and Bottleneck
In addition, OraFAQ Forum Guide also gives what are the information you have to give to get help on performances question.

Regards
Michel
Re: performance tuning [message #289888 is a reply to message #289883] Wed, 26 December 2007 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Purging row by row for 200 rows is not really smart.
Why not just "delete from where ..."?

Using a hint parallel degree 8 to delete ONE row is completly stupid.

Committing every 100 rows is also silly.

In the end, I think this procedure wraps up all what you must NOT do. Thanks for that.

Regards
Michel

[Updated on: Wed, 26 December 2007 07:19]

Report message to a moderator

Re: performance tuning [message #289950 is a reply to message #289883] Wed, 26 December 2007 19:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
To continue on with Michel's line of reasoning:

piscean_n, why did you write this as a pl/sql procedure rather than just one simple delete statement?

Do yourself a favor, and take the time to write a single delete statement that deletes all the rows you want deleted, then time it. Once you do this, you will forever see the reasoning behind why ROW BY ROW = SLOW BY SLOW (I didn't invent the phrase, Tom Kyte did).

This may actually be a very good thing for you. Once you figure out for yourself the value of SQL only solutions, you will in the future spend more time trying to create them, before giving in to the urge to write some kind of LOOPING code.

Once you have written, timed, and tested your SQL only delete version of this, please, post your results; others will benefit. Your PL/SQL solution stacked up against its simple SQL conterpart, coupled with the fact that you created both, will make an excellent learning aid for others. A chance for you to contribute to the community.

Good luck, Kevin.
Re: performance tuning [message #289954 is a reply to message #289950] Wed, 26 December 2007 21:49 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
No actually we want to delete in chunks like first 1000 records will be deleted then another 1000 like so on.
so for this i think i have to use pl/sql.
Re: performance tuning [message #289955 is a reply to message #289883] Wed, 26 December 2007 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>No actually we want to delete in chunks like
You only think this way because you don't realize the consequences for making such a poorly performing choice.
Re: performance tuning [message #289956 is a reply to message #289955] Wed, 26 December 2007 22:27 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
I tried with simple query to delete still it is taking so much time for deletion of only 2000 records among 40,000 records.


DELETE FROM RQ_ADVICES_IN_PROGRESS_DUP WHERE REQUEST_CREATION_TIME<SYSDATE-7 AND STATUS IN('S') 
         AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_SERVICE_ADVICE_DETAILS_DUP)
         AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_ACCOUNT_ADVICE_DETAILS_DUP)
         AND ROWNUM<2000;
Re: performance tuning [message #289962 is a reply to message #289883] Wed, 26 December 2007 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>AND STATUS IN('S')
above make little sense to have an IN list of one item

I strongly suspect that the slowness is on the SELECT side (deciding which rows to delete) & not from actual DELETE itself.

What does SQL_TRACE & TKPROF show for

SELECT * FROM RQ_ADVICES_IN_PROGRESS_DUP WHERE REQUEST_CREATION_TIME<SYSDATE-7 AND STATUS IN('S')
AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_SERVICE_ADVICE_DETAILS_DUP)
AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_ACCOUNT_ADVICE_DETAILS_DUP)
AND ROWNUM<2000;

[Updated on: Wed, 26 December 2007 22:54] by Moderator

Report message to a moderator

Re: performance tuning [message #289965 is a reply to message #289883] Wed, 26 December 2007 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_SERVICE_ADVICE_DETAILS_DUP)
>AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_ACCOUNT_ADVICE_DETAILS_DUP)
Possibly results in cartesian product between RQ_ACCOUNT_ADVICE_DETAILS_DUP & RQ_SERVICE_ADVICE_DETAILS_DUP
Re: performance tuning [message #289972 is a reply to message #289965] Wed, 26 December 2007 23:09 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
how to see sql_trace and tkprof.
can you tell me the site to download pl/sql developer for 10g.
Re: performance tuning [message #290004 is a reply to message #289962] Thu, 27 December 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_SERVICE_ADVICE_DETAILS_DUP)
AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_ACCOUNT_ADVICE_DETAILS_DUP)

REQUEST_ID IN(
SELECT REQUEST_ID FROM RQ_SERVICE_ADVICE_DETAILS_DUP
intersect
SELECT REQUEST_ID FROM RQ_ACCOUNT_ADVICE_DETAILS_DUP)
is surely a better way.
Are the columns indexed?
Why delete per chunk and not in a whole?
...
You chose the wrong way since the beginning.
I bet there are some missing foreign keys.

Regards
Michel

[Updated on: Thu, 27 December 2007 01:49]

Report message to a moderator

Re: performance tuning [message #290385 is a reply to message #289883] Fri, 28 December 2007 12:04 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Do you have foreign keys pointing back to the table you are deleting from? If so, are these foreign keys supported by an index? If not, then you must do a full table scan of each child table before the row can be deleted. Consider this:

create table parent_table
(
 parent_table_id number not null
,somedata varchar2(4000)
)
/

alter table parent_table add primary key (parent_table_id)
/

create table child_table
(
 child_table_id number not null
,parent_table_id number not null
,somedata varchar2(4000)
)
/

alter table child_table add primary key (child_table_id)
/
alter table child_table add foreign key (parent_table_id) references parent_table
/


insert into parent_table values (1,'abc');
insert into parent_table values (2,'xyz');

insert into child_table values (101,2,'pdq');
--
-- insert 10 million more rows into child_table
--

commit
/

delete
from parent_table
where parent_table_id = 1
/


What happens when you delete the above row? One thing that happens is that Oracle must look at CHILD_TABLE to see if there are any children tied to the row in PARENT_TABLE you are trying to delete. How will Oracle do this? There is no index on the foreign key column in CHILD_TABLE. I guess that means Oracle must do a full table scan on CHILD_TABLE. Hmm... 10 million rows will be scanned for each row deleted from PARENT_TABLE.

But if you have an index on CHILD_TABLE.PARENT_TABLE_ID, then no FTS, just a simple index lookup.

I am not saying this is your problem, but I have seen this before. It is one of the reasons why a delete can take a long time even if you are deleting only a few rows. Check this out and let us know what you find.

Good luck, Kevin
Re: performance tuning [message #291555 is a reply to message #289883] Fri, 04 January 2008 16:47 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
yoh! piscean_n! where is our update?

Kevin
Re: performance tuning [message #292422 is a reply to message #291555] Tue, 08 January 2008 22:34 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
Columns are indexed in both parent and child table.

But in my case both parent ancd child table is nested tables.

can performance be an issue with nested tables.
Re: performance tuning [message #292499 is a reply to message #292422] Wed, 09 January 2008 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

can performance be an issue with nested tables

Sure.

Regards
Michel
Re: performance tuning [message #292545 is a reply to message #292499] Wed, 09 January 2008 03:24 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
in that case how i should delete.
SELECT request_id FROM REQUESTS_DUP 
         WHERE STATUS in('C') AND MONTHS_BETWEEN(SYSDATE,REQUEST_DATE)>6 
         AND (REQUEST_SOURCE_ID in(2,4)) AND (REQUEST_TYPE_ID in('L','S'));


In this the table requests_dup has 2 columns as nested type.
and it has 6 other child tables referring request_id and all these child tables have 2 columns as nested type.
Re: performance tuning [message #292671 is a reply to message #289883] Wed, 09 January 2008 10:08 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you should do some reading about nested tables. If I recall correctly, there is a "hidden column" added to each nested table that Oracle uses as a foreign key column to join nested tables back to parent tables. Althought this column is "hidden" I use the term loosely as documentation clear says that you can and possibly should consider creating an index on this column.

Do some reading on nested tables. Maybe I am confusing it with something else (I try to stay away from creating nested tables as part of a table design (although I relish using Oracle Objects and Collections in code)). There is no substitute for original thinking and original research. So maybe this has something to do with what you need.

It is quite possible I could be misleading you here as my memory fails me from time to time. So DO SOME READING!, and get back to use with what you find out. A simple google search on Oracle Nested Tables should do the trick.

Good luck, Kevin
Previous Topic: Comparing collection against cursor
Next Topic: retrieve execution plan for running session in Oracle 8i database
Goto Forum:
  


Current Time: Sat Dec 10 09:00:47 CST 2016

Total time taken to generate the page: 0.30292 seconds