Mohamed Houri’s Oracle Notes

January 5, 2020

PHV2

Filed under: Tuning — hourim @ 2:47 pm

PLAN_HASH_VALUE_2 (Phv2) includes the hash value of the execution(PLAN_HASH_VALUE) and the hash value of its predicate part. Whenever, I have a doubt about the difference between two similar execution plans I use the following query to get their corresponding Phv2:

-- phv2.sql
SELECT
  p.sql_id
 ,p.plan_hash_value
 ,p.child_number
 ,t.phv2
FROM 
  gv$sql_plan p
 ,xmltable('for $i in /other_xml/info
             where $i/@type eq "plan_hash_2"
             return $i'
             passing xmltype(p.other_xml)
             columns phv2 number path '/'
            ) t
WHERE 
     p.sql_id = '&sql_id'
AND p.other_xml is not null;

Phv2 is important when trying to understand why an enabled and accepted SPM baseline plan has not been used. Indeed, the CBO will use the SPM plan provided it will be able to produce, at query execution time, an execution plan having a PHV2 that equals the PLAN_ID of the SPM plan stored in the SPM baseline as shown below in the CBO-SPM plan selection diagram:

There exist different reasons which make two execution plans having the same plan_hash_value but a different Phv2 of which I can list two:

There is, though, an effort deployed by Oracle to make Phv2 independent from the generated CBO view names like it is already the case for the materialized CTE SYS_TEMP table transformation view as explained by Dominic Brooks in this blog post.

That’s said, a couple of weeks ago I observed an interesting practical real life case :

SQL> @phv2
Enter value for sql_id: 9m80uw87u330r

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
------------- --------------- ------------ ----------
9m80uw87u330r      1858552262            1  513024182
9m80uw87u330r      1858552262            0  513024182

As you can see, the above parent SQL_ID has two child cursors n°0 and n°1 having the same PLAN_HASH_VALUE and the same PHV2. But they are performing differently as shown by the following execution statistics where child n°0 consumes more physical I/O then child n°1 :

SQL> @sqlstats
Enter value for sql_id: 9m80uw87u330r

     CHILD PLAN_HASH_VALUE   AVG_GETS   AVG_PIOS  AVG_ETIME     EXECS
---------- --------------- ---------- ---------- ---------- ---------
         0      1858552262    1786506    1275800 453.184926         1
         1      1858552262    3408017       4095 18.6310735         4

I would have loved to direct you to the excellent presentation done by Mauro Pagano about same plan but different performance to have an idea about the performance difference between these two child cursors. But despite they have the same PLAN_HASH_VALUE and the same PHV2 they are, in this case, two really different execution plans as the followings prove:

SQL> select * from table(dbms_xplan.display_cursor('9m80uw87u330r',null));

SQL_ID  9m80uw87u330r, child number 0
-------------------------------------
Plan hash value: 1858552262
------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |       |          |
|   1 |  CONCATENATION                |                         |       |          |
|*  2 |   HASH JOIN                   |                         |     1 | 00:03:17 |
|*  3 |    TABLE ACCESS FULL          | UB_XYZBLTYVG_VALLEE_PAD | 46302 | 00:00:01 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| RB_SALA                 |    34M| 00:01:50 | --> Notice the presence of *
|*  5 |     INDEX RANGE SCAN          | RB_SALA_CER_IDX         |    34M| 00:00:05 |
|*  6 |   HASH JOIN                   |                         |     1 | 00:09:03 |
|*  7 |    TABLE ACCESS FULL          | UB_XYZBLTYVG_VALLEE_PAD | 46302 | 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID| RB_SALA                 |    93M| 00:05:02 |
|*  9 |     INDEX RANGE SCAN          | RB_SALA_CER_IDX         |    93M| 00:00:15 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("D"."SUB_ACCT"<>(-:SYS_B_21))
   5 - access("D"."STATE"=:SYS_B_15 AND "D"."CERMETHOD"=:SYS_B_16)
       filter("D"."CREATEDBY"<>:SYS_B_20)
   9 - access("D"."STATE"=:SYS_B_14)
       filter(("D"."CREATEDBY"<>:SYS_B_20 AND (LNNVL("D"."STATE"=:SYS_B_15) OR
              LNNVL("D"."CERMETHOD"=:SYS_B_16))))
Note
-----
   - SQL profile prf_9m80uw87u330r_2229773696 used for this statement
   - SQL patch "PATCH_9m80uw87u330r" used for this statement


SQL_ID  9m80uw87u330r, child number 1
-------------------------------------
Plan hash value: 1858552262
------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |       |          |
|   1 |  CONCATENATION                |                         |       |          |
|*  2 |   HASH JOIN                   |                         |     1 | 00:04:58 |
|*  3 |    TABLE ACCESS FULL          | UB_XYZBLTYVG_VALLEE_PAD | 46302 | 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| RB_SALA                 |    34M| 00:03:30 | --> Notice the absence of *
|*  5 |     INDEX RANGE SCAN          | RB_SALA_CER_IDX         |    34M| 00:00:05 |
|*  6 |   HASH JOIN                   |                         |     1 | 00:13:38 |
|*  7 |    TABLE ACCESS FULL          | UB_XYZBLTYVG_VALLEE_PAD | 46302 | 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| RB_SALA                 |    93M| 00:09:37 |
|*  9 |     INDEX RANGE SCAN          | RB_SALA_CER_IDX         |    93M| 00:00:13 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("D"."STATE"=:SYS_B_15 AND "D"."CERMETHOD"=:SYS_B_16)
       filter(("D"."CREATEDBY"<>:SYS_B_20 AND "D"."SUB_ACCT"<>(-:SYS_B_21)))   
   9 - access("D"."STATE"=:SYS_B_14)
       filter(("D"."CREATEDBY"<>:SYS_B_20 AND "D"."SUB_ACCT"<>(-:SYS_B_21) AND
              (LNNVL("D"."STATE"=:SYS_B_15) OR LNNVL("D"."CERMETHOD"=:SYS_B_16))))

Note
-----
   - SQL profile prf_9m80uw87u330r_2229773696 used for this statement
   - SQL patch "PATCH_9m80uw87u330r" used for this statement

I have deleted from the two execution plans predicates n°2,3,6, 7 and 8 since they are not playing any role in this context.

Two important points can be pointed out here:

  • The first interesting point to emphasize is that there is no filter predicate on the RB_SALA table at line id n° 4 in the second execution plan (absence of *)
  •  The second difference resides in the filter predicates n° 5 and 9 of the second execution plan. They have an extra clause on the SUB_ACCT column.

The drastic performance enhancement I brought to this query, was particularly due to the elimination of the filter predicate n°4 on the big table RB_SALA. Filtering at the index level reduces the total physical I/O consumption (4,095 versus 1,275,800) making the execution time of cursor child n°1 very interesting (18 seconds versus 453).

If you want to know the whole story, then here’s what I did:

One of my clients asked me to look at a critical query that was not very well performing following an upgrade from 11gR2 to 12cR1. This query was using the execution plan of child n°0. I knew immediately that if I can get rid of the filter predicate from the RB_SALA at line n°4 I will make this query performing very well. Therefore, I created the following index :

SQL> create /*+ parallel(4) */ index RB_SALA_CER_IDX_MHO on RB_SALA(STATE, CERMETHOD, CREATEDBY, SUB_ACCT) compress 3;

By the way spot how I managed to create the index using parallelism without having to unset the DOP of the created index afterward.

For the sake of completeness there exist in this application the following index which is used by a SQL Profile

RB_SALA_CER_IDX on RB_SALA(STATE, CERMETHOD, CREATEDBY);

I disabled the SQL patch and the SQL Profile and asked the client to launch the query, which unfortunately refused to use the new index and was, this time, dramatically slow.

After several failed tests trying to make the CBO using the new index I decided to use the brute force

prompt renaming existing RB_SALA_CER_IDX index used in SQL Profile
alter index RB_SALA_CER_IDX rename to RB_SALA_CER_IDX_OLD;
--
prompt setting existing RB_SALA_CER_IDX index invisible
alter index RB_SALA_CER_IDX_OLD invisible;
--
prompt rename newly created index to the old existing one so that it will be used by the SQL Profile 
alter index RB_SALA_CER_IDX_MHO rename to RB_SALA_CER_IDX;

With the above three commands I produced the performant and acceptable execution plan represented by child n°1 having the same PLAN_HASH_VALUE and the same PHV2 as that of the bad performing execution plan

How to model this case

Here’s an easy way to model this case:

SQL> create table t1 as select rownum n1, trunc((rownum -1)/3) n2 , 'xY' vc 
    from dual connect by level <=10; 

SQL> create index t1_idx on t1(n2);

SQL> select * from t1 where n2=0 and vc='xY';

        N1         N2 VC
---------- ---------- --
         1          0 xY
         2          0 xY
         3          0 xY

SQL_ID  gzfq0fm0jf4v9, child number 0
-------------------------------------
select * from t1 where n2=0 and vc='xY'

Plan hash value: 1775246573
--------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     3 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX |     3 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("VC"='xY')
   2 - access("N2"=0)

SQL> @Phv2
Enter value for sql_id: gzfq0fm0jf4v9

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
------------- --------------- ------------ ----------
gzfq0fm0jf4v9      1775246573            0  409377851

SQL> alter index t1_idx invisible;

SQL> alter index t1_idx rename to t1_idx_old;

SQL> create index t1_idx on t1(n2, vc);

SQL> select * from t1 where n2=0 and vc='xY';

        N1         N2 VC
---------- ---------- --
         1          0 xY
         2          0 xY
         3          0 xY

SQL_ID  gzfq0fm0jf4v9, child number 0
-------------------------------------
select * from t1 where n2=0 and vc='xY'

Plan hash value: 1775246573
--------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     3 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX |     3 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=0 AND "VC"='xY')

SQL> @phv2
Enter value for sql_id: gzfq0fm0jf4v9

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
------------- --------------- ------------ ----------
gzfq0fm0jf4v9      1775246573            0  409377851

Bottom Line

Don’t be surprised to see two non-identical execution plans having the same PLAN_HASH_VALUE, the same PHV2, generating the same rows but performing differently. This occurs particularly when

  •  a filter predicate on table access operation is deleted
  •  a filter predicate is enriched by an extra condition

December 23, 2019

PUSH SUBQUERY

Filed under: Tuning — hourim @ 8:27 am

