Categotry Archives: 参数调整

清空Statistics History

0

Posted on by

看到SYSAUX表空间涨的很快,

主要是以下表

  • wri$_optstat_tab_history
  • wri$_optstat_ind_history
  • wri$_optstat_histhead_history
  • wri$_optstat_histgrm_history

经查是bug

Document 10279045.8 Slow Statistics purging (SYSAUX grows)


Document 8553944.8 SYSAUX tablespace grows


Document 14373728.8 Bug 14373728 – Old statistics not purged from SYSAUX tablespace


Document 11869207.8 Improvements to archived statistics purging / SYSAUX tablespace grows

 

解决如下:

1.

begin


for i in reverse 10..100


loop


dbms_stats.purge_stats(sysdate-i);


end loop;


end;


/

 

2.

exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL)

 

 

 

关闭TFA工具

0

Posted on by

在检查数据库的时候看到cpu负载比较高,原来是tfa check的时候占用的cpu比较多,因非核心系统,决定关闭掉。

用root执行。

	--检查状态

	/u01/app/11.2/grid/bin/tfactl status

	--自启动关闭

	/u01/app/11.2/grid/bin/tfactl disable

	--关闭tfa

	/u01/app/11.2/grid/bin/tfactl stop

 

NLS_LENGTH_SEMANTICS参数说明

0

Posted on by

      在oracle中有NLS_LENGTH_SEMANTICS这个参数,他有两个取值,CHAR和BYTE。默认取值为BYTE,当为CHAR时字符类型的长度是按字符个数来计算,而不是按BYTE来计算,一般情况数据库都会使用UTF-8编码,一个汉字如果按字节存放就需要3个字节。同时NLS_LENGTH_SEMANTICS对于属于SYS的表(对SYSTEM有效)无效。

 

看字段用的char和byte的sql为:

select owner,
       table_name,
       data_type,
       decode(char_used, 'C', 'char', 'B', 'BYTE') char_used,
       DATA_LENGTH,
       column_name
  from dba_tab_columns
 where data_type like '%CHAR%'
   and table_name = 'T_ORDER_TABLE'

 

 

NLS_LENGTH_SEMANTICS

Property Description
Parameter type String
Syntax NLS_LENGTH_SEMANTICS = string

 

Example: NLS_LENGTH_SEMANTICS = 'CHAR'

Default value BYTE
Modifiable ALTER SESSION
Range of values BYTE | CHAR

The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use for VARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE andCHAR in column, attribute, and variable definitions.

The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value if NLS_LENGTH_SEMANTICS it is not set explicitly by the database client through the NLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or the ALTER SESSION SETNLS_LENGTH_SEMANTICS statement.

NCHARNVARCHAR2CLOB, and NCLOB columns are always character-based.

Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).

11g自动统计信息修改调用时间

2

Posted on by

在11g中,自动收集统计信息的时间为晚上10点(周一到周五),早上6点(周六,周日)

由于有些系统在晚上10点还是高分期。会影响系统的性能,所以简单的方法就是把时间调整为晚上3点;

当然也可以把自动统计信息关闭,自己写个存储过程自定义的去收集统计信息。

把时间调整为晚上3点的方法:

 sql:
select a.window_name, a.repeat_interval,a.duration
   from dba_scheduler_windows a, dba_scheduler_wingroup_members b
  where a.window_name = b.window_name
    and b.window_group_name = 'MAINTENANCE_WINDOW_GROUP';

 

MONDAY_WINDOW	freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0	+000 04:00:00
TUESDAY_WINDOW	freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0	+000 04:00:00
WEDNESDAY_WINDOW	freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0	+000 04:00:00
THURSDAY_WINDOW	freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0	+000 04:00:00
FRIDAY_WINDOW	freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0	+000 04:00:00
SATURDAY_WINDOW	freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0	+000 20:00:00
SUNDAY_WINDOW	freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0	+000 20:00:00

 

用sys执行

 

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 03:00:00');
end;
/

查看一下是否修改成功

select t1.window_name, t1.repeat_interval, t1.duration
  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');




MONDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
TUESDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
WEDNESDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
THURSDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
FRIDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
SATURDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00
SUNDAY_WINDOW	freq=daily;byday=SAT;byhour=3;byminute=0; bysecond=0	+000 03:00:00

 

已修改成功。

 

另附怎么关闭这个scheduler调度:

To disable a specific maintenance task, use the DISABLE procedure, as follows:

SQL> BEGIN
       dbms_auto_task_admin.disable(
       client_name => 'auto optimizer stats collection',
       operation => NULL,
       window_name => NULL);
     END;  
    /


To enable this specific maintenance task again, use the ENABLE procedure, as follows:
 

SQL> BEGIN
       dbms_auto_task_admin.enable(
       client_name => 'auto optimizer stats collection',
       operation => NULL,
       window_name => NULL);
     END;
     /

The task names to use for the client_name argument are listed in the DBA_AUTOTASK_CLIENT database dictionary view.

select client_name,status from DBA_AUTOTASK_CLIENT;