2016-09-10/11星期六/日：第9/10/11/12次课

botang

手工周期捕获 STS：

1. BEGIN
   
2.   dbms_sqltune.create_sqlset(sqlset_name => 'XXX', sqlset_owner =>'SYSTEM');
   
3. END;
   
4. /
   
5. DECLARE
   
6. bf VARCHAR2(110);
   
7. BEGIN
   
8. bf := q'#UPPER(PARSING_SCHEMA_NAME) = 'APPS' AND UPPER(SQL_TEXT) NOT  LIKE 'APPLICAT%' AND UPPER(MODULE) = 'DWH_TEST%' #';
   
9. dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name=>'XXX', time_limit=>'360',
   
10.                                            repeat_interval=>'5',
    
11.                                            basic_filter=>bf,
    
12.                                            sqlset_owner=>'SYSTEM');
    
13. END;
    
14. /

复制代码

练习查看执行计划步骤顺序：

1. explain plan for
   
2. SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
   
3. FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
   
4. WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
   
5. AND c.cust_state_province = 'FL'
   
6. AND ch.channel_desc = 'Direct Sales'
   
7. AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
   
8. t.calendar_quarter_desc;

复制代码

记录重演元数据的15个AWR底表：

1. select  substr(t.table_name,3,1),count(*)    from dba_tables t where  t.tablespace_name='SYSAUX'
   
2.                              and t.table_name  like 'WR_$\_%' escape '\'
   
3.                              group by substr(t.table_name,3,1);
   

复制代码