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
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.
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
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
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
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
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?
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
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?
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
July 20, 2023 - 2:39 pm UTC
Thanks for clarifying. Contact support if you're hitting this issue.