Oracle Database - Enterprise Edition - Version 10.2.0.2 and later Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Information in this document applies to any platform.
*** Checked for relevance on 6-Oct-2014 ***
*** Checked for relevance on 8-Mar-2016 ***
Symptoms
Cluster C_OBJ#_INTCOL# is growing too fast.
Cause
The default stats job (GATHER_STATS_JOB) or any other job based on
the DBMS_STATS used to collect Database statistics is being run
frequently, while having a large number of subpartitions within the
database. To explain how the large number of subpartitions plays a role in the problem, consider the following example:
If 55000 subpartitions are being used, all of which have data and if
each subpartition has 100 columns and further if the histogram has 200
buckets, then the worst case scenario for number of rows in the histgrm$
table for these objects is:
55000*100*200 = approx 1 billion rows
That's the worst case scenario since there may not be 100 columns and
all subpartitions may not have data and all columns may not have 200
buckets. But it illustrates the point that histograms can take up a lot
of space as new objects are created and new data is loaded.
To make sure of the previous information, perform the following SQL queries:
1. SQL> SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
If the result is "scheduled", then the default stats job is automatically running.
2. SQL> select job_name, schedule_name, last_start_date,repeat_interval,next_run_date from dba_scheduler_jobs;
SQL> select * from dba_scheduler_wingroup_members;
SQL> select * from dba_scheduler_window_details;
This is to check for any other job that gathers statistics and
determine its scheduled run time and that of the default stats job.
3. SQL> select count(*) from histgrm$;
Before and after running the job, if the histgrm$ number of rows increases obviously, then the assumptions are correct.
4. Check the number of subpartitions in the database if it has been increased or not.
Solution
To workaround this issue:
1. Adjust the scheduler of the jobs so as not to run so often or run them manually.
2. Avoid adding so many subpartitions to the database.
Other than recreating the database, there is no other supported way to reduce the size of the cluster.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战