There are unlimited features provided by oracle
with an upgraded version, one of the feature we are going to discuss is stats
lock and unlock in the oracle database,
Assume the application team has set up stats gather
job from application end but that causing issue to the database like CPU/Memory
utilization getting high during the job execution, being a DBA you can ask them to
stop/disable the job but in case if it’s not feasible then you can lock stats
on tables and unlock whenever required.
Another scenario- you have locked a good execution
plan for ETL/Job run and that should not change with stats gathered, then you can
lock the stats for the table.
Below we will discuss how to do this exactly, I
have a table called LOCATIONS under user TECHNO, I do not want to gather stats on
the same, in the below query, if stats are locked, the output will be ALL if not then
it will be blank, you can stats are not locked
SQL> SELECT stattype_locked FROM
dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';
STATT
-----
SQL>
Now I will lock stats and execute the
query again
SQL> exec
dbms_stats.lock_table_stats('TECHNO','LOCATIONS');
PL/SQL procedure successfully completed.
SQL> SELECT stattype_locked FROM
dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';
STATT
-----
ALL
SQL>
Now I will try to gather stats on the
same table, it should throw error
SQL> exec
dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');
BEGIN dbms_stats.gather_table_stats(ownname=>
'TECHNO', tabname=>'LOCATIONS'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype =
ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40751
Now we will see how to unlock stats and
gather stats
SQL> exec
dbms_stats.unlock_table_stats('TECHNO','LOCATIONS');
PL/SQL procedure successfully completed.
SQL> exec
dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');
PL/SQL procedure successfully completed.
SQL>
0 comments:
Post a Comment