Oakies Blog Aggregator

Hackers are clever people; they must be to hack other people and take over their private data and steal identities and money. I have to draw the limit at the number of hackers who claim to be in my PC....[Read More]

Posted by Pete On 19/05/19 At 10:08 PM

GG, this approach(pathfinder) can be used with GTT. You can fill relevant data in the script.sql before the statement that is taggued.

Here is a simple example of using Mauro Pagano ‘pathfinder’ tool where you don’t really want to run the query, but just get the execution plan with all variations of optimizer settings. That’s something I used many times in situations similar to this one:

• the database was upgraded, say from 11.2.0.4 to 19.3
• one (or a few) SQL statements have problematic performance regression
• the execution plan (in 19.3) is different than from the previous version (11.2.0.4) — you get both with SQL Tuning Sets or AWR
• you set optimizer_features_enable to 11.2.0.4 and the old plan with acceptable performance is back

That’s a quick workaround, thanks to this unique Oracle Optimizer feature which let us run the latest version of the database with a previous version of the optimizer code. But the goal is not to stay long like this. Once the service is made acceptable again with this temporary setting, the second step is to understand which bug or feature is responsible for the change. Then, at least, the workaround can be limited to only one underscore setting instead of the generic optimizer_features_enable which sets hundreds of them. The third step then will be to fix the root cause, of course, and understanding what was wrong will help.

This post is about the second step — going from the general optimizer_features_enable to a unique focused setting.

This is something I wanted to write for a long time but I was always in a rush when encountering this kind of problem. But I’m currently attending Mike Dietrich upgrade workshop at AOUG conference in Vienna and this, the change of execution plan, is addressed by the exercises. Mike exposes the tools that can be used to compare the performance before and after the upgrade: capture the statements and performance statistics and compare them, and to fix them with SQL Plan Management.

The workshop instructions are on Mike’s blog:

HOL 19c - Main Index Page

If you did the workshop you have seen that the query sql_id=13dn4hkrzfpdy has a different execution plan between 11g and 19c and the idea of the lab is to fix the previous plan with a SQL Plan Baseline. That’s perfect, but I was curious about the reason for this execution plan change. There are many new features or fixes between 11.2.0.4 and 19.3 and one is probably responsible for that.

This is where Mauro Pagano ‘pathfinder’ can be used. Setting optimizer_features_enable is a shortcut to set all individual features or fixes, and pathfinder will try each of them one by one.

The query with a plan regression was:

SQL Details:
-----------------------------
 Object ID            : 34
 Schema Name          : TPCC
 Container Name       : Unknown (con_dbid: 72245725)
 SQL ID               : 13dn4hkrzfpdy
 Execution Frequency  : 3273
 SQL Text             : 
SELECT COUNT(DISTINCT (S_I_ID)) FROM ORDER_LINE, STOCK
 WHERE OL_W_ID = :B2 AND OL_D_ID = :B4 AND (OL_O_ID < :B3
 ) AND OL_O_ID >= (:B3 - 20) AND S_W_ID = :B2 AND S_I_ID =
 OL_I_ID AND S_QUANTITY < :B1

The plan before and after, as reported by AWR Diff Report are the following:

And my goal is to understand which feature or fix control, when disabled, gets back to the plan hash value 954326358 instead of 3300316041

I installed sqldb360 (open sourced by Carlos Sierra and Mauro Pagano), which contains pathfinder:

git clone https://github.com/sqldb360/sqldb360.git
cd ./sqldb360/sql/

I changed the script.sql to put my query there with an EXPLAIN PLAN because I don’t want to execute it (which would require parameters):

alter session set current_schema=TPCC;

explain plan for
 SELECT  /* ^^pathfinder_testid */
  COUNT(DISTINCT (S_I_ID)) FROM ORDER_LINE, STOCK
  WHERE OL_W_ID = :B2 AND OL_D_ID = :B4 AND (OL_O_ID < :B3
  ) AND OL_O_ID >= (:B3 - 20) AND S_W_ID = :B2 AND S_I_ID =
  OL_I_ID AND S_QUANTITY < :B1

