活动第49/50次（2018-09-01星期六上下午）

botang

autotrace脚本所在的位置：

1. /u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin

复制代码

1. [oracle@station90 admin]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 1 09:42:10 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> @plustrce.sql
   
10. SQL>
    
11. SQL> drop role plustrace;
    
12. drop role plustrace
    
13.           *
    
14. ERROR at line 1:
    
15. ORA-01919: role 'PLUSTRACE' does not exist
    
16. 
    
17. 
    
18. SQL> create role plustrace;
    
19. 
    
20. Role created.
    
21. 
    
22. SQL>
    
23. SQL> grant select on v_$sesstat to plustrace;
    
24. 
    
25. Grant succeeded.
    
26. 
    
27. SQL> grant select on v_$statname to plustrace;
    
28. 
    
29. Grant succeeded.
    
30. 
    
31. SQL> grant select on v_$mystat to plustrace;
    
32. 
    
33. Grant succeeded.
    
34. 
    
35. SQL> grant plustrace to dba with admin option;
    
36. 
    
37. Grant succeeded.
    
38. 
    
39. SQL>
    
40. SQL> set echo off
    
41. SQL> grant plustrace to hr;
    
42. 
    
43. Grant succeeded.
    
44. 
    
45. SQL>
    

复制代码

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. SQL> set autot  traceonly  explain
   
4. SQL> set autot  traceonly  statistics
   
5. SQL> set autot  on
   
6. SQL> set autot  traceonly
   
7. SQL>
   

复制代码

1. select  * from dba_outstanding_alerts;
   
2.   
   
3. ---WR_[        DISCUZ_CODE_7        ]nbsp;   I M R H   SYSAUX SYS
   
4. 
   
5. select  * from dba_views v
   
6.   where v.VIEW_NAME='DBA_OUTSTANDING_ALERTS';
   
7.   
   
8. select  * from dba_views v
   
9. where v.VIEW_NAME= 'INT$DBA_OUTSTANDING_ALERTS';
   
10. 
    
11. ----WRI$_ALERT_OUTSTANDING
    
12. 
    
13. select  * from dba_tables t where t.table_name='WRI$_ALERT_OUTSTANDING';

复制代码

OWNER

TABLE_NAME

TABLESPACE_NAME

CLUSTER_NAME

IOT_NAME

STATUS

PCT_FREE

PCT_USED

INI_TRANS

MAX_TRANS

INITIAL_EXTENT

NEXT_EXTENT

MIN_EXTENTS

MAX_EXTENTS

PCT_INCREASE

FREELISTS

FREELIST_GROUPS

LOGGING

BACKED_UP

NUM_ROWS

BLOCKS

EMPTY_BLOCKS

AVG_SPACE

CHAIN_CNT

AVG_ROW_LEN

AVG_SPACE_FREELIST_BLOCKS

NUM_FREELIST_BLOCKS

DEGREE

INSTANCES

CACHE

TABLE_LOCK

SAMPLE_SIZE

LAST_ANALYZED

PARTITIONED

IOT_TYPE

TEMPORARY

SECONDARY

NESTED

BUFFER_POOL

FLASH_CACHE

CELL_FLASH_CACHE

ROW_MOVEMENT

GLOBAL_STATS

USER_STATS

DURATION

SKIP_CORRUPT

MONITORING

CLUSTER_OWNER

DEPENDENCIES

COMPRESSION

COMPRESS_FOR

DROPPED

READ_ONLY

SEGMENT_CREATED

RESULT_CACHE

CLUSTERING

ACTIVITY_TRACKING

DML_TIMESTAMP

HAS_IDENTITY

CONTAINER_DATA

INMEMORY

INMEMORY_PRIORITY

INMEMORY_DISTRIBUTE

INMEMORY_COMPRESSION

INMEMORY_DUPLICATE

1

SYS

WRI$_ALERT_OUTSTANDING

SYSAUX

VALID

10

1

255

65536

1048576

1

2147483645

YES

N

0

0

0

0

0

0

0

0

1

1

N

ENABLED

0

1/20/2018 5:53:03 PM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

1. select  * from dba_alert_history  ah
   
2.   order by ah.TIME_SUGGESTED desc;
   
3.   
   
4. select  * from dba_views v
   
