Query Using Bind Variables Suddenly Starts to Perform Slowly
(Doc ID 387394.1)
Last updated on AUGUST 04, 2018
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 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
NOTE: the cursor will NOT get moved out by flushing the Shared Pool if it is pinned (ie in use)
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!