This discussion is archived
1 2 3 Previous Next 41 Replies Latest reply: Aug 26, 2012 3:23 AM by Mustafa KALAYCI Go to original post RSS
  • 30. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    hi Charles,

    HAREKET_TABLE is in my user's schema and there is one public synonym which indicates HAREKET_TABLE table. there is no any other HAREKET_TABLE in db (after so many tests, I couldn't be sure and check it too). and setting table as NOPARALLEL, I also do that and also set degree as 1 too. same again.

    I am not able to list parallel parameters (no connection to db) tomorrow, I will do that and also got an 10053 trace too.

    thanks for interest and answers by the way.
  • 31. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    Hello everyone,

    me again :-)

    finally I've got 10053 trace,

    here it is:
    http://yadi.sk/d/bwlTsyMpEYpz

    the file is too big, so I couldn't put the content here.

    I'm not working with 10053 trace for a long time so I would be appreciate if you look at it.
    -----------------------------------------------------------------------------------------------------------------------------

    also while I was checking something else, I found something come strange to me. when I select V$DB_OBJECT_CACHE view, HAREKET_TABLE (base of my problems) and all of it's indexes are listed as *"MULTI-VERSIONED OBJECT"* type.
    select * from V$DB_OBJECT_CACHE where type = 'MULTI-VERSIONED OBJECT' and owner = 'SG';
    
    NAME                         NAMESPACE                           TYPE                      STATUS
    I_NEW_HAREKET_1              MULTI-VERSION OBJECT FOR INDEX      MULTI-VERSIONED OBJECT    VALID
    NEW_HAREKET81_NDX            MULTI-VERSION OBJECT FOR INDEX      MULTI-VERSIONED OBJECT    VALID
    NEW_HAREKET_NDX              MULTI-VERSION OBJECT FOR INDEX      MULTI-VERSIONED OBJECT    VALID
    NEW_TARIK_NDX                MULTI-VERSION OBJECT FOR INDEX      MULTI-VERSIONED OBJECT    VALID
    NEW_HAREKET_MAHEKOL_NDX      MULTI-VERSION OBJECT FOR INDEX      MULTI-VERSIONED OBJECT    VALID
    NEW_HAREKET6_NDX             MULTI-VERSION OBJECT FOR INDEX      MULTI-VERSIONED OBJECT    VALID
    NEW_HAREKET_POLICE_NDX       MULTI-VERSION OBJECT FOR INDEX      MULTI-VERSIONED OBJECT    VALID
    I_NEW_HAREKET_2              MULTI-VERSION OBJECT FOR INDEX      MULTI-VERSIONED OBJECT    VALID
    HAREKET_TABLE                MULTI-VERSION OBJECT FOR TABLE      MULTI-VERSIONED OBJECT    VALID
    I've never heard about MULTI-VERSION OBJECT, so I looked for it and find this:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7037830724934

    at the end of page, Tom says:
    >
    It probably has to do with multiple versions of statistics in the database, can happen with things during statistics gathering and at other times.
    >

    what do you thing, does it be relevant to parallel process problem ?

    Edited by: Mustafa KALAYCI on Aug 16, 2012 9:52 AM
  • 32. Re: why is my sql running parallel ?
    rp0428 Guru
    Currently Being Moderated
    >
    the file is too big, so I couldn't put the content here.
    >
    Well then it isn't likely to get looked at since this site is where most of us look at things. Most volunteers are not going to visit unknown, unsecure links.
  • 33. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    rp0428, what is your advise ? that is a yandex disk link. I just upload the file to my disk area and link it. at first, what is your suggest ? unfotunately forum doesn't support uploading files right ?

    just wanted to keep forum clean. whatever, here it is part by part (it is not fit in one post).
    Dump file /oracle/u01/app/oracle/diag/rdbms/teb/TEB/trace/teb_ora_50659554.trc
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    ORACLE_HOME = /oracle/u01/app/oracle/product/11.2.0/dbhome_2
    System name:     AIX
    Node name:     TRZSPRODDB
    Release:     1
    Version:     6
    Machine:     00F627BD4C00
    Instance name: TEB
    Redo thread mounted by this instance: 1
    Oracle process number: 48
    Unix process pid: 50659554, image: oracle@TRZSPRODDB
    
    
    *** 2012-08-16 16:25:09.986
    *** SESSION ID:(3680.4459) 2012-08-16 16:25:09.986
    *** CLIENT ID:() 2012-08-16 16:25:09.986
    *** SERVICE NAME:(SYS$USERS) 2012-08-16 16:25:09.986
    *** MODULE NAME:(SQL Developer) 2012-08-16 16:25:09.986
    *** ACTION NAME:() 2012-08-16 16:25:09.986
     
    Registered qb: SEL$1 0x10d1cef0 (PARSER)
    ---------------------
    QUERY BLOCK SIGNATURE
    ---------------------
      signature (): qb_name=SEL$1 nbfros=1 flg=0
        fro(0): flg=4 objn=571873 hint_alias="HAREKET_TABLE"@"SEL$1"
    SPM: statement not found in SMB
    Dynamic sampling level auto-adjusted from 6 to 6
    **************************
    Automatic degree of parallelism (ADOP)
    **************************
    Automatic degree of parallelism is disabled: Parameter.
    PM: Considering predicate move-around in query block SEL$1 (#0)
    **************************
    Predicate Move-Around (PM)
    **************************
    OPTIMIZER INFORMATION
    ******************************************
    ----- Current SQL Statement for this session (sql_id=ct3jkvbv19af7) -----
    select *
      from   hareket_table
      where islem_tar between to_date('01/05/2012', 'dd/mm/yyyy') and to_date('14/07/2012', 'dd/mm/yyyy')
    *******************************************
    Legend
    The following abbreviations are used by optimizer trace.
    CBQT - cost-based query transformation
    JPPD - join predicate push-down
    OJPPD - old-style (non-cost-based) JPPD
    FPD - filter push-down
    PM - predicate move-around
    CVM - complex view merging
    SPJ - select-project-join
    SJC - set join conversion
    SU - subquery unnesting
    OBYE - order by elimination
    OST - old style star transformation
    ST - new (cbqt) star transformation
    CNT - count(col) to count(*) transformation
    JE - Join Elimination
    JF - join factorization
    SLP - select list pruning
    DP - distinct placement
    qb - query block
    LB - leaf blocks
    DK - distinct keys
    LB/K - average number of leaf blocks per key
    DB/K - average number of data blocks per key
    CLUF - clustering factor
    NDV - number of distinct values
    Resp - response cost
    Card - cardinality
    Resc - resource cost
    NL - nested loops (join)
    SM - sort merge (join)
    HA - hash (join)
    CPUSPEED - CPU Speed 
    IOTFRSPEED - I/O transfer speed
    IOSEEKTIM - I/O seek time
    SREADTIM - average single block read time
    MREADTIM - average multiblock read time
    MBRC - average multiblock read count
    MAXTHR - maximum I/O system throughput
    SLAVETHR - average slave I/O throughput
    dmeth - distribution method
      1: no partitioning required
      2: value partitioned
      4: right is random (round-robin)
      128: left is random (round-robin)
      8: broadcast right and partition left
      16: broadcast left and partition right
      32: partition left using partitioning of right
      64: partition right using partitioning of left
      256: run the join in serial
      0: invalid distribution method
    sel - selectivity
    ptn - partition
    ***************************************
    PARAMETERS USED BY THE OPTIMIZER
    ********************************
      *************************************
      PARAMETERS WITH ALTERED VALUES
      ******************************
    Compilation Environment Dump
    optimizer_index_cost_adj            = 30
    optimizer_index_caching             = 50
    query_rewrite_enabled               = false
    parallel_query_default_dop          = 20
    db_file_multiblock_read_count       = 16
    Bug Fix Control Environment
      *************************************
      PARAMETERS WITH DEFAULT VALUES
      ******************************
    Compilation Environment Dump
    optimizer_mode_hinted               = false
    optimizer_features_hinted           = 0.0.0
    parallel_execution_enabled          = true
    parallel_query_forced_dop           = 0
    parallel_dml_forced_dop             = 0
    parallel_ddl_forced_degree          = 0
    parallel_ddl_forced_instances       = 0
    _query_rewrite_fudge                = 90
    optimizer_features_enable           = 11.2.0.2
    _optimizer_search_limit             = 5
    cpu_count                           = 10
    active_instance_count               = 1
    parallel_threads_per_cpu            = 2
    hash_area_size                      = 131072
    bitmap_merge_area_size              = 1048576
    sort_area_size                      = 65536
    sort_area_retained_size             = 0
    _sort_elimination_cost_ratio        = 0
    _optimizer_block_size               = 16384
    _sort_multiblock_read_count         = 2
    _hash_multiblock_io_count           = 0
    _db_file_optimizer_read_count       = 16
    _optimizer_max_permutations         = 2000
    pga_aggregate_target                = 12582912 KB
    _pga_max_size                       = 2097152 KB
    _query_rewrite_maxdisjunct          = 257
    _smm_auto_min_io_size               = 48 KB
    _smm_auto_max_io_size               = 240 KB
    _smm_min_size                       = 1024 KB
    _smm_max_size                       = 1048576 KB
    _smm_px_max_size                    = 6291456 KB
    _cpu_to_io                          = 0
    _optimizer_undo_cost_change         = 11.2.0.2
    parallel_query_mode                 = enabled
    parallel_dml_mode                   = disabled
    parallel_ddl_mode                   = enabled
    optimizer_mode                      = all_rows
    sqlstat_enabled                     = false
    _optimizer_percent_parallel         = 101
    _always_anti_join                   = choose
    _always_semi_join                   = choose
    _optimizer_mode_force               = true
    _partition_view_enabled             = true
    _always_star_transformation         = false
    _query_rewrite_or_error             = false
    _hash_join_enabled                  = true
    cursor_sharing                      = exact
    _b_tree_bitmap_plans                = true
    star_transformation_enabled         = false
    _optimizer_cost_model               = choose
    _new_sort_cost_estimate             = true
    _complex_view_merging               = true
    _unnest_subquery                    = true
    _eliminate_common_subexpr           = true
    _pred_move_around                   = true
    _convert_set_to_join                = false
    _push_join_predicate                = true
    _push_join_union_view               = true
    _fast_full_scan_enabled             = true
    _optim_enhance_nnull_detection      = true
    _parallel_broadcast_enabled         = true
    _px_broadcast_fudge_factor          = 100
    _ordered_nested_loop                = true
    _no_or_expansion                    = false
    _system_index_caching               = 0
    _disable_datalayer_sampling         = false
    query_rewrite_integrity             = enforced
    _query_cost_rewrite                 = true
    _query_rewrite_2                    = true
    _query_rewrite_1                    = true
    _query_rewrite_expression           = true
    _query_rewrite_jgmigrate            = true
    _query_rewrite_fpc                  = true
    _query_rewrite_drj                  = true
    _full_pwise_join_enabled            = true
    _partial_pwise_join_enabled         = true
    _left_nested_loops_random           = true
    _improved_row_length_enabled        = true
    _index_join_enabled                 = true
    _enable_type_dep_selectivity        = true
    _improved_outerjoin_card            = true
    _optimizer_adjust_for_nulls         = true
    _optimizer_degree                   = 0
    _use_column_stats_for_function      = true
    _subquery_pruning_enabled           = true
    _subquery_pruning_mv_enabled        = false
    _or_expand_nvl_predicate            = true
    _like_with_bind_as_equality         = false
    _table_scan_cost_plus_one           = true
    _cost_equality_semi_join            = true
    _default_non_equality_sel_check     = true
    _new_initial_join_orders            = true
    _oneside_colstat_for_equijoins      = true
    _optim_peek_user_binds              = true
    _minimal_stats_aggregation          = true
    _force_temptables_for_gsets         = false
    workarea_size_policy                = auto
    _smm_auto_cost_enabled              = true
    _gs_anti_semi_join_allowed          = true
    _optim_new_default_join_sel         = true
    optimizer_dynamic_sampling          = 2
    _pre_rewrite_push_pred              = true
    _optimizer_new_join_card_computation = true
    _union_rewrite_for_gs               = yes_gset_mvs
    _generalized_pruning_enabled        = true
    _optim_adjust_for_part_skews        = true
    _force_datefold_trunc               = false
    statistics_level                    = typical
    _optimizer_system_stats_usage       = true
    skip_unusable_indexes               = true
    _remove_aggr_subquery               = true
    _optimizer_push_down_distinct       = 0
    _dml_monitoring_enabled             = true
    _optimizer_undo_changes             = false
    _predicate_elimination_enabled      = true
    _nested_loop_fudge                  = 100
    _project_view_columns               = true
    _local_communication_costing_enabled = true
    _local_communication_ratio          = 50
    _query_rewrite_vop_cleanup          = true
    _slave_mapping_enabled              = true
    _optimizer_cost_based_transformation = linear
    _optimizer_mjc_enabled              = true
    _right_outer_hash_enable            = true
    _spr_push_pred_refspr               = true
    _optimizer_cache_stats              = false
    _optimizer_cbqt_factor              = 50
    _optimizer_squ_bottomup             = true
    _fic_area_size                      = 131072
    _optimizer_skip_scan_enabled        = true
    _optimizer_cost_filter_pred         = false
    _optimizer_sortmerge_join_enabled   = true
    _optimizer_join_sel_sanity_check    = true
    _mmv_query_rewrite_enabled          = true
    _bt_mmv_query_rewrite_enabled       = true
    _add_stale_mv_to_dependency_list    = true
    _distinct_view_unnesting            = false
    _optimizer_dim_subq_join_sel        = true
    _optimizer_disable_strans_sanity_checks = 0
    _optimizer_compute_index_stats      = true
    _push_join_union_view2              = true
    _optimizer_ignore_hints             = false
    _optimizer_random_plan              = 0
    _query_rewrite_setopgrw_enable      = true
    _optimizer_correct_sq_selectivity   = true
    _disable_function_based_index       = false
    _optimizer_join_order_control       = 3
    _optimizer_cartesian_enabled        = true
    _optimizer_starplan_enabled         = true
    _extended_pruning_enabled           = true
    _optimizer_push_pred_cost_based     = true
    _optimizer_null_aware_antijoin      = true
    _optimizer_extend_jppd_view_types   = true
    _sql_model_unfold_forloops          = run_time
    _enable_dml_lock_escalation         = false
    _bloom_filter_enabled               = true
    _update_bji_ipdml_enabled           = 0
    _optimizer_extended_cursor_sharing  = udo
    _dm_max_shared_pool_pct             = 1
    _optimizer_cost_hjsmj_multimatch    = true
    _optimizer_transitivity_retain      = true
    _px_pwg_enabled                     = true
    optimizer_secure_view_merging       = true
    _optimizer_join_elimination_enabled = true
    flashback_table_rpi                 = non_fbt
    _optimizer_cbqt_no_size_restriction = true
    _optimizer_enhanced_filter_push     = true
    _optimizer_filter_pred_pullup       = true
    _rowsrc_trace_level                 = 0
    _simple_view_merging                = true
    _optimizer_rownum_pred_based_fkr    = true
    _optimizer_better_inlist_costing    = all
    _optimizer_self_induced_cache_cost  = false
    _optimizer_min_cache_blocks         = 10
    _optimizer_or_expansion             = depth
    _optimizer_order_by_elimination_enabled = true
    _optimizer_outer_to_anti_enabled    = true
    _selfjoin_mv_duplicates             = true
    _dimension_skip_null                = true
    _force_rewrite_enable               = false
    _optimizer_star_tran_in_with_clause = true
    _optimizer_complex_pred_selectivity = true
    _optimizer_connect_by_cost_based    = true
    _gby_hash_aggregation_enabled       = true
    _globalindex_pnum_filter_enabled    = true
    _px_minus_intersect                 = true
    _fix_control_key                    = 0
    _force_slave_mapping_intra_part_loads = false
    _force_tmp_segment_loads            = false
    _query_mmvrewrite_maxpreds          = 10
    _query_mmvrewrite_maxintervals      = 5
    _query_mmvrewrite_maxinlists        = 5
    _query_mmvrewrite_maxdmaps          = 10
    _query_mmvrewrite_maxcmaps          = 20
    _query_mmvrewrite_maxregperm        = 512
    _query_mmvrewrite_maxqryinlistvals  = 500
    _disable_parallel_conventional_load = false
    _trace_virtual_columns              = false
    _replace_virtual_columns            = true
    _virtual_column_overload_allowed    = true
    _kdt_buffering                      = true
    _first_k_rows_dynamic_proration     = true
    _optimizer_sortmerge_join_inequality = true
    _optimizer_aw_stats_enabled         = true
    _bloom_pruning_enabled              = true
    result_cache_mode                   = MANUAL
    _px_ual_serial_input                = true
    _optimizer_skip_scan_guess          = false
    _enable_row_shipping                = true
    _row_shipping_threshold             = 80
    _row_shipping_explain               = false
    transaction_isolation_level         = read_commited
    _optimizer_distinct_elimination     = true
    _optimizer_multi_level_push_pred    = true
    _optimizer_group_by_placement       = true
    _optimizer_rownum_bind_default      = 10
    _enable_query_rewrite_on_remote_objs = true
    _optimizer_extended_cursor_sharing_rel = simple
    _optimizer_adaptive_cursor_sharing  = true
    _direct_path_insert_features        = 0
    _optimizer_improve_selectivity      = true
    optimizer_use_pending_statistics    = false
    _optimizer_enable_density_improvements = true
    _optimizer_aw_join_push_enabled     = true
    _optimizer_connect_by_combine_sw    = true
    _enable_pmo_ctas                    = 0
    _optimizer_native_full_outer_join   = force
    _bloom_predicate_enabled            = true
    _optimizer_enable_extended_stats    = true
    _is_lock_table_for_ddl_wait_lock    = 0
    _pivot_implementation_method        = choose
    optimizer_capture_sql_plan_baselines = false
    optimizer_use_sql_plan_baselines    = true
    _optimizer_star_trans_min_cost      = 0
    _optimizer_star_trans_min_ratio     = 0
    _with_subquery                      = OPTIMIZER
    _optimizer_fkr_index_cost_bias      = 10
    _optimizer_use_subheap              = true
    parallel_degree_policy              = manual
    parallel_degree                     = 0
    parallel_min_time_threshold         = 10
    _parallel_time_unit                 = 10
    _optimizer_or_expansion_subheap     = true
    _optimizer_free_transformation_heap = true
    _optimizer_reuse_cost_annotations   = true
    _result_cache_auto_size_threshold   = 100
    _result_cache_auto_time_threshold   = 1000
    _optimizer_nested_rollup_for_gset   = 100
    _nlj_batching_enabled               = 1
    is_recur_flags                      = 0
    optimizer_use_invisible_indexes     = false
    flashback_data_archive_internal_cursor = 0
    _optimizer_extended_stats_usage_control = 192
    _parallel_syspls_obey_force         = true
    cell_offload_processing             = true
    _rdbms_internal_fplib_enabled       = false
    _bloom_folding_enabled              = true
    _mv_generalized_oj_refresh_opt      = true
    cell_offload_compaction             = ADAPTIVE
    cell_offload_plan_display           = AUTO
    _bloom_predicate_pushdown_to_storage = true
    _bloom_vector_elements              = 0
    _bloom_pushing_max                  = 512
    parallel_degree_limit               = 65535
    parallel_force_local                = false
    parallel_max_degree                 = 20
    total_cpu_count                     = 10
    _optimizer_coalesce_subqueries      = true
    _optimizer_fast_pred_transitivity   = true
    _optimizer_fast_access_pred_analysis = true
    _optimizer_unnest_disjunctive_subq  = true
    _optimizer_unnest_corr_set_subq     = true
    _optimizer_distinct_agg_transform   = true
    _aggregation_optimization_settings  = 0
    _optimizer_connect_by_elim_dups     = true
    _optimizer_eliminate_filtering_join = true
    _connect_by_use_union_all           = true
    dst_upgrade_insert_conv             = true
    advanced_queuing_internal_cursor    = 0
    _optimizer_unnest_all_subqueries    = true
    parallel_autodop                    = 0
    parallel_ddldml                     = 0
    _parallel_cluster_cache_policy      = adaptive
    _parallel_scalability               = 50
    iot_internal_cursor                 = 0
    _optimizer_instance_count           = 0
    _optimizer_connect_by_cb_whr_only   = false
    _suppress_scn_chk_for_cqn           = nosuppress_1466
    _optimizer_join_factorization       = true
    _optimizer_use_cbqt_star_transformation = true
    _optimizer_table_expansion          = true
    _and_pruning_enabled                = true
    _deferred_constant_folding_mode     = DEFAULT
    _optimizer_distinct_placement       = true
    partition_pruning_internal_cursor   = 0
    parallel_hinted                     = none
    _sql_compatibility                  = 0
    _optimizer_use_feedback             = true
    _optimizer_try_st_before_jppd       = true
    _dml_frequency_tracking             = false
    _optimizer_interleave_jppd          = true
    kkb_drop_empty_segments             = 0
    _px_partition_scan_enabled          = true
    _px_partition_scan_threshold        = 64
    _optimizer_false_filter_pred_pullup = true
    _bloom_minmax_enabled               = true
    only_move_row                       = 0
    _optimizer_enable_table_lookup_by_nl = true
    Bug Fix Control Environment
        fix  3834770 = 1       
        fix  3746511 = enabled
        fix  4519016 = enabled
        fix  3118776 = enabled
        fix  4488689 = enabled
        fix  2194204 = disabled
        fix  2660592 = enabled
        fix  2320291 = enabled
        fix  2324795 = enabled
        fix  4308414 = enabled
        fix  3499674 = disabled
        fix  4569940 = enabled
        fix  4631959 = enabled
        fix  4519340 = enabled
        fix  4550003 = enabled
        fix  1403283 = enabled
        fix  4554846 = enabled
        fix  4602374 = enabled
        fix  4584065 = enabled
        fix  4545833 = enabled
        fix  4611850 = enabled
        fix  4663698 = enabled
        fix  4663804 = enabled
        fix  4666174 = enabled
        fix  4567767 = enabled
        fix  4556762 = 15      
        fix  4728348 = enabled
        fix  4708389 = enabled
        fix  4175830 = enabled
        fix  4752814 = enabled
        fix  4583239 = enabled
        fix  4386734 = enabled
        fix  4887636 = enabled
        fix  4483240 = enabled
        fix  4872602 = disabled
        fix  4711525 = enabled
        fix  4545802 = enabled
        fix  4605810 = enabled
        fix  4704779 = enabled
        fix  4900129 = enabled
        fix  4924149 = enabled
        fix  4663702 = enabled
        fix  4878299 = enabled
        fix  4658342 = enabled
        fix  4881533 = enabled
        fix  4676955 = enabled
        fix  4273361 = enabled
        fix  4967068 = enabled
        fix  4969880 = disabled
        fix  5005866 = enabled
        fix  5015557 = enabled
        fix  4705343 = enabled
        fix  4904838 = enabled
        fix  4716096 = enabled
        fix  4483286 = disabled
        fix  4722900 = enabled
        fix  4615392 = enabled
        fix  5096560 = enabled
        fix  5029464 = enabled
        fix  4134994 = enabled
        fix  4904890 = enabled
        fix  5104624 = enabled
        fix  5014836 = enabled
        fix  4768040 = enabled
        fix  4600710 = enabled
        fix  5129233 = enabled
        fix  4595987 = enabled
        fix  4908162 = enabled
        fix  5139520 = enabled
        fix  5084239 = enabled
        fix  5143477 = disabled
        fix  2663857 = enabled
        fix  4717546 = enabled
        fix  5240264 = disabled
        fix  5099909 = enabled
        fix  5240607 = enabled
        fix  5195882 = enabled
        fix  5220356 = enabled
        fix  5263572 = enabled
        fix  5385629 = enabled
        fix  5302124 = enabled
        fix  5391942 = enabled
        fix  5384335 = enabled
        fix  5482831 = enabled
        fix  4158812 = enabled
  • 34. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
        fix  5387148 = enabled
        fix  5383891 = enabled
        fix  5466973 = enabled
        fix  5396162 = enabled
        fix  5394888 = enabled
        fix  5395291 = enabled
        fix  5236908 = enabled
        fix  5509293 = enabled
        fix  5449488 = enabled
        fix  5567933 = enabled
        fix  5570494 = enabled
        fix  5288623 = enabled
        fix  5505995 = enabled
        fix  5505157 = enabled
        fix  5112460 = enabled
        fix  5554865 = enabled
        fix  5112260 = enabled
        fix  5112352 = enabled
        fix  5547058 = enabled
        fix  5618040 = enabled
        fix  5585313 = enabled
        fix  5547895 = enabled
        fix  5634346 = enabled
        fix  5620485 = enabled
        fix  5483301 = enabled
        fix  5657044 = enabled
        fix  5694984 = enabled
        fix  5868490 = enabled
        fix  5650477 = enabled
        fix  5611962 = enabled
        fix  4279274 = enabled
        fix  5741121 = enabled
        fix  5714944 = enabled
        fix  5391505 = enabled
        fix  5762598 = enabled
        fix  5578791 = enabled
        fix  5259048 = enabled
        fix  5882954 = enabled
        fix  2492766 = enabled
        fix  5707608 = enabled
        fix  5891471 = enabled
        fix  5884780 = enabled
        fix  5680702 = enabled
        fix  5371452 = enabled
        fix  5838613 = enabled
        fix  5949981 = enabled
        fix  5624216 = enabled
        fix  5741044 = enabled
        fix  5976822 = enabled
        fix  6006457 = enabled
        fix  5872956 = enabled
        fix  5923644 = enabled
        fix  5943234 = enabled
        fix  5844495 = enabled
        fix  4168080 = enabled
        fix  6020579 = enabled
        fix  5842686 = disabled
        fix  5996801 = enabled
        fix  5593639 = enabled
        fix  6133948 = enabled
        fix  3151991 = enabled
        fix  6146906 = enabled
        fix  6239909 = enabled
        fix  6267621 = enabled
        fix  5909305 = enabled
        fix  6279918 = enabled
        fix  6141818 = enabled
        fix  6151963 = enabled
        fix  6251917 = enabled
        fix  6282093 = enabled
        fix  6119510 = enabled
        fix  6119382 = enabled
        fix  3801750 = enabled
        fix  5705630 = disabled
        fix  5944076 = enabled
        fix  5406763 = enabled
        fix  6070954 = enabled
        fix  6282944 = enabled
        fix  6138746 = enabled
        fix  6082745 = enabled
        fix  3426050 = enabled
        fix   599680 = enabled
        fix  6062266 = enabled
        fix  6087237 = enabled
        fix  6122894 = enabled
        fix  6377505 = enabled
        fix  5893768 = enabled
        fix  6163564 = enabled
        fix  6073325 = enabled
        fix  6188881 = enabled
        fix  6007259 = enabled
        fix  6239971 = enabled
        fix  5284200 = disabled
        fix  6042205 = enabled
        fix  6051211 = enabled
        fix  6434668 = enabled
        fix  6438752 = enabled
        fix  5936366 = enabled
        fix  6439032 = enabled
        fix  6438892 = enabled
        fix  6006300 = enabled
        fix  5947231 = enabled
        fix  5416118 = 1       
        fix  6365442 = 1       
        fix  6239039 = enabled
        fix  6502845 = enabled
        fix  6913094 = enabled
        fix  6029469 = enabled
        fix  5919513 = enabled
        fix  6057611 = enabled
        fix  6469667 = enabled
        fix  6608941 = disabled
        fix  6368066 = enabled
        fix  6329318 = enabled
        fix  6656356 = enabled
        fix  4507997 = enabled
        fix  6671155 = enabled
        fix  6694548 = enabled
        fix  6688200 = enabled
        fix  6612471 = enabled
        fix  6708183 = disabled
        fix  6326934 = enabled
        fix  6520717 = disabled
        fix  6714199 = enabled
        fix  6681545 = enabled
        fix  6748058 = enabled
        fix  6167716 = enabled
        fix  6674254 = enabled
        fix  6468287 = enabled
        fix  6503543 = enabled
        fix  6808773 = disabled
        fix  6766962 = enabled
        fix  6120483 = enabled
        fix  6670551 = enabled
        fix  6771838 = enabled
        fix  6626018 = disabled
        fix  6530596 = enabled
        fix  6778642 = enabled
        fix  6699059 = enabled
        fix  6376551 = enabled
        fix  6429113 = enabled
        fix  6782437 = enabled
        fix  6776808 = enabled
        fix  6765823 = enabled
        fix  6768660 = enabled
        fix  6782665 = enabled
        fix  6610822 = enabled
        fix  6514189 = enabled
        fix  6818410 = enabled
        fix  6827696 = enabled
        fix  6773613 = enabled
        fix  5902962 = enabled
        fix  6956212 = enabled
        fix  3056297 = enabled
        fix  6440977 = disabled
        fix  6972291 = disabled
        fix  6904146 = enabled
        fix  6221403 = enabled
        fix  5475051 = enabled
        fix  6845871 = enabled
        fix  5468809 = enabled
        fix  6917633 = enabled
        fix  4444536 = disabled
        fix  6955210 = enabled
        fix  6994194 = enabled
        fix  6399597 = disabled
        fix  6951776 = enabled
        fix  5648287 = 3       
        fix  6987082 = disabled
        fix  7132036 = enabled
        fix  6980350 = enabled
        fix  5199213 = enabled
        fix  7138405 = enabled
        fix  7148689 = enabled
        fix  6820988 = enabled
        fix  7032684 = enabled
        fix  6617866 = enabled
        fix  7155968 = enabled
        fix  7127980 = enabled
        fix  6982954 = enabled
        fix  7241819 = enabled
        fix  6897034 = enabled
        fix  7236148 = enabled
        fix  7298570 = enabled
        fix  7249095 = enabled
        fix  7314499 = enabled
        fix  7324224 = enabled
        fix  7289023 = enabled
        fix  7237571 = enabled
        fix  7116357 = enabled
        fix  7345484 = enabled
        fix  7375179 = enabled
        fix  6430500 = disabled
        fix  5897486 = enabled
        fix  6774209 = enabled
        fix  7306637 = enabled
        fix  6451322 = enabled
        fix  7208131 = enabled
        fix  7388652 = enabled
        fix  7127530 = enabled
        fix  6751206 = enabled
        fix  6669103 = enabled
        fix  7430474 = enabled
        fix  6990305 = enabled
        fix  7043307 = enabled
        fix  3120429 = enabled
        fix  7452823 = disabled
        fix  6838105 = enabled
        fix  6769711 = enabled
        fix  7170213 = enabled
        fix  6528872 = enabled
        fix  7295298 = enabled
        fix  5922070 = enabled
        fix  7259468 = enabled
        fix  6418552 = enabled
        fix  4619997 = enabled
        fix  7524366 = enabled
        fix  6942476 = enabled
        fix  6418771 = enabled
        fix  7375077 = enabled
        fix  5400639 = enabled
        fix  4570921 = enabled
        fix  7426911 = enabled
        fix  5099019 = disabled
        fix  7528216 = enabled
        fix  7521266 = enabled
        fix  7385140 = enabled
        fix  7576516 = enabled
        fix  7573526 = enabled
        fix  7576476 = enabled
        fix  7165898 = enabled
        fix  7263214 = enabled
        fix  3320140 = enabled
        fix  7555510 = enabled
        fix  7613118 = enabled
        fix  7597059 = enabled
        fix  7558911 = enabled
        fix  5520732 = enabled
        fix  7679490 = disabled
        fix  7449971 = enabled
        fix  3628118 = enabled
        fix  4370840 = enabled
        fix  7281191 = enabled
        fix  7519687 = enabled
        fix  5029592 = 3       
        fix  6012093 = 1       
        fix  6053861 = disabled
        fix  6941515 = disabled
        fix  7696414 = enabled
        fix  7272039 = enabled
        fix  7834811 = enabled
        fix  7640597 = enabled
        fix  7341616 = enabled
        fix  7168184 = enabled
        fix   399198 = enabled
        fix  7831070 = enabled
        fix  7676897 = disabled
        fix  7414637 = enabled
        fix  7585456 = enabled
        fix  8202421 = enabled
        fix  7658097 = disabled
        fix  8251486 = enabled
        fix  7132684 = enabled
        fix  7512227 = enabled
        fix  6972987 = enabled
        fix  7199035 = enabled
        fix  8243446 = enabled
        fix  7650462 = enabled
        fix  6720701 = enabled
        fix  7592673 = enabled
        fix  7718694 = enabled
        fix  7534027 = enabled
        fix  7708267 = enabled
        fix  5716785 = enabled
        fix  7356191 = enabled
        fix  7679161 = enabled
        fix  7597159 = enabled
        fix  7499258 = enabled
        fix  8328363 = enabled
        fix  7452863 = enabled
        fix  8284930 = enabled
        fix  7298626 = enabled
        fix  7657126 = enabled
        fix  8371884 = enabled
        fix  8318020 = enabled
        fix  8255423 = enabled
        fix  7135745 = enabled
        fix  8356253 = enabled
        fix  7534257 = enabled
        fix  8323407 = enabled
        fix  7539815 = enabled
        fix  8289316 = enabled
        fix  8447850 = enabled
        fix  7675944 = enabled
        fix  8355120 = enabled
        fix  7176746 = enabled
        fix  8442891 = enabled
        fix  8373261 = enabled
        fix  7679164 = enabled
        fix  7670533 = enabled
        fix  8408665 = enabled
        fix  8491399 = enabled
        fix  8348392 = enabled
        fix  8348585 = enabled
        fix  8508056 = enabled
        fix  8335178 = enabled
        fix  8515269 = enabled
        fix  8247017 = enabled
        fix  7325597 = enabled
        fix  8531490 = enabled
        fix  6163600 = enabled
        fix  8589278 = disabled
        fix  8557992 = enabled
        fix  7556098 = enabled
        fix  8580883 = enabled
        fix  5892599 = disabled
        fix  8609714 = enabled
        fix  8619631 = disabled
        fix  8672915 = enabled
        fix  8514561 = enabled
        fix  8213977 = enabled
        fix  8560951 = disabled
        fix  8578587 = enabled
        fix  8287870 = enabled
        fix  8467123 = enabled
        fix  8602185 = enabled
        fix  8519457 = enabled
        fix  3335182 = enabled
        fix  8602840 = enabled
        fix  8725296 = enabled
        fix  8628970 = enabled
        fix  6754080 = enabled
        fix  8767442 = enabled
        fix  8760135 = enabled
        fix  8644935 = enabled
        fix  8352378 = enabled
        fix  8685327 = enabled
        fix  8763472 = enabled
        fix  8773324 = enabled
        fix  8813674 = enabled
        fix  8532236 = enabled
        fix  8629716 = enabled
        fix  7277732 = enabled
        fix  8692170 = enabled
        fix  8900973 = enabled
        fix  8919133 = enabled
        fix  8927050 = enabled
        fix  8551880 = enabled
        fix  8901237 = enabled
        fix  8812372 = enabled
        fix  6236862 = enabled
        fix  8528517 = enabled
        fix  7215982 = enabled
        fix  8214022 = enabled
        fix  8595392 = enabled
        fix  8890233 = enabled
        fix  8999317 = enabled
        fix  9004800 = enabled
        fix  8986163 = enabled
        fix  8855396 = enabled
        fix  8800514 = 20      
        fix  9007859 = enabled
        fix  8198783 = enabled
        fix  9053879 = enabled
        fix  6086930 = enabled
        fix  7641601 = enabled
        fix  9052506 = enabled
        fix  9103775 = enabled
        fix  9047975 = enabled
        fix  8893626 = enabled
        fix  9111170 = enabled
        fix  8971829 = enabled
        fix  7628358 = enabled
        fix  9125151 = enabled
        fix  9039715 = enabled
        fix  9106224 = enabled
        fix  9185228 = enabled
        fix  9206747 = enabled
        fix  9088510 = enabled
        fix  9143856 = enabled
        fix  8833381 = enabled
        fix  8949971 = enabled
        fix  8951812 = enabled
        fix  9148171 = enabled
        fix  8706652 = enabled
        fix  9245114 = enabled
        fix  8802198 = enabled
        fix  9011016 = enabled
        fix  9265681 = enabled
        fix  7284269 = enabled
        fix  9272549 = enabled
        fix  8917507 = 7       
        fix  8531463 = enabled
        fix  9263333 = enabled
        fix  8675087 = enabled
        fix  8571403 = enabled
        fix  8896955 = enabled
        fix  9041934 = enabled
        fix  9344709 = enabled
        fix  9024933 = enabled
        fix  9033718 = enabled
        fix  9240455 = enabled
        fix  9081848 = enabled
        fix  5982893 = enabled
        fix  9287401 = enabled
        fix  8590021 = enabled
        fix  9340120 = enabled
        fix  9355794 = enabled
        fix  9356656 = enabled
        fix  9385634 = enabled
        fix  9069046 = enabled
        fix  9239337 = enabled
        fix  9300228 = enabled
        fix  9298010 = enabled
        fix  9384170 = enabled
        fix  9301862 = disabled
        fix  9407929 = enabled
        fix  8836806 = enabled
        fix  9344055 = enabled
        fix  9274675 = enabled
        fix  9203723 = enabled
        fix  9443476 = enabled
        fix  9195582 = enabled
        fix  8226666 = enabled
        fix  9433490 = enabled
        fix  9065494 = enabled
        fix  9303766 = enabled
        fix  9437283 = enabled
        fix  9116214 = enabled
        fix  9456688 = enabled
        fix  9456746 = disabled
        fix  9342979 = enabled
        fix  9465425 = enabled
        fix  9092442 = enabled
        fix  4926618 = enabled
        fix  8792846 = enabled
        fix  9474259 = enabled
        fix  9495669 = disabled
        fix  6472966 = enabled
        fix  6408301 = enabled
        fix  9380298 = disabled
        fix  8500130 = enabled
        fix  9584723 = enabled
        fix  9270951 = enabled
        fix  9508254 = enabled
        fix  9593680 = enabled
        fix  9196440 = disabled
        fix  9309281 = enabled
        fix  8693158 = enabled
        fix  9381638 = enabled
        fix  9383967 = enabled
        fix  7711900 = enabled
        fix  9218587 = enabled
        fix  9728438 = enabled
        fix  9038395 = enabled
        fix  9577300 = enabled
        fix  9171113 = enabled
        fix  8973745 = enabled
        fix  9732434 = enabled
        fix  8937971 = disabled
        fix  9102474 = enabled
        fix  9243499 = enabled
        fix  9912503 = enabled
        fix  9153459 = enabled
        fix  9762592 = 3       
        fix  9785632 = enabled
      ***************************************
      PARAMETERS IN OPT_PARAM HINT
      ****************************
  • 35. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    ***************************************
    Column Usage Monitoring is ON: tracking level = 1
    ***************************************
    Considering Query Transformations on query block SEL$1 (#0)
    **************************
    Query transformations (QT)
    **************************
    JF: Checking validity of join factorization for query block SEL$1 (#0)
    JF: Bypassed: not a UNION or UNION-ALL query block.
    ST: not valid since star transformation parameter is FALSE
    TE: Checking validity of table expansion for query block SEL$1 (#0)
    TE: Bypassed: No relevant table found.
    CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
    CBQT: Validity checks failed for ct3jkvbv19af7.
    CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
    *************************
    Common Subexpression elimination (CSE)
    *************************
    CSE:     CSE not performed on query block SEL$1 (#0).
    OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
    ***************************
    Order-by elimination (OBYE)
    ***************************
    OBYE:     OBYE bypassed: no order by to eliminate.
    CVM: Considering view merge in query block SEL$1 (#0)
    query block SEL$1 (#0) unchanged
    Considering Query Transformations on query block SEL$1 (#0)
    **************************
    Query transformations (QT)
    **************************
    JF: Checking validity of join factorization for query block SEL$1 (#0)
    JF: Bypassed: not a UNION or UNION-ALL query block.
    ST: not valid since star transformation parameter is FALSE
    TE: Checking validity of table expansion for query block SEL$1 (#0)
    TE: Bypassed: No relevant table found.
    CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
    CBQT: Validity checks failed for ct3jkvbv19af7.
    CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
    *************************
    Common Subexpression elimination (CSE)
    *************************
    CSE:     CSE not performed on query block SEL$1 (#0).
    SU: Considering subquery unnesting in query block SEL$1 (#0)
    ********************
    Subquery Unnest (SU)
    ********************
    SJC: Considering set-join conversion in query block SEL$1 (#0)
    *************************
    Set-Join Conversion (SJC)
    *************************
    SJC: not performed
    PM: Considering predicate move-around in query block SEL$1 (#0)
    **************************
    Predicate Move-Around (PM)
    **************************
    PM:     PM bypassed: Outer query contains no views.
    PM:     PM bypassed: Outer query contains no views.
    query block SEL$1 (#0) unchanged
    FPD: Considering simple filter push in query block SEL$1 (#0)
    "HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    try to generate transitive predicate from check constraints for query block SEL$1 (#0)
    finally: "HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(' 2012-
    FPD:   transitive predicates are generated in query block SEL$1 (#0)
    "HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(' 2012-
    apadrv-start sqlid=14739774546443020743
      :
        call(in-use=6936, alloc=16344), compile(in-use=110240, alloc=112920), execution(in-use=2952, alloc=4032)
    *******************************************
    Peeked values of the binds in SQL statement
    *******************************************
    Final query after transformations:******* UNPARSED QUERY IS *******
    SELECT "HAREKET_TABLE"."SIRKET_KOD" "SIRKET_KOD","HAREKET_TABLE"."SUBE_KOD" "SUBE_KOD","HAREKET_TABLE"."KSUBE_KOD" "KSUBE_KOD","HAREKET_TABLE"."HESAP_NO" "HESAP_NO","HAREKET_TABLE"."MUST_KOD" "MUST_KOD","HAREKET_TABLE"."EKNO" "EKNO","HAREKET_TABLE"."BA" "BA","HAREKET_TABLE"."KASA" "KASA","HAREKET_TABLE"."DOVIZ" "DOVIZ","HAREKET_TABLE"."TUTAR" "TUTAR","HAREKET_TABLE"."DTUTAR" "DTUTAR","HAREKET_TABLE"."REFERANS" "REFERANS","HAREKET_TABLE"."ACIKLAMA" "ACIKLAMA","HAREKET_TABLE"."ISLEM_TAR" "ISLEM_TAR","HAREKET_TABLE"."ISLEM_REF" "ISLEM_REF","HAREKET_TABLE"."OP_ID" "OP_ID","HAREKET_TABLE"."SYS_TARIH" "SYS_TARIH","HAREKET_TABLE"."SAAT" "SAAT","HAREKET_TABLE"."KUR" "KUR","HAREKET_TABLE"."ANL_KOD1" "ANL_KOD1","HAREKET_TABLE"."ANL_KOD2" "ANL_KOD2","HAREKET_TABLE"."VADE" "VADE","HAREKET_TABLE"."SIG_NO" "SIG_NO","HAREKET_TABLE"."SIG_EKNO" "SIG_EKNO","HAREKET_TABLE"."POL_NO" "POL_NO","HAREKET_TABLE"."ZEYL_SIRA_NO" "ZEYL_SIRA_NO","HAREKET_TABLE"."SIRA_NO" "SIRA_NO","HAREKET_TABLE"."ESKI_HESAP_NO" "ESKI_HESAP_NO","HAREKET_TABLE"."YANSIMA_TUR" "YANSIMA_TUR","HAREKET_TABLE"."YANSIMA_ABRN_BRN" "YANSIMA_ABRN_BRN","HAREKET_TABLE"."TRETE_POL_NO" "TRETE_POL_NO","HAREKET_TABLE"."TRETE_ZEYL_SIRA_NO" "TRETE_ZEYL_SIRA_NO","HAREKET_TABLE"."TRETE_DOSYA_NO" "TRETE_DOSYA_NO","HAREKET_TABLE"."SEGMENT" "SEGMENT","HAREKET_TABLE"."HASAR_YILI" "HASAR_YILI","HAREKET_TABLE"."OTIP" "OTIP","HAREKET_TABLE"."TRADING_PARTNER" "TRADING_PARTNER","HAREKET_TABLE"."CRK_MUST_KOD" "CRK_MUST_KOD","HAREKET_TABLE"."BRANS_KOD" "BRANS_KOD","HAREKET_TABLE"."REAS_TUR" "REAS_TUR","HAREKET_TABLE"."ISLEM_REF_TIPI" "ISLEM_REF_TIPI","HAREKET_TABLE"."KOMISYON_ISLEMI_MI" "KOMISYON_ISLEMI_MI","HAREKET_TABLE"."DOVIZLI_TUTAR" "DOVIZLI_TUTAR","HAREKET_TABLE"."ISLEM_DOVIZ" "ISLEM_DOVIZ","HAREKET_TABLE"."TUR" "TUR","HAREKET_TABLE"."TUR_NO" "TUR_NO","HAREKET_TABLE"."MUSTERI_ACENTA" "MUSTERI_ACENTA","HAREKET_TABLE"."EK_KOMISYON" "EK_KOMISYON","HAREKET_TABLE"."KUR_FARKI" "KUR_FARKI","HAREKET_TABLE"."KOMISYON_FERAGAT" "KOMISYON_FERAGAT","HAREKET_TABLE"."ODEME_ID" "ODEME_ID" FROM "SG"."HAREKET_TABLE" "HAREKET_TABLE" WHERE "HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkoqbc: optimizing query block SEL$1 (#0)
            
            :
        call(in-use=7080, alloc=16344), compile(in-use=111160, alloc=112920), execution(in-use=2952, alloc=4032)
    kkoqbc-subheap (create addr=0x110c7e950)
    ****************
    QUERY BLOCK TEXT
    ****************
    select *
      from   hareket_table
      where islem_tar between to_date('01/05/2012', 'dd/mm/yyyy') and to_date('14/07/2012', 'dd/mm/yyyy')
    ---------------------
    QUERY BLOCK SIGNATURE
    ---------------------
    signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
      fro(0): flg=0 objn=571873 hint_alias="HAREKET_TABLE"@"SEL$1"
    -----------------------------
    SYSTEM STATISTICS INFORMATION
    -----------------------------
      Using NOWORKLOAD Stats
      CPUSPEEDNW: 1519 millions instructions/sec (default is 100)
      IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
      IOSEEKTIM: 10 milliseconds (default is 10)
      MBRC: -1 blocks (default is 16)
    ***************************************
    BASE STATISTICAL INFORMATION
    ***********************
    Table Stats::
      Table: HAREKET_TABLE  Alias: HAREKET_TABLE  (Using composite stats)
      (making adjustments for partition skews)
      ORIGINAL VALUES::    #Rows: 117493841  #Blks:  1250662  AvgRowLen:  168.00  ChainCnt:  0.00
      PARTITIONS::
      PRUNED: 3
      ANALYZED: 3  UNANALYZED: 0
        #Rows: 117493841  #Blks:  122985  AvgRowLen:  168.00  ChainCnt:  0.00
    Index Stats::
      Index: I_NEW_HAREKET_1  Col#: 14 15 1
        USING COMPOSITE STATS
        LVLS: 2  #LB: 271302  #DK: 4658087  LB/K: 1.00  DB/K: 1.00  CLUF: 3486185.00
      Index: I_NEW_HAREKET_2  Col#: 4 14 15 1
        USING COMPOSITE STATS
        LVLS: 2  #LB: 387006  #DK: 941116  LB/K: 1.00  DB/K: 1.00  CLUF: 10849749.00
      Index: NEW_HAREKET6_2_NDX  Col#: 1 5 6 14 52
        LVLS: 3  #LB: 284932  #DK: 3741239  LB/K: 1.00  DB/K: 3.00  CLUF: 13328232.00
      Index: NEW_HAREKET6_NDX  Col#: 5 1 6 14 52
        USING COMPOSITE STATS
        PARTITION [0]    LVLS: 2  #LB: 628  #DK: 44838  LB/K: 1.00  DB/K: 2.00  CLUF: 91553.00
        PARTITION [1]    LVLS: 2  #LB: 4493  #DK: 674  LB/K: 6.00  DB/K: 223.00  CLUF: 150870.00
        PARTITION [2]    LVLS: 1  #LB: 151  #DK: 7931  LB/K: 1.00  DB/K: 1.00  CLUF: 13886.00
        PARTITION [3]    LVLS: 2  #LB: 25958  #DK: 739438  LB/K: 1.00  DB/K: 2.00  CLUF: 2202874.00
        PARTITION [4]    LVLS: 2  #LB: 18121  #DK: 547851  LB/K: 1.00  DB/K: 3.00  CLUF: 1683667.00
        PARTITION [5]    LVLS: 2  #LB: 12242  #DK: 410138  LB/K: 1.00  DB/K: 2.00  CLUF: 1229634.00
        PARTITION [6]    LVLS: 2  #LB: 3594  #DK: 146293  LB/K: 1.00  DB/K: 2.00  CLUF: 379740.00
        PARTITION [7]    LVLS: 1  #LB: 103  #DK: 4242  LB/K: 1.00  DB/K: 2.00  CLUF: 9793.00
        PARTITION [8]    LVLS: 2  #LB: 3183  #DK: 127935  LB/K: 1.00  DB/K: 3.00  CLUF: 398048.00
        PARTITION [9]    LVLS: 2  #LB: 3984  #DK: 84256  LB/K: 1.00  DB/K: 3.00  CLUF: 269671.00
        PARTITION [10]    LVLS: 2  #LB: 8955  #DK: 232806  LB/K: 1.00  DB/K: 2.00  CLUF: 526087.00
        PARTITION [11]    LVLS: 2  #LB: 7789  #DK: 196945  LB/K: 1.00  DB/K: 2.00  CLUF: 485194.00
        PARTITION [12]    LVLS: 2  #LB: 8229  #DK: 286838  LB/K: 1.00  DB/K: 2.00  CLUF: 783651.00
        PARTITION [13]    LVLS: 2  #LB: 11872  #DK: 483523  LB/K: 1.00  DB/K: 2.00  CLUF: 1182413.00
        PARTITION [14]    LVLS: 2  #LB: 21922  #DK: 59648  LB/K: 1.00  DB/K: 15.00  CLUF: 904999.00
        PARTITION [15]    LVLS: 2  #LB: 3924  #DK: 64116  LB/K: 1.00  DB/K: 6.00  CLUF: 441458.00
        PARTITION [16]    LVLS: 2  #LB: 4297  #DK: 3922  LB/K: 1.00  DB/K: 53.00  CLUF: 210983.00
        PARTITION [17]    LVLS: 2  #LB: 1117  #DK: 4489  LB/K: 1.00  DB/K: 30.00  CLUF: 137641.00
        PARTITION [18]    LVLS: 2  #LB: 3859  #DK: 6810  LB/K: 1.00  DB/K: 32.00  CLUF: 223112.00
        PARTITION [19]    LVLS: 1  #LB: 345  #DK: 6350  LB/K: 1.00  DB/K: 2.00  CLUF: 17227.00
        PARTITION [20]    LVLS: 2  #LB: 139448  #DK: 3747  LB/K: 37.00  DB/K: 526.00  CLUF: 1973261.00
        (NOT ANALYZED)
        LVLS: 2  #LB: 277291  #DK: 3336488  LB/K: 1.00  DB/K: 3.00  CLUF: 12931894.00
      Index: NEW_HAREKET81_NDX  Col#: 1 4 2 14
        USING COMPOSITE STATS
        LVLS: 2  #LB: 311612  #DK: 1040194  LB/K: 1.00  DB/K: 9.00  CLUF: 9458746.00
      Index: NEW_HAREKET_MAHEKOL_NDX  Col#: 1 6 14 15
        USING COMPOSITE STATS
        LVLS: 2  #LB: 284353  #DK: 10465428  LB/K: 1.00  DB/K: 1.00  CLUF: 8263654.00
      Index: NEW_HAREKET_NDX  Col#: 14 15 2 1
        USING COMPOSITE STATS
        LVLS: 2  #LB: 279411  #DK: 4685413  LB/K: 1.00  DB/K: 1.00  CLUF: 3604701.00
      Index: NEW_HAREKET_POLICE_NDX  Col#: 25 1 26 15
        USING COMPOSITE STATS
        PARTITION [0]    LVLS: 2  #LB: 3936  #DK: 1156209  LB/K: 1.00  DB/K: 1.00  CLUF: 1473695.00
        PARTITION [1]    LVLS: 2  #LB: 2236  #DK: 538307  LB/K: 1.00  DB/K: 1.00  CLUF: 696559.00
        PARTITION [2]    LVLS: 2  #LB: 2908  #DK: 708073  LB/K: 1.00  DB/K: 1.00  CLUF: 908220.00
        PARTITION [3]    LVLS: 2  #LB: 3723  #DK: 1084099  LB/K: 1.00  DB/K: 1.00  CLUF: 1313585.00
        PARTITION [4]    LVLS: 2  #LB: 4620  #DK: 1350710  LB/K: 1.00  DB/K: 1.00  CLUF: 1598862.00
        PARTITION [5]    LVLS: 2  #LB: 5105  #DK: 1442233  LB/K: 1.00  DB/K: 1.00  CLUF: 1613109.00
        PARTITION [6]    LVLS: 2  #LB: 7049  #DK: 1605136  LB/K: 1.00  DB/K: 1.00  CLUF: 1941559.00
        PARTITION [7]    LVLS: 2  #LB: 4269  #DK: 1117439  LB/K: 1.00  DB/K: 1.00  CLUF: 1296980.00
        PARTITION [8]    LVLS: 2  #LB: 6126  #DK: 1615305  LB/K: 1.00  DB/K: 1.00  CLUF: 1914781.00
        PARTITION [9]    LVLS: 2  #LB: 5817  #DK: 1598066  LB/K: 1.00  DB/K: 1.00  CLUF: 1837018.00
        PARTITION [10]    LVLS: 2  #LB: 5287  #DK: 1532602  LB/K: 1.00  DB/K: 1.00  CLUF: 1652259.00
        PARTITION [11]    LVLS: 2  #LB: 7118  #DK: 1899907  LB/K: 1.00  DB/K: 1.00  CLUF: 2137516.00
        PARTITION [12]    LVLS: 2  #LB: 7313  #DK: 1820570  LB/K: 1.00  DB/K: 1.00  CLUF: 2033708.00
        PARTITION [13]    LVLS: 2  #LB: 4182  #DK: 854544  LB/K: 1.00  DB/K: 1.00  CLUF: 920105.00
        PARTITION [14]    LVLS: 1  #LB: 144  #DK: 14156  LB/K: 1.00  DB/K: 1.00  CLUF: 17596.00
        PARTITION [15]    LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
        PARTITION [16]    LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
        PARTITION [17]    LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
        PARTITION [18]    LVLS: 1  #LB: 126  #DK: 45404  LB/K: 1.00  DB/K: 1.00  CLUF: 46284.00
        PARTITION [19]    LVLS: 1  #LB: 29  #DK: 8627  LB/K: 1.00  DB/K: 1.00  CLUF: 11643.00
        PARTITION [20]    LVLS: 2  #LB: 187777  #DK: 906  LB/K: 207.00  DB/K: 6117.00  CLUF: 5542441.00
        (NOT ANALYZED)
        LVLS: 2  #LB: 262395  #DK: 19256937  LB/K: 1.00  DB/K: 1.00  CLUF: 27832841.00
      Index: NEW_TARIK_NDX  Col#: 1 3 4 14
        USING COMPOSITE STATS
        LVLS: 2  #LB: 292870  #DK: 945050  LB/K: 1.00  DB/K: 10.00  CLUF: 10019333.00
  • 36. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    Access path analysis for HAREKET_TABLE
    ***************************************
    SINGLE TABLE ACCESS PATH 
      Single Table Cardinality Estimation for HAREKET_TABLE[HAREKET_TABLE] 
    
    *** 2012-08-16 16:25:10.003
    ** Performing dynamic sampling initial checks. **
      Column (#14): 
        NewDensity:0.000166, OldDensity:0.001179 BktCnt:254, PopBktCnt:89, PopValCnt:33, NDV:3950
      Column (#14): ISLEM_TAR(
        AvgLen: 8 NDV: 3950 Nulls: 2 Density: 0.000166 Min: 2450814 Max: 2456136
        Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 199
    ** Dynamic sampling initial checks returning FALSE.
      Table: HAREKET_TABLE  Alias: HAREKET_TABLE
        Card: Original: 117493841.000000  Rounded: 8802126  Computed: 8802125.62  Non Adjusted: 8802125.62
      Access Path: TableScan
        Cost:  41189.63  Resp: 2288.31  Degree: 0
          Cost_io: 40631.00  Cost_cpu: 11879362714
          Resp_io: 2257.28  Resp_cpu: 659964595
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR">=TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    kkofmx: index filter:"HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
      Access Path: index (RangeScan)
        Index: I_NEW_HAREKET_1
        resc_io: 281497.00  resc_cpu: 14878479941
        ix_sel: 0.074916  ix_sel_with_filters: 0.074916 
        Cost: 84659.00  Resp: 84659.00  Degree: 1
      Access Path: index (skip-scan)
        SS sel: 0.074916  ANDV (#skips): 88184.000000
        SS io: 26455.200000 vs. table scan io: 40631.000000
        Skip Scan chosen
      Access Path: index (SkipScan)
        Index: I_NEW_HAREKET_2
        resc_io: 901006.00  resc_cpu: 20909375887
        ix_sel: 0.074916  ix_sel_with_filters: 0.074916 
        Cost: 270596.78  Resp: 270596.78  Degree: 1
      Access Path: index (FullScan)
        Index: NEW_HAREKET6_2_NDX
        resc_io: 1283428.00  resc_cpu: 46848050593
        ix_sel: 1.000000  ix_sel_with_filters: 0.074916 
     ***** Logdef predicate Adjustment ****** 
     Final IO cst 0.00 , CPU cst 50.00
     ***** End Logdef Adjustment ****** 
     ***** Logdef predicate Adjustment ****** 
     Final IO cst 0.00 , CPU cst 54.42
     ***** End Logdef Adjustment ****** 
        Cost: 385781.44  Resp: 385781.44  Degree: 1
      Access Path: index (FullScan)
        Index: NEW_HAREKET6_NDX
        resc_io: 1246095.00  resc_cpu: 45372249204
        ix_sel: 1.000000  ix_sel_with_filters: 0.074916 
     ***** Logdef predicate Adjustment ****** 
     Final IO cst 0.00 , CPU cst 50.00
     ***** End Logdef Adjustment ****** 
     ***** Logdef predicate Adjustment ****** 
     Final IO cst 0.00 , CPU cst 54.42
     ***** End Logdef Adjustment ****** 
        Cost: 374558.78  Resp: 374558.78  Degree: 1
      Access Path: index (skip-scan)
        SS sel: 0.075667  ANDV (#skips): 78708.393565
        SS io: 23612.518070 vs. table scan io: 40631.000000
        Skip Scan chosen
      Access Path: index (SkipScan)
        Index: NEW_HAREKET81_NDX
        resc_io: 794430.39  resc_cpu: 19622907393
        ix_sel: 0.075667  ix_sel_with_filters: 0.075667 
        Cost: 238605.95  Resp: 238605.95  Degree: 1
      Access Path: index (skip-scan)
        SS sel: 0.075104  ANDV (#skips): 785998.362529
        SS io: 85305.900000 vs. table scan io: 40631.000000
        Skip Scan rejected
      Access Path: index (FullScan)
        Index: NEW_HAREKET_MAHEKOL_NDX
        resc_io: 904995.00  resc_cpu: 42379443786
        ix_sel: 1.000000  ix_sel_with_filters: 0.075104 
     ***** Logdef predicate Adjustment ****** 
     Final IO cst 0.00 , CPU cst 50.00
     ***** End Logdef Adjustment ****** 
     ***** Logdef predicate Adjustment ****** 
     Final IO cst 0.00 , CPU cst 54.42
     ***** End Logdef Adjustment ****** 
        Cost: 272186.33  Resp: 272186.33  Degree: 1
      Access Path: index (RangeScan)
        Index: NEW_HAREKET_NDX
        resc_io: 297726.00  resc_cpu: 14783578791
        ix_sel: 0.076651  ix_sel_with_filters: 0.076651 
        Cost: 89526.36  Resp: 89526.36  Degree: 1
      Access Path: index (skip-scan)
        SS sel: 0.080704  ANDV (#skips): 76269.607364
        SS io: 22880.882209 vs. table scan io: 40631.000000
        Skip Scan chosen
      Access Path: index (SkipScan)
        Index: NEW_TARIK_NDX
        resc_io: 884879.61  resc_cpu: 20504143229
        ix_sel: 0.080704  ix_sel_with_filters: 0.080704 
        Cost: 265753.14  Resp: 265753.14  Degree: 1
      ****** trying bitmap/domain indexes ******
      ****** finished trying bitmap/domain indexes ******
      Best:: AccessPath: TableScan
             Cost: 2288.31  Degree: 20  Resp: 2288.31  Card: 8802125.62  Bytes: 0
  • 37. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    ***************************************
    OPTIMIZER STATISTICS AND COMPUTATIONS
    ***************************************
    GENERAL PLANS
    ***************************************
    Considering cardinality-based initial join order.
    Permutations for Starting Table :0
    Join order[1]:  HAREKET_TABLE[HAREKET_TABLE]#0
    ***********************
    Best so far:  Table#: 0  cost: 2288.3125  card: 8802125.6214  bytes: 1478757168
    ***********************
    (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
    *********************************
    Number of join permutations tried: 1
    *********************************
    Enumerating distribution method (advanced)
    Trying or-Expansion on query block SEL$1 (#0)
    Transfer Optimizer annotations for query block SEL$1 (#0)
    id=0 frofand predicate="HAREKET_TABLE"."ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    Final cost for query block SEL$1 (#0) - All Rows Plan:
      Best join order: 1
      Cost: 2288.3125  Degree: 20  Card: 8802126.0000  Bytes: 1478757168
      Resc: 41189.6251  Resc_io: 40631.0000  Resc_cpu: 11879362714
      Resp: 2288.3125  Resp_io: 2257.2778  Resc_cpu: 659964595
    kkoqbc-subheap (delete addr=0x110c7e950, in-use=21512, alloc=32840)
    kkoqbc-end:
            :
        call(in-use=101544, alloc=147496), compile(in-use=113200, alloc=117104), execution(in-use=3424, alloc=4032)
    kkoqbc: finish optimizing query block SEL$1 (#0)
    apadrv-end
              :
        call(in-use=101544, alloc=147496), compile(in-use=114112, alloc=117104), execution(in-use=3424, alloc=4032)
    kkeCostToTime: using io calibrate stats 
     maxmbps=290(MB/s) maxpmbps=182(MB/s) 
     block_size=16384 mb_io_count=1 mb_io_size=16384 (bytes) 
     tot_io_size=36(MB) time=196(ms)
    kkeCostToTime: using io calibrate stats 
     maxmbps=290(MB/s) maxpmbps=182(MB/s) 
     block_size=16384 mb_io_count=1 mb_io_size=16384 (bytes) 
     tot_io_size=36(MB) time=196(ms)
    kkeCostToTime: using io calibrate stats 
     maxmbps=290(MB/s) maxpmbps=182(MB/s) 
     block_size=16384 mb_io_count=1 mb_io_size=16384 (bytes) 
     tot_io_size=36(MB) time=196(ms)
    kkeCostToTime: using io calibrate stats 
     maxmbps=290(MB/s) maxpmbps=182(MB/s) 
     block_size=16384 mb_io_count=1 mb_io_size=16384 (bytes) 
     tot_io_size=36(MB) time=196(ms)
    kkeCostToTime: using io calibrate stats 
     maxmbps=290(MB/s) maxpmbps=182(MB/s) 
     block_size=16384 mb_io_count=1 mb_io_size=16384 (bytes) 
     tot_io_size=36(MB) time=196(ms)
    kkeCostToTime: using io calibrate stats 
     maxmbps=290(MB/s) maxpmbps=182(MB/s) 
     block_size=16384 mb_io_count=1 mb_io_size=16384 (bytes) 
     tot_io_size=36(MB) time=196(ms)
    Starting SQL statement dump
    user_id=265 user_name=PARTITION_ADMIN module=SQL Developer action=
    sql_id=ct3jkvbv19af7 plan_hash_value=217421128 problem_type=3
    ----- Current SQL Statement for this session (sql_id=ct3jkvbv19af7) -----
    select *
      from   hareket_table
      where islem_tar between to_date('01/05/2012', 'dd/mm/yyyy') and to_date('14/07/2012', 'dd/mm/yyyy')
    sql_text_length=134
    sql=select *
      from   hareket_table
      where islem_tar between to_date('01/05/2012', 'dd/mm/yyyy') and to_date('14/07/2012', 'dd/mm/yyyy')
    ----- Explain Plan Dump -----
    ----- Plan Table -----
     
    ============
    Plan Table
    ============
    ---------------------------------------------+-----------------------------------+-------------------------+---------------+
    | Id  | Operation             | Name         | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop |
    ---------------------------------------------+-----------------------------------+-------------------------+---------------+
    | 0   | SELECT STATEMENT      |              |       |       |  2288 |           |      |      |           |       |       |
    | 1   |  PX COORDINATOR       |              |       |       |       |           |      |      |           |       |       |
    | 2   |   PX SEND QC (RANDOM) | :TQ10000     | 8596K | 1410M |  2288 |  00:00:33 |:Q1000| P->S |QC (RANDOM)|       |       |
    | 3   |    PX BLOCK ITERATOR  |              | 8596K | 1410M |  2288 |  00:00:33 |:Q1000| PCWC |           | 90    | 92    |
    | 4   |     TABLE ACCESS FULL | HAREKET_TABLE| 8596K | 1410M |  2288 |  00:00:33 |:Q1000| PCWP |           | 90    | 92    |
    ---------------------------------------------+-----------------------------------+-------------------------+---------------+
    Predicate Information:
    ----------------------
    4 - access(:Z>=:Z AND :Z<=:Z)
    4 - filter("ISLEM_TAR"<=TO_DATE(' 2012-07-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
     
  • 38. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    Content of other_xml column
    ===========================
    nodeid/pflags: 4 1nodeid/pflags: 3 1  db_version     : 11.2.0.2
      parse_schema   : PARTITION_ADMIN
      plan_hash      : 217421128
      plan_hash_2    : 1884306151
      Outline Data:
      /*+
        BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
          DB_VERSION('11.2.0.2')
          OPT_PARAM('query_rewrite_enabled' 'false')
          OPT_PARAM('optimizer_index_cost_adj' 30)
          OPT_PARAM('optimizer_index_caching' 50)
          OPT_PARAM('optimizer_dynamic_sampling' 6)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          FULL(@"SEL$1" "HAREKET_TABLE"@"SEL$1")
        END_OUTLINE_DATA
      */
     
    Optimizer state dump:
    Compilation Environment Dump
    optimizer_mode_hinted               = false
    optimizer_features_hinted           = 0.0.0
    parallel_execution_enabled          = true
    parallel_query_forced_dop           = 0
    parallel_dml_forced_dop             = 0
    parallel_ddl_forced_degree          = 0
    parallel_ddl_forced_instances       = 0
    _query_rewrite_fudge                = 90
    optimizer_features_enable           = 11.2.0.2
    _optimizer_search_limit             = 5
    cpu_count                           = 10
    active_instance_count               = 1
    parallel_threads_per_cpu            = 2
    hash_area_size                      = 131072
    bitmap_merge_area_size              = 1048576
    sort_area_size                      = 65536
    sort_area_retained_size             = 0
    _sort_elimination_cost_ratio        = 0
    _optimizer_block_size               = 16384
    _sort_multiblock_read_count         = 2
    _hash_multiblock_io_count           = 0
    _db_file_optimizer_read_count       = 16
    _optimizer_max_permutations         = 2000
    pga_aggregate_target                = 12582912 KB
    _pga_max_size                       = 2097152 KB
    _query_rewrite_maxdisjunct          = 257
    _smm_auto_min_io_size               = 48 KB
    _smm_auto_max_io_size               = 240 KB
    _smm_min_size                       = 1024 KB
    _smm_max_size                       = 1048576 KB
    _smm_px_max_size                    = 6291456 KB
    _cpu_to_io                          = 0
    _optimizer_undo_cost_change         = 11.2.0.2
    parallel_query_mode                 = enabled
    parallel_dml_mode                   = disabled
    parallel_ddl_mode                   = enabled
    optimizer_mode                      = all_rows
    sqlstat_enabled                     = false
    _optimizer_percent_parallel         = 101
    _always_anti_join                   = choose
    _always_semi_join                   = choose
    _optimizer_mode_force               = true
    _partition_view_enabled             = true
    _always_star_transformation         = false
    _query_rewrite_or_error             = false
    _hash_join_enabled                  = true
    cursor_sharing                      = exact
    _b_tree_bitmap_plans                = true
    star_transformation_enabled         = false
    _optimizer_cost_model               = choose
    _new_sort_cost_estimate             = true
    _complex_view_merging               = true
    _unnest_subquery                    = true
    _eliminate_common_subexpr           = true
    _pred_move_around                   = true
    _convert_set_to_join                = false
    _push_join_predicate                = true
    _push_join_union_view               = true
    _fast_full_scan_enabled             = true
    _optim_enhance_nnull_detection      = true
    _parallel_broadcast_enabled         = true
    _px_broadcast_fudge_factor          = 100
    _ordered_nested_loop                = true
    _no_or_expansion                    = false
    optimizer_index_cost_adj            = 30
    optimizer_index_caching             = 50
    _system_index_caching               = 0
    _disable_datalayer_sampling         = false
    query_rewrite_enabled               = false
    query_rewrite_integrity             = enforced
    _query_cost_rewrite                 = true
    _query_rewrite_2                    = true
    _query_rewrite_1                    = true
    _query_rewrite_expression           = true
    _query_rewrite_jgmigrate            = true
    _query_rewrite_fpc                  = true
    _query_rewrite_drj                  = true
    _full_pwise_join_enabled            = true
    _partial_pwise_join_enabled         = true
    _left_nested_loops_random           = true
    _improved_row_length_enabled        = true
    _index_join_enabled                 = true
    _enable_type_dep_selectivity        = true
    _improved_outerjoin_card            = true
    _optimizer_adjust_for_nulls         = true
    _optimizer_degree                   = 0
    _use_column_stats_for_function      = true
    _subquery_pruning_enabled           = true
    _subquery_pruning_mv_enabled        = false
    _or_expand_nvl_predicate            = true
    _like_with_bind_as_equality         = false
    _table_scan_cost_plus_one           = true
    _cost_equality_semi_join            = true
    _default_non_equality_sel_check     = true
    _new_initial_join_orders            = true
    _oneside_colstat_for_equijoins      = true
    _optim_peek_user_binds              = true
    _minimal_stats_aggregation          = true
    _force_temptables_for_gsets         = false
    workarea_size_policy                = auto
    _smm_auto_cost_enabled              = true
    _gs_anti_semi_join_allowed          = true
    _optim_new_default_join_sel         = true
    optimizer_dynamic_sampling          = 2
    _pre_rewrite_push_pred              = true
    _optimizer_new_join_card_computation = true
    _union_rewrite_for_gs               = yes_gset_mvs
    _generalized_pruning_enabled        = true
    _optim_adjust_for_part_skews        = true
    _force_datefold_trunc               = false
    statistics_level                    = typical
    _optimizer_system_stats_usage       = true
    skip_unusable_indexes               = true
    _remove_aggr_subquery               = true
    _optimizer_push_down_distinct       = 0
    _dml_monitoring_enabled             = true
    _optimizer_undo_changes             = false
    _predicate_elimination_enabled      = true
    _nested_loop_fudge                  = 100
    _project_view_columns               = true
    _local_communication_costing_enabled = true
    _local_communication_ratio          = 50
    _query_rewrite_vop_cleanup          = true
    _slave_mapping_enabled              = true
    _optimizer_cost_based_transformation = linear
    _optimizer_mjc_enabled              = true
    _right_outer_hash_enable            = true
    _spr_push_pred_refspr               = true
    _optimizer_cache_stats              = false
    _optimizer_cbqt_factor              = 50
    _optimizer_squ_bottomup             = true
    _fic_area_size                      = 131072
    _optimizer_skip_scan_enabled        = true
    _optimizer_cost_filter_pred         = false
    _optimizer_sortmerge_join_enabled   = true
    _optimizer_join_sel_sanity_check    = true
    _mmv_query_rewrite_enabled          = true
    _bt_mmv_query_rewrite_enabled       = true
    _add_stale_mv_to_dependency_list    = true
    _distinct_view_unnesting            = false
    _optimizer_dim_subq_join_sel        = true
    _optimizer_disable_strans_sanity_checks = 0
    _optimizer_compute_index_stats      = true
    _push_join_union_view2              = true
    _optimizer_ignore_hints             = false
    _optimizer_random_plan              = 0
    _query_rewrite_setopgrw_enable      = true
    _optimizer_correct_sq_selectivity   = true
    _disable_function_based_index       = false
    _optimizer_join_order_control       = 3
    _optimizer_cartesian_enabled        = true
    _optimizer_starplan_enabled         = true
    _extended_pruning_enabled           = true
    _optimizer_push_pred_cost_based     = true
    _optimizer_null_aware_antijoin      = true
    _optimizer_extend_jppd_view_types   = true
    _sql_model_unfold_forloops          = run_time
    _enable_dml_lock_escalation         = false
    _bloom_filter_enabled               = true
    _update_bji_ipdml_enabled           = 0
    _optimizer_extended_cursor_sharing  = udo
    _dm_max_shared_pool_pct             = 1
    _optimizer_cost_hjsmj_multimatch    = true
    _optimizer_transitivity_retain      = true
    _px_pwg_enabled                     = true
    optimizer_secure_view_merging       = true
    _optimizer_join_elimination_enabled = true
    flashback_table_rpi                 = non_fbt
    _optimizer_cbqt_no_size_restriction = true
    _optimizer_enhanced_filter_push     = true
    _optimizer_filter_pred_pullup       = true
    _rowsrc_trace_level                 = 0
    _simple_view_merging                = true
    _optimizer_rownum_pred_based_fkr    = true
    _optimizer_better_inlist_costing    = all
    _optimizer_self_induced_cache_cost  = false
    _optimizer_min_cache_blocks         = 10
    _optimizer_or_expansion             = depth
    _optimizer_order_by_elimination_enabled = true
    _optimizer_outer_to_anti_enabled    = true
    _selfjoin_mv_duplicates             = true
    _dimension_skip_null                = true
    _force_rewrite_enable               = false
    _optimizer_star_tran_in_with_clause = true
    _optimizer_complex_pred_selectivity = true
    _optimizer_connect_by_cost_based    = true
    _gby_hash_aggregation_enabled       = true
    _globalindex_pnum_filter_enabled    = true
    _px_minus_intersect                 = true
    _fix_control_key                    = 0
    _force_slave_mapping_intra_part_loads = false
    _force_tmp_segment_loads            = false
    _query_mmvrewrite_maxpreds          = 10
    _query_mmvrewrite_maxintervals      = 5
    _query_mmvrewrite_maxinlists        = 5
    _query_mmvrewrite_maxdmaps          = 10
    _query_mmvrewrite_maxcmaps          = 20
    _query_mmvrewrite_maxregperm        = 512
    _query_mmvrewrite_maxqryinlistvals  = 500
    _disable_parallel_conventional_load = false
    _trace_virtual_columns              = false
    _replace_virtual_columns            = true
    _virtual_column_overload_allowed    = true
    _kdt_buffering                      = true
    _first_k_rows_dynamic_proration     = true
    _optimizer_sortmerge_join_inequality = true
    _optimizer_aw_stats_enabled         = true
    _bloom_pruning_enabled              = true
    result_cache_mode                   = MANUAL
    _px_ual_serial_input                = true
    _optimizer_skip_scan_guess          = false
    _enable_row_shipping                = true
    _row_shipping_threshold             = 80
    _row_shipping_explain               = false
    transaction_isolation_level         = read_commited
    _optimizer_distinct_elimination     = true
    _optimizer_multi_level_push_pred    = true
    _optimizer_group_by_placement       = true
    _optimizer_rownum_bind_default      = 10
    _enable_query_rewrite_on_remote_objs = true
    _optimizer_extended_cursor_sharing_rel = simple
    _optimizer_adaptive_cursor_sharing  = true
    _direct_path_insert_features        = 0
    _optimizer_improve_selectivity      = true
    optimizer_use_pending_statistics    = false
    _optimizer_enable_density_improvements = true
    _optimizer_aw_join_push_enabled     = true
    _optimizer_connect_by_combine_sw    = true
    _enable_pmo_ctas                    = 0
    _optimizer_native_full_outer_join   = force
    _bloom_predicate_enabled            = true
    _optimizer_enable_extended_stats    = true
    _is_lock_table_for_ddl_wait_lock    = 0
    _pivot_implementation_method        = choose
    optimizer_capture_sql_plan_baselines = false
    optimizer_use_sql_plan_baselines    = true
    _optimizer_star_trans_min_cost      = 0
    _optimizer_star_trans_min_ratio     = 0
    _with_subquery                      = OPTIMIZER
    _optimizer_fkr_index_cost_bias      = 10
    _optimizer_use_subheap              = true
    parallel_degree_policy              = manual
    parallel_degree                     = 20
    parallel_min_time_threshold         = 10
    _parallel_time_unit                 = 10
    _optimizer_or_expansion_subheap     = true
    _optimizer_free_transformation_heap = true
    _optimizer_reuse_cost_annotations   = true
    _result_cache_auto_size_threshold   = 100
    _result_cache_auto_time_threshold   = 1000
    _optimizer_nested_rollup_for_gset   = 100
    _nlj_batching_enabled               = 1
    parallel_query_default_dop          = 20
    is_recur_flags                      = 0
    optimizer_use_invisible_indexes     = false
    flashback_data_archive_internal_cursor = 0
    _optimizer_extended_stats_usage_control = 192
    _parallel_syspls_obey_force         = true
    cell_offload_processing             = true
    _rdbms_internal_fplib_enabled       = false
    db_file_multiblock_read_count       = 16
    _bloom_folding_enabled              = true
    _mv_generalized_oj_refresh_opt      = true
    cell_offload_compaction             = ADAPTIVE
    cell_offload_plan_display           = AUTO
    _bloom_predicate_pushdown_to_storage = true
    _bloom_vector_elements              = 0
    _bloom_pushing_max                  = 512
    parallel_degree_limit               = 65535
    parallel_force_local                = false
    parallel_max_degree                 = 20
    total_cpu_count                     = 10
    _optimizer_coalesce_subqueries      = true
    _optimizer_fast_pred_transitivity   = true
    _optimizer_fast_access_pred_analysis = true
    _optimizer_unnest_disjunctive_subq  = true
    _optimizer_unnest_corr_set_subq     = true
    _optimizer_distinct_agg_transform   = true
    _aggregation_optimization_settings  = 0
    _optimizer_connect_by_elim_dups     = true
    _optimizer_eliminate_filtering_join = true
    _connect_by_use_union_all           = true
    dst_upgrade_insert_conv             = true
    advanced_queuing_internal_cursor    = 0
    _optimizer_unnest_all_subqueries    = true
    parallel_autodop                    = 0
    parallel_ddldml                     = 0
    _parallel_cluster_cache_policy      = adaptive
    _parallel_scalability               = 50
    iot_internal_cursor                 = 0
    _optimizer_instance_count           = 0
    _optimizer_connect_by_cb_whr_only   = false
    _suppress_scn_chk_for_cqn           = nosuppress_1466
    _optimizer_join_factorization       = true
    _optimizer_use_cbqt_star_transformation = true
    _optimizer_table_expansion          = true
    _and_pruning_enabled                = true
    _deferred_constant_folding_mode     = DEFAULT
    _optimizer_distinct_placement       = true
    partition_pruning_internal_cursor   = 0
    parallel_hinted                     = none
    _sql_compatibility                  = 0
    _optimizer_use_feedback             = true
    _optimizer_try_st_before_jppd       = true
    _dml_frequency_tracking             = false
    _optimizer_interleave_jppd          = true
    kkb_drop_empty_segments             = 0
    _px_partition_scan_enabled          = true
    _px_partition_scan_threshold        = 64
    _optimizer_false_filter_pred_pullup = true
    _bloom_minmax_enabled               = true
    only_move_row                       = 0
    _optimizer_enable_table_lookup_by_nl = true
  • 39. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    Bug Fix Control Environment
        fix  3834770 = 1       
        fix  3746511 = enabled
        fix  4519016 = enabled
        fix  3118776 = enabled
        fix  4488689 = enabled
        fix  2194204 = disabled
        fix  2660592 = enabled
        fix  2320291 = enabled
        fix  2324795 = enabled
        fix  4308414 = enabled
        fix  3499674 = disabled
        fix  4569940 = enabled
        fix  4631959 = enabled
        fix  4519340 = enabled
        fix  4550003 = enabled
        fix  1403283 = enabled
        fix  4554846 = enabled
        fix  4602374 = enabled
        fix  4584065 = enabled
        fix  4545833 = enabled
        fix  4611850 = enabled
        fix  4663698 = enabled
        fix  4663804 = enabled
        fix  4666174 = enabled
        fix  4567767 = enabled
        fix  4556762 = 15      
        fix  4728348 = enabled
        fix  4708389 = enabled
        fix  4175830 = enabled
        fix  4752814 = enabled
        fix  4583239 = enabled
        fix  4386734 = enabled
        fix  4887636 = enabled
        fix  4483240 = enabled
        fix  4872602 = disabled
        fix  4711525 = enabled
        fix  4545802 = enabled
        fix  4605810 = enabled
        fix  4704779 = enabled
        fix  4900129 = enabled
        fix  4924149 = enabled
        fix  4663702 = enabled
        fix  4878299 = enabled
        fix  4658342 = enabled
        fix  4881533 = enabled
        fix  4676955 = enabled
        fix  4273361 = enabled
        fix  4967068 = enabled
        fix  4969880 = disabled
        fix  5005866 = enabled
        fix  5015557 = enabled
        fix  4705343 = enabled
        fix  4904838 = enabled
        fix  4716096 = enabled
        fix  4483286 = disabled
        fix  4722900 = enabled
        fix  4615392 = enabled
        fix  5096560 = enabled
        fix  5029464 = enabled
        fix  4134994 = enabled
        fix  4904890 = enabled
        fix  5104624 = enabled
        fix  5014836 = enabled
        fix  4768040 = enabled
        fix  4600710 = enabled
        fix  5129233 = enabled
        fix  4595987 = enabled
        fix  4908162 = enabled
        fix  5139520 = enabled
        fix  5084239 = enabled
        fix  5143477 = disabled
        fix  2663857 = enabled
        fix  4717546 = enabled
        fix  5240264 = disabled
        fix  5099909 = enabled
        fix  5240607 = enabled
        fix  5195882 = enabled
        fix  5220356 = enabled
        fix  5263572 = enabled
        fix  5385629 = enabled
        fix  5302124 = enabled
        fix  5391942 = enabled
        fix  5384335 = enabled
        fix  5482831 = enabled
        fix  4158812 = enabled
        fix  5387148 = enabled
        fix  5383891 = enabled
        fix  5466973 = enabled
        fix  5396162 = enabled
        fix  5394888 = enabled
        fix  5395291 = enabled
        fix  5236908 = enabled
        fix  5509293 = enabled
        fix  5449488 = enabled
        fix  5567933 = enabled
        fix  5570494 = enabled
        fix  5288623 = enabled
        fix  5505995 = enabled
        fix  5505157 = enabled
        fix  5112460 = enabled
        fix  5554865 = enabled
        fix  5112260 = enabled
        fix  5112352 = enabled
        fix  5547058 = enabled
        fix  5618040 = enabled
        fix  5585313 = enabled
        fix  5547895 = enabled
        fix  5634346 = enabled
        fix  5620485 = enabled
        fix  5483301 = enabled
        fix  5657044 = enabled
        fix  5694984 = enabled
        fix  5868490 = enabled
        fix  5650477 = enabled
        fix  5611962 = enabled
        fix  4279274 = enabled
        fix  5741121 = enabled
        fix  5714944 = enabled
        fix  5391505 = enabled
        fix  5762598 = enabled
        fix  5578791 = enabled
        fix  5259048 = enabled
        fix  5882954 = enabled
        fix  2492766 = enabled
        fix  5707608 = enabled
        fix  5891471 = enabled
        fix  5884780 = enabled
        fix  5680702 = enabled
        fix  5371452 = enabled
        fix  5838613 = enabled
        fix  5949981 = enabled
        fix  5624216 = enabled
        fix  5741044 = enabled
        fix  5976822 = enabled
        fix  6006457 = enabled
        fix  5872956 = enabled
        fix  5923644 = enabled
        fix  5943234 = enabled
        fix  5844495 = enabled
        fix  4168080 = enabled
        fix  6020579 = enabled
        fix  5842686 = disabled
        fix  5996801 = enabled
        fix  5593639 = enabled
        fix  6133948 = enabled
        fix  3151991 = enabled
        fix  6146906 = enabled
        fix  6239909 = enabled
        fix  6267621 = enabled
        fix  5909305 = enabled
        fix  6279918 = enabled
        fix  6141818 = enabled
        fix  6151963 = enabled
        fix  6251917 = enabled
        fix  6282093 = enabled
        fix  6119510 = enabled
        fix  6119382 = enabled
        fix  3801750 = enabled
        fix  5705630 = disabled
        fix  5944076 = enabled
        fix  5406763 = enabled
        fix  6070954 = enabled
        fix  6282944 = enabled
        fix  6138746 = enabled
        fix  6082745 = enabled
        fix  3426050 = enabled
        fix   599680 = enabled
        fix  6062266 = enabled
        fix  6087237 = enabled
        fix  6122894 = enabled
        fix  6377505 = enabled
        fix  5893768 = enabled
        fix  6163564 = enabled
        fix  6073325 = enabled
        fix  6188881 = enabled
        fix  6007259 = enabled
        fix  6239971 = enabled
        fix  5284200 = disabled
        fix  6042205 = enabled
        fix  6051211 = enabled
        fix  6434668 = enabled
        fix  6438752 = enabled
        fix  5936366 = enabled
        fix  6439032 = enabled
        fix  6438892 = enabled
        fix  6006300 = enabled
        fix  5947231 = enabled
        fix  5416118 = 1       
        fix  6365442 = 1       
        fix  6239039 = enabled
        fix  6502845 = enabled
        fix  6913094 = enabled
        fix  6029469 = enabled
        fix  5919513 = enabled
        fix  6057611 = enabled
        fix  6469667 = enabled
        fix  6608941 = disabled
        fix  6368066 = enabled
        fix  6329318 = enabled
        fix  6656356 = enabled
        fix  4507997 = enabled
        fix  6671155 = enabled
        fix  6694548 = enabled
        fix  6688200 = enabled
        fix  6612471 = enabled
        fix  6708183 = disabled
        fix  6326934 = enabled
        fix  6520717 = disabled
        fix  6714199 = enabled
        fix  6681545 = enabled
        fix  6748058 = enabled
        fix  6167716 = enabled
        fix  6674254 = enabled
        fix  6468287 = enabled
        fix  6503543 = enabled
        fix  6808773 = disabled
        fix  6766962 = enabled
        fix  6120483 = enabled
        fix  6670551 = enabled
        fix  6771838 = enabled
        fix  6626018 = disabled
        fix  6530596 = enabled
        fix  6778642 = enabled
        fix  6699059 = enabled
        fix  6376551 = enabled
        fix  6429113 = enabled
        fix  6782437 = enabled
        fix  6776808 = enabled
        fix  6765823 = enabled
        fix  6768660 = enabled
        fix  6782665 = enabled
        fix  6610822 = enabled
        fix  6514189 = enabled
        fix  6818410 = enabled
        fix  6827696 = enabled
        fix  6773613 = enabled
        fix  5902962 = enabled
        fix  6956212 = enabled
        fix  3056297 = enabled
        fix  6440977 = disabled
        fix  6972291 = disabled
        fix  6904146 = enabled
        fix  6221403 = enabled
        fix  5475051 = enabled
        fix  6845871 = enabled
        fix  5468809 = enabled
        fix  6917633 = enabled
        fix  4444536 = disabled
        fix  6955210 = enabled
        fix  6994194 = enabled
        fix  6399597 = disabled
        fix  6951776 = enabled
        fix  5648287 = 3       
        fix  6987082 = disabled
        fix  7132036 = enabled
        fix  6980350 = enabled
        fix  5199213 = enabled
        fix  7138405 = enabled
        fix  7148689 = enabled
        fix  6820988 = enabled
        fix  7032684 = enabled
        fix  6617866 = enabled
        fix  7155968 = enabled
        fix  7127980 = enabled
        fix  6982954 = enabled
        fix  7241819 = enabled
        fix  6897034 = enabled
        fix  7236148 = enabled
        fix  7298570 = enabled
        fix  7249095 = enabled
        fix  7314499 = enabled
        fix  7324224 = enabled
        fix  7289023 = enabled
        fix  7237571 = enabled
        fix  7116357 = enabled
        fix  7345484 = enabled
        fix  7375179 = enabled
        fix  6430500 = disabled
        fix  5897486 = enabled
        fix  6774209 = enabled
        fix  7306637 = enabled
        fix  6451322 = enabled
        fix  7208131 = enabled
        fix  7388652 = enabled
        fix  7127530 = enabled
        fix  6751206 = enabled
        fix  6669103 = enabled
        fix  7430474 = enabled
        fix  6990305 = enabled
        fix  7043307 = enabled
        fix  3120429 = enabled
        fix  7452823 = disabled
        fix  6838105 = enabled
        fix  6769711 = enabled
        fix  7170213 = enabled
        fix  6528872 = enabled
        fix  7295298 = enabled
        fix  5922070 = enabled
        fix  7259468 = enabled
        fix  6418552 = enabled
        fix  4619997 = enabled
        fix  7524366 = enabled
        fix  6942476 = enabled
        fix  6418771 = enabled
        fix  7375077 = enabled
        fix  5400639 = enabled
        fix  4570921 = enabled
        fix  7426911 = enabled
        fix  5099019 = disabled
        fix  7528216 = enabled
        fix  7521266 = enabled
        fix  7385140 = enabled
        fix  7576516 = enabled
        fix  7573526 = enabled
        fix  7576476 = enabled
        fix  7165898 = enabled
        fix  7263214 = enabled
        fix  3320140 = enabled
        fix  7555510 = enabled
        fix  7613118 = enabled
        fix  7597059 = enabled
        fix  7558911 = enabled
        fix  5520732 = enabled
        fix  7679490 = disabled
        fix  7449971 = enabled
        fix  3628118 = enabled
        fix  4370840 = enabled
        fix  7281191 = enabled
        fix  7519687 = enabled
        fix  5029592 = 3       
        fix  6012093 = 1       
        fix  6053861 = disabled
        fix  6941515 = disabled
        fix  7696414 = enabled
        fix  7272039 = enabled
        fix  7834811 = enabled
        fix  7640597 = enabled
        fix  7341616 = enabled
        fix  7168184 = enabled
        fix   399198 = enabled
        fix  7831070 = enabled
        fix  7676897 = disabled
        fix  7414637 = enabled
        fix  7585456 = enabled
        fix  8202421 = enabled
        fix  7658097 = disabled
        fix  8251486 = enabled
        fix  7132684 = enabled
        fix  7512227 = enabled
        fix  6972987 = enabled
        fix  7199035 = enabled
        fix  8243446 = enabled
        fix  7650462 = enabled
        fix  6720701 = enabled
        fix  7592673 = enabled
        fix  7718694 = enabled
        fix  7534027 = enabled
        fix  7708267 = enabled
        fix  5716785 = enabled
        fix  7356191 = enabled
        fix  7679161 = enabled
        fix  7597159 = enabled
        fix  7499258 = enabled
        fix  8328363 = enabled
        fix  7452863 = enabled
        fix  8284930 = enabled
        fix  7298626 = enabled
        fix  7657126 = enabled
        fix  8371884 = enabled
        fix  8318020 = enabled
        fix  8255423 = enabled
        fix  7135745 = enabled
        fix  8356253 = enabled
        fix  7534257 = enabled
        fix  8323407 = enabled
        fix  7539815 = enabled
        fix  8289316 = enabled
        fix  8447850 = enabled
        fix  7675944 = enabled
        fix  8355120 = enabled
        fix  7176746 = enabled
        fix  8442891 = enabled
        fix  8373261 = enabled
        fix  7679164 = enabled
        fix  7670533 = enabled
        fix  8408665 = enabled
        fix  8491399 = enabled
        fix  8348392 = enabled
        fix  8348585 = enabled
        fix  8508056 = enabled
        fix  8335178 = enabled
        fix  8515269 = enabled
        fix  8247017 = enabled
        fix  7325597 = enabled
        fix  8531490 = enabled
        fix  6163600 = enabled
        fix  8589278 = disabled
        fix  8557992 = enabled
        fix  7556098 = enabled
        fix  8580883 = enabled
        fix  5892599 = disabled
        fix  8609714 = enabled
        fix  8619631 = disabled
        fix  8672915 = enabled
        fix  8514561 = enabled
        fix  8213977 = enabled
        fix  8560951 = disabled
        fix  8578587 = enabled
        fix  8287870 = enabled
        fix  8467123 = enabled
        fix  8602185 = enabled
        fix  8519457 = enabled
        fix  3335182 = enabled
        fix  8602840 = enabled
        fix  8725296 = enabled
        fix  8628970 = enabled
        fix  6754080 = enabled
        fix  8767442 = enabled
        fix  8760135 = enabled
        fix  8644935 = enabled
        fix  8352378 = enabled
        fix  8685327 = enabled
        fix  8763472 = enabled
        fix  8773324 = enabled
        fix  8813674 = enabled
        fix  8532236 = enabled
        fix  8629716 = enabled
        fix  7277732 = enabled
        fix  8692170 = enabled
        fix  8900973 = enabled
        fix  8919133 = enabled
        fix  8927050 = enabled
        fix  8551880 = enabled
        fix  8901237 = enabled
        fix  8812372 = enabled
        fix  6236862 = enabled
        fix  8528517 = enabled
        fix  7215982 = enabled
        fix  8214022 = enabled
        fix  8595392 = enabled
        fix  8890233 = enabled
        fix  8999317 = enabled
        fix  9004800 = enabled
        fix  8986163 = enabled
        fix  8855396 = enabled
        fix  8800514 = 20      
        fix  9007859 = enabled
        fix  8198783 = enabled
        fix  9053879 = enabled
        fix  6086930 = enabled
        fix  7641601 = enabled
        fix  9052506 = enabled
        fix  9103775 = enabled
        fix  9047975 = enabled
        fix  8893626 = enabled
        fix  9111170 = enabled
        fix  8971829 = enabled
        fix  7628358 = enabled
        fix  9125151 = enabled
        fix  9039715 = enabled
        fix  9106224 = enabled
        fix  9185228 = enabled
        fix  9206747 = enabled
        fix  9088510 = enabled
        fix  9143856 = enabled
        fix  8833381 = enabled
        fix  8949971 = enabled
        fix  8951812 = enabled
        fix  9148171 = enabled
        fix  8706652 = enabled
        fix  9245114 = enabled
        fix  8802198 = enabled
        fix  9011016 = enabled
        fix  9265681 = enabled
        fix  7284269 = enabled
        fix  9272549 = enabled
        fix  8917507 = 7       
        fix  8531463 = enabled
        fix  9263333 = enabled
        fix  8675087 = enabled
        fix  8571403 = enabled
        fix  8896955 = enabled
        fix  9041934 = enabled
        fix  9344709 = enabled
        fix  9024933 = enabled
        fix  9033718 = enabled
        fix  9240455 = enabled
        fix  9081848 = enabled
        fix  5982893 = enabled
        fix  9287401 = enabled
        fix  8590021 = enabled
        fix  9340120 = enabled
        fix  9355794 = enabled
        fix  9356656 = enabled
        fix  9385634 = enabled
        fix  9069046 = enabled
        fix  9239337 = enabled
        fix  9300228 = enabled
        fix  9298010 = enabled
        fix  9384170 = enabled
        fix  9301862 = disabled
        fix  9407929 = enabled
        fix  8836806 = enabled
        fix  9344055 = enabled
        fix  9274675 = enabled
        fix  9203723 = enabled
        fix  9443476 = enabled
        fix  9195582 = enabled
        fix  8226666 = enabled
        fix  9433490 = enabled
        fix  9065494 = enabled
        fix  9303766 = enabled
        fix  9437283 = enabled
        fix  9116214 = enabled
        fix  9456688 = enabled
        fix  9456746 = disabled
        fix  9342979 = enabled
        fix  9465425 = enabled
        fix  9092442 = enabled
        fix  4926618 = enabled
        fix  8792846 = enabled
        fix  9474259 = enabled
        fix  9495669 = disabled
        fix  6472966 = enabled
        fix  6408301 = enabled
        fix  9380298 = disabled
        fix  8500130 = enabled
        fix  9584723 = enabled
        fix  9270951 = enabled
        fix  9508254 = enabled
        fix  9593680 = enabled
        fix  9196440 = disabled
        fix  9309281 = enabled
        fix  8693158 = enabled
        fix  9381638 = enabled
        fix  9383967 = enabled
        fix  7711900 = enabled
        fix  9218587 = enabled
        fix  9728438 = enabled
        fix  9038395 = enabled
        fix  9577300 = enabled
        fix  9171113 = enabled
        fix  8973745 = enabled
        fix  9732434 = enabled
        fix  8937971 = disabled
        fix  9102474 = enabled
        fix  9243499 = enabled
        fix  9912503 = enabled
        fix  9153459 = enabled
        fix  9762592 = 3       
        fix  9785632 = enabled
    Query Block Registry:
    SEL$1 0x10d1cef0 (PARSER) [FINAL]
    :
        call(in-use=109584, alloc=147496), compile(in-use=200448, alloc=264984), execution(in-use=38776, alloc=40624)
    End of Optimizer State Dump
    Dumping Hints
    =============
    ====================== END SQL Statement Dump ======================
  • 40. Re: why is my sql running parallel ?
    CharlesHooper Expert
    Currently Being Moderated
    Mustafa,

    Unless I am overlooking something that is not obvious, the 10053 trace is seemingly indicating what IS NOT happening, rather than providing a suggestion of what IS happening (knowing what IS NOT happening is important, and is one of the reasons why I suggested collecting a 10053 trace).
    * The parallel_degree_policy parameter is not being adjusted at the session level, “parallel_degree_policy = manual” is listed in the parameter list, so that cause for parallelism may be ruled out.
    * The SQL Developer application is not adding a parallel hint to the query that is submitted, the “Current SQL Statement for this session” section shows the SQL statement that was submitted, so that cause for parallelism may be ruled out.
    * ALTER SESSION FORCE PARALLEL QUERY; is not executed by the SQL Developer - "parallel_query_mode = forced" does not appear under the heading "PARAMETERS WITH ALTERED VALUES".
    * The default degree of parallel for the indexes is shown as 1, but the default degree of parallel of the table is apparently not shown in the 10053 trace (I saw the same value of 0 regardless of whether I set a test table to NOPARALLEL, just PARALLEL, and PARALLEL 16). You mentioned that setting the table to NOPARALLEL or PARALLEL 1 made no difference in the result.

    You might check to see if the SQL Tuning Advisor previously suggested accepting a profile to convert the serial execution of the SQL statement into a parallel execution. From the documentation:
    http://docs.oracle.com/cd/E14072_01/server.112/e10821/sql_tune.htm#CHDHBJEC
    “Sometimes SQL Tuning Advisor may recommend accepting a profile that uses the Automatic Degree of Parallelism (Auto DOP) feature. A parallel query profile is only recommended when the original plan is serial and when parallel execution can significantly reduce the elapsed time for a long-running query. When it recommends a profile that uses Auto DOP, SQL Tuning Advisor gives details about the performance overhead of using parallel execution for the SQL statement in the report.”

    How old is this table – did it once reside in an Oracle Database 8.1.7.4 or earlier? An interesting quote from the documentation:
    http://docs.oracle.com/cd/E14072_01/server.112/e10837/parallel.htm#i1009083
    “For tables created prior to Oracle9i Database release version 9.0.1 or tables that do not have the parallel DML itl invariant property, the DOP is equal to the number of partitions or subpartitions.”
    Use the following query to locate potentially affected tables:
    SELECT u.name, o.name FROM obj$ o, tab$ t, user$ u
    WHERE o.obj# = t.obj# AND o.owner# = u.user#
     AND bitand(t.property,536870912) != 536870912;
    Perhaps someone else knows if the above quote applies to parallel query also?

    If you have access to Metalink (My Oracle Support), you might take a look at the following (somewhat outdated) article: Doc ID 196938.1, “Why Did My Query Go Parallel?” I believe that the items mentioned in the Metalink article as causes for parallel execution have been determined to not apply to your specific SQL statement and execution environment.

    Doc ID 464057.1, “Resource Manager And Parallel Query - How Does It Work?” indicates that even if an execution plan indicates that parallel query will be used, a resource manager profile could still force a serial execution.

    Doc ID 1467105.1, “SQLDeveloper Holds On To Query Slaves where Paralleism is in use” also appears to be interesting, and might be one of the reasons why you do not want SQL Developer executing all SQL statements in parallel. A longer article that describes the problem may be found here: http://structureddata.org/2012/02/29/pitfalls-of-using-parallel-execution-with-sql-developer/

    There is a chance that the parallel execution in this case is caused by a bug in Oracle Database 11.2.0.2. I have not yet located a specific bug number that might apply to your particular situation.

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 41. Re: why is my sql running parallel ?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    Charles,

    thanks a lot for your answer. I was on a trip so I couldn't response. in a week, I will check the documentation you gave me and inform you if I find anything. if not, I will create an SR for this. appreciate for your help.

    as a short answer one of your question, this db is upgrated from 9i to 11gr2.
1 2 3 Previous Next

Legend

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