Query Using Bind Variables Suddenly Starts to Perform Slowly
(Doc ID 387394.1)
Last updated on MAY 20, 2021
Applies to:Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document