Essesntially, I'm saying that it's unlikely to be a Perl problem

Thank you very much Ozone, the problem looks similar
but I am unable to get the solution.
Could you please elaborate it more
This is my explain plan with with the hard-coded values

SELECT STATEMENT Optimizer=CHOOSE (Cost=3541 Card=2 Bytes=874)
  SORT (ORDER BY) (Cost=3541 Card=2 Bytes=874)
    CONCATENATION
      NESTED LOOPS (Cost=1775 Card=1 Bytes=437)
        NESTED LOOPS (Cost=1774 Card=1 Bytes=324)
          NESTED LOOPS (Cost=1773 Card=1 Bytes=236)
            NESTED LOOPS (Cost=1772 Card=1 Bytes=190)
              TABLE ACCESS (BY INDEX ROWID) OF UCSII_UNIT (Cost=1736 C
+ard=117 Bytes=9243)
                INDEX (RANGE SCAN) OF XIE1UCSII_UNIT (NON-UNIQUE) (Cos
+t=879 Card=117)
              TABLE ACCESS (BY INDEX ROWID) OF UCSI_ITEM (Cost=1 Card=
+1829857 Bytes=203114127)
                INDEX (UNIQUE SCAN) OF XPKUCSI_ITEM (UNIQUE) (Cost=2 C
+ard=1829857)
            INDEX (UNIQUE SCAN) OF XPKUCS_INV (UNIQUE) (Cost=1 Card=55
+8346 Bytes=25683916)
          TABLE ACCESS (BY INDEX ROWID) OF ULT_CUST_SALE (Cost=1 Card=
+60595 Bytes=5332360)
            INDEX (UNIQUE SCAN) OF XPKULT_CUST_SALE (UNIQUE) (Cost=1 C
+ard=60595)
        TABLE ACCESS (BY INDEX ROWID) OF ULT_CUST_MASTER (Cost=1 Card=
+115517 Bytes=13053421)
          INDEX (UNIQUE SCAN) OF SYS_C0017576 (UNIQUE) (Cost=1 Card=11
+5517)
      NESTED LOOPS (Cost=1775 Card=1 Bytes=437)
        NESTED LOOPS (Cost=1774 Card=1 Bytes=324)
          NESTED LOOPS (Cost=1773 Card=1 Bytes=236)
            NESTED LOOPS (Cost=1772 Card=1 Bytes=190)
              TABLE ACCESS (BY INDEX ROWID) OF UCSII_UNIT (Cost=1736 C
+ard=117 Bytes=9243)
                INDEX (RANGE SCAN) OF XIE1UCSII_UNIT (NON-UNIQUE) (Cos
+t=879 Card=117)
              TABLE ACCESS (BY INDEX ROWID) OF UCSI_ITEM (Cost=1 Card=
+1829857 Bytes=203114127)
                INDEX (UNIQUE SCAN) OF XPKUCSI_ITEM (UNIQUE) (Cost=2 C
+ard=1829857)
            INDEX (UNIQUE SCAN) OF XPKUCS_INV (UNIQUE) (Cost=1 Card=55
+8346 Bytes=25683916)
          TABLE ACCESS (BY INDEX ROWID) OF ULT_CUST_SALE (Cost=1 Card=
+60595 Bytes=5332360)
            INDEX (UNIQUE SCAN) OF XPKULT_CUST_SALE (UNIQUE) (Cost=1 C
+ard=60595)
        TABLE ACCESS (BY INDEX ROWID) OF ULT_CUST_MASTER (Cost=1 Card=
+115517 Bytes=13053421)
          INDEX (UNIQUE SCAN) OF SYS_C0017576 (UNIQUE) (Cost=1 Card=11
+5517)
[download]

Please reply.

You can force Oracle to use specific indexes or join methods using hints. It's been a couple of years since I've done heavy Oracle work, but I think it's possible that the 'between' may not use the indexes if it doesn't know how many values it's going to return (as if its returns more than 5-20% (number dependant on oracle version) of the table, it's going to be faster to do a full table scan for a typical table) Generating histograms can help, but if you have skewed data, the optimizer won't know the best situation for the bind variables.

One thing that looks odd to me is that you've got two ranges on the same table, with a different status flag -- if they are the same range, I'd suggest instead using:

WHERE (
    rec_status IN ( 'A', 'I' )
  AND
    ucsiiu_receipt_at_mot_dt BETWEEN TO_DATE( ?, 'mm/dd/yy') AND TO_DA
+TE( ?, 'mm/dd/yy' )
)
[download]

(if they were the same range, Oracle could sort it out when it generates the execution plan ... if it couldn't, it has to do two queries, then merge them, which might mean a sort-merge, which is very slow for large sets)

If this is a common query, you might also make a special index for it, that contains both columns.

Thanks jhourcle, This is a common query. I used two ranges of same table because the range clause is exactly as

AND ((UCSII.REC_STATUS = 'A'
AND UCSII.UCSIIU_RECEIPT_AT_MOT_DT between 
to_date(?,'mm/dd/yyyy') and to_date(?,'mm/dd/yyyy') ) 
-- <1.start date, 2.end date>
OR (UCSII.UCSIIU_RECEIPT_AT_MOT_DT between 
to_date(?,'mm/dd/yyyy') and to_date(?,'mm/dd/yyyy')  
-- <3.start date, 4.end date>
AND UCSII.REC_STATUS = 'I'
AND UCSII.UCSIIU_CIF_SENT_TO_ITA_CNT > 0))
[download]

Can this range be combind into one? This is a common query. Could you please explain me little bit more on special index which you mentioned. both the column mean, which columns? Thanks a lot for your valuable comments. Regards, Ram

Can this range be combind into one?

Based on what you're saying, no. (you may get slightly different performance using a 'UNION ALL', as it doesn't have to perform the sort-merge, but if you're using any aggregate functions, you'll erase the benefit) (you won't get duplicates as REC_STATUS wouldn't match between the two sub-queries)

Could you please explain me little bit more on special index which you mentioned. both the column mean, which columns?

Sure -- instead of specifying indexes in with the individual column definitions, you do the following:

CREATE INDEX index_name ON table_name ( column1, column2 column3 )

(this also allows you to specify an alternate tablespace for your index, to reduce disk contention on writes)

Now, if you run queries that act on those columns, it can use the single index, rather than needing to merge between multiple indexes. However, index design is tricky -- if you're just searching on a single column, this could be worse than a single column index on that value. It's probably more than I can really go into, and this isn't really Perl related anymore ... try searching for 'multicolumn index' or 'composite index')

...

Oh -- and it seems that the use of bind variables and histograms may cause problems I wasn't aware of -- since 9i, Oracle does 'bind variable peeking', (ie, it looks at the bind variables to determine the best execution plan). Unfortunately, it then caches the plan, and it may be optimized for an outlier case (the first one run). Some folks are actually recommending not using histograms on skewed data

Also, please note that this script is running smoothly on test server. The query takes few minutes to execute on test server, however on production this is getting hanged on the step when query is executed.