Long Parse Times, Huge In Lists

10.2.0.4 EE on Aix 5.3.

Asked to look at a new COTS application in development environment. Huge in-lists, 1000's of literal values ("column_name in (1,2,3..1000) or column name in (1001,1002,...2000) or ..." ). Surrogate non-meaningful values in the list, most probably building the statement within a cursor (hey, databases can join tables!!!). Anyway, tracing shows typical parse times of 20 to 200 seconds. Execute and fetch < 0.5 seconds.

So, considering we cannot change the code, and it is unlikely we can get the vendor to change the code, any strategies for speeding up the parse times?

I'm not familiar with this database but it looks like it is a pretty generic out of the box setup database wise, complete with automatic stats gathering job. Tested removing histograms to minimize evaluation paths, no real improvement. Cursor sharing tried, sometimes gets a hit and avoids the hard parse. Very hit or miss in this case.

ASMM is used, and in some cases a resize operation of 5+ minutes gets triggered, during the parse so then the bulk of the time in the trace is on "latch: shared pool" while resize is occurring, parse time still shows 20 - 200 seconds, and then 5+ minutes on the shared pool latch. Considering turning off ASMM, or at least working the min size on various components, probably the former. But that only addresses the occasional case where it triggers a long resize operation, and not the typical 20-200 second parse cases.

So, anything that can be done to reduce the parse times? When it is in-house code, we have all kinds of options to eliminate the huge in-list altogether. But being a COTS application, that's off the table, for now anyways.

Larry G. Elkins
elkinsl_at_flash.net

--
http://www.freelists.org/webpage/oracle-l