You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you...
You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you can see that it is beginning to affect your service levels. You suspect that it has a sub-optimal execution plan and you need to get it sorted out immediately. Is there a quick and easy way to do that? Automatic SQL plan management can deal with this type of issue without DBA intervention, but what can you do if this...
You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you can see that it is beginning to...
Occasionally I want to get historical information on histogram changes for a particular table. I have had a script in my back pocket for a long while so I...
Occasionally I want to get historical information on histogram changes for a particular table. I have had a script in my back pocket for a long while so I thought I'd share it in the Oracle Optimizer GitHub repository. My main motivation for doing this is that I still consider the script experimental and I would like feedback from the field. Feel free to comment below, particularly if you see room for improvement or flaws. Perhaps the output could be clearer or maybe there...
Occasionally I want to get historical information on histogram changes for a particular table. I have had a script in my back pocket for a long while so I thought I'd share it in the Oracle Optimizer...
Oracle Database 18c Standard Edition includes a new way to control SQL execution plans. Stored outlines were deprecated in Oracle Database 11g Release 1 and SQL...
Oracle Database 18c Standard Edition includes a new way to control SQL execution plans. Stored outlines were deprecated in Oracle Database 11g Release 1 and SQL plan management (SPM) was only available in Oracle Database Enterprise Edition. This meant that many DBAs continued to use stored outlines in Standard Edition and they continue to work to this day. At some point in the future we will begin to remove support for stored outlines so, to avoid leaving a hole in SE, we...
Oracle Database 18c Standard Edition includes a new way to control SQL execution plans. Stored outlines were deprecated in Oracle Database 11g Release 1 and SQL plan management (SPM) was...
SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in...
SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in question are used more than once). SPM uses SQL plan baselines that are associated with individual SQL statements to control what execution plans they are permitted to use. It’s a simple but powerful idea that opens the door to the possibility of using SQL plan baselines in a more selective and reactive way: to influence...
SQL plan management (SPM) is designed to prevent performance regression for all SQL statements used by an application (assuming that the SQL statements in question are used more than once). SPM uses...
If you’re a DBA, it’s likely that you’ve encountered systems where a lot of SQL statements have been hinted almost as a matter of policy. Perhaps you’d like to...
If you’re a DBA, it’s likely that you’ve encountered systems where a lot of SQL statements have been hinted almost as a matter of policy. Perhaps you’d like to figure out if these hints are actually helping. You might like to demonstrate to a development team that they should probably dial down their enthusiasm for micro-managing the Oracle Optimizer. Sometimes, you might want to apply a hints on-the-fly. A while ago, Maria Colgan wrote a couple of posts (here and here) on...
If you’re a DBA, it’s likely that you’ve encountered systems where a lot of SQL statements have been hinted almost as a matter of policy. Perhaps you’d like to figure out if these hints are actually...