By default, pathfinder executes the query and gets the execution plan with dbms_xplan.display_cursor, using the tag in the comment to identify it.

Here I’m doing an EXPLAIN PLAN and then I changed the pathfinder.sql to use dbms_xplan.display. My change in the ‘xplan driver’ is the following:

I’ve left the old query, but add the following one to be executed:

-- my addition there
PRO .
PRO SELECT RPAD('explain plan', 11) inst_child, plan_table_output
PRO FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ADVANCED'))
-- done
PRO /

Then running pathfinder:

[oracle@hol]$ sqlplus / as sysdba @ pathfinder.sql '"/ as sysdba"'

This takes some time to test all settings for optimizer underscore parameters (632 ones here in 19.3) and fix controls (1459 here):

The result is a zip file containing an index and the detail of each test.

The index (00001_pathfinder_upgr_20190515_1113_index.html) has one line per combination and it is easy to search from the plan hash value:

My old plan is chosen when _optimizer_partial_join_eval is set to false:

And now, I have a better workaround. Instead of setting the optimizer_feature_enable, I can set only:

ALTER SESSION SET "_optimizer_partial_join_eval" = FALSE;

Of course, my search for the plan hash value also highlights which versions set the same:

The goal of this post is to show the tool. If you want to know more about Partial Join Evaluation, Google tells me that I blogged about this in the past:

Partial Join Evaluation in Oracle 12c - Blog dbi services

The query here, a count(distinct) on a join, is subject to this optimization which changes the join to a semi-join.

If I can change the query, maybe I’ll prefer to disable it with a hint. If I click on the baseline plan from the pathfinder index, I can see the plan with hints:

Then probably a NO_PARTIAL_JOIN can disable this feature.

Side remark: you can see OPTIMIZER_FEATURES_ENABLE(‘19.1.0') but I told you that I’m on 19.3, right? And that this is the pathfinder baseline without any session setting. I didn’t expect 19.3 there because Release Updates should not add features that change the execution plan. But I expected something like ‘19.0.0’. The magic of the new release model…

In summary:

• Pathfinder is easy to run, give it a try when you need to understand why an execution plan has changed.
• Do the Mike Dietrich hands-on lab: upgrade is something to exercise before doing it in production.
• Since 8i, the Oracle Optimizer developers add a flag for any change, in order to give us the possibility to enable or disable the feature or the fix. And you control it at instance, session or query level. This is a unique feature you do not find on other database systems. And it can save your business because a critical regression can always happen after an upgrade.
• AOUG conference had a great idea with the ‘workshop and live-demo’ day before the conference day. Fewer attendees and more interaction with the speakers.

Indexing null values in Oracle is something that has been written about a lot in the past already. Nowadays it should be common knowledge that Oracle B*Tree indexes don't index entries that are entirely null, but it's possible to include null values in B*Tree indexes when combining them with something guaranteed to be non-null, be it another column or simply a constant expression.

Jonathan Lewis not too long ago published a note that showed an oddity when dealing with IS NULL predicates that in the end turned out not to be a real threat and looked more like an oddity how Oracle displays the access and filter predicates when accessing an index and using IS NULL together with other predicates following after.

However, I've recently come across a rather similar case where this display oddity turns into a real threat. To get things started, let's have a look at the following (this is from 18.3.0, but other recent versions should show similar results):

#eeeeee; border: 1px dashed rgb(204 , 204 , 204); overflow: auto;">SQL> create table null_index as select * from dba_tables;

Table created.

SQL> insert /*+ append */ into null_index select a.* from null_index a, (select /*+ no_merge cardinality(100) */ rownum as dup from dual connect by level <= 100);

214700 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(null, 'NULL_INDEX', method_opt => 'for all columns size auto for columns size 254 owner')

PL/SQL procedure successfully completed.

SQL> create index null_index_idx on null_index (pct_free, ' ');

Index created.

SQL> set serveroutput off pagesize 5000 arraysize 500

Session altered.

SQL> set autotrace traceonly
SQL>
SQL> select * from null_index where pct_free is null and owner in ('AUDSYS', 'CBO_TEST');

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3608178030

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |    19 |  5852 |  1028   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| NULL_INDEX     |    19 |  5852 |  1028   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | NULL_INDEX_IDX | 13433 |       |    32   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='AUDSYS' OR "OWNER"='CBO_TEST')
   2 - access("PCT_FREE" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2178  consistent gets
         35  physical reads
          0  redo size
       7199  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        101  rows processed

So this is the known approach of indexing null values by simply adding a constant expression and we can see from the execution plan that indeed the index was used to identify the rows having NULLs.

But we can also see from the execution plan, the number of consistent gets and also the Rowsource Statistics that this access can surely be further improved:

#eeeeee; border: 1px dashed rgb(204 , 204 , 204); overflow: auto;">Plan hash value: 3608178030

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |  1028 (100)|    101 |00:00:00.01 |    2178 |     35 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| NULL_INDEX     |      1 |     19 |  1028   (1)|    101 |00:00:00.01 |    2178 |     35 |
|*  2 |   INDEX RANGE SCAN                  | NULL_INDEX_IDX |      1 |  13433 |    32   (0)|  13433 |00:00:00.01 |      30 |     35 |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("OWNER"='AUDSYS' OR "OWNER"='CBO_TEST'))
   2 - access("PCT_FREE" IS NULL)

