Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manoj.

Asked: December 12, 2018 - 4:53 pm UTC

Last updated: December 18, 2018 - 1:59 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi,

We have noticed that for few tables and indexes in our DB stats are locked and its causing some queries to use bad plan. We are trying to unlock them and do gather stats to try and help them run faster.

We can find the tables and indexes for which stats are locked using DBA_TAB_STATISTICS or DBA_IND_STATISTICS. However, we have many "Object Type Tables" in our DB. We couldnt find these tables in DBA_TAB_STATISTICS or in DBA_OBJECT_TABLES.

Where can we find "Object Type Tables" and check their statistics in Oracle DB?

Also, if the stats are locked for a long time for a table or index and it is using a bad plan, would it help in doing an table/index rebuild on them or gathering stats could be enough?

Thanks in Advance,

and Connor said...

Interestingly the definition for xxx_TAB_STATISTICS explicitly contains in the definition:

and bitand(t.property, 1) = 0 /* not a typed table */


so I assume there is a reason for that, but I've asked the PM for that if its just an erroneous omission.

You can still get a fair degree of stats information from the other dictionary views

SQL> create type myobj as object  ( x int, y int );
  2  /

Type created.

SQL> create table t of myobj;

Table created.

SQL>
SQL> insert into t
  2  select myobj(rownum,rownum)
  3  from dual connect by level <= 10;

10 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from   user_object_tables
  3  where  table_name = 'T'
  4  @pr
==============================
TABLE_NAME                    : T
TABLESPACE_NAME               : USERS
CLUSTER_NAME                  :
IOT_NAME                      :
STATUS                        : VALID
PCT_FREE                      : 10
PCT_USED                      :
INI_TRANS                     : 1
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
LOGGING                       : YES
BACKED_UP                     : N
NUM_ROWS                      : 10
BLOCKS                        : 5
EMPTY_BLOCKS                  : 0
AVG_SPACE                     : 0
CHAIN_CNT                     : 0
AVG_ROW_LEN                   : 23
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
DEGREE                        :          1
INSTANCES                     :          1
CACHE                         :     N
TABLE_LOCK                    : ENABLED
SAMPLE_SIZE                   : 10
LAST_ANALYZED                 : 17-DEC-18
PARTITIONED                   : NO
IOT_TYPE                      :
OBJECT_ID_TYPE                : SYSTEM GENERATED
TABLE_TYPE_OWNER              : MCDONAC
TABLE_TYPE                    : MYOBJ
TEMPORARY                     : N
SECONDARY                     : N
NESTED                        : NO
BUFFER_POOL                   : DEFAULT
FLASH_CACHE                   : DEFAULT
CELL_FLASH_CACHE              : DEFAULT
ROW_MOVEMENT                  : DISABLED
GLOBAL_STATS                  : YES
USER_STATS                    : NO
DURATION                      :
SKIP_CORRUPT                  : DISABLED
MONITORING                    : YES
CLUSTER_OWNER                 :
DEPENDENCIES                  : DISABLED
COMPRESSION                   : DISABLED
COMPRESS_FOR                  :
DROPPED                       : NO
SEGMENT_CREATED               : YES
INMEMORY                      : DISABLED
INMEMORY_PRIORITY             :
INMEMORY_DISTRIBUTE           :
INMEMORY_COMPRESSION          :
INMEMORY_DUPLICATE            :
EXTERNAL                      : NO
CELLMEMORY                    :
INMEMORY_SERVICE              :
INMEMORY_SERVICE_NAME         :

PL/SQL procedure successfully completed.


SQL>
SQL> select *
  2  from   user_tab_col_statistics
  3  where  table_name = 'T'
  4  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : SYS_NC_OID$
