Mohamed Houri’s Oracle Notes

July 30, 2019

Global Temporary Table private statistics

Filed under: CBO — hourim @ 6:27 pm

Abstract

There is one Oracle running system launching simultaneously 49 sessions doing the same business for distinct partitions and distinct ranges of employees so that there will never be any collision in this process. However, this application uses bind variables and is, therefore, confronted to the classical performance threat of sharing the same execution plan between a set of bind variables not necessarily dealing with the same volume of data. This application was upgrading from 11gR2 to 12cR1. I was then asked to find a solution so that execution plans will not be shared between those 49 sessions.

I know that Adaptive Cursor Sharing has been implemented for such kind of situations. But I know also that the transition from a bind sensitive to a bind aware cursor depends on a combination of executions done at different bind variable values which makes it very difficult to guaranty each execution will have its proper optimized execution plan. So, I immediately ruled out this option. However, when I looked at one of those shared execution plans, I realized that those 49 sessions are extensively using global temporary table (GTT). This observation gave me an idea which reveals later to be very useful: use the cursor invalidation introduced by the new 12c GTT SESSION PRIVATE Statistics to force the CBO hard parsing a new execution plan for each of the 49 sessions.

Session PRIVATE statistics

Very often the Cost Based Optimizer does a wrong cardinality estimation when visiting Global Temporary Tables (GTT). This is particularly true when multiple sessions are visiting the same GTT but using different number of rows per session. In this case, sharing GTT statistics between sessions leads, generally, to a performance pain. Dynamic sampling at its default value might also reveal to be not sufficient to help the CBO in this situation. The application locks the statistics of the 11gR2 temporary tables used during the 49 sessions, so that it becomes impossible to see one session propagates its temporary table statistics to all other sessions. But this was before 12c. Starting from 12c, Oracle introduces a global statistics preference which makes possible to have session-private statistics for temporary tables:

SQL> @getPrefs
Enter value for preference: GLOBAL_TEMP_TABLE_STATS 
Enter value for tablename: 
PREFS 
-------- 
SESSION

Therefore, I decided to:

• Let each session has its proper GTT private SESSION statistics
• Use the cursor invalidation due to this GTT private statistics to avoid sharing execution plan between sessions

I started by unlocking the statistics of a couple of GTT tables involved in the 49 sessions DML queries as follows:

  SQL> execute dbms_stats.unlock_table_stats('SYSADM',T1_GTT_TEMP);

Parsing issue

Once I have implemented this change (unlock GTT statistics and use the default SESSION global parameter) I immediately realized that the 49 sessions were not anymore suffering from a performance issue due to execution plan sharing.But, as you might suspect, this doesn’t come free of charge. Indeed, I have spotted out new wait events appearing near the TOP 10 foreground wait events:

• cursor: pin S wait on X 
• library cache lock

These two wait events are symptoms of parsing effect.

While the 49 sessions were still running I issued the following query to get the SQL_ID suffering from this parsing issue:

SQL> select 
         sql_id
	 ,count(1) 
     from 
	  gv$active_session_history 
     where sample_time between to_date('05022019 15:29:00', 'ddmmyyyy hh24:mi:ss') 
	                      and to_date('05022019 16:00:00', 'ddmmyyyy hh24:mi:ss') 
     and event = 'cursor: pin S wait on X' 
     group by sql_id order by 2 desc fetch first 5 rows only
	
SQL_ID        COUNT(1) 
------------- ---------- 
5wgr71p4aj10v   28204
6fas07f6nptcu    6415 
                 5748 
fy64nh1g4ucxp    3063 
9rhtk5vh3qkjg    2394

I then tried to check why the above red bolded SQL_ID is being hard parsed so many times