In the previous article we examined the performance benefit brought by the scalar subquery caching effect when a call to a PL/SQL function is wrapped into a select from dual. Scalar subquery caching, almost always, reduces the number of PL/SQL function recursive calls. In this article I wanted to share with you the observations I have made in the same real-life query when I replaced the call to the PL/SQL function by its content.

The real-life query running under a 12cR1 Exadata machine and using a PL/SQL function in the where clause looks like the following:

SQL> SELECT /*+ gather_plan_statistics */
2     a.xy_etp_dat_deb,
3     a.xy_etp_num,
4     a.xy_bat_id
5  FROM
6     ps_xy_exp_etp_exe a
7  WHERE
8       a.xy_bat_id = f_get_id('BJOBD176')
9   AND a.xy_etp_dat_deb = (select
10                               max(b.xy_etp_dat_deb)
11                           from ps_xy_exp_etp_exe b
12                           where a.xy_bat_id = b.xy_bat_id
13                           )
14   AND a.xy_etp_num     = (select
15                              max(c.xy_etp_num)
16                           from ps_xy_exp_etp_exe c
17                           where c.xy_bat_id    = a.xy_bat_id
18                           and c.xy_etp_dat_deb = a.xy_etp_dat_deb
19                           );

Elapsed: 00:00:01.80

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

Instead of surrounding the PL/SQL function at line 8 by a “select from dual” as explained in the previous article, I decided to use its content directly into the where clause as shown below at line 8:

SQL> SELECT /*+ gather_plan_statistics */
2     a.xy_etp_dat_deb,
3     a.xy_etp_num,
4     a.xy_bat_id
5  FROM
6     ps_xy_exp_etp_exe a
7  WHERE
8       a.xy_bat_id = (select xy_bat_id from ps_xy_exp_bat where xy_bat_nom = 'BJOBD176')
9  AND a.xy_etp_dat_deb = (select
10                               max(b.xy_etp_dat_deb)
11                           from ps_xy_exp_etp_exe b
12                           where a.xy_bat_id = b.xy_bat_id
13                          )
14   AND a.xy_etp_num     = (select
15                              max(c.xy_etp_num)
16                           from ps_xy_exp_etp_exe c
17                           where c.xy_bat_id    = a.xy_bat_id
18                           and c.xy_etp_dat_deb = a.xy_etp_dat_deb
19                           );
Elapsed: 00:00:00.04

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

As you can see, replacing the PL/SQL function call by its content has eliminated the number of recursive calls as we went from 18506 recursive to 0 call and from 2 seconds to 4 milliseconds.

Let’s try to understand what happened so that we got such a performant improvement. First the row source execution plan (for the sake of simplicity I am using the predicate part taken from the explain plan for command):

Plan hash value: 408154845
------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |      1 |        |      1 |
|*  1 |  FILTER                                 |                   |      1 |        |      1 |
|*  2 |   HASH JOIN                             |                   |      1 |      1 |      1 |
|*  3 |    TABLE ACCESS STORAGE FULL            | PS_XY_EXP_ETP_EXE |      1 |     63 |     76 |
|*  4 |     TABLE ACCESS STORAGE FULL FIRST ROWS| PS_XY_EXP_BAT     |      1 |      1 |      1 |
|   5 |    VIEW                                 | VW_SQ_1           |      1 |    296 |    296 |
|   6 |     SORT GROUP BY                       |                   |      1 |    296 |    296 |
|   7 |      TABLE ACCESS STORAGE FULL          | PS_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |
|   8 |   SORT AGGREGATE                        |                   |      1 |      1 |      1 |
|*  9 |    TABLE ACCESS STORAGE FULL            | PS_XY_EXP_ETP_EXE |      1 |      1 |      1 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."XY_ETP_NUM"= (SELECT MAX("C"."XY_ETP_NUM") FROM "PS_XY_EXP_ETP_EXE" "C" WHERE
"C"."XY_ETP_DAT_DEB"=:B1 AND "C"."XY_BAT_ID"=:B2))
2 - access("A"."XY_ETP_DAT_DEB"="MAX(B.XY_ETP_DAT_DEB)" AND "A"."XY_BAT_ID"="ITEM_1")
3 - storage("A"."XY_BAT_ID"= (SELECT "XY_BAT_ID" FROM "PS_XY_EXP_BAT" "PS_XY_EXP_BAT" WHERE
"XY_BAT_NOM"='BJOBD176'))
filter("A"."XY_BAT_ID"= (SELECT "XY_BAT_ID" FROM "PS_XY_EXP_BAT" "PS_XY_EXP_BAT" WHERE
"XY_BAT_NOM"='BJOBD176'))
4 - storage("XY_BAT_NOM"='BJOBD176')
filter("XY_BAT_NOM"='BJOBD176')
9 - filter("C"."XY_ETP_DAT_DEB"=:B1 AND "C"."XY_BAT_ID"=:B2)

That’s a weird non-common execution plan, right?

How could it be differently when we see these two unusual operations at lines Id n°3 and n°4?

Usually, a PARENT-CHILD operation is, for example, an index feeding its parent table with ROWIDS which, then, get filtered by the parent table access by index ROWID. But, here, we have a parent TABLE ACCESS STORAGE FULL of PS_XY_EXP_ETP_EXE at line Id n°3 and an unrelated child operation TABLE ACCESS STORAGE FULL FIRST ROWS of table PS_XY_EXP_BAT at line Id n°4.

What I, very often do, when trying to understand what cunning transformation has been done by Oracle behind the scenes is to check the outline section of the execution plan:

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
../..
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$BD9E0841")
OUTLINE(@"SEL$1")
FULL(@"SEL$B9151BA2" "A"@"SEL$1")
NO_ACCESS(@"SEL$B9151BA2" "VW_SQ_1"@"SEL$BD9E0841")
LEADING(@"SEL$B9151BA2" "A"@"SEL$1" "VW_SQ_1"@"SEL$BD9E0841")
USE_HASH(@"SEL$B9151BA2" "VW_SQ_1"@"SEL$BD9E0841")
PUSH_SUBQ(@"SEL$2")                              --> this is where the subuqery is pushed
PQ_FILTER(@"SEL$B9151BA2" SERIAL)
FULL(@"SEL$291F8F59" "B"@"SEL$3")
FULL(@"SEL$4" "C"@"SEL$4")
END_OUTLINE_DATA
*/

Indeed, there is that hint PUSH_SUBQ(@”SEL$2″) which indicates that he scalar subquery against PS_XY_EXP_BAT table has been first evaluated once (Starts =1, Id operation n°4) and then, the result of this evaluation, has been pushed as a predicate against the PS_XY_EXP_ETP_EXE table (Id operation n°3). And this is exactly what made PS_XY_EXP_BAT table a child subordinate action to PS_XY_EXP_ETP_EXE parent table.

If Oracle had not pushed the scalar subquery down the execution plan tree, it would have produced the following execution plan:

explain plan for
SELECT
a.xy_etp_dat_deb,
a.xy_etp_num,
a.xy_bat_id
FROM
ps_xy_exp_etp_exe a
WHERE
a.xy_bat_id = (select /*+ NO_PUSH_SUBQ(@"SEL$2")*/ xy_bat_id from ps_xy_exp_bat where xy_bat_nom = 'BJOBD176')
AND a.xy_etp_dat_deb = (select
max(b.xy_etp_dat_deb)
from ps_xy_exp_etp_exe b
where a.xy_bat_id = b.xy_bat_id)
AND a.xy_etp_num     = (select
max(c.xy_etp_num)
from ps_xy_exp_etp_exe c
where c.xy_bat_id    = a.xy_bat_id
and c.xy_etp_dat_deb = a.xy_etp_dat_deb);

Plan hash value: 1136506203
----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |      1 |        |      1 |
|*  1 |  FILTER                               |                   |      1 |        |      1 |
|*  2 |   HASH JOIN                           |                   |      1 |    296 |    296 |
|*  3 |    HASH JOIN                          |                   |      1 |    296 |    318 |
|   4 |     VIEW                              | VW_SQ_1           |      1 |    296 |    296 |
|   5 |      SORT GROUP BY                    |                   |      1 |    296 |    296 |
|   6 |       TABLE ACCESS STORAGE FULL       | PS_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |--1
|   7 |     TABLE ACCESS STORAGE FULL         | PS_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |
|   8 |    VIEW                               | VW_SQ_2           |      1 |  18697 |  17933 |
|   9 |     SORT GROUP BY                     |                   |      1 |  18697 |  17933 |
|  10 |      TABLE ACCESS STORAGE FULL        | PS_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |
|* 11 |   TABLE ACCESS STORAGE FULL FIRST ROWS| PS_XY_EXP_BAT     |      1 |      1 |      1 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."XY_BAT_ID"= (SELECT /*+ NO_PUSH_SUBQ */ "XY_BAT_ID" FROM "PS_XY_EXP_BAT"
"PS_XY_EXP_BAT" WHERE "XY_BAT_NOM"='BJOBD176'))
2 - access("A"."XY_ETP_NUM"="MAX(C.XY_ETP_NUM)" AND "ITEM_2"="A"."XY_BAT_ID" AND
"ITEM_3"="A"."XY_ETP_DAT_DEB")
3 - access("A"."XY_ETP_DAT_DEB"="MAX(B.XY_ETP_DAT_DEB)" AND "A"."XY_BAT_ID"="ITEM_1")
11 - storage("XY_BAT_NOM"='BJOBD176')
filter("XY_BAT_NOM"='BJOBD176')

Notice now how the scalar subquery has been left until all the other predicates have been applied to the parent query block (operation Id n°1). This is the case where the subquery is evaluated as a FILTER predicate for every row in the result set. In the PUSH_SUBQ former case, Oracle runs the subquery for each row in PS_XY_EXP_ETP_EXE table and join the 76 survived rows with the rest of the data. This means that when the subquery has been pushed, the first operation in the execution plan tree has eliminated almost all rows and kept only 76. However, in the NO_PUSH_SUBQ case, the first operation(id n°6 in plan with plan hash value 1136506203) in the execution plan produced 18605 rows. Since Oracle started bigger, more data have then to be carried out through the rest of the plan causing, consequently, more work and more time.

Summary

Through two distinct articles, we’ve examined a performance issue of a real-life query using a PL/SQL function in one of its WHERE clause to FILTER rows from the parent query block. In the first article we outlined how we can take benefit from scalar subquery cache effect simply by surrounding the PL/SQL function call with a SELECT FROM DUAL. In the second article (current one) we examined how sometimes it is better to replace the PL/SQL function by its content in the scalar subquery WHERE clause. Proceeding as such, we can offer the Oracle optimizer engine the possibility to push the result of the scalar subquery where clause deeper in the execution plan path; filtering, as such, non-qualified rows much earlier and carrying less data through the rest of the execution plan.

