CODEWITHSUNDEEP
oracle-databaseoptimizationappendhint
Ram
Ram

Reputation: 825

Parallel hint in Oracle DB

I want to use parallel hint in my select query to improve performance. But this select statement contains more than one tables in the from clause. How can we use parallel hint in the select statement.

INSERT INTO /*+APPEND */ ITEM
    (
    )
    
    SELECT a FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>
    
    UNION
    
    SELECT a FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>

Upvotes: 2

Views: 8102

Answers (2)

Paul W
Paul W

Reputation: 11603

Prior to 11g, you had to provide a hint for every table you wanted parallelism for. Since 11g, you can omit the table alias and a single parallel hint applies to the whole statement. Oracle will decide for itself what is to be parallelized and what not, and typically it will tend to parallelize most everything. The degree specified will be used for all objects where possible. It's simply:

INSERT /*+ APPEND PARALLEL(16) */ INTO ITEM
    (
    )
    SELECT a 
    FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>
    UNION
    SELECT a 
    FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>

Keep in mind that the parallel hint will apply to the select portion, not the insert because that would require enabling parallel dml (ALTER SESSION ENABLE PARALLEL DML or the hint enable_parallel_dml). So to parallelize the whole thing including the block loading step of the insert, you need:

INSERT /*+ APPEND PARALLEL(16) enable_parallel_dml */ INTO ITEM
    (
    )
    SELECT a 
    FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>
    UNION
    SELECT a 
    FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>

Lastly, note that I shifted your hint one word back, to right after the keyword INSERT. Oracle only looks for hints immediately following the initial keyword of a SQL block.

With regard to choosing a DOP (degree of parallelism, 16 in this case), you have several factors to consider:

 1. What is the parallel_max_servers
 2. Is parallel_degree_policy = manual or auto?
 3. If auto, what is parallel_degree_limit set to?
 4. Has the DBA overridden hints with optimizer_ignore_parallel_hints?
 5. How many CPU cores are on the host(s) the database is mounted on?
 6. How many others sessions might be using how many parallel slaves at the same time?
 7. Is there a DBRM (Resource Manager) rule defined that caps DOP for your consumer group?
 8. How big are the tables you are scanning or joining?

Because of the complexity of all this, it is best to simply ask your DBA what is recommended for this system. If you use too much, you can cause problems for the database. I recommend starting out with 4 and only increasing if needed, assuming sufficient resources. Numbers of 8 and 16 are not special, they're just conventional among programmers.

Upvotes: 2

Aksen P
Aksen P

Reputation: 4599

Let's say your PARALLEL_MAX_SERVERS value is 80. Then you can use it:

INSERT INTO /*+APPEND */ ITEM
    (
    )
    
    SELECT /*+ PARALLEL(p, 8) PARALLEL(r, 16) */ a 
    FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>
    
    UNION
    
    SELECT /*+ PARALLEL(p, 8) PARALLEL(r, 16) */ a 
    FROM GTT_LINE p, GTT_LINE_XY r
    Where <condition>

Note: 8 and 16 in sum shouldn't exceed the maximum value of 80. To check the maximum value you have to log-in as SYS or SYSDBA and run the next command:

SELECT name, value FROM v$parameter WHERE name IN ('parallel_max_servers', 'parallel_execution_enabled');

Upvotes: -1

Related Questions