Solved

Parallel hint not working in an insert

Posted on 2008-10-16
7
2,121 Views
Last Modified: 2012-05-05
Here is my INSERT pseudo code.  How come parallel not working?  Working on another insert in the same database with same source table.  Thanks.

 INSERT /*+ append */ INTO BIG_TABLE
         SELECT   /*+ index (source_table my_idx1)  parallel_index(source_table, my_idx1,14)
                DISTINCT  ...
                    FROM
                  (SELECT /*+ index (source_table my_idx1)  parallel_index(source_table, my_idx1,14)
                                 from ....big query)
            where ...
0
Comment
Question by:ewang1205
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 100 total points
ID: 22737369
what is the result of the following from sqlplus:

show parameter parallel
0
 

Author Comment

by:ewang1205
ID: 22740846
Here is the results.  Parallel works on this server because some other parallel queries work fine.  I guess my query may be too complex.

SQL> show parameter parallel;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_max_servers                 integer     385
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
0
 

Author Comment

by:ewang1205
ID: 22744003
Might be limitation on parallel.  Will parallel still work if I have ROLLUP function in the SQL query?  Thanks.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:ewang1205
ID: 22757808
Looks like parallel doesn't work on ROLLUP function in the SQL query.  I tried without ROLLUP function in the SQL query and parallel hint works.  
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 100 total points
ID: 22775316
the settings looks fine to me.

Parallelism doesnt work for query part or the insert part? or for both?
0
 

Author Comment

by:ewang1205
ID: 22778367
Parallelism doesnt work for query part .  
0
 

Accepted Solution

by:
ewang1205 earned 0 total points
ID: 22835824
I conclude the parallel doesn't always work.  It doesn't work where the query is too complex.  In my case, the query has analytical function called rollup, the parallel  doesn't work.  I will accept my answer as solution if there is no other objections.  Thanks.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

734 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