Skip to Main Content
  • Questions
  • How to exclude a list of tables in DBMS_STATS.GATHER_SCHEMA_STATS ?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mohammed Amine.

Asked: March 21, 2016 - 3:30 pm UTC

Last updated: July 20, 2023 - 2:39 pm UTC

Version: 12.1

Viewed 10K+ times! This question is

You Asked

Hello,

Is there any way to exclude a list of tables while doing DBMS_STATS.GATHER_SCHEMA_STATS?

DBMS_STATS.LOCK_TABLE_STATS doesn't answer my need exactly.

Best regards,
Amine

and Chris said...

What's wrong with dbms_stats.lock_table_stats exactly?

I'm not aware of a way to exclude tables completely. But there is another workaround.

By default publishes stats for a table after gathering them. In 11gR1 there's the concept of pending stats. By setting the PUBLISH table preference to false, Oracle still gathers the stats. But doesn't make them generally available:

create table t1 as
  select rownum x from dual connect by level <= 1000;

create table t2 as
  select rownum x from dual connect by level <= 1000;

select table_name, last_analyzed from user_tables;

TABLE_NAME                     LAST_ANALYZED      
------------------------------ --------------------
T1                                                 
T2

exec dbms_stats.set_table_prefs(user, 't2', 'PUBLISH', 'false');

exec dbms_stats.gather_schema_stats(user);

-- last analyzed for t2 not set; the stats aren't published
select table_name, last_analyzed from user_tables;

TABLE_NAME                     LAST_ANALYZED      
------------------------------ --------------------
T1                             21-MAR-2016 16:15:37
T2


You can read more about this at:

https://oracle-base.com/articles/11g/statistics-collection-enhancements-11gr1#pending_statistics

If these don't help you'll need to give us more info on why lock stats and this aren't appropriate!

Rating

  (11 ratings)

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

Comments

Thanks Chris

Mohammed Amine HLIMI, March 21, 2016 - 4:57 pm UTC

Thank you Chris,
In fact, we have a list of "staging" tables (volatile data) for which we gather the statistics within the code, just at the beginning, if the stats are locked, I'll have an ORA-20005 because:
1- I don't have FORCE => true,
2- I'm trying to avoid modifying the code.

When the Automatic statistics gathering job runs (by night), most of these tables are empty, so, I wanted someway to "filter" those.

If only we had a way to exclude tables using "obj_filter_list" parameter of DBMS_STATS.GATHER_SCHEMA_STATS!

Maybe I'm asking for something impossible here.

Regards,
Amine
Chris Saxon
March 21, 2016 - 5:09 pm UTC

Well you could always approach it from the other angle. Specifically state all the table you *do* want to gather stats on:

drop table t1 purge;
drop table t2 purge;
create table t1 as
  select rownum x from dual connect by level <= 1000;

create table t2 as
  select rownum x from dual connect by level <= 1000;
 
DECLARE
   filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
   filter_lst.extend(1);
   filter_lst(1).ownname := user;
   filter_lst(1).objname := 'T1';
   DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>user,obj_filter_list=>filter_lst);
END;
/

select table_name, last_analyzed from user_tables;

TABLE_NAME                     LAST_ANALYZED      
------------------------------ --------------------
T1                             21-MAR-2016 17:09:06
T2

A reader, March 21, 2016 - 5:53 pm UTC

Would you guys ever recommend disabling stats gathering all together? Many people seem to fear it having a negative impact on plans rather than positive. My thinking was that it's essential to gather stats unless all your data is static, read-only.
Chris Saxon
March 22, 2016 - 12:59 am UTC

It very much depends on the database and the support of that database.

Some databases are installed and are never touched by anyone. They are just backed up, but not proactively watched etc. For those, as much automation as possible (stats, memory, etc) is probably a good thing. We'll get (say) to within 80% of the theoretically optimal performance and that's fine.

Some databases have occasional proactive support (eg, DBA checks on them weekly, or has them in OEM etc). So we'll have *mostly* automation, but we might lock some stats, manually set some stats etc, and leave the rest to default. We'll get (say) to within 90% of the theoretically optimal performance.

