negating hint

Posted on 2012-09-05
Last Modified: 2012-09-09
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..
Question by:gs79
    LVL 28

    Accepted Solution

    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.

    LVL 36

    Assisted Solution

    by:Geert Gruwez
    i just remove the + after /* to disable hints

    Author Comment

    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?


    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Article by: Swadhin
    From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now