A Study on DBMS_STATS.GATHER_FIXED_OBJECT_STATS

The other day my boss sent me an email telling me that we must have fixed object stats (GATHER_FIXED_OBJECT_STATS) in the database. He also included a link to this article explaining why we have to have fixed object stats. I have never run fixed object stats and I thought I had missed something extreme. At first I was embarrassed I had missed it, but realizing that not everyone knows everything I quickly got over that feeling. I figure here is yet one more thing I can learn so I did some testing.

Part of the reason I did some testing was to see if I had missed something terrible and I wanted to know if my databases had been suffering in performance based on my own negligence. Below is my research – as Tom Kyte says on his Ask Tom web site and in his books, “put up or shut up” – so let’s get to it.  The article mentioned above also refers this post on the Ask Tom web site.

So … what are fixed object stats. Fixed object stats are running statistics on the lowest level of oracle objects there are. They are commonly referred to as the “X$ tables”. The statistics on the X$ tables can be affected by changing init.ora parameters and the few article I read (and oracle documentation) basically explained it is a good idea to gather fixed object stats after a change to the init.ora parameters.

What Do Fixed Stats Do To Execution Plans

With no fixed object stats this is what I see.

SQL> select * from gv$process;

Execution Plan
----------------------------------------------------------
Plan hash value: 3699664968
----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     5 |  1685 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$KSUPR |     5 |  1685 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

Next, I gather stats on the fixed objects and check the execution plan again.

 SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> select * from gv$process;

Execution Plan
----------------------------------------------------------
Plan hash value: 3699664968
----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |    75 |  8175 |     0   (0)| 00:00:01 |
|   1 |  FIXED TABLE FULL| X$KSUPR |    75 |  8175 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

Hmmm. From the looks of things there could be some drastically different query plans for the V$ performance views based on running fixed object stats. However, so far the data is inconclusive so we have to keep digging.

Since I have read the stats will be different based on changing init.ora parameters (see links from above) that is where I go next. Let’s change the processes parameter and see what happens.

SQL> alter system set processes=1000 scope=spfile;
SQL> startup force
SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> select * from gv$process;
SQL> set autotrace traceonly explain
SQL> select * from gv$process;

Execution Plan
----------------------------------------------------------
Plan hash value: 3699664968
----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |   500 | 47500 |     0   (0)| 00:00:01 |
|   1 |  FIXED TABLE FULL| X$KSUPR |   500 | 47500 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

Ok, now rows has jumped to 500 and if there were more than just the X$KSUPR table involved the plan could have changed dramatically. Next what I want to look at is the difference in statistics between having no fixed object stats and when the stats are present. I ran the following query two times: 1) With no fixed object stats; 2) With fixed object stats with processes=1000.

select ft.name, ts.rowcnt
from   v$fixed_table ft, tab_stats$ ts
where  ft.object_id = ts.obj#
order by ft.name;

What I saw after running the query was there were several X$ tables with differences enough that I though it could change query plans. The tables that had different enough values are listed here.

NO STATS   WITH STATS
NAME              ROWCNT       ROWCNT
------------  ----------   ----------
X$KCBBF              750         5000
X$KDNSSF             170         1105
X$KEWSSESV         10710        69615
X$KNSTACR            170         1105
X$KNSTASL            170         1105
X$KNSTCAP            170         1105
X$KNSTMVR            170         1105
X$KNSTRPP            170         1105
X$KQRFP             1626         1808
X$KQRFS              202          236
X$KSLCS             2040        13260
X$KSLES            48960       318240
X$KSLLT            18725        20835
X$KSMDD              208          248
X$KSMPGST            900         6000
X$KSMSPR              45           57
X$KSMSP_NWEX          15           19
X$KSQEQ             2496        13920
X$KSQRS              976         5088
X$KSULOP               2            3
X$KSUPR              150         1000
X$KSURU             1700        11050
X$KSUSE              170         1105
X$KSUSECON           170         1105
X$KSUSECST           170         1105
X$KSUSESTA         65450       425425
X$KSUSEX             170         1105
X$KSUSIO             170         1105
X$KSUSM              170         1105
X$KTADM              752         4864
X$KTCXB              184         1196
X$KTFBFE               6            7
X$KTIFP               18          121
X$KXFPDP             135          320

The next thing I wanted to do was to find out what V$ performance views used the list of X$ tables listed above. Then I could do some testing to see if any of the V$ views would perform differently based on having stats or not having stats.

