I was asked to compare SQL plan management auto capture with automatic SQL plan management, so here goes... SQL plan management (SPM) auto capture is enabled by...
I was asked to compare SQL plan management auto capture with automatic SQL plan management, so here goes... SQL plan management (SPM) auto capture is enabled by setting the database parameter optimizer_capture_sql_plan_baselines to TRUE (covered here). Then, by default, all plans for all SQL statements executed in the database more than once are captured. From Oracle Database 12c Release 2 you can specify filters to limit which SQL statements are chosen. This is done...
I was asked to compare SQL plan management auto capture with automatic SQL plan management, so here goes... SQL plan management (SPM) auto capture is enabled by setting the database parameter...
I mentioned in an earlier blog post that in Oracle Database 19c we introduced a hint usage report for DBMS_XPLAN. If you have been using this feature then you...
I mentioned in an earlier blog post that in Oracle Database 19c we introduced a hint usage report for DBMS_XPLAN. If you have been using this feature then you might have noticed that some hints are not reported. We can loosely categorize them as execution hints, and this is a known limitation of the hint usage report. The ones you are most likely to notice are perhaps APPEND, BIND_AWARE and GATHER_OPTIMIZER_STATISTICS. There are some inconsistencies in the reporting of...
I mentioned in an earlier blog post that in Oracle Database 19c we introduced a hint usage report for DBMS_XPLAN. If you have been using this feature then you might have noticed that some hints are...
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 19c adds a new feature called automatic SQL plan management. This post covers: What is automatic SQL plan management and how it works. How to...
Oracle Database 19c adds a new feature called automatic SQL plan management. This post covers: What is automatic SQL plan management and how it works. How to configure. Summary. Where is it available? This is an autonomous feature so it is only available on some platforms. See Automatic SQL Plan Management in table 1-6 of the license guide for full details. If you are not using a supported platform, there are still some great SPM enhancements in Oracle Database 19c for you to...
Oracle Database 19c adds a new feature called automatic SQL plan management. This post covers: What is automatic SQL plan management and how it works. How to configure. Summary. Where is it...
For those of you that want to keep up-to-speed with the latest changes, this post includes a quick summary of the headline new features in Oracle Database 19c...
For those of you that want to keep up-to-speed with the latest changes, this post includes a quick summary of the headline new features in Oracle Database 19c with links to the documentation. In addition, most of the new optimizer white papers have been published. You will find links to them in the new Oracle Query Optimization page (and there will be further updates to this page soon). Automatic Indexing This is a complex and sophisticated piece of functionality but,...
For those of you that want to keep up-to-speed with the latest changes, this post includes a quick summary of the headline new features in Oracle Database 19c with links to the documentation....
A popular enhancement request I see is to provide an easier way to diagnose issues with SQL plan baselines; in particular the situation where a SQL plan...
A popular enhancement request I see is to provide an easier way to diagnose issues with SQL plan baselines; in particular the situation where a SQL plan baseline is ACCEPTED but is not being used for whatever reason. This is rare, but can happen if changes are made to the database such as dropping indexes or changing partitioning schemes. If a SQL plan baseline can't be used, you will see something like this in Oracle Database 19c: So why did it fail? In this example I...
A popular enhancement request I see is to provide an easier way to diagnose issues with SQL plan baselines; in particular the situation where a SQL plan baseline is ACCEPTED but is not being used for...
Since LiveSQL is running on Oracle Database 19c, now is the time to be one of the first to take a look at the new Hint Usage Reporting feature. Trying to figure...
Since LiveSQL is running on Oracle Database 19c, now is the time to be one of the first to take a look at the new Hint Usage Reporting feature. Trying to figure out why a hint is not being used is not on my top-100-fun-things-to-do list. Luckily, there's now a way to do it much more easily than before. Here is a very simple example - but it's just the tip of the iceberg... create table mytab (a number(10) primary key); insert into mytab values (1); commit; exec...
Since LiveSQL is running on Oracle Database 19c, now is the time to be one of the first to take a look at the new Hint Usage Reporting feature. Trying to figure out why a hint is not being used is not...
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...