December 18, 2019

Scalar subquery caching : the select from dual trick

Filed under: Oracle — hourim @ 3:00 pm

This short note explains why you should think about wrapping your PL/SQL function calls in a SELECT FROM DUAL so that you will get the performance benefit of scalar subquery caching whenever possible.

Recursive calls

Here’s below a real-life query running under a 12cR1 Exadata machine and using a call to a PL/SQL function in line number 8 below:

SQL> SELECT /*+ gather_plan_statistics */
2     a.xy_etp_dat_deb,
3     a.xy_etp_num,
4     a.xy_bat_id
5  FROM
6     sp_xy_exp_etp_exe a
7  WHERE
8       a.xy_bat_id = f_get_id('BJOBD176')
9   AND a.xy_etp_dat_deb = (select
10                               max(b.xy_etp_dat_deb)
11                           from sp_xy_exp_etp_exe b
12                           where a.xy_bat_id = b.xy_bat_id
13                           )
14   AND a.xy_etp_num     = (select
15                              max(c.xy_etp_num)
16                           from sp_xy_exp_etp_exe c
17                           where c.xy_bat_id    = a.xy_bat_id
18                           and c.xy_etp_dat_deb = a.xy_etp_dat_deb
19                           );

Elapsed: 00:00:01.82

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

I was immediately intrigued by the 18605 recursive calls that were systematically appearing at each execution of the above query. Therefore, I started trying to figure out the root cause of those recursive calls. The corresponding row-source execution plan is:

Plan hash value: 1967369873
--------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |      1 |
|*  1 |  FILTER                       |                   |      1 |        |      1 |
|*  2 |   HASH JOIN                   |                   |      1 |      1 |      1 |
|*  3 |    TABLE ACCESS STORAGE FULL  | SP_XY_EXP_ETP_EXE |      1 |     63 |     76 | --> this
|   4 |    VIEW                       | VW_SQ_1           |      1 |    296 |    296 |
|   5 |     SORT GROUP BY             |                   |      1 |    296 |    296 |
|   6 |      TABLE ACCESS STORAGE FULL| SP_XY_EXP_ETP_EXE |      1 |  18697 |  18605 | --> this
|   7 |   SORT AGGREGATE              |                   |      1 |      1 |      1 |
|*  8 |    TABLE ACCESS STORAGE FULL  | SP_XY_EXP_ETP_EXE |      1 |      1 |      1 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."XY_ETP_NUM"=)
2 - access("A"."XY_ETP_DAT_DEB"="MAX(B.XY_ETP_DAT_DEB)" AND "A"."XY_BAT_ID"="ITEM_1")
3 - filter("A"."XY_BAT_ID"="F_GET_ID"('BJOBD176'))
8 - filter(("C"."XY_ETP_DAT_DEB"=:B1 AND "C"."XY_BAT_ID"=:B2))

I don’t think that the 18605 A-Rows at operation Id n°6 and the 18605 recursive calls are a result of a mere coincidence. They are inevitably related to each other. Let’s then try to find out this relationship. One way to do that is to look at a 10046-trace of the above query:

SQL> alter session set tracefile_identifier ='QueryRecursiveCalls';

SQL> @46on 12
SQL –- execute query here
SQL> @46off

The TKRPOFED trace file shows the followings:

SELECT /*+ gather_plan_statistics */
a.xy_etp_dat_deb,
a.xy_etp_num,
a.xy_bat_id
FROM
sp_xy_exp_etp_exe a
WHERE
a.xy_bat_id = f_get_id('BJOBD176')
AND a.xy_etp_dat_deb = (select
max(b.xy_etp_dat_deb)
from sp_xy_exp_etp_exe b
where a.xy_bat_id = b.xy_bat_id)
AND a.xy_etp_num     = (select
max(c.xy_etp_num)
from sp_xy_exp_etp_exe c
where c.xy_bat_id    = a.xy_bat_id
and c.xy_etp_dat_deb = a.xy_etp_dat_deb)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0         12          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0         12          0           0

SQL ID: 5gb8jzxq0zcj4 Plan Hash: 1613371837

SELECT XY_BAT_ID
FROM
SP_XY_EXP_BAT WHERE XY_BAT_NOM = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  11146      0.93       0.95          0          0          0           0
Fetch    11146      0.83       0.90          0     178336          0       11146
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    22293      1.77       1.85          0     178336          0       11146

As you we can see, there is a recursive SELECT against SP_XY_EXP_BAT table consuming almost all the total time of the query (1.85 seconds). This recursive SELECT, identified by SQL_ID 5gb8jzxq0zcj4, is nothing else than the content of the PL/SQL function used in line number 8 of the original main query and which I am reproducing here below:

WHERE
a.xy_bat_id = f_get_id('BJOBD176');

The capital bind variable :B1 in the TKPROF trace file represents another indication of a SQL embedded in PL/SQL. I can even get the captured value of this bind variable as shown below:

col value_string format a10
col last_captured format a20
select
last_captured
,value_string
from
v$sql_bind_capture
where sql_id = '5gb8jzxq0zcj4';

LAST_CAPTURED        VALUE_STRI
-------------------- ----------
05/08/19             BJOBD176

While the number of executions of the recursive SELECT shown in the TKPROF (11,146) is not exactly the number I was expecting i.e. 18,605, I can, nevertheless, see that the number of executions of the corresponding SQL_ID (5gb8jzxq0zcj4) stored in memory is exactly 18,605

SQL> @gv$sql
Enter value for sql_id: 5gb8jzxq0zcj4

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE TO_CHAR(P.LAST_ACTI EXECUTIONS
------------- ------------ --------------- ------------------- ----------
5gb8jzxq0zcj4            0      1613371837 05/08/2019 11:27:19      18605

So far, we have identified that the 18605 recursive calls represent simply the 18605 executions of predicate n°3 above which involves the call to the PL/SQL function. But what is, exactly, the relationship between those 18605 recursive calls and the 18605 A-Rows of line 6 in the above execution plan?

The 18605 recursive calls are strongly related to the number of rows present in the SP_XY_EXP_ETP_EXE table.

SQL> select count(1) from SP_XY_EXP_ETP_EXE;

COUNT(1)
----------
18605

Indeed, the PL/SQL function was called as many times as there are rows in this table producing 76 rows as confirmed via the A-Rows column of operation at line n°3 in the above execution plan:

SQL> select count(1) from SP_XY_EXP_ETP_EXE
where xy_bat_id = f_get_id('BJOBD176');

COUNT(1)
----------
76

How to fix the problem while still using the PL/SQL function?

The problem resorts to the multiple executions (18605) of the predicate n°3 on table SP_XY_EXP_ETP_EXE:

3 - filter("A"."XY_BAT_ID"="F_GET_ID"('BJOBD176'))

This happens even though the input to the function, BJOBD176, is constant.

If we surround the call to that function with a SELECT FROM DUAL Oracle will use scalar subquery caching producing, as such, a massive reduction in the number of calls to that function as the following proves:

SELECT /*+ gather_plan_statistics */
a.xy_etp_dat_deb,
a.xy_etp_num,
a.xy_bat_id
FROM
sp_xy_exp_etp_exe a
WHERE
a.xy_bat_id = (select f_get_id('BJOBD176') from dual)
AND a.xy_etp_dat_deb = (select
max(b.xy_etp_dat_deb)
from sp_xy_exp_etp_exe b
where a.xy_bat_id = b.xy_bat_id
)
AND a.xy_etp_num     = (select
max(c.xy_etp_num)
from sp_xy_exp_etp_exe c
where c.xy_bat_id    = a.xy_bat_id
and c.xy_etp_dat_deb = a.xy_etp_dat_deb
);

Elapsed: 00:00:00.02 --> drastic execution time reduction
--------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |      1 |
|*  1 |  FILTER                       |                   |      1 |        |      1 |
|*  2 |   HASH JOIN                   |                   |      1 |      1 |      1 |
|*  3 |    TABLE ACCESS STORAGE FULL  | SP_XY_EXP_ETP_EXE |      1 |     63 |     76 |
|   4 |     FAST DUAL                 |                   |      1 |      1 |      1 |
|   5 |    VIEW                       | VW_SQ_1           |      1 |    296 |    296 |
|   6 |     SORT GROUP BY             |                   |      1 |    296 |    296 |
|   7 |      TABLE ACCESS STORAGE FULL| SP_XY_EXP_ETP_EXE |      1 |  18697 |  18605 |
|   8 |   SORT AGGREGATE              |                   |      1 |      1 |      1 |
|*  9 |    TABLE ACCESS STORAGE FULL  | SP_XY_EXP_ETP_EXE |      1 |      1 |      1 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."XY_ETP_NUM"=)
2 - access("A"."XY_ETP_DAT_DEB"="MAX(B.XY_ETP_DAT_DEB)" AND"A"."XY_BAT_ID"="ITEM_1")
3 - storage("A"."XY_BAT_ID"=)
filter("A"."XY_BAT_ID"=)
9 - filter(("C"."XY_ETP_DAT_DEB"=:B1 AND "C"."XY_BAT_ID"=:B2))

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

Surrounding the call to the function with a select from dual allows Oracle to cache the result of the function for identical results. Therefore, the number of recursive calls drops from 18605 to only a single call and the query completes in about 2 milli-seconds instead of 2 seconds when scalar subquery caching was not possible.

Summary

As explained by Tom Kyte in this excellent article I can confirm that I have, almost always, improved the performance of my queries when I wrapped the PL/SQL function call into a SELECT FROM DUAL. This trick allows us to get the performance benefit of the scalar subquery cache.
In the next article I will show you what happens to the real-life query when I have replaced the PL/SQL function call by its content.

December 9, 2019

AWR bulk collect

Filed under: Oracle — hourim @ 5:08 pm

There are many posts out there explaining how to collect a bunch of AWR reports which I could have just referenced and leave it there, but as much for my own documentation as anything else, I decided to write this simple note as an answer to my client request of generating and analyzing 49 one-hour AWR reports.

First, the SQL script to generate a bulk of AWR reports between two dates:

