Execution Plan Changes I am often asked: Why do my SQL execution plans keep changing, and why are they sometimes so bad? Is there a bug? I have upgraded a...
Execution Plan Changes I am often asked: Why do my SQL execution plans keep changing, and why are they sometimes so bad? Is there a bug? I have upgraded a database, but some SQL statements perform poorly compared to the previous release. There are many possible reasons for plan changes. Still, an essential factor to consider is the possibility that one or more cardinality estimates (used during optimization) are inaccurate, even though optimizer statistics are fresh....
Execution Plan Changes I am often asked: Why do my SQL execution plans keep changing, and why are they sometimes so bad? Is there a bug? I have upgraded a database, but some SQL statements...
The Oracle Database 21c database initialisation parameter optimizer_real_time_statistics has been backported to Oracle Database 19c RU 10. The real-time...
The Oracle Database 21c database initialisation parameter optimizer_real_time_statistics has been backported to Oracle Database 19c RU 10. The real-time statistics feature is available of some platforms (as specified in the licensing manual). It is now disabled by default in Oracle Database 19c RU10 on-premises Exadata systems, but it can be enabled using the new parameter. This means that if you are... Upgrading an on-premises Exadata platform to Oracle Database RU 19.10...
The Oracle Database 21c database initialisation parameter optimizer_real_time_statistics has been backported to Oracle Database 19c RU 10. The real-time statistics feature is available of some...
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...
