Oracle … as usual

Oracle by Laurent Leturgez

Statistics on fixed objects

On a previous post, I spoke about statistics and I promised new posts about statistics on fixed objects and system stats and their potential impact.

Fixed objects are internal structures where oracle internal data are stored. This objects are the source of data for dynamic performance views (V$SQL, V$ACCESS, and many V$ views).

Let’s see how statistics can be important on this.

To demonstrate this, I used the V$ACCESS view which have the SQL above for definition:

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.
Notice that, the cardinality estimation have changed before and after statistics gathering. The second one is more precise and closer from the real value. The plan have changed too but without any effect on execution.
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.
SQL> exec dbms_stats.delete_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> select count(*) from v$access;

  COUNT(*)
----------
       315

Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  dw4h66wchcsm7, child number 0
-------------------------------------
select 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)|
-------------------------------------------------------------------------------------

22 rows selected.

Elapsed: 00:00:00.23
 This takes less than a second.
I wait a while and take a coffee. Then, I execute a the same query, cardinality feedback will be used because there’s a significant difference between the first estimation and the real cardinality.
SQL> select count(*) from v$access;

  COUNT(*)
----------
       429

Elapsed: 00:00:10.44
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID  dw4h66wchcsm7, child number 2
-------------------------------------
select count(*) from v$access

Plan hash value: 1711387798

------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |       |       |     1 (100)|
|   1 |  SORT AGGREGATE             |                 |     1 |       |            |
|   2 |   VIEW                      | GV$ACCESS       |   315 |       |     0   (0)|
|   3 |    HASH UNIQUE              |                 |   315 |   214K|     0   (0)|
|   4 |     NESTED LOOPS SEMI       |                 |   470 |   319K|     0   (0)|
|   5 |      HASH JOIN              |                 |   100 | 65900 |     0   (0)|
|   6 |       FIXED TABLE FULL      | X$KGLLK         |   100 |  2500 |     0   (0)|
|   7 |       MERGE JOIN CARTESIAN  |                 |   100 | 63400 |     0   (0)|
|   8 |        FIXED TABLE FULL     | X$KSUSE         |     1 |    45 |     0   (0)|
|   9 |        BUFFER SORT          |                 |   100 | 58900 |     0   (0)|
|  10 |         FIXED TABLE FULL    | X$KGLOB         |   100 | 58900 |     0   (0)|
|  11 |      FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) |   470M|    16G|     0   (0)|
------------------------------------------------------------------------------------

Note
-----
   - statistics feedback used for this statement

27 rows selected.

Elapsed: 00:00:00.04
The query is executed in 10 seconds with a changed plan which is now using a MERGE JOIN CARTESIAN operation.
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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: