Search

Top 60 Oracle Blogs

Recent comments

Optimizer

Being generous to the optimizer

In a perfect world, the optimizer would reach out from the server room, say to us: “Hey, lets grab a coffee and have a chat about that query of yours”. Because ultimately, that is the task we are bestowing on the optimizer – to know what our intent was in terms of running a query in a way that meets the performance needs of our applications. It generally does a pretty good job even without the coffee Smile, but if we can keep that caffeine hit in mind, we can do our bit as SQL developers to give the optimizer as much assistance as we can.

Statistics on Object tables

Way back in Oracle 8.0 we introduced the “Object-Relational” database, which was “the next big thing” in the database community back then. Every vendor was scrambling to show just how cool their database technology was with the object-oriented programming paradigm.

Don’t get me wrong – using the Oracle database object types and features associated with them has made my programming life a lot easier over the years. But for me, it’s always been pretty much limited to that, ie, programming, not actually using the object types in a database design as such. Nevertheless, using objects as columns, or even creating tables of objects is supported by the database. For example, I can create a object type of MY_OBJECT (which could itself be made up of objects) and then have a table, not with that object as a column, but actually a table of that object.

Oracle 19c Hint Usage reporting

One reason why we try to avoid hints in our queries is that it is very difficult to use correctly. No error is raised when there’s an incorrect syntax or when the hint cannot be used semantically. 19c dbms_xplan has an important enhancement as it can report hint usage, at least for optimizer hints.

By default, DBMS_XPLAN in the default TYPICAL format will report only invalid hints:

SYS.STATS_TARGET$

Here is a little note about the SYS.STATS_TARGET$ table used by the automatic statistics gathering job run at maintenance window, or when running it manually with:

exec dbms_auto_task_immediate.gather_optimizer_stats

This table is not documented and has no view on it, so those are only my guesses about what I observed, and comments are welcome. Basically, this table is used by the Auto Stats job to list the tables to process, from one execution to the other.

Note that in 12c the same information is updated into DBA_OPTSTAT_OPERATION_TASKS and visible through DBMS_STATS.REPORT_STATS_OPERATIONS. But I still use STATS_TARGET$ so see in real-time what is currently processed.

Columns description

STATUS

When the Auto Stats job lists the objects to process, they are in state PENDING (STATUS=0).

EXPORT not GATHER with DBMS_STATS

Just a short post today on something that came in as a question for the upcoming Office Hours session which I thought could be covered quickly in a blog post without needing a lot of additional discussion for which Office Hours is more suited to.

The question was:

“When I gather statistics using DBMS_STATS, can I just create a statistic table and pass that as a parameter to get the results of the gather”

And the answer simply is “No” Smile but let me clear up the confusion.

You don’t need the PLAN_TABLE table

This post is about the following error you may get when looking at an execution plan after setting the current_schema:

Error: cannot fetch last explain plan from PLAN_TABLE

It is related with old versions and relics from even older versions.

In the old times, PLAN_TABLE was a permanent shared regular table created by utlxplan.sql. Since Oracle 8i which introduced Global Temporary Tables, the PLAN_TABLE public synonym refers to SYS.PLAN_TABLE$ which is a GTT, not shared and emptied at the end of your session.

When I want to tune a query, I usually connect with my DBA user and change my session schema to the application one, so that I can explain or run the user query without having to prefix all tables. But when there is a PLAN_TABLE in the current schema, DBMS_XPLAN.DISPLAY may fail:

Oracle literal vs bind-variable in partition pruning and Top-N queries

Here is a query that retrieves the most recent value for a variable within a time range. The table has the variable ID, the timestamp and the value. All is properly indexed (it is actually an IOT) correctly: index on (VARIABLE_ID, TIMESTAMP, VALUE) locally partitioned on TIMESTAMP.

For whatever reason, the optimizer chooses an index fast full scan. With small partitions, this should not be a problem as we do not need to read all partitions: we want only the last value (rownum=1 after order by desc).

literals

Here is the execution plan with literals:

Another day…another "use the right datatype" post

Here’s an interesting little oddity (aka bug) with scalar queries.

We’ll start with a simple working example


SQL> create table t1 ( c1 number );

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> create table t2 ( c1 int, c2 varchar2(10));

Table created.

SQL>
SQL> insert into t2 values(1,'t1');

1 row created.

SQL> insert into t2 values(1,'t01');

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T1')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','T2')

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select a.c1,
  2    ( select max(b.c2) from t2 b where a.c1 = b.c1 )
  3  from t1 a;

        C1 (SELECTMAX
---------- ----------
         1 t1

1 row selected.

That all seems straightforward:

How to gather Oracle optimizer statistics with minimal risks of regression

The ATLAS experiment control room

Here is, on the Databases at CERN blog, an example to lower to risks when you need to gather statistics in production:

  • use pending stats to be able to test them before publishing
  • be ready to restore old ones if a critical regression comes after publishing

The whole demo (I encourage you to follow the Databases at CERN blog):

https://db-blog.web.cern.ch/blog/franck-pachot/2018-09-optimizer-statistics-gathering-pending-and-history

Here is a summary of commands used, for an easy copy/paste:

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 columns because it lets us establish the rows of interest before having to visit the heap blocks.  Here’s an example of that in action, even when the indexes in question are Text indexes.

Enterprise Edition plan