Home » RDBMS Server » Performance Tuning » How to use optimizer hint in a recursivly called procedure
How to use optimizer hint in a recursivly called procedure [message #249915] Fri, 06 July 2007 09:01 Go to next message
kmkan28
Messages: 14
Registered: December 2005
Junior Member
hi all,
A recursive procedure is given here.Based on the condition it self the procedure called itself.my concern is how to set a optimizer hint in order to reduce the execution time.it works only for the little amount of data but not for huge volume of data.any one please assist me to get this problem resolved.

the procedure is

CREATE OR REPLACE PROCEDURE CHILD_TO_PARENT(P_ITEM_CODE VARCHAR2)
IS

M_LEVEL VARCHAR2(12);
M_PARENT_ITEM VARCHAR2(12);
M_PARENT_ITEM1 VARCHAR2(12);
M_CHILD_ITEM VARCHAR2(12);
M_NULL VARCHAR2(12);
M_DUMMY VARCHAR2(1) DEFAULT NULL;

CURSOR C1 IS
SELECT /*+ ALL_ROWS */ LEVEL BOM_LEVEL , BOM_ITEM_CODE BOM_PARENT_ITEM ,
BI_ITEM_CODE BOM_CHILD_ITEM
FROM ( SELECT BOM_CODE , BOM_ITEM_CODE ,
BI_ITEM_CODE , BI_BOM_CODE
FROM OM_BOM,OM_BOM_ITEM_DETAIL
WHERE BOM_CODE = BI_BOM_CODE
)
WHERE BI_BOM_CODE = BOM_CODE AND LEVEL=1
CONNECT BY PRIOR BI_ITEM_CODE = BOM_ITEM_CODE
START WITH BI_ITEM_CODE = P_ITEM_CODE
ORDER BY LEVEL;


CURSOR C2 IS
SELECT 'X' FROM OM_BOM_ITEM_DETAIL
WHERE BI_ITEM_CODE=M_PARENT_ITEM;



BEGIN

OPEN C1;
LOOP
FETCH C1 INTO M_LEVEL ,M_PARENT_ITEM,M_CHILD_ITEM;

IF C1%FOUND THEN
CHILD_TO_PARENT(M_PARENT_ITEM);
IF M_LEVEL=1 AND M_PARENT_ITEM IS NOT NULL THEN
OPEN C2;
FETCH C2 INTO M_DUMMY;
IF M_DUMMY IS NULL THEN
DBMS_OUTPUT.PUT_LINE('TRUE '||M_LEVEL||'--'||M_PARENT_ITEM||'--'||M_CHILD_ITEM);
INSERT INTO TEMP5 VALUES(M_PARENT_ITEM);
END IF;
CLOSE C2;
END IF;
ELSE
EXIT;
END IF;
END LOOP;
COMMIT;
CLOSE C1;
END;
/
Re: How to use optimizer hint in a recursivly called procedure [message #249949 is a reply to message #249915] Fri, 06 July 2007 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then, don't use hint unless you know what they do and how the optimizer works.
My advice, remove the hint.
I don't know what your procedure does but I'm pretty sure you can do without it.

Regards
Michel
Re: How to use optimizer hint in a recursivly called procedure [message #250187 is a reply to message #249949] Mon, 09 July 2007 01:05 Go to previous message
kmkan28
Messages: 14
Registered: December 2005
Junior Member
micheal,
thanks for your reply.i just forget to mentioned the version of the oracle that i am using.the oracle version is 9i.
The procedure CHILD_TO_PARENT is called in side the program with the input of processed cursor value.
The value which is brought in to the procedure AGAIN execute the same procedure.
The Execution time is reduced after the use of hint.
Without the hint it will work but the Time to execute the procedure pays a lot time.that's why i requested the expert's suggestion.

Previous Topic: Help : Understanding the Explain Plan
Next Topic: Partitioned table takes longer to load than ordinary table
Goto Forum:
  


Current Time: Thu Dec 08 22:04:51 CST 2016

Total time taken to generate the page: 0.11446 seconds