RE: Long Parse Times, Huge In Lists
Date: Fri, 23 Oct 2009 19:53:31 +0200
Message-ID: <4814386347E41145AAE79139EAA398980D33FD8059_at_ws03-exch07.iconos.be>
Hi,
Check out metalink note: Doc ID: 744664.1- High Parse Time and Memory Usage for Query with Complex OR Predicates or IN-Lists After Upgrade to 10.2 or 11.1.
I came accross it on one of our client sites.
regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
e-mail: freek.dhooge_at_uptime.be
tel. +32 (0)3 451 23 82
http://www.uptime.be
disclaimer
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Larry G. Elkins [elkinsl_at_flash.net] Sent: 23 October 2009 18:58
To: oracle-l_at_freelists.org
Subject: Long Parse Times, Huge In Lists
Listers,
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-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 23 2009 - 12:53:31 CDT