-- +----------------------------------------------------------------------------+
-- | Author : Mohamed Houri                                                     |
-- |----------------------------------------------------------------------------|
-- | DATABASE : 12cR1                                                           |
-- | Name     : AwrBulkcollect.sql                                              |
-- | PURPOSE  : Dynamically create a SQL script to generate a list of AWR       |
-- |            reports between two snapshots dates.                            |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- | Remarks : CHR(10) adds a new line                                          |
-- |           SET termout off so that AWR content will not be displayed        |
-- |           For RAC configuration consider the instance number               |       
-- |          Enter input date in this format :'mmddyyyy hh24:mi:ss'            |
-- |                                                                            |
-- ------------------------------------------------------------------------------
set termout off
set head off
set define off
spool collectAWRs.sql

SELECT
    'spool awr_XYZ_inst_1_'
    || t.si
    || '_'
    || t.se
    || '.text '
    || CHR(10)
    || 'SELECT * FROM TABLE(dbms_workload_repository.awr_report_text('
    || t.dbid
    || ','
    || t.instance_number
    || ','
    || t.si
    || ','
    || t.se
    || '));'
    || CHR(10)
    || ' spool off;'
FROM
    (SELECT
            dbid,
            snap_id si,
            snap_id + 1 se,
            instance_number
     FROM
            dba_hist_snapshot
     WHERE
            begin_interval_time >   TO_DATE('27102019 18:00:00', 'ddmmyyyy hh24:mi:ss')
      AND end_interval_time   <=  TO_DATE('27102019 19:30:00', 'ddmmyyyy hh24:mi:ss')
      AND  instance_number = 1
      ) t;
	
spool off;
-- +-------------------------------------+	
  prompt running collectAWRs.sql script |
-- +-------------------------------------+
@collectAWRs.sql

Depending on the number of snapshots found between the two entered dates the output of the script might look like the following:

spool awr_XYZ_inst_1_11923_11924.text                                                                                   
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(4209020461,1,11923,11924));                                
spool off;                                                                                                             
                                                                                                                        
spool awr_XYZ_inst_1_11922_11923.text                                                                                   
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(4209020461,1,11922,11923));                                
spool off;     

etc.…

When the script ends you can see such a list of AWR files

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       10/28/2019   2:45 PM         451751 awr_XYZ_inst_1_11946_11947.text
-a----       10/28/2019   2:46 PM         464089 awr_XYZ_inst_1_11952_11953.text
-a----       10/28/2019   2:47 PM         446377 awr_XYZ_inst_1_11945_11946.text
-a----       10/28/2019   2:48 PM         494261 awr_XYZ_inst_1_11949_11950.text
-a----       10/28/2019   2:49 PM         447109 awr_XYZ_inst_1_11954_11955.text
-a----       10/28/2019   2:50 PM         440269 awr_XYZ_inst_1_11956_11957.text
-a----       10/28/2019   2:51 PM         448697 awr_XYZ_inst_1_11955_11956.text
-a----       10/28/2019   2:52 PM         441979 awr_XYZ_inst_1_11942_11943.text
-a----       10/28/2019   2:53 PM         444789 awr_XYZ_inst_1_11943_11944.text
-a----       10/28/2019   2:54 PM         424755 awr_XYZ_inst_1_11947_11948.text
-a----       10/28/2019   2:55 PM         441857 awr_XYZ_inst_1_11951_11952.text
-a----       10/28/2019   2:55 PM         445155 awr_XYZ_inst_1_11944_11945.text
-a----       10/28/2019   2:56 PM         468731 awr_XYZ_inst_1_11948_11949.text
-a----       10/28/2019   2:57 PM         449185 awr_XYZ_inst_1_11950_11951.text
-a----       10/28/2019   2:58 PM         428541 awr_XYZ_inst_1_11953_11954.text
-a----       10/28/2019   2:59 PM         442711 awr_XYZ_inst_1_11957_11958.text

Top 10 Foreground Events

This list of AWR reports has been requested by the Oracle support so that tuning suggestions can be given to my client. One of the suggestions given by Oracle was that reducing the number of log file sync wait event will help improving the performance of the application. I was curious to know what in the AWR reports list makes Oracle support suggesting this log file sync issue. So, I decided to examine the 45 Top 10 Foreground Events to see how much the log file sync wait event participate to the % of the total DB time.

For that, I used the following simple PowerShell script to generate a single file containing only the Top 10 Foreground Events of the totality of the 45 AWR reports:

$SearchString = 'Top 10 Foreground Events by Total Wait Time'
$AllAwrs=Get-ChildItem -Recurse -Include *.text	
for ($i=0; $i -lt $AllAwrs.Count; $i++)
 {
	$result = Select-String $SearchString $AllAwrs[$i].FullName -Context 0, 14 | % {$_.Context.PostContext} 
    $result |  Out-File Top10ForegroundWaitevents.txt   -Append	
 }     	


And, finally, all what I have to do was to analyze the Top10ForegroundWaitevents.txt file

 Event                                 Waits Time Avg(ms)   time Wait Class 
------------------------------ ------------ ---- ------- ------ ---------- 
DB CPU                                      1820           55.2            
db file scattered read               44,406 1032      23   31.3 User I/O   
db file sequential read           2,346,969 735.       0   22.3 User I/O   
direct path write temp                4,085 50.1      12    1.5 User I/O   
log file sync                         3,688 18.3       5     .6 Commit     --> only 0.6%
direct path read temp                13,173 14.2       1     .4 User I/O   
enq: TX - row lock contention           133    1       7     .0 Applicatio 
control file sequential read         11,846   .6       0     .0 System I/O 
db file parallel read                    13   .6      43     .0 User I/O   
direct path read                      2,556   .5       0     .0 User I/O   
                                                                           
                                            Tota    Wait   % DB            
Event                                 Waits Time Avg(ms)   time Wait Class 
------------------------------ ------------ ---- ------- ------ ---------- 
db file scattered read              212,869 3439      16   94.1 User I/O   
DB CPU                                      422.           11.5            
control file sequential read         11,846   .5       0     .0 System I/O 
OJVM: Generic                           139   .5       3     .0 Other      
db file sequential read               5,270   .3       0     .0 User I/O   
log file sync                            67   .3       4     .0 Commit     --> almost 0.0% 
Disk file operations I/O                373    0       0     .0 User I/O   
direct path read                        108    0       0     .0 User I/O   
SQL*Net message to client             1,706    0       0     .0 Network    
direct path sync                          1    0       2     .0 User I/O   
                                                                           
                                            Tota    Wait   % DB            
Event                                 Waits Time Avg(ms)   time Wait Class 
------------------------------ ------------ ---- ------- ------ ---------- 
DB CPU                                      3918           91.8            
db file scattered read               11,553  235      20    5.5 User I/O   
db file sequential read              28,353 87.4       3    2.0 User I/O   
log file sync                         9,537   78       8    1.8 Commit     --> only 1.8% 
resmgr:become active                      4   10    2512     .2 Scheduler  
enq: TX - row lock contention           344  4.3      13     .1 Applicatio 
direct path read temp                 1,241    1       1     .0 User I/O   
direct path read                      6,324    1       0     .0 User I/O   
direct path write temp                  130   .8       6     .0 User I/O   
control file sequential read         11,871   .7       0     .0 System I/O

Etc.….

Bottom-line

In this very simple note I gave a SQL script to generate multiple AWR reports between two dates. I have also examined, via a PowerShell script, how to loop over the content of those AWR reports and extract only an AWR section into a single file for a better and clean analysis.

November 28, 2019

DOP Downgrade

Filed under: Oracle — hourim @ 5:53 pm

Suppose you are confronted to a performance issue due to an initially optimized parallel execution plan that has been 100% DOP downgraded. Suppose also that this DOP downgrade is due to an Oracle Resource Manager max DOP limit directive.
Would you be able to point out this DOP downgrade root cause using gv$sql_monitor view? Would you be able as well to say, a posteriori (using AWR tables), that your issue was due to ORM max DOP limit?

This blog post aims to answer the above two questions.

1.Replaying the issue using a reproducible model

While it is not so straightforward to link a real-life performance issue to a DOP downgrade due to a resource manager maxim allowed DOP, it is nevertheless, very easy to model and reproduce this case at will.

RDEV>  create table t1 as select a.* from dba_objects a,dba_objects where rownum<= 1e6;

RDEV2> alter table t1 parallel 4;

RDEV2> select count(1) from t1 where owner >= 'A';

Parallel Execution Details (DOP=4 , Servers Allocated=4)
===================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    |
|                |       |         | Time(s) | Time(s) | Waits(s) |
===================================================================
| PX Coordinator | QC    |         |    0.12 |    0.01 |          |
| p000           | Set 1 |       1 |    0.20 |    0.03 |     0.15 |
| p001           | Set 1 |       2 |    0.20 |    0.03 |     0.15 |
| p002           | Set 1 |       3 |    0.20 |    0.04 |     0.15 |
| p003           | Set 1 |       4 |    0.20 |    0.04 |     0.12 |
===================================================================
-------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  |    TQ  |IN-OUT|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |        |      |
|   1 |  SORT AGGREGATE        |          |     1 |        |      |
|   2 |   PX COORDINATOR       |          |       |        |      |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |  Q1,00 | P->S |
|   4 |     SORT AGGREGATE     |          |     1 |  Q1,00 | PCWP |
|   5 |      PX BLOCK ITERATOR |          |  1000K|  Q1,00 | PCWC |
|*  6 |       TABLE ACCESS FULL| T1       |  1000K|  Q1,00 | PCWP |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter("OWNER">='A')

Note
-----
   - Degree of Parallelism is 4 because of table property
 

As you can see, the Degree Of Parallelism 4 has been honored and the SQL query has been executed using Parallelism.
However, if under the hood, I create an Oracle Resource Manager which will limit the maximum DOP allowed to be used by the current user (RDEV) then the same query will be DOP downgraded causing performance pain like
in one of my real-life case (a blog post will follow):

BEGIN
-- clear pending area
  DBMS_RESOURCE_MANAGER.clear_pending_area();
  DBMS_RESOURCE_MANAGER.create_pending_area();
  
-- create resource plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'DOPDOWNGRAD_RPL',
    comment => 'Resource Plan limiting DOP');
    
-- create consumer group
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'DOPDONWGRAD_RSG',
    comment        => 'LOW Parallel Priority group');

-- assign the DOP limitation to the consumer group
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan                     => 'DOPDOWNGRAD_RPL',
    group_or_subplan         => 'DOPDONWGRAD_RSG',
    comment                  => 'LOW PDOP Priority',    
    parallel_degree_limit_p1 => 1);

