Query Using Bind Variables Suddenly Starts to Perform Slowly
(Doc ID 387394.1)
Last updated on AUGUST 31, 2023
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterGen 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.
Symptoms
- 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.
Changes
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
- etc
NOTE: the cursor will NOT get moved out by flushing the Shared Pool if it is pinned (ie in use)
Cause
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
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |