活动第49/50次（2018-09-01星期六上下午）
autotrace脚本所在的位置：/u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 1 09:42:10 2018
Copyright (c) 1982, 2014, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL> grant plustrace to hr;
Grant succeeded.
SQL>
SQL> conn hr/oracle_4U
Connected.
SQL> set autottraceonlyexplain
SQL> set autottraceonlystatistics
SQL> set autoton
SQL> set autottraceonly
SQL>
select* from dba_outstanding_alerts;
---WR_[ DISCUZ_CODE_7 ]nbsp; I M R H SYSAUX SYS
select* from dba_views v
where v.VIEW_NAME='DBA_OUTSTANDING_ALERTS';
select* from dba_views v
where v.VIEW_NAME= 'INT$DBA_OUTSTANDING_ALERTS';
----WRI$_ALERT_OUTSTANDING
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
select* from dba_alert_historyah
order by ah.TIME_SUGGESTED desc;
select* from dba_views v
where v.VIEW_NAME='DBA_ALERT_HISTORY';
select* from dba_views v
where v.VIEW_NAME= 'INT$DBA_ALERT_HISTORY';
----WRI$_ALERT_HISTORY
select* from dba_tables t where t.table_name='WRI$_ALERT_HISTORY';
selectsubstr(t.table_name,3,1), count(*)
from dba_tables t
where t.owner='SYS' and t.tablespace_name='SYSAUX' and
t.table_namelike 'WR_$%'
group by substr(t.table_name,3,1);
selectt.table_namefrom dba_tables t
where t.owner='SYS' and t.tablespace_name='SYSAUX' and
t.table_namelike 'WRH$%';
select* from WRH$_MEMORY_RESIZE_OPS;
select* from v$MEMORY_RESIZE_OPS;
begin
dbms_stats.gather_table_stats('HR','T04209_UNAME');
end;
selectt.num_rows,t.last_analyzed from dba_tables t
where t.owner='HR' and t.table_name='T04209_UNAME';
select* from dba_tab_col_statistics tcs
where tcs.owner='HR' and tcs.table_name='T04209_UNAME';
begin
dbms_stats.gather_table_stats('HR','T04209_UNAME',
estimate_percent => 100,
method_opt => 'for all columns size auto for columns uvalue size 101'
);
end;
select* from dba_tab_col_statistics tcs
where tcs.owner='HR' and tcs.table_name='T04209_UNAME';
select* from dba_histograms h
where h.owner='HR' and h.table_name='T04209_UNAME'
and h.column_name='UVALUE';
select dbms_stats.get_prefs (pname => 'STALE_PERCENT') from dual;
begin
dbms_stats.set_table_prefs('HR','T04209_UNAME','STALE_PERCENT','13');
end;
select dbms_stats.get_prefs (pname => 'STALE_PERCENT',
ownname => 'HR' , tabname => 'T04209_UNAME') from dual;
KEEP:
create tablespace tbs16k datafile size 5M blocksize 16384;
alter system set db_16k_cache_size=10M;
alter system set db_keep_cache_size=10M;
selectt.buffer_pool from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';
alter table hr.employees storage ( buffer_pool keep);
begin
dbms_shared_pool.keep('HR.SECURE_DML');
end;
selectc.NAME, c.KEPTfrom v_$db_object_cache c
where c.OWNER='HR';
cursor_sharing=similar:
---------------------------------
发布与否优化器统计信息：
selects.SQL_ID, s.SQL_HASH_VALUE from v_$session s
where terminal='pts/10';
selectsql_text from v$sql where sql_id='dh73w3ss300hp';
begin
dbms_stats.set_table_prefs('HR','EMPLOYEES',pname => 'PUBLISH',pvalue => 'FALSE');
end;
select* from dba_tab_pending_stats;
begin
dbms_stats.gather_table_stats('HR','EMPLOYEES');
end;
begin
dbms_stats.publish_pending_stats(ownname => 'HR',tabname => 'EMPLOYEES');
end;看懂AWR的前提是看懂以下两个视图：
select* from v$system_wait_class;
select* from v$event_name;
页:
[1]