Fixed objects are in-memory structures and variables inside the Oracle kernel. Fixed table names start with X$ and represent data via performance views like v$session. Performance views are used in applications (fixed tables should not be queried directly).
Why to gather fixed object statistics
Missing or wrong fixed object statistics can lead to performance issues (often seen on queries on performance views e.g. dba_extents, v$access). Many tools (RMAN, Data Guard, Streams, Grid/Database Control, etc.) query performance views and may suffer from bad performance if fixed object statistics are missing.
Blog Update for Oracle 12c (26-NOV-2016): CDB and PDBs require to have fixed object statistics.
Blog Update for Oracle 12c (26-NOV-2016): CDB and PDBs require to have fixed object statistics.
When to gather fixed object statistics
Fixed objects expose current database activity. Therefore, statistics should be gathered during a “representative workload” (and not during a time when the database is idle or has an unusual load).
While fixed tables loose their content when the database is shut down, the statistics survive and remain valid after reboot. If the workload changes or for some oracle server parameters changes, fixed object statistics have to be gathered again.
Blog Update for Oracle 12c (26-NOV-2016): Auto task “auto optimizer stats collection” in 12c gathers fixed object statistics automatically. But it is still recommended to gather fixed object stats manually during a time with “representative workload”.
Blog Update for Oracle 12c (26-NOV-2016): Auto task “auto optimizer stats collection” in 12c gathers fixed object statistics automatically. But it is still recommended to gather fixed object stats manually during a time with “representative workload”.
How to manage fixed object statistics
The following procedure can be run to gather fixed object statistics
- dbms_stats.gather_fixed_objects_stats
Fixed object statistics can be restored from an earlier run:
- dbms_stats.restore_fixed_objects_stats
Additionally, it is possible to use the procedures for single table statistics:
- dbms_stats.gather_table_stats
- dbms_stats.set_table_stats
Fixed objects statistics can be deleted by running:
- dbms_stats.delete_fixed_objects_stats
How to check for missing fixed object statistics
The following statements can be used to check if fixed table statistics have been gathered (last_analyzed must not be NULL).
SELECT owner
, table_name
, last_analyzed
, last_analyzed
FROM dba_tab_statistics
WHERE table_name=’X$KGLDP’;
Or list last_analyzed date for all fixed tables. V$FIXED_TABLE contains all dynamic performance tables, views, and derived tables in the database. If type = TABLE, X$ tables are listed.
SELECT owner
, table_name
, last_analyzed
FROM dba_tab_statistics
WHERE table_name IN
(SELECT name
FROM v$fixed_table
WHERE type = ‘TABLE’
)
ORDER BY last_analyzed NULLS LAST;
Table DBA_OPTSTAT_OPERATIONS contains executions from dbms_stats package performed at the schema and database level. The history may have been purged in the meantime though (retention time can be retrieved by DBMS_STATS.GET_STATS_HISTORY_RETENTION).
SELECT operation
, target
, start_time
FROM dba_optstat_operations
WHERE operation = ‘gather_fixed_objects_stats’
ORDER BY start_time;
Sample session
The following session shows different execution plans if
- fixed object statistics are missing
- fixed object statistics are set.
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production
SQL> exec dbms_stats.delete_fixed_objects_stats();
PL/SQL procedure successfully completed.
SQL> SELECT owner
2 , table_name
3 , last_analyzed
4 FROM dba_tab_statistics
5 WHERE table_name IN (‘X$KSUSE’, ‘X$KGLDP’, ‘X$KGLLK’, ‘X$KGLOB’);
OWNER TABLE_NAME LAST_ANALYZED
—————————— —————————— ——————-
SYS X$KSUSE
SYS X$KGLOB
SYS X$KGLLK
SYS X$KGLDP
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM v$access;
Explained.
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 3216752464
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 583 | 1 (100)| 00:00:01 |
| 1 | VIEW | GV$ACCESS | 1 | 583 | 1 (100)| 00:00:01 |
| 2 | HASH UNIQUE | | 1 | 684 | 1 (100)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 684 | 0 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 95 | 0 (0)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 100 | 7000 | 0 (0)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 100 | 3800 | 0 (0)| 00:00:01 |
| 8 | FIXED TABLE FULL | X$KGLDP | 100 | 3800 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FIXED INDEX| X$KGLLK (ind:1) | 1 | 25 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) | 1 | 589 | 0 (0)| 00:00:01 |
———————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
6 – filter(“S”.”INST_ID”=USERENV(‘INSTANCE’))
9 – filter(“L”.”KGLLKUSE”=”S”.”ADDR” AND “L”.”KGLLKHDL”=”D”.”KGLHDADR” AND
“L”.”KGLNAHSH”=”D”.”KGLNAHSH”)
10 – filter(“O”.”KGLNAHSH”=”D”.”KGLRFHSH” AND “O”.”KGLHDADR”=”D”.”KGLRFHDL”)
25 rows selected.
SQL> exec dbms_stats.gather_fixed_objects_stats();
PL/SQL procedure successfully completed.
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM v$access;
Explained.
SQL> SELECT *
2 FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 1019812077
———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 733 | 417K| 3 (100)| 00:00:01 |
| 1 | VIEW | GV$ACCESS | 733 | 417K| 3 (100)| 00:00:01 |
| 2 | HASH UNIQUE | | 733 | 123K| 3 (100)| 00:00:01 |
| 3 | NESTED LOOPS | | 733 | 123K| 2 (100)| 00:00:01 |
| 4 | NESTED LOOPS | | 733 | 53509 | 1 (100)| 00:00:01 |
| 5 | HASH JOIN | | 733 | 30053 | 1 (100)| 00:00:01 |
| 6 | FIXED TABLE FULL | X$KSUSE | 248 | 3968 | 0 (0)| 00:00:01 |
| 7 | FIXED TABLE FULL | X$KGLLK | 733 | 18325 | 0 (0)| 00:00:01 |
| 8 | FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) | 1 | 32 | 0 (0)| 00:00:01 |
| 9 | FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) | 1 | 100 | 0 (0)| 00:00:01 |
———————————————————————————————–
16 rows selected.
SQL> SELECT owner
2 , table_name
3 , last_analyzed
4 FROM dba_tab_statistics
5 WHERE table_name IN (‘X$KSUSE’, ‘X$KGLDP’, ‘X$KGLLK’, ‘X$KGLOB’);
OWNER TABLE_NAME LAST_ANALYZED
—————————— —————————— ——————-
SYS X$KSUSE 30.07.2011 15:04:21
SYS X$KGLOB 30.07.2011 15:02:17
SYS X$KGLLK 30.07.2011 15:02:12
SYS X$KGLDP 30.07.2011 15:02:12
See also my blog post on Oracle Dictionary Statistics.
Additional Sources
- Fixed Objects Statistics Considerations [ID 798257.1]
- Gathering Statistics For All fixed Objects In The Data Dictionary. [ID 272479.1]
- How to gather statistics on SYS objects and fixed_objects? [ID 457926.1]
- Mike Dietrich Blog: https://blogs.oracle.com/UPGRADE/entry/gather_fixed_objects_stats_in