当前位置：首页 > news >正文

Oracel 统计信息收集

news 来源：原创 2025/7/3 17:57:49

1. 常规的通过PLSQL客户端进行统计信息收集，需要指定用户名和表名

begin

dbms_stats.gather_table_stats(ownname => '&owner',

tabname => '&tablename',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

cascade => TRUE,

degree => 4,

method_opt => 'FOR ALL COLUMNS SIZE AUTO',

no_invalidate => false);

end;

estimate_percent // 0.000001 - 100 ，这是采样率

2通过dbms收集统计信息

2.1 收集数据表的统计信息。

exec dbms_stats.gather_table_stats(ownname =>'OWNER', tabname =>'TABLE_NAME');

2.2 收集索引的统计信息。

exec dbms_stats.gather_schema_stats(ownname =>'OWNER');

3. 确认是否自动开启统计信息收集

--如下语句可以查询自动统计信息是否打开，需要这三个语句查出来的所有状态都是enable。

select window_name,autotask_status,optimizer_stats from dba_autotask_window_clients;

select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';

select window_name,repeat_interval,duration,enabled from dba_scheduler_windows where ENABLED='TRUE' AND window_name not like 'WEEK%';

--通过如下语句可以查询任务执行历史信息

select * from dba_scheduler_job_run_details where job_name like 'ORA$AT_OS%' order by log_date desc;

--查询最近7天的统计信息任务执行历史

select m.LOG_DATE, m.STATUS, m.ACTUAL_START_DATE, m.RUN_DURATION

from dba_scheduler_job_run_details m

where job_name like 'ORA$AT_OS%'

and m.log_date > sysdate - 10

order by log_date desc;

--查询执行计划的设定时间

select t.window_name,

t.schedule_type,

t.last_start_date,

t.next_start_date,

t.enabled,

t.active,

t.repeat_interval,

t.duration,

t.window_priority,

t.start_date

from dba_scheduler_windows t;

--查看表或索引的统计信息是否需更新，如果行数相差很多,则该表需要更新统计信息

select t.table_name, t.num_rows, t.last_analyzed

from dba_tables t

where table_name = 'TBL_REQUEST';

---查询表数据量变化多少，Oracle的角度超过0.1 都需要统计信息更新

select a.table_name,

a.num_rows,

b.inserts + b.updates + b.deletes change_rows,

to_char((b.inserts + b.updates + b.deletes) / a.num_rows, '90.000') chg_pct

from DBA_TABLES a, DBA_TAB_MODIFICATIONS b

where a.TABLE_NAME = b.table_name

and a.OWNER = b.table_owner

and a.num_rows is not null

and a.num_rows != 0;

查看当前数据库时间

select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "now" from dual;

查看统计信息执行进度

select * from v$session_longops where sofar!=totalwork;

--查询索引创建进度

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA WHERE SQL_ID = '2hjuwpdamsxb5';

ADDRESS HASH_VALUE SQL_TEXT

-------- ---------- ------------------------------

1EFB91B8 3642190903 select count(*) from xff

SQL> exec dbms_shared_pool.purge('1EFB91B8, 3642190903','C');

PL/SQL 过程已成功完成。

SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA WHERE SQL_ID = '2hjuwpdamsxb5';

--Oracle自带统计更新定时任务默认时间窗设置如下：

周一到周五每天22:00启动，执行时间窗4小时；

周六周日每天06:00启动，执行时间窗20小时；

针对不同的局点，以及机型性能，业务定时任务影响，数据量大小，有时需要进行调整，错开高峰期，防止对业务产生冲突；

【操作步骤】

根据业务运行的需要，可以根据实际情况调整时间窗设置，调整样例如下：

#1修改周一到周五的启动时间, 修改启动时间到晚上3点（直接修改byhour=3即可），

sqlplus "/as sysdba"

begin

dbms_scheduler.set_attribute(name=>'MONDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=MON;byhour=3;byminute=0; bysecond=0');

dbms_scheduler.set_attribute(name=>'TUESDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=TUE;byhour=3;byminute=0; bysecond=0');

dbms_scheduler.set_attribute(name=>'WEDNESDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=WED;byhour=3;byminute=0; bysecond=0');

dbms_scheduler.set_attribute(name=>'THURSDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=THU;byhour=3;byminute=0; bysecond=0');

dbms_scheduler.set_attribute(name=>'FRIDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=FRI;byhour=3;byminute=0; bysecond=0');

end;

#2设置周六、周日每天3点执行定时任务，每次执行四小时：

sqlplus "/as sysdba"

begin

dbms_scheduler.set_attribute(name=>'SATURDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');

dbms_scheduler.set_attribute(name=>'SATURDAY_WINDOW',attribute=>'DURATION',value=>'+000 04:00:00');

dbms_scheduler.set_attribute(name=>'SUNDAY_WINDOW',attribute=>'REPEAT_INTERVAL',value=>'freq=daily;byday=SUN;byhour=3;byminute=0; bysecond=0');

dbms_scheduler.set_attribute(name=>'SUNDAY_WINDOW',attribute=>'DURATION',value=>'+000 04:00:00');

end;

#3查询定时任务窗口，周期性执行策略配置：

select window_name,repeat_interval from dba_scheduler_windows;

维护窗口组有哪些维护组

select * from dba_scheduler_wingroup_members;

select t1.window_name,

t1.repeat_interval,

t1.duration,

t2.window_group_name

from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2

where t1.window_name = t2.window_name

and t2.window_group_name in

('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');

常用视图：

select * from dba_scheduler_programs

select * from dba_scheduler_jobs

select * from dba_scheduler_running_jobs

select * from dba_scheduler_job_run_details

select * from dba_scheduler_schedules

select * from dba_scheduler_wingroup_members

select * from DBA_AUTOTASK_CLIENT_JOB;

select * from DBA_AUTOTASK_CLIENT;

select * from DBA_AUTOTASK_JOB_HISTORY;

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

select * from DBA_AUTOTASK_CLIENT_HISTORY;

10g关闭自动收集job

select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

所以建议最好关闭自动统计信息收集功能：

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

启动自动统计信息收集功能

exec DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');

手工收集统计信息：

SQL> exec dbms_stats.gather_schema_stats('detail',options=>'gather stale',estimate_percent =>10);

--收集没有分析过的表的统计信息

begin

dbms_stats.gather_schema_stats(ownname=>scott,options=>'gather empty');

end;

--重新分析修改量超过10%的表（这些修改包括插入、更新和删除）

begin

dbms_stats.gather_schema_stats(ownname=>scott,options=>'gather stale');

end;

11g关闭自动收集job

The automated maintenance tasks infrastructure (known as AutoTask) schedules tasks to

run automatically in Oracle Scheduler windows known as maintenance windows. By default,

one window is scheduled for each day of the week. Automatic optimizer statistics collection

runs as part of AutoTask and is enabled by default to run in all predefined maintenance windows.

If for some reason automatic optimizer statistics collection is disabled, then you can enable

it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => NULL);

END;

When you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => NULL);

END;

手工收集统计信息：

SQL> exec dbms_stats.gather_schema_stats('detail',options=>'gather stale',estimate_percent =>10);

相关文章：