Home » RDBMS Server » Performance Tuning » APPEND hint in Rule and Cost based optimizer (oracle 10g)
APPEND hint in Rule and Cost based optimizer [message #434640] Thu, 10 December 2009 01:22 Go to next message
ganesh104
Messages: 5
Registered: November 2009
Location: India
Junior Member
hi,
There is a select which include hint /*+APPEND*/ while inserting the record. Query is like:
INSERT      /*+ APPEND */
       INTO Table1   (Col1, Col2, Col3,......)
       SELECT (select statemment here)....


This query used to process a batch and post the atm fee charges. Batch was performing good when database was on RULE base optimizer. But now client has upgraded the system and database is on COST based optimizer.
Now this batch is taking too long to execute.
Is there anything to do with this append hint? how this is getting affected on Rule and Cost?
Re: APPEND hint in Rule and Cost based optimizer [message #434647 is a reply to message #434640] Thu, 10 December 2009 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As RBO does not know hints it was not used, so I should say remove it.

Regards
Michel

[Updated on: Thu, 10 December 2009 01:57]

Report message to a moderator

Re: APPEND hint in Rule and Cost based optimizer [message #434663 is a reply to message #434647] Thu, 10 December 2009 03:10 Go to previous messageGo to next message
ganesh104
Messages: 5
Registered: November 2009
Location: India
Junior Member
ok..
Current database is on Cost Base. so i need to remove the /*+ Append*/ hint in CBO?

Re: APPEND hint in Rule and Cost based optimizer [message #434672 is a reply to message #434663] Thu, 10 December 2009 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to have the same behaviour than previously, yes.

Regards
Michel
Re: APPEND hint in Rule and Cost based optimizer [message #434679 is a reply to message #434672] Thu, 10 December 2009 04:15 Go to previous messageGo to next message
ganesh104
Messages: 5
Registered: November 2009
Location: India
Junior Member
Ok...
Thanks for the suggession. I will make the changes and try..

thanks again.. Razz
Re: APPEND hint in Rule and Cost based optimizer [message #434803 is a reply to message #434679] Thu, 10 December 2009 19:35 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think the hint is a red herring - it is very unlikely to be your problem. Shifting to CBO would only me the hint work (if it wasn't already), which would make the job faster.

Much more likely is that the SELECT part of the INSERT has changed plan since the upgrade. Get an EXPLAIN PLAN under both CBO and RBO and post them here.

Ross Leishman
Previous Topic: require to tune a sql query
Next Topic: Query to be restructured for performance gain
Goto Forum:
  


Current Time: Thu Dec 08 06:24:26 CST 2016

Total time taken to generate the page: 0.14100 seconds