Tuning query on the fixed table

Oracle performance monitoring is very oftenly followed by querying on the fixed table(x$ table). Query on the fixed table means a direct access on the SGA memory area, which means the promised efficiency.

But this does not mean that you don’t need to care about the performance. Reading memory is fast, but hundreds of megabytes of memory makes the situation far worse than you imagine. Just for this reason, many fixed tables have indexes.

UKJA@ukja1021> desc v$indexed_fixed_column
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      TABLE_NAME                               VARCHAR2(30)
    2      INDEX_NUMBER                             NUMBER
    3      COLUMN_NAME                              VARCHAR2(30)
    4      COLUMN_POSITION                          NUMBER

For instance, X$KGLOB table which represents the list of LCO(Library Cache Object) has following two indexes.

UKJA@ukja1021> exec print_table('select * from v$indexed_fixed_column -
> 		  where table_name = ''X$KGLOB''');
TABLE_NAME                    : X$KGLOB
INDEX_NUMBER                  : 1
COLUMN_NAME                   : KGLNAHSH
COLUMN_POSITION               : 0
-----------------
TABLE_NAME                    : X$KGLOB
INDEX_NUMBER                  : 2
COLUMN_NAME                   : KGLOBT03
COLUMN_POSITION               : 0
-----------------

We have indexes on SQL Hash Value(KGLNAHSH) and SQL ID(KGLOBT03) columns. This means that you should always try to query X$KGLOB table through these two columns.

Here I make a simple test case to demonstrate the usefulness of the indexes on the fixed table.

1. Execute a simple SQL statement and extract SQL ID.

UKJA@ukja1021> select * from t1;

no rows selected

Elapsed: 00:00:00.00
UKJA@ukja1021> col sql_id new_value sql_id
UKJA@ukja1021> select regexp_replace(plan_table_output,
  2  				     'SQL_ID[[:blank:]]+([[:alnum:]]+),.*', '\1') as sql_id
  3  from table(dbms_xplan.display_cursor)
  4  where plan_table_output like 'SQL_ID%';

SQL_ID
--------------------------------------------------------------------------------
27uhu2q2xuu7r

2. Execute two SQL statements which query X$KGLOB table through KGNAOBJ(without index) and KGLOBT03(with index) column respectively.

UKJA@ukja1021> select count(*) from sys.xm$kglob
  2  where kglnaobj = 'select * from t1';

  COUNT(*)
----------
         2

Elapsed: 00:00:00.06
UKJA@ukja1021> 
UKJA@ukja1021> select count(*) from sys.xm$kglob
  2  where kglobt03 = '&sql_id';
old   2: where kglobt03 = '&sql_id'
new   2: where kglobt03 = '27uhu2q2xuu7r'

  COUNT(*)
----------
         2

Elapsed: 00:00:00.01

3. TKPROF result on both of the SQL statements.

select count(*) from sys.xm$kglob
where kglnaobj = 'select * from t1'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.06       0.05          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.06       0.05          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=59402 us)
      2   FIXED TABLE FULL X$KGLOB (cr=0 pr=0 pw=0 time=43810 us)

select count(*) from sys.xm$kglob
where kglobt03 = '27uhu2q2xuu7r'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=167 us)
      2   FIXED TABLE FIXED INDEX X$KGLOB (ind:2) (cr=0 pr=0 pw=0 time=120 us)

Interpretation:

• Logical reads is 0 on both cases.Fixed table is read by direct access on the memory not by scanning database blocks. For this reason logical reads has no meaning here.
• Execution plan cleary shows that the first one follows full scan(FIXED TABLE FULL) and the second one follows index scan(FIXED TABLE FIXED INDEX (ind:2)).
• This makes the difference of 0.05s and 0s of the elapsed time.

Keep in mind the fact that the traditional way of comparing the logical reads is useless in this test case.

4. My personal monitoring script shows following additional info.

@mon_on userenv('sid')

select count(*) from sys.xm$kglob
where kglnaobj = 'select * from t1';

@mon_off

select count(*) from sys.xm$kglob
where kglobt03 = '&sql_id';

@mon_off2
@mon_show2
...
02. time model

STAT_NAME                                   VALUE1         VALUE2           DIFF
----------------------------------- -------------- -------------- --------------
DB time                                    166,458        115,196        -51,262
sql execute elapsed time                   164,356        113,308        -51,048
DB CPU                                     134,972        103,749        -31,223
...

03. latch

LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS
------------------------------ ---------- ---------- ---------- ----------
library cache                       -4186          0          0          0
row cache objects                     -45          0          0          0
enqueues                              -19          0          0          0
enqueue hash chains                   -18          0          0          0
...

The biggest difference lies on the library cache latchacquisition count. Full scan on X$KGLOB needs far more library cache latch acquisition than index scan. More scans on the memory means more CPU assumption, more latch acquisition and potential concurrency problem.

Carelessly written monitoring scripts are likely to make unwanted side effects especially without the knowledge on these facts. Even many commerical monitoring tools are making these kinds of mistakes.

Same principles can be applied to the dictionary views. Following post is reporting an interesting investigation on the performance of querying dictionary views.

• Data Dictionary Performance2