-- This is mandatory to avoid ORA-29377: consumer group OTHER_GROUPS is not part of top-plan DOPDOWNGRAD_RPL
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN             => 'DOPDOWNGRAD_RPL',
    GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
    MGMT_P1          => 10,
    COMMENT          => 'Directive for OTHER_GROUPS (mandatory)');
    
 -- validate the resource plan
  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

The preceding anonymous PL/SLQ block creates the DOPDOWNGRAD_RPL resource plan, attaches to it the DOPDONWGRAD_RSG consumer group and assigns a directive to this consumer group so that maximum DOP allowed by this consumer group would be 1.

To finish the setup, I will assign the user RDEV to this consumer group and set the DOPDOWNGRAD_RPL plan as the default resource plan:

-- Affect user RDEV to resource group
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE      => DBMS_RESOURCE_MANAGER.ORACLE_USER,
       VALUE          => 'RDEV',
       CONSUMER_GROUP => 'DOPDONWGRAD_RSG');
	  
   DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
   DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

We are now ready to run again the same query and check whether it has been executed parallelly or it has been pre-empted by the DOPDOWNGRAD_RPL resource plan to use parallelism:

RDEV> alter system set resource_manager_plan='DOPDOWNGRAD_RPL';

System altered.

RDEV> show parameter resource_manager_plan

NAME                    TYPE        VALUE
----------------------- ----------- ----------------
resource_manager_plan   string      DOPDOWNGRAD_RPL

Let’s execute the same query using RDEV user. Remember that this user belongs to an oracle resource manager group with a parallel degree limit directive as shown above:

SQL> SELECT /*+ monitor */ count(1) from t1 where owner >= 'A';

  COUNT(1)
----------
   1000000

RDEV> @xpsimp

SQL_ID  b60wdaqys7wf9, child number 0
-------------------------------------
SELECT /*+ monitor */ count(1) from t1 where owner >= 'A'

Plan hash value: 3110199320
---------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes |   TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |       |       |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |     4 |       |      |            |
|   2 |   PX COORDINATOR       |          |       |       |       |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |     4 | Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |     4 | Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  1000K|  3906K| Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       |  1000K|  3906K| Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter("OWNER">='A')

The absence of the Note line at the bottom of the execution plan represents, already, a first indication that the query has not been executed using parallelism. Indeed, this is confirmed via the corresponding SQL Monitoring report:

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)     
 Instance ID         :  1                   
 Session             :  RDEV (273:24189)    
 SQL ID              :  b60wdaqys7wf9       
 SQL Execution ID    :  16777216            
 Execution Started   :  11/07/2019 09:02:53 
 First Refresh Time  :  11/07/2019 09:02:53 
 Last Refresh Time   :  11/07/2019 09:02:53 
 Duration            :  .414398s            
 Module/Action       :  SQL*Plus/-          
 Service             :  diiqazbx            
 Program             :  sqlplus.exe         
 DOP Downgrade       :  100%        --> here            
 Fetch Calls         :  1   

The ACTIVE type of the same SQL Monitoring report shows the reason:

Other Plan Line Statistics

   Min DOP after downgrade 1
         downgrade reason  352

Thanks to Yasin Baskan, I knew that the description of the downgrade reason codes differs from release to release. We need to use the following query in the corresponding database (12cR2 in the current case) to get the actual significance of each code:

-- +-----------------------------------|
-- query supplied by Yasin Baskan
-- SQL> desc x$qksxa_reason
--		 Name           Type
--		 -------------- ---------------|
--		 ADDR           RAW(8)
--		 INDX           NUMBER
--		 INST_ID        NUMBER
--		 CON_ID         NUMBER
--		 QKSXAREASONS   VARCHAR2(255)
-- +-----------------------------------|
col qksxareasons format a100
col indx         format 999
SELECT
    indx
   ,qksxareasons
FROM
    x$qksxa_reason
WHERE
    qksxareasons like '%DOP downgrade%';   	


INDX QKSXAREASONS
---- -----------------------------------------------------
 351 DOP downgrade due to adaptive DOP
 352 DOP downgrade due to resource manager max DOP
 353 DOP downgrade due to insufficient number of processes
 354 DOP downgrade because slaves failed to join

We finally, have found the real root cause of the simulated performance issue: 352 DOP downgrade due to resource manager max DOP

2. Back to the aim of the article

The first questions that came to my mind when I have started troubleshooting the DOP Downgrade real-life case were:

• Can I find any Oracle Resource manager related information in the gv$sql_monitor view ?
• Can I find historical (AWR) execution statistics of any active Oracle Resource manager actions ?

2.1. v$sql_monitor

As per regards to the first question, hopefully the gv$sql_monitor view contains 4 columns which seem to be strictly related to the Oracle resource manager as shown below with column name starting by RM_:

SQL> @desc gv$sql_monitor

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INST_ID                                            NUMBER
 KEY                                                NUMBER
 REPORT_ID                                          NUMBER
 STATUS                                             VARCHAR2(19)
 USER#                                              NUMBER
 USERNAME                                           VARCHAR2(128)
../..
 RM_LAST_ACTION                                     VARCHAR2(48)
 RM_LAST_ACTION_REASON                              VARCHAR2(128)
 RM_LAST_ACTION_TIME                                DATE
 RM_CONSUMER_GROUP                                  VARCHAR2(128)

Let’s see then what Oracle has stored in those four RM_ columns:

RDEV> SELECT
 decode(process_name, 'ora', 'QC', process_name) as process_name
 ,rm_consumer_group
 ,rm_last_action
 ,rm_last_action_reason
 ,rm_last_action_time
     FROM
        gv$sql_monitor
    WHERE 
        sql_id = 'b60wdaqys7wf9';

PROCES RM_CONSUMER_GROUP RM_LAST_ACTION RM_LAST_ACTION_REASO RM_LAST_ACTION_TIME
------ ----------------- -------------- -------------------- --------------------
QC     DOPDONWGRAD_RSG   {null}               {null}               {null}

Unfortunately, it looks like only the column RM_CONSUMER_GROUP is filled up by Oracle. The remaining RM_ columns are always null.

However, it looks like that whenever the column RM_CONSUMER_GROUP contains a value which is not equal to OTHER_GROUPS (like in the current example where RM_CONSUMER_GROUP= DOPDONWGRAD_RSG), we can infer that the current monitored query
is subject to a resource manager directive which might influence its execution.

For example, let’s execute the same query when connected with a different user (RDEV2) and see what information will be stored in the gv$sql_monitor view:

RDEV2> SELECT /*+ monitor */ count(1) from t1 where owner >= 'A'

COUNT(1)
----------
   1000000

SQL_ID  b60wdaqys7wf9, child number 2
-------------------------------------
SELECT /*+ monitor */ count(1) from t1 where owner >= 'A'
Plan hash value: 3110199320
--------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |        |      |            |
|   2 |   PX COORDINATOR       |          |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  1000K|  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       |  1000K|  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter("OWNER">='A')

Note
-----
   - Degree of Parallelism is 4 because of table property

RDEV2> SELECT  
   username
  ,decode(process_name, 'ora', 'QC', process_name) as process_name
  ,rm_consumer_group
  ,rm_last_action
  ,rm_last_action_reason
  ,rm_last_action_time
      FROM
        gv$sql_monitor
      WHERE 
        sql_id = 'b60wdaqys7wf9'
order by last_refresh_time;

USERNAME  PROCES  RM_CONSUMER_GROUP  RM_LAST_ACTION  RM_LAST_ACTION_REASO  RM_LAST_ACTION_TIME
-------- -------- ----------------- ---------------- -------------------- --------------------
RDEV      QC     DOPDONWGRAD_RSG    {null}           {null}                {null}
RDEV2     QC     OTHER_GROUPS       {null}           {null}                {null}                 
{null}    p002   OTHER_GROUPS       {null}           {null}                {null}                 
{null}    p000   OTHER_GROUPS       {null}           {null}                {null}                 
{null}    p003   OTHER_GROUPS       {null}           {null}                {null}                 
{null}    p001   OTHER_GROUPS       {null}           {null}                {null}     

When the same query is executed by user RDEV2 there is no DOP downgrade because this user doesn’t belong to any user created resource manager group. There are two information backing this claim:

• User RDEV2 has OTHER_GROUPS as a default consumer group
• There is 4 PX server associated with the QC of user RDEV2

3.2. AWR and Oracle Resource manager
The second investigation was to see whether I can, a posteriori, diagnose that my query underwent a resource manager directive; particularly those concerning parallel degree limit. For that reason, I wrote the following script

SQL> @getConsGrpHist
SELECT
   begin_time
  ,execs 
  ,avg_etime
  ,avg_px
 -- ,trunc(avg_etime/decode(avg_px,0,1,avg_px) ,2) avg_px_time
  ,plan_name
  ,cpu_managed
  ,parallel   
FROM
   (SELECT 	   
        sn.begin_interval_time      begin_time
       ,st.executions_delta         execs 
       ,st.end_of_fetch_count_delta end_of_fetch 
       ,trunc(st.elapsed_time_delta/1e6/decode(st.executions_delta, 0, 1,st.executions_delta))   avg_etime
       ,round(st.px_servers_execs_delta/decode(st.executions_delta,0,1, st.executions_delta), 1) avg_px	  
       ,sr.plan_name
       ,sr.cpu_managed
       ,sr.parallel_execution_managed parallel    
    FROM 
         dba_hist_rsrc_plan sr
        ,dba_hist_snapshot  sn
        ,dba_hist_sqlstat   st
    WHERE
	sr.snap_id = sn.snap_id
    AND sn.snap_id = st.snap_id
    AND sn.begin_interval_time between to_date('&from_date', 'ddmmyyyy hh24:mi:ss')
				      and    to_date('&to_date'  , 'ddmmyyyy hh24:mi:ss')
    AND st.sql_id ='&sql_id'
    AND plan_name != 'DEFAULT_MAINTENANCE_PLAN'	
	) 
order by begin_time asc;      

SQL> @GetConsGrpHist
Enter value for from_date: 08112019 15:07:00
Enter value for to_date: 08112019 15:09:00
Enter value for sql_id: b60wdaqys7wf9

BEGIN_TIME                 EXECS  AVG_ETIME  AVG_PX PLAN_NAME       CPU_MANAGED PARALLEL
------------------------- ------ ---------- ------- --------------- ----------- ----------
08-NOV-19 03.07.08.369 PM      1          0       0 DOPDOWNGRAD_RPL ON          FULL
08-NOV-19 03.07.08.369 PM      1          0       0 DOPDOWNGRAD_RPL ON          FULL

