Skip to Main Content
  • Questions
  • When no data definition change, is SYS/SYSTEM stats recollection needed?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, jc.

Asked: March 25, 2006 - 12:38 pm UTC

Last updated: November 21, 2011 - 2:08 pm UTC

Version: 10.1.0.4

Viewed 1000+ times

You Asked

Tom,

After all schema contents (table, index, proc, triggers..) are created in our 10.1.0.4 database, we collect statistics for SYS/SYSTEM and all other users schema. Then the database is rolled over to production.

In production, there is no more schema definition change; ONLY DATA volume growth. This is an OLTP environment; only a few tables may grow data to multi-million records.

We collect statistics on users schema daily, except SYS/SYSTEM, which I think no need to update stats since there's no more data definition change.

(I understand that a few SYS tables that maintain the block/bytes count of table/index segments will change in time, and most probably other things are happening in SYS/SYSTEM that I do not know.)

In this case,

1) Is it safe that we don't collect SYS/SYSTEM statistics any more, except one time at the begining?

2) Can you explain a little about why it is not safe?

Thanks!!
jc

(P.S. Thanks again to you and Johnathan L. for helping on my question regarding to the safety of using ANALYZE INDEX, instead of dbms_stats.gather_index which always uses 919 blocks sample size, at Hotsos 2006 Symposium.)



and Tom said...

1) likely yes. Your applications in general would not be querying the dictionary using anything other than recursive SQL and that is all "transactional" (designed to be keyed read).

It would be if your monitoring tools started observing excessive response times from their favorite dictionary queries - whatever they may be.

2) it likely would be "safe", it would affect predominantly queries against the dictionary - and likely not the recursive sql we ourselves execute against the dictionary since that is all transactional by nature. It would most likely affect 3rd party tools IF anything at all (you would need to add lots of new data to affect the dictionary to an extent that would raise the need for new plans)

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks!!

jc, March 26, 2006 - 3:15 pm UTC


difference between SYS/SYSTEM stats and FIXED_OBJECTS

Tapas Mishra, April 20, 2006 - 5:34 pm UTC

Tom,

While the above makes sense, how about the GATHER_FIXED_OBJECTS_STATS Procedure? How is it differnet than the GATHER_DICTIONARY_STATS?

I understand that using the GATHER_DICTIONARY_STATS we can gather SYS/SYSTEM stats ONCE for our apps that do not use SYS/SYSTEM except those recursive calls. But since the x$ tables are in SYS, how the GATHER_FIXED_OBJECTS_STATS is different and how often it should be carried out?

Thanks.
-tapas-

Tom Kyte
April 20, 2006 - 5:50 pm UTC

fixed objects are the magic tables that are not tables - they are not "dictionary" tables.

the x$ tables would change size in response to init.ora setting changes generally. Setting processes higher will add rows to various x$ views for example.

so, they could be considered a one time thing unless you make a big change to your parameter settings.

SYS/SYSTEM vs. FIXED_OBJECTS

Tapas Mishra, April 21, 2006 - 1:19 pm UTC

Thanks Tom, for the explanation. Your example of "processes" is good to know, that affects the x$ tables and subsequently triggers the needs to re-collect stats for the FIXED_OBJECTS. Can you please point me some directions/links where I will get some kind of a list of such init.ora parameters like processes?
Thanks again.
-tapas-

Tom Kyte
April 21, 2006 - 3:31 pm UTC

'most of them'

a parameter change in production is pretty "big", something to be tested first. That would help identify the need to redo them.

SYS/SYSTEM vs FIXED_OBJECTS

Tapas Mishra, April 24, 2006 - 2:10 pm UTC

Thanks Tom, for explaining this FIXED_OBJECTt hign and how it is dependent on the INIT.ORA parameters. I have not read any articles/documents on this so far that mentions this that FIXED_OBJECTS stats are dependent on the INIT.ORa parameters. Does Oracle says this in any documents? I'm glad that I asked this to you.

