Home » RDBMS Server » Performance Tuning » Need to tune package. (Oracle 11.2.0.3.0)
Need to tune package. [message #625611] Sat, 11 October 2014 05:24 Go to next message
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 Go to previous messageGo to next message
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 #625613 is a reply to message #625611] Sat, 11 October 2014 06:24 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your /*+ append */ hint was not working because you placed it incorrectly. It must be immediately after the SELECT key word.

--update: typo

[Updated on: Sat, 11 October 2014 06:25]

Report message to a moderator

Re: Need to tune package. [message #625614 is a reply to message #625612] Sat, 11 October 2014 06:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
John Watson wrote on Sat, 11 October 2014 12:24
Your /*+ 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 #625616 is a reply to message #625615] Sat, 11 October 2014 06:57 Go to previous messageGo to next message
swap19
Messages: 11
Registered: October 2010
Location: India
Junior Member
Please ignore it. While posting mistakenly wrote.
Re: Need to tune package. [message #625617 is a reply to message #625616] Sat, 11 October 2014 07:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
swap19 wrote on Sat, 11 October 2014 12:57
Please 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #625620 is a reply to message #625619] Sat, 11 October 2014 07:26 Go to previous messageGo to next message
swap19
Messages: 11
Registered: October 2010
Location: India
Junior Member
Okay.
That i will discuss with DBA. Apart from this any other approaches that i will try from my end as Developer.
Re: Need to tune package. [message #625622 is a reply to message #625620] Sat, 11 October 2014 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why are you duplicating TABLE_NAME_2 data into TABLE_NAME_1?
It appears to be a design flaw, wasteful & violates Third Normal Form.

Never do in PL/SQL that which can be done in plain SQL.
Re: Need to tune package. [message #625623 is a reply to message #625622] Sat, 11 October 2014 08:47 Go to previous messageGo to next message
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 #625624 is a reply to message #625623] Sat, 11 October 2014 08:50 Go to previous messageGo to next message
swap19
Messages: 11
Registered: October 2010
Location: India
Junior Member
Please let me know, how I can achieve by using plain SQL.
Re: Need to tune package. [message #625625 is a reply to message #625624] Sat, 11 October 2014 09:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We need the actual query and all information mentioned at http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888

Re: Need to tune package. [message #625626 is a reply to message #625625] Sat, 11 October 2014 11:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP, and also provide the trigger details. The code and logic.
Re: Need to tune package. [message #625665 is a reply to message #625626] Sun, 12 October 2014 23:25 Go to previous messageGo to next message
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 #625666 is a reply to message #625665] Sun, 12 October 2014 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You're on Your Own (YOYO)!
Re: Need to tune package. [message #625668 is a reply to message #625665] Mon, 13 October 2014 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Because this forum open for everyone & my company policy not allow me to share code with somebody.


But you want to share it to those that give you their email?
So post it here to get free help or hire someone to get confidential help.

Re: Need to tune package. [message #625671 is a reply to message #625665] Mon, 13 October 2014 01:49 Go to previous message
John Watson
Messages: 8922
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.
Previous Topic: Default value of SEND_BUF_SIZE and RECV_BUF_SIZE for IBM Aix 7
Next Topic: Materialized view refresh thrashing DBWR
Goto Forum:
  


Current Time: Thu Mar 28 06:55:05 CDT 2024