• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 634
  • Last Modified:

negating hint

There was sql hint in one of the script as

/*+ append parallel(TAB_NAME 8) */

since dba's are recommending not to use append parallel hint in 11g r2 since its has issue in oracle..i did the following modification in the script:

/*+ *****append parallel(TAB_NAME 8) */

now I have two questions:

1. Dba is saying oracle has some bad effects with modification like above.. Is it true?

With the changes as i did above, will not negate the use of hint?

2. My 2nd question is, it it true that append parallel hints have negative impact on the performance of data base in 11g. We were asked to remove these hints in all the scripts.
Has oracle said this any where?

Please advice..
  • 2
2 Solutions
Naveen KumarProduction Manager / Application Support ManagerCommented:
1) it will negate because the hint has to follow immediately after /*+ ... */
But because anyway you are negating the hint, why do you want to put more * like this /*+ *****append parallel(TAB_NAME 8) */  and you can remove it from the sql statement right.

2) Oracle has said both things... earlier when oracle was with 7/8 as the latest versions they said if your queries have performance issues/problems, then we have a solution and you can try to use hints to make them faster. Once they are in 9i/10g, they kind of started saying optimizer has all best features so no need to use hints etc though they were still supported etc. Once they are with 11g version and any future versions, they will say get rid of hints etc in your code as they might interrupt / possible cause the way the optimizer not to work in the way it is supposed/designed to work.

But you need understand by removing them in 11i, is your code going to work or it will be back with performance issues. You need to test in test environments and then decide what you wanted to do.

Geert GruwezOracle dbaCommented:
i just remove the + after /* to disable hints
gs79Author Commented:
I think what they want is 'direct path' insert not to happen..

They are still ok if there its a conventional insert happening in parallel

I think using parallel hint and enabling for parallel dml (using alter session enable parallelel dml), it will take a direct path approach..

May be i need to use no append hint? to ensure parallel dml with conventional insert?

gs79Author Commented:
They didnt want direct path to be used for the inserts by using append parallel hints. I think using noappend parallel hint solved the problem..thank you for your response

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now