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. Why it is...
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. Why it is important for you to be aware of it. How to configure. Summary. What is it? Automatic SQL plan management identifies SQL statements that consume significant system resources and, in addition, they have been observed by the database to be using multiple SQL execution plans, some apparently more optimal than others....
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. Why it is important for you to be aware of...
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...