Here is a suummary of what I'm going to do:
1. Use GATHER_SYSTEM_STATS under known hardware like I/O, CPU speed etc, and just gather it just ONCE. If the harware like I/O, CPU change, that means that it's time to recollect stats one more time using GATHER_SYSTEM_STATS.

Only thing that confuses me is "no workload stats" and "workload stats". I can not set the workload stats untill all users are on the system, so this has to be on the 1st day of production, right? This is something I can not simulate on my TEST environment. Please advise what you think.

2. Gather the FIXED_OBJECTS stats - and only ONE - when I know that the init.ora parameters are settled. If they change whil on production, then recollect this stats once more.

Is my idea correct? And can you advise little more on the point 1 of "Workload and noworkload" system stats please?
Thanks again.
-tapas-

Tom Kyte
April 24, 2006 - 2:16 pm UTC

the fixed objects are views into the things that are set by the parameters. Not sure it is a) documented, b) needs to be specifically.

1) well, not necessarily - if your workload changes, it would affect this as well.

2) yes.



SYS/SYSTEM Stat Vs. FIXED_OBJECTS

Tapas Mishra, April 24, 2006 - 4:20 pm UTC

Thanks Tom, I agree normally the workload change might trigger the harware change but yes, I will keep this in mind while writing my docuementations that if more users added due to new applications launched against the database or business requirements then, there will be a need for the SYSTEM STATS collection process.

You did not mention about your thoughts on the "Workload and no workload stats". Workload stats - is it normally can be taken on the first day of production if one donot know to simulate it and set it in the TEST system?
Thanks for all your time.
-tapas-

Tom Kyte
April 25, 2006 - 12:26 am UTC

you can let dbms_stats generate a workload for you if you have none, yes, it would be better than "nothing"

Work load SYSTEM stats

Tapas Mishra, April 25, 2006 - 11:34 am UTC

Thanks Tom, on your comment "you can let dbms_stats generate a workload for you if you have none", are you saying the START and STOP option of the DBMS_STATS.GATHER_SYSTEM_STATS or you are pointing to something else? Could you please clarify this? If I understand correct, I can do the START and STOP thing on a normal workload after gone live on production. Correct?Thanks.
-tapas-


Tom Kyte
April 25, 2006 - 2:23 pm UTC

I'm saying "if you have no representative workload, letting dbms_stats generate one for you is better than nothing"

Workload statistics

Tapas Mishra, April 25, 2006 - 5:45 pm UTC

I understand Tom. However, my question was, how to do that using DBMS_STATS? Coorect me if I'm wrong, I'm going to use the following approach:
1. Collect the NOWORKLOAD stats first after the database is created and all tablespaces are created etc, to have the base ready for I/O and CPU information on that hardware where the system will go live.
2. Then, once the system is live in production and all the users are on it, determine a good time that represents a good workload , say 10:00AM to 1:00PM, and then do a START at 10:00AM and STOP at 1:00PM approach of gathering the WORKLOAD STATISTICS, using GATHER_SCHEMA_STATS. Meaning,
at 10:00AM EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS (‘start’);
and at 1:00PM
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS (‘stop’);
3. My understanding is, once the proper workload stats are loaded, then NOWORKLOAD stats will be "ignored" by optimizer.
Just trying to reverify that the above approach is correct or not? Also, could you let me know where these are stored, like the sreadtim, mreadtim, mbrc etc...?? Is is some OS_STATS view?
Thanks.
-tapas-

Tom Kyte
April 25, 2006 - 11:59 pm UTC

yes, if you gather system statistics after having gathered them, the "last gather" is what will be used - just like gathering stats on a table.

Question on Gather_Fixed_Objects_Stats

Brian Burton, December 20, 2007 - 10:00 am UTC

Tom

I'm trying to get a handle on whether/how often to gather fixed_object stats.

Metalink Note 457926.1 dated 10-Sept-2007 indicates they are flushed when database recycles and would have to be regathered after each database restart. Comments earlier in this thread (and some other places) indicate otherwise, that you just gather these fixed_object stats once under load, or when load changes.

Can you clarify the best practice?
Tom Kyte
December 20, 2007 - 2:36 pm UTC