Indeed, I can see that my query has used a non-default resource plan (DOPDOWNGRAD_RPL) in which a parallel statement queuing is enabled as indicated by the FULL value of the PARALLEL column.

Bottom-line

In this simple Note I outlined two main points:

• Show how to identify a DOP downgrade and its reason
• Show how to link a non-default Oracle resource manager to a SQL_ID by checking the value of the column RM_CONSUMER_GROUP from the gv$sql_monitor view
• Show that the remaining 3 resource manager columns (RM_LAST_ACTION, RM_LAST_ACTION_REASON, RM_LAST_ACTION_TIME) in the gv$sql_monitor view are, unfortunately, not maintained by Oracle
• Show how to look in AWR historical execution for queries subject to user defined Oracle Resource Manager directives

November 21, 2019

Incremental, histogram and fix control

Filed under: Statistics — hourim @ 5:35 pm

Due to my zeal to understand the curious comportment of the non-usage of histogram in a partitioned table under incremental mode, I decided to write this 4th part article in the series of blog posts on wrong cardinality estimation due to the non-utilization of histogram during query optimization:

  •  Part I where I wrongly put the blame of wrong cardinality estimation on corrupted histogram
  •  Part II where I examined via a real-life example the impact of this wrong cardinality estimation on the query performance
  •  Part III where I showed that, in fact, the wrong cardinality estimation is due to the incremental mode which, under certain circumstances, forces Oracle to ignore the usage of histogram

In this 4th Part, I would like to share with you a couple of ways you can use to make Oracle not ignoring the usage of histogram while keeping the incremental mode up (TRUE).

1. Fix Control 13583722

In Part III I mentioned that the dbms_stats trace file shows the presence of the 13583722 fix control

DBMS_STATS:          Need Actual Values (DSC_EAVS)                    
DBMS_STATS:          Partition: XZ_JOB_T_12                           
DBMS_STATS:          Histogram Type: HYBRID Data Type: 2              
DBMS_STATS:          Histogram Flags: 4 Histogram Gathering Flags: 10 
DBMS_STATS:          Incremental: TRUE Fix Control 13583722: 1        

The definition of this fix control is :

SQL> select  bugno, description from V$SYSTEM_FIX_CONTROL where bugno = '13583722';

     BUGNO DESCRIPTION
---------- ----------------------------------------------------------------
  13583722 preserve partition level histograms & actual values for incremen

Let’s see what happens if I disable this fix control?

But first, here’s below the cardinality estimation before modifying the fix control :

select /*+ full(a) */
         count(1)
     from
        XZ_JOB a
     where EMPLID>= 'XZ990501004'
     and   EMPLID<= 'XZ990562000'; 

  COUNT(1)
----------
     44724

Plan hash value: 2916242030
----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE    |        |     7 |    42 |    42 |
|*  3 |    TABLE ACCESS STORAGE FULL| XZ_JOB |     7 |    42 |    42 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - storage("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000')
       filter("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000')

This wrong cardinality estimation of 7 instead of 44K, is due to Oracle ignoring the usage of histogram when visiting partition n°42

@getPartTab
compute sum label 'Total num_rows' of num_rows on report
break   on report
set verify off
select 
    table_name
   ,partition_name     pname
   ,partition_position ppos
   ,num_rows
from    
  (select
       table_name
      ,partition_name      
      ,partition_position  	
      ,num_rows	   
   from
      all_tab_partitions
  where 
    table_owner = upper('&owner')
  and
    table_name = upper('&table_name')
);

Enter value for owner: sysadm
Enter value for table_name: XZ_JOB

Enter value for owner: sysadm
Enter value for table_name: XZ_JOB

TABLE_NAME   PNAME             PPOS   NUM_ROWS 
------------ --------------- ------ ---------- 
XZ_JOB       XZ_JOB_T_1           1     721565 
XZ_JOB       XZ_JOB_T_2           2     213356 
XZ_JOB       XZ_JOB_T_3           3      35906 
../..
XZ_JOB       XZ_JOB_T_42         42      46919  -->
XZ_JOB       XZ_JOB_T_43         43      39403 
XZ_JOB       XZ_JOB_T_48         48     110980 
XZ_JOB       XZ_JOB_T_49         49    1342747 
                                      ----------
Total num_rows                         5118455

@PartHist
SQL> select
       partition_name
      ,num_distinct
      ,density
      ,histogram
       ,notes
    from
        all_part_col_statistics
    where owner    = 'SYSADM'
    and table_name = 'XZ_JOB'
    and column_name = 'EMPLID';

PARTITION_NAME  NUM_DISTINCT    DENSITY HISTOGRAM  NOTES
--------------- ------------ ---------- ---------- ---------------------
XZ_JOB_T_1             14954    ,000067 HYBRID
XZ_JOB_T_2              5141    ,000035 HYBRID
XZ_JOB_T_3              1059    ,000941 HYBRID     HIST_FOR_INCREM_STATS
../..
XZ_JOB_T_42             6372     ,00008 HYBRID     HIST_FOR_INCREM_STATS -->
XZ_JOB_T_43             6071    ,000165 HYBRID     HIST_FOR_INCREM_STATS
XZ_JOB_T_48            21812    ,000046 HYBRID     HIST_FOR_INCREM_STATS
XZ_JOB_T_49           243840    ,000004 HYBRID     HIST_FOR_INCREM_STATS

49 rows selected.

E-Rows = partition num_rows/ NDV = 46919/6372 = 7.36~7

And here’s the new cardinality estimation after the fix control change

SQL> alter session set "_fix_control" = '13583722:0';

SQL> exec dbms_stats.delete_table_stats
          (ownname=>'SYSADM',tabname=>'XZ_JOB',partname=>'XZ_JOB_T_42');

SQL> exec dbms_stats.gather_table_stats
        (ownname=>'SYSADM',tabname=>'XZ_JOB',          
         partname=>'XZ_JOB_T_42',granularity=>'PARTITION');

@PartHist
PARTITION_NAME  NUM_DISTINCT    DENSITY HISTOGRAM NOTES
--------------- ------------ ---------- --------- ----------------------
XZ_JOB_T_1             14954    ,000067 HYBRID
XZ_JOB_T_2              5141    ,000035 HYBRID
XZ_JOB_T_3              1059    ,000941 HYBRID    HIST_FOR_INCREM_STATS
../..
XZ_JOB_T_42             6372    ,000082 HYBRID
XZ_JOB_T_43             6071    ,000165 HYBRID    HIST_FOR_INCREM_STATS
XZ_JOB_T_48            21812    ,000046 HYBRID    HIST_FOR_INCREM_STATS
XZ_JOB_T_49           243840    ,000004 HYBRID

49 rows selected.

select /*+ full(a) */
         count(1)
     from
        XZ_JOB a
     where EMPLID>= 'XZ990501004'
     and   EMPLID<= 'XZ990562000'
  COUNT(1)
----------
     44724

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE    |        | 44649 |    42 |    42 |
|*  3 |    TABLE ACCESS STORAGE FULL| XZ_JOB | 44649 |    42 |    42 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - storage(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))
       filter(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))

As you can see the fix control 13583722 has made Oracle not ignoring the usage of histogram when visiting partition n°42 (Notes column is null) which explains why the new cardinality estimation is excellent.

2. Fix Control 16726844

As far as fix control goes I decided to check whether I can find any other fix control that might be related to this incremental-histogram issue:

SQL> select bugno, value, description
     from v$system_fix_control
     where description like '%incremen%';

     BUGNO      VALUE DESCRIPTION
---------- ---------- ----------------------------------------------------------------
  10175079          1 increment kafcoref to simulate obsolete fropqop list
  13583722          1 preserve partition level histograms & actual values for incremen
  16726844          1 consider histogram request in incremental stats gathering

The fix control 16726844 seems to be also related to the usage of histogram when incremental mode is set to TRUE. Let’s change its value and check again (before changing the fix control I have of course reproduced the wrong cardinality estimation of 7):

SQL> alter session set "_fix_control" = '16726844:0';

SQL> exec dbms_stats.delete_table_stats(ownname=>'SYSADM',tabname=>'XZ_JOB',partname=>'XZ_JOB_T_42');