Because the additional predicate on OWNER can only be applied on table level, we first identify more than 13,000 rows on index level, visit all those table rows via random access and apply the filter to end up with the final 101 rows.

So obviously we should add OWNER to the index to avoid visiting that many table rows:

#eeeeee; border: 1px dashed rgb(204 , 204 , 204); overflow: auto;">SQL> create index null_index_idx2 on null_index (pct_free, owner);

Index created.

SQL> select * from null_index where pct_free is null and owner in ('AUDSYS', 'CBO_TEST');

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3808602675

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |    19 |  5852 |    40   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| NULL_INDEX      |    19 |  5852 |    40   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | NULL_INDEX_IDX2 |    19 |       |    38   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PCT_FREE" IS NULL)
       filter("OWNER"='AUDSYS' OR "OWNER"='CBO_TEST')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        137  consistent gets
         61  physical reads
          0  redo size
      33646  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        101  rows processed

Plan hash value: 3808602675

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |    40 (100)|    101 |00:00:00.01 |     137 |     61 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| NULL_INDEX      |      1 |     19 |    40   (0)|    101 |00:00:00.01 |     137 |     61 |
|*  2 |   INDEX RANGE SCAN                  | NULL_INDEX_IDX2 |      1 |     19 |    38   (0)|    101 |00:00:00.01 |      36 |     61 |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PCT_FREE" IS NULL)
       filter(("OWNER"='AUDSYS' OR "OWNER"='CBO_TEST'))

So at first sight this looks indeed like an improvement, and it is compared to the previous execution plan, see for example how the number of consistent gets has been reduced. However, there is something odd going on: The index cost part is even greater than in the previous example, and looking more closely at the predicate information section it becomes obvious that the additional predicate on OWNER isn't applied as access predicate to the index, but only as filter. This means rather than directly identifying the relevant parts of the index by navigating the index structure efficiently using both predicates, only the PCT_FREE IS NULL expression gets used to identify the more than 13,000 corresponding index entries and then applying the filter on OWNER afterwards. While this is better than applying the filter on table level, it still can become a very costly operation and the question here is, why doesn't Oracle use both expressions to access the index? The answer to me looks like an implementation restriction - I don't see any technical reason why Oracle shouldn't be capable of doing so. Currently it looks like that in this particular case when using an IN predicate or the equivalent OR predicates following an IS NULL on index level gets only applied as filter, similar to predicates following range or unequal comparisons, or skipping columns / expressions in a composite index. But for those cases there is a reason why Oracle does so - it no longer can use the sorted index entries for efficient access, but I don't see why this should apply to this IS NULL case - and Jonathan's note above shows that in principle for other kinds of predicates it works as expected (except the oddity discussed).

