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=2011For 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: exitExperiments:
- 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.
Recent Comments