SQL> exec dbms_stats.gather_table_stats(ownname=>'SYSADM',tabname=>'XZ_JOB', partname=>'XZ_JOB_T_42',granularity=>'PARTITION');

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE    |        | 44598 |    42 |    42 |
|*  3 |    TABLE ACCESS STORAGE FULL| XZ_JOB | 44598 |    42 |    42 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------
   3 - storage(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))
       filter(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000')))

Indeed, this fix control has also made Oracle using the histogram when visiting partition n°42.

3. For columns size 254 EMPLID

The last option I have checked and used to workaround this issue of not using histogram in presence of incremental mode is to force histograms to be kept by manually specifying bucket size 254:

  method_opt => 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 EMPLID'
SQL> exec dbms_stats.delete_table_stats(ownname=>'SYSADM',tabname=>'XZ_JOB');

SQL> BEGIN 
        dbms_stats.gather_table_stats
        (ownname=>'SYSADM',tabname=>'XZ_JOB'
         ,method_opt => 'for all columns size auto, for columns size 254 emplid'
	 ,degree => 4);
     END;
     /

SQL> @GlobHist

TABLE_NAME   COLUMN_NAME   HISTOGRAM       NOTES
------------ ------------- --------------- ------------
XZ_JOB       EMPLID        HYBRID          INCREMENTAL

SQL> @PartHist

PARTITION_NAME  NUM_DISTINCT    DENSITY HISTOGRAM       NOTES
--------------- ------------ ---------- --------------- -----------
XZ_JOB_T_1             14954    ,000067 HYBRID
XZ_JOB_T_10             1232    ,000805 HYBRID
XZ_JOB_T_11             1191    ,000832 HYBRID
XZ_JOB_T_12             1153    ,000861 HYBRID
XZ_JOB_T_13             1187    ,000837 HYBRID
XZ_JOB_T_14             1390    ,000703 HYBRID
XZ_JOB_T_15             1733    ,000555 HYBRID
XZ_JOB_T_16             1950    ,000476 HYBRID
XZ_JOB_T_17             1842    ,000512 HYBRID
XZ_JOB_T_18             1584    ,000611 HYBRID
XZ_JOB_T_19             1714     ,00056 HYBRID
XZ_JOB_T_2              5141    ,000031 HYBRID
XZ_JOB_T_20             1410    ,000695 HYBRID
XZ_JOB_T_21             1460    ,000671 HYBRID
XZ_JOB_T_22             1899    ,000493 HYBRID
XZ_JOB_T_23             1916    ,000489 HYBRID
XZ_JOB_T_24             1568    ,000618 HYBRID
XZ_JOB_T_25             1550    ,000627 HYBRID
XZ_JOB_T_26             1697    ,000568 HYBRID
XZ_JOB_T_27             1637    ,000593 HYBRID
XZ_JOB_T_28             1528    ,000639 HYBRID
XZ_JOB_T_29             1629    ,000595 HYBRID
XZ_JOB_T_3              1059    ,000941 HYBRID
XZ_JOB_T_30             1479    ,000662 HYBRID
XZ_JOB_T_31             1480    ,000661 HYBRID
XZ_JOB_T_32             1839    ,000514 HYBRID
XZ_JOB_T_33             3083    ,000319 HYBRID
XZ_JOB_T_34             7756    ,000028 HYBRID
XZ_JOB_T_35             2789    ,000359 HYBRID
XZ_JOB_T_36             3669    ,000273 HYBRID
XZ_JOB_T_37             7947    ,000071 HYBRID
XZ_JOB_T_38             3277    ,000305 HYBRID
XZ_JOB_T_39             8466    ,000118 HYBRID
XZ_JOB_T_4               960     ,00104 HYBRID
XZ_JOB_T_40             7836    ,000044 HYBRID
XZ_JOB_T_41             4873    ,000162 HYBRID
XZ_JOB_T_42             6372    ,000077 HYBRID
XZ_JOB_T_43             6071    ,000165 HYBRID
XZ_JOB_T_44             9642    ,000104 HYBRID
XZ_JOB_T_45             9121     ,00011 HYBRID
XZ_JOB_T_46             8028    ,000125 HYBRID
XZ_JOB_T_47             5635    ,000177 HYBRID
XZ_JOB_T_48            21812    ,000046 HYBRID
XZ_JOB_T_49           243840    ,000004 HYBRID
XZ_JOB_T_5              1764    ,000541 HYBRID
XZ_JOB_T_6              1379    ,000712 HYBRID
XZ_JOB_T_7              1050    ,000949 HYBRID
XZ_JOB_T_8              1016    ,000982 HYBRID
XZ_JOB_T_9              1032    ,000965 HYBRID
                
49 rows selected.
SQL> select /*+ full(a) */
  2           count(1)
  3       FROM
  4          XZ_JOB a
  5       where EMPLID>= 'XZ990501004'
  6       and   EMPLID<= 'XZ990562000'; 
  COUNT(1)
----------
     44724

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |       |
|   1 |  SORT AGGREGATE             |        |     1 |       |       |
|   2 |   PARTITION RANGE SINGLE    |        | 44645 |    42 |    42 |
|*  3 |    TABLE ACCESS STORAGE FULL| XZ_JOB | 44645 |    42 |    42 |
----------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_fix_control' '18302923:1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - storage(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))
       filter(("EMPLID">='XZ990501004' AND "EMPLID"<='XZ990562000'))

Again, changing the method_opt parameter value so that histogram on the EMPLID column is forced has made Oracle not only create the HYBRID histogram but to use them during query optimization even when the incremental mode is set to TRUE.

4. Bottom-line

Understandably, when incremental mode is set to TRUE Oracle might ignore the usage of histogram during query optimization. To avoid wrong cardinality estimation in such a situation you can use one of the following fixes:

  •  Set the preference incremental mode to FALSE (at table or global level)
  •  Unset the fix control 13583722
  •  Unset the fix control 16726844
  •  Force Oracle to create histogram on the desired column by manually specifying the bucket size 254:
     method_opt => 'for all columns size auto, for columns size 254 COL1' 

November 14, 2019

Incremental and ruin

Filed under: Oracle — hourim @ 8:02 pm

It’s hardly a secret that many people are not fan of histograms. The main problem of them is that they are not very well documented, and they drive, under the hood, many other features like Adaptive Cursor Sharing. They also interact with gathering statistics for partitioned tables when the incremental preference is set to TRUE.

As you might know, if you have read my two preceding articles here and here, I have been struggling with recurrent PeopleSoft performance issues because of a wrong cardinality estimation when visiting a partitioned table. I couldn’t figure out exactly the root cause of this cardinality mis-estimation so that I opened an Oracle Service Request (SR). As an answer to my request, Oracle support send me a reference to my two articles mentioned above😊. While I was still waiting for a viable answer from the Oracle support, the same performance issue kicked in and I was asked to solve it as quickly as possible.

After a couple of thinking minutes, I decided to change the incremental preference of that partitioned table and regather its statistics as in the following:

SQL> exec dbms_stats.set_table_prefs('SYSADM', 'PS_XYZ', 'incremental', 'false');
SQL> exec dbms_stats.gather_table_stats('SYSADM', 'PS_XYZ',degree => 4);

This made the culprit query, identified by the SQL_ID 147gz7h1x7jkq, going from a non-ending 42,886 seconds

Global Information
------------------------------
 Status              :  EXECUTING                         
 Instance ID         :  1                                 
 Session             :  SYSADM (23:47269)                 
 SQL ID              :  147gz7h1x7jkq                     
 SQL Execution ID    :  16777219                          
 Execution Started   :  08/28/2019 21:19:19               
 First Refresh Time  :  08/28/2019 21:19:25               
 Last Refresh Time   :  08/29/2019 09:14:05               
 Duration            :  42886s                            
 

to about only 5 seconds for the worst case

SQL> @sqlstats
Enter value for sql_id: 147gz7h1x7jkq

     CHILD    INST_ID PLAN_HASH_VALUE   AVG_GETS LAST_ACTI            AVG_ETIME      EXECS
---------- ---------- --------------- ---------- ------------------- ---------- ----------
         0          1      3376574399      23452 29/08/2019 17:09:08   5,357016          1
         0          2       430435019       2961 29/08/2019 17:09:36   1,366556          1

The goal of this article is to explain the negative effect the incremental mode can have on the effective use of histogram during cardinality estimation.

By the way, if you are wondering why the same query has got two different execution plans (3376574399,430435019) at almost the same time, then bear in mind that this is the effect of a GTT table having a SESSION private statistics.

1.Few words about the Setup

This is a PeopleSoft software using Oracle 12cR1 under Exadata machine. The cardinality misestimation occurs when Oracle visits a range (or a range/list) partitioned table of 49 partitions. The partition key is of varchar2(11) data type having a uniform distribution for certain partitions, and a skewed distribution at others. The incremental preference of the partitioned table is set to TRUE.

Here’s an image of the partition key column (EMPLID) statistics when the performance issue occurred (incremental = TRUE) and after it has been fixed (incremental =FALSE) respectively (at global and partition level):

-- incremental = TRUE
SELECT
   table_name
  ,column_name
  ,notes
FROM
  all_tab_col_statistics
WHERE 
    owner       = 'SYSADM'
AND table_name  = 'PS_XYZ'
AND column_name = 'EMPLID';    
  
TABLE_NAME  COLUMN_NAME   HISTOGRAM       NOTES   
----------- ------------- --------------- ----------------------------------
PS_XYZ      EMPLID        HYBRID          INCREMENTAL HIST_FOR_INCREM_STATS        
SQL> select
         partition_name
        ,num_distinct
        ,density
        ,histogram
        ,notes
    from
        all_part_col_statistics
    where owner = 'SYSADM'
    and table_name = 'PS_XYZ'
    and column_name = 'EMPLID';


PARTITION_NAME  NUM_DISTINCT    DENSITY HISTOGRAM       NOTES                 
--------------- ------------ ---------- --------------- ----------------------
PS_XYZ_T_1             19888     ,00005 HYBRID                                
PS_XYZ_T_10             1191    ,000831 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_11             1153    ,000861 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_12             1187    ,000835 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_13             1390    ,000707 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_14             1733    ,000552 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_15             1950    ,000479 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_16             1842    ,000516 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_17             1584    ,000611 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_18             1714     ,00056 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_19             1410    ,000697 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_2              1059    ,000941 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_20             1460    ,000672 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_21             1899    ,000526 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_22             1916     ,00049 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_23             1568    ,000621 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_24             1550    ,000625 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_25             1697    ,000568 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_26             1637     ,00059 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_27             1528    ,000639 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_28             1629    ,000596 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_29             1479    ,000663 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_3               960     ,00104 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_30             1480    ,000662 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_31             1839     ,00052 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_32             3007    ,000284 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_33             7693     ,00013 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_34             2776     ,00036 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_35             3653    ,000215 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_36             7940    ,000126 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_37             3277    ,000305 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_38             8461    ,000118 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_39             7855    ,000127 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_4              1764    ,000567 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_40             4878    ,000205 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_41             6361    ,000157 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_42             6064    ,000165 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_43             9641    ,000104 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_44             9142    ,000109 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_45             8043    ,000124 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_46             5664    ,000078 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_47            21980    ,000045 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_48             9550    ,000105 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_49           270752    ,000004 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_5              1379    ,000725 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_6              1050    ,000952 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_7              1016    ,000984 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_8              1032    ,000969 HYBRID          HIST_FOR_INCREM_STATS 
PS_XYZ_T_9              1232    ,000811 HYBRID          HIST_FOR_INCREM_STATS 

49 rows selected.
-- incremental = FALSE

TABLE_NAME  COLUMN_NAME  HISTOGRAM       NOTES                                
----------- ------------ --------------- ------
PS_XYZ      EMPLID       HYBRID

