Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle 23ai Staging Table Statistics bug?

nmmmgOct 22 2024 — edited Oct 22 2024

Hi, I was testing out a new feature in 23ai and I stumbled upon something.

To summarize the issue: When inserting data into a staging table using append insert, statistics are not collected (which is expected behavior). However, when the staging table is created using CTAS, statistics are automatically collected(which is not expected behavior).

--By default, staging tables in Oracle 23ai should not have statistics collected.
SQL>
create table staging_tbl1 (col1 number) for staging;
insert into staging_tbl1 values (1);
commit;

--Manual collection of table statistics fails.
SQL> exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'STAGING_TBL1');
BEGIN dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'STAGING_TBL1'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 42592
ORA-06512: at "SYS.DBMS_STATS", line 41877
ORA-06512: at "SYS.DBMS_STATS", line 9201
ORA-06512: at "SYS.DBMS_STATS", line 10265
ORA-06512: at "SYS.DBMS_STATS", line 41025
ORA-06512: at "SYS.DBMS_STATS", line 42025
ORA-06512: at "SYS.DBMS_STATS", line 42573
ORA-06512: at line 1

--Additionally, due to the 12c feature, the `_optimizer_gather_stats_on_load` parameter prevents statistics from being collected during append inserts.
SQL>
create table staging_tbl2 (col1 number) for staging;
insert /*+ append */ into staging_tbl2 select * from staging_tbl1;
commit;

--Check the table statistics.
SQL>
set lines 200 pages 1000
col table_name for a15
select table_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed
from dba_tables
where table_name = 'STAGING_TBL2';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANAL
--------------- ---------- ---------- ------------ ----------- ---------
STAGING_TBL2
--Statistics have not been collected.

--However, when creating a staging table using CTAS (Create Table As Select), statistics are generated.
SQL> create table staging_tbl3 for staging as select * from staging_tbl1;

Table created.

--Check the table statistics.
SQL>
set lines 200 pages 1000
col table_name for a15
select table_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed
from dba_tables
where table_name = 'STAGING_TBL3';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANAL
--------------- ---------- ---------- ------------ ----------- ---------
STAGING_TBL3 1 4 0 3 20-OCT-24
--Statistics have been collected.

In my opinion, this appears to be a bug. Could someone from Oracle verify this?

And my database version is 23.5.0.24.07.

SQL> select * from v$version;

BANNER
---------------------------------------------------------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
--------------------------------------------------------------------------------------------------------------------------------- ----------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems 0

This post has been answered by Connor-Oracle on Nov 13 2024
Jump to Answer
Comments
Post Details
Added on Oct 22 2024
3 comments
341 views