SQL> @gv$sql 
      Enter value for sql_id: 5wgr71p4aj10v 
	  
	  SQL_ID 		CHILD_NUMBER TO_CHAR(P.LAST_ACTI EXECUTIONS END_FETCH  INVALIDATIONS OBJECT_STATUS
	  ------------- ------------ ------------------- ---------- ---------- ------------- -------------- 
	  5wgr71p4aj10v 0            05/02/2019 15:39:30         0        0               8 VALID 
	  5wgr71p4aj10v 1            05/02/2019 15:36:49         1        1               9 INVALID_UNAUTH 
	  5wgr71p4aj10v 2            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 3            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 4            05/02/2019 15:39:30         0        0               7 VALID 
	  5wgr71p4aj10v 5            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 6            05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 7            05/02/2019 15:39:30         0        0               5 VALID
	  5wgr71p4aj10v 8            05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 9            05/02/2019 15:39:30         0        0               5 VALID 
	  5wgr71p4aj10v 10           05/02/2019 15:36:10         1        1               6 INVALID_UNAUTH 
	  5wgr71p4aj10v 11           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 12           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 13           05/02/2019 15:39:30         0        0               5 VALID
	  5wgr71p4aj10v 14           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 15           05/02/2019 15:37:18         1        1               5 INVALID_UNAUTH 
	  5wgr71p4aj10v 17           05/02/2019 15:39:29         0        0               3 VALID 
	  5wgr71p4aj10v 18           05/02/2019 15:39:30         0        0               3 VALID 
	  5wgr71p4aj10v 19           05/02/2019 15:39:30         0        0               2 VALID 
	  5wgr71p4aj10v 20           05/02/2019 15:39:30         0        0               2 VALID 
	  5wgr71p4aj10v 21           05/02/2019 15:39:30         0        0               2 VALID 

Notice the high number of invalidated cursors (several in about 1 min). The INVALID_UNAUTH status means that the underlying child cursor will not be shared the next time and will be aged out when there is a stress on the library cache.

The reason of this frequent invalidation is shown here below:

SQL> @nonshared 
     Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

	 Enter value for 1: 5wgr71p4aj10v 
	 SQL_ID           : 5wgr71p4aj10v 
	 DDRESS           : 0000000308AB25B8 
	 CHILD_ADDRESS    : 00000002FCDABB40 
	 CHILD_NUMBER     : 0 
	 REASON           :<reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0 
	 ----------------- 
	 SQL_ID           : 5wgr71p4aj10v 
	 ADDRESS          : 0000000308AB25B8 
	 CHILD_ADDRESS    : 00000002FC680A48 
	 CHILD_NUMBER     : 1 
	 REASON           : <reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0
	 ----------------- 
	 ../.. 
	 SQL_ID           : 5wgr71p4aj10v 
	 ADDRESS          : 0000000308AB25B8
	 CHILD_ADDRESS    : 0000000317274F10 
	 CHILD_NUMBER     : 22 
	 REASON           : <reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0
	 ----------------- 

The Session Specific Cursor Session Mismatch reason indicates that Oracle has decided to invalidate the current cursor and to optimize a new execution plan because the current session is not allowed to use the GTT statistics of another session. It is crystal clear that when using SESSION PRIVATE statistics for GTT Oracle will not share the same child cursor that was optimized from another session. Each session will want to have its proper execution plan. it tries to pin the parent cursor and finds that it has been already pinned out by a preceding session. It then starts waiting on this famous wait event: cursor: pin S wait on X
It is important also to remember that Oracle gives us an information via the Note, at the bottom of the execution plan, when it is using a GTT private session statistics as shown below:

Note 
----- 
-- Global temporary table session private statistics used

Conclusion
This brief note shows, via a practical example taken from a running system, that we can use the 12c SESSION PRIVATE statistics to achieve two goals at least:

• Have each session with its proper GTT statistics without propagating them to another session
• Use the cursor invalidation side effect induced by the underlying code of the GTT SESSION PRIVATE statistics to avoid sharing execution plan between sessions

All things being equal you must balance between the performance improvement brought by this new GTT feature and the parsing side effect it introduces because of the underlying cursor invalidation. In my client case
the Library cache and Cursor Pin S wait on X wait events introduced by the SESSION PRIVATE statistics largely outweigh the performance penalty that comes when the 49 streams share the same GTT statistics.

Advertisements

July 28, 2019

Hybrid-Height Balanced corrupted histogram – Part II

Filed under: CBO — hourim @ 7:58 am

Setting the Scene

At one client site, using PeopleSoft software for the employee’s payroll, I was asked to look at a weird performance issue occurring in an overnight batch. This batch launches 49 different Oracle sessions. Each session starts a bunch of DML queries. The data model has been engineered so that any session will treat a different range of employees. To accomplish this workload distribution, the main tables have been range/list partitioned. 49 range partitions with 58 list sub-partitions per partition. The range partition key identifies the employee id and is of varchar2(11) datatype. There is a skew in both the partition key values distribution inside a single partition and in the number of rows per partition for the whole table. The partition key has a HYBRID histogram as we are in 12cR1 under an Exadata machine.

The last time I did intervene for this batch, it was to find a solution so that execution plans will not be shared between the 49 sessions. Indeed, the skew of the number of rows(num_rows) in the range and list sub-partitions makes sharing the same execution plan a real performance threat. I will blog about how I achieved this goal in a couple of weeks or so.

In the current case the performance problem turned to be due to a wrong cardinality estimation because of corrupted Height-Balanced-Hybrid histogram as explained in part I of this mini-series on corrupted histogram. When visiting a single partition, the CBO underestimates the number of rows it will scan and ends up by using a dramatic NESTED LOOPS operation. But this was the case only for the single partitions having a corrupted histogram at its corresponding partition key. In this article I will show how I have fixed this HYBRID histogram corruption simply by deleting the statistics of the concerned partitions and regathering them.

The Scene in details

The part of the complex query where Oracle underestimates the cardinality of the single range partition resembles to this:

SQL> select /*+ full(a) */
        count(1)
     from
        PS_XX_YY_ZZZ_STAT a
     where EMPLID >= 'XX999501004'
     and EMPLID >== 'XX999562000';
 
COUNT(1)
---------
546461
 
Plan hash value: 2020349843
-------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows | Pstart| Pstop |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                   |      |       |       |
| 1 |  SORT AGGREGATE              |                   |    1 |       |       |
| 2 |   PARTITION RANGE SINGLE     |                   |   91 |    41 | 41    |
| 3 |    PARTITION LIST ALL        |                   |   91 |     1 | 58    |
|*4 |    TABLE ACCESS STORAGE FULL | PS_XX_YY_ZZZ_STAT |   91 |  2321 | 2378  |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage(("EMPLID">='XX999501004' AND "EMPLID"<='XX999562000'))
    filter(("EMPLID">='XX999501004' AND "EMPLID"<='XX999562000'))

We can see that the cardinality estimation is wrong since Oracle estimates 91 rows while the actual generated rows are 546461. Let’s try to figure out why Oracle is underestimating this full table scan cardinality.
Since Oracle has identified a single partition (41) to visit it has then necessarily used the statistics of this partition when estimating the single column table selectivity. Below are listed the statistics (num_rows) at global and partition level for the range/list PS_XX_YY_ZZZ_STAT partitioned table:

SQL> @getPartTab
Enter value for owner: sysadm
Enter value for table_name: PS_XX_YY_ZZZ_STAT

TABLE_NAME            PNAME                   PPOS    SUBPARTITION_COUNT NUM_ROWS
-------------------- ------------------------ ------ ------------------ ----------
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_1    1               58         5811088
../..                                                         
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_41   41              58         568804      
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_42   42              58         527248
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_43   43              58         854141
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_44   44              58         809086
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_45   45              58         697071
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_46   46              58         484041
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_47   47              58         887009
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_48   48              58         259714
PS_XX_YY_ZZZ_STAT    PS_XX_YY_ZZZ_STAT_T_49   49              58         1025587
                                                                        ----------
Total num_rows                                                           38434347

As you can see the 41st partition has 568K rows while globally the whole table contains 38,4M rows. At this stage of the investigation we have got the first piece of the puzzle: num_rows (partition 41) = 568804
The second step is to check the EMPLID predicate column statistics at the 41st partition level:


col column_name    format a10
col partition_name format a35
col low_value      format a15
col high_value     format a15

SQL> select
       partition_name
      ,num_distinct
      ,utl_raw.cast_to_varchar2(low_value) low_value
      ,utl_raw.cast_to_varchar2(high_value) high_value
      ,density
      ,histogram
    from
      all_part_col_statistics
    where owner = 'SYSADM'
    and table_name = 'PS_XX_YY_ZZZ_STAT'
    and column_name = 'EMPLID'
    and partition_name in ('PS_XX_YY_ZZZ_STAT_T_41');

PARTITION_NAME             NUM_DISTINCT LOW_VALUE HIGH_VALUE            DENSITY    HISTOGRAM
------------------------- ------------ --------------- --------------- ---------- -----------
PS_XX_YY_ZZZ_STAT_T_41      6278         XX999498003    XX999561990     ,000159    HYBRID

We see that this column has a HYBRID histogram which, as you know, follows a different cardinality estimation formula depending on whether it is:
• A popular value
• A non-popular with an endpoint number
• A non-popular without an endpoint number

Let’s then check in what of the above three situations we are:

SQL> select
		partition_name
		,bucket_number
		,endpoint_actual_value
		,endpoint_repeat_count
		,bucket_size
		,case when Popularity > 0 then 'Pop'
		else 'Non-Pop'
		end Popularity
    from
		(select
			uth.partition_name
			,uth.bucket_number
			,uth.endpoint_actual_value
			,uth.endpoint_repeat_count
			,ucs.sample_size/ucs.num_buckets bucket_size
			,(uth.endpoint_repeat_count - ucs.sample_size/ucs.num_buckets) Popularity
		 from
		   all_part_histograms uth
		   ,all_part_col_statistics ucs
		 where
			uth.table_name = ucs.table_name
			and uth.column_name = ucs.column_name
			and uth.owner = ucs.owner
			and uth.owner = 'SYSADM'
			and uth.table_name = 'PS_XX_YY_ZZZ_STAT'
			and uth.partition_name in ('PS_XX_YY_ZZZ_STAT_T_41')
			and uth.column_name = 'EMPLID'
			and uth.endpoint_actual_value in ('XX999501004','XX999562000')
		)
    order by 
	partition_name, bucket_number;
	
no rows selected

It is a non-popular value without an endpoint number which then normally obeys to the following cardinality estimation formula:

   Card = num_rows * NewDensity = 568804 * .000159 = 90.439836

According to the above computed cardinality, you might think that Oracle has used the HYBRID histogram to estimate the wrong 90.43 ~ 91 cardinality. But, in fact, Oracle has completely ignored the histogram as proved by the corresponding 10053 trace file:

Column (#1): EMPLID(VARCHAR2) Part#: 0
AvgLen: 12 NDV: 267112 Nulls: 0 Density: 0.000004 Min: 0.000000 Max: 0.000000
Histogram: HtBal #Bkts: 5866 UncompBkts: 5866 EndPtVals: 0 ActualVal: yes

NOTE: ignoring histogram of column (PS_XX_YY_ZZZ_STAT.EMPLID) --->
used only for incremental stats maintenance -->

#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
Column (#1): EMPLID(VARCHAR2) Part#: 0

AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159 Min: 0.000000 Max: 0.000000 Histogram: HtBal #Bkts: 5308 
UncompBkts: 5308 EndPtVals: 0 ActualVal: yes

NOTE: ignoring histogram of column (PS_XX_YY_ZZZ_STAT.EMPLID)
used only for incremental stats maintenance Column (#1): EMPLID(VARCHAR2) Part#: 40 AvgLen: 12 
NDV: 6278 Nulls: 0 Density: 0.000159
Column (#1): EMPLID(VARCHAR2)
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159
Table: PS_XX_YY_ZZZ_STAT Alias: A Card: Original: 568804.000000 Rounded: 91 
          Computed: 90.602740 Non Adjusted: 90.602740

Oracle is clearly showing that it has ignored the histogram for the column EMPLID and that it will use it only for incremental statistics maintenance.

Moreover, there is another oddity in the above 10053 trace file:

Histogram: HtBal #Bkts: 5308 UncompBkts: 5308 EndPtVals: 0 ActualVal: yes

See how Oracle CBO trace is looking at Height Balanced histogram while the statistics clearly shows that the EMPLID column has a HYBRID histogram?

I think that the histograms of this column at this partition are corrupted and therefore Oracle has ignored them and fall back to the cardinality estimation of a column without histogram:

Card = num_rows/NDV = 568804/6278 = 90.60273972 –-> exactly the value computed by Oracle

By the way we can know that Oracle will ignore the histogram, beforehand, just by selecting the NOTES column from
the all_part_col_statistics table as shown below:

SQL> select
	 partition_name
	,column_name
	,histogram
	,notes
    from
	all_part_col_statistics
    where owner = 'SYSADM'
     and table_name = 'PS_XX_YY_ZZZ_STAT'
     and column_name = 'EMPLID'
     and partition_name in ('PS_XX_YY_ZZZ_STAT_T_41');
		
PARTITION_NAME           COLUMN_NAME  HISTOGRAM       NOTES
------------------------ ----------- --------------- ---------------------
PS_XX_YY_ZZZ_STAT_T_41   EMPLID        YBRID          HIST_FOR_INCREM_STATS

The Notes indicates clearly that the HYBRID histogram for column EMPLID for partition n°41 will be used only for incremental statistics maintenance.

Working around the Scene

To get rid of this Height Balanced-HYBRID histogram cohabitation within the same column we need to delete and regather statistics in the concerned partition:

BEGIN
      dbms_stats.delete_table_stats(ownname  =>'SYSADM',tabname=>'PS_XX_YY_ZZZ_STAT',
                                    partname =>'PS_XX_YY_ZZZ_STAT_T_41');
									
      dbms_stats.gather_table_stats(ownname  =>'SYSADM',tabname=>'PS_XX_YY_ZZZ_STAT',
                                    partname =>'PS_XX_YY_ZZZ_STAT_T_41',
                                    granularity=>'PARTITION');
END ;
/

Querying again gives a perfect cardinality estimation

SQL> sELect /*+ full(a) */
		count(1)
	from
		PS_XX_YY_ZZZ_STAT a
	where EMPLID >= 'XX999501004'
	and EMPLID   <= 'XX999562000';
	
COUNT(1)
----------
546461

--------------------------------------------------------------------------------
| Id | Operation                   | Name               | Rows | Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                    |      |       |       |
  SORT AGGREGATE                   |                    |    1 |       |       |
| 2 |   PARTITION RANGE SINGLE     |                    | 545K |     41|    41 |
| 3 |    PARTITION LIST ALL        |                    | 545K |      1|    58 |
|*4 |    TABLE ACCESS STORAGE FULL | PS_XX_YY_ZZZ_STAT  | 545K |   2321|  2378 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage(("EMPLID">='XX999501004' AND "EMPLID"<='XX999562000'))
    filter(("EMPLID">='XX999501004' AND "EMPLID"<='XX999562000'))

And this time Oracle has clearly used the HYBRID histogram as proved by the corresponding 10053 trace file

Column (#1):
NewDensity:0.000159, OldDensity:0.000159 BktCnt:5560.000000, PopBktCnt:0.000000, PopValCnt:0, NDV:6278
Column (#1): EMPLID(VARCHAR2) Part#: 40
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159 Histogram: Hybrid #Bkts: 254 UncompBkts: 5560 EndPtVals: 254 ActualVal: yes
Column (#1): EMPLID(VARCHAR2)
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159
Histogram: Hybrid #Bkts: 254 UncompBkts: 5560 EndPtVals: 254 ActualVal: yes
Table: PS_XX_YY_ZZZ_STAT Alias: A
Card: Original: 568804 Rounded: 545382 Computed: 545381.630747 Non Adjusted: 545381.630747

This HYBRID histogram utilization is also backed by the new values of the Notes (null) columns:

SQL> select
		 partition_name
		,column_name
		,histogram
		,notes
     from
        all_part_col_statistics
    where owner = 'SYSADM'
    and table_name = 'PS_XX_YY_ZZZ_STAT'
    and column_name = 'EMPLID'
    and partition_name in ('PS_XX_YY_ZZZ_STAT_T_41');
	
PARTITION_NAME            COLUMN_NAM HISTOGRAM     NOTES
------------------------- ---------- ------------ ------
PS_XX_YY_ZZZ_STAT_T_41      EMPLID    HYBRID

The Scene Bottom-line

Oracle might ignore the presence of histogram when calculating the cardinality estimation of table/partition/sub-partition. This happens for corrupted histogram. You can check whether the histogram for a column at table/partition/sub-partition level will be used or not by the CBO simply by selecting the NOTES column from all_tab/part/subpart_col_statistics table.

July 26, 2019

Hybrid-Height Balanced corrupted histogram – Part I

Filed under: CBO — hourim @ 6:13 pm

Bad cardinality estimates lead to bad execution plans. If you understand why Oracle is misestimating object and operation cardinalities you can then address the performance problem properly. If you’re doubtful of that claim this article will show you how I have solved a performance issue by fixing a wrong single partition cardinality estimation due to a HEIGHT-BALANCED-HYBRID corrupted histogram of the partition key column. For the sake of simplicity, I will write a two-parts article. Part I explains how I have discovered this HEIGHT-BALANCED-HYBRID histogram corruption. It outlines also how it is easy to know, beforehand, that Oracle will ignore the usage of such histogram via a simple query. The second part examines the bad effect of this histogram corruption on the performance of a critical query and how I fixed it.

The Notes column

The column named NOTES in the following tables (database version is 12cR1):

  • all_tab_col_statistics
  • all_part_col_statistics
  • all_subpart_col_statistics

is, nonchalantly, an important piece of information when trying to understand why Oracle has messed up its cardinality estimation.

Here’s below a list of partitions of a range-list sub partitioned table:


SELECT
   partition_name
   ,num_distinct
   ,sample_size
   ,density
   ,histogram
   ,notes
FROM
    all_part_col_statistics
WHERE
    owner = 'SYSADM'
AND table_name = 'RANGE_LIST_TAB'
AND column_name = 'EMPLID';

PARTITION_NAME             NUM_DISTINCT SAMPLE_SIZE DENSITY    HISTOGRAM NOTES
------------------------- ------------ ----------- ---------- --------- ------------------------
RANGE_LIST_TAB_T_1          18240       5396        ,000055     HYBRID
RANGE_LIST_TAB_T_10 		1180 		5472 		,000839 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_11 		1145 		5391 		,000865 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_12 		1167 		5572 		,000849 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_13 		1373 		5564 		,000715 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_14 		1720 		5520 		,000555 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_15 		1919 		5532 		,000486 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_16 		1821 		5454 		,000517 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_17 		1565 		5535 		,000618 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_18 		1690 		5377 		,000563 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_19 		1393 		5619 		,000704 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_2 			1045 		5560 		,000951 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_20 		1445 		5589 		,000676 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_21 		1874 		5401 		,000498 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_22			1895 		5332 		,000489 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_23 		1546 		5539 		,000627 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_24 		1532 		5449 		,000633 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_25 		1680 		5422 		,000568 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_26 		1623 		5511 		,000593 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_27 		1518 		5428 		,000639 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_28 		1624 		5674 		,000595 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_29 		1463 		5412 		,000665 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_3 			949 		5497 		,00105 		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_30 		1476 		5371 		,000658 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_31 		1824 		5519 		,000517 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_32 		3039 		5445 		,00024		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_33 		7647 		5372 		,000131 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_34 		2749 		5516 		,000287 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_35 		3622 		5398 		,000161 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_36 		7833 		5462 		,000128		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_37 		3233 		5622 		,000221 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_38 		8323 		5471 		,00012 		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_39 		7733 		5286 		,000129 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_4 			1745 		5588 		,000546 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_40 		4798 		5491 		,000052		HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_41 		6278 		5560 		,000159 	HYBRID
RANGE_LIST_TAB_T_42 		5995 		5542 		,000167 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_43 		9502 		5538 		,000105 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_44 		9018 		5499 		,000111 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_45 		7941 		5544 		,000126 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_46 		5587 		5516 		,000179 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_47 		21670 		5484 		,000046 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_48 		9486 		5473 		,000105 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_49 		78008 		5385 		,000013 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_5 			1360 		5503 		,000722 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_6 			1040 		5583 		,000958 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_7 			998 		5450 		,000997 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_8 			1020 		5418 		,000975 	HYBRID HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_9 			1219 		5441 		,000811 	HYBRID HIST_FOR_INCREM_STATS

49 rows selected.

What does this mean?

When Notes equals HIST_FOR_INCREM_STATS then it means that the HYBRID histogram for the EMPLID column will be ignored by the CBO during its cardinality estimation whenever this one will hit any of the 49 partitions except partition 1 and 41 where the Notes column is null.

HYBRID-Height Balanced corrupted histogram

Here’s below an illustration of what I’ve claimed above; first for partition 10 where histograms are ignored, followed by partition 41 where HYBRID histograms are used:

SQL> select /*+ full(a) */ count(1) from RANGE_LIST_TAB a where a.emplid = 'XY361125999';

COUNT(1)
----------
1414

---------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows | Pstart| Pstop  |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                 |                |      |       |        |
|  1 |   SORT AGGREGATE                 |                | 1    |       |        |
|  2 |    PARTITION RANGE SINGLE        |                | 534  |     10|     10 |
|  3 |      PARTITION LIST ALL          |                | 534  |     1 |     58 |
|* 4 |       TABLE ACCESS STORAGE FULL  | RANGE_LIST_TAB | 534  |   523 |    580 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("A"."EMPLID"='XY361125999')
    filter("A"."EMPLID"='XY361125999')

And a here’s below the important piece of the corresponding 10053 trace file

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for RANGE_LIST_TAB[A]
  
Column (#1): EMPLID(VARCHAR2) Part#: 0
AvgLen: 12 NDV: 1180 Nulls: 0 Density: 0.000839 Min: 0.000000 Max: 0.000000 

Histogram: HtBal #Bkts: 5472 UncompBkts: 5472 EndPtVals: 0 ActualVal: yes <------

NOTE: ignoring histogram of column (RANGE_LIST_TAB.EMPLID)   <------
      used only for incremental stats maintenance            <------

Column (#1): EMPLID(VARCHAR2) Part#: 9
AvgLen: 12 NDV: 1180 Nulls: 0 Density: 0.000847
Column (#1): EMPLID(VARCHAR2)
AvgLen: 12 NDV: 1180 Nulls: 0 Density: 0.000847
Table: RANGE_LIST_TAB Alias: A Card: Original: 630005 Rounded: 534 Computed: 533.902542 Non Adjusted: 533.902542

There are two important pieces of information in the above 10053 snippet code. The first one is:

Column (#1): EMPLID(VARCHAR2) Part#: 0
AvgLen: 12 NDV: 1180 Nulls: 0 Density: 0.000839 Min: 0.000000 Max: 0.000000 
Histogram: HtBal #Bkts: 5472 UncompBkts: 5472 EndPtVals: 0 ActualVal: yes

What the heck is this Height Balanced information (HtBal)? I’ve clearly shown above that the EMPLID column has a HYBRID histogram. Something is wrong with the histogram of this column which makes Oracle ignoring its usage confirmed by the second piece of information in the same 10053-trace file

NOTE: ignoring histogram of column (RANGE_LIST_TAB.EMPLID)
      used only for incremental stats maintenance

Since the histogram of EMPLID column has been ignored Oracle falls back to the classical single table selectivity without histogram:

  E-Rows = num_rows/num_distinct = 630005/1180 = 533,902542 –-> exact value in the 10053 trace file

Let’s now consider partition 41 where the Notes column is NULL:


SQL> select /*+ full(a) */ count(1) from RANGE_LIST_TAB a where a.emplid = 'XY178963531';

COUNT(1)
----------
1022
 ---------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Rows | Pstart| Pstop  |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                 |                |      |       |        |
|  1 |   SORT AGGREGATE                 |                |    1 |       |        |
|  2 |    PARTITION RANGE SINGLE        |                | 1330 |     41|     41 |
|  3 |      PARTITION LIST ALL          |                | 1330 |      1|     58 |
|* 4 |       TABLE ACCESS STORAGE FULL  | RANGE_LIST_TAB | 1330 |   2321|   2378 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("A"."EMPLID"='XY178963531')
    filter("A"."EMPLID"='XY178963531')

SINGLE TABLE ACCESS PATH
  Column (#1):
   NewDensity:0.000159, OldDensity:0.000159 BktCnt:5560.000000, PopBktCnt:0.000000, PopValCnt:0, 
  NDV:6278
Column (#1): EMPLID(VARCHAR2) Part#: 40
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159 Histogram: Hybrid #Bkts: 254 UncompBkts: 5560 EndPtVals: 254 ActualVal: yes

Column (#1): EMPLID(VARCHAR2)
AvgLen: 12 NDV: 6278 Nulls: 0 Density: 0.000159 Histogram: Hybrid #Bkts: 254 UncompBkts: 5560 EndPtVals: 254 ActualVal: yes

Table: RANGE_LIST_TAB Alias: A Card: Original: 568804 Rounded: 1330 Computed: 1329.937410 
   Non Adjusted: 1329.937410 <-----

Oracle is, this time, using HYBRID histogram for the EMPLID value as shown in the above corresponding 10053 trace file. Let’s see if the partition key ‘XY178963531’ value is popular or not

SELECT
   partition_name
  ,bucket_number
  ,endpoint_actual_value
  ,endpoint_repeat_count
  , sample_size
  ,case when Popularity > 0 then 'Pop'
     else 'Non-Pop'
  end Popularity
FROM
	(select
		uth.partition_name
		,uth.bucket_number
		,uth.endpoint_actual_value
		,uth.endpoint_repeat_count
		,ucs.sample_size
		,(uth.endpoint_repeat_count - ucs.sample_size/ucs.num_buckets) Popularity
	from
	  all_part_histograms uth
	  ,all_part_col_statistics ucs
	where
	uth.table_name = ucs.table_name
	and uth.column_name = ucs.column_name
	and uth.owner = ucs.owner
	and uth.owner = 'SYSADM'
	and uth.table_name = 'RANGE_LIST_TAB'
	and uth.partition_name in ('RANGE_LIST_TAB_T_41')
	and uth.column_name = 'EMPLID'
	and uth.endpoint_actual_value = 'XY178963531'
	)
ORDER BY 
  partition_name, bucket_number;
  
PARTITION_NAME 			 BUCKET_NUMBER 	ENDPOINT_ACTUAL ENDPOINT_REPEAT_COUNT SAMPLE_SIZE POPULAR
------------------------ ------------- --------------- --------------------- ----------- -------
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5396        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5441        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5391        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5572        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5564        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5520        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5532        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5454        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5535        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5377        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5619        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5560        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5589        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5401        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5332        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5539        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5449        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5422        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5511        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5428        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5674        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5412        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5497        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5371        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5519        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5445        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5372        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5516        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5398        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5462        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5622        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5471        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5286        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5588        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5491        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5560        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5542        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5538        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5499        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5544        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5516        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5484        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5473        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5385        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5503        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5583        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5450        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5418        Non-Pop
RANGE_LIST_TAB_T_41       242           XY178963531     13                    5472        Non-Pop

‘XY178963531’ is a non-popular value with an endpoint number which resolves to the following cardinality estimation formula:

E-Rows = num_rows * greatest (NewDensity, ENDPOINT_REPEAT_COUNT/sample_size) 
E-Rows = 568804 * greatest (0.000159, 13/5560) = 1329,93741

This is exactly the cardinality computed by Oracle (visible in the 10053-trace file) which proves, again, that for partition 41 the HYBRID histogram has really been used.

How to proactively check for any corruption in histogram

Oracle, via this MOS Note 2483224.1 proposes the following query (which I have slightly updated) to proactively check for any corruption in histogram:

SQL> select
		c1.table_name,
		c1.column_name
    from
		all_histograms c1,
		all_histograms c2
    where 
	    c1.table_name = c2.table_name
		and c1.column_name = c2.column_name
		and c1.endpoint_number < c2.endpoint_number
		and c1.endpoint_value > c2.endpoint_value
		and c1.owner = c2.owner
		and c1.owner = 'SYSADM'
	group by
	  c1.table_name,
	  c1.column_name;

no rows selected

As you can see, this query is not correct since it shows no histogram corruption in my application.
If, instead, I use the following query it will show clearly that there are corrupted histograms or at least histograms that will be ignored by the CBO during its cardinality estimations:

SQL> SELECT
		 table_name
		,column_name
		,notes
	 FROM
		all_tab_col_statistics
	WHERE owner = 'SYSADM'
	  and histogram != 'NONE'
	  and trim(notes) = 'INCREMENTAL HIST_FOR_INCREM_STATS'
	GROUP BY
      table_name, column_name, notes
  ORDER BY
     table_name, column_name;
	 
TABLE_NAME 				COLUMN_NAME 	NOTES
-------------------- -------------- ----------------------------------
RANGE_LIST_TAB 			EMPLID 		INCREMENTAL HIST_FOR_INCREM_STATS
RANGE_LIST_TAB 			PRC_ORD_TS 	INCREMENTAL HIST_FOR_INCREM_STATS

Here we see that the table RANGE_LIST_TAB has two columns on which the histogram will be ignored. In addition, since RANGE_LIST_TAB is a partitioned table I can show this corrupted histogram information per partition using the following query as I did above in the first paragraph of this article:

SQL> SELECT
		 partition_name
		,column_name
		,histogram
		,notes
      FROM
		all_part_col_statistics
	WHERE owner = 'SYSADM'
		and table_name = 'RANGE_LIST_TAB'
		and column_name = 'EMPLID'
		and histogram != 'NONE'
		and trim(notes) = 'HIST_FOR_INCREM_STATS'
	ORDER BY
		partition_name, column_name;
		
PARTITION_NAME 				COLUMN_NAME 	HISTOGRAM 		NOTES
-------------------------- -------------- --------------- ----------------------
RANGE_LIST_TAB_T_10        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_11        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_12        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_13        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_14        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_15        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_16        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_17        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_18        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_19        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_2         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_20        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_21        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_22        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_23        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_24        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_25        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_26        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_27        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_28        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_29        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_3         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_30        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_31        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_32        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_33        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_34        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_35        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_36        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_37        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_38        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_39        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_4         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_40        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_42        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_43        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_44        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_45        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_46        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_47        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_48        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_49        EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_5         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_6         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_7         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_8         EMPLID          HYBRID          HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_9         EMPLID          HYBRID          HIST_FOR_INCREM_STATS

47 rows selected.

There are 47 partitions out of 49 in this RANGE_LIST_TAB table for which the HYBRID histogram for EMPLID column is corrupted. Partition 1 and 41 are not concerned by this corruption.
To work around this issue, you must delete and regather the statistics of the concerned partition. Let’s do the experiment for partition 42 for example:

BEGIN
  dbms_stats.delete_table_stats(ownname       =>'SYSADM'
                                ,tabname      =>'RANGE_LIST_TAB' ,partname =>'RANGE_LIST_TAB_T_42');
  dbms_stats.gather_table_stats(ownname       =>'SYSADM'
                                 ,tabname     =>'RANGE_LIST_TAB', partname =>'RANGE_LIST_TAB_T_42'
                                 ,granularity =>'PARTITION');
END ;
/

SQL> SELECT
		 partition_name
		,column_name
		,histogram
		,notes
     FROM
	     all_part_col_statistics
	WHERE owner = 'SYSADM'
	 and table_name = 'RANGE_LIST_TAB'
	 and column_name = 'EMPLID'
	 and histogram != 'NONE'
	 and trim(notes) = 'HIST_FOR_INCREM_STATS'
	ORDER BY
	   partition_name, column_name;
	   
PARTITION_NAME 				COLUMN_NAME 	HISTOGRAM 		NOTES
--------------------------- -------------- --------------- ------------------------
RANGE_LIST_TAB_T_10         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_11         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_12         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_13         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_14         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_15         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_16         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_17         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_18         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_19         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_2          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_20         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_21         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_22         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_23         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_24         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_25         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_26         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_27         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_28         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_29         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_3          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_30         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_31         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_32         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_33         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_34         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_35         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_36         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_37         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_38         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_39         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_4          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_40         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_43         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_44         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_45         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_46         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_47         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_48         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_49         EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_5          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_6          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_7          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_8          EMPLID         HYBRID           HIST_FOR_INCREM_STATS
RANGE_LIST_TAB_T_9          EMPLID         HYBRID           HIST_FOR_INCREM_STATS

46 rows selected.

The number of partitions concerned by the corrupted histogram is now 46 and the partition 42 is not anymore among the list.

Summary
You should always show a tenacity in trying to understand why Oracle has messed up its cardinality estimation. You may end up by finding the root cause of the sup-optimal execution plan. In this case, I ended up by finding that the 12c HYBRID histograms can be ignored by the CBO when they are corrupted (presence of Height Balance histogram in the 10053-trace file while the column dictionary table is showing HYBRID histogram).
I’ve also found that we can know whether the histogram will be used or not by Oracle during its cardinality estimation, simply by looking at the value of the column Note from all_tab/part_col_statistics table.

January 20, 2018

NESTED LOOP and full/right outer join in modern RDBMS

Filed under: CBO,explain plan — hourim @ 11:36 am

The Oracle Cost Based Optimizer, the MS-SQL Server optimizer and the PostgreSQL query planner cannot use a NESTED LOOP physical operation to execute FULL OUTER and RIGHT OUTER joins logical operations. They all address the RIGHT OUTER join limitation by switching the inner and the outer row source so that a LEFT OUTER JOIN can be used. While the first two optimizer turn the FULL OUTER join into a LEFT OUTER join concatenated with an ANTI-join, PostgreSQL query planner will always use a HASH/MERGE JOIN to do a FULL OUTER join.

Let’s make this less confusing by starting with the basics. The algorithm of a NESTED LOOP physical operation is:

for each row ro in the outer row source 
loop
   for each row ri in the inner row source
   loop
     if ro joins ri then return current row
   end loop
end loop

1.Oracle 12cR2

A simple execution of the above algorithm can be schematically represented via the following Oracle execution plan:

select /*+ use_nl(t1 t2) */ 
      t1.*
from t1 inner join t2
on t1.n1 = t2.n1;

----------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      3 |
|   1 |  NESTED LOOPS      |        |      1 |      4 |      3 |
|   2 |   TABLE ACCESS FULL| T1     |      1 |      3 |      3 |
|*  3 |   INDEX RANGE SCAN | T2_IDX |      3 |      1 |      3 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 – access("T1"."N1"="T2"."N1")

As you can see, in accordance with the algorithm, for each row in T1 table (A-Rows=3 operation id n°2) we scanned 3 times (Starts = 3 operation id n°3) the T2_IDX index.

Let’s now try a FULL OUTER join but without any hint:

select  
      t1.*
from t1 full outer join t2
on t1.n1 = t2.n1;

---------------------------------------------------
| Id  | Operation             | Name     | Rows  |
---------------------------------------------------
|   0 | SELECT STATEMENT      |          |       |
|   1 |  VIEW                 | VW_FOJ_0 |     4 |
|*  2 |   HASH JOIN FULL OUTER|          |     4 |
|   3 |    TABLE ACCESS FULL  | T1       |     3 |
|   4 |    TABLE ACCESS FULL  | T2       |     4 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."N1"="T2"."N1")

So far so good. A HASH JOIN FULL OUTER to honor a full outer join between two tables.

But what if I want to use a NESTED LOOP FULL OUTER instead of HASH JOIN FULL OUTER join ?

select  /*+ use_nl(t1 t2) */
      t1.*
from t1 FULL outer join t2
on t1.n1 = t2.n1;

-------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes |
-------------------------------------------------------
|   0 | SELECT STATEMENT     |        |       |       |
|   1 |  VIEW                |        |     6 |   120 |
|   2 |   UNION-ALL          |        |       |       |
|   3 |    NESTED LOOPS OUTER|        |     4 |    40 |
|   4 |     TABLE ACCESS FULL| T1     |     3 |    21 |
|*  5 |     INDEX RANGE SCAN | T2_IDX |     1 |     3 |
|   6 |    NESTED LOOPS ANTI |        |     2 |    12 |
|   7 |     TABLE ACCESS FULL| T2     |     4 |    12 |
|*  8 |     TABLE ACCESS FULL| T1     |     2 |     6 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."N1"="T2"."N1")
   8 - filter("T1"."N1"="T2"."N1")

What the heck is this execution plan of 8 operations?

Instead of having a simple NESTED LOOP FULL OUTER I got a concatenation of NESTED LOOPS OUTER and a NESTED LOOPS ANTI join.That’s an interesting transformation operated by the CBO.

Should I have tried to reverse engineer the query that sits behind the above execution plan I would have very probably obtained the following query:

select  
      t1.*
from 
      t1 
     ,t2 
where t1.n1 = t2.n1(+)
union all
select 
     t2.*
from t2
where not exists (select /*+ use_nl(t2 t1) */ 
                      null 
                  from t1 
                  where t1.n1 = t2.n1);

------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT    |        |       |       |
|   1 |  UNION-ALL          |        |       |       |
|   2 |   NESTED LOOPS OUTER|        |     4 |    40 |
|   3 |    TABLE ACCESS FULL| T1     |     3 |    21 |
|*  4 |    INDEX RANGE SCAN | T2_IDX |     1 |     3 |
|   5 |   NESTED LOOPS ANTI |        |     2 |    20 |
|   6 |    TABLE ACCESS FULL| T2     |     4 |    28 |
|*  7 |    TABLE ACCESS FULL| T1     |     2 |     6 |
------------------------------------------------------

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

   4 - access("T1"."N1"="T2"."N1")
   7 - filter("T1"."N1"="T2"."N1")

In fact when I have directed Oracle to use a NESTED LOOP to FULL OUTER JOIN T1 and T2 it has turned out my instruction into:

 T1 LEFT OUTER JOIN T2 UNION ALL T2 ANTI JOIN T1

Which is nothing else than :

  • select all rows from T1 and T2 provided they join
  • add to these rows, rows from T1 that don’t join (LEFT OUTER)
  • add to these rows, all rows from T2 that don’t join (ANTI) with rows from T1

Do you know why Oracle did all this somehow complicated gymnastic?

It did it because I asked it to do an impossible operation: NESTED LOOP doesn’t support FULL OUTER join.

It doesn’t support RIGHT OUTER join as well as shown below:

select  /*+ use_nl(t1 t2) */
      t1.*
from t1 
RIGHT outer join t2
on t1.n1 = t2.n1;

---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  NESTED LOOPS OUTER|      |     4 |    40 |
|   2 |   TABLE ACCESS FULL| T2   |     4 |    12 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     7 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."N1"="T2"."N1")

Don’t be confused here. The above RIGHT OUTER JOIN has been turned into a LEFT OUTER JOIN by switching the inner and the outer table. As such, T2 being placed in the left side of the join Oracle is able to use a NESTED LOOP to operate a LEFT OUTER JOIN. You will see this clearly explained in the corresponding SQL Server execution plan I will show later in this article.

2. PostgreSQL 10.1

Since there are no hints in PostgreSQL to make a join using a NESTED LOOP I will start by cancelling hash and merge join operations as shown below:

postgres=# set enable_mergejoin=false;
SET
postgres=# set enable_hashjoin=false;
SET

And now I am ready to show you how the PostgreSQL query planner turns a right outer join into a left outer join when a NESTED LOOP operation is used:

postgres=# explain
postgres-# select
postgres-#       t1.*
postgres-# from t1 right outer join t2
postgres-# on t1.n1 = t2.n1;

                            QUERY PLAN
-------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..95.14 rows=23 width=42)
   Join Filter: (t1.n1 = t2.n1)
   ->  Seq Scan on t2  (cost=0.00..1.04 rows=4 width=4)
   ->  Materialize  (cost=0.00..27.40 rows=1160 width=42)
         ->  Seq Scan on t1  (cost=0.00..21.60 rows=1160 width=42)
(5 lignes)

However, in contrast to Oracle and MS-SQL Server, PostgreSQL query planner is unable to transform a full outer join into a combination of an NESTED LOOP LEFT OUTER join and an ANTI-join as the following demonstrates:

explain 
select
      t1.*
from t1 full outer join t2
on t1.n1 = t2.n1;
                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Full Join  (cost=10000000001.09..10000000027.27 rows=1160 width=42)
   Hash Cond: (t1.n1 = t2.n1)
   ->  Seq Scan on t1  (cost=0.00..21.60 rows=1160 width=42)
   ->  Hash  (cost=1.04..1.04 rows=4 width=4)
         ->  Seq Scan on t2  (cost=0.00..1.04 rows=4 width=4)

Spot in passing how disabling the hash join option (set enable_hashjoin=false) is not an irreversible action. Whenever the query planner is unable to find another way to accomplish its work it will use all option available even those being explicitely disabled.

3. MS-SQL Server 2016



4. Summary
In several if not all modern Relational DataBase Management Systems, NESTED LOOP operation doesn’t support right outer and full outer join. Oracle, MS-SQL Server and PostgreSQL turn “T1 right outer join T2” into “T2 left outer join T1” by switching the inner and the outer row source. Oracle and SQL Server turn a full outer join between T1 and T2 into a T1 left outer join T2 union-all T2 anti-join T1. PostgreSQL will always use a hash/merge to full outer join T1 and T2.

Model

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- NESTED LOOP and full/right outer join : Oracle
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
drop table t1;
drop table t2;
create table t1(t1_id int , t1_vc varchar2(10));

insert into t1 values (1, 't1x');
insert into t1 values (2, 't1y');
insert into t1 values (3, 't1z');
 
create index t1_idx on t1(t1_id);

create table t2 (t2_id int, t2_vc varchar(10));

insert into t2 values (2, 't2x');
insert into t2 values (3, 't2y');
insert into t2 values (3, 't2yy');
insert into t2 values (4, 't2z');

create index t2_idx on t2(t2_id);

December 22, 2017

Null-Accepting Semi-Join

Filed under: CBO — hourim @ 11:15 am

Introduction

Null-Accepting semi-join is a new enhancement brought to the CBO by the 12cR1 release. It extends the semi-join algorithm to qualify rows from the table in the left hand side that have a null value in the join column.It kicks in for queries like the following one:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT null FROM t2 where t2.n1 = t1.n1)
		);

It is recognisable in execution plans via its acronym NA (Null-Accepting)

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN SEMI NA |      |     7 |    42 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1")

It appears in the CBO 10053 trace file as (for its HASH JOIN version):

 Best:: JoinMethod: HashNullAcceptingSemi

Don’t get confused by the NA acronym that appears in the ANTI-JOIN operation where it refers to Null-Aware rather than to Null-Accepting as shown in the following execution plan and 10053 trace file respectively:

SELECT  
   count(1)
FROM t1
   WHERE t1.n1 NOT IN (select n1 from t2);

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN ANTI NA |      |     1 |     6 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 – access("T1"."N1"="N1")

 Best:: JoinMethod: HashNullAwareAnti

If you want to clear this confusion then remember that the Null-Accepting transformation occurs for rows that (SEMI) join while Null-Aware transformation is for rows that would not join (ANTI).

The semi-join Null-Accepting logical operation can also be serviced by the NESTED LOOP physical operation as the following demonstrates:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT /*+ NL_SJ */ null FROM t2 where t2.n1 = t1.n1)
		); 

------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |
|   1 |  SORT AGGREGATE       |      |     1 |     6 |
|   2 |   NESTED LOOPS SEMI NA|      |     7 |    42 |
|   3 |    TABLE ACCESS FULL  | T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL  | T2   |     7 |    21 |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 – filter("T2"."N1"="T1"."N1")

Best:: JoinMethod: NestedLoopNullAcceptingSemi

It is the ability, acquired by the CBO as from 12cR1, to unnest the above kind of disjunctive subquery that makes the Null-Accepting transformation possible as shown in the corresponding10053 trace file:

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$2 (#2)
SU: Transforming EXISTS subquery to a join.
Registered qb: SEL$5DA710D3 0x1d225e60 (SUBQUERY UNNEST SEL$1; SEL$2)

Prior to 12cR1 it was not possible to automatically unnest the above subquery to join it with its parent block leading to the below execution plan where the inner table T2 is scanned mutliple times:

SELECT /*+ gather_plan_statistics */ 
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT /*+ no_unnest */ null FROM t2 where t2.n1 = t1.n1)
        );  
---------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |
|*  2 |   FILTER            |      |      1 |        |     10 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |     10 |     10 |
|*  4 |    TABLE ACCESS FULL| T2   |      7 |      1 |      7 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter("T2"."N1"=:B1)

The Bug

The Null-Accepting semi-join transformation comes, unfortunately, with a bug already identified in MyOracle Support via number 21201446. Here’s below how it can be reproduced:


SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

create table t1 as select rownum n1, trunc((rownum -1/5)) n2 from dual connect by level <= 10; 

create table t2 as select rownum n1, trunc((rownum -1/3)) n2 from dual connect by level <= 10;

update t1 set n1 = null where n1 in (5,6,7);

exec dbms_stats.gather_table_stats(user, 't1');

exec dbms_stats.gather_table_stats(user, 't2');

SQL> SELECT  
         count(1)
    FROM t1
      WHERE(t1.n1 is null
         OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	    );

  COUNT(1)
----------
         7

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN SEMI NA |      |     7 |    42 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(NVL("T2"."N1",42)=NVL("T1"."N1",42))

Using the HASH JOIN physical operation the query returns 7 rows. But it returns 10 rows when it uses the NESTED LOOP operation a shown below:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT /*+ NL_SJ */ null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10
		
------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |
|   1 |  SORT AGGREGATE       |      |     1 |     6 |
|   2 |   NESTED LOOPS SEMI NA|      |     7 |    42 |
|   3 |    TABLE ACCESS FULL  | T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL  | T2   |     7 |    21 |
------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 – filter(NVL("T2"."N1",42)=NVL("T1"."N1",42))	

The Null-Accepting semi-join transformation is driven by the following hidden parameter which , if cancelled, will workarround this bug as shown below:

SQL> alter session set "_optimizer_null_accepting_semijoin"=false;

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter(NVL("T2"."N1",42)=NVL(:B1,42))

How this has been fixed in 12cR2?

Because of the bug n° 21201446 It seems that Oracle has completely cancelled the Null-Accepting semi-join transformation in 12cR2 for both NESTED LOOP and HASH JOIN physical operations when the NVL function is applied on the join column. Here’s below why I am thinking so:

SQL> select banner from v$version where rownum=1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 – filter(NVL("T2"."N1",42)=NVL(:B1,42))

In 12cR1, as we’ve shown above, the NESTED LOOP was not concerned by the bug. As from 12cR2 the NESTED LOOP SEMI JOIN is not anymore allowed to occur if a NVL function is applied on the join column as the following proves:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT /*+ NL_SJ */ null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$2 / T2@SEL$2

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('_optimizer_null_accepting_semijoin' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      FULL(@"SEL$2" "T2"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter(NVL("T2"."N1",42)=NVL(:B1,42))

June 17, 2017

12cR2 OR-Expansion

Filed under: CBO — hourim @ 7:47 am

In contrast to what the title seems suggesting, the OR-Expansion is not a new 12cR2 transformation. It has existed before and has been nicely explained in this article by the Oracle optimizer group. However, let me invite you to observe with me the following simple demonstration:

create table t1 (n1 number, n2 number, c1 varchar2(10));
create index t1_idx1 on t1(n1, n2);
create index t1_idx2 on t1(n2);

explain plan for
select * 
from t1
where (n1 =1 or n2 = 42);

select * from table(dbms_xplan.display);

The above query has two different execution plans in 12cR1 and 12cR2 as shown below respectively:

-- 12cR1 : 12.1.0.2.0
---------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | 
----------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |     2 |
|   1 |  CONCATENATION                       |         |       | 
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1 |
|*  3 |    INDEX RANGE SCAN                  | T1_IDX2 |     1 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1 |
|*  5 |    INDEX RANGE SCAN                  | T1_IDX1 |     1 | 
----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N2"=42)
   5 - access("N1"=1)
       filter(LNNVL("N2"=42))
-- 12cR2 : 12.2.0.1.0
------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | 
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     2 | 
|   1 |  VIEW                                 | VW_ORE_BA8ECEFB |     2 |   
|   2 |   UNION-ALL                           |                 |       |   
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |   
|*  4 |     INDEX RANGE SCAN                  | T1_IDX1         |     1 | 
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 | 
|*  6 |     INDEX RANGE SCAN                  | T1_IDX2         |     1 | 
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("N1"=1)
   5 - filter(LNNVL("N1"=1))
   6 – access("N2"=42)

As you can see starting from Oracle 12cR2 Oracle has enhanced the OR expansion from a simple concatenation operator to a slightly more sophisticated union-all operation. The new OR-Expansion has by now been given a new name recognizable in the execution plan via the word: VW_ORE_###.

But what performance added value this transformation is supposed to bring?

Hopefully one of my real life queries gave me the answer to the above question: in contrast to its old implementation, the new enhanced 12cR2 OR-Expansion can be combined with other transformations opening, as such, a new path to the Oracle optimizer that is simply impossible to happen with the pre-12cR2 OR-Expansion.

A picture being worth a thousand of words here’s below a simple demonstration of how such a double transformation can be combined (the model comes from the previous article):

--12cR1
explain plan for
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14949 |
|*  1 |  HASH JOIN OUTER    |                 | 14949 | 
|   2 |   TABLE ACCESS FULL | T1              | 10000 |
|   3 |   VIEW              | VW_DCL_C83A7ED5 |  9950 |
|*  4 |    TABLE ACCESS FULL| T2              |  9950 |
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

If you have read my previous article you will recognize in the above execution plan the Decorrelated Lateral view transformation via its name at line 3 (VW_DCL_C83A7ED5). At my client site this transformation was systematically triggering a full table scan so that I was obliged to cancel it as explained in the corresponding article. I would have instead loved if Oracle had gone a step forward and or-expanded this VW_DCL_C83A7ED5 lateral view in order to eliminate the OR disjunctive predicate to open the index access path possibility. Unfortunately combining these two transformation seems to be impossible by then.

That was until the arrival of the 12cR2 and its new enhanced OR-Expansion.

-- 12cR2 : 12.2.0.1.0
explain plan for
select /*+ OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
---------------------------------------------------------
| Id  | Operation             | Name            | Rows  | 
---------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 14950 |
|*  1 |  HASH JOIN OUTER      |                 | 14950 | 
|   2 |   TABLE ACCESS FULL   | T1              | 10000 | 
|   3 |   VIEW                | VW_DCL_C83A7ED5 |  9951 |
|   4 |    VIEW               | VW_ORE_37EAC9F1 |  9951 | 
|   5 |     UNION-ALL         |                 |       | 
|*  6 |      TABLE ACCESS FULL| T2              |  9901 | 
|*  7 |      TABLE ACCESS FULL| T2              |    50 | 
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("T1"."ID1"="ITEM_2"(+))
 6 - filter("T2"."ID1">100)
 7 - filter("T2"."START_DATE"<=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd 
    hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND LNNVL("T2"."ID1">100))

Spot now how the 12cR2 Or-Expansion(VW_ORE_37EAC9F1) has been combined with the 12cR1 Decorrelated Lateral view(VW_DCL_C83A7ED5) opening, as such, the possibility to visit t2 table via an index access path since the disjunctive or predicate has been removed from the refactored query (the index has not been used in my case but that’s only a question of cost).

Since the 12cR2 Or-Expansion is a cost based transformation (see below in the 10053 trace file) it happens that Oracle has decided to do not use it. This is why I used the hint OR_EXPAND in order to force it kicking in for the pedagogic purpose of this article.

In order to produce the above execution plan, Oracle has gone through the following SQL refactoring steps:

 --first it has OR-Expanded the t2 table query block and named it VW_ORE_37EAC9F1

(select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where t2.id1 >100
union all
select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where 
    t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and lnnvl(t2.id1 >100)  VW_ORE_37EAC9F1
 --it have then Decorrelated the OR-Expanded query block and named it VW_DCL_C83A7ED5 

((select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where t2.id1 >100
union all
select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where 
    t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and lnnvl(t2.id1 >100)
) VW_ORE_37EAC9F1
) VW_DCL_C83A7ED5

--and finally it joined the Decorrelated OR-Expanded t2 table query with t1 table
select
  t1.id1   id1,
  t1.flag1 flag1,
  t1.flag2 flag2,
  t1.n1    n1,
  t1.v1    v1,
  vw_dcl_c83a7Ed5.item_1_0 id1,
  vw_dcl_c83a7Ed5.item_2_1 product_t1,
  vw_dcl_c83a7Ed5.item_3_2 start_date,
  vw_dcl_c83a7Ed5.item_4_3 end_date,
  vw_dcl_c83a7Ed5.item_5_4 padding
from t1 t1,
     (select
        vw_ore_37eac9f1.item_1 item_1_0,
        vw_ore_37eac9f1.item_2 item_2_1,
        vw_ore_37eac9f1.item_3 item_3_2,
        vw_ore_37eac9f1.item_4 item_4_3,
        vw_ore_37eac9f1.item_4 item_5_4
      from
        (select 
           t2.id1        item_1,
           t2.product_t1 item_2,
           t2.start_date item_3,
           t2.end_date   item_4,
           t2.padding    item_5
         from
            t2 t2
         where t2.id1 >100
        union all
         select 
           t2.id1        item_1,
           t2.product_t1 item_2,
           t2.start_date item_3,
           t2.end_date   item_4,
           t2.padding    item_5
         from
           t2 t2
         where 
           t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       and lnnvl(t2.id1 >100)
      )  vw_ore_37eac9f1
     ) vw_dcl_c83a7Ed5
where t1.id1 = vw_dcl_c83a7ed5.item_2_1(+);

The OR-Expansion appears first at the Legend section of the CBO 10053 trace file:

Legend
 The following abbreviations are used by optimizer trace
 CBQT  – cost-based query transformation
 ORE   - CBQT OR-Expansion
 

It can be disabled locally using the hint NO_OR_EXPAND:

explain plan for
select /*+ NO_OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );

-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14999 | 
|*  1 |  HASH JOIN OUTER    |                 | 14999 | 
|   2 |   TABLE ACCESS FULL | T1              | 10000 |  
|   3 |   VIEW              | VW_DCL_C83A7ED5 | 10000 | 
|*  4 |    TABLE ACCESS FULL| T2              | 10000 | 
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

And globally via the hidden param _optimizer_cbqt_or_expansion which defaults to on:

SQL> alter system set "_optimizer_cbqt_or_expansion"= off;

explain plan for
select /*+ OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
  
  select * from table(dbms_xplan.display);
 --------------------------------------------------------
| Id  | Operation             | Name            | Rows  | 
---------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 14999 |
|*  1 |  HASH JOIN RIGHT OUTER|                 | 14999 |
|   2 |   VIEW                | VW_DCL_C83A7ED5 | 10000 |
|*  3 |    TABLE ACCESS FULL  | T2              | 10000 |
|   4 |   TABLE ACCESS FULL   | T1              | 10000 |
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   3 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SUMMARY

Starting from Oracle 12cR2 the OR-Expansion has been enhanced from the classical concatenation to a more flexible union-all operation. This article shows that one of the advantages brought by this enhancement resides in the possibility it offers to the CBO to combine the new OR-Expansion with other transformations like with the Decorrelated Lateral view. This might increase the chance to have optimal execution plan in queries using disjunctive predicates.

June 14, 2017

Optimiser non sharing reasons

Filed under: CBO,cursor sharing — hourim @ 4:28 pm

Whenever Oracle is prevented from sharing an execution plan of an already seen parent cursor, it hard parses a new plan and externalizes the corresponding non-sharing reason in the gv$sql_shared_cursor dedicated view. As of Oracle 12cR2 there are as many as 66 non-sharing reasons of which I have already explained 10 in the following series of articles I wrote for Toad World:

June 2, 2017

_optimizer_ansi_join_lateral_enhance

Filed under: CBO — hourim @ 1:51 pm

There was a question raised at my client site shorter after I troubleshooted a performance issue due to the new 12cR1 Decorrelated Lateral View transformation: Is there a way to disable this CBO transformation without sending back the optimizer to its previous version?”

It ‘s an interesting question which has triggered a brief discussion and this article.

My short answer was: I have to test and check

And the long answer is:

10053 to the rescue

As long as I was able to engineer a reproducible case where this CBO transformation is used, I decided to generate its corresponding 10053 trace file and look for the word “lateral” in this file. I found 14 hits of this word of which one seems to correlate very well with my aim:

 *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
parallel_query_forced_dop           = 0
parallel_dml_forced_dop             = 0
parallel_ddl_forced_degree          = 0
parallel_ddl_forced_instances       = 0
_query_rewrite_fudge                = 90
optimizer_features_enable           = 12.1.0.1
_optimizer_ansi_join_lateral_enhance = true --> this one

Although there is nothing in the name of the above parameter suggesting a decorrelation of a lateral view, it is nevertheless, the unique CBO parameter whith a name including the word lateral. This suggests that setting it off will cancel this transformation. Let’s then implement this transformation and try cancelling it with setting this parameter off.

SQL> select banner from v$version where rownum =1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

create table t1
as
 with generator as (
   select --+ materialize
      rownum id
   from dual
   connect by level <= 1000
)
select
    rownum id1,
    mod(rownum-1,2) flag1,
    mod(rownum-1,3) flag2,
    rownum          n1,
    lpad(rownum,30) v1
from
   generator v1,
   generator v2
where
   rownum <= 1e4;
    
alter table t1 add constraint t1_pk primary key (id1);
 
create table t2
as
 select
    level id1
   ,trunc((rownum+2)/2) product_t1
   ,date '2012-06-07' + mod((level-1)*5,10) + interval '5' minute start_date
   ,date '2012-06-08' + mod((level-1)*5,10) + interval '5' minute end_date
   ,rpad('xx',10) padding
from
   dual
connect by level <=1e4;
 
alter table t2 add constraint t2_pk primary key (id1);
alter table t2 add constraint t2_t1_fk foreign key (product_t1) references t1(id1);
 
-- creating an index covering the FK deadlock threat 
create index idx_t2_usr_1 on t2(product_t1, start_date);

explain plan for 
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );
select * from table(dbms_xplan.display(format=>'advanced'));

-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14925 |
|*  1 |  HASH JOIN OUTER    |                 | 14925 |
|   2 |   TABLE ACCESS FULL | T1              | 10000 |
|   3 |   VIEW              | VW_DCL_1B0973D4 |  9926 |
|*  4 |    TABLE ACCESS FULL| T2              |  9926 |
-------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A7519627
   2 - SEL$A7519627 / T1@SEL$1
   3 - SEL$6226B99A / VW_LAT_AE9E49E8@SEL$AE9E49E8
   4 - SEL$6226B99A / T2@SEL$1


Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$6226B99A" "T2"@"SEL$1")
      USE_HASH(@"SEL$A7519627" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      LEADING(@"SEL$A7519627" "T1"@"SEL$1" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      NO_ACCESS(@"SEL$A7519627" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      FULL(@"SEL$A7519627" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$AE9E49E8")
      DECORRELATE(@"SEL$BCD4421C")
      OUTLINE(@"SEL$6226B99A")
      MERGE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$0E991E55")
      OUTLINE(@"SEL$BCD4421C")
      DECORRELATE(@"SEL$6226B99A")
      OUTLINE_LEAF(@"SEL$A7519627")
      DECORRELATE(@"SEL$BCD4421C")
      OUTLINE_LEAF(@"SEL$6226B99A")
      ALL_ROWS
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)

Notice the apparition of view operation (VW_DCL_1B0973D4) at line n°3 in the above execution plan. This is Oracle way to signal the presence of a decorrelated lateral view transformation. If you want to learn more about this new transformation then you can read this US patent

DECORRELATE and NO_DECORRELATE hint

Before we go on, I want to show you very quickly how we can locally disable this new transformation using the following hint:

NO_DECORRELATE(@"SEL$BCD4421C")
explain plan for  
select /*+ NO_DECORRELATE(@"SEL$BCD4421C") */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 | 10000 |
|   1 |  NESTED LOOPS OUTER                   |                 | 10000 |
|   2 |   TABLE ACCESS FULL                   | T1              | 10000 |
|   3 |   VIEW                                | VW_LAT_1B0973D4 |     1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |
|*  5 |     INDEX RANGE SCAN                  | IDX_T2_USR_1    |     1 |
-------------------------------------------------------------------------  

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)
   5 - access("T1"."ID1"="T2"."PRODUCT_T1")

As you can see using the NO_DECORRELATE(@”SEL$BCD4421C”) hint we cancelled the VW_DCL_1B0973D4 decorrelated lateral view since we have now a simple VW_LAT_1B0973D4 lateral view

In passing it is important to note that the outline presented above contains three hints with the word DECORRELATE:

Outline Data
-------------
  /*+
      DECORRELATE(@"SEL$BCD4421C")
      DECORRELATE(@"SEL$6226B99A")
      DECORRELATE(@"SEL$BCD4421C")
  */

Of these three hints it is only the hint referencing the query block named @”SEL$BCD4421C” that will succeed to correlated/decorrelate the lateral view. I don’t know why this hint with that query block named is repeated two times. I don’t know also why using the other hint with the query block named @”SEL$6226B99A” doesn’t decorrelate the lateral view.

Looking back into the 10053 trace file I found the following lines for query blocks SEL$BCD4421C and SEL$6226B99A respectively:

DCL: Checking validity of lateral view decorrelation SEL$BCD4421C (#1)
DCL: Passed decorrelation validity for lateral view block SEL$BCD4421C (#1)
DCL: Decorrelation of lateral view query block SEL$BCD4421C (#1).
Registered qb: SEL$6226B99A 0x8e4a5b58 (VIEW DECORRELATED SEL$BCD4421C; SEL$BCD4421C)

This tends to suggest that SEL$6226B99A represents the query block name of the lateral view and that SEL$BCD4421C represents the query block name of the decorrelated lateral view. Simply put I think that when confronted to such a kind of situation where you want to locally cancel the lateral decorrelation view than use the hint NO_DECORRELATE with the query block name that exists in the outline and doesn’t exist in the query block name given by the dbms_xplan.display_cursor:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A7519627
   2 - SEL$A7519627 / T1@SEL$1
   3 - SEL$6226B99A / VW_LAT_AE9E49E8@SEL$AE9E49E8
   4 - SEL$6226B99A / T2@SEL$1

Don’t ask me why not simply using the table aliases as they appear in the original query to cancel this transformation. All my attempts have been unsuccessful.

Hidden parameter

What exactly is the parameter driving this transformation?

alter session set "_optimizer_ansi_join_lateral_enhance"=false;

explain plan for 
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );

select * from table(dbms_xplan.display(format =>'+outline'));
-------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 | 10000 |
|   1 |  NESTED LOOPS OUTER                   |                 | 10000 |
|   2 |   TABLE ACCESS FULL                   | T1              | 10000 |
|   3 |   VIEW                                | VW_LAT_1B0973D4 |     1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |
|*  5 |     INDEX RANGE SCAN                  | IDX_T2_USR_1    |     1 |
-------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$BCD4421C" "T2"@"SEL$1")
      INDEX_RS_ASC(@"SEL$BCD4421C" "T2"@"SEL$1" ("T2"."PRODUCT_T1" "T2"."START_DATE"))
      USE_NL(@"SEL$0E991E55" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      LEADING(@"SEL$0E991E55" "T1"@"SEL$1" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      NO_ACCESS(@"SEL$0E991E55" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      FULL(@"SEL$0E991E55" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$AE9E49E8")
      MERGE(@"SEL$9EC647DD")
      OUTLINE_LEAF(@"SEL$0E991E55")
      OUTLINE_LEAF(@"SEL$BCD4421C")
      ALL_ROWS
      OPT_PARAM('_optimizer_ansi_join_lateral_enhance' 'false')
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)
   5 – access("T1"."ID1"="T2"."PRODUCT_T1")

Notice again how we have succeeded to cancel the decorrelated lateral view transformation at a global level by setting off the hidden parameter _optimizer_ansi_join_lateral_enhance.

SUMMARY
In summary, does a decorrelated lateral view causes always a performance issue? No, not necessarily always. However if you fill in need of disabling it then you can:

  • Use the corresponding no_decorrelate hint with the correct query block name for a local effect
  • Set off the _optimizer_ansi_join_lateral_enhance parameter for a global effect with the usual warning when changing a hidden parameter.

October 23, 2013

CBO and unusable unique index

Filed under: CBO — hourim @ 6:50 am

Very recently a question came up on oracle-list where the original poster was wondering about two things (a) how could a refresh on materialized view allows duplicate key to be possible in the presence of a unique index and (b) he was struggling about a particular select which is giving a wrong results.

The answer to the first question is easy and I have already blogged about it. He was refreshing the materialized view using a FALSE value for the parameter atomic refresh. With this particular refresh parameter, the materialized view is refreshed  using a rapid truncate table followed by a direct path insert. Direct path load as shown in my blog, will silently disable the unique index allowing duplicate keys to be accepted. Thought that this seems to be true (in this context of materialized view refresh) only in 11gR2. The preceding release (10gR2) is not allowing duplicate keys during this kind of refresh as I’ve shown in my answer in the oracle-list forum. Does this mean that 10gR2 is not direct path loading when atomic refresh is set to FALSE? I have to check.

But what motivated the current blog article is the second question. See with me

SQL> create table a(id int,val number);
Table created.

SQL> insert into a select 1, 1 from dual;
1 row created.

SQL> create table b(id int);
Table created.

SQL> create unique index uq_b on b(id);
Index created.

Then I will use a sqlloader to load data into table b using a direct path load in order to silently disable the unique index. The control file(c.ctl) I will be using resembles to:

LOAD DATA
INFILE *
REPLACE
INTO TABLE B
(id position(1:1) TERMINATED BY ",")
BEGINDATA
1;
1;

And now I will launch the sqlloader

C:\>sqlldr user/paswd@database control=c.ctl direct=true

SQL*Loader: Release 10.2.0.3.0 - Production on Tue Oct 22 16:46:06 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Load completed - logical record count 3.

What do you think it happens to the unique index and to the table b after this direct path load?


SQL> select index_name, status from user_indexes where index_name ='UQ_B';

INDEX_NAME                     STATUS
------------------------------ --------
UQ_B                           UNUSABLE

SQL> select count(1) from b;

COUNT(1)
----------
2

The unique index has been disabled and there are duplicate keys in table b.

So far so good.

Let’s now start exploring the Original Poster queries problem

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    15 (100)|          |
|   1 |  TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2) 

The CBO knows that there is a unique index on b(id). And, as far as there is one id in table a, the CBO, obviously, assumes that there will be only one record for the corresponding id in table b. This is why table b is not present in the execution plan by the way. Unfortunately the unique index has been disabled by the direct path load and has permitted the presence of duplicate record in table b. This is the reason why the query is producing a wrong result.

If we force the CBO to access the table b the result is however correct

SQL>  select a.*,b.id
from a, b
where a.id = b.id(+);

ID        VAL         ID
---------- ---------- ----------
1          1          1
1          1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    78 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    26 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Note
-----
- dynamic sampling used for this statement (level=2)

It is clear that the CBO is not looking the unique index status during the optimization (plan generation) phase.

But, what if instead of the unusable unique index, we have a disabled unique constraint? Will the CBO consider the status of the unique constraint in this case?

SQL> alter table b add constraint b_uk unique (id) disable;
Table altered.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1

Oups. The CBO is still wrong. What if we drop the culprit index?

SQL> drop index uq_b;
Index dropped.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    18 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |     6 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |     6 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Which finally gives a correct result.

Bottom line: always make sure your unique indexes are usable.

UPDATE 27-12-2013 :  following a discussion here,Ted from the Oracle Global Customer Support, pointed me to the bug number 17533502 which seems very close the problem exposed here. Ted also pointedme to a work arround solution as shown below:

SQL> select * from v$version where rownum=1;

BANNER                                                                       CON_ID
---------------------------------------------------------------------------- -------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID   VAL
--- ----
 1   1

SQL> ALTER SESSION SET "_optimizer_join_elimination_enabled" = false;

Session altered.

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID  VAL
--- -----
 1  1
 1  1

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)