And some databases have the luxury of full time DBA monitoring, most likely because they are mission critical, and every single SQL statement is also mission critical. So we might lock lots of stats, manually control histogram creation, judiciously decide on extended column stats etc.

The nice thing with dbms_stats - you can choose the level you want.

Alam Saad, March 06, 2018 - 1:28 pm UTC

my production sytem have dbms stats jobs failing issue.
exec dbms_stats.set_table_prefs('SYS','X$LOGMNR_CONTENTS','PUBLISH','false');
i tried to run this procedure but it failed with this error
ERROR at line 1:
ORA-20000: TABLE "SYS"."X$LOGMNR_CONTENTS" does not exist or insufficient
privileges
ORA-06512: at "SYS.DBMS_STATS", line 45274
ORA-06512: at line 1

the error i am getting on cloud control 12 is

SYS.X$LOGMNR_CONTENTS TABLE 27-FEB-18 10.07.10.371953 PM -07:00 27-FEB-18 10.07.10.507713 PM -07:00 FAILED
SYS.X$LOGMNR_REGION TABLE 27-FEB-18 10.07.10.511051 PM -07:00 27-FEB-18 10.07.10.561863 PM -07:00 FAILED
SYS.X$DRC TABLE 27-FEB-18 10.07.10.565742 PM -07:00 27-FEB-18 10.07.10.624197 PM -07:00 FAILED
all other jobs are running successfully and its dbms gather stats Job ,kidly help me on this

Connor McDonald
March 07, 2018 - 1:19 am UTC

Don't ever run stats against SYS objects directly.

For that, you use:

dbms_stats.gather_dictionary_stats, and
dbms_stats.gather_fixed_object_stats

dbms_stats job failing

Alam Saad, March 07, 2018 - 9:25 am UTC

after painful searching for over a week i got the solution for my problem ,since gathering stats on these three tables SYS.X$LOGMNR_CONTENTS
SYS.X$LOGMNR_REGION
SYS.X$DRC
requires either dataguard or logminer to be enabled based on the below error i was getting on my production server i fired below commands to fix the issue .
SQL> exec dbms_stats.lock_table_stats('SYS','X$LOGMNR_CONTENTS');

PL/SQL procedure successfully completed.

SQL> dbms_stats.lock_table_stats('SYS','X$LOGMNR_REGION');

SQL> exec dbms_stats.lock_table_stats('SYS','X$LOGMNR_REGION');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.lock_table_stats('SYS','X$DRC');

PL/SQL procedure successfully completed.

after this i reran the job gather database stats which completed successfully without any error.
and lastly i think people on ask tom forum should be more helpful and open in resolving the problem


Chris Saxon
March 07, 2018 - 11:35 am UTC

What are exactly are you doing that leads you to lock stats on these tables? As Connor says, you shouldn't be writing jobs to gather stats on SYS tables. Or locking them.

If you have problems with gather_dictionary_stats or gather_fixed_object_stats, please tell us what they are!

dbms_stats job failing

Alam Saad, March 08, 2018 - 10:28 am UTC

i was getting this error daily whenever dbms_stats job ran :
Errors in file /u01/TBCCDDB/diag/rdbms/tbccd/TBCCD/trace/TBCCD_j003_19550.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_3196"
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 44417

Script Name : /DBA/SCRIPTS/UTIL/SCAN_ALERT_LOG/Call_Scan_Alert_Log.ksh

after logging on to cloud control 12c optimizer statistics window i found that it was these 3 tables which have stats collection failed status
X$LOGMNR_CONTENTS,X$LOGMNR_REGION,X$DRC
when i tried to gather stats on these tables manually i got these errors
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$LOGMNR_REGION", log miner or data
guard must be started before analyzing this fixed table"
ORA-06512: at "SYS.DBMS_STATS", line 34757
ORA-06512: at line 1