I ran the query below to find a list of V$ views which use the X$ tables from above.

select distinct view_name
from   v$fixed_view_definition
where  upper( view_definition ) like '%X$KCBBF%'
or     upper( view_definition ) like '%X$KCBBF%'
or     upper( view_definition ) like '%X$KDNSSF%'
or     upper( view_definition ) like '%X$KEWSSESV%'
or     upper( view_definition ) like '%X$KNSTACR%'
or     upper( view_definition ) like '%X$KNSTASL%'
or     upper( view_definition ) like '%X$KNSTCAP%'
or     upper( view_definition ) like '%X$KNSTMVR%'
or     upper( view_definition ) like '%X$KNSTRPP%'
or     upper( view_definition ) like '%X$KQRFP%'
or     upper( view_definition ) like '%X$KQRFS%'
or     upper( view_definition ) like '%X$KSLCS%'
or     upper( view_definition ) like '%X$KSLES%'
or     upper( view_definition ) like '%X$KSLLT%'
or     upper( view_definition ) like '%X$KSMDD%'
or     upper( view_definition ) like '%X$KSMPGST%'
or     upper( view_definition ) like '%X$KSMSPR%'
or     upper( view_definition ) like '%X$KSMSP_NWEX%'
or     upper( view_definition ) like '%X$KSQEQ%'
or     upper( view_definition ) like '%X$KSQRS%'
or     upper( view_definition ) like '%X$KSULOP%'
or     upper( view_definition ) like '%X$KSUPR%'
or     upper( view_definition ) like '%X$KSURU%'
or     upper( view_definition ) like '%X$KSUSE%'
or     upper( view_definition ) like '%X$KSUSECON%'
or     upper( view_definition ) like '%X$KSUSECST%'
or     upper( view_definition ) like '%X$KSUSESTA%'
or     upper( view_definition ) like '%X$KSUSEX%'
or     upper( view_definition ) like '%X$KSUSIO%'
or     upper( view_definition ) like '%X$KSUSM%'
or     upper( view_definition ) like '%X$KTADM%'
or     upper( view_definition ) like '%X$KTCXB%'
or     upper( view_definition ) like '%X$KTFBFE%'
or     upper( view_definition ) like '%X$KTIFP%'
or     upper( view_definition ) like '%X$KXFPDP%';

I won’t go into all of the differences I noticed. I’m just going to concentrate on where I saw the biggest differences only.

What I did next was to use autotrace to show the explain plan of all the V$ views and spool them to files. I had output similar to the execution plans from above for several V$ views and then I compared the differences. Finally, I took teh V$ views with the biggest differences and ran them again, but this time I included statistics in the output. Here is what I saw.

With No Fixed Object Stats

SQL> set autotrace traceonly explain statistics
SQL> select * from gv$lock;
14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2514336078

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |    10 |  1560 |     1 (100)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |    10 |  1560 |     1 (100)| 00:00:01 |
|*  2 |   HASH JOIN              |                 |    10 |  1210 |     1 (100)| 00:00:01 |
|*  3 |    VIEW                  | GV$_LOCK        |    10 |   890 |     0   (0)| 00:00:01 |
|   4 |     UNION-ALL            |                 |       |       |            |          |
|*  5 |      VIEW                | GV$_LOCK1       |     2 |   178 |     0   (0)| 00:00:01 |
|   6 |       UNION-ALL          |                 |       |       |            |          |
|*  7 |        FIXED TABLE FULL  | X$KDNSSF        |     1 |   102 |     0   (0)| 00:00:01 |
|*  8 |        FIXED TABLE FULL  | X$KSQEQ         |     1 |   102 |     0   (0)| 00:00:01 |
|*  9 |      FIXED TABLE FULL    | X$KTADM         |     1 |   102 |     0   (0)| 00:00:01 |
|* 10 |      FIXED TABLE FULL    | X$KTATRFIL      |     1 |   102 |     0   (0)| 00:00:01 |
|* 11 |      FIXED TABLE FULL    | X$KTATRFSL      |     1 |   102 |     0   (0)| 00:00:01 |
|* 12 |      FIXED TABLE FULL    | X$KTATL         |     1 |   102 |     0   (0)| 00:00:01 |
|* 13 |      FIXED TABLE FULL    | X$KTSTUSC       |     1 |   102 |     0   (0)| 00:00:01 |
|* 14 |      FIXED TABLE FULL    | X$KTSTUSS       |     1 |   102 |     0   (0)| 00:00:01 |
|* 15 |      FIXED TABLE FULL    | X$KTSTUSG       |     1 |   102 |     0   (0)| 00:00:01 |
|* 16 |      FIXED TABLE FULL    | X$KTCXB         |     1 |   102 |     0   (0)| 00:00:01 |
|  17 |    FIXED TABLE FULL      | X$KSUSE         |   100 |  3200 |     0   (0)| 00:00:01 |
|* 18 |   FIXED TABLE FIXED INDEX| X$KSQRS (ind:1) |     1 |    35 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
     1877  recursive calls
        1  db block gets
      229  consistent gets
        3  physical reads
        0  redo size
     1330  bytes sent via SQL*Net to client
      338  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       14  rows processed

