课程第57次：2016-07-24星期日下午

botang · 发表于 2016-7-23 22:15:08

本帖最后由 botang 于 2016-7-24 18:56 编辑

课程第57次：2016-07-24星期日下午

【上完1Z0-052的第13章】内存
【上完1Z0-053的第13章】内存
【上完1Z0-053的第14章】调优
【上完1Z0-053的第15章】SQL调优
【上完1Z0-051】：共12章（0 1 2 3 4 5 6 7 8 9 10 11）
【上完1Z0-052】：共19章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18）
【1Z0-053】：共15章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20）
表示已经上过的，表示还没上的。

参考别期的帖子：
https://www.botangdb.com/forum.php?mod=viewthread&tid=266&extra=page%3D1

amm.sql：

select * from v$memory_dynamic_components;
select t.buffer_pool from dba_tables t where t.table_name='T04209_UNAME';
alter table hr.t04209_uname storage ( buffer_pool keep ) ;
alter system set db_keep_cache_size=1M;
select * from v$memory_resize_ops;
alter index hr.i04209_uname storage ( buffer_pool keep ) ;
select * from dba_objects o where o.object_type='PROCEDURE' and
o.owner='HR';
select * from v$db_object_cache
where owner='HR' and name='PROCREMOTEDB_WIN' ;
begin
dbms_shared_pool.keep(name => 'HR.PROCREMOTEDB_WIN');
end;
-----
select s.SQL_HASH_VALUE , s.SQL_ID from v_$session s
where terminal='pts/1';
-----
select * from v$sqlarea where sql_id='dh73w3ss300hp';

复制代码

optimizerstat.sql：

select * from dba_tab_stat_prefs t where t.owner='SH'
and t.table_name='SALES';
select t.num_rows 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';
select i.num_rows
from dba_indexes i where i.owner='HR' and i.table_name='T04209_UNAME';
begin
dbms_stats.delete_table_stats (ownname => 'HR' , tabname => 'T04209_UNAME'
) ;
end;
---
begin
dbms_stats.set_table_prefs('HR','T04209_UNAME','PUBLISH','FALSE');
end;
begin
dbms_Stats.gather_table_stats('HR','T04209_UNAME',
method_opt =>'for columns uvalue size 254 for all columns size auto');
end;
select * from dba_tab_pending_stats;
begin
dbms_Stats.publish_pending_stats('HR','T04209_UNAME');
end;
---

复制代码

performance.sql：

select * from
(select * from v_$sql s order by s.CPU_TIME)
where rownum <=6;
select n.name , se.VALUE
from v_$session s , v_$sesstat se , v_$statname n
where s.SID=se.sid
and se.STATISTIC#=n.STATISTIC#
and s.TERMINAL='pts/14'
and n.NAME='redo size';

复制代码

saa.sql：

select * from dba_tables t
where t.owner='SYS' and t.tablespace_name='SYSAUX'
and t.table_name like 'WR%';
select substr(t.table_name,3,1),count(*) from dba_tables t where t.tablespace_name='SYSAUX'
and t.table_name like 'WR_$\_%' escape '\'
group by substr(t.table_name,3,1);
select * from dba_Tables where table_name like 'WR%SNAP%';
select * from WRM$_SNAPSHOT;
begin
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
start_snap_id =>24,
end_snap_id =>25,
baseline_name =>'BASELINE1');
end;
begin
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
start_snap_id =>25,
end_snap_id =>26,
baseline_name =>'SNAPSHOTSET25_26');
end;
select * from dba_sql_profiles;
select * from dba_ind_columns i where
i.table_name='T04209_UNAME';
CREATE INDEX "HR"."I04209_UNAME"
ON "HR"."T04209_UNAME"
("UNAME")
COMPUTE STATISTICS;
DROP INDEX "HR"."I04209_UNAME";
-------------------------------------------------
begin
dbms_stats.gather_table_stats('HR','TBIG');
end;
begin
dbms_advisor.quick_tune(advisor_name => 'SQL Access Advisor',
task_name =>'MYTASK1',
attr1 => 'select name from hr.tbig where type=''PACKAGE'' ',
template => 'SQLACCESS_OLTP');
end;
begin
dbms_advisor.quick_tune(advisor_name => 'SQL Access Advisor',
task_name =>'MYTASK2',
attr1 => 'select name from hr.tbig where type=''PACKAGE'' ',
template => 'SQLACCESS_WAREHOUSE');
end;

复制代码