This example highlights another oddity: Since it contains an IN list, ideally we would like to see an INLIST ITERATOR used as part of the execution plan, but there is only an INDEX RANGE SCAN operation using this FILTER expression.

By changing the order of the index expressions and having the expression used for the IS NULL predicate as trailing one, we can see the following:

#eeeeee; border: 1px dashed rgb(204 , 204 , 204); overflow: auto;">SQL> create index null_index_idx3 on null_index (owner, pct_free);

Index created.

SQL> select * from null_index where pct_free is null and owner in ('AUDSYS', 'CBO_TEST');

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2178707950

--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |    19 |  5852 |     6   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                     |                 |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| NULL_INDEX      |    19 |  5852 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | NULL_INDEX_IDX3 |    19 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(("OWNER"='AUDSYS' OR "OWNER"='CBO_TEST') AND "PCT_FREE" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        108  consistent gets
         31  physical reads
          0  redo size
      33646  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        101  rows processed

Plan hash value: 2178707950

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |      1 |        |     6 (100)|    101 |00:00:00.01 |     108 |     31 |
|   1 |  INLIST ITERATOR                     |                 |      1 |        |            |    101 |00:00:00.01 |     108 |     31 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| NULL_INDEX      |      2 |     19 |     6   (0)|    101 |00:00:00.01 |     108 |     31 |
|*  3 |    INDEX RANGE SCAN                  | NULL_INDEX_IDX3 |      2 |     19 |     4   (0)|    101 |00:00:00.01 |       7 |     31 |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access((("OWNER"='AUDSYS' OR "OWNER"='CBO_TEST')) AND "PCT_FREE" IS NULL)

So this is the expected execution plan, including an INLIST ITERATOR and showing that all predicate expressions get used to access the index efficiently, reducing the number of consistent gets further. Of course, a potential downside here is that this index might not be appropriate if queries are looking for PCT_FREE IS NULL only.

Summary

It looks like that IN / OR predicates following an IS NULL comparison on index level are only applied as filters and therefore also prevent other efficient operations like inlist iterators. The problem in principle can be worked around by putting the IS NULL expression at the end of a composite index, but that could come at the price of requiring an additional index on the IS NULL expression when there might be the need for searching just for that expression efficiently.

In part 2 for curiosity I'll have a look at what happens when applying the same to Bitmap indexes, which include NULL values anyway...

Script used:

#eeeeee; border: 1px dashed rgb(204 , 204 , 204); overflow: auto;">set echo on

drop table null_index purge;

create table null_index as select * from dba_tables;

insert /*+ append */ into null_index select a.* from null_index a, (select /*+ no_merge cardinality(100) */ rownum as dup from dual connect by level <= 100);

commit;

exec dbms_stats.gather_table_stats(null, 'NULL_INDEX', method_opt => 'for all columns size auto for columns size 254 owner')

create index null_index_idx on null_index (pct_free, ' ');

set serveroutput off pagesize 5000 arraysize 500

set autotrace traceonly

select * from null_index where pct_free is null and owner in ('AUDSYS', 'CBO_TEST');

create index null_index_idx2 on null_index (pct_free, owner);

select * from null_index where pct_free is null and owner in ('AUDSYS', 'CBO_TEST');

create index null_index_idx3 on null_index (owner, pct_free);

select * from null_index where pct_free is null and owner in ('AUDSYS', 'CBO_TEST');