课程第29次（2018-08-23星期四）

botang

AWR的4类：

1. select  substr (  t.table_name , 3,1 )     ,  count(*)    from
   
2. dba_tables t where
   
3. t.owner='SYS' and t.tablespace_name='SYSAUX'
   
4. and t.table_name  like 'WR_$%'
   
5. group by substr (  t.table_name , 3,1 ) ;

复制代码

	SUBSTR(T.TABLE_NAME,3,1)	COUNT(*)
1	R	15
2	H	100
3	I	80
4	M	10

AWR H类  来自对应V$视图：

1. select  t.table_name   from
   
2. dba_tables t where
   
3. t.owner='SYS' and t.tablespace_name='SYSAUX'
   
4. and t.table_name  like 'WR_$%ACTIVE%' ;

复制代码

	TABLE_NAME
1	WRH$_ACTIVE_SESSION_HISTORY_BL

1. select  t.table_name   from
   
2. dict t where t.table_name  like 'V$%ACTIVE_SESSION%' ;

复制代码

	TABLE_NAME
1	V$ACTIVE_SESSION_HISTORY

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

1. select  dbms_stats.get_prefs(pname => 'ESTIMATE_PERCENT')  from dual;
   
2. 
   
3. begin
   
4.    dbms_stats.set_table_prefs(ownname => 'HR',tabname => 'EMPLOYEES',
   
5.    pname =>  'ESTIMATE_PERCENT',pvalue => 100);
   
6. end;
   
7. 
   
8. select  * from dba_tab_stat_prefs;

复制代码

	OWNER	TABLE_NAME	PREFERENCE_NAME	PREFERENCE_VALUE
1	SH	SALES	STALE_PERCENT	13
2	SH	SALES	METHOD_OPT	FOR ALL COLUMNS SIZE AUTO FOR COLUMNS AMOUNT_SOLD SIZE 254
3	HR	EMPLOYEES	ESTIMATE_PERCENT	100

1. select  dbms_stats.get_prefs(pname => 'METHOD_OPT')  from dual;

复制代码

	DBMS_STATS.GET_PREFS(PNAME=>'M
1	FOR ALL COLUMNS SIZE AUTO

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

1. begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(23040,60); end;

复制代码

-------

1. begin
   
2. DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
   
3.         start_snap_id =>119,
   
4.         end_snap_id  => 120,
   
5.         baseline_name =>'BASELINE2');
   
6. end;

复制代码

------
SAA一般要选 删除无效对象：