5.   where v.VIEW_NAME='DBA_ALERT_HISTORY';
   
6.   
   
7. select  * from dba_views v
   
8. where v.VIEW_NAME= 'INT$DBA_ALERT_HISTORY';   
   
9. 
   
10. ----WRI$_ALERT_HISTORY
    
11. select  * from dba_tables t where t.table_name='WRI$_ALERT_HISTORY';
    
12. 
    
13. select  substr(t.table_name,3,1), count(*)  
    
14. from dba_tables t
    
15. where t.owner='SYS' and t.tablespace_name='SYSAUX' and
    
16.    t.table_name  like 'WR_$%'
    
17.   group by substr(t.table_name,3,1);
    
18. 
    
19. select  t.table_name  from dba_tables t
    
20. where t.owner='SYS' and t.tablespace_name='SYSAUX' and
    
21.    t.table_name  like 'WRH$%';
    
22. 
    
23. select  * from WRH$_MEMORY_RESIZE_OPS;
    
24. 
    
25. select  * from v$MEMORY_RESIZE_OPS;
    

复制代码

1. begin
   
2.    dbms_stats.gather_table_stats('HR','T04209_UNAME');
   
3. end;
   
4. 
   
5. select  t.num_rows  ,t.last_analyzed   from dba_tables t
   
6.   where t.owner='HR' and t.table_name='T04209_UNAME';
   
7.   
   
8. select  * from dba_tab_col_statistics tcs
   
9.   where tcs.owner='HR' and tcs.table_name='T04209_UNAME';
   
10.   
    
11. begin
    
12.    dbms_stats.gather_table_stats('HR','T04209_UNAME',
    
13.    estimate_percent => 100,
    
14.    method_opt => 'for all columns size auto for columns uvalue size 101'
    
15.       );
    
16. end;
    
17. 
    
18.   select  * from dba_tab_col_statistics tcs
    
19.   where tcs.owner='HR' and tcs.table_name='T04209_UNAME';
    
20.   
    
21.   select  * from dba_histograms h
    
22.    where h.owner='HR' and h.table_name='T04209_UNAME'
    
23.     and h.column_name='UVALUE';

复制代码

1. select dbms_stats.get_prefs (pname => 'STALE_PERCENT') from dual;
   
2. 
   
3. begin
   
4. dbms_stats.set_table_prefs('HR','T04209_UNAME','STALE_PERCENT','13');
   
5. end;
   
6. 
   
7. select dbms_stats.get_prefs (pname => 'STALE_PERCENT',
   
8. ownname => 'HR' , tabname => 'T04209_UNAME') from dual;

复制代码

KEEP:

1. create tablespace tbs16k datafile size 5M blocksize 16384;
   
2. 
   
3. alter system set db_16k_cache_size=10M;
   
4. 
   
5. alter system set db_keep_cache_size=10M;
   
6. 
   
7. select  t.buffer_pool from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';
   
8. 
   
9. alter table hr.employees storage ( buffer_pool keep);
   
10. 
    
11. begin
    
12.    dbms_shared_pool.keep('HR.SECURE_DML');
    
13. end;
    
14. 
    
15. select  c.NAME, c.KEPT  from v_$db_object_cache   c  
    
16.    where c.OWNER='HR';

复制代码

cursor_sharing=similar:



---------------------------------
发布与否优化器统计信息：

1. select  s.SQL_ID, s.SQL_HASH_VALUE   from v_$session s
   
2. where terminal='pts/10';
   
3. 
   
4. select  sql_text from v$sql where sql_id='dh73w3ss300hp';
   
5. 
   
6. begin
   
7.     dbms_stats.set_table_prefs('HR','EMPLOYEES',pname => 'PUBLISH',pvalue => 'FALSE');
   
8.   end;  
   
9.   
   
10.   select  * from dba_tab_pending_stats;
    
11. 
    
12. begin
    
13.     dbms_stats.gather_table_stats('HR','EMPLOYEES');
    
14.   end;
    
15. 
    
16. begin
    
17.     dbms_stats.publish_pending_stats(ownname => 'HR',tabname => 'EMPLOYEES');
    
18.   end;

复制代码

看懂AWR的前提是看懂以下两个视图：

1. select  * from v$system_wait_class;
   
2. 
   
3. select  * from v$event_name;

复制代码