NUM_DISTINCT                  : 10
LOW_VALUE                     : 2A67D00F4BB6466487D27B03585B8AA8
HIGH_VALUE                    : F299F7493C9249D39E579F62D0CD11CA
DENSITY                       : .1
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 17-DEC-18
SAMPLE_SIZE                   : 10
GLOBAL_STATS                  : YES
USER_STATS                    : NO
NOTES                         :
AVG_COL_LEN                   : 17
HISTOGRAM                     : NONE
SCOPE                         : SHARED
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : X
NUM_DISTINCT                  : 10
LOW_VALUE                     : C102
HIGH_VALUE                    : C10B
DENSITY                       : .1
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 17-DEC-18
SAMPLE_SIZE                   : 10
GLOBAL_STATS                  : YES
USER_STATS                    : NO
NOTES                         :
AVG_COL_LEN                   : 3
HISTOGRAM                     : NONE
SCOPE                         : SHARED
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : Y
NUM_DISTINCT                  : 10
LOW_VALUE                     : C102
HIGH_VALUE                    : C10B
DENSITY                       : .1
NUM_NULLS                     : 0
NUM_BUCKETS                   : 1
LAST_ANALYZED                 : 17-DEC-18
SAMPLE_SIZE                   : 10
GLOBAL_STATS                  : YES
USER_STATS                    : NO
NOTES                         :
AVG_COL_LEN                   : 3
HISTOGRAM                     : NONE
SCOPE                         : SHARED

PL/SQL procedure successfully completed.


I offer the following hack (I stress - totally unsupported!) that might assist in seeing lock status for object tables. No guarantees on validity or correctness - use at own risk.

SQL> conn / as sysdba
Connected.

SQL>
SQL> create or replace view tmp$V (
  2  OWNER
  3  ,TABLE_NAME
  4  ,PARTITION_NAME
  5  ,PARTITION_POSITION
  6  ,SUBPARTITION_NAME
  7  ,SUBPARTITION_POSITION
  8  ,OBJECT_TYPE
  9  ,NUM_ROWS
 10  ,BLOCKS
 11  ,EMPTY_BLOCKS
 12  ,AVG_SPACE
 13  ,CHAIN_CNT
 14  ,AVG_ROW_LEN
 15  ,AVG_SPACE_FREELIST_BLOCKS
 16  ,NUM_FREELIST_BLOCKS
 17  ,AVG_CACHED_BLOCKS
 18  ,AVG_CACHE_HIT_RATIO
 19  ,IM_IMCU_COUNT
 20  ,IM_BLOCK_COUNT
 21  ,IM_STAT_UPDATE_TIME
 22  ,SCAN_RATE
 23  ,SAMPLE_SIZE
 24  ,LAST_ANALYZED
 25  ,GLOBAL_STATS
 26  ,USER_STATS
 27  ,STATTYPE_LOCKED
 28  ,STALE_STATS
 29  ,SCOPE
 30  ) as
 31    SELECT /* TABLES */
 32      u.name, o.name, NULL, NULL, NULL, NULL, 'TABLE', t.rowcnt,
 33      decode(bitand(t.property, 64), 0, t.blkcnt, TO_NUMBER(NULL)),
 34      decode(bitand(t.property, 64), 0, t.empcnt, TO_NUMBER(NULL)),
 35      decode(bitand(t.property, 64), 0, t.avgspc, TO_NUMBER(NULL)),
 36      t.chncnt, t.avgrln, t.avgspc_flb,
 37      decode(bitand(t.property, 64), 0, t.flbcnt, TO_NUMBER(NULL)),
 38      ts.cachedblk, ts.cachehit, ts.im_imcu_count, ts.im_block_count,
 39      ts.im_stat_update_time, ts.scanrate, t.samplesize, t.analyzetime,
 40      decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
 41      decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
 42      decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
 43             0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
 44      case
 45        when t.analyzetime is null then null
 46        when (dbms_stats_internal.is_stale(t.obj#, null,
 47                null,
 48                (m.inserts + m.deletes + m.updates),
 49                t.rowcnt, m.flags) > 0) then 'YES'
 50        else  'NO'
 51      end,
 52      'SHARED'
 53    FROM
 54      sys.user$ u, sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_v m
 55    WHERE
 56          o.owner# = u.user#
 57      and o.obj# = t.obj#
 58      --and bitand(t.property, 1) = 0 /* not a typed table */
 59      and o.obj# = ts.obj# (+)
 60      and t.obj# = m.obj# (+)
 61      and o.subname IS NULL
 62      and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
 63      and bitand(o.flags, 128) = 0 -- not in recycle bin
 64  /

View created.

SQL>
SQL> select * from sys.tmp$v
  2  where  table_name = 'T'
  3  and owner = 'MCDONAC'
  4  @pr
