An Interesting ERP Problem in Oracle 11g that is Not a Problem in 10g R2

15 01 2010

January 15, 2010

Let’s imagine that there is an ERP platform that supports Oracle Database 10.2.0.x, but not Oracle 11.1.0.x (or 11.2.0.1).  Why would the vendor not support Oracle 11g?  Read this article and see if you are able to determine the source of the problem, and a solution.  Yes, there is a solution, but not the one that you are thinking about (the solution that I put together works with 11.1.0.6, 11.1.0.7, and 11.2.0.1).

Here is the story:

I have been testing Oracle 11.1.0.6 and 11.1.0.7 with an ERP package since January 2008 and have encountered an interesting issue where the ERP package throws an error “ORA-02005: implicit (-1) length not valid for this bind or define datatype” error, when selecting the BLOB column from any table containing a BLOB – this same ERP package executes without problem with Oracle 10.2.0.2/10.2.0.3/10.2.0.4. The table definition is as follows:

PART_ID     NOT NULL VARCHAR2(30)
TYPE        NOT NULL CHAR(1)
BITS                 BLOB
BITS_LENGTH NOT NULL NUMBER(38)

The previous version of the ERP package had the same table defined as follows, and the previous version of the ERP package had no problem with Oracle 11.1.0.6:

PART_ID     NOT NULL VARCHAR2(30)
TYPE        NOT NULL CHAR(1)
BITS                 LONG RAW
BITS_LENGTH NOT NULL NUMBER(38)

One of the SQL statements that tosses the error is this one:

SELECT BITS FROM PART_MFG_BINARY where TYPE = :1 and PART_ID = :2

A portion of a 10046 trace file from Oracle 10.2.0.2 that includes this SQL statement follows:

=====================
PARSING IN CURSOR #2 len=87 dep=0 uid=30 oct=3 lid=30 tim=749963475 hv=1159951869 ad='53a45ac8'
select mfg_name, mfg_part_id from part where id = :1                                  
END OF STMT
PARSE #2:c=0,e=1427,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=749963466
BINDS #2:
kkscoacd
 Bind#0
  oacdty=96 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=380b9b68  bln=32  avl=09  flg=05
  value="18567109M"
EXEC #2:c=0,e=3357,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=749971833
FETCH #2:c=0,e=52,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=749971968
FETCH #2:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=749973655
=====================
PARSING IN CURSOR #3 len=59 dep=0 uid=30 oct=3 lid=30 tim=749983314 hv=2907586799 ad='5457f690'
select part_udf_labels from APPLICATION_GLOBAL            
END OF STMT
PARSE #3:c=0,e=3389,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=749983305
BINDS #3:
EXEC #3:c=0,e=152,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=749986393
FETCH #3:c=0,e=124,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=749988214
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=11925 op='TABLE ACCESS FULL APPLICATION_GLOBAL (cr=7 pr=0 pw=0 time=104 us)'
=====================
PARSING IN CURSOR #3 len=59 dep=0 uid=30 oct=3 lid=30 tim=749992936 hv=2907586799 ad='5457f690'
select part_udf_labels from APPLICATION_GLOBAL            
END OF STMT
PARSE #3:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=749992932
BINDS #3:
EXEC #3:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=749996097
FETCH #3:c=0,e=116,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=749997800
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=12429 op='TABLE ACCESS BY INDEX ROWID PART (cr=3 pr=0 pw=0 time=48 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=12436 op='INDEX UNIQUE SCAN SYS_C005496 (cr=2 pr=0 pw=0 time=28 us)'
=====================
PARSING IN CURSOR #2 len=99 dep=0 uid=30 oct=3 lid=30 tim=750003263 hv=1519706035 ad='7e235fc0'
SELECT BITS FROM PART_MFG_BINARY  where TYPE = :1       and PART_ID = :2                          
END OF STMT
PARSE #2:c=0,e=1100,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=750003255
BINDS #2:
kkscoacd
 Bind#0
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=64 off=0
  kxsbbbfp=380bdcd8  bln=32  avl=01  flg=05
  value="D"
 Bind#1
  oacdty=96 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=32
  kxsbbbfp=380bdcf8  bln=32  avl=09  flg=01
  value="18567109M"
EXEC #2:c=0,e=2512,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=750022595
FETCH #2:c=0,e=33,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=750024142
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=101246 op='TABLE ACCESS BY INDEX ROWID PART_MFG_BINARY (cr=1 pr=0 pw=0 time=30 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=101249 op='INDEX UNIQUE SCAN SYS_C0018720 (cr=1 pr=0 pw=0 time=21 us)'
=====================

A portion of a 10046 trace file from Oracle 11.1.0.6 that includes this SQL statement follows:

=====================
PARSING IN CURSOR #3 len=87 dep=0 uid=59 oct=3 lid=59 tim=1023659125907 hv=1159951869 ad='22a109c8' sqlid='7k8rzcj2k6xgx'
select mfg_name, mfg_part_id from part where id = :1                                  
END OF STMT
PARSE #3:c=0,e=432,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1023659125903
BINDS #3:
 Bind#0
  oacdty=96 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=0c6e0fd4  bln=32  avl=09  flg=05
  value="18567109M"
