Query Using Bind Variables Suddenly Starts to Perform Slowly
Last updated on OCTOBER 12, 2017
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 and later
Information in this document applies to any platform.
Checked for relevance on 09-May-2013
- 9.x or above
- sometimes, for no apparent reason, some SQL which has been running fine suddenly runs very poorly.
- There are no changes to the data, the SQL, or the statistics for the objects involved.
- On further examination of the SQL, it can be seen that it is using bind variables.
The cursor has been re-parsed for some reason and a new plan has been generated.
There are many reasons why this might occur, not least the following:
- database shutdown/restart
- cursor not in use by any session, and aged out by LRU algorithm
- change to the stats associated with any referenced object (eg following a gather stats job)
- change to the structure of any referenced object (eg alter table)
- granting/revoking privileges on a referenced object
- gathering of new statistics unless NO_INVALIDATE is set to true
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms