Home » RDBMS Server » Performance Tuning » Procedure Performance (Oracle 10g)
Procedure Performance [message #288535] Mon, 17 December 2007 23:41 Go to next message
Mannu
Messages: 4
Registered: December 2007
Junior Member
Hi All,

Please help us in optimising the following query,
The record count in each of this table (A,B,C,G) is around 60 lakhs.

This query is basically reqd to fetch correct user details and store in table TAB1 which will ultimately help in providing security. This query takes around 1.5 to 2 hrs for each user and we'll have around 600 users to be uploaded every month.

The load frequency for thi table is monthly


CREATE OR REPLACE PROCEDURE SAMPLE
(USERID IN VARCHAR2)

AS

BEGIN

DELETE FROM TAB1 WHERE OPRID = USERID;

INSERT INTO TAB1
SELECT DISTINCT A.C1, A.C2, B.C3, C.C4
FROM
(
SELECT R.C1 C1, F.C2 C2
FROM R, F
WHERE
R.C1 = USERID
AND R.JOIN = F.JOIN
) A,

(
SELECT R.C1 C1, E.C3 C3
FROM R, E
WHERE
R.C1 = USERID
AND R.JOIN = E.JOIN
) B,

(
SELECT R.C1 C1, U.C4 C4
FROM R, U
WHERE
R.C1 = USERID
AND R.JOIN = U.JOIN
) C,

(
SELECT DISTINCT C2, C3, C4
FROM G
) G,

WHERE
A.C1 = B.C1
AND
B.C1 = C.C1
AND
A.C2 = G.C2
AND
B.C3 = G.C3
AND
C.C4 = G.C4;

COMMIT;

END;


Guess the logic is clear here, kindly help us in tuning this query for better performance

Re: Procedure Performance [message #288536 is a reply to message #288535] Mon, 17 December 2007 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Guess the logic is clear here
If this is true, then you don't need advice. Proceed as is reasonable.
Re: Procedure Performance [message #288537 is a reply to message #288536] Mon, 17 December 2007 23:46 Go to previous messageGo to next message
Mannu
Messages: 4
Registered: December 2007
Junior Member
Logic is clear, but is there a better way to do the same functionality which will result in better performance?
Re: Procedure Performance [message #288539 is a reply to message #288535] Mon, 17 December 2007 23:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Which of the following have you done & what were the results?
Re: Procedure Performance [message #288540 is a reply to message #288535] Mon, 17 December 2007 23:55 Go to previous messageGo to next message
Mannu
Messages: 4
Registered: December 2007
Junior Member

Will look into the guide and get back,

As of now we jus ran this procedure which i had shared and it took around 1,5 to 2 hrs for each user ( as i had already mentioned)
Re: Procedure Performance [message #288545 is a reply to message #288540] Tue, 18 December 2007 00:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is C1 unique in table R?
Re: Procedure Performance [message #288548 is a reply to message #288535] Tue, 18 December 2007 00:07 Go to previous message
Mannu
Messages: 4
Registered: December 2007
Junior Member
No it is not, C1 can be repeated in R
Previous Topic: Indexing for lookup tables
Next Topic: ORA-03127 error?
Goto Forum:
  


Current Time: Sat Dec 10 20:18:22 CST 2016

Total time taken to generate the page: 0.17332 seconds