EXEC #3:c=0,e=1068,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1023659130848
FETCH #3:c=0,e=37,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1023659132062
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=67567 op='TABLE ACCESS BY INDEX ROWID PART (cr=3 pr=0 pw=0 time=0 us cost=2 size=14 card=1)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=69248 op='INDEX UNIQUE SCAN SYS_C0011926 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
=====================
PARSING IN CURSOR #6 len=59 dep=0 uid=59 oct=3 lid=59 tim=1023659138710 hv=2907586799 ad='22a44ae8' sqlid='3n102kqqnwh7g'
select part_udf_labels from APPLICATION_GLOBAL            
END OF STMT
PARSE #6:c=0,e=701,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1023659138706
BINDS #6:
EXEC #6:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1023659142030
FETCH #6:c=0,e=55,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1023659143936
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=67410 op='TABLE ACCESS FULL APPLICATION_GLOBAL (cr=3 pr=0 pw=0 time=0 us cost=3 size=146 card=1)'
=====================
PARSING IN CURSOR #6 len=59 dep=0 uid=59 oct=3 lid=59 tim=1023659148354 hv=2907586799 ad='22a44ae8' sqlid='3n102kqqnwh7g'
select part_udf_labels from APPLICATION_GLOBAL            
END OF STMT
PARSE #6:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1023659148351
BINDS #6:
EXEC #6:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1023659151927
FETCH #6:c=0,e=46,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1023659153664
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=67410 op='TABLE ACCESS FULL APPLICATION_GLOBAL (cr=3 pr=0 pw=0 time=0 us cost=3 size=146 card=1)'
=====================
PARSING IN CURSOR #3 len=99 dep=0 uid=59 oct=3 lid=59 tim=1023659158452 hv=1519706035 ad='22a10580' sqlid='gm6bkj9d99rxm'
SELECT BITS FROM PART_MFG_BINARY  where TYPE = :1       and PART_ID = :2                          
END OF STMT
PARSE #3:c=0,e=399,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1023659158448
XCTEND rlbk=1, rd_only=1

In the above, notice the rollback (XCTEND rlbk=1, rd_only=1) before Oracle would have output the bind variable values in the trace file (bind variable values were never written).

Flushing the shared pool and enabling both a 10046 and 10053 trace in Oracle 11.1.0.6 during an execution of the program generated the following snippet from the 10053 trace file for the failing query on the PART_MFG_BINARY table:

******************************************
----- Current SQL Statement for this session (sql_id=gm6bkj9d99rxm) -----
SELECT BITS FROM PART_MFG_BINARY  where TYPE = :1       and PART_ID = :2                          
*******************************************
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_smm_min_size                       = 128 KB
_smm_max_size                       = 20480 KB
_smm_px_max_size                    = 51200 KB
sqlstat_enabled                     = true
Bug Fix Control Environment

  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = false
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.1.0.6
_optimizer_search_limit             = 5
cpu_count                           = 2
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               = 8192
_sort_multiblock_read_count         = 2
_hash_multiblock_io_count           = 0
_db_file_optimizer_read_count       = 8
_optimizer_max_permutations         = 2000
pga_aggregate_target                = 167936 KB
_pga_max_size                       = 204800 KB
_query_rewrite_maxdisjunct          = 257
_smm_auto_min_io_size               = 56 KB
_smm_auto_max_io_size               = 248 KB
_cpu_to_io                          = 0
_optimizer_undo_cost_change         = 11.1.0.6
parallel_query_mode                 = enabled
parallel_dml_mode                   = disabled
parallel_ddl_mode                   = enabled
optimizer_mode                      = all_rows
_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            = 100
optimizer_index_caching             = 0
_system_index_caching               = 0
_disable_datalayer_sampling         = false
query_rewrite_enabled               = true
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_maxmergedcmaps    = 50
_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
_aw_row_source_enabled              = 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            = false
_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_policy                    = manual
parallel_degree                     = 0
_parallel_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          = 0
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0
_optimizer_extended_stats_usage_control = 240
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 = enabled
    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  6239909 = enabled

  ***************************************
  PARAMETERS IN OPT_PARAM HINT
  ****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************

Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): no complex view or sub-queries.
CBQT: Validity checks failed for gm6bkj9d99rxm.
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)
**************************
CBQT bypassed for query block SEL$1 (#0): no complex view or sub-queries.
CBQT: Validity checks failed for gm6bkj9d99rxm.
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)
"PART_MFG_BINARY"."TYPE"=:B1 AND "PART_MFG_BINARY"."PART_ID"=:B2
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "PART_MFG_BINARY"."TYPE"=:B1 AND "PART_MFG_BINARY"."PART_ID"=:B2