With Fixed Object Stats

SQL> select * from gv$lock;
14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 506911936
-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            | 11609 |  1394K|    13 (100)| 00:00:01 |
|*  1 |  HASH JOIN             |            | 11609 |  1394K|    13 (100)| 00:00:01 |
|   2 |   FIXED TABLE FULL     | X$KSQRS    |  5088 | 91584 |     2 (100)| 00:00:01 |
|*  3 |   HASH JOIN            |            | 11609 |  1190K|    10 (100)| 00:00:01 |
|   4 |    FIXED TABLE FULL    | X$KSUSE    |  1105 | 17680 |     0   (0)| 00:00:01 |
|*  5 |    VIEW                | GV$_LOCK   | 11609 |  1008K|     9 (100)| 00:00:01 |
|   6 |     UNION-ALL          |            |       |       |            |          |
|*  7 |      VIEW              | GV$_LOCK1  | 11601 |  1008K|     7 (100)| 00:00:01 |
|   8 |       UNION-ALL        |            |       |       |            |          |
|*  9 |        FIXED TABLE FULL| X$KDNSSF   |     1 |    40 |     0   (0)| 00:00:01 |
|* 10 |        FIXED TABLE FULL| X$KSQEQ    | 11600 |   453K|     6 (100)| 00:00:01 |
|* 11 |      FIXED TABLE FULL  | X$KTADM    |     1 |    40 |     2 (100)| 00:00:01 |
|* 12 |      FIXED TABLE FULL  | X$KTATRFIL |     1 |    36 |     0   (0)| 00:00:01 |
|* 13 |      FIXED TABLE FULL  | X$KTATRFSL |     1 |    36 |     0   (0)| 00:00:01 |
|* 14 |      FIXED TABLE FULL  | X$KTATL    |     1 |    36 |     0   (0)| 00:00:01 |
|* 15 |      FIXED TABLE FULL  | X$KTSTUSC  |     1 |    36 |     0   (0)| 00:00:01 |
|* 16 |      FIXED TABLE FULL  | X$KTSTUSS  |     1 |    40 |     0   (0)| 00:00:01 |
|* 17 |      FIXED TABLE FULL  | X$KTSTUSG  |     1 |    36 |     0   (0)| 00:00:01 |
|* 18 |      FIXED TABLE FULL  | X$KTCXB    |     1 |    38 |     1 (100)| 00:00:01 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
     1895  recursive calls
        1  db block gets
      337  consistent gets
        7  physical reads
        0  redo size
     1347  bytes sent via SQL*Net to client
      338  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       14  rows processed

What I noticed first of all was the difference in the number of rows and bytes the optimizer had to work with.  In the statistics there were more consistent gets when fixed object stats were present. There were also more recursive calls, but all of these tests were done after restarting the database.  After the query is run once and the data is all in cache you will see the recursive calls decrease to zero for each subsequent execution – so do the consistent gets and physical reads. In subsequent execution all of the statistics numbers were the same with or without stats. Interesting…

I guess the moral of this story is I shouldn’t be worried at all that I wasn’t gathering fixed object stats because the performance was not different enough that I needed to be overly concerned about it. I should also note that gathering stats didn’t help or hurt in this situation. However, there may be init.ora parameters that cause a bigger change to data in the X$ tables – such as parameter dealing with oracle streams, but I’m not sure.

I suppose in the end I can just be thankful that I learned something this week that I didn’t know last week and I’m always happy with that. I’ll also make note to run DBMS_STATS.GATHER_FIXED_OBJECT_STATS whenever I make changes to the init.ora parameters.

If you have any more information to share on this topic please contribute so we can all benefit.

Post a Comment