since dataguard has not been implemented in my environment ,i thought this was the root cause of the problem so i disabled stats collection on these three objects which resolved my issue
Chris Saxon
March 08, 2018 - 11:02 am UTC

Is:

/DBA/SCRIPTS/UTIL/SCAN_ALERT_LOG/Call_Scan_Alert_Log.ksh

the name of the script this job runs?

If so, that looks like a custom script. And the correct place to fix this - by removing any dbms_stats calls to SYS objects!

dbms_stats job failing

Alam Saad, March 09, 2018 - 6:50 am UTC

output of the script
$ more /DBA/SCRIPTS/UTIL/SCAN_ALERT_LOG/Call_Scan_Alert_Log.ksh
MAILTO=$1
LOG=/tmp/Call_Check_Alert.log
rm $LOG > /dev/null 2>&1
for i in `more /DBA/LISTS/.gateway | grep -v '^#'|grep -v FPRM |awk -F: '{print $1}'|grep -v '^D'`
do
echo processing $i
ssh ${i}db -o StrictHostKeyChecking=no /DBA/SCRIPTS/UTIL/SCAN_ALERT_LOG/Scan_Alert_Log.ksh $i $MAILTO |tee -a >> $LOG 2>&1
if [[ `echo $?` -gt 0 ]]; then
echo "SSH Failing to $i" > /tmp/ssh_failure.log
echo "Script : /DBA/SCRIPTS/UTIL/SCAN_ALERT_LOG/Call_Scan_Alert_Log.ksh" >> /tmp/ssh_failure.log
echo "Server : TOEM" >> /tmp/ssh_failure.log
cat /tmp/ssh_failure.log | mailx -s "Alert : SSH / Script Failure in $i" alerts_oracle@dish.com
fi
done
[TERP] oracle@tm1erpdbl01 :/u01/app/oracle]

the script is only looking for ora errors in alert log .It is not connecting to the database and executing dbms_stats procedure .It is oracle's automated maintenance task(gather_database_stats (auto)) that is firing the the above said procedure
Chris Saxon
March 14, 2018 - 12:08 pm UTC

If the errors are coming from the supplied job, speak with support to find out what the issue is.

How to exclude Oracle Text Tables?

Amin Adatia, July 23, 2018 - 3:28 pm UTC

The "documentation" for Oracle Text says that the auto stats gathering job will exclude any of the DR$ tables. However, we are finding that some of the intermediate tables usd by Oracle Text are not excluded -- DR$L for example. So these tables get the stats and when the Oracle Text optimize (REBUILD) completes and the DR$L table is swapped to the DR$I table, the stats go with it.
How to avoid this?
Chris Saxon
July 24, 2018 - 9:46 am UTC

You can stop the database gathering fresh stats on a table by locking them with DBMS_STATS.LOCK_TABLE_STATS

But... are you sure you need to do this?

12.1 bug MoS 2051004.1

Andrew Fraser, March 15, 2019 - 11:41 am UTC

Chris Saxon
March 15, 2019 - 2:56 pm UTC

Good find, thanks.

fixed ???

Michael, July 19, 2023 - 1:56 pm UTC

MOS note says it's fixed in 12.2, in my version 19.17 it's still present.
What to do now?
Chris Saxon
July 19, 2023 - 4:23 pm UTC

What exactly are you referring to?

If you think you're hitting a bug/regression, contact support.

Michael, July 19, 2023 - 7:17 pm UTC

TABLE "SYS"."X$LOGMNR_REGION" and failure during automatic gather statistics job

Michael, July 19, 2023 - 7:21 pm UTC

Andrew said: that issue looks like this bug https://support.oracle.com/epmos/faces/DocumentDisplay?id=2051004.1
you said: Good find, thanks.

Read MoS 2051004.1 where was mentioned fixed in 12.2

i have still this problem with error messages and in total fixed objects where not gatherd,
but my version is 19.17
Chris Saxon
July 20, 2023 - 2:39 pm UTC

Thanks for clarifying. Contact support if you're hitting this issue.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here