Home » RDBMS Server » Performance Tuning » deletion taking longer time (Oracle 10g,win xp)
deletion taking longer time [message #320915] Fri, 16 May 2008 13:28 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi All

The below process does the following.
1st procedure creates a frontend screen
and 2nd procedure deletes from ace2_list_output table on the basis of list_id.
After the deployment into production we found that for 300 odd records it is taking 14 minutes as pointed out by the clients

the same we did the test in test database for 4000 records on the basis of lis_id it took 1.2 mins

We need to tune this code,specifically the second proc which does the delete operation.

things to note

1 there is an index on both ar_List_Id and alo_list_id
2 the table is not partitioned.
3 table contains around 27 million records
4 both of there procs are inside a package which is run from the front end.

change thought of(might be wrong)

using dynamic sql?
parallel dml?
will partition index help?
altering session for parallel dml?
using set transaction before delete operation

any help? Smile



First Procedure

PROCEDURE p_DeleteList
IS
p_List_Id Ace2_Request.ar_List_Id%TYPE;
BEGIN
IF p_LogIncheck = 'TRUE' THEN
p_BeginTags;

htp.Print('<head>');

htp.p('<script language="Javascript">');

htp.p('function value_prompt() {');

htp.p('var list_id = document.DeleteForm.p_list_id.value');

htp.p('if (list_id == "")');

htp.p('{alert("List ID cannot be null " + ''\n'' + " Please enter any Valid List ID ")}');

htp.p('if (list_id != "")');

htp.p('{location.href='
||''''
||'Lenin_Ace2_Listgen_Screens_pkg.p_deletion?p_list_id='
||''''
||'+document.DeleteForm.p_list_id.value + '
||''''
||'&p_calledby=S'
||''''
||'}}');

htp.p('</script>');

htp.Print('</head>');

p_SetStyle('M');

htp.Print('<form name="DeleteForm" action="Lenin_Ace2_Listgen_Screens_pkg.p_deletelist" method="post">');

htp.Print('<br>');

htp.Print('<br>');

htp.Print('<table border="1" align="center" cellpadding="1" cellspacing="1" width="50%" rules="none">');

htp.Print('<tr bgcolor="#330099">');

htp.Print('<td width="50%" colspan="2" align="center" class="rvideo"><font color="white">Delete Requests</font></td>');

htp.Print('</tr>');

htp.Print('<tr><td width="50"> </td></tr>');

htp.Print('<tr>');

htp.Print('<td width="25%" align="right" class="10">List ID:</td>');

htp.Print('<td width="25%" align="left" class="10"><input type="text" name="p_list_id" size="20" maxlength="20" id="p_list_id" onblur="changecase(''p_list_id'');"></td>');

htp.Print('</tr>');

htp.Print('<tr>');

htp.Print('<tr><td width="50"> </td></tr>');

htp.Print('<td width="25%" align="right" class="10"><input type="button" value="Delete" onclick="value_prompt();"></td>');

htp.Print('<td width="25%" align="left" class="10"><input type="reset" value="Reset"></td>');

htp.Print('</tr>');

htp.Print('</table>');

htp.Print('</form>');

p_EndTags;
ELSE
lenIn_Ace2_Listgen_Screens_pkg.p_Load_Page('Lenin_Ace2_Listgen_Screens_pkg.p_Display_Message?p_MsgType=Not Logged In&p_MsgText=Please Log In the Application&p_Url=Lenin_Ace2_Listgen_Screens_pkg.p_Listgen');
END IF;
END p_DeleteList;

2nd procedure

PROCEDURE p_Deletion
(p_List_Id IN VARCHAR2 DEFAULT NULL,
p_CalledBy IN VARCHAR2 DEFAULT 'V')
IS
p_Current_User VARCHAR2(15);
p_Current_User_Id VARCHAR2(30);
p_Before_del_cnt NUMBER;
p_After_del_cnt NUMBER;
p_del_cnt NUMBER;
BEGIN
p_Current_User := nvl(lenIn_Ace2_Listgen_AdmIn_pkg.f_GetCurrentUser,
'Blank');

p_Current_User_Id := nvl(lenIn_Ace2_Listgen_AdmIn_pkg.f_GetCurrentUserId,
'Blank');

SELECT COUNT(aLo_List_Id)
INTO p_Before_del_cnt
FROM Ace2_List_Output;

DELETE FROM Ace2_Request
WHERE ar_List_Id = p_List_Id;

DELETE FROM Ace2_List_Output
WHERE aLo_List_Id = p_List_Id;

SELECT COUNT(aLo_List_Id)
INTO p_After_del_cnt
FROM Ace2_List_Output;

p_del_cnt := p_Before_del_cnt - p_After_del_cnt;

lenIn_Ace2_Listgen_Screens_pkg.p_Load_Page('Lenin_Ace2_Listgen_Screens_pkg.p_Display_Message?p_MsgType=Delete Requests&p_MsgText='
||p_del_cnt
||' Record(s) Deleted for List ID '
||p_List_Id
||' by '
||p_Current_User
||'&p_Url=Lenin_Ace2_Listgen_Screens_pkg.P_DeleteList');
END p_Deletion;
PROCEDURE p_Deletion
(p_List_Id IN VARCHAR2 DEFAULT NULL,
p_CalledBy IN VARCHAR2 DEFAULT 'V')
IS
p_Current_User VARCHAR2(15);
p_Current_User_Id VARCHAR2(30);
p_Before_del_cnt NUMBER;
p_After_del_cnt NUMBER;
p_del_cnt NUMBER;
BEGIN
p_Current_User := nvl(lenIn_Ace2_Listgen_AdmIn_pkg.f_GetCurrentUser,
'Blank');

p_Current_User_Id := nvl(lenIn_Ace2_Listgen_AdmIn_pkg.f_GetCurrentUserId,
'Blank');

SELECT COUNT(aLo_List_Id)
INTO p_Before_del_cnt
FROM Ace2_List_Output;

DELETE FROM Ace2_Request
WHERE ar_List_Id = p_List_Id;

DELETE FROM Ace2_List_Output
WHERE aLo_List_Id = p_List_Id;

SELECT COUNT(aLo_List_Id)
INTO p_After_del_cnt
FROM Ace2_List_Output;

p_del_cnt := p_Before_del_cnt - p_After_del_cnt;

lenIn_Ace2_Listgen_Screens_pkg.p_Load_Page('Lenin_Ace2_Listgen_Screens_pkg.p_Display_Message?p_MsgType=Delete Requests&p_MsgText='
||p_del_cnt
||' Record(s) Deleted for List ID '
||p_List_Id
||' by '
||p_Current_User
||'&p_Url=Lenin_Ace2_Listgen_Screens_pkg.P_DeleteList');
END p_Deletion;

Report message to a moderator

Re: deletion taking longer time [message #320919 is a reply to message #320915] Fri, 16 May 2008 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Why do you NOT follow Posting Guidelines?

You're On Your own (YOYO)!

Report message to a moderator

Re: deletion taking longer time [message #321256 is a reply to message #320915] Mon, 19 May 2008 12:01 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi All

The below process does the following.
1st procedure creates a frontend screen
and 2nd procedure deletes from ace2_list_output table on the basis of list_id.
After the deployment into production we found that for 300 odd records it is taking 14 minutes as pointed out by the clients

the same we did the test in test database for 4000 records on the basis of lis_id it took 1.2 mins

We need to tune this code,specifically the second proc which does the delete operation.

things to note

1 there is an index on both ar_List_Id and alo_list_id
2 the table is not partitioned.
3 table contains around 27 million records
4 both of there procs are inside a package which is run from the front end.

change thought of(might be wrong)

using dynamic sql?
parallel dml?
will partition index help?
altering session for parallel dml?
using set transaction before delete operation

any help? Smile



First Procedure

PROCEDURE p_DeleteList
IS
p_List_Id Ace2_Request.ar_List_Id%TYPE;
BEGIN
IF p_LogIncheck = 'TRUE' THEN
p_BeginTags;

htp.Print('<head>');

htp.p('<script language="Javascript">');

htp.p('function value_prompt() {');

htp.p('var list_id = document.DeleteForm.p_list_id.value');

htp.p('if (list_id == "")');

htp.p('{alert("List ID cannot be null " + ''\n'' + " Please enter any Valid List ID ")}');

htp.p('if (list_id != "")');

htp.p('{location.href='
||''''
||'Lenin_Ace2_Listgen_Screens_pkg.p_deletion?p_list_id='
||''''
||'+document.DeleteForm.p_list_id.value + '
||''''
||'&p_calledby=S'
||''''
||'}}');

htp.p('</script>');

htp.Print('</head>');

p_SetStyle('M');

htp.Print('<form name="DeleteForm" action="Lenin_Ace2_Listgen_Screens_pkg.p_deletelist" method="post">');

htp.Print('<br>');

htp.Print('<br>');

htp.Print('<table border="1" align="center" cellpadding="1" cellspacing="1" width="50%" rules="none">');

htp.Print('<tr bgcolor="#330099">');

htp.Print('<td width="50%" colspan="2" align="center" class="rvideo"><font color="white">Delete Requests</font></td>');

htp.Print('</tr>');

htp.Print('<tr><td width="50"> </td></tr>');

htp.Print('<tr>');

htp.Print('<td width="25%" align="right" class="10">List ID:</td>');

htp.Print('<td width="25%" align="left" class="10"><input type="text" name="p_list_id" size="20" maxlength="20" id="p_list_id" onblur="changecase(''p_list_id'');"></td>');

htp.Print('</tr>');

htp.Print('<tr>');

htp.Print('<tr><td width="50"> </td></tr>');

htp.Print('<td width="25%" align="right" class="10"><input type="button" value="Delete" onclick="value_prompt();"></td>');

htp.Print('<td width="25%" align="left" class="10"><input type="reset" value="Reset"></td>');

htp.Print('</tr>');

htp.Print('</table>');

htp.Print('</form>');

p_EndTags;
ELSE
lenIn_Ace2_Listgen_Screens_pkg.p_Load_Page('Lenin_Ace2_Listgen_Screens_pkg.p_Display_Message?p_MsgType=Not Logged In&p_MsgText=Please Log In the Application&p_Url=Lenin_Ace2_Listgen_Screens_pkg.p_Listgen');
END IF;
END p_DeleteList;


2nd procedure

PROCEDURE p_Deletion
(p_List_Id IN VARCHAR2 DEFAULT NULL,
p_CalledBy IN VARCHAR2 DEFAULT 'V')
IS
p_Current_User VARCHAR2(15);
p_Current_User_Id VARCHAR2(30);
p_Before_del_cnt NUMBER;
p_After_del_cnt NUMBER;
p_del_cnt NUMBER;
BEGIN
p_Current_User := nvl(lenIn_Ace2_Listgen_AdmIn_pkg.f_GetCurrentUser,
'Blank');

p_Current_User_Id := nvl(lenIn_Ace2_Listgen_AdmIn_pkg.f_GetCurrentUserId,
'Blank');

SELECT COUNT(aLo_List_Id)
INTO p_Before_del_cnt
FROM Ace2_List_Output;

DELETE FROM Ace2_Request
WHERE ar_List_Id = p_List_Id;

DELETE FROM Ace2_List_Output
WHERE aLo_List_Id = p_List_Id;

SELECT COUNT(aLo_List_Id)
INTO p_After_del_cnt
FROM Ace2_List_Output;

p_del_cnt := p_Before_del_cnt - p_After_del_cnt;

lenIn_Ace2_Listgen_Screens_pkg.p_Load_Page('Lenin_Ace2_Listgen_Screens_pkg.p_Display_Message?p_MsgType=Delete Requests&p_MsgText='
||p_del_cnt
||' Record(s) Deleted for List ID '
||p_List_Id
||' by '
||p_Current_User
||'&p_Url=Lenin_Ace2_Listgen_Screens_pkg.P_DeleteList');
END p_Deletion;
PROCEDURE p_Deletion
(p_List_Id IN VARCHAR2 DEFAULT NULL,
p_CalledBy IN VARCHAR2 DEFAULT 'V')
IS
p_Current_User VARCHAR2(15);
p_Current_User_Id VARCHAR2(30);
p_Before_del_cnt NUMBER;
p_After_del_cnt NUMBER;
p_del_cnt NUMBER;
BEGIN
p_Current_User := nvl(lenIn_Ace2_Listgen_AdmIn_pkg.f_GetCurrentUser,
'Blank');

p_Current_User_Id := nvl(lenIn_Ace2_Listgen_AdmIn_pkg.f_GetCurrentUserId,
'Blank');

SELECT COUNT(aLo_List_Id)
INTO p_Before_del_cnt
FROM Ace2_List_Output;

DELETE FROM Ace2_Request
WHERE ar_List_Id = p_List_Id;

DELETE FROM Ace2_List_Output
WHERE aLo_List_Id = p_List_Id;

SELECT COUNT(aLo_List_Id)
INTO p_After_del_cnt
FROM Ace2_List_Output;

p_del_cnt := p_Before_del_cnt - p_After_del_cnt;

lenIn_Ace2_Listgen_Screens_pkg.p_Load_Page('Lenin_Ace2_Listgen_Screens_pkg.p_Display_Message?p_MsgType=Delete Requests&p_MsgText='
||p_del_cnt
||' Record(s) Deleted for List ID '
||p_List_Id
||' by '
||p_Current_User
||'&p_Url=Lenin_Ace2_Listgen_Screens_pkg.P_DeleteList');
END p_Deletion;


and for an invalid list_id i need to through an exception which should display the message as "invalid list id",where can i put and use the exception?

Report message to a moderator

Re: deletion taking longer time [message #321629 is a reply to message #320915] Tue, 20 May 2008 21:38 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
When you ran your tests (in the test database) did the table contain 27,000,000 rows?

Report message to a moderator

Re: deletion taking longer time [message #322042 is a reply to message #321629] Thu, 22 May 2008 04:44 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Quote:
When you ran your tests (in the test database) did the table contain 27,000,000 rows?


Hey this is a valid point,and really this is not the case in test cluster as test has only 200000 records,

tehn how can we proceed here?

Report message to a moderator

Re: deletion taking longer time [message #322246 is a reply to message #322042] Thu, 22 May 2008 22:08 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Quote:
Hey this is a valid point,and really this is not the case in test cluster as test has only 200000 records,

tehn how can we proceed here?


First of all, my $0.02 on your ideas:


    Using dynamic sql will probably make it a little worse.
    parallel dml might help, and might make everything else worse
    partitioning the table would help -- if all the deletes fall into a single (small) partition. But, this will require a major redesign of your app.
    using set transaction will not help at all. But it would be informative for you to research just why.


You know you don't have to fetch the counts to get the number of rows deleted, the %ROWCOUNT cursor attribute will give you that. So, your two select count(*) statements can be eliminated. I don't know how much of the 14 minutes they are taking.

Do you have any other indexes on your 27M table? Are they also being updated by your delete?

Report message to a moderator

Re: deletion taking longer time [message #322247 is a reply to message #320915] Thu, 22 May 2008 22:20 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Which of the suggestions/hints in the URL have you tried & what were the results?

More than likely one or more of the techniques in the URL above would reduce run time.

Since you have NO idea where time is being actually being spent, you are shooting in the dark hoping to get lucky.

Ready, Fire, Aim.

Are statistics current on all objects?

What is the EXPLAIN_PLAN for all SQL statements?

Report message to a moderator

Previous Topic: Incorrect optimizer_index_caching affect on plan optimization
Next Topic: What situation should I add more termporary tablespace?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 22 22:44:13 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.