Bo's Oracle Station

查看: 2968|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-9-1 09:42:20 | 显示全部楼层 |阅读模式
autotrace脚本所在的位置:
  1. /u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin
复制代码
  1. [oracle@station90 admin]$ sqlplus /nolog

  2. SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 1 09:42:10 2018

  3. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> @plustrce.sql
  7. SQL>
  8. SQL> drop role plustrace;
  9. drop role plustrace
  10.           *
  11. ERROR at line 1:
  12. ORA-01919: role 'PLUSTRACE' does not exist


  13. SQL> create role plustrace;

  14. Role created.

  15. SQL>
  16. SQL> grant select on v_$sesstat to plustrace;

  17. Grant succeeded.

  18. SQL> grant select on v_$statname to plustrace;

  19. Grant succeeded.

  20. SQL> grant select on v_$mystat to plustrace;

  21. Grant succeeded.

  22. SQL> grant plustrace to dba with admin option;

  23. Grant succeeded.

  24. SQL>
  25. SQL> set echo off
  26. SQL> grant plustrace to hr;

  27. Grant succeeded.

  28. 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.png


2.png

  1. select  * from dba_outstanding_alerts;
  2.   
  3. ---WR_[        DISCUZ_CODE_7        ]nbsp;   I M R H   SYSAUX SYS

  4. select  * from dba_views v
  5.   where v.VIEW_NAME='DBA_OUTSTANDING_ALERTS';
  6.   
  7. select  * from dba_views v
  8. where v.VIEW_NAME= 'INT$DBA_OUTSTANDING_ALERTS';

  9. ----WRI$_ALERT_OUTSTANDING

  10. select  * from dba_tables t where t.table_name='WRI$_ALERT_OUTSTANDING';
复制代码
   OWNERTABLE_NAMETABLESPACE_NAMECLUSTER_NAMEIOT_NAMESTATUSPCT_FREEPCT_USEDINI_TRANSMAX_TRANSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASEFREELISTSFREELIST_GROUPSLOGGINGBACKED_UPNUM_ROWSBLOCKSEMPTY_BLOCKSAVG_SPACECHAIN_CNTAVG_ROW_LENAVG_SPACE_FREELIST_BLOCKSNUM_FREELIST_BLOCKSDEGREEINSTANCESCACHETABLE_LOCKSAMPLE_SIZELAST_ANALYZEDPARTITIONEDIOT_TYPETEMPORARYSECONDARYNESTEDBUFFER_POOLFLASH_CACHECELL_FLASH_CACHEROW_MOVEMENTGLOBAL_STATSUSER_STATSDURATIONSKIP_CORRUPTMONITORINGCLUSTER_OWNERDEPENDENCIESCOMPRESSIONCOMPRESS_FORDROPPEDREAD_ONLYSEGMENT_CREATEDRESULT_CACHECLUSTERINGACTIVITY_TRACKINGDML_TIMESTAMPHAS_IDENTITYCONTAINER_DATAINMEMORYINMEMORY_PRIORITYINMEMORY_DISTRIBUTEINMEMORY_COMPRESSIONINMEMORY_DUPLICATE
1SYSWRI$_ALERT_OUTSTANDINGSYSAUX VALID10 125565536104857612147483645 YESN00000000         1         1    NENABLED01/20/2018 5:53:03 PMNO NNNODEFAULTDEFAULTDEFAULTDISABLEDYESNO DISABLEDYES DISABLEDDISABLED NONOYESDEFAULTNO NONODISABLED

  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. ----WRI$_ALERT_HISTORY
  10. select  * from dba_tables t where t.table_name='WRI$_ALERT_HISTORY';

  11. select  substr(t.table_name,3,1), count(*)  
  12. from dba_tables t
  13. where t.owner='SYS' and t.tablespace_name='SYSAUX' and
  14.    t.table_name  like 'WR_$%'
  15.   group by substr(t.table_name,3,1);

  16. select  t.table_name  from dba_tables t
  17. where t.owner='SYS' and t.tablespace_name='SYSAUX' and
  18.    t.table_name  like 'WRH$%';

  19. select  * from WRH$_MEMORY_RESIZE_OPS;

  20. select  * from v$MEMORY_RESIZE_OPS;
复制代码
  1. begin
  2.    dbms_stats.gather_table_stats('HR','T04209_UNAME');
  3. end;

  4. select  t.num_rows  ,t.last_analyzed   from dba_tables t
  5.   where t.owner='HR' and t.table_name='T04209_UNAME';
  6.   
  7. select  * from dba_tab_col_statistics tcs
  8.   where tcs.owner='HR' and tcs.table_name='T04209_UNAME';
  9.   
  10. begin
  11.    dbms_stats.gather_table_stats('HR','T04209_UNAME',
  12.    estimate_percent => 100,
  13.    method_opt => 'for all columns size auto for columns uvalue size 101'
  14.       );
  15. end;

  16.   select  * from dba_tab_col_statistics tcs
  17.   where tcs.owner='HR' and tcs.table_name='T04209_UNAME';
  18.   
  19.   select  * from dba_histograms h
  20.    where h.owner='HR' and h.table_name='T04209_UNAME'
  21.     and h.column_name='UVALUE';
复制代码
  1. select dbms_stats.get_prefs (pname => 'STALE_PERCENT') from dual;

  2. begin
  3. dbms_stats.set_table_prefs('HR','T04209_UNAME','STALE_PERCENT','13');
  4. end;

  5. select dbms_stats.get_prefs (pname => 'STALE_PERCENT',
  6. ownname => 'HR' , tabname => 'T04209_UNAME') from dual;
复制代码

KEEP:

  1. create tablespace tbs16k datafile size 5M blocksize 16384;

  2. alter system set db_16k_cache_size=10M;

  3. alter system set db_keep_cache_size=10M;

  4. select  t.buffer_pool from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';

  5. alter table hr.employees storage ( buffer_pool keep);

  6. begin
  7.    dbms_shared_pool.keep('HR.SECURE_DML');
  8. end;

  9. select  c.NAME, c.KEPT  from v_$db_object_cache   c  
  10.    where c.OWNER='HR';
复制代码

cursor_sharing=similar:
4.png


---------------------------------
发布与否优化器统计信息:
  1. select  s.SQL_ID, s.SQL_HASH_VALUE   from v_$session s
  2. where terminal='pts/10';

  3. select  sql_text from v$sql where sql_id='dh73w3ss300hp';

  4. begin
  5.     dbms_stats.set_table_prefs('HR','EMPLOYEES',pname => 'PUBLISH',pvalue => 'FALSE');
  6.   end;  
  7.   
  8.   select  * from dba_tab_pending_stats;

  9. begin
  10.     dbms_stats.gather_table_stats('HR','EMPLOYEES');
  11.   end;

  12. begin
  13.     dbms_stats.publish_pending_stats(ownname => 'HR',tabname => 'EMPLOYEES');
  14.   end;
复制代码
看懂AWR的前提是看懂以下两个视图:
  1. select  * from v$system_wait_class;

  2. select  * from v$event_name;
复制代码





3.png
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 17:52 , Processed in 0.045114 second(s), 27 queries .

快速回复 返回顶部 返回列表