课程第55次（2017-07-15星期六下午）

botang

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

复制代码

1. select  * from dba_views  v
   
2. where v.view_name='DBA_ALERT_HISTORY';
   
3.   
   
4. select * from dict d
   
5.   where d.table_name  like '%ACTIVE%';
   
6.   
   
7.   select  * from dba_views v
   
8.    where v.view_name='DBA_HIST_ACTIVE_SESS_HISTORY';

复制代码

1. select  t.TABLE_NAME
   
2. from dba_tables t
   
3. where t.OWNER='SYS' and t.TABLESPACE_NAME='SYSAUX'
   
4. and t.TABLE_NAME  like 'WRH$%'
   
5. order by t.TABLE_NAME;
   
6. 
   
7. select  * from v$asm_diskgroup;
   
8. 
   
9. select  t.TABLE_NAME
   
10. from dba_tables t
    
11. where t.OWNER='SYS' and t.TABLESPACE_NAME='SYSAUX'
    
12. and t.TABLE_NAME  like 'WRI$%'
    
13. order by t.TABLE_NAME;
    
14. 
    
15. select  t.TABLE_NAME
    
16. from dba_tables t
    
17. where t.OWNER='SYS' and t.TABLESPACE_NAME='SYSAUX'
    
18. and t.TABLE_NAME  like 'WRM$%'
    
19. order by t.TABLE_NAME;
    
20. 
    
21. 
    
22. select  * from WRM$_snapshot;
    
23. 
    
24. select  t.TABLE_NAME
    
25. from dba_tables t
    
26. where t.OWNER='SYS' and t.TABLESPACE_NAME='SYSAUX'
    
27. and t.TABLE_NAME  like 'WRR$%'
    
28. order by t.TABLE_NAME;

复制代码

1. SQL> exec dbms_stats.gather_table_Stats('HR','TABC') ;
   
2. 
   
3. PL/SQL procedure successfully completed.
   
4. 
   
5. SQL> insert into  tabc  values ( 'a1' ,1 ) ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> create index iabc on tabc ( uname ) ;
    
14. 
    
15. Index created.
    
16. 
    
17. SQL>  insert into  tabc  values ( 'a2' ,2  ) ;
    
18. 
    
19. 1 row created.
    
20. 
    
21. SQL> commit;
    
22. 
    
23. Commit complete.
    
24. 
    
25. SQL> exec dbms_stats.gather_table_Stats('HR','TABC') ;
    
26. 
    
27. PL/SQL procedure successfully completed.
    
28. 
    
29. SQL>
    

复制代码

1. select  t.NUM_ROWS, t.LAST_ANALYZED
   
2.   from dba_tables t
   
3.   where t.OWNER='HR' and t.TABLE_NAME='TABC';
   
4.   
   
5.   select  i.NUM_ROWS , i.LAST_ANALYZED
   
6.     from dba_indexes i
   
7.          where i.OWNER='HR' and i.INDEX_NAME='IABC';

复制代码

1. SQL> insert into tabc select  * from t04209_uname  where uvalue > 2   ;
   
2. 
   
3. 99998 rows created.
   
4. 
   
5. SQL> commit;
   
6. 
   
7. Commit complete.
   
8. 
   
9. SQL> update tabc  set uvalue=100 where uvalue < 99900 ;         
   
10. 
    
11. 99899 rows updated.
    
12. 
    
13. SQL> commit;
    
14. 
    
15. Commit complete.
    
16. 
    
17. SQL> exec dbms_Stats.gather_table_stats('HR','TABC') ;
    
18. 
    
19. PL/SQL procedure successfully completed.
    
20. 
    
21. SQL> begin
    
22.   2   for i in 1..100
    
23.   3   loop
    
24.   4    update tabc  set uvalue=i where uname = 'a'||to_char(i);  
    
25.   5   end loop;
    
26.   6   commit;
    
27.   7  end;
    
28.   8  /
    
29. 
    
30. PL/SQL procedure successfully completed.
    
31. 
    
32. SQL> exec dbms_stats.gather_table_stats ('HR', 'TABC' , estimate_percent=>100 , method_opt=>'for all columns size auto for columns uvalue size 254' );
    
33. 
    
34. PL/SQL procedure successfully completed.
    

复制代码

1. SQL> exec dbms_stats.gather_table_stats ('HR', 'TABC' , estimate_percent=>100 ,  method_opt=>'for all columns size skewonly') ;
   
2. 
   
3. PL/SQL procedure successfully completed.
   

复制代码

1. begin
   
2. dbms_stats.gather_schema_stats('HR');
   
3. end;
   
4. 
   
5. select  * from dict where table_name like 'DBA_%PREF%';
   
6. 
   
7. select  * from DBA_TAB_STAT_PREFS;
   
8. 
   
9. select   dbms_stats.get_prefs('method_opt')  from dual;
   

复制代码

1. select  * from v$fixed_table
   
2. where name like 'V$%STAT';
   
3. 
   
4. select  * from v$fixed_table
   
5. where name like 'V$%CLASS%';
   
6. 
   
7. select   wait_class  from V$SYSTEM_WAIT_CLASS;

复制代码

1. select   s.sid, s.SQL_HASH_VALUE, s.SQL_ID
   
2.   from v$session s
   
3.   where s.sid  in ( 40, 272 );

复制代码