Tag: optimizer

Standard Edition–different optimizer but still cool

One cool technique that the optimizer can employ is the BITMAP CONVERSION TO ROWIDS method to take advantage of B-tree indexes in a means that we would normally associate with a bitmap index. This can be particularly useful with multiple predicates on individually indexed…

Hybrid histograms

Just a quick post here so I could consolidate some information about histograms in 12c. On my last Office Hours session, one of the questions that was posed was asking for an explanation of the new types of histograms in use in 12c.  So…

Execution plans on LiveSQL

To protect the integrity of people’s data, and isolate sessions on LiveSQL, we lock down the environment.  Clearly if you are doing some testing with sensitive data, you don’t want an anonymous member of the user community mining V$SQL to see what commands you…

12c Statistics on load–special cases

One of the cool features in 12c is the automatic collection of optimizer statistics when a table is either created or loaded via direct path from empty.  This makes a lot of sense because it saves us from what used to be the mandatory…

AskTOM TV episode 8

On AskTOM episode 8, I’ve taken a look at locating the SQL Plan Directives used for a particular query.  Here is the script output from the video if you want to use this for your own exploration SQL> SQL> create table t as 2…

Subtle variations in optimizer stats

Subtle variances in the way you work with statistics can have a significant impact on how your optimizer plans work out…so you need to take care. Let’s look at the following example SQL> create table T ( 2 x varchar2(20) , y varchar2(100)); Table…

TOP-N histograms on 12c

I had an interesting question on AskTom today, with a nicely provided test case !!!,  so I thought I’d share it here with some additional commentary. The basic premise of the TOP-N histogram in 12c is that when the number of distinct values in…

Common GATHER_PLAN_STATISTIC confusion

Most people already know about the very cool GATHER_PLAN_STATISTICS hint.  If not, you can see an example here But here’s a common cause of confusion when using it in SQL Plus: SQL> select /*+ gather_plan_statistics*/ count(p) from t where x > sysdate – 30;…

SAMPLE costing

People often think when using the SAMPLE clause, that because they are only dealing with a subset of the data, that immediately it should be a much faster operation. And whilst sometimes this is indeed the case, it is not a guarantee. The optimizer…

Optimizer curiosity in 12.1.0.2

For almost as long as I can remember, the optimizer has had a nifty little trick when you (in effect) try to combine two different usage models within a single SQL.  To explain that, I’m referring to the common scenario of: “If a bind…

WHEN OTHERS … sometimes just not enough

We currently are working on a bug with Oracle Support with dbms_stats in 12.1.0.2. That may be the subject of a latter post, but in a nutshell, here’s the issue SQL> exec dbms_stats.gather_table_stats(‘MY_SCHEMA’, ‘MY_SCHEMA’); BEGIN dbms_stats.gather_table_stats(‘MY_SCHEMA’, ‘MY_SCHEMA’); END; * ERROR at line 1: ORA-21700:…

The challenge of optimization

With every release of Oracle, more and more power comes to the optimizer. Many of these are new features (such as adaptive cursor sharing, adaptive optimization, dynamic sampling, etc)…but also within the “core” of the optimizer, there are continuing efforts to transform and interrogate…