apadrv-start sqlid=17985532525431545779
  :
    call(in-use=456, alloc=16360), compile(in-use=61748, alloc=65000), execution(in-use=67844, alloc=69396)

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=01 mxl=2000(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0020 frm=01 csi=178 siz=2000 off=0
  No bind buffers allocated
 Bind#1
  oacdty=01 mxl=2000(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0020 frm=01 csi=178 siz=2000 off=0
  No bind buffers allocated

kkoqbc: optimizing query block SEL$1 (#0)

        :
    call(in-use=456, alloc=16360), compile(in-use=62388, alloc=65000), execution(in-use=67844, alloc=69396)

kkoqbc-subheap (create addr=0x0FC5BDB0)
****************
QUERY BLOCK TEXT
****************
SELECT BITS FROM PART_MFG_BINARY  where TYPE = :1       and PART_ID = :2                          
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=68499 hint_alias="PART_MFG_BINARY"@"SEL$1"

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEED: 1416 millions instructions/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: PART_MFG_BINARY  Alias: PART_MFG_BINARY
    #Rows: 0  #Blks:  1  AvgRowLen:  0.00
Index Stats::
  Index: SYS_C0012623  Col#: 1 2
    LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
  Index: SYS_IL0000068499C00003$$  Col#:    (NOT ANALYZED)
    LVLS: 1  #LB: 25  #DK: 100  LB/K: 1.00  DB/K: 1.00  CLUF: 800.00
***************************************
1-ROW TABLES:  PART_MFG_BINARY[PART_MFG_BINARY]#0
Access path analysis for PART_MFG_BINARY
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for PART_MFG_BINARY[PART_MFG_BINARY]
=====================
PARSING IN CURSOR #7 len=210 dep=1 uid=0 oct=3 lid=0 tim=519852188056 hv=864012087 ad='521c267c' sqlid='96g93hntrzjtr'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where

obj#=:1 and intcol#=:2
END OF STMT
PARSE #7:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=519852188053
BINDS #7:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=10e3cfdc  bln=22  avl=04  flg=05
  value=68499
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=10e3cfb8  bln=24  avl=02  flg=05
  value=2
EXEC #7:c=0,e=150,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=519852188332
FETCH #7:c=0,e=35,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,tim=519852188392
STAT #7 id=1 cnt=1 pid=0 pos=1 obj=411 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=0 pw=0 time=0 us)'
STAT #7 id=2 cnt=1 pid=1 pos=1 obj=413 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)'
BINDS #4:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=10e3cfdc  bln=22  avl=04  flg=05
  value=68499
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=10e3cfb8  bln=24  avl=02  flg=05
  value=1
=====================
PARSING IN CURSOR #4 len=210 dep=1 uid=0 oct=3 lid=0 tim=519852188677 hv=864012087 ad='521c267c' sqlid='96g93hntrzjtr'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where

obj#=:1 and intcol#=:2
END OF STMT
EXEC #4:c=0,e=129,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=519852188675
FETCH #4:c=0,e=17,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,tim=519852188762
  ColGroup (#1, Index) SYS_C0012623
    Col#: 1 2    CorStregth: 0.00
  ColGroup Usage:: PredCnt: 2  Matches Full: #0  Partial:  Sel: 1.0000
  Table: PART_MFG_BINARY  Alias: PART_MFG_BINARY
    Card: Original: 0.000000  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7121
      Resp_io: 2.00  Resp_cpu: 7121
  Access Path: index (UniqueScan)
    Index: SYS_C0012623
    resc_io: 0.00  resc_cpu: 1240
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000
    Cost: 0.00  Resp: 0.00  Degree: 1
  ColGroup Usage:: PredCnt: 2  Matches Full: #0  Partial:  Sel: 1.0000
  ColGroup Usage:: PredCnt: 2  Matches Full: #0  Partial:  Sel: 1.0000
  Access Path: index (AllEqUnique)
    Index: SYS_C0012623
    resc_io: 0.00  resc_cpu: 1240
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000
    Cost: 0.00  Resp: 0.00  Degree: 1
 One row Card: 1.000000
  Best:: AccessPath: IndexUnique
  Index: SYS_C0012623
         Cost: 0.00  Degree: 1  Resp: 0.00  Card: 1.00  Bytes: 0

BINDS #9:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=10e3cfdc  bln=22  avl=04  flg=05
  value=68499
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=10e3cfb8  bln=24  avl=02  flg=05
  value=3
=====================
PARSING IN CURSOR #9 len=210 dep=1 uid=0 oct=3 lid=0 tim=519852189639 hv=864012087 ad='521c267c' sqlid='96g93hntrzjtr'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where

obj#=:1 and intcol#=:2
END OF STMT
EXEC #9:c=0,e=137,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=519852189635
FETCH #9:c=0,e=21,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=3,tim=519852189729
***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  PART_MFG_BINARY[PART_MFG_BINARY]#0
***********************
Best so far:  Table#: 0  cost: 0.0001  card: 1.0000  bytes: 2022
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************
Or-Expansion validity checks failed on query block SEL$1 (#0) because no need for OR expansion if we only have 1-row tables
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="PART_MFG_BINARY"."PART_ID"=:B1
id=0 frofkks[i] (index start key) predicate="PART_MFG_BINARY"."TYPE"=:B1
id=0 frofkke[i] (index stop key) predicate="PART_MFG_BINARY"."PART_ID"=:B1
id=0 frofkke[i] (index stop key) predicate="PART_MFG_BINARY"."TYPE"=:B1
Final cost for query block SEL$1 (#0) - All Rows Plan:
  Best join order: 1
  Cost: 0.0001  Degree: 1  Card: 1.0000  Bytes: 2022
  Resc: 0.0001  Resc_io: 0.0000  Resc_cpu: 1240
  Resp: 0.0001  Resp_io: 0.0000  Resc_cpu: 1240
kkoqbc-subheap (delete addr=0x0FC5BDB0, in-use=10612, alloc=12148)
kkoqbc-end:
        :
    call(in-use=6600, alloc=32736), compile(in-use=68440, alloc=69056), execution(in-use=72232, alloc=73472)

kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
          :
    call(in-use=6600, alloc=32736), compile(in-use=69096, alloc=73112), execution(in-use=76308, alloc=77548)

Starting SQL statement dump

user_id=59 user_name=TESTUSER module=PRTMNT.EXE action=
sql_id=gm6bkj9d99rxm plan_hash_value=1612811168 problem_type=3
----- Current SQL Statement for this session (sql_id=gm6bkj9d99rxm) -----
SELECT BITS FROM PART_MFG_BINARY  where TYPE = :1       and PART_ID = :2                          
sql_text_length=100
sql=SELECT BITS FROM PART_MFG_BINARY  where TYPE = :1       and PART_ID = :2                          
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
------------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name           | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |                |       |       |     1 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | PART_MFG_BINARY|     1 |  2022 |     0 |           |
| 2   |   INDEX UNIQUE SCAN          | SYS_C0012623   |     1 |       |     0 |           |
------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("PART_ID"=:2 AND "TYPE"=:1)

Content of other_xml column
===========================
  db_version     : 11.1.0.6
  parse_schema   : TESTUSER
  plan_hash      : 1612811168
  plan_hash_2    : 424781238
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      DB_VERSION('11.1.0.6')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "PART_MFG_BINARY"@"SEL$1" ("PART_MFG_BINARY"."PART_ID" "PART_MFG_BINARY"."TYPE"))
    END_OUTLINE_DATA
  */

Query Block Registry:
SEL$1 0x4220f70c (PARSER) [FINAL]

:
    call(in-use=8828, alloc=32736), compile(in-use=89856, alloc=187352), execution(in-use=194096, alloc=196424)

End of Optimizer State Dump
====================== END SQL Statement Dump ======================
XCTEND rlbk=1, rd_only=1

A portion of a SQL*Net trace captured when the client ERP program is connected to Oracle 10.2.0.2 follows:

-----------------------------------------------------------------------------
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
nsdofls: entry
nsdofls: DATA flags: 0x0
nsdofls: sending NSPTDA packet
nspsend: entry
nspsend: plen=276, type=6
nttwr: entry
nttwr: socket 340 had bytes written=276
nttwr: exit
nspsend: packet dump
nspsend: 01 14 00 00 06 00 00 00  |........|
nspsend: 00 00 03 5E 85 09 80 02  |...^....|
nspsend: 00 02 00 00 00 01 63 00  |......c.|
nspsend: 00 00 01 0D 00 00 00 00  |........|
nspsend: 01 00 00 00 00 01 00 00  |........|
nspsend: 00 00 00 00 00 01 02 00  |........|
nspsend: 00 00 00 00 00 00 01 01  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 01 63 53 45 4C 45 43  |..cSELEC|
nspsend: 54 20 42 49 54 53 20 46  |T.BITS.F|
nspsend: 52 4F 4D 20 50 41 52 54  |ROM.PART|
nspsend: 5F 4D 46 47 5F 42 49 4E  |_MFG_BIN|
nspsend: 41 52 59 20 20 77 68 65  |ARY..whe|
nspsend: 72 65 20 54 59 50 45 20  |re.TYPE.|
nspsend: 3D 20 3A 31 20 20 20 20  |=.:1....|
nspsend: 20 20 20 61 6E 64 20 50  |...and.P|
nspsend: 41 52 54 5F 49 44 20 3D  |ART_ID.=|
nspsend: 20 3A 32 20 20 20 20 20  |.:2.....|
nspsend: 20 20 20 20 20 20 20 20  |........|
nspsend: 20 20 20 20 20 20 20 20  |........|
nspsend: 20 20 20 20 20 20 01 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 01 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 01 80 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 01  |........|
nspsend: 80 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00              |....    |
nspsend: 276 bytes to transport
nspsend: normal exit
nsdofls: exit (0)
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nsdo: entry
nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: switching to application buffer
nsrdr: entry
nsrdr: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 340 had bytes read=223
nttrd: exit
nsprecv: 223 bytes from transport
nsprecv: tlen=223, plen=223, type=6
nsprecv: packet dump
nsprecv: 00 DF 00 00 06 00 00 00  |........|
nsprecv: 00 00 10 17 34 44 80 BB  |....4D..|
nsprecv: 49 5F 2C 75 8A 72 99 F9  |I_,u.r..|
nsprecv: B3 DF 94 5A 78 6C 0B 18  |...Zxl..|
nsprecv: 08 30 12 00 00 00 00 01  |.0......|
nsprecv: 00 00 00 4D 71 00 00 00  |...Mq...|
nsprecv: A0 0F 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 01 04 04 00 00 00 04  |........|
nsprecv: 42 49 54 53 00 00 00 00  |BITS....|
nsprecv: 00 00 00 00 00 00 07 00  |........|
nsprecv: 00 00 07 78 6C 0B 18 0C  |...xl...|
nsprecv: 17 2C 01 00 00 00 E8 1F  |.,......|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 08 06 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 02 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 04 01 00  |........|
nsprecv: 00 00 83 01 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 02 00  |........|
nsprecv: 11 00 03 00 00 00 00 00  |........|
nsprecv: C3 88 01 00 04 00 00 0D  |........|
nsprecv: EA 0A 00 0E 00 00 00 00  |........|
nsprecv: 00 00 85 00 00 01 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00     |....... |
nsprecv: normal exit
nsrdr: got NSPTDA packet
nsrdr: NSPTDA flags: 0x0
nsrdr: normal exit
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: *what=1, *bl=2001
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nioqrc: exit
nioqsn: entry
nioqsn: exit
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
nsdofls: entry
nsdofls: DATA flags: 0x0
nsdofls: sending NSPTDA packet
nspsend: entry
nspsend: plen=218, type=6
nttwr: entry
nttwr: socket 340 had bytes written=218
nttwr: exit
nspsend: packet dump
nspsend: 00 DA 00 00 06 00 00 00  |........|
nspsend: 00 00 03 5E 86 78 80 00  |...^.x..|
nspsend: 00 02 00 00 00 00 00 00  |........|
nspsend: 00 00 01 0D 00 00 00 00  |........|
nspsend: 01 00 00 00 00 01 00 00  |........|
nspsend: 00 14 00 00 00 01 02 00  |........|
nspsend: 00 00 00 00 00 00 01 01  |........|
nspsend: 01 00 00 00 00 00 00 00  |........|
nspsend: 00 01 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 01 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 60 01  |......`.|
nspsend: 00 00 01 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 B2 00 01 00  |........|
nspsend: 00 00 00 60 01 00 00 09  |...`....|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 B2 00 01 00 00 00 00  |........|
nspsend: 71 05 00 00 14 00 00 00  |q.......|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 07 01 44  |.......D|
nspsend: 09 30 31 35 34 37 30 30  |.1856710|
nspsend: 39 4D                    |9M      |
nspsend: 218 bytes to transport
nspsend: normal exit
nsdofls: exit (0)
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nsdo: entry
nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: switching to application buffer
nsrdr: entry
nsrdr: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 340 had bytes read=223
nttrd: exit
nsprecv: 223 bytes from transport
nsprecv: tlen=223, plen=223, type=6
nsprecv: packet dump
nsprecv: 00 DF 00 00 06 00 00 00  |........|
nsprecv: 00 00 10 17 34 44 80 BB  |....4D..|
nsprecv: 49 5F 2C 75 8A 72 99 F9  |I_,u.r..|
nsprecv: B3 DF 94 5A 78 6C 0B 18  |...Zxl..|
nsprecv: 08 30 12 00 00 00 00 01  |.0......|
nsprecv: 00 00 00 4D 71 00 00 00  |...Mq...|
nsprecv: A0 0F 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 01 04 04 00 00 00 04  |........|
nsprecv: 42 49 54 53 00 00 00 00  |BITS....|
nsprecv: 00 00 00 00 00 00 07 00  |........|
nsprecv: 00 00 07 78 6C 0B 18 0C  |...xl...|
nsprecv: 17 2C 01 00 00 00 E8 1F  |.,......|
nsprecv: 00 00 02 00 00 00 02 00  |........|
nsprecv: 00 00 08 06 00 B5 D0 3D  |.......=|
nsprecv: 47 00 00 00 00 02 00 00  |G.......|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 04 01 00  |........|
nsprecv: 00 00 84 01 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 02 00  |........|
nsprecv: 00 00 03 00 00 00 00 00  |........|
nsprecv: C3 88 01 00 04 00 00 0D  |........|
nsprecv: EA 0A 00 0E 00 00 00 00  |........|
nsprecv: 00 00 86 00 00 01 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00     |....... |
nsprecv: normal exit
nsrdr: got NSPTDA packet
nsrdr: NSPTDA flags: 0x0
nsrdr: normal exit
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: *what=1, *bl=2001
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nioqrc: exit
nioqsn: entry
nioqsn: exit
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
nsdofls: entry
nsdofls: DATA flags: 0x0
nsdofls: sending NSPTDA packet
nspsend: entry
nspsend: plen=21, type=6
nttwr: entry
nttwr: socket 340 had bytes written=21
nttwr: exit
nspsend: packet dump
nspsend: 00 15 00 00 06 00 00 00  |........|
nspsend: 00 00 03 05 87 02 00 00  |........|
nspsend: 00 01 00 00 00           |.....   |
nspsend: 21 bytes to transport
nspsend: normal exit
nsdofls: exit (0)
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nsdo: entry
nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: switching to application buffer
nsrdr: entry
nsrdr: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 340 had bytes read=102
nttrd: exit
nsprecv: 102 bytes from transport
nsprecv: tlen=102, plen=102, type=6
nsprecv: packet dump
nsprecv: 00 66 00 00 06 00 00 00  |.f......|
nsprecv: 00 00 04 01 00 00 00 85  |........|
nsprecv: 01 00 00 00 00 7B 05 00  |.....{..|
nsprecv: 00 00 00 02 00 00 00 03  |........|
nsprecv: 00 00 00 00 00 C3 88 01  |........|
nsprecv: 00 04 00 00 0D EA 0A 00  |........|
nsprecv: 0E 00 00 00 00 00 00 87  |........|
nsprecv: 00 00 01 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 19 4F 52 41  |.....ORA|
nsprecv: 2D 30 31 34 30 33 3A 20  |-01403:.|
nsprecv: 6E 6F 20 64 61 74 61 20  |no.data.|
nsprecv: 66 6F 75 6E 64 0A        |found.  |
nsprecv: normal exit
nsrdr: got NSPTDA packet
nsrdr: NSPTDA flags: 0x0
nsrdr: normal exit
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: *what=1, *bl=2001
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nioqrc: exit 

A portion of a SQL*Net trace captured when the client ERP program (Oracle 10.2.0.1 Client) is connected to Oracle 11.1.0.6 follows:

-----------------------------------------------------------------------------
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
nsdofls: entry
nsdofls: DATA flags: 0x0
nsdofls: sending NSPTDA packet
nspsend: entry
nspsend: plen=177, type=6
nttwr: entry
nttwr: socket 308 had bytes written=177
nttwr: exit
nspsend: packet dump
nspsend: 00 B1 00 00 06 00 00 00  |........|
nspsend: 00 00 03 4A FE 01 00 00  |...J....|
nspsend: 00 03 00 00 00 78 14 FD  |.....x..|
nspsend: 02 63 00 00 00 00 00 00  |.c......|
nspsend: 00 00 00 00 00 48 D8 12  |.....H..|
nspsend: 00 01 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 63 53 45 4C 45 43 54  |.cSELECT|
nspsend: 20 42 49 54 53 20 46 52  |.BITS.FR|
nspsend: 4F 4D 20 50 41 52 54 5F  |OM.PART_|
nspsend: 4D 46 47 5F 42 49 4E 41  |MFG_BINA|
nspsend: 52 59 20 20 77 68 65 72  |RY..wher|
nspsend: 65 20 54 59 50 45 20 3D  |e.TYPE.=|
nspsend: 20 3A 31 20 20 20 20 20  |.:1.....|
nspsend: 20 20 61 6E 64 20 50 41  |..and.PA|
nspsend: 52 54 5F 49 44 20 3D 20  |RT_ID.=.|
nspsend: 3A 32 20 20 20 20 20 20  |:2......|
nspsend: 20 20 20 20 20 20 20 20  |........|
nspsend: 20 20 20 20 20 20 20 20  |........|
nspsend: 20 20 20 20 20 02 00 00  |........|
nspsend: 00                       |.       |
nspsend: 177 bytes to transport
nspsend: normal exit
nsdofls: exit (0)
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nsdo: entry
nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: switching to application buffer
nsrdr: entry
nsrdr: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 308 had bytes read=106
nttrd: exit
nsprecv: 106 bytes from transport
nsprecv: tlen=106, plen=106, type=6
nsprecv: packet dump
nsprecv: 00 6A 00 00 06 00 00 00  |.j......|
nsprecv: 00 00 04 05 00 00 00 FC  |........|
nsprecv: 01 01 01 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 03 00 00 00  |........|
nsprecv: 03 00 00 00 00 00 30 0A  |......0.|
nsprecv: 01 00 05 00 00 00 86 2A  |.......*|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 FE 00 00 01 00  |........|
nsprecv: 00 00 36 01 00 00 00 00  |..6.....|
nsprecv: 00 00 58 BF 0C 0E 00 00  |..X.....|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00                    |..      |
nsprecv: normal exit
nsrdr: got NSPTDA packet
nsrdr: NSPTDA flags: 0x0
nsrdr: normal exit
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: *what=1, *bl=2001
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nioqrc: exit
nioqsn: entry
nioqsn: exit
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
nsdofls: entry
nsdofls: DATA flags: 0x0
nsdofls: sending NSPTDA packet
nspsend: entry
nspsend: plen=49, type=6
nttwr: entry
nttwr: socket 308 had bytes written=49
nttwr: exit
nspsend: packet dump
nspsend: 00 31 00 00 06 00 00 00  |.1......|
nspsend: 00 00 03 2B FF 03 00 00  |...+....|
nspsend: 00 01 00 00 00 90 DA C2  |........|
nspsend: 01 60 A2 C2 01 20 00 00  |.`......|
nspsend: 00 92 DA C2 01 94 DA C2  |........|
nspsend: 01 C0 03 00 00 54 DE C2  |.....T..|
nspsend: 01                       |.       |
nspsend: 49 bytes to transport
nspsend: normal exit
nsdofls: exit (0)
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nsdo: entry
nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: switching to application buffer
nsrdr: entry
nsrdr: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 308 had bytes read=79
nttrd: exit
nsprecv: 79 bytes from transport
nsprecv: tlen=79, plen=79, type=6
nsprecv: packet dump
nsprecv: 00 4F 00 00 06 00 00 00  |.O......|
nsprecv: 00 00 08 01 00 01 00 01  |........|
nsprecv: 71 00 00 00 A0 0F 00 00  |q.......|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 01 04 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 05 00 05 42 49 54 53 22  |...BITS"|
nsprecv: 09 05 00 00 00 FD 01     |....... |
nsprecv: normal exit
nsrdr: got NSPTDA packet
nsrdr: NSPTDA flags: 0x0
nsrdr: normal exit
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: *what=1, *bl=2001
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nioqrc: exit
nioqsn: entry
nioqsn: exit
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
nsdofls: entry
nsdofls: DATA flags: 0x0
nsdofls: sending NSPTDA packet
nspsend: entry
nspsend: plen=33, type=6
nttwr: entry
nttwr: socket 308 had bytes written=33
nttwr: exit
nspsend: packet dump
nspsend: 00 21 00 00 06 00 00 00  |.!......|
nspsend: 00 00 03 15 00 D5 07 00  |........|
nspsend: 00 00 00 00 00 EB 8B DB  |........|
nspsend: 00 C8 00 00 00 48 D8 12  |.....H..|
nspsend: 00                       |.       |
nspsend: 33 bytes to transport
nspsend: normal exit
nsdofls: exit (0)
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nsdo: entry
nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: switching to application buffer
nsrdr: entry
nsrdr: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 308 had bytes read=96
nttrd: exit
nsprecv: 96 bytes from transport
nsprecv: tlen=96, plen=96, type=6
nsprecv: packet dump
nsprecv: 00 60 00 00 06 00 00 00  |.`......|
nsprecv: 00 00 08 4B 00 4B 4F 52  |...K.KOR|
nsprecv: 41 2D 30 32 30 30 35 3A  |A-02005:|
nsprecv: 20 69 6D 70 6C 69 63 69  |.implici|
nsprecv: 74 20 28 2D 31 29 20 6C  |t.(-1).l|
nsprecv: 65 6E 67 74 68 20 6E 6F  |ength.no|
nsprecv: 74 20 76 61 6C 69 64 20  |t.valid.|
nsprecv: 66 6F 72 20 74 68 69 73  |for.this|
nsprecv: 20 62 69 6E 64 20 6F 72  |.bind.or|
nsprecv: 20 64 65 66 69 6E 65 20  |.define.|
nsprecv: 64 61 74 61 74 79 70 65  |datatype|
nsprecv: 0A 09 05 00 00 00 FD 01  |........|
nsprecv: normal exit
nsrdr: got NSPTDA packet
nsrdr: NSPTDA flags: 0x0
nsrdr: normal exit
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: *what=1, *bl=2001
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nioqrc: exit
nioqsn: entry
nioqsn: exit
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011 


For comparison, a portion of a SQL*Net Trace at level 16, 10.2.0.1 client -> 11.1.0.6 server, custom app, successful:


-----------------------------------------------------------------------------
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
nsdofls: entry
nsdofls: DATA flags: 0x0
nsdofls: sending NSPTDA packet
nspsend: entry
nspsend: plen=329, type=6
nttwr: entry
nttwr: socket 672 had bytes written=329
nttwr: exit
nspsend: packet dump
nspsend: 01 49 00 00 06 00 00 00  |.I......|
nspsend: 00 00 11 69 0B A8 E8 14  |...i....|
nspsend: 03 01 00 00 00 04 00 00  |........|
nspsend: 00 03 5E 0C 69 80 00 00  |..^.i...|
nspsend: 00 00 00 00 48 E3 14 03  |....H...|
nspsend: 4A 00 00 00 14 AB 14 03  |J.......|
nspsend: 0D 00 00 00 00 00 00 00  |........|
nspsend: 48 AB 14 03 00 00 00 00  |H.......|
nspsend: 64 00 00 00 00 00 00 00  |d.......|
nspsend: DC E3 14 03 02 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 4A AB 14 03 DC E3 14 03  |J.......|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 58 AB 14 03  |....X...|
nspsend: FE 40 53 45 4C 45 43 54  |.@SELECT|
nspsend: 20 42 49 54 53 20 20 46  |.BITS..F|
nspsend: 52 4F 4D 20 50 41 52 54  |ROM.PART|
nspsend: 5F 4D 46 47 5F 42 49 4E  |_MFG_BIN|
nspsend: 41 52 59 20 77 68 65 72  |ARY.wher|
nspsend: 65 20 20 54 59 50 45 20  |e..TYPE.|
nspsend: 3D 20 3A 31 20 20 20 20  |=.:1....|
nspsend: 20 20 20 61 6E 64 20 50  |...and.P|
nspsend: 41 52 0A 54 5F 49 44 20  |AR.T_ID.|
nspsend: 3D 20 3A 32 20 00 01 00  |=.:2....|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 01 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 01 60 00 00 00 02  |...`....|
nspsend: 00 00 00 00 00 00 00 10  |........|
nspsend: 00 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 B2 00 01 00 00  |........|
nspsend: 00 00 00 01 60 00 00 00  |....`...|
nspsend: 12 00 00 00 00 00 00 00  |........|
nspsend: 10 00 00 00 00 00 00 00  |........|
nspsend: 00 00 00 00 B2 00 01 00  |........|
nspsend: 00 00 00 00 07 01 44 09  |......D.|
nspsend: 30 39 35 34 37 30 30 39  |98567109|
nspsend: 4D                       |M       |
nspsend: 329 bytes to transport
nspsend: normal exit
nsdofls: exit (0)
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nsdo: entry
nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: switching to application buffer
nsrdr: entry
nsrdr: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 672 had bytes read=257
nttrd: exit
nsprecv: 257 bytes from transport
nsprecv: tlen=257, plen=257, type=6
nsprecv: packet dump
nsprecv: 01 01 00 00 06 00 00 00  |........|
nsprecv: 00 00 10 17 4E A5 71 9E  |....N.q.|
nsprecv: 5F 80 3E 52 46 CC 9F F4  |_.>RF...|
nsprecv: 96 4C 0C FD 78 6C 0B 18  |.L..xl..|
nsprecv: 0C 31 23 00 00 00 00 01  |.1#.....|
nsprecv: 00 00 00 39 01 71 00 00  |...9.q..|
nsprecv: 00 A0 0F 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 01 04 04  |........|
nsprecv: 00 00 00 04 42 49 54 53  |....BITS|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 07 00 00 00 07 78  |.......x|
nsprecv: 6C 0B 18 0C 31 23 00 00  |l...1#..|
nsprecv: 00 00 E8 1F 00 00 02 00  |........|
nsprecv: 00 00 02 00 00 00 08 06  |........|
nsprecv: 00 15 90 51 47 00 00 00  |...QG...|
nsprecv: 00 03 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 04 01 00 00 00 0A 00  |........|
nsprecv: 01 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 03 00 12 00 03  |........|
nsprecv: 00 00 08 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 0C 00 00 01 00 00  |........|
nsprecv: 00 36 01 00 00 00 00 00  |.6......|
nsprecv: 00 10 EE 0D 0E 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00                       |.       |
nsprecv: normal exit
nsrdr: got NSPTDA packet
nsrdr: NSPTDA flags: 0x0
nsrdr: normal exit
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: *what=1, *bl=2001
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nioqrc: exit
nioqsn: entry
nioqsn: exit
nioqrc: entry
nsdo: entry
nsdo: cid=0, opcode=84, *bl=0, *what=1, uflgs=0x20, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
nsdofls: entry
nsdofls: DATA flags: 0x0
nsdofls: sending NSPTDA packet
nspsend: entry
nspsend: plen=21, type=6
nttwr: entry
nttwr: socket 672 had bytes written=21
nttwr: exit
nspsend: packet dump
nspsend: 00 15 00 00 06 00 00 00  |........|
nspsend: 00 00 03 05 0D 03 00 00  |........|
nspsend: 00 64 00 00 00           |.d...   |
nspsend: 21 bytes to transport
nspsend: normal exit
nsdofls: exit (0)
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nsdo: entry
nsdo: cid=0, opcode=85, *bl=0, *what=0, uflgs=0x0, cflgs=0x3
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: rank=64, nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: nsctx: state=8, flg=0x400d, mvd=0
nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: switching to application buffer
nsrdr: entry
nsrdr: recving a packet
nsprecv: entry
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 672 had bytes read=132
nttrd: exit
nsprecv: 132 bytes from transport
nsprecv: tlen=132, plen=132, type=6
nsprecv: packet dump
nsprecv: 00 84 00 00 06 00 00 00  |........|
nsprecv: 00 00 04 01 00 00 00 0B  |........|
nsprecv: 00 01 00 00 00 00 7B 05  |......{.|
nsprecv: 00 00 00 00 03 00 00 00  |........|
nsprecv: 03 00 20 08 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 0D 00 00 01 00  |........|
nsprecv: 00 00 36 01 00 00 00 00  |..6.....|
nsprecv: 00 00 10 EE 0D 0E 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 00 00 00 00 00 00  |........|
nsprecv: 00 00 19 4F 52 41 2D 30  |...ORA-0|
nsprecv: 31 34 30 33 3A 20 6E 6F  |1403:.no|
nsprecv: 20 64 61 74 61 20 66 6F  |.data.fo|
nsprecv: 75 6E 64 0A              |und.    |
nsprecv: normal exit
nsrdr: got NSPTDA packet
nsrdr: NSPTDA flags: 0x0
nsrdr: normal exit
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: *what=1, *bl=2001
snsbitts_ts: entry
snsbitts_ts: acquired the bit
snsbitts_ts: normal exit
nsdo: nsctxrnk=0
snsbitcl_ts: entry
snsbitcl_ts: normal exit
nsdo: normal exit
nioqrc: exit


Experiments:


    

  • Oracle 10.2.0.1 client, Oracle 11.1.0.7 client, Oracle 11.1.0.6 client – Same issue with all when connecting to Oracle 11.1.0.6/7 database.
    • 

  • Setting optimizer_features_enable to 10.2.0.1 and 10.1.0.4 – same issue with both when connecting to Oracle 11.1.0.6/7 database.
    • 

  • Compared the Oracle parameters including the hidden (underscore) parameters between 10.2.0.2 and 11.1.0.7 and adjusted a couple that seemed as though they would be possible causes:
    
— _OPTIMIZER_ADAPTIVE_CURSOR_SHARING=FALSE
    
— _OPTIMIZER_COMPLEX_PRED_SELECTIVITY=FALSE
    
— _OPTIMIZER_EXTENDED_CURSOR_SHARING=NONE
    
— etc.
    • 



The end result of all of the experiments was exactly the same – any query from the ERP program that accessed a table with a BLOB column triggered the  “ORA-02005: implicit (-1)  length not valid for this bind or define datatype” error.


So, what would you do to troubleshoot this problem?  Remember, the old version of the ERP system that used the LONG RAW datatype worked fine with Oracle 11.1.0.6, while the new version that uses the BLOB datatype fails to work on Oracle 11.1.0.6, 11.1.0.7, and 11.2.0.1.

Comments : Leave a Comment »

Categories : General Administration