Tuesday, May 17, 2016

Oracle Fixed Table Practice

Fixed objects x$ tables and their indexes are memory structures storing run-time information. They are open to outside as normal tables because every thing is Relation (Table) in relational database, principle founded by Edgar F. Codd ("A relational model of data for large shared data banks"). Similar uniform philosophy in UNIX is every thing is file (notably proc filesystem procfs).

This Blog will try to do expose meta data of x$ tables since they are not handled in a uniform way as normal tables. We also take GV$SQL_OPTIMIZER_ENV and its underlined X$KQLFSQCE as example in the queries because this x$ table is dynamic, big, and longer duration of its stats gathering.


1. List all fixed objects 


select * from x$kqfta fxt, sys.fixed_obj$ fobj where fobj.obj# = fxt.kqftaobj and fxt.kqftanam in ('X$KQLFSQCE');


2. List indexes of fixed objects 

 
select index_number, column_name from gv$indexed_fixed_column where table_name in ('X$KQLFSQCE');
 1 KQLFSQCE_HASH
 2 KQLFSQCE_SQLID
We can try to verify them by looking the xplan of following queries: 

select * from gv$sql_optimizer_env where hash_value = :hash_value;
 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KQLFSQCE (ind:1)

select * from gv$sql_optimizer_env where sql_id = :sql_id;
 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KQLFSQCE (ind:2)

select * from gv$sql_optimizer_env where id = :id;   (KQLFSQCE_PNUM)
 FIXED TABLE FULL TABLE (FIXED) SYS.X$KQLFSQCE


3. List histogram of fixed objects 


select column_name, histogram from dba_tab_col_statistics 
 where table_name in ('X$KQLFSQCE') and histogram not in ('NONE');
  INST_ID        FREQUENCY
  KQLFSQCE_FLAGS FREQUENCY
 
select distinct table_name, column_name from dba_tab_histograms where table_name in ('X$KQLFSQCE');
 INST_ID
 KQLFSQCE_FLAGS
 
select dbms_stats.report_gather_fixed_obj_stats(detail_level=>'ALL', format=>'TEXT') from dual;
 INST_ID
 KQLFSQCE_HADD
 KQLFSQCE_SQLID
 KQLFSQCE_FLAGS
 KQLFSQCE_PNUM
The above 3 outputs have differences, and we can try to verify them by looking the xplan of following queries: 
                                                                                                        
select * from gv$sql_optimizer_env where inst_id = 1;
  FIXED TABLE FULL TABLE (FIXED) SYS.X$KQLFSQCE QBlock Name: SEL$5C160134
    Cost: 1  Bytes: 32,349,932  Cardinality: 376,162

select * from gv$sql_optimizer_env where inst_id = 9;
  FIXED TABLE FULL TABLE (FIXED) SYS.X$KQLFSQCE QBlock Name: SEL$5C160134
    Cost: 1  Bytes: 86  Cardinality: 1

select * from gv$sql_optimizer_env where id = 1;
  FIXED TABLE FULL TABLE (FIXED) SYS.X$KQLFSQCE QBlock Name: SEL$5C160134
    Cost: 1  Bytes: 68,800  Cardinality: 800

select * from gv$sql_optimizer_env where id = 9;
  FIXED TABLE FULL TABLE (FIXED) SYS.X$KQLFSQCE QBlock Name: SEL$5C160134
    Cost: 1  Bytes: 68,800  Cardinality: 800
We can see that two queries on column ID (KQLFSQCE_PNUM) give the same xplan estimation, probably does not have histogram. It is not clear why dbms_stats.report_gather_fixed_obj_stats lists more columns.


4. List stats gathering performance of fixed tables 

                                                                                                  
select v.last_analyzed, table_name, num_rows, sample_size, avg_row_len 
      ,(v.last_analyzed - lag(v.last_analyzed) over (order by v.last_analyzed))*86400 seconds 
      ,(num_rows * avg_row_len) bytes 
      ,(sample_size/nullif(num_rows, 0)) sample_ratio 
      ,v.* 
  from dba_tab_statistics v 
 where object_type in ('FIXED TABLE') 
   and v.last_analyzed between timestamp'2016-05-17 09:00:00' and timestamp'2016-05-17 10:00:00' 
order by seconds desc nulls last;
As a result of above query, we can find the most time consuming of x$ tables in:
  dbms_stats.gather_fixed_objects_stats;
and hence exclude them in next stats gather by calling
  dbms_stats.lock_table_stats


5. Trace x$ table access


On Solaris, run:
     exec dbms_stats.gather_fixed_objects_stats;
and simultaneously watch kqlfsqce call by dtrace script (8694 is PID of above SQL Session): 

sudo dtrace -n \
'BEGIN {printf("dtrace started: %Y\n", walltimestamp);}
pid8694:a.out:kqlfsqce: { @[execname, ustack()] = count();} 
END { trunc(@, 10);}
END { printf("dtrace ended: %Y\n", walltimestamp);}'
and stop the tracing once X$KQLFSQCE is gathered, the output looks as: 

dtrace: description 'BEGIN ' matched 111 probes
CPU     ID                    FUNCTION:NAME
 17      1                           :BEGIN dtrace started: 2016 May 17 09:13:01
^C
 21      2                             :END
 21      2                             :END dtrace ended: 2016 May 17 09:42:42

  oracle
              oracle`kqlfsqce+0x2c
              oracle`qerfxFetch+0x125f
              oracle`rwsfcd+0x6f
              oracle`qeruaFetch+0x173
              oracle`rwsfcd+0x6f
              oracle`qerltFetch+0x4c8
              oracle`insdlexe+0x202
              oracle`insExecStmtExecIniEngine+0x6b
              oracle`insexe+0x6eb
              oracle`opiexe+0x179f
              oracle`opipls+0x62a
              oracle`opiodr+0x433
              oracle`rpidrus+0xde
              oracle`skgmstack+0x85
              oracle`rpidru+0x88
              oracle`rpiswu2+0x2fd
              oracle`rpidrv+0x589
              oracle`psddr0+0x138
              oracle`psdnal+0x2cd
              oracle`pevm_EXIM+0xf0
          3290001
Crosschecking with output of above sql query, the row for X$KQLFSQCE shows:
  sample_size = 3281070
  seconds = 1736
Posted by at