Shulou(Shulou.com)06/01 Report--

This is what we always talk about in production. Inaccurate statistical information leads to abnormal sql execution. The main reason recorded this time is that the table statistics are locked, which makes it impossible to collect statistics normally, resulting in abnormal sql execution:

Collect statistics for the table:

SQL > exec DBMS_STATS.GATHER_TABLE_STATS (OWNNAME = > 'crmdb', TABNAME = >' titled ORDERDELIVERYY, CASCADE = > TRUE)

BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME = > 'crmdb', TABNAME = >' CASCADE = > TRUE); END

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 24281

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 1

Confirm the error message:

SQL > select table_name,d.stattype_locked,D.LAST_ANALYZED,d.NUM_ROWS from user_tab_statistics d where table_name in ('titled Orderliness DELIVERY')

TABLE_NAME STATTYPE_LOCKED LAST_ANALYZED NUM_ROWS

T_ORDER_DELIVERY ALL 27-APR-2017 22:00:12 0

SQL > select count (*) from T_ORDER_DELIVERY

COUNT (*)

1029883

It shows that the statistical information of the table is inaccurate and has not been collected since 2017.

Solution:

1) unlock a single table object:

Find out all the locked tables under schema:

Select table_name from user_tab_statistics where stattype_locked is not null

Query a single table:

SELECT TABLE_NAME,D.STATTYPE_LOCKED,D.LAST_ANALYZED,D.NUM_ROWS FROM USER_TAB_STATISTICS D WHERE TABLE_NAME IN ('qualified Orderlies Delaware')

Then unlock the object:

Exec dbms_stats.unlock_table_stats ('username','table_name')

SQL > exec dbms_stats.unlock_table_stats ('crmdb','T_ORDER_DELIVERY')

PL/SQL procedure successfully completed.

Collect statistics again:

SQL > exec DBMS_STATS.GATHER_TABLE_STATS (OWNNAME = > 'crmdb', TABNAME = >' titled ORDERDELIVERYY, CASCADE = > TRUE)

PL/SQL procedure successfully completed.

SQL > select table_name,d.stattype_locked,D.LAST_ANALYZED,d.NUM_ROWS from user_tab_statistics d where table_name in ('titled Orderliness DELIVERY')

TABLE_NAME STATTYPE_LOCKED LAST_ANALYZED NUM_ROWS

T_DM_ORDER_DELIVERY 22-JAN-2019 11:07:05 1029884

Unlock the entire schema:

DBMS_STATS.UNLOCK_SCHEMA_STATS ('username')

So why are the statistics for these tables locked?

It may be due to the stable execution of the plan, or the fact that impdp only imports metadata_only, or manual locking, etc. Normally, when Oracle10g or above, Oracle automatically collects statistics as needed. If you want to lock statistics manually,

Locks can be made using DBMS_STATS.LOCK_SCHEMA_STATS and DBMS_STATS.LOCK_TABLE_STATS packages.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.