Data … as usual

SELECT DISTINCT s.inst_id,
  s.ksusenum,
  o.kglnaown,
  o.kglnaobj,
  o.kglobtyd,
  s.con_id
FROM x$ksuse s,
  x$kglob o,
  x$kgldp d,
  x$kgllk l
WHERE l.kgllkuse=s.addr
AND l.kgllkhdl  =d.kglhdadr
AND l.kglnahsh  =d.kglnahsh
AND o.kglnahsh  =d.kglrfhsh
AND o.kglhdadr  =d.kglrfhdl

First, we’ll have a look at a simple query on V$ACCESS on a 12cR1 instance without any statistics. Notice that I disable cardinality feedback … we’ll see why later.

SQL> exec dbms_stats.delete_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> select rowcnt, blkcnt, analyzetime, samplesize
  2  from tab_stats$
  3  where obj# in (select OBJECT_ID from V$FIXED_TABLE)
  4  /

no rows selected

SQL> select /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */ count(*) from v$access;

  COUNT(*)
----------
       845

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID  bqjbmqr1xtypb, child number 0
-------------------------------------
select /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */ count(*)
from v$access

Plan hash value: 2879213603

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |       |       |     1 (100)|
|   1 |  SORT AGGREGATE              |                 |     1 |       |            |
|   2 |   VIEW                       | GV$ACCESS       |     1 |       |     0   (0)|
|   3 |    HASH UNIQUE               |                 |     1 |   697 |     0   (0)|
|   4 |     NESTED LOOPS             |                 |     1 |   697 |     0   (0)|
|   5 |      NESTED LOOPS            |                 |     1 |   108 |     0   (0)|
|   6 |       HASH JOIN              |                 |     1 |    70 |     0   (0)|
|   7 |        FIXED TABLE FULL      | X$KSUSE         |     1 |    45 |     0   (0)|
|   8 |        FIXED TABLE FULL      | X$KGLLK         |   100 |  2500 |     0   (0)|
|   9 |       FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) |     1 |    38 |     0   (0)|
|  10 |      FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) |     1 |   589 |     0   (0)|
-------------------------------------------------------------------------------------

23 rows selected.

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> select /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */ count(*)
  2  from v$access;

  COUNT(*)
----------
       911

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID  12qyw31bvmptb, child number 0
-------------------------------------
select /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */ count(*)
from v$access

Plan hash value: 2296283840

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE             |                 |     1 |       |            |          |
|   2 |   VIEW                      | GV$ACCESS       |  1226 |       |     1 (100)| 00:00:01 |
|   3 |    HASH UNIQUE              |                 |  1226 |   335K|     1 (100)| 00:00:01 |
|   4 |     NESTED LOOPS            |                 |  1226 |   335K|     1 (100)| 00:00:01 |
|   5 |      HASH JOIN              |                 |  1226 | 91950 |     1 (100)| 00:00:01 |
|   6 |       HASH JOIN             |                 |  1226 | 52718 |     0   (0)|          |
|   7 |        FIXED TABLE FULL     | X$KSUSE         |   472 |  8496 |     0   (0)|          |
|   8 |        FIXED TABLE FULL     | X$KGLLK         |  1226 | 30650 |     0   (0)|          |
|   9 |       FIXED TABLE FULL      | X$KGLDP         |  4524 |   141K|     0   (0)|          |
|  10 |      FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |     1 |   205 |     0   (0)|          |
-----------------------------------------------------------------------------------------------

23 rows selected.

Starting in Oracle 11gR2, cardinality feedback can help to calculate a better cardinality based on previous execution. If a first execution is estimated as a bad execution (ie. estimated cardinality is significantly different from the real cardinality), oracle stores the real cardinality and uses it for a future execution. This feature can be very helpful for the CBO in the case of repeated statement and cardinalities which don’t evolve too much. (See this link for more information: https://blogs.oracle.com/optimizer/entry/cardinality_feedback)

So, you can choose to gather statistics or to leave cardinality feedback do the job.

The next example show how cardinality feedback can have a bad impact on fixed objects cardinality estimation.

As these internal structures are used by many views and in internal processes, a misestimation can directly have an impact on the oracle kernel performance.

That’s why it’s recommended to gather these stats every time there’s a significant change on the instance configuration (memory configuration for example), new apps deployment etc. And, if you can, it’s recommended to gather these stats during a representative workload.