sys%ORA10GR2> @plan "select count(*) from v$session"

5 rows deleted.

old   1: explain plan for &1
new   1: explain plan for select count(*) from v$session

Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2376410614

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    65 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    65 |            |          |
|   2 |   NESTED LOOPS            |                 |     1 |    65 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FULL       | X$KSUSE         |     1 |    52 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    13 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
              BITAND("S"."KSUSEFLG",1)<>0)
   4 - filter("S"."KSUSEOPC"="E"."INDX")

18 rows selected.


so, right now X$KSUSE is presumed to have a single row flowing out of it....

we gather fixed stats:

sys%ORA10GR2> exec dbms_stats.gather_fixed_objects_stats

PL/SQL procedure successfully completed.

sys%ORA10GR2> @plan "select count(*) from v$session"

5 rows deleted.

old   1: explain plan for &1
new   1: explain plan for select count(*) from v$session

Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2376410614

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    16 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    16 |            |          |
|   2 |   NESTED LOOPS            |                 |   141 |  2256 |     0   (0)| 00:00:01 |
|   3 |    FIXED TABLE FULL       | X$KSUSE         |   141 |  1692 |     0   (0)| 00:00:01 |
|   4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

11 rows selected.


and we can see the statistics have materially changed - 141 rows.....

We restart:

sys%ORA10GR2> startup force
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1261836 bytes
Variable Size             247467764 bytes
Database Buffers          281018368 bytes
Redo Buffers                7122944 bytes
Database mounted.
Database opened.
sys%ORA10GR2> @plan "select count(*) from v$session"

0 rows deleted.

old   1: explain plan for &1
new   1: explain plan for select count(*) from v$session

Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2376410614

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    16 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    16 |            |          |
|   2 |   NESTED LOOPS            |                 |   141 |  2256 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FULL       | X$KSUSE         |   141 |  1692 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - filter(BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
              "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter("S"."KSUSEOPC"="E"."INDX")

18 rows selected.


it did not "lose them". Just in case that is a weird thing with startup force, we start again:

sys%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[tkyte@dellpe ~]$ bounce

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 20 10:28:06 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

sys%ORA10GR2> ORACLE instance shut down.
sys%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 20 10:28:07 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> idle> 
ORACLE instance started.

Total System Global Area      536,870,912 bytes
Fixed Size                      1,261,836 bytes
Variable Size                 247,467,764 bytes
Database Buffers              281,018,368 bytes
Redo Buffers                    7,122,944 bytes
Database mounted.
Database opened.
idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


and upon looking:



[tkyte@dellpe ~]$ sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 20 10:28:18 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

sys%ORA10GR2> @plan "select count(*) from v$session"

0 rows deleted.

old   1: explain plan for &1
new   1: explain plan for select count(*) from v$session

Explained.


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2376410614

-------------------------------------------------------------------------------
--------------

| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CP
U)| Time     |

-------------------------------------------------------------------------------
--------------

|   0 | SELECT STATEMENT          |                 |     1 |    16 |     0   (
0)| 00:00:01 |

|   1 |  SORT AGGREGATE           |                 |     1 |    16 |
  |          |

|   2 |   NESTED LOOPS            |                 |   141 |  2256 |     0   (
0)| 00:00:01 |

|*  3 |    FIXED TABLE FULL       | X$KSUSE         |   141 |  1692 |     0   (
0)| 00:00:01 |

|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (
0)| 00:00:01 |

