You Asked

Hi Tom,
Recently, we found that our database had a slow performance. After checking the table stats, we found that most of the table stats of one schema was locked while other schema were not. And I have to unlocked all the table stats and regather those table stats manually.

SQL> select count(*) from user_tables;

COUNT(*)
----------
311

SQL> select table_name from user_tab_statistics where stattype_locked is not null;

COUNT(*)
----------
285

SQL>

Here's my questions:
1) Will oracle automatically lock the table stats of some certain schema or not?
2) If someone locked the table stats manually, how could we find out when he/she did this (the audit_trail is set to NONE)?

and Connor said...

1) To my knowledge, nothing automatically locks the stats

2) lock/unlock are considered a "manipulation" so they will be tracked in the standard history view

SQL> create table t1 ( x int );

Table created.

SQL>
SQL> insert into t1
  2  select rownum
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL>
SQL> select STATS_UPDATE_TIME
  2  from DBA_TAB_STATS_HISTORY
  3  where owner = user
  4  and table_name = 'T1';

no rows selected

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

PL/SQL procedure successfully completed.

SQL>
SQL> select STATS_UPDATE_TIME
  2  from DBA_TAB_STATS_HISTORY
  3  where owner = user
  4  and table_name = 'T1';

STATS_UPDATE_TIME
--------------------------------------------------------------------
22-MAR-16 02.18.36.354000 PM +08:00

SQL>
SQL> exec dbms_stats.lock_table_stats('','T1');

PL/SQL procedure successfully completed.

SQL>
SQL> select STATS_UPDATE_TIME
  2  from DBA_TAB_STATS_HISTORY
  3  where owner = user
  4  and table_name = 'T1';

STATS_UPDATE_TIME
--------------------------------------------------------------------
22-MAR-16 02.18.36.354000 PM +08:00
22-MAR-16 02.18.36.381000 PM +08:00

SQL>
SQL> exec dbms_stats.unlock_table_stats('','T1');

PL/SQL procedure successfully completed.

SQL> select STATS_UPDATE_TIME
  2  from DBA_TAB_STATS_HISTORY
  3  where owner = user
  4  and table_name = 'T1';

STATS_UPDATE_TIME
--------------------------------------------------------------------
22-MAR-16 02.18.36.354000 PM +08:00
22-MAR-16 02.18.36.381000 PM +08:00
22-MAR-16 02.18.36.391000 PM +08:00

To determine which row is an unlock, you'd to liaise with Support. (It's tucked away on one of the internal tables that make up the view).

Rating

(1 rating)

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

Comments

Oracle 18c - about lock/unlock stats at subpartitions

Rajeshwaran Jeyabal, April 25, 2021 - 4:28 pm UTC

Team:

The below demo was from Oracle 18c database, The document stats that "lock_partition_stats" method was possible on subpartition level.

However the below test case shows that it was not possible - could you please confirm, is it not possible to lock subpartition level stats in Oracle 18c database?

https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_STATS.html#GUID-C8437383-8D09-4BB1-936A-0B620E0B6A0E

demo@XEPDB1> create table t(x int,y int, z date)
  2  partition by list(x)
  3  subpartition by hash(y)
  4  subpartitions 2
  5  ( partition p1 values (1) ,
  6    partition p2 values (2) );

Table created.

demo@XEPDB1> select table_name,partition_name,subpartition_name,stattype_locked,stale_stats
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name;

TABLE_NAME PARTITION_ SUBPARTITION_ STATT STALE_S
---------- ---------- ------------- ----- -------
T          P1         SYS_SUBP376
T          P1
T          P1         SYS_SUBP377
T          P2
T          P2         SYS_SUBP379
T          P2         SYS_SUBP378
T

7 rows selected.

demo@XEPDB1> exec dbms_stats.LOCK_PARTITION_STATS(user,'T','P1');

PL/SQL procedure successfully completed.

demo@XEPDB1> select table_name,partition_name,subpartition_name,stattype_locked,stale_stats
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name;

TABLE_NAME PARTITION_ SUBPARTITION_ STATT STALE_S
---------- ---------- ------------- ----- -------
T          P1         SYS_SUBP376   ALL
T          P1                       ALL
T          P1         SYS_SUBP377   ALL
T          P2
T          P2         SYS_SUBP379
T          P2         SYS_SUBP378
T

7 rows selected.

demo@XEPDB1> exec dbms_stats.UNLOCK_PARTITION_STATS(user,'T','P1');

PL/SQL procedure successfully completed.

demo@XEPDB1> select table_name,partition_name,subpartition_name,stattype_locked,stale_stats
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name;

TABLE_NAME PARTITION_ SUBPARTITION_ STATT STALE_S
---------- ---------- ------------- ----- -------
T          P1         SYS_SUBP376
T          P1
T          P1         SYS_SUBP377
T          P2
T          P2         SYS_SUBP379
T          P2         SYS_SUBP378
T

7 rows selected.

demo@XEPDB1> exec dbms_stats.LOCK_PARTITION_STATS(user,'T','SYS_SUBP376');

PL/SQL procedure successfully completed.

demo@XEPDB1> select table_name,partition_name,subpartition_name,stattype_locked,stale_stats
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name;

TABLE_NAME PARTITION_ SUBPARTITION_ STATT STALE_S
---------- ---------- ------------- ----- -------
T          P1         SYS_SUBP376
T          P1
T          P1         SYS_SUBP377
T          P2
T          P2         SYS_SUBP379
T          P2         SYS_SUBP378
T

7 rows selected.

demo@XEPDB1>

April 27, 2021 - 6:09 am UTC

Replicated on 19.10.

I'll log a bug

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Oracle 18c - about lock/unlock stats at subpartitions

More to Explore

PL/SQL demos

PL/SQL docs

Will Oracle automatically lock the table stats of one schema?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Oracle 18c - about lock/unlock stats at subpartitions

More to Explore

PL/SQL demos

PL/SQL docs