SQL Tuning和快速调优工具，1Z0-053第15章，课程第62/63次（2017-03-11星期日上下午）

botang · 发表于 2017-3-13 11:19:07

上完1Z0-053第15章（SQL Tuning）
1Z0-052共19章（上完19章），1Z0-053共21章（上完19章），1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的38章

2017-03-12.sql：

select t.NUM_ROWS , t.LAST_ANALYZED
from dba_Tables t
where t.TABLE_NAME='T_BIG' and
t.OWNER='HR';
begin
dbms_stats.delete_table_stats('HR','T_BIG');
end;
begin
dbms_advisor.quick_tune(advisor_name=>'SQL Access Advisor',
task_name=>'Mytask1',
attr1=>'select sum(line) from hr.t_big group by text',
template=>'SQLACCESS_OLTP');
end;
begin
dbms_Stats.gather_table_stats('HR','T_BIG');
end;
select t.NUM_ROWS , t.LAST_ANALYZED
from dba_Tables t
where t.TABLE_NAME='T_BIG' and
t.OWNER='HR';
begin
dbms_advisor.quick_tune(advisor_name=>'SQL Access Advisor',
task_name=>'Mytask2',
attr1=>'select sum(line) from hr.t_big group by text',
template=>'SQLACCESS_OLTP');
end;
begin
dbms_advisor.quick_tune(advisor_name=>'SQL Access Advisor',
task_name=>'Mytask5',
attr1=>'select sum(line) from hr.t_big group by type',
template=>'SQLACCESS_OLTP');
end;
---
CREATE MATERIALIZED VIEW LOG ON
"HR"."T_BIG"
WITH ROWID, SEQUENCE("LINE","TEXT")
INCLUDING NEW VALUES;
grant create MATERIALIZED VIEW to hr;
grant query rewrite to hr;
ALTER MATERIALIZED VIEW LOG FORCE ON
"HR"."T_BIG"
ADD ROWID, SEQUENCE("TYPE","LINE")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW hr.MV1
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT TYPE C1, SUM(LINE) M1, COUNT(LINE)
M2, COUNT(*) M3 FROM HR.T_BIG GROUP BY TYPE;
begin
dbms_stats.gather_table_stats('HR','MV1');
end;
select * from dba_segments s
where s.SEGMENT_NAME in ('T05318_A', 'I05318_A');
create table t05318_part ( a number )
partition by range(a)
( partition p1 values less than(10),
partition p2 values less than (maxvalue)) ;
select * from dba_segments s
where s.SEGMENT_NAME in ('T05318_PART', 'I05318_PART');
select s.bytes from dba_segments s
where s.SEGMENT_NAME='I05318_B';

复制代码

复制代码

关于1Z0-053第18章的内容：

http://124.16.180.178:8080/studentguide_sec_O11g/1Z0-053-18/

游客，本帖隐藏的内容需要积分高于 100 才可浏览，您当前积分为 0