Posted by Way back in Oracle 8.0 we introduced the “Object-Relational” database, which was “the next big thing” in the database community back then. Every vendor was scrambling to show just how cool their database technology was with the object-oriented programming paradigm.
Don’t get me wrong – using the Oracle database object types and features associated with them has made my programming life a lot easier over the years. But for me, it’s always been pretty much limited to that, ie, programming, not actually using the object types in a database design as such. Nevertheless, using objects as columns, or even creating tables of objects is supported by the database. For example, I can create a object type of MY_OBJECT (which could itself be made up of objects) and then have a table, not with that object as a column, but actually a table of that object.
SQL> create type myobj as object ( x int, y int );
2 /
Type created.
SQL> create table t of myobj;
Table created.
To populate that table, I need to construct objects before they can be inserted, because are inserting objects not rows.
SQL> insert into t
2 select myobj(rownum,rownum)
3 from dual connect by level <= 10;
10 rows created.
Obviously, for good performance, we always need to gather optimizer statistics on database tables so that the optimizer can derive the best execution plans it can for any SQL statements that access this object table. Which leads to the question: Where can we see the optimizer statistics for an object table? Because once we get into object table territory, the first thing that normally takes DBAs and Developers by surprise is that the standard dictionary view for tables looks … sparse.
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from user_tables
3 where table_name = 'T';
no rows selected
A quick perusal of the documentation reveals where we can get information for object tables – and that is the aptly named USER_OBJECT_TABLES view
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 : 18-FEB-19
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 :
MEMOPTIMIZE_READ : DISABLED
MEMOPTIMIZE_WRITE : DISABLED
HAS_SENSITIVE_COLUMN : NO
PL/SQL procedure successfully completed.
So far so good. But if I want to really dig down into optimizer statistics for table, I need to look further afield than just USER_TABLES and look at the dictionary views dedicated to just optimizer statistics information, and that is when things get a little interesting for object tables.
SQL> select *
2 from user_tab_statistics
3 where table_name = 'T';
no rows selected.
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 : 265DF9DB62004A2B90B6FB3D550CB9FD
HIGH_VALUE : E868127216F045C89F54B55A48EAD6CF
DENSITY : .1
NUM_NULLS : 0
NUM_BUCKETS : 1
LAST_ANALYZED : 18-FEB-19
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 : 18-FEB-19
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 : 18-FEB-19
SAMPLE_SIZE : 10
GLOBAL_STATS : YES
USER_STATS : NO
NOTES :
AVG_COL_LEN : 3
HISTOGRAM : NONE
SCOPE : SHARED
PL/SQL procedure successfully completed.
I can get column level statistics, but there is no entry in USER_TAB_STATISTICS for my object table. Checking the documentation indicates that unlike the USER_TABLES/USER_OBJECT_TABLES pairing, there is no partnering object-based view to match USER_TAB_STATISTICS. Digging into the definition for the USER_TAB_STATISTICS view shows that object tables are explicitly excluded:
and bitand(t.property, 1) = 0 /* not a typed table */
That might not seem such a big deal, but what happens if you lock the statistics for a table.
SQL> exec dbms_stats.lock_table_stats('','T');
PL/SQL procedure successfully completed.
The STATTYPE_LOCKED column is not on USER_TABLES and since we do not have an entry for the table in USER_TAB_STATISTICS, there is no direct mean of seeing if an object table has locked statistics. If you are faced with this problem, you have a couple of options at your disposal:
1) It would appear that the same flag on the internal dictionary table that indicates locked statistics is set for object tables as it would be for standard relational tables. Hence you could create a clone of the USER_TAB_STATISTICS view and remove the BITAND condition on the PROPERTY column. That of course is a very unsupported thing to do, and is just a rod for your back every time you patch or upgrade the database.
2) The other option is to assume that no-one is going to “mix and match” locking table statistics with index statistics. Every object table has an underlying index that is automatically created, so you can look at the locked status for this underlying index as a representative indicator of the table’s statistics locked state.
SQL> select *
2 from user_ind_statistics
3 where table_name = 'T'
4 @pr
==============================
INDEX_NAME : SYS_C0028743
TABLE_OWNER : MCDONAC
TABLE_NAME : T
PARTITION_NAME :
PARTITION_POSITION :
SUBPARTITION_NAME :
SUBPARTITION_POSITION :
OBJECT_TYPE : INDEX
BLEVEL : 0
LEAF_BLOCKS : 1
DISTINCT_KEYS : 10
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
CLUSTERING_FACTOR : 1
NUM_ROWS : 10
AVG_CACHED_BLOCKS :
AVG_CACHE_HIT_RATIO :
SAMPLE_SIZE : 10
LAST_ANALYZED : 18-FEB-19
GLOBAL_STATS : YES
USER_STATS : NO
STATTYPE_LOCKED : ALL
STALE_STATS : NO
SCOPE : SHARED
PL/SQL procedure successfully completed.
TL;DR: Querying optimizer statistics for object tables takes a little more care than with normal tables.
Learning is not a spectator sport 