PARTITION_NAME   NUM_DISTINCT    DENSITY HISTOGRAM       NOTES   
--------------- ------------ ---------- --------------- ---------
PS_XYZ_T_1             19888     ,00005 HYBRID                   
PS_XYZ_T_10             1191 ,000839631 NONE                     
PS_XYZ_T_11             1153    ,000861 HYBRID                   
PS_XYZ_T_12             1187  ,00084246 NONE                     
PS_XYZ_T_13             1390 ,000719424 NONE                     
PS_XYZ_T_14             1733 ,000577034 NONE                     
PS_XYZ_T_15             1950    ,000476 HYBRID                   
PS_XYZ_T_16             1842    ,000515 HYBRID                   
PS_XYZ_T_17             1584 ,000631313 NONE                     
PS_XYZ_T_18             1714     ,00056 HYBRID                   
PS_XYZ_T_19             1410  ,00070922 NONE                     
PS_XYZ_T_2              1059 ,000944287 NONE                     
PS_XYZ_T_20             1460    ,000671 HYBRID                   
PS_XYZ_T_21             1899    ,000526 HYBRID                   
PS_XYZ_T_22             1916    ,000491 HYBRID                   
PS_XYZ_T_23             1568     ,00062 HYBRID                   
PS_XYZ_T_24             1550    ,000626 HYBRID                   
PS_XYZ_T_25             1697    ,000562 HYBRID                   
PS_XYZ_T_26             1637    ,000593 HYBRID                   
PS_XYZ_T_27             1528     ,00064 HYBRID                   
PS_XYZ_T_28             1629 ,000613874 NONE                     
PS_XYZ_T_29             1479    ,000663 HYBRID                   
PS_XYZ_T_3               960 ,001041667 NONE                     
PS_XYZ_T_30             1480    ,000661 HYBRID                   
PS_XYZ_T_31             1839    ,000518 HYBRID                   
PS_XYZ_T_32             3007    ,000288 HYBRID                   
PS_XYZ_T_33             7693     ,00013 HYBRID                   
PS_XYZ_T_34             2776 ,000360231 NONE                     
PS_XYZ_T_35             3653    ,000215 HYBRID                   
PS_XYZ_T_36             7940    ,000126 HYBRID                   
PS_XYZ_T_37             3277 ,000305157 NONE                     
PS_XYZ_T_38             8461    ,000118 HYBRID                   
PS_XYZ_T_39             7855    ,000127 HYBRID                   
PS_XYZ_T_4              1764    ,000567 HYBRID                   
PS_XYZ_T_40             4878 ,000205002 NONE                     
PS_XYZ_T_41             6361    ,000157 HYBRID                   
PS_XYZ_T_42             6064    ,000165 HYBRID                   
PS_XYZ_T_43             9641    ,000104 HYBRID                   
PS_XYZ_T_44             9142    ,000109 HYBRID                   
PS_XYZ_T_45             8043 ,000124332 NONE                     
PS_XYZ_T_46             5664    ,000076 HYBRID                   
PS_XYZ_T_47            21980    ,000045 HYBRID                   
PS_XYZ_T_48             9550    ,000105 HYBRID                   
PS_XYZ_T_49           270944    ,000004 HYBRID                   
PS_XYZ_T_5              1379 ,000725163 NONE                     
PS_XYZ_T_6              1050 ,000952381 NONE                     
PS_XYZ_T_7              1016    ,000984 HYBRID                   
PS_XYZ_T_8              1032 ,000968992 NONE                     
PS_XYZ_T_9              1232 ,000811688 NONE                     

49 rows selected.

As you can see, this is a weird non-common situation. There is a clear contradiction here. How could it be differently when we see that, switching the incremental mode from true to false, switches the same data distribution for the same column at the same partition (PS_JOB_T_10 for example) from skewed (HYBIRD) to non-skewed (NONE)?

In fact, if you look closer you will realize that, as far as query optimization is concerned, there is no contradiction in the following situation:

HYBRID  +  HIST_FOR_INCREM_STATS = NONE

Because, when the CBO sees that the HYBRID histogram is accompanied by the Notes HIST_FOR_INCREM_STATS, it knows exactly that this histogram will be ignored at query optimization time, which is equivalent to not having histogram at all. This is for example correct for partitions like PS_JOB_T_10, PS_JOB_T_12 and PS_JOB_T_13 where column values are evenly distributed. But the real threat resides in partitions like PS_JOB_T_11, PS_JOB_T_15 and PS_JOB_T_16 for example where we have the following situation:

HYBRID  +  HIST_FOR_INCREM_STATS != HYBRID

In this case, the column values are skewed but their histogram are ignored by the CBO during query optimization because of the HIST_FOR_INCREM_STATS Notes. This is exactly the root cause of my performance issue.

  • Regardless of whether the partition key is skewed or not it will have a histogram
  • But this histogram will be ignored both at partition (except partition n°1 in this case) and at global level during query optimization.

Therefore, it becomes clear for me that fixing my performance issue resides in making the CBO not ignoring the presence of histogram at both partition level and global level. And, disabling the incremental mode, is what I did to achieve this goal.

2. Why histograms are ignored?

In the SR I opened for the Oracle support I have supplied the trace file of a dbms_stats call under incremental mode. Something resembling to this (partition n°11 in TEST database contains non-skewed value for the EMPLID column):

SQL> exec dbms_stats.set_global_prefs ('TRACE', to_char (1+16));

SQL> begin
        dbms_stats.gather_table_stats('sysadm'
                                      ,'PS_XYZ'
                                      ,partname  =>'PS_XYZ_T_11'
                                      ,granularity =>'partition');
    end;
   /
SQL> exec dbms_stats.set_global_prefs('TRACE', null);
DBMS_STATS: Building Histogram for EMPLID                                                                          
DBMS_STATS:  bktnum=-1, nnv=50819, snnv=5511,604748383025713834989746016721880424, sndv=1191, est_ndv=1191, mnb=254
DBMS_STATS:  Trying hybrid histogram                                                                               
DBMS_STATS:  > cdn 5592, popFreq 0, popCnt 0, bktSize 22,0750988142292490118577075098814229249
             , bktSzFrc ,0750988142292490118577075098814229249     
                                                
DBMS_STATS:  Evaluating hybrid histogram:  cht.count 254, mnb 254, ssize 5592, min_ssize 2500, appr_ndv  TRUE
             ,ndv 1191, selNdv 0, selFreq 0, 
             pct 10,84555923647262975232686543618867329232, avg_bktsize 22, csr.hreq FALSE, normalize TRUE   
DBMS_STATS:   Checking range skew:                                                                                 
DBMS_STATS:      >> NOT SKEWED                           ----------------->                                                   
DBMS_STATS:  Not Discarding histogram (Incremental mode) ----------------->                                        
DBMS_STATS:   Histogram gathering flags: 4623     

As you can see, Oracle is aware that the EMPLID column has an even distribution in partition n°11 but it decides to do not discard the creation of histogram because of the incremental mode. Oracle, nevertheless, updates the corresponding Notes column to indicate that this histogram will not be used during cardinality estimation.

PARTITION_NAME   NUM_DISTINCT    DENSITY HISTOGRAM       NOTES
---------------- ------------ ---------- --------------- ----------------------
PS_XYZ_T_11              1191    ,000833 HYBRID          HIST_FOR_INCREM_STATS

If, I regather the same statistics for partition n°12 where the EMPLID is skewed (in TEST) I will have the following trace snippet:

DBMS_STATS: Building Histogram for EMPLID                                                                          
DBMS_STATS:  bktnum=-1, nnv=47961, snnv=5501,261730969760166840458811261730969758, sndv=1153, est_ndv=1153, mnb=254
DBMS_STATS:  Trying hybrid histogram                                                                               
DBMS_STATS:  > cdn 5443, popFreq 0, popCnt 0, bktSize 21,49407114624505928853754940711462450593, 
              bktSzFrc ,49407114624505928853754940711462450593                                    
DBMS_STATS:  Evaluating hybrid histogram:  cht.count 254, mnb 254, ssize 5443, min_ssize 2500, appr_ndv  TRUE
            ,ndv 1153, selNdv 0, selFreq 0
            ,pct 11,47028154327424400417101147028154327424, avg_bktsize 21, csr.hreq FALSE, normalize TRUE   
DBMS_STATS:   Checking range skew:                                                                                 
DBMS_STATS:      >> SKEWED                   ------------------------>                                                       
DBMS_STATS:   Histogram gathering flags: 527                                                                       
DBMS_STATS:  Accepting histogram             ------------------------>             

DBMS_STATS:          Need Actual Values (DSC_EAVS)                                                                 
DBMS_STATS:          Partition: PS_XYZ_T_12                                                                        
DBMS_STATS:          Histogram Type: HYBRID Data Type: 2                                                           
DBMS_STATS:          Histogram Flags: 4 Histogram Gathering Flags: 10                                              
DBMS_STATS:          Incremental: TRUE Fix Control 13583722: 1      

This time, Oracle has correctly created the histogram and has left the Notes column empty indicating that the histogram will be used by the CBO when computing cardinality estimation for partition n°12:

PARTITION_NAME    NUM_DISTINCT DENSITY HISTOGRAM          NOTES
---------------- ------------ -------- ----------------- ----------------------
PS_XYZ_T_11       1191        ,000833  HYBRID             HIST_FOR_INCREM_STATS
PS_XYZ_T_12       1153        ,000861  HYBRID

However, at global level, Oracle will still ignore the usage of histogram as the following proves:

SQL> SELECT
       table_name
       ,column_name
       ,histogram
       ,notes
    FROM
      all_tab_col_statistics
    WHERE
     owner = 'SYSADM'
     and table_name ='PS_XYZ'
     and column_name = 'EMPLID';

TABLE_NAME  COLUMN_NAME   HISTOGRAM       NOTES   
----------- ------------- --------------- ----------------------------------
PS_XYZ      EMPLID        HYBRID          INCREMENTAL HIST_FOR_INCREM_STATS        

I think that it suffices to have a single partition with that Note value (HIST_FOR_INCREM_STATS ) to see Oracle ignoring the usage of histogram at global level as well.

In passing, if you have exercised eyes you should have already remarked the control fix in the dbms_stats trace file

DBMS_STATS: Incremental: TRUE Fix Control 13583722: 1

SQL> select description from V$SYSTEM_FIX_CONTROL where bugno = '13583722';

DESCRIPTION
----------------------------------------------------------------
preserve partition level histograms &amp; actual values for increment

For the sake of simplicity, I will postpone publishing my investigations on this fix_control to a separate article.

3. Possible reasons

The following references might help understanding the situation explained in this article and the possible reasons pushing Oracle to keep creating histograms but ignoring their usage during query optimisation process:

4. Bottom-line

As we have seen above, partition level histogram will not be discarded for tables with incremental mode set to TRUE. This happens regardless of whether the column values are skewed or evenly distributed. I think that, the reason behind this is that, under incremental mode, when global histogram aggregations are calculated, missing partition level histograms must be regathered at partition level with even distribution. Probably Oracle decided to keep these histograms at partition level to avoid doing the same thing two times under incremental mode. But it decides, at the same time, to pre-empt the CBO from using the histograms both at global and partition level during query optimization.

All in all, I think that disabling incremental mode is something one should envisage if confronted to a similar performance issue.

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.

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.

Next Page »

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)