in reply to Excessive execution time passing parameters to Oracle

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.

Replies are listed 'Best First'.
Re^2: Excessive execution time passing parameters to Oracle
by Anonymous Monk on Sep 26, 2006 at 09:45 UTC
    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
[reply]
[d/l]
      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

[reply]
[d/l]
      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.
[reply]

In Section Seekers of Perl Wisdom