==============================
OWNER                         : MCDONAC
TABLE_NAME                    : T
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : TABLE
NUM_ROWS                      : 10
BLOCKS                        : 5
EMPTY_BLOCKS                  : 0
AVG_SPACE                     : 0
CHAIN_CNT                     : 0
AVG_ROW_LEN                   : 23
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
IM_IMCU_COUNT                 :
IM_BLOCK_COUNT                :
IM_STAT_UPDATE_TIME           :
SCAN_RATE                     :
SAMPLE_SIZE                   : 10
LAST_ANALYZED                 : 17-DEC-18
GLOBAL_STATS                  : YES
USER_STATS                    : NO
STATTYPE_LOCKED               :
STALE_STATS                   : NO
SCOPE                         : SHARED

PL/SQL procedure successfully completed.


SQL>
SQL> exec dbms_stats.lock_table_stats('MCDONAC','T');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from sys.tmp$v
  2  where  table_name = 'T'
  3  and owner = 'MCDONAC'
  4  @pr
==============================
OWNER                         : MCDONAC
TABLE_NAME                    : T
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : TABLE
NUM_ROWS                      : 10
BLOCKS                        : 5
EMPTY_BLOCKS                  : 0
AVG_SPACE                     : 0
CHAIN_CNT                     : 0
AVG_ROW_LEN                   : 23
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
IM_IMCU_COUNT                 :
IM_BLOCK_COUNT                :
IM_STAT_UPDATE_TIME           :
SCAN_RATE                     :
SAMPLE_SIZE                   : 10
LAST_ANALYZED                 : 17-DEC-18
GLOBAL_STATS                  : YES
USER_STATS                    : NO
STATTYPE_LOCKED               : ALL
STALE_STATS                   : NO
SCOPE                         : SHARED

PL/SQL procedure successfully completed.


SQL>
SQL>
SQL>


Rating

  (2 ratings)

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

Comments

Pr

A reader, December 17, 2018 - 7:10 am UTC

Could you pls provide script behind @pr?
Connor McDonald
December 18, 2018 - 1:51 am UTC

Sure. This variant came via Tom, then Tanel Poder, then some edits by myself.

def _pr_tmpfile=c:\tmp\pr.out

set termout off
store set &_pr_tmpfile.set replace
set termout on

set serverout on size 1000000 termout off
save &_pr_tmpfile replace
set termout on

0 c clob := q'\
0 declare

999999      \';;
999999      l_theCursor     integer default dbms_sql.open_cursor;;
999999      l_columnValue   varchar2(4000);;
999999      l_status        integer;;
999999      l_descTbl       dbms_sql.desc_tab2;;
999999      l_colCnt        number;;
999999  begin
999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );;
999999      dbms_sql.describe_columns2( l_theCursor, l_colCnt, l_descTbl );;
999999      for i in 1 .. l_colCnt loop
999999          dbms_sql.define_column( l_theCursor, i,
999999                                  l_columnValue, 4000 );;
999999      end loop;;
999999      l_status := dbms_sql.execute(l_theCursor);;
999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999          dbms_output.put_line( '==============================' );;
999999          for i in 1 .. l_colCnt loop
999999                  dbms_sql.column_value( l_theCursor, i,
999999                                         l_columnValue );;
999999                  dbms_output.put_line
999999                      ( rpad( l_descTbl(i).col_name,
999999                        30 ) || ': ' || l_columnValue );;
999999          end loop;;
999999      end loop;;
999999  exception
999999      when others then
999999          dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999          raise;;
999999 end;;
/

set termout off
@&_pr_tmpfile.set
set termout on

get &_pr_tmpfile nolist
host del &_pr_tmpfile


Thanks

Manoj Kumar, December 17, 2018 - 3:20 pm UTC

Thanks for the details. I think dba_ind_statistics shows the indexes on object table and shows whether stats are locked on them. And as per my understanding, if table stats are locked, index stats also will be locked automatically.
That means, if we query dba_ind_statistics, we might get the list of object tables which has stats locked.. provided they have an index on them.

I was trying to find a more direct way of getting the list of object tables which has their stats locked. Thanks again.

Could you please help me with this 2nd part of the question?

"Also, if the stats are locked for a long time for a table or index and it is using a bad plan, would it help in doing an table/index rebuild on them or gathering stats could be enough?"
Connor McDonald
December 18, 2018 - 1:59 am UTC

I think dba_ind_statistics shows the indexes on object table


Well there you go. I love it when I learn new stuff from answering questions. Yes, an object table always has an internal index.

On the second question, the answer is "maybe" :-)

The first thing I would do is gather stats. Only then would I consider looking at data structural options (rebuild etc) if query plans were still problematic.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.