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.