Hi,
  Below is the code where i am encountering problems.If i give bindvariable instead of travis in the below query,it is reflecting in the explain plan and so execution time also increasing.
I am sending you the query and explain plans.
How to get the same explain plan with bind variable.

Can any one help me in this reg ASAP..


Query :
SELECT cp.customer_id
FROM CUSTOMERS_PERSONAL cp, CUSTOMERS_ADDRESS ca ,CUSTOMERS c
WHERE ca.customer_id = cp.customer_id
AND ROWNUM < 1000
AND (ca.address_type = 'SH')
AND ca.country_code = 'US'
AND cp.first_name LIKE UPPER('travis%')
AND NVL(c.is_master,'Y')='Y'
AND c.customer_id = cp.customer_id
AND c.customer_code IS NOT NULL
ORDER BY cp.last_name, cp.first_name, cp.middle_name, cp.name_suffix

Explain Plan with first_name as travis:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 34 135
  SORT ORDER BY 34 1 K 135
    COUNT STOPKEY
      NESTED LOOPS 34 1 K 135
        NESTED LOOPS 33 1 K 69
          TABLE ACCESS BY INDEX ROWID CNH_CALMS2APP.CUSTOMERS_PERSONAL 33 825 3
            INDEX RANGE SCAN CNH_CALMS2APP.INDX_FIRST_NAME 33 2
          TABLE ACCESS BY INDEX ROWID CNH_CALMS2APP.CUSTOMERS 1 17 2
            INDEX UNIQUE SCAN CNH_CALMS2APP.XPKCUSTOMERS 3 1
        TABLE ACCESS BY INDEX ROWID CNH_CALMS2APP.CUSTOMERS_ADDRESS 1 12 2
          INDEX UNIQUE SCAN CNH_CALMS2APP.XAK1_CUSTOMERS_ADDRESS 8 1

Explain Plan with first_name as bind-variable(:QueryStrFN):

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 999 4936
  SORT ORDER BY 999 52 K 4936
    COUNT STOPKEY
      HASH JOIN 40 K 2 M 4487
        HASH JOIN 40 K 1 M 2526
          TABLE ACCESS BY INDEX ROWID CNH_CALMS2APP.CUSTOMERS_PERSONAL 39 K 957 K 10
            INDEX RANGE SCAN CNH_CALMS2APP.INDX_FIRST_NAME 7 K 2
          TABLE ACCESS FULL CNH_CALMS2APP.CUSTOMERS_ADDRESS 162 K 1 M 2299
        TABLE ACCESS FULL CNH_CALMS2APP.CUSTOMERS 399 K 6 M 1411