| Need to tune package. [message #625611] |
Sat, 11 October 2014 05:24  |
swap19
Messages: 11
Registered: October 2010
Location: India
|
Junior Member |
|
|
Hello Expert,
I want to tune my package & found some SQL taking too much time so while tuning package replaced that SQL into bulk collection. Now I am getting some issue that mention in below step by step.
Step 1 : Previous code
INSERT INTO /*+APPEND*/ <TABLE_NAME_1> SELECT * FROM <TABLE_NAME_2>;
TABLE_NAME_1 have 3 trigger (Row level)
Step 2 : Replaced code
DECLARE
CURSOR C_SOURCE IS SELECT * FROM <TABLE_NAME_2>;
TYPE TYP_SOURCE IS TABLE OF C_SOURCE%ROWTYPE INDEX BY BINARY_INTEGER;
TAB_SOURCE TYP_SOURCE;
TAB_SOURCE_EMPTY TYP_SOURCE;
BEGIN
OPEN C_SOURCE;
LOOP
FETCH C_SOURCE BULK COLLECT INTO TAB_SOURCE LIMIT 10000;
IF TAB_SOURCE.COUNT > 0 THEN
FORALL I IN TAB_SOURCE.FIRST..TAB_SOURCE.LAST
INSERT INTO <TABLE_NAME_1>
VALUES TAB_SOURCE(I);
END IF;
TAB_SOURCE := TAB_SOURCE_EMPTY;
EXIT WHEN C_SOURCE%NOTFOUND;
END LOOP;
COMMIT;
CLOSE C_SOURCE;
END;
Issue : Getting not-null constraint error. After search in google found some update 'Bulk collection not work for ROW level trigger'.
Need Advice :
1. Is it correct approach for tuning package ?
2. Doing any mistake while using bulk collection ?
3. If you have any better approach for tuning package.Please let me know.
Some Addition information :
1.Inserting & deleting (vai Truncating partition) 5 Million records daily.
2.Under weekly maintence we are always anyalzing table & rebuilding index.
If you need any addtional information please let me know.
Thanks
Swap19
|
|
|
|
| Re: Need to tune package. [message #625612 is a reply to message #625611] |
Sat, 11 October 2014 05:52  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Welcome to the forum!
Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
Why did you move from SQL to PL/SQL approach. Bulk collect + For All is never going to be faster than simple insert. There is an unnecessary overhead due to context switch between the two engines. Please explain why did you change your approach.
|
|
|
|
|
|
| Re: Need to tune package. [message #625614 is a reply to message #625612] |
Sat, 11 October 2014 06:28 |
swap19
Messages: 11
Registered: October 2010
Location: India
|
Junior Member |
|
|
Thanks Lalit for reply
In my package particular this SQL taking to much time so improve the performance i thought BULK COLLECTION will help me. If you have any other approach please let me know.
|
|
|
|
| Re: Need to tune package. [message #625615 is a reply to message #625613] |
Sat, 11 October 2014 06:36 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
John Watson wrote on Sat, 11 October 2014 12:24Your /*+ append */ hint was not working because you placed it incorrectly. It must be immediately after the SELECT key word.
My apologies - the hint must be after the INSERT keyword. Sorry about that.
|
|
|
|
|
|
| Re: Need to tune package. [message #625617 is a reply to message #625616] |
Sat, 11 October 2014 07:02 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
swap19 wrote on Sat, 11 October 2014 12:57Please ignore it. While posting mistakenly wrote. You are saying that the hint is in the correct place? OK.
Next: Have you calibrated the system IO and enabled automatic parallel degree policy?
|
|
|
|
| Re: Need to tune package. [message #625618 is a reply to message #625617] |
Sat, 11 October 2014 07:15 |
swap19
Messages: 11
Registered: October 2010
Location: India
|
Junior Member |
|
|
|
How i can do that. I do not have any DBA privileges & DBA will not allow me do any changes. Please suggest any other method that i can do changes in the query.
|
|
|
|
| Re: Need to tune package. [message #625619 is a reply to message #625618] |
Sat, 11 October 2014 07:18 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Talk to your DBA. Speaking as a DBA myself, I hate developers who do not talk to me about their problems (technical problems, not personal) but try to fiddle around by themselves.
|
|
|
|
|
|
|
|
| Re: Need to tune package. [message #625623 is a reply to message #625622] |
Sat, 11 October 2014 08:47 |
swap19
Messages: 11
Registered: October 2010
Location: India
|
Junior Member |
|
|
Some where clause also available there, that filtering the data & it's proper tune query.
if you need some more information please let me know.
|
|
|
|
|
|
|
|
|
|
| Re: Need to tune package. [message #625665 is a reply to message #625626] |
Sun, 12 October 2014 23:25 |
swap19
Messages: 11
Registered: October 2010
Location: India
|
Junior Member |
|
|
Hello Expert,
Sorry for the delay. As already mention above, i do not have much privileges so whatever privileges i have on that basis i will share some code with you.
Please share your email id , i will share code through email conversation. Because this forum open for everyone & my company policy not allow me to share code with somebody.
Please understand me.
Thanks for all your support
Swap19
|
|
|
|
|
|
|
|
| Re: Need to tune package. [message #625671 is a reply to message #625665] |
Mon, 13 October 2014 01:49 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You can mail me a non-disclosure agreement, and then I can mail you a quotation. Then e can proceed on a confidential and commercial basis.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
