Architecture andPerformance Tuning

Tuesday, 8 Jun 2010 5:35 PM

Query tuning

By niket

Optimizer is not using index when i am giving hint to the query which contains bind variables

It is generating its own plan

The query is

^?Download text

1 2 3 4 5 6 7 8 9 10

SELECT b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no, b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date, b.charge_code, b.charge_type, price_inclusive_of_taxes, b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = 'N' AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no);

Now execution plan is
============

^?Download text

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

Plan Table ============ ----------------------------------------------------------------+-------------------------------- ---+ | Id | Operation | Name | Rows | Bytes | Cost | Time | ----------------------------------------------------------------+-------------------------------- ---+ | 0 | SELECT STATEMENT | | | | 539 | | | 1 | CONCATENATION | | | | | | | 2 | FILTER | | | | | | | 3 | TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES | 1 | 88 | 269 | 00:00: 04 | | 4 | INDEX RANGE SCAN | IDX_BT_BILLING_ENTITYID| 807 | | 8 | 00:00: 01 | | 5 | FILTER | | | | | | | 6 | TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES | 1 | 88 | 269 | 00:00: 04 | | 7 | INDEX RANGE SCAN | IDX_BT_BILLING_ENTITYID| 807 | | 8 | 00:00: 01 | ----------------------------------------------------------------+-------------------------------- ---+ Predicate Information: ---------------------- 2 - filter(:B1 IS NULL) 3 - filter((NVL("B"."INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND "B"."PAYMODE_TRANS_NO" IS NOT NULL AND "B"."CP_CODE"=NVL(:B2,"B"."CP_CODE"))) 4 - access("B"."ENTITY_ID"=TO_NUMBER(:B3)) 5 - filter(:B1 IS NOT NULL) 6 - filter((NVL("B"."INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND "B"."CP_CODE"=NVL(:B2,"B"."CP _CODE") AND "B"."PAYMODE_TRANS_NO"=:B1)) 7 - access("B"."ENTITY_ID"=TO_NUMBER(:B3))

Now i am passing hint to the query

^?Download text

1 2 3 4 5 6 7 8 9 10

SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no, b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date, b.charge_code, b.charge_type, price_inclusive_of_taxes, b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = 'N' AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)

But still it is using same plan.

Now when i removed bind variables with values then it is using index

^?Download text

1 2 3 4 5

SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTER NAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B .CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM IMCL420.BT_BILLI NG_CHARGES B WHERE B.ENTITY_ID=12345263 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS='N' AND B.CP_ CODE=NVL(0,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(0,B.PAYMODE_TRANS_NO)

and exec plan is

^?Download text

1 2 3 4 5 6 7 8 9 10 11 12 13

Plan Table ============ -------------------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 5 | | | 1 | INDEX RANGE SCAN | BT_BILLING_CHARGES_I4| 1 | 88 | 5 | 00:00:01 | -------------------------------------------------+-----------------------------------+ Predicate Information: ---------------------- 1 - access("B"."ENTITY_ID"=12345263 AND NVL("INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N') 1 - filter((NVL("INVOICE_NO",0)=0 AND "B"."PAID_STATUS"='N' AND TO_NUMBER("B"."CP_CODE")=NVL(0,TO _NUMBER("B"."CP_CODE")) AND "B"."PAYMODE_TRANS_NO"=NVL(0,"B"."PAYMODE_TRANS_NO")))

so my question is how to force query containing bind variables to use index

Kindly guide me

Thanks

Regards

Niket

Popularity: 16% [?]

9 Responses to “Query tuning”

1. Ramesh Menon Says:
   June 8th, 2010 at 9:25 pm

   Niket,

   I wont be able to give you a firm answer with just the information you have provided. However, kindly look into the following

   1. Flush your shared pool after providing a hint
   2. Generate an optimizer trace …10053 and analyze (upload here if possible)

   Use the following syntax

   ^?Download text

   1 2 3 4 5

   SQL> EXEC SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053, (1 | 2),'')   OR   SQL> Alter session set events'10053 trace name context forever [, level (1 / 2)] ';

   3. Check if you have histograms collected for this column. This could be a bind peeking issue
2. niket Says:
   June 9th, 2010 at 11:38 am

   Dear Ramesh Sir,

   I have used the event 10053 and output for hinted index query with bind variables are as follows.

   a) /home/oracle/admin/IMCLDB/udump/imcldb_ora_19027.trc
   Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
   With the Partitioning, OLAP and Data Mining options
   ORACLE_HOME = /home/oracle/product/10.2
   System name: Linux
   Node name: localhost.localdomain
   Release: 2.6.9-42.ELsmp
   Version: #1 SMP Tue Aug 15 10:35:26 BST 2006
   Machine: x86_64
   Instance name: IMCLDB
   Redo thread mounted by this instance: 1
   Oracle process number: 30
   Unix process pid: 19027, image: oracle@localhost.localdomain (TNS V1-V3)

   *** 2010-06-03 07:09:11.492
   *** ACTION NAME:() 2010-06-03 07:09:11.433
   *** MODULE NAME:(SQL*Plus) 2010-06-03 07:09:11.433
   *** SERVICE NAME:(SYS$USERS) 2010-06-03 07:09:11.433
   *** SESSION ID:(410.477) 2010-06-03 07:09:11.433
   KGX cleanup…
   KGX Atomic Operation Log 0xfdc51e18
   Mutex 0x1072b2c80(410, 0) idn 0 oper EXAM
   Cursor Parent uid 410 efd 5 whr 26 slp 0
   oper=DEFAULT pt1=0xfcc60598 pt2=0xfcc601b8 pt3=0xfcc60ca0
   pt4=(nil) u41=1 stt=0
   Registered qb: SEL$1 0xff856ed8 (PARSER)
   signature (): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=4 objn=32031 hint_alias=”B”@”SEL$1″
   **************************
   Predicate Move-Around (PM)
   **************************
   PM: Considering predicate move-around in SEL$1 (#0).
   PM: Checking validity of predicate move-around in SEL$1 (#0).
   PM: PM bypassed: Predicate move-around disabled due to star transformation.
   CBQT: Validity checks failed for 65gpmcavg2x91.
   CVM: Considering view merge in query block SEL$1 (#0)
   CBQT: Validity checks failed for 65gpmcavg2x91.
   ***************
   Subquery Unnest
   ***************
   SU: Considering subquery unnesting in query block SEL$1 (#0)
   *************************
   Set-Join Conversion (SJC)
   *************************
   SJC: Considering set-join conversion in SEL$1 (#0).
   **************************
   Predicate Move-Around (PM)
   **************************
   PM: Considering predicate move-around in SEL$1 (#0).
   PM: Checking validity of predicate move-around in SEL$1 (#0).
   PM: PM bypassed: Predicate move-around disabled due to star transformation.
   FPD: Considering simple filter push in SEL$1 (#0)
   FPD: Current where clause predicates in SEL$1 (#0) :
   “B”.”ENTITY_ID”=TO_NUMBER(:B1) AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B3,”B”.”PAYMODE_TRANS_NO”)
   kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
   predicates with check contraints: “B”.”ENTITY_ID”=TO_NUMBER(:B1) AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B3,”B”.”PAYMODE_TRANS_NO”)
   after transitive predicate generation: “B”.”ENTITY_ID”=TO_NUMBER(:B1) AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B3,”B”.”PAYMODE_TRANS_NO”)
   finally: “B”.”ENTITY_ID”=TO_NUMBER(:B1) AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B3,”B”.”PAYMODE_TRANS_NO”)
   apadrv-start: call(in-use=720, alloc=16344), compile(in-use=40688, alloc=44568)
   kkoqbc-start
   : call(in-use=720, alloc=16344), compile(in-use=41536, alloc=44568)
   ******************************************
   Current SQL statement for this session:
   explain plan for SELECT /*+ INDEX(b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,b.charge_code, b.charge_type, price_inclusive_of_taxes,b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = ‘N’ AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)
   *******************************************
   Legend
   The following abbreviations are used by optimizer trace.
   CBQT – cost-based query transformation
   JPPD – join predicate push-down
   FPD – filter push-down
   PM – predicate move-around
   CVM – complex view merging
   SPJ – select-project-join
   SJC – set join conversion
   SU – subquery unnesting
   OBYE – order by elimination
   ST – star transformation
   qb – query block
   LB – leaf blocks
   DK – distinct keys
   LB/K – average number of leaf blocks per key
   DB/K – average number of data blocks per key
   CLUF – clustering factor
   NDV – number of distinct values
   Resp – response cost
   Card – cardinality
   Resc – resource cost
   NL – nested loops (join)
   SM – sort merge (join)
   HA – hash (join)
   CPUCSPEED – CPU Speed
   IOTFRSPEED – I/O transfer speed
   IOSEEKTIM – I/O seek time
   SREADTIM – average single block read time
   MREADTIM – average multiblock read time
   MBRC – average multiblock read count
   MAXTHR – maximum I/O system throughput
   SLAVETHR – average slave I/O throughput
   dmeth – distribution method
   1: no partitioning required
   2: value partitioned
   4: right is random (round-robin)
   512: left is random (round-robin)
   8: broadcast right and partition left
   16: broadcast left and partition right
   32: partition left using partitioning of right
   64: partition right using partitioning of left
   128: use hash partitioning dimension
   256: use range partitioning dimension
   2048: use list partitioning dimension
   1024: run the join in serial
   0: invalid distribution method
   sel – selectivity
   ptn – partition
   *******************************************
   Peeked values of the binds in SQL statement
   *******************************************
   kkscoacd
   Bind#0
   oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
   oacflg=00 fl2=1010000 frm=01 csi=31 siz=96 off=0
   kxsbbbfp=2a978a1f80 bln=32 avl=00 flg=05
   Bind#1
   oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
   oacflg=00 fl2=1010000 frm=01 csi=31 siz=0 off=32
   kxsbbbfp=2a978a1fa0 bln=32 avl=00 flg=01
   Bind#2
   oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
   oacflg=00 fl2=1010000 frm=01 csi=31 siz=0 off=64
   kxsbbbfp=2a978a1fc0 bln=32 avl=00 flg=01
   ***************************************
   PARAMETERS USED BY THE OPTIMIZER
   ********************************
   *************************************
   PARAMETERS WITH ALTERED VALUES
   ******************************
   hash_area_size = 10485760
   sort_area_size = 10485760
   _pga_max_size = 4194304 KB
   star_transformation_enabled = true
   *********************************
   Bug Fix Control Environment
   ***************************
   fix 4611850 = enabled
   fix 4663804 = enabled
   fix 4663698 = enabled
   fix 4545833 = enabled
   fix 3499674 = disabled
   fix 4584065 = enabled
   fix 4602374 = enabled
   fix 4569940 = enabled
   fix 4631959 = enabled
   fix 4519340 = enabled
   fix 4550003 = enabled
   fix 4488689 = enabled
   fix 3118776 = enabled
   fix 4519016 = enabled
   fix 4487253 = enabled
   fix 4556762 = 15
   fix 4728348 = enabled
   fix 4723244 = enabled
   fix 4554846 = enabled
   fix 4175830 = enabled
   fix 4722900 = enabled
   fix 5094217 = enabled
   fix 4904890 = enabled
   fix 4483286 = disabled
   fix 4969880 = disabled
   fix 4711525 = enabled
   fix 4717546 = enabled
   fix 4904838 = enabled
   fix 5005866 = enabled
   fix 4600710 = enabled
   fix 5129233 = enabled
   fix 5195882 = enabled
   fix 5084239 = enabled
   fix 4595987 = enabled
   fix 4134994 = enabled
   fix 5104624 = enabled
   fix 4908162 = enabled
   fix 5015557 = enabled
   *************************************
   PARAMETERS WITH DEFAULT VALUES
   ******************************
   optimizer_mode_hinted = false
   optimizer_features_hinted = 0.0.0
   parallel_execution_enabled = true
   parallel_query_forced_dop = 0
   parallel_dml_forced_dop = 0
   parallel_ddl_forced_degree = 0
   parallel_ddl_forced_instances = 0
   _query_rewrite_fudge = 90
   optimizer_features_enable = 10.2.0.3
   _optimizer_search_limit = 5
   cpu_count = 2
   active_instance_count = 1
   parallel_threads_per_cpu = 2
   bitmap_merge_area_size = 1048576
   sort_area_retained_size = 0
   _sort_elimination_cost_ratio = 0
   _optimizer_block_size = 4096
   _sort_multiblock_read_count = 2
   _hash_multiblock_io_count = 0
   _db_file_optimizer_read_count = 256
   _optimizer_max_permutations = 2000
   pga_aggregate_target = 3145728 KB
   _query_rewrite_maxdisjunct = 257
   _smm_auto_min_io_size = 60 KB
   _smm_auto_max_io_size = 252 KB
   _smm_min_size = 1024 KB
   _smm_max_size = 629145 KB
   _smm_px_max_size = 1572864 KB
   _cpu_to_io = 0
   _optimizer_undo_cost_change = 10.2.0.3
   parallel_query_mode = enabled
   parallel_dml_mode = disabled
   parallel_ddl_mode = enabled
   optimizer_mode = all_rows
   sqlstat_enabled = false
   _optimizer_percent_parallel = 101
   _always_anti_join = choose
   _always_semi_join = choose
   _optimizer_mode_force = true
   _partition_view_enabled = true
   _always_star_transformation = false
   _query_rewrite_or_error = false
   _hash_join_enabled = true
   cursor_sharing = exact
   _b_tree_bitmap_plans = true
   _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
   _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
   _fix_control_key = 0
   _optimizer_skip_scan_guess = false
   _enable_row_shipping = false
   _row_shipping_threshold = 80
   _row_shipping_explain = false
   _optimizer_rownum_bind_default = 10
   _first_k_rows_dynamic_proration = true
   _optimizer_native_full_outer_join = off
   *********************************
   Bug Fix Control Environment
   ***************************
   fix 4611850 = enabled
   fix 4663804 = enabled
   fix 4663698 = enabled
   fix 4545833 = enabled
   fix 3499674 = disabled
   fix 4584065 = enabled
   fix 4602374 = enabled
   fix 4569940 = enabled
   fix 4631959 = enabled
   fix 4519340 = enabled
   fix 4550003 = enabled
   fix 4488689 = enabled
   fix 3118776 = enabled
   fix 4519016 = enabled
   fix 4487253 = enabled
   fix 4556762 = 15
   fix 4728348 = enabled
   fix 4723244 = enabled
   fix 4554846 = enabled
   fix 4175830 = enabled
   fix 4722900 = enabled
   fix 5094217 = enabled
   fix 4904890 = enabled
   fix 4483286 = disabled
   fix 4969880 = disabled
   fix 4711525 = enabled
   fix 4717546 = enabled
   fix 4904838 = enabled
   fix 5005866 = enabled
   fix 4600710 = enabled
   fix 5129233 = enabled
   fix 5195882 = enabled
   fix 5084239 = enabled
   fix 4595987 = enabled
   fix 4134994 = enabled
   fix 5104624 = enabled
   fix 4908162 = enabled
   fix 5015557 = enabled
   ***************************************
   PARAMETERS IN OPT_PARAM HINT
   ****************************
   ***************************************
   Column Usage Monitoring is ON: tracking level = 1
   ***************************************
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT /*+ INDEX(b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,b.charge_code, b.charge_type, price_inclusive_of_taxes,b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = ‘N’ AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   kkofmx: index filter:NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B1,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B2,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:”B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B1,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B2,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:”B”.”CP_CODE”=NVL(:B1,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B2,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:”B”.”PAYMODE_TRANS_NO”=NVL(:B1,”B”.”PAYMODE_TRANS_NO”)
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Access Path: index (IndexOnly)
   Index: BT_BILLING_CHARGES_I4
   resc_io: 5.00 resc_cpu: 238929
   ix_sel: 1.2497e-05 ix_sel_with_filters: 2.0196e-10
   Cost: 5.03 Resp: 5.03 Degree: 1
   Best:: AccessPath: IndexRange Index: BT_BILLING_CHARGES_I4
   Cost: 5.03 Degree: 1 Resp: 5.03 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 5.0271 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   Transformed predicate “B”.”PAYMODE_TRANS_NO”=NVL(:B1,”B”.”PAYMODE_TRANS_NO”)
   to :B1 IS NOT NULL AND “B”.”PAYMODE_TRANS_NO”=:B2 OR :B3 IS NULL AND “B”.”PAYMODE_TRANS_NO” IS NOT NULL
   ******** Next OR predicate ********
   ******** OR-branching ********
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT /*+ INDEX(b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,b.charge_code, b.charge_type, price_inclusive_of_taxes,b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = ‘N’ AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   ******** OR-branching ********
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT /*+ INDEX(b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,b.charge_code, b.charge_type, price_inclusive_of_taxes,b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = ‘N’ AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   ******** Same chain after expansion ********
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT /*+ INDEX(b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,b.charge_code, b.charge_type, price_inclusive_of_taxes,b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = ‘N’ AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT /*+ INDEX(b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,b.charge_code, b.charge_type, price_inclusive_of_taxes,b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = ‘N’ AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=4 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   ******** Final costing ********
   Registered qb: SEL$1_1 0xff856ed8 (OR EXPANSION SEL$1; SEL$1;
   signature (): qb_name=SEL$1_1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT /*+ INDEX(b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,b.charge_code, b.charge_type, price_inclusive_of_taxes,b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = ‘N’ AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1_1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   Final – All Rows Plan: Best join order: 1
   Cost: 269.2736 Degree: 1 Card: 1.0000 Bytes: 88
   Resc: 269.2736 Resc_io: 269.0000 Resc_cpu: 2987872
   Resp: 269.2736 Resp_io: 269.0000 Resc_cpu: 2987872
   kkoipt: Query block SEL$1_1 (#0)
   Registered qb: SEL$1_2 0xff856ed8 (OR EXPANSION SEL$1)
   signature (): qb_name=SEL$1_2 nbfros=1 flg=0
   fro(0): flg=4 objn=32031 hint_alias=”B”@”SEL$1_2″
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT /*+ INDEX(b BT_BILLING_CHARGES_I4) */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,b.charge_code, b.charge_type, price_inclusive_of_taxes,b.pricing_entity_id, b.party_slaccount FROM bt_billing_charges b WHERE b.entity_id = :b3 AND NVL (b.invoice_no, 0) = 0 AND b.paid_status = ‘N’ AND b.cp_code = NVL (:b2, b.cp_code) AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1_2 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1_2″
   *****************************
   SYSTEM STATISTICS INFORMATION

3. niket Says:
   June 9th, 2010 at 11:43 am

   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Using prorated density: 1.2485e-05 of col #22 as selectivity of out-of-range value pred
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   kkofmx: index filter:NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND TO_NUMBER(“B”.”CP_CODE”)=NVL(0,TO_NUMBER(“B”.”CP_CODE”)) AND “B”.”PAYMODE_TRANS_NO”=NVL(0,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:”B”.”PAID_STATUS”=’N’ AND TO_NUMBER(“B”.”CP_CODE”)=NVL(0,TO_NUMBER(“B”.”CP_CODE”)) AND “B”.”PAYMODE_TRANS_NO”=NVL(0,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:TO_NUMBER(“B”.”CP_CODE”)=NVL(0,TO_NUMBER(“B”.”CP_CODE”)) AND “B”.”PAYMODE_TRANS_NO”=NVL(0,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:”B”.”PAYMODE_TRANS_NO”=NVL(0,”B”.”PAYMODE_TRANS_NO”)
   Using prorated density: 1.2485e-05 of col #22 as selectivity of out-of-range value pred
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Access Path: index (IndexOnly)
   Index: BT_BILLING_CHARGES_I4
   resc_io: 5.00 resc_cpu: 238929
   ix_sel: 1.2485e-05 ix_sel_with_filters: 2.0175e-10
   Cost: 5.03 Resp: 5.03 Degree: 1
   Best:: AccessPath: IndexRange Index: BT_BILLING_CHARGES_I4
   Cost: 5.03 Degree: 1 Resp: 5.03 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 5.0271 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   Final – All Rows Plan: Best join order: 1
   Cost: 5.0271 Degree: 1 Card: 1.0000 Bytes: 88
   Resc: 5.0271 Resc_io: 5.0000 Resc_cpu: 295432
   Resp: 5.0271 Resp_io: 5.0000 Resc_cpu: 295432
   kkoipt: Query block SEL$1 (#0)
   ******* UNPARSED QUERY IS *******
   SELECT /*+ INDEX (“B” “BT_BILLING_CHARGES_I4″) */ “B”.”AMOUNT” “AMOUNT”,”B”.”INVOICE_CHARGE_PRICE” “INVOICE_CHARGE_PRICE”,”B”.”CHARGE_ID” “CHARGE_ID”,”B”.”INTERNAL_AGREEMENT_NO” “INTERNAL_AGREEMENT_NO”,”B”.”ITEM_CODE” “ITEM_CODE”,”B”.”ENTITY_ID” “ENTITY_ID”,”B”.”AGREEMENT_ID” “AGREEMENT_ID”,”B”.”START_DATE” “START_DATE”,”B”.”END_DATE” “END_DATE”,”B”.”CHARGE_CODE” “CHARGE_CODE”,”B”.”CHARGE_TYPE” “CHARGE_TYPE”,”B”.”PRICE_INCLUSIVE_OF_TAXES” “PRICE_INCLUSIVE_OF_TAXES”,”B”.”PRICING_ENTITY_ID” “PRICING_ENTITY_ID”,”B”.”PARTY_SLACCOUNT” “PARTY_SLACCOUNT” FROM “IMCL420″.”BT_BILLING_CHARGES” “B” WHERE “B”.”ENTITY_ID”=12345263 AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND TO_NUMBER(“B”.”CP_CODE”)=NVL(0,TO_NUMBER(“B”.”CP_CODE”)) AND “B”.”PAYMODE_TRANS_NO”=NVL(0,”B”.”PAYMODE_TRANS_NO”)
   kkoqbc-end
   : call(in-use=141088, alloc=147288), compile(in-use=50296, alloc=52600)
   apadrv-end: call(in-use=141088, alloc=147288), compile(in-use=51080, alloc=52600)

   sql_id=10p1y1658w8ft.
   Current SQL statement for this session:
   explain plan for SELECT /*+ INDEX (b BT_BILLING_CHARGES_I4) */ B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM IMCL420.BT_BILLING_CHARGES B WHERE B.ENTITY_ID=12345263 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(0,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(0,B.PAYMODE_TRANS_NO)

   ============
   Plan Table
   ============
   ————————————————-+———————————–+
   | Id | Operation | Name | Rows | Bytes | Cost | Time |
   ————————————————-+———————————–+
   | 0 | SELECT STATEMENT | | | | 5 | |
   | 1 | INDEX RANGE SCAN | BT_BILLING_CHARGES_I4| 1 | 88 | 5 | 00:00:01 |
   ————————————————-+———————————–+
   Predicate Information:
   ———————-
   1 – access(“B”.”ENTITY_ID”=12345263 AND NVL(“INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N')
   1 – filter((NVL(“INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND TO_NUMBER(“B”.”CP_CODE”)=NVL(0,TO_NUMBER(“B”.”CP_CODE”)) AND “B”.”PAYMODE_TRANS_NO”=NVL(0,”B”.”PAYMODE_TRANS_NO”)))

   Content of other_xml column
   ===========================
   db_version : 10.2.0.3
   parse_schema : SYS
   plan_hash : 3818140540
   Outline Data:
   /*+
   BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE(’10.2.0.3′)
   ALL_ROWS
   OUTLINE_LEAF(@”SEL$1″)
   INDEX(@”SEL$1″ “B”@”SEL$1″ “BT_BILLING_CHARGES_I4″)
   END_OUTLINE_DATA
   */

   Optimizer environment:
   optimizer_mode_hinted = false
   optimizer_features_hinted = 0.0.0
   parallel_execution_enabled = true
   parallel_query_forced_dop = 0
   parallel_dml_forced_dop = 0
   parallel_ddl_forced_degree = 0
   parallel_ddl_forced_instances = 0
   _query_rewrite_fudge = 90
   optimizer_features_enable = 10.2.0.3
   _optimizer_search_limit = 5
   cpu_count = 2
   active_instance_count = 1
   parallel_threads_per_cpu = 2
   hash_area_size = 10485760
   bitmap_merge_area_size = 1048576
   sort_area_size = 10485760
   sort_area_retained_size = 0
   _sort_elimination_cost_ratio = 0
   _optimizer_block_size = 4096
   _sort_multiblock_read_count = 2
   _hash_multiblock_io_count = 0
   _db_file_optimizer_read_count = 256
   _optimizer_max_permutations = 2000
   pga_aggregate_target = 3145728 KB
   _pga_max_size = 4194304 KB
   _query_rewrite_maxdisjunct = 257
   _smm_auto_min_io_size = 60 KB
   _smm_auto_max_io_size = 252 KB
   _smm_min_size = 1024 KB
   _smm_max_size = 629145 KB
   _smm_px_max_size = 1572864 KB
   _cpu_to_io = 0
   _optimizer_undo_cost_change = 10.2.0.3
   parallel_query_mode = enabled
   parallel_dml_mode = disabled
   parallel_ddl_mode = enabled
   optimizer_mode = all_rows
   sqlstat_enabled = false
   _optimizer_percent_parallel = 101
   _always_anti_join = choose
   _always_semi_join = choose
   _optimizer_mode_force = true
   _partition_view_enabled = true
   _always_star_transformation = false
   _query_rewrite_or_error = false
   _hash_join_enabled = true
   cursor_sharing = exact
   _b_tree_bitmap_plans = true
   star_transformation_enabled = false
   _optimizer_cost_model = choose
   _new_sort_cost_estimate = true
   _complex_view_merging = true
   _unnest_subquery = true
   _eliminate_common_subexpr = true
   _pred_move_around = true
   _convert_set_to_join = false
   _push_join_predicate = true
   _push_join_union_view = true
   _fast_full_scan_enabled = true
   _optim_enhance_nnull_detection = true
   _parallel_broadcast_enabled = true
   _px_broadcast_fudge_factor = 100
   _ordered_nested_loop = true
   _no_or_expansion = false
   optimizer_index_cost_adj = 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
   _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
   _fix_control_key = 0
   _optimizer_skip_scan_guess = false
   _enable_row_shipping = false
   _row_shipping_threshold = 80
   _row_shipping_explain = false
   _optimizer_rownum_bind_default = 10
   _first_k_rows_dynamic_proration = true
   _optimizer_native_full_outer_join = off
   *********************************
   Bug Fix Control Environment
   ***************************
   fix 4611850 = enabled
   fix 4663804 = enabled
   fix 4663698 = enabled
   fix 4545833 = enabled
   fix 3499674 = disabled
   fix 4584065 = enabled
   fix 4602374 = enabled
   fix 4569940 = enabled
   fix 4631959 = enabled
   fix 4519340 = enabled
   fix 4550003 = enabled
   fix 4488689 = enabled
   fix 3118776 = enabled
   fix 4519016 = enabled
   fix 4487253 = enabled
   fix 4556762 = 15
   fix 4728348 = enabled
   fix 4723244 = enabled
   fix 4554846 = enabled
   fix 4175830 = enabled
   fix 4722900 = enabled
   fix 5094217 = enabled
   fix 4904890 = enabled
   fix 4483286 = disabled
   fix 4969880 = disabled
   fix 4711525 = enabled
   fix 4717546 = enabled
   fix 4904838 = enabled
   fix 5005866 = enabled
   fix 4600710 = enabled
   fix 5129233 = enabled
   fix 5195882 = enabled
   fix 5084239 = enabled
   fix 4595987 = enabled
   fix 4134994 = enabled
   fix 5104624 = enabled
   fix 4908162 = enabled
   fix 5015557 = enabled
   Query Block Registry:
   *********************
   SEL$1 0xff747610 (PARSER) [FINAL]
   Optimizer State Dump: call(in-use=144800, alloc=180024), compile(in-use=88648, alloc=138728)
   Dumping Hints
   =============
   atom_hint=(@=0xff740618 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX (“B” “BT_BILLING_CHARGES_I4″) )

4. niket Says:
   June 9th, 2010 at 11:53 am

   As our application is hard coded i cannot use index hint in the query so i have used DBMS_SQLTUNE (SQL Profiles) – DBMS_SQLTUNE.IMPORT_SQL_PROFILE – inorder that the query uses index whenever it gets executed.
   It is showing “User hint to use this index” in trace file But still it is not using index pls find the trace file after using sql profile

   /home/oracle/admin/IMCLDB/udump/imcldb_ora_12231.trc
   Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
   With the Partitioning, OLAP and Data Mining options
   ORACLE_HOME = /home/oracle/product/10.2
   System name: Linux
   Node name: localhost.localdomain
   Release: 2.6.9-42.ELsmp
   Version: #1 SMP Tue Aug 15 10:35:26 BST 2006
   Machine: x86_64
   Instance name: IMCLDB
   Redo thread mounted by this instance: 1
   Oracle process number: 27
   Unix process pid: 12231, image: oracle@localhost.localdomain (TNS V1-V3)

   *** 2010-06-05 11:59:20.633
   *** ACTION NAME:() 2010-06-05 11:59:20.632
   *** MODULE NAME:(SQL*Plus) 2010-06-05 11:59:20.632
   *** SERVICE NAME:(SYS$USERS) 2010-06-05 11:59:20.632
   *** SESSION ID:(421.55) 2010-06-05 11:59:20.632
   Registered qb: SEL$1 0xffab2590 (PARSER)
   signature (): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=4 objn=32031 hint_alias=”B”@”SEL$1″
   **************************
   Predicate Move-Around (PM)
   **************************
   PM: Considering predicate move-around in SEL$1 (#0).
   PM: Checking validity of predicate move-around in SEL$1 (#0).
   CBQT: Validity checks failed for 6rb5chhfaquf8.
   CVM: Considering view merge in query block SEL$1 (#0)
   CBQT: Validity checks failed for 6rb5chhfaquf8.
   ***************
   Subquery Unnest
   ***************
   SU: Considering subquery unnesting in query block SEL$1 (#0)
   *************************
   Set-Join Conversion (SJC)
   *************************
   SJC: Considering set-join conversion in SEL$1 (#0).
   **************************
   Predicate Move-Around (PM)
   **************************
   PM: Considering predicate move-around in SEL$1 (#0).
   PM: Checking validity of predicate move-around in SEL$1 (#0).
   PM: PM bypassed: Outer query contains no views.
   FPD: Considering simple filter push in SEL$1 (#0)
   FPD: Current where clause predicates in SEL$1 (#0) :
   “B”.”ENTITY_ID”=TO_NUMBER(:B1) AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B3,”B”.”PAYMODE_TRANS_NO”)
   kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
   predicates with check contraints: “B”.”ENTITY_ID”=TO_NUMBER(:B1) AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B3,”B”.”PAYMODE_TRANS_NO”)
   after transitive predicate generation: “B”.”ENTITY_ID”=TO_NUMBER(:B1) AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B3,”B”.”PAYMODE_TRANS_NO”)
   finally: “B”.”ENTITY_ID”=TO_NUMBER(:B1) AND NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B3,”B”.”PAYMODE_TRANS_NO”)
   apadrv-start: call(in-use=736, alloc=16344), compile(in-use=41160, alloc=44568)
   kkoqbc-start
   : call(in-use=736, alloc=16344), compile(in-use=42008, alloc=44568)
   ******************************************
   Current SQL statement for this session:
   explain plan for SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)
   *******************************************
   Legend
   The following abbreviations are used by optimizer trace.
   CBQT – cost-based query transformation
   JPPD – join predicate push-down
   FPD – filter push-down
   PM – predicate move-around
   CVM – complex view merging
   SPJ – select-project-join
   SJC – set join conversion
   SU – subquery unnesting
   OBYE – order by elimination
   ST – star transformation
   qb – query block
   LB – leaf blocks
   DK – distinct keys
   LB/K – average number of leaf blocks per key
   DB/K – average number of data blocks per key
   CLUF – clustering factor
   NDV – number of distinct values
   Resp – response cost
   Card – cardinality
   Resc – resource cost
   NL – nested loops (join)
   SM – sort merge (join)
   HA – hash (join)
   CPUCSPEED – CPU Speed
   IOTFRSPEED – I/O transfer speed
   IOSEEKTIM – I/O seek time
   SREADTIM – average single block read time
   MREADTIM – average multiblock read time
   MBRC – average multiblock read count
   MAXTHR – maximum I/O system throughput
   SLAVETHR – average slave I/O throughput
   dmeth – distribution method
   1: no partitioning required
   2: value partitioned
   4: right is random (round-robin)
   512: left is random (round-robin)
   8: broadcast right and partition left
   16: broadcast left and partition right
   32: partition left using partitioning of right
   64: partition right using partitioning of left
   128: use hash partitioning dimension
   256: use range partitioning dimension
   2048: use list partitioning dimension
   1024: run the join in serial
   0: invalid distribution method
   sel – selectivity
   ptn – partition
   *******************************************
   Peeked values of the binds in SQL statement
   *******************************************
   kkscoacd
   Bind#0
   oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
   oacflg=00 fl2=1010000 frm=01 csi=31 siz=96 off=0
   kxsbbbfp=2a978aa130 bln=32 avl=00 flg=05
   Bind#1
   oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
   oacflg=00 fl2=1010000 frm=01 csi=31 siz=0 off=32
   kxsbbbfp=2a978aa150 bln=32 avl=00 flg=01
   Bind#2
   oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
   oacflg=00 fl2=1010000 frm=01 csi=31 siz=0 off=64
   kxsbbbfp=2a978aa170 bln=32 avl=00 flg=01
   ***************************************
   PARAMETERS USED BY THE OPTIMIZER
   ********************************
   *************************************
   PARAMETERS WITH ALTERED VALUES
   ******************************
   hash_area_size = 10485760
   sort_area_size = 10485760
   _pga_max_size = 4194304 KB
   *********************************
   Bug Fix Control Environment
   ***************************
   fix 4611850 = enabled
   fix 4663804 = enabled
   fix 4663698 = enabled
   fix 4545833 = enabled
   fix 3499674 = disabled
   fix 4584065 = enabled
   fix 4602374 = enabled
   fix 4569940 = enabled
   fix 4631959 = enabled
   fix 4519340 = enabled
   fix 4550003 = enabled
   fix 4488689 = enabled
   fix 3118776 = enabled
   fix 4519016 = enabled
   fix 4487253 = enabled
   fix 4556762 = 15
   fix 4728348 = enabled
   fix 4723244 = enabled
   fix 4554846 = enabled
   fix 4175830 = enabled
   fix 4722900 = enabled
   fix 5094217 = enabled
   fix 4904890 = enabled
   fix 4483286 = disabled
   fix 4969880 = disabled
   fix 4711525 = enabled
   fix 4717546 = enabled
   fix 4904838 = enabled
   fix 5005866 = enabled
   fix 4600710 = enabled
   fix 5129233 = enabled
   fix 5195882 = enabled
   fix 5084239 = enabled
   fix 4595987 = enabled
   fix 4134994 = enabled
   fix 5104624 = enabled
   fix 4908162 = enabled
   fix 5015557 = enabled
   *************************************
   PARAMETERS WITH DEFAULT VALUES
   ******************************
   optimizer_mode_hinted = false
   optimizer_features_hinted = 0.0.0
   parallel_execution_enabled = true
   parallel_query_forced_dop = 0
   parallel_dml_forced_dop = 0
   parallel_ddl_forced_degree = 0
   parallel_ddl_forced_instances = 0
   _query_rewrite_fudge = 90
   optimizer_features_enable = 10.2.0.3
   _optimizer_search_limit = 5
   cpu_count = 2
   active_instance_count = 1
   parallel_threads_per_cpu = 2
   bitmap_merge_area_size = 1048576
   sort_area_retained_size = 0
   _sort_elimination_cost_ratio = 0
   _optimizer_block_size = 4096
   _sort_multiblock_read_count = 2
   _hash_multiblock_io_count = 0
   _db_file_optimizer_read_count = 256
   _optimizer_max_permutations = 2000
   pga_aggregate_target = 3145728 KB
   _query_rewrite_maxdisjunct = 257
   _smm_auto_min_io_size = 60 KB
   _smm_auto_max_io_size = 252 KB
   _smm_min_size = 1024 KB
   _smm_max_size = 629145 KB
   _smm_px_max_size = 1572864 KB
   _cpu_to_io = 0
   _optimizer_undo_cost_change = 10.2.0.3
   parallel_query_mode = enabled
   parallel_dml_mode = disabled
   parallel_ddl_mode = enabled
   optimizer_mode = all_rows
   sqlstat_enabled = false
   _optimizer_percent_parallel = 101
   _always_anti_join = choose
   _always_semi_join = choose
   _optimizer_mode_force = true
   _partition_view_enabled = true
   _always_star_transformation = false
   _query_rewrite_or_error = false
   _hash_join_enabled = true
   cursor_sharing = exact
   _b_tree_bitmap_plans = true
   star_transformation_enabled = false
   _optimizer_cost_model = choose
   _new_sort_cost_estimate = true
   _complex_view_merging = true
   _unnest_subquery = true
   _eliminate_common_subexpr = true
   _pred_move_around = true
   _convert_set_to_join = false
   _push_join_predicate = true
   _push_join_union_view = true
   _fast_full_scan_enabled = true
   _optim_enhance_nnull_detection = true
   _parallel_broadcast_enabled = true
   _px_broadcast_fudge_factor = 100
   _ordered_nested_loop = true
   _no_or_expansion = false
   optimizer_index_cost_adj = 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
   _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
   _fix_control_key = 0
   _optimizer_skip_scan_guess = false
   _enable_row_shipping = false
   _row_shipping_threshold = 80
   _row_shipping_explain = false
   _optimizer_rownum_bind_default = 10
   _first_k_rows_dynamic_proration = true
   _optimizer_native_full_outer_join = off
   *********************************
   Bug Fix Control Environment
   ***************************
   fix 4611850 = enabled
   fix 4663804 = enabled
   fix 4663698 = enabled
   fix 4545833 = enabled
   fix 3499674 = disabled
   fix 4584065 = enabled
   fix 4602374 = enabled
   fix 4569940 = enabled
   fix 4631959 = enabled
   fix 4519340 = enabled
   fix 4550003 = enabled
   fix 4488689 = enabled
   fix 3118776 = enabled
   fix 4519016 = enabled
   fix 4487253 = enabled
   fix 4556762 = 15
   fix 4728348 = enabled
   fix 4723244 = enabled
   fix 4554846 = enabled
   fix 4175830 = enabled
   fix 4722900 = enabled
   fix 5094217 = enabled
   fix 4904890 = enabled
   fix 4483286 = disabled
   fix 4969880 = disabled
   fix 4711525 = enabled
   fix 4717546 = enabled
   fix 4904838 = enabled
   fix 5005866 = enabled
   fix 4600710 = enabled
   fix 5129233 = enabled
   fix 5195882 = enabled
   fix 5084239 = enabled
   fix 4595987 = enabled
   fix 4134994 = enabled
   fix 5104624 = enabled
   fix 4908162 = enabled
   fix 5015557 = enabled
   ***************************************
   PARAMETERS IN OPT_PARAM HINT
   ****************************
   ***************************************
   Column Usage Monitoring is ON: tracking level = 1
   ***************************************
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   kkofmx: index filter:NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B1,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B2,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:”B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B1,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B2,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:”B”.”CP_CODE”=NVL(:B1,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B2,”B”.”PAYMODE_TRANS_NO”)
   kkofmx: index filter:”B”.”PAYMODE_TRANS_NO”=NVL(:B1,”B”.”PAYMODE_TRANS_NO”)
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Access Path: index (IndexOnly)
   Index: BT_BILLING_CHARGES_I4
   resc_io: 5.00 resc_cpu: 238929
   ix_sel: 1.2497e-05 ix_sel_with_filters: 2.0196e-10
   Cost: 5.03 Resp: 5.03 Degree: 1
   Best:: AccessPath: IndexRange Index: BT_BILLING_CHARGES_I4
   Cost: 5.03 Degree: 1 Resp: 5.03 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 5.0271 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   Transformed predicate “B”.”PAYMODE_TRANS_NO”=NVL(:B1,”B”.”PAYMODE_TRANS_NO”)
   to :B1 IS NOT NULL AND “B”.”PAYMODE_TRANS_NO”=:B2 OR :B3 IS NULL AND “B”.”PAYMODE_TRANS_NO” IS NOT NULL
   ******** Next OR predicate ********
   ******** OR-branching ********
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   ******** OR-branching ********
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   ******** Same chain after expansion ********
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
   fro(0): flg=4 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   ******** Final costing ********
   Registered qb: SEL$1_1 0xffab2590 (OR EXPANSION SEL$1; SEL$1;
   signature (): qb_name=SEL$1_1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1_1 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   Final – All Rows Plan: Best join order: 1
   Cost: 269.2736 Degree: 1 Card: 1.0000 Bytes: 88
   Resc: 269.2736 Resc_io: 269.0000 Resc_cpu: 2987872
   Resp: 269.2736 Resp_io: 269.0000 Resc_cpu: 2987872
   kkoipt: Query block SEL$1_1 (#0)
   Registered qb: SEL$1_2 0xffab2590 (OR EXPANSION SEL$1)
   signature (): qb_name=SEL$1_2 nbfros=1 flg=0
   fro(0): flg=4 objn=32031 hint_alias=”B”@”SEL$1_2″
   ****************
   QUERY BLOCK TEXT
   ****************
   SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)
   *********************
   QUERY BLOCK SIGNATURE
   *********************
   qb name was generated
   signature (optimizer): qb_name=SEL$1_2 nbfros=1 flg=0
   fro(0): flg=0 objn=32031 hint_alias=”B”@”SEL$1_2″
   *****************************
   SYSTEM STATISTICS INFORMATION
   *****************************
   Using WORKLOAD Stats
   CPUSPEED: 888 millions instructions/sec
   SREADTIM: 12 milliseconds
   MREADTIM: 143 millisecons
   MBRC: 50.000000 blocks
   MAXTHR: 10408960 bytes/sec
   SLAVETHR: 5120 bytes/sec
   ***************************************
   BASE STATISTICAL INFORMATION
   ***********************
   Table Stats::
   Table: BT_BILLING_CHARGES Alias: B
   #Rows: 64612462 #Blks: 2478456 AvgRowLen: 255.00
   Column (#8): AMOUNT(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#7): INVOI

5. niket Says:
   June 9th, 2010 at 11:57 am

   INVOICE_CHARGE_PRICE(NUMBER)
   AvgLen: 4.00 NDV: 218 Nulls: 0 Density: 0.0045872 Min: 0 Max: 126720
   Column (#1): CHARGE_ID(NUMBER)
   AvgLen: 6.00 NDV: 64612462 Nulls: 0 Density: 1.5477e-08 Min: 2053 Max: 66080516
   Column (#31): INTERNAL_AGREEMENT_NO(NUMBER)
   AvgLen: 5.00 NDV: 478923 Nulls: 0 Density: 2.4515e-06 Min: 0 Max: 1000971
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 242
   Column (#26): ITEM_CODE(VARCHAR2)
   AvgLen: 9.00 NDV: 191 Nulls: 201310 Density: 7.7217e-09
   Histogram: Freq #Bkts: 190 UncompBkts: 8363 EndPtVals: 190
   Column (#22): ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 141419 Nulls: 0 Density: 1.2497e-05 Min: 12345692 Max: 12774836
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#21): AGREEMENT_ID(NUMBER)
   AvgLen: 3.00 NDV: 96369 Nulls: 0 Density: 8.2243e-07 Min: 0 Max: 134525
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 10
   Column (#19): START_DATE(DATE)
   AvgLen: 8.00 NDV: 602 Nulls: 0 Density: 6.7981e-04 Min: 2418916 Max: 2455349
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 65
   Column (#20): END_DATE(DATE)
   AvgLen: 8.00 NDV: 342 Nulls: 0 Density: 6.3694e-04 Min: 2418945 Max: 2455378
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 61
   Column (#45): CHARGE_CODE(VARCHAR2)
   AvgLen: 7.00 NDV: 21 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 21 UncompBkts: 8389 EndPtVals: 21
   Column (#41): CHARGE_TYPE(VARCHAR2)
   AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 2 UncompBkts: 8389 EndPtVals: 2
   Column (#37): PRICE_INCLUSIVE_OF_TAXES(CHARACTER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1
   Column (#39): PRICING_ENTITY_ID(NUMBER)
   AvgLen: 6.00 NDV: 3597405 Nulls: 0 Density: 3.9909e-07 Min: 0 Max: 4149906
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 251
   Column (#2): PARTY_SLACCOUNT(VARCHAR2)
   AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
   Column (#4): INVOICE_NO(NUMBER)
   AvgLen: 6.00 NDV: 10953325 Nulls: 1061 Density: 1.2781e-07 Min: 15 Max: 10465909
   Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 255
   Column (#11): PAID_STATUS(CHARACTER)
   AvgLen: 2.00 NDV: 3 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 3 UncompBkts: 8389 EndPtVals: 3
   Column (#47): CP_CODE(VARCHAR2)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#46): PAYMODE_TRANS_NO(NUMBER)
   AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 7.6978e-09 Min: 0 Max: 0
   Histogram: Freq #Bkts: 1 UncompBkts: 8389 EndPtVals: 1
   Column (#48): SYS_NC00048$(NUMBER)
   AvgLen: 6.00 NDV: 4505105 Nulls: 0 Density: 2.2197e-07 Min: 2053 Max: 10465683
   Index Stats::
   Index: BT_BILLING_CHARGES_I1 Col#: 48 22 11
   LVLS: 2 #LB: 53359 #DK: 10753486 LB/K: 1.00 DB/K: 1.00 CLUF: 18259951.00
   Index: BT_BILLING_CHARGES_I2 Col#: 22 1 31 48 11 27 30
   LVLS: 2 #LB: 104180 #DK: 63971062 LB/K: 1.00 DB/K: 1.00 CLUF: 20666383.00
   Index: BT_BILLING_CHARGES_I3 Col#: 22 1 31 48 11 27 30 47 46 28
   LVLS: 2 #LB: 100218 #DK: 64103929 LB/K: 1.00 DB/K: 1.00 CLUF: 20587999.00
   Index: BT_BILLING_CHARGES_I4 Col#: 22 1 31 48 11 47 46 28 8 7 26 21 19 20 45 41 37 39 2
   LVLS: 2 #LB: 218228 #DK: 65607296 LB/K: 1.00 DB/K: 1.00 CLUF: 21232548.00
   User hint to use this index
   Index: IDX_BTBILLCHRG_PAID_STATUS Col#: 11
   LVLS: 3 #LB: 261683 #DK: 3 LB/K: 87227.00 DB/K: 983911.00 CLUF: 2951733.00
   Index: IDX_BTBILLCHR_AGREEMENTID Col#: 21
   LVLS: 3 #LB: 251108 #DK: 96369 LB/K: 2.00 DB/K: 56.00 CLUF: 5422367.00
   Index: IDX_BTBILLCHR_BILLTOID Col#: 42
   LVLS: 3 #LB: 352086 #DK: 141419 LB/K: 2.00 DB/K: 144.00 CLUF: 20415887.00
   Index: IDX_BTBILL_BPDET_NO Col#: 29
   LVLS: 3 #LB: 307708 #DK: 6440535 LB/K: 1.00 DB/K: 3.00 CLUF: 19781554.00
   Index: IDX_BT_BILLING_ENTITYID Col#: 22
   LVLS: 3 #LB: 343155 #DK: 141419 LB/K: 2.00 DB/K: 139.00 CLUF: 19742720.00
   Index: IDX_BT_BILLING_INVOICE_NO Col#: 4
   LVLS: 3 #LB: 322255 #DK: 10953325 LB/K: 1.00 DB/K: 1.00 CLUF: 18159610.00
   Index: IDX_BT_BILLING_PRICING_ID Col#: 39
   LVLS: 3 #LB: 330552 #DK: 3597405 LB/K: 1.00 DB/K: 15.00 CLUF: 55539437.00
   Index: IDX_CHG_CODE_ID Col#: 6
   LVLS: 3 #LB: 281084 #DK: 21 LB/K: 13384.00 DB/K: 177563.00 CLUF: 3728832.00
   Index: IDX_INT_AGMT_NO_BLNG_CHGS Col#: 31
   LVLS: 3 #LB: 320928 #DK: 478923 LB/K: 1.00 DB/K: 41.00 CLUF: 20078083.00
   Index: IDX_LOAN_ID Col#: 30
   LVLS: 3 #LB: 256860 #DK: 1 LB/K: 256860.00 DB/K: 2601320.00 CLUF: 2601320.00
   Index: IDX_SAL_ID Col#: 27
   LVLS: 3 #LB: 247429 #DK: 29 LB/K: 8532.00 DB/K: 96412.00 CLUF: 2795975.00
   Index: SYS_C002908 Col#: 1
   LVLS: 3 #LB: 307196 #DK: 62735145 LB/K: 1.00 DB/K: 1.00 CLUF: 8384564.00
   ***************************************
   SINGLE TABLE ACCESS PATH
   Using prorated density: 1.2781e-07 of col #4 as selectivity of out-of-range value pred
   Table: BT_BILLING_CHARGES Alias: B
   Card: Original: 64612462 Rounded: 1 Computed: 0.01 Non Adjusted: 0.01
   Access Path: TableScan
   Cost: 586533.11 Resp: 586533.11 Degree: 0
   Cost_io: 578172.00 Cost_cpu: 91308489723
   Resp_io: 578172.00 Resp_cpu: 91308489723
   Access Path: index (AllEqRange)
   Index: IDX_BTBILLCHRG_PAID_STATUS
   resc_io: 3137959.00 resc_cpu: 109722673835
   ix_sel: 0.97652 ix_sel_with_filters: 0.97652
   Cost: 3148006.29 Resp: 3148006.29 Degree: 1
   Access Path: index (AllEqRange)
   Index: IDX_BT_BILLING_ENTITYID
   resc_io: 269.00 resc_cpu: 2987872
   ix_sel: 1.3181e-05 ix_sel_with_filters: 1.3181e-05
   Cost: 269.27 Resp: 269.27 Degree: 1
   ****** trying bitmap/domain indexes ******
   Best:: AccessPath: IndexRange Index: IDX_BT_BILLING_ENTITYID
   Cost: 269.27 Degree: 1 Resp: 269.27 Card: 0.01 Bytes: 0
   ***************************************
   OPTIMIZER STATISTICS AND COMPUTATIONS
   ***************************************
   GENERAL PLANS
   ***************************************
   Considering cardinality-based initial join order.
   Permutations for Starting Table :0
   ***********************
   Join order[1]: BT_BILLING_CHARGES[B]#0
   ***********************
   Best so far: Table#: 0 cost: 269.2736 card: 0.0130 bytes: 88
   (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
   *********************************
   Number of join permutations tried: 1
   *********************************
   Final – All Rows Plan: Best join order: 1
   Cost: 538.5472 Degree: 1 Card: 2.0000 Bytes: 176
   Resc: 538.5472 Resc_io: 538.0000 Resc_cpu: 5975744
   Resp: 538.5472 Resp_io: 538.0000 Resc_cpu: 5975744
   kkoipt: Query block SEL$1_2 (#0)
   kkoqbc-end
   : call(in-use=286368, alloc=294600), compile(in-use=94656, alloc=96776)
   apadrv-end: call(in-use=286368, alloc=294600), compile(in-use=95600, alloc=96776)

   sql_id=6rb5chhfaquf8.
   Current SQL statement for this session:
   explain plan for SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO, B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE, B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,B.PARTY_SLACCOUNT FROM BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO= NVL(:B1 ,B.PAYMODE_TRANS_NO)

   ============
   Plan Table
   ============
   —————————————————————-+———————————–+
   | Id | Operation | Name | Rows | Bytes | Cost | Time |
   —————————————————————-+———————————–+
   | 0 | SELECT STATEMENT | | | | 539 | |
   | 1 | CONCATENATION | | | | | |
   | 2 | FILTER | | | | | |
   | 3 | TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES | 1 | 88 | 269 | 00:00:04 |
   | 4 | INDEX RANGE SCAN | IDX_BT_BILLING_ENTITYID| 807 | | 8 | 00:00:01 |
   | 5 | FILTER | | | | | |
   | 6 | TABLE ACCESS BY INDEX ROWID | BT_BILLING_CHARGES | 1 | 88 | 269 | 00:00:04 |
   | 7 | INDEX RANGE SCAN | IDX_BT_BILLING_ENTITYID| 807 | | 8 | 00:00:01 |
   —————————————————————-+———————————–+
   Predicate Information:
   ———————-
   2 – filter(:B1 IS NULL)
   3 – filter((NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”PAYMODE_TRANS_NO” IS NOT NULL AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”)))
   4 – access(“B”.”ENTITY_ID”=TO_NUMBER(:B3))
   5 – filter(:B1 IS NOT NULL)
   6 – filter((NVL(“B”.”INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_CODE”) AND “B”.”PAYMODE_TRANS_NO”=:B1))
   7 – access(“B”.”ENTITY_ID”=TO_NUMBER(:B3))

   Content of other_xml column
   ===========================
   db_version : 10.2.0.3
   parse_schema : IMCL420
   plan_hash : 1251470260
   sql_profile : SQL_PROF_SAMPLE
   Outline Data:
   /*+
   BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE(’10.2.0.3′)
   ALL_ROWS
   OUTLINE_LEAF(@”SEL$1″)
   OUTLINE_LEAF(@”SEL$1_1″)
   USE_CONCAT(@”SEL$1″
   OUTLINE_LEAF(@”SEL$1_2″)
   OUTLINE(@”SEL$1″)
   INDEX_RS_ASC(@”SEL$1_1″ “B”@”SEL$1″ (“BT_BILLING_CHARGES”.”ENTITY_ID”))
   INDEX_RS_ASC(@”SEL$1_2″ “B”@”SEL$1_2″ (“BT_BILLING_CHARGES”.”ENTITY_ID”))
   END_OUTLINE_DATA
   */

   Optimizer environment:
   optimizer_mode_hinted = false
   optimizer_features_hinted = 0.0.0
   parallel_execution_enabled = true
   parallel_query_forced_dop = 0
   parallel_dml_forced_dop = 0
   parallel_ddl_forced_degree = 0
   parallel_ddl_forced_instances = 0
   _query_rewrite_fudge = 90
   optimizer_features_enable = 10.2.0.3
   _optimizer_search_limit = 5
   cpu_count = 2
   active_instance_count = 1
   parallel_threads_per_cpu = 2
   hash_area_size = 10485760
   bitmap_merge_area_size = 1048576
   sort_area_size = 10485760
   sort_area_retained_size = 0
   _sort_elimination_cost_ratio = 0
   _optimizer_block_size = 4096
   _sort_multiblock_read_count = 2
   _hash_multiblock_io_count = 0
   _db_file_optimizer_read_count = 256
   _optimizer_max_permutations = 2000
   pga_aggregate_target = 3145728 KB
   _pga_max_size = 4194304 KB
   _query_rewrite_maxdisjunct = 257
   _smm_auto_min_io_size = 60 KB
   _smm_auto_max_io_size = 252 KB
   _smm_min_size = 1024 KB
   _smm_max_size = 629145 KB
   _smm_px_max_size = 1572864 KB
   _cpu_to_io = 0
   _optimizer_undo_cost_change = 10.2.0.3
   parallel_query_mode = enabled
   parallel_dml_mode = disabled
   parallel_ddl_mode = enabled
   optimizer_mode = all_rows
   sqlstat_enabled = false
   _optimizer_percent_parallel = 101
   _always_anti_join = choose
   _always_semi_join = choose
   _optimizer_mode_force = true
   _partition_view_enabled = true
   _always_star_transformation = false
   _query_rewrite_or_error = false
   _hash_join_enabled = true
   cursor_sharing = exact
   _b_tree_bitmap_plans = true
   star_transformation_enabled = false
   _optimizer_cost_model = choose
   _new_sort_cost_estimate = true
   _complex_view_merging = true
   _unnest_subquery = true
   _eliminate_common_subexpr = true
   _pred_move_around = true
   _convert_set_to_join = false
   _push_join_predicate = true
   _push_join_union_view = true
   _fast_full_scan_enabled = true
   _optim_enhance_nnull_detection = true
   _parallel_broadcast_enabled = true
   _px_broadcast_fudge_factor = 100
   _ordered_nested_loop = true
   _no_or_expansion = false
   optimizer_index_cost_adj = 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
   _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
   _fix_control_key = 0
   _optimizer_skip_scan_guess = false
   _enable_row_shipping = false
   _row_shipping_threshold = 80
   _row_shipping_explain = false
   _optimizer_rownum_bind_default = 10
   _first_k_rows_dynamic_proration = true
   _optimizer_native_full_outer_join = off
   *********************************
   Bug Fix Control Environment
   ***************************
   fix 4611850 = enabled
   fix 4663804 = enabled
   fix 4663698 = enabled
   fix 4545833 = enabled
   fix 3499674 = disabled
   fix 4584065 = enabled
   fix 4602374 = enabled
   fix 4569940 = enabled
   fix 4631959 = enabled
   fix 4519340 = enabled
   fix 4550003 = enabled
   fix 4488689 = enabled
   fix 3118776 = enabled
   fix 4519016 = enabled
   fix 4487253 = enabled
   fix 4556762 = 15
   fix 4728348 = enabled
   fix 4723244 = enabled
   fix 4554846 = enabled
   fix 4175830 = enabled
   fix 4722900 = enabled
   fix 5094217 = enabled
   fix 4904890 = enabled
   fix 4483286 = disabled
   fix 4969880 = disabled
   fix 4711525 = enabled
   fix 4717546 = enabled
   fix 4904838 = enabled
   fix 5005866 = enabled
   fix 4600710 = enabled
   fix 5129233 = enabled
   fix 5195882 = enabled
   fix 5084239 = enabled
   fix 4595987 = enabled
   fix 4134994 = enabled
   fix 5104624 = enabled
   fix 4908162 = enabled
   fix 5015557 = enabled
   Query Block Registry:
   *********************
   SEL$1 0xffab2590 (PARSER) [FINAL]
   SEL$1_2 0xffab2590 (OR EXPANSION SEL$1) [FINAL]
   SEL$1_1 0xffab2590 (OR EXPANSION SEL$1; SEL$1; [FINAL]
   SEL$1_1 0xffab2590 (OR EXPANSION SEL$1; SEL$1; [FINAL]
   Optimizer State Dump: call(in-use=292920, alloc=327336), compile(in-use=154056, alloc=203008)
   Dumping Hints
   =============
   atom_hint=(@=0xffaa44f0 err=0 resol=1 used=0 token=83 org=2 lvl=3 txt=INDEX (“B” “BT_BILLING_CHARGES_I4″) )
   atom_hint=(@=0xffa928d0 err=0 resol=1 used=1 token=83 org=2 lvl=3 txt=INDEX (“B” “BT_BILLING_CHARGES_I4″) )

6. niket Says:
   June 9th, 2010 at 12:05 pm

   I have also flush shared pool.

   I have used histogram (buckets 254) but at the end after all activity i tried. Inorder to check whether it uses index after histogram.
   So histogram might not be problem.

   This query is taken lot of time it is waiting on “Sequential reads” searching so that was the reason i wanted to use index . So that there is only one access path that is BT_BILLING_CHARGES_I4 instead of 4 way searching.

   After generating the tkprof about the query it showed the following output

   SELECT B.AMOUNT,B.INVOICE_CHARGE_PRICE,B.CHARGE_ID,B.INTERNAL_AGREEMENT_NO,
   B.ITEM_CODE,B.ENTITY_ID,B.AGREEMENT_ID,B.START_DATE,B.END_DATE,
   B.CHARGE_CODE,B.CHARGE_TYPE,PRICE_INCLUSIVE_OF_TAXES,B.PRICING_ENTITY_ID,
   B.PARTY_SLACCOUNT
   FROM
   BT_BILLING_CHARGES B WHERE B.ENTITY_ID=:B3 AND NVL(B.INVOICE_NO,0)=0 AND
   B.PAID_STATUS=’N’ AND B.CP_CODE=NVL(:B2 ,B.CP_CODE) AND B.PAYMODE_TRANS_NO=
   NVL(:B1 ,B.PAYMODE_TRANS_NO)

   call count cpu elapsed disk query current rows
   ——- —— ——– ———- ———- ———- ———- ———-
   Parse 27646 0.95 0.97 0 0 0 0
   Execute 27646 6.33 6.24 0 0 0 0
   Fetch 205610 368.92 25142.85 2516541 3823078 0 177964
   ——- —— ——– ———- ———- ———- ———- ———-
   total 260902 376.21 25150.07 2516541 3823078 0 177964

   Misses in library cache during parse: 1
   Misses in library cache during execute: 1
   Optimizer mode: ALL_ROWS
   Parsing user id: 59 (recursive depth: 1)

   Rows Row Source Operation
   ——- —————————————————
   1 CONCATENATION (cr=117 pr=64 pw=0 time=316987 us)
   0 FILTER (cr=0 pr=0 pw=0 time=4 us)
   0 TABLE ACCESS BY INDEX ROWID BT_BILLING_CHARGES (cr=0 pr=0 pw=0 time=0 us)
   0 INDEX RANGE SCAN IDX_BT_BILLING_ENTITYID (cr=0 pr=0 pw=0 time=0 us)(object id 32032)
   1 FILTER (cr=117 pr=64 pw=0 time=316977 us)
   1 TABLE ACCESS BY INDEX ROWID BT_BILLING_CHARGES (cr=117 pr=64 pw=0 time=316968 us)

   113 INDEX RANGE SCAN IDX_BT_BILLING_ENTITYID (cr=5 pr=1 pw=0 time=14330 us)(object id 320
   32)

   Elapsed times include waiting on following events:
   Event waited on Times Max. Wait Total Waited
   —————————————- Waited ———- ————
   db file sequential read 2516541 1.17 24974.13
   latch: library cache 5 0.08 0.13
   latch: shared pool 1 0.02 0.02
   ********************************************************************************

   It is generating 1 record in one minute so for 1.6 lakh records it is taking 1 and 1/2 to 2 days on test server.

   Kindly guide us.

   Thanks

   Regards

   Niket

7. Ramesh Menon Says:
   June 10th, 2010 at 12:40 am

   Hi,

   1) Please provide the column index list ….
   2) Give me the following querie’s plan

   SELECT /*+ OPT_PARAM(‘_or_expand_nvl_predicate’,’FAlSE') */ b.amount, b.invoice_charge_price, b.charge_id, b.internal_agreement_no,
          b.item_code, b.entity_id, b.agreement_id, b.start_date, b.end_date,
          b.charge_code, b.charge_type, price_inclusive_of_taxes,
          b.pricing_entity_id, b.party_slaccount
     FROM bt_billing_charges b
    WHERE b.entity_id = :b3
      AND NVL (b.invoice_no, 0) = 0
      AND b.paid_status = 'N'
      AND b.cp_code = NVL (:b2, b.cp_code)
      AND b.paymode_trans_no = NVL (:b1, b.paymode_trans_no);
   

8. niket Says:
   June 10th, 2010 at 11:38 am

   Dear Ramesh Sir,

   The index script and column use in the index are as follows.

   CREATE INDEX IMCL420.BT_BILLING_CHARGES_I4 ON IMCL420.BT_BILLING_CHARGES
   (ENTITY_ID, CHARGE_ID, INTERNAL_AGREEMENT_NO, NVL(“INVOICE_NO”,0), PAID_STATUS,
   CP_CODE, PAYMODE_TRANS_NO, INVOICE_TYPE, AMOUNT, INVOICE_CHARGE_PRICE,
   ITEM_CODE, AGREEMENT_ID, START_DATE, END_DATE, CHARGE_CODE,
   CHARGE_TYPE, PRICE_INCLUSIVE_OF_TAXES, PRICING_ENTITY_ID, PARTY_SLACCOUNT)
   NOLOGGING
   TABLESPACE MAGNALARGEIDX
   PCTFREE 10
   INITRANS 2
   MAXTRANS 255
   STORAGE (
   INITIAL 64K
   MINEXTENTS 1
   MAXEXTENTS 2147483645
   PCTINCREASE 0
   BUFFER_POOL DEFAULT
   )
   PARALLEL ( DEGREE 3 INSTANCES 1 );

   The Execution plan for the query mention by you is as follows.
   ============
   Plan Table
   ============
   ————————————————-+———————————–+
   | Id | Operation | Name | Rows | Bytes | Cost | Time |
   ————————————————-+———————————–+
   | 0 | SELECT STATEMENT | | | | 3 | |
   | 1 | INDEX RANGE SCAN | BT_BILLING_CHARGES_I4| 1 | 72 | 3 | 00:00:01 |
   ————————————————-+———————————–+
   Predicate Information:
   ———————-
   1 – access(“B”.”ENTITY_ID”=TO_NUMBER(:B3) AND NVL(“INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N')
   1 – filter((NVL(“INVOICE_NO”,0)=0 AND “B”.”PAID_STATUS”=’N’ AND “B”.”CP_CODE”=NVL(:B2,”B”.”CP_COD
   E”) AND “B”.”PAYMODE_TRANS_NO”=NVL(:B1,”B”.”PAYMODE_TRANS_NO”)))

   Execution plan is very much suitable.But i am not able to understand the hint in the query.

   Kindly guide us

   Thank you very much for your help.

   Regards,

   Niket

9. niket Says:
   June 21st, 2010 at 4:06 am

   Dear Ramesh Sir,

   Is it possible for you to provide details about Oracle hidden parameter “_or_expand_nvl_predicate”

   If set “_or_expand_nvl_predicate” to false than what will be the positive and negative point related
   to it.

   Kindly help.

   Regards

   Niket

Leave a Reply