1 2 3 Previous Next 31 Replies Latest reply: Dec 7, 2012 2:52 PM by 940856 RSS

    Parallel Query

    940856
      Hell All,

      Database 11gR2
      Host -- Linux

      I am executing following query from sys schema and the objects owned by some other schema . its using 4 parallel servers to execute , that is what the plan has to say. I also checked the degree for Tables and Indexes , but they all set to 1. Just wondering , what parameters/stuff caused this to use parallelism in spite of no parallelism being used for Tables and indexes.
      SQL> explain plan for
        2  SELECT SUM(IMC.TYPICAL_PERCENT)   TYPICAL_PERCENT
        3        FROM rds.ITEM_MASTER_COMPONENT   IMC,
        4             rds.master_formula          frm
           WHERE FRM.FRM_ID = 2060055 AND
        5    6             IMC.IML_ID = FRM.IML_ID AND
        7             EXISTS (SELECT 'X' FROM DUAL WHERE
        8                     IMC.CMP_ID in (select cmp_id
        9                            from rds.component_regulation cmr, rds.regulatory_application rap
       10                            where cmr.rap_id = rap.rap_id
       11                            and rap.procedure_name = 'CHALLENGE_USA_HYDROCARBON')) AND
       12             frm.iml_id IS NOT NULL
       13  /
      
      Explained.
      
      SQL> select * from table (dbms_xplan.display) ;
      
      
      
      ----------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                             | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
      ----------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                      |                               |     1 |    37 |    16  (13)| 00:00:01 |        |      |            |
      |   1 |  SORT AGGREGATE                       |                               |     1 |    37 |            |          |        |      |            |
      |   2 |   PX COORDINATOR                      |                               |       |       |            |          |        |      |            |
      |   3 |    PX SEND QC (RANDOM)                | :TQ10004                      |     1 |    37 |            |          |  Q1,04 | P->S | QC (RAND)  |
      |   4 |     SORT AGGREGATE                    |                               |     1 |    37 |            |          |  Q1,04 | PCWP |            |
      |   5 |      NESTED LOOPS                     |                               |       |       |            |          |  Q1,04 | PCWP |            |
      |   6 |       NESTED LOOPS                    |                               |     1 |    37 |    16  (13)| 00:00:01 |  Q1,04 | PCWP |            |
      |   7 |        NESTED LOOPS                   |                               |   153 |  3213 |    11  (19)| 00:00:01 |  Q1,04 | PCWP |            |
      |   8 |         BUFFER SORT                   |                               |       |       |            |          |  Q1,04 | PCWC |            |
      |   9 |          PX RECEIVE                   |                               |       |       |            |          |  Q1,04 | PCWP |            |
      |  10 |           PX SEND BROADCAST           | :TQ10002                      |       |       |            |          |        | S->P | BROADCAST  |
      |* 11 |            TABLE ACCESS BY INDEX ROWID| MASTER_FORMULA                |     1 |     8 |     3   (0)| 00:00:01 |        |      |            |
      |* 12 |             INDEX UNIQUE SCAN         | FRM_PK00                      |     1 |       |     2   (0)| 00:00:01 |        |      |            |
      |  13 |         VIEW                          | VW_SQ_1                       |   153 |  1989 |     8  (25)| 00:00:01 |  Q1,04 | PCWP |            |
      |  14 |          HASH UNIQUE                  |                               |   153 |  3519 |            |          |  Q1,04 | PCWP |            |
      |  15 |           BUFFER SORT                 |                               |       |       |            |          |  Q1,04 | PCWC |            |
      |  16 |            PX RECEIVE                 |                               |   153 |  3519 |            |          |  Q1,04 | PCWP |            |
      |  17 |             PX SEND HASH              | :TQ10003                      |   153 |  3519 |            |          |  Q1,03 | P->P | HASH       |
      |  18 |              HASH UNIQUE              |                               |   153 |  3519 |            |          |  Q1,03 | PCWP |            |
      |  19 |               MERGE JOIN              |                               |   153 |  3519 |    22  (14)| 00:00:01 |  Q1,03 | PCWP |            |
      |  20 |                SORT JOIN              |                               | 30200 |   353K|    19  (11)| 00:00:01 |  Q1,03 | PCWP |            |
      |  21 |                 NESTED LOOPS          |                               | 30200 |   353K|    17   (0)| 00:00:01 |  Q1,03 | PCWP |            |
      |  22 |                  BUFFER SORT          |                               |       |       |            |          |  Q1,03 | PCWC |            |
      |  23 |                   PX RECEIVE          |                               |       |       |            |          |  Q1,03 | PCWP |            |
      |  24 |                    PX SEND BROADCAST  | :TQ10000                      |       |       |            |          |        | S->P | BROADCAST  |
      |  25 |                     FAST DUAL         |                               |     1 |       |     2   (0)| 00:00:01 |        |      |            |
      |  26 |                  PX BLOCK ITERATOR    |                               | 30200 |   353K|    15   (0)| 00:00:01 |  Q1,03 | PCWC |            |
      |  27 |                   INDEX FAST FULL SCAN| CMR_UN01                      | 30200 |   353K|    15   (0)| 00:00:01 |  Q1,03 | PCWP |            |
      |* 28 |                SORT JOIN              |                               |     1 |    11 |     3  (34)| 00:00:01 |  Q1,03 | PCWP |            |
      |  29 |                 BUFFER SORT           |                               |       |       |            |          |  Q1,03 | PCWC |            |
      |  30 |                  PX RECEIVE           |                               |     1 |    11 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
      |  31 |                   PX SEND BROADCAST   | :TQ10001                      |     1 |    11 |     2   (0)| 00:00:01 |        | S->P | BROADCAST  |
      |* 32 |                    TABLE ACCESS FULL  | REGULATORY_APPLICATION        |     1 |    11 |     2   (0)| 00:00:01 |        |      |            |
      |* 33 |        INDEX UNIQUE SCAN              | ITEM_MASTER_COMPONENT_NDX_N01 |     1 |       |     1   (0)| 00:00:01 |  Q1,04 | PCWP |            |
      |  34 |       TABLE ACCESS BY INDEX ROWID     | ITEM_MASTER_COMPONENT         |     1 |    16 |     2   (0)| 00:00:01 |  Q1,04 | PCWP |            |
      ----------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
        11 - filter("FRM"."IML_ID" IS NOT NULL)
        12 - access("FRM"."FRM_ID"=2060055)
        28 - access("CMR"."RAP_ID"="RAP"."RAP_ID")
             filter("CMR"."RAP_ID"="RAP"."RAP_ID")
        32 - filter("RAP"."PROCEDURE_NAME"='CHALLENGE_USA_HYDROCARBON')
        33 - access("IMC"."IML_ID"="FRM"."IML_ID" AND "IMC"."CMP_ID"="ITEM_1")
      
      51 rows selected.
      Regards
        • 1. Re: Parallel Query
          VijayaraghavanKrishnan
          Hi,

          1. Check you parallelism parameters are changed whether it is set at the instance level
          2. Check whether the current session is forced for parallel query
          3. Check once again the table and index degree

          Is the table partitioned?

          Thanks,
          Vijay
          • 2. Re: Parallel Query
            jgarry
            Also, don't do user type things from sys.
            • 3. Re: Parallel Query
              940856
              Hello Krishna,

              These are the settings for various parallel parameters. Has , parallel_adaptive_multi_user , anything to do , why there are number of parallel sessions invoked.
              Also, is there any way , I can find , why the parallelism is used here. Thanks for your inputs
              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_io_cap_enabled              boolean     FALSE
              parallel_max_servers                 integer     20
              parallel_min_percent                 integer     0
              parallel_min_servers                 integer     0
              parallel_server                      boolean     FALSE
              parallel_server_instances            integer     1
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ----------
              parallel_threads_per_cpu             integer     2
              recovery_parallelism                 integer     0
              • 4. Re: Parallel Query
                VijayaraghavanKrishnan
                Hi,

                Are you sure that the tables does not have DoP set? The DoP might be setup at your table or index level for sure.

                Please post the output
                select table_name, degree from user_tables where table_name in ('ITEM_MASTER_COMPONENT','MASTER_FORMULA','COMPONENT_REGULATION','REGULATORY_APPLICATION');
                
                select index_name, degree from user_indexes where index_name in ('ITEM_MASTER_COMPONENT_NDX_N01','FRM_PK00');
                Thanks,
                Vijay
                • 5. Re: Parallel Query
                  940856
                  Yes, the degree is 1 for all the tables and indexes.
                  TABLE_NAME                                                                                 DEGREE
                  ------------------------------------------------------------------------------------------ --------------
                  REGULATORY_APPLICATION                                                                              1
                  MASTER_FORMULA                                                                                      1
                  ITEM_MASTER_COMPONENT                                                                               1
                  COMPONENT_REGULATION                                                                                1
                  
                  
                  INDEX_NAME                     DEGREE
                  ------------------------------ --------------------
                  ITEM_MASTER_COMPONENT_NDX_N01  1
                  FRM_PK00                       1
                  Regards
                  • 6. Re: Parallel Query
                    VijayaraghavanKrishnan
                    Hi,

                    If you would like to turn of the parallelism try making "Parallel_max_servers" to Zero value and run the explain plan for you query. But non of your SQL will not run in parallel till the Parallel_max_servers remains Zero.



                    Thanks,
                    Vijay
                    • 7. Re: Parallel Query
                      940856
                      Hello,

                      Thanks for your advise , but what really baffling me here is , why this sql making use of parallelism. We do not want to switch off the parallelism from other folks.
                      do you think , any user level parallelism making this behave like this. I might need to take this matter to oracle support to know about this.

                      Regards
                      • 8. Re: Parallel Query
                        rarain
                        Get the actual execution plan by executing the query because sometimes explain plan don't report the actual execution plan.

                        Edited by: 963750 on Dec 7, 2012 5:07 AM
                        • 9. Re: Parallel Query
                          940856
                          There you go.
                          SQL> alter system set parallel_adaptive_multi_user=false scope=memory;                                                                                                       
                                                                                                                                                                                                      
                          System altered.                                                                                                                                                             
                                                                                                                                                                                                      
                          SQL> explain plan for                                                                                                                                                       
                            2      SELECT SUM(IMC.TYPICAL_PERCENT)   TYPICAL_PERCENT                                                                                                                  
                            3            FROM rds.ITEM_MASTER_COMPONENT   IMC,                                                                                                                        
                            4                 rds.master_formula          frm                                                                                                                         
                            5      WHERE FRM.FRM_ID = 2060055 AND                                                                                                                                     
                            6                   IMC.IML_ID = FRM.IML_ID AND                                                                                                                           
                            7                 EXISTS (SELECT 'X' FROM DUAL WHERE                                                                                                                      
                            8                         IMC.CMP_ID in (select cmp_id                                                                                                                    
                            9                                from rds.component_regulation cmr, rds.regulatory_application rap                                                                        
                                                     where cmr.rap_id = rap.rap_id                                                                                                                    
                           10   11                             and rap.procedure_name = 'CHALLENGE_USA_HYDROCARBON')) AND                                                                             
                           12              frm.iml_id IS NOT NULL                                                                                                                                     
                           13  /                                                                                                                                                                      
                                                                                                                                                                                                      
                          Explained.                                                                                                                                                                  
                                                                                                                                                                                                      
                          SQL> select * from table (dbms_xplan.display) ;                                                                                                                             
                                                                                                                                                                                                      
                                                                                                                                                                                                      
                          ---------------------------------------------------------------------------------------------------------------------------------------------------                         
                          | Id  | Operation                            | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |                         
                          ---------------------------------------------------------------------------------------------------------------------------------------------------                         
                          |   0 | SELECT STATEMENT                     |                               |     1 |    37 |    18  (12)| 00:00:01 |        |      |            |                         
                          |   1 |  SORT AGGREGATE                      |                               |     1 |    37 |            |          |        |      |            |                         
                          |   2 |   PX COORDINATOR                     |                               |       |       |            |          |        |      |            |                         
                          |   3 |    PX SEND QC (RANDOM)               | :TQ10004                      |     1 |    37 |            |          |  Q1,04 | P->S | QC (RAND)  |                         
                          |   4 |     SORT AGGREGATE                   |                               |     1 |    37 |            |          |  Q1,04 | PCWP |            |                         
                          |*  5 |      HASH JOIN SEMI                  |                               |     1 |    37 |    18  (12)| 00:00:01 |  Q1,04 | PCWP |            |                         
                          |   6 |       BUFFER SORT                    |                               |       |       |            |          |  Q1,04 | PCWC |            |                         
                          |   7 |        PX RECEIVE                    |                               |    21 |   504 |     8   (0)| 00:00:01 |  Q1,04 | PCWP |            |                         
                          |   8 |         PX SEND HASH                 | :TQ10002                      |    21 |   504 |     8   (0)| 00:00:01 |        | S->P | HASH       |                         
                          |   9 |          NESTED LOOPS                |                               |    21 |   504 |     8   (0)| 00:00:01 |        |      |            |                         
                          |* 10 |           TABLE ACCESS BY INDEX ROWID| MASTER_FORMULA                |     1 |     8 |     3   (0)| 00:00:01 |        |      |            |                         
                          |* 11 |            INDEX UNIQUE SCAN         | FRM_PK00                      |     1 |       |     2   (0)| 00:00:01 |        |      |            |                         
                          |  12 |           TABLE ACCESS BY INDEX ROWID| ITEM_MASTER_COMPONENT         |    21 |   336 |     5   (0)| 00:00:01 |        |      |            |                         
                          |* 13 |            INDEX RANGE SCAN          | ITEM_MASTER_COMPONENT_NDX_N01 |    21 |       |     2   (0)| 00:00:01 |        |      |            |                         
                          |  14 |       PX RECEIVE                     |                               |   115 |  1495 |    37  (79)| 00:00:01 |  Q1,04 | PCWP |            |                         
                          |  15 |        PX SEND HASH                  | :TQ10003                      |   115 |  1495 |    37  (79)| 00:00:01 |  Q1,03 | P->P | HASH       |                         
                          |  16 |         VIEW                         | VW_SQ_1                       |   115 |  1495 |    37  (79)| 00:00:01 |  Q1,03 | PCWP |            |                         
                          |  17 |          MERGE JOIN                  |                               |   115 |  2645 |    37   (9)| 00:00:01 |  Q1,03 | PCWP |            |                         
                          |  18 |           SORT JOIN                  |                               | 29662 |   347K|    33   (7)| 00:00:01 |  Q1,03 | PCWP |            |                         
                          |  19 |            NESTED LOOPS              |                               | 29662 |   347K|    31   (0)| 00:00:01 |  Q1,03 | PCWP |            |                         
                          |  20 |             BUFFER SORT              |                               |       |       |            |          |  Q1,03 | PCWC |            |                         
                          |  21 |              PX RECEIVE              |                               |       |       |            |          |  Q1,03 | PCWP |            |                         
                          |  22 |               PX SEND BROADCAST      | :TQ10000                      |       |       |            |          |        | S->P | BROADCAST  |                         
                          |  23 |                FAST DUAL             |                               |     1 |       |     2   (0)| 00:00:01 |        |      |            |                         
                          |  24 |             PX BLOCK ITERATOR        |                               | 29662 |   347K|    29   (0)| 00:00:01 |  Q1,03 | PCWC |            |                         
                          |  25 |              INDEX FAST FULL SCAN    | CMR_UN01                      | 29662 |   347K|    29   (0)| 00:00:01 |  Q1,03 | PCWP |            |                         
                          |* 26 |           SORT JOIN                  |                               |     1 |    11 |     4  (25)| 00:00:01 |  Q1,03 | PCWP |            |                         
                          |  27 |            BUFFER SORT               |                               |       |       |            |          |  Q1,03 | PCWC |            |                         
                          |  28 |             PX RECEIVE               |                               |     1 |    11 |     3   (0)| 00:00:01 |  Q1,03 | PCWP |            |                         
                          |  29 |              PX SEND BROADCAST       | :TQ10001                      |     1 |    11 |     3   (0)| 00:00:01 |        | S->P | BROADCAST  |                         
                          |* 30 |               TABLE ACCESS FULL      | REGULATORY_APPLICATION        |     1 |    11 |     3   (0)| 00:00:01 |        |      |            |                         
                          ---------------------------------------------------------------------------------------------------------------------------------------------------                         
                          • 10. Re: Parallel Query
                            Richard Harrison .
                            Hi,
                            Do you have any logon triggers set which are forcing the session into parallel mode?

                            Cheers,
                            Harry
                            • 11. Re: Parallel Query
                              Richard Harrison .
                              Also try


                              select * from table(dbms_xplan.display(null, null, 'outline'));

                              Cheers,
                              Harry
                              • 12. Re: Parallel Query
                                940856
                                Harry,

                                Thanks. following is the output.
                                SQL> alter system set parallel_adaptive_multi_user=false scope=memory;
                                
                                System altered.
                                
                                SQL> select * from table(dbms_xplan.display(null, null, 'outline'));
                                
                                PLAN_TABLE_OUTPUT
                                -------------------------------------------------------------------------------------------------------------------------------------------------------------
                                -------------------------------------------
                                Plan hash value: 1894673998
                                
                                ---------------------------------------------------------------------------------------------------------------------------------------------------
                                | Id  | Operation                            | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
                                ---------------------------------------------------------------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT                     |                               |     1 |    37 |    18  (12)| 00:00:01 |        |      |            |
                                |   1 |  SORT AGGREGATE                      |                               |     1 |    37 |            |          |        |      |            |
                                |   2 |   PX COORDINATOR                     |                               |       |       |            |          |        |      |            |
                                |   3 |    PX SEND QC (RANDOM)               | :TQ10004                      |     1 |    37 |            |          |  Q1,04 | P->S | QC (RAND)  |
                                |   4 |     SORT AGGREGATE                   |                               |     1 |    37 |            |          |  Q1,04 | PCWP |            |
                                |*  5 |      HASH JOIN SEMI                  |                               |     1 |    37 |    18  (12)| 00:00:01 |  Q1,04 | PCWP |            |
                                |   6 |       BUFFER SORT                    |                               |       |       |            |          |  Q1,04 | PCWC |            |
                                |   7 |        PX RECEIVE                    |                               |    21 |   504 |     8   (0)| 00:00:01 |  Q1,04 | PCWP |            |
                                |   8 |         PX SEND HASH                 | :TQ10002                      |    21 |   504 |     8   (0)| 00:00:01 |        | S->P | HASH       |
                                |   9 |          NESTED LOOPS                |                               |    21 |   504 |     8   (0)| 00:00:01 |        |      |            |
                                |* 10 |           TABLE ACCESS BY INDEX ROWID| MASTER_FORMULA                |     1 |     8 |     3   (0)| 00:00:01 |        |      |            |
                                |* 11 |            INDEX UNIQUE SCAN         | FRM_PK00                      |     1 |       |     2   (0)| 00:00:01 |        |      |            |
                                |  12 |           TABLE ACCESS BY INDEX ROWID| ITEM_MASTER_COMPONENT         |    21 |   336 |     5   (0)| 00:00:01 |        |      |            |
                                |* 13 |            INDEX RANGE SCAN          | ITEM_MASTER_COMPONENT_NDX_N01 |    21 |       |     2   (0)| 00:00:01 |        |      |            |
                                |  14 |       PX RECEIVE                     |                               |   115 |  1495 |    37  (79)| 00:00:01 |  Q1,04 | PCWP |            |
                                |  15 |        PX SEND HASH                  | :TQ10003                      |   115 |  1495 |    37  (79)| 00:00:01 |  Q1,03 | P->P | HASH       |
                                |  16 |         VIEW                         | VW_SQ_1                       |   115 |  1495 |    37  (79)| 00:00:01 |  Q1,03 | PCWP |            |
                                |  17 |          MERGE JOIN                  |                               |   115 |  2645 |    37   (9)| 00:00:01 |  Q1,03 | PCWP |            |
                                |  18 |           SORT JOIN                  |                               | 29662 |   347K|    33   (7)| 00:00:01 |  Q1,03 | PCWP |            |
                                |  19 |            NESTED LOOPS              |                               | 29662 |   347K|    31   (0)| 00:00:01 |  Q1,03 | PCWP |            |
                                |  20 |             BUFFER SORT              |                               |       |       |            |          |  Q1,03 | PCWC |            |
                                |  21 |              PX RECEIVE              |                               |       |       |            |          |  Q1,03 | PCWP |            |
                                |  22 |               PX SEND BROADCAST      | :TQ10000                      |       |       |            |          |        | S->P | BROADCAST  |
                                |  23 |                FAST DUAL             |                               |     1 |       |     2   (0)| 00:00:01 |        |      |            |
                                |  24 |             PX BLOCK ITERATOR        |                               | 29662 |   347K|    29   (0)| 00:00:01 |  Q1,03 | PCWC |            |
                                |  25 |              INDEX FAST FULL SCAN    | CMR_UN01                      | 29662 |   347K|    29   (0)| 00:00:01 |  Q1,03 | PCWP |            |
                                |* 26 |           SORT JOIN                  |                               |     1 |    11 |     4  (25)| 00:00:01 |  Q1,03 | PCWP |            |
                                |  27 |            BUFFER SORT               |                               |       |       |            |          |  Q1,03 | PCWC |            |
                                |  28 |             PX RECEIVE               |                               |     1 |    11 |     3   (0)| 00:00:01 |  Q1,03 | PCWP |            |
                                |  29 |              PX SEND BROADCAST       | :TQ10001                      |     1 |    11 |     3   (0)| 00:00:01 |        | S->P | BROADCAST  |
                                |* 30 |               TABLE ACCESS FULL      | REGULATORY_APPLICATION        |     1 |    11 |     3   (0)| 00:00:01 |        |      |            |
                                ---------------------------------------------------------------------------------------------------------------------------------------------------
                                
                                Outline Data
                                -------------
                                
                                  /*+
                                      BEGIN_OUTLINE_DATA
                                      PQ_DISTRIBUTE(@"SEL$CD57E2F5" "RAP"@"SEL$3" NONE BROADCAST)
                                      PQ_DISTRIBUTE(@"SEL$CD57E2F5" "CMR"@"SEL$3" BROADCAST NONE)
                                      USE_MERGE(@"SEL$CD57E2F5" "RAP"@"SEL$3")
                                      USE_NL(@"SEL$CD57E2F5" "CMR"@"SEL$3")
                                      LEADING(@"SEL$CD57E2F5" "DUAL"@"SEL$2" "CMR"@"SEL$3" "RAP"@"SEL$3")
                                      FULL(@"SEL$CD57E2F5" "RAP"@"SEL$3")
                                      INDEX_FFS(@"SEL$CD57E2F5" "CMR"@"SEL$3" ("COMPONENT_REGULATION"."CMP_ID" "COMPONENT_REGULATION"."CNT_ID" "COMPONENT_REGULATION"."RRS_ID"
                                              "COMPONENT_REGULATION"."RAP_ID"))
                                      PQ_DISTRIBUTE(@"SEL$BE3D019A" "VW_SQ_1"@"SEL$94B5B0D0" HASH HASH)
                                      USE_HASH(@"SEL$BE3D019A" "VW_SQ_1"@"SEL$94B5B0D0")
                                      USE_NL(@"SEL$BE3D019A" "IMC"@"SEL$1")
                                      LEADING(@"SEL$BE3D019A" "FRM"@"SEL$1" "IMC"@"SEL$1" "VW_SQ_1"@"SEL$94B5B0D0")
                                      NO_ACCESS(@"SEL$BE3D019A" "VW_SQ_1"@"SEL$94B5B0D0")
                                      INDEX_RS_ASC(@"SEL$BE3D019A" "IMC"@"SEL$1" ("ITEM_MASTER_COMPONENT"."IML_ID" "ITEM_MASTER_COMPONENT"."CMP_ID"))
                                      INDEX_RS_ASC(@"SEL$BE3D019A" "FRM"@"SEL$1" ("MASTER_FORMULA"."FRM_ID"))
                                      OUTLINE(@"SEL$1")
                                      OUTLINE(@"SEL$3")
                                      OUTLINE(@"SEL$2")
                                      OUTLINE(@"SEL$94B5B0D0")
                                      UNNEST(@"SEL$3")
                                      OUTLINE(@"SEL$BE5C8E60")
                                      UNNEST(@"SEL$BE5C8E60")
                                      OUTLINE_LEAF(@"SEL$BE3D019A")
                                      OUTLINE_LEAF(@"SEL$CD57E2F5")
                                      DB_VERSION('11.2.0.3')
                                      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
                                      IGNORE_OPTIM_EMBEDDED_HINTS
                                      END_OUTLINE_DATA
                                  */
                                
                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                
                                   5 - access("IMC"."CMP_ID"="ITEM_1")
                                  10 - filter("FRM"."IML_ID" IS NOT NULL)
                                  11 - access("FRM"."FRM_ID"=2060055)
                                  13 - access("IMC"."IML_ID"="FRM"."IML_ID")
                                  26 - access("CMR"."RAP_ID"="RAP"."RAP_ID")
                                       filter("CMR"."RAP_ID"="RAP"."RAP_ID")
                                  30 - filter("RAP"."PROCEDURE_NAME"='CHALLENGE_USA_HYDROCARBON')
                                
                                83 rows selected.
                                Regards
                                • 13. Re: Parallel Query
                                  Richard Harrison .
                                  Hi,
                                  I was trying to see if it was using a sql profile or not and wasn't sure if the default xplan showed it or not - i thought the outline option would show it but that just seems to have generated the code to enable you to build a stored outline. Try

                                  select * from table(dbms_xplan.display(null, null, 'ALL'));

                                  If it doesn't show then there is no profile in place.


                                  What happens if you run this before the SQL?

                                  alter session disable parallel ddl;
                                  alter session disable parallel dml;

                                  Cheers,
                                  Harry
                                  • 14. Re: Parallel Query
                                    rahulras
                                    What is the value for database level parameter PARALLEL_DEGREE_POLICY ?
                                    If that is AUTO, Oracle decides if it wants to use parallelism (and it has full permission to use parallel query if it think so).

                                    Set that parameter to MANUAL, if you don't want Oracle to make this decision.
                                    1 2 3 Previous Next