-------------------------------------------------------------------------------
--------------


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

   3 - filter(BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
              "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter("S"."KSUSEOPC"="E"."INDX")

18 rows selected.

sys%ORA10GR2> set linesize 1000
sys%ORA10GR2> @plan "select count(*) from v$session"

5 rows deleted.

old   1: explain plan for &1
new   1: explain plan for select count(*) from v$session

Explained.


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2376410614

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |    16 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |                 |     1 |    16 |            |          |
|   2 |   NESTED LOOPS            |                 |   141 |  2256 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FULL       | X$KSUSE         |   141 |  1692 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |     4 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - filter(BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
              "S"."INST_ID"=USERENV('INSTANCE'))
   4 - filter("S"."KSUSEOPC"="E"."INDX")

18 rows selected.


still there - they are sticky. In fact:

sys%ORA10GR2> select a.name, b.rowcnt from v$fixed_table a, tab_stats$ b where a.object_id = b.obj# and rownum <= 10;

NAME                               ROWCNT
------------------------------ ----------
X$KQFTA                               600
X$KQFVI                               774
X$KQFVT                               774
X$KQFDT                                16
X$KQFCO                              8612
X$KQFOPT                               18
X$KSLLT                              2568
X$KSLHOT                               10
X$KSLLCLASS                             8
X$KSLECLASS                             4

10 rows selected.



I've emailed the note owner this.

Question on Gather_Fixed_Objects_Stats

Brian Burton, December 20, 2007 - 1:44 pm UTC

Thanks, good info.

So is it considered good practice to routinely gather stats on fixed_objects in 10g (10.2.0.3), or only if seeing performance problems?

Would the database just gather those fixed_object stats on it's own as it needs them (if it needs them)?
Tom Kyte
December 20, 2007 - 2:38 pm UTC

"good practice"

one persons good is another persons evil.

These fixed tables change size when you change a major parameter that affects the size of some data structure - so init.ora changes would be a "trigger" to cause you to think about these statistics.

Question on GATHER_DICTIONARY_STATS

Zahir M, March 31, 2008 - 8:58 pm UTC

Few questions on GATHER_DICTIONARY_STATS. Please clarify

When should we use GATHER_DICTIONARY_STATS (under what circumstances)?
What should be its frequency (is it after any schema changes / data load ) ?
How to identify whether this GATHER_DICTIONARY_STATS used in the database ?

Tom Kyte
April 01, 2008 - 9:17 am UTC

dictionary stats are affected by....

the size of the dictionary.

so, if you did "big things" to the dictionary (loaded a schema - not put data into the schema, but rather did things in the schema that affect the dictionary like creating and dropping objects...) - you would consider gathering statistics.


look at last_analyzed for the sys tables to see when the dictionary was gathered against.

DBMS_STATS.gather_fixed_objects_stats

A reader, April 20, 2009 - 2:53 pm UTC

How to identify whether DBMS_STATS.gather_fixed_objects_stats was ever executed in the database ?


Tom Kyte
April 21, 2009 - 2:45 pm UTC

select v.name, ts.analyzetime from v$fixed_table v, sys.tab_stats$ ts where v.object_id = ts.obj#;

would be a sneaky way.


DBA_TAB_STATS_HISTORY would be another view of interest.

Zahir M, May 04, 2009 - 5:15 pm UTC

In addition to the above sql mentioned by Tom , You can use the following sql


a) to see when gather_fixed_objects_stats was done.

Select * from DBA_OPTSTAT_OPERATIONS
Where operation = 'gather_fixed_objects_stats'

b) to see the tables affected by gather_fixed_objects_stats

Select ats.* from
ALL_TAB_STATS_HISTORY ats , V$FIXED_TABLE ft
where ats.table_name = ft.NAME

A reader, October 21, 2009 - 2:45 pm UTC

( In RAC) If I generate the system stats , does Oracle takes the average or minimum or maximum
of the ( involved) cpu's speed and stores it in ( CPUSPEEDNW / CPUSPEED) ? Which one ?
Tom Kyte
October 23, 2009 - 12:45 pm UTC

it takes it from the system you just ran on.

Fixed objects Stats not existing

Prashant, November 21, 2011 - 6:05 am UTC

Hi Tom

In our environments I don't see fixed object stats being ever collected. These are new environments created for a major implementation.

Is it necessary to collect fixed object stats now that the init.ora parameters are settled? How does it help?

select a.name, b.rowcnt from v$fixed_table a, sys.tab_stats$ b where a.object_id = b.obj#;

--no rows returned


Thanks
Prashant
Tom Kyte
November 21, 2011 - 2:08 pm UTC

it depends, do you perform lots of queries against the fixed tables yourself? do you use a tool that does so?

It would affect query plans against those objects.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library