This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Dec 7, 2012 12:52 PM by 940856 RSS

Parallel Query

940856 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Also, don't do user type things from sys.
  • 3. Re: Parallel Query
    940856 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    Hi,
    Do you have any logon triggers set which are forcing the session into parallel mode?

    Cheers,
    Harry
  • 11. Re: Parallel Query
    Richard Harrison . Expert
    Currently Being Moderated
    Also try


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

    Cheers,
    Harry
  • 12. Re: Parallel Query
    940856 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points