segment advisor

admin

1.段指导需要的权限
对数据库进行空间分析,主要是看表和索引的碎片回收率，建议使用oracle的段指导，如果不是sysdba，则需要授权，或者直接使用sys用户

1. grant advisor to zhyu;

复制代码

2.自动段指导是否开启
自动维护任务使用默认的窗口和调度，如果开启，可以直接查询分析过的任务

1. select client_name,window_group ,status from dba_autotask_client;
   
2. 
   
3. CLIENT_NAME                               WINDOW_GROUP            STATUS
   
4. ----------------------                   ------------------ ------------------------------ --------
   
5. auto optimizer stats collection      ORA$AT_WGRP_OS         ENABLED
   
6. auto space advisor                         ORA$AT_WGRP_SA         ENABLED
   
7. sql tuning advisor                          ORA$AT_WGRP_SQ         ENABLED
   

复制代码

指导任务可以针对以下类型进行操作

1. select * from dba_advisor_definitions;
   
2. 
   
3. ADVISOR_ID ADVISOR_NAME                     PROPERTY
   
4. ---------- ------------------------------ ----------
   
5.          1 ADDM                                    1
   
6.          2 SQL Access Advisor                         303
   
7.          3 Undo Advisor                            1
   
8.          4 SQL Tuning Advisor                         935
   
9.          5 Segment Advisor                          67
   
10.          6 SQL Workload Manager                    0
    
11.          7 Tune MView                                  31
    
12.          8 SQL Performance Analyzer                 935
    
13.          9 SQL Repair Advisor                         679
    
14.         10 Compression Advisor                           3
    
15.         11 SPM Evolve Advisor                         935
    
16.         12 Statistics Advisor                         935
    

复制代码

3.手动运行段指导
对单表或者多表做段分析
如果是多个表，则相应添加dbms_advisor.create_object行修改对应得表名就可以了。

1. DECLARE
   
2. 
   
3. taskname varchar2(100);
   
4. taskdesc varchar2(128);
   
5. task_id number;
   
6. object_id number;
   
7. timeLimit varchar2(25);
   
8. numDaysToRetain varchar2(25);
   
9. objectName varchar2(100);
   
10. objectType varchar2(100);
    
11. 
    
12. BEGIN
    
13. taskname := 'SEGMENTADV_01';
    
14. taskdesc :='SEGMENTADV';
    
15. numDaysToRetain :='30';
    
16. dbms_advisor.create_task('Segment Advisor',taskname,taskdesc,NULL);
    
17. dbms_advisor.create_object( task_name => taskname, object_type => 'TABLE', attr1 => 'ZHYU', attr2 => 'OBJS', attr3 => ' ', attr4 => NULL, attr5 => NULL,object_id => object_id);
    
18. dbms_advisor.set_task_parameter(taskname, 'RECOMMEND_ALL', 'TRUE');
    
19. dbms_advisor.set_task_parameter(taskname, 'DAYS_TO_EXPIRE', numDaysToRetain);
    
20. END;
    
21. 
    
22. DECLARE
    
23. taskname varchar2(100);
    
24. BEGIN
    
25. taskname := 'SEGMENTADV_01';
    
26. dbms_advisor.reset_task(taskname);
    
27. dbms_advisor.execute_task(taskname);
    
28. END;

复制代码

对表空间进行段分析
如果是多个表空间，则相应添加dbms_advisor.create_object行修改对应得表空间名就可以了。

1. DECLARE
   
2.     taskname varchar2(100);
   
3.     taskdesc varchar2(128);
   
4.     task_id number;
   
5.     object_id number;
   
6.     timeLimit varchar2(25);
   
7.     numDaysToRetain varchar2(25);
   
8.     objectName varchar2(100);
   
9.     objectType varchar2(100);
   
10. 
    
11. BEGIN
    
12.     taskname := 'SEGMENTADV02';
    
13.     taskdesc := 'SEGMENTADV';
    
14.     numDaysToRetain :='30';
    
15.     dbms_advisor.create_task('Segment Advisor',taskname,taskdesc,NULL);
    
16.     dbms_advisor.create_object( task_name => taskname, object_type => 'TABLESPACE', attr1 => 'TBS_PIS_DATA01', attr2 => 'NULL', attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => object_id);
    
17.     dbms_advisor.set_task_parameter(taskname, 'RECOMMEND_ALL', 'TRUE');
    
18.     dbms_advisor.execute_task(taskname);
    
19. END;
    
20. 
    

复制代码

4.查询运行状态

1. set lines 200
   
2. col DESCRIPTION for a50
   
3. SELECT TASK_NAME,
   
4.         DESCRIPTION,
   
5.         STATUS,
   
6.         EXECUTION_START,
   
7.         EXECUTION_START
   
8. FROM DBA_ADVISOR_TASKS
   
9. WHERE ADVISOR_NAME = 'Segment Advisor'
   
10. ORDER BY 4 DESC;

复制代码

5.查询任务结果
dba_advisor_xxx 视图保存了结果;dba_advisor_tasks可以查看task的状态，确定其是否完成;dba_advisor_findings
一个更好的办法是使用
DBMS_SPACE.ASA_RECOMMENDATIONS

1. SELECT
   
2.         'Segment Advice --------------------------'
   
3.                 || CHR(10)
   
4.                 || 'TABLESPACE_NAME  : '
   
5.                 || tablespace_name
   
6.                 || CHR(10)
   
7.                 || 'SEGMENT_OWNER    : '
   
8.                 || segment_owner
   
9.                 || CHR(10)
   
10.                 || 'SEGMENT_NAME     : '
    
11.                 || segment_name
    
12.                 || CHR(10)
    
13.                 || 'ALLOCATED_SPACE  : '
    
14.                 || allocated_space
    
15.                 || CHR(10)
    
16.                 || 'RECLAIMABLE_SPACE: '
    
17.                 || reclaimable_space
    
18.                 || CHR(10)
    
19.                 || 'RECOMMENDATIONS  : '
    
20.                 || recommendations
    
21.                 || CHR(10)
    
22.                 || 'SOLUTION 1       : '
    
23.                 || c1
    
24.                 || CHR(10)
    
25.                 || 'SOLUTION 2       : '
    
26.                 || c2
    
27.                 || CHR(10)
    
28.                 || 'SOLUTION 3       : '
    
29.                 || c3 Advice
    
30. FROM
    
31.         TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));

复制代码

1. SET TERMOUT     OFF
   
2. SET ECHO        OFF
   
3. SET FEEDBACK    OFF
   
4. SET HEADING     ON
   
5. SET LINESIZE    200
   
6. SET PAGESIZE    3000
   
7. SET TIMING      OFF
   
8. SET TRIMSPOOL   ON
   
9. SET VERIFY      OFF
   
10. CLEAR COLUMNS BREAKS COMPUTES
    
11. CLEAR COLUMNS BREAKS COMPUTES
    
12. 
    
13. col tablespace_name format a30 heading 'ts_name'
    
14. col segment_owner format a30 heading 'seg_owner'
    
15. col segment_name format a30 heading 'seg_name'
    
16. col allocated_space for 999,999,999,999 heading 'allo_mb'
    
17. col reclaimable_space for 999,999,999,999 heading 'used_mb'
    
18. col reclaimable_space for 999,999,999,999 heading 'recl_mb'
    
19. 
    
20. BREAK ON report
    
21. COMPUTE sum OF allo_mb  ON report
    
22. COMPUTE sum OF recl_mb  ON report
    
23. 
    
24. SELECT
    
25. tablespace_name
    
26. ,segment_owner
    
27. ,segment_name
    
28. ,setment_type
    
29. ,round(allocated_space/1024/1024,0) allo_mb
    
30. ,roud(used_space/1024/1024,0) used_mb
    
31. ,round(reclaimable_space/1024/1024,0) recl_mb
    
32. FROM
    
33. TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));

复制代码

1. set markup html on spool on
   
2. SPOOL ADVISOR.HTML
   
3. set pagesize 200
   
4. set echo on
   
5. 
   
6. select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
   
7. from dba_advisor_findings af, dba_advisor_objects ao
   
8. where ao.task_id = af.task_id
   
9. and ao.object_id = af.object_id;
   
10.   
    
11. select tablespace_name, segment_name, segment_type, partition_name,
    
12. recommendations, c1 from
    
13. table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
    
14. 
    
15. spool off
    
16. set markup html off spool off

复制代码

7.执行段指导时，会不停重复这两个sql_id

1. SQL> select sid,serial#,sql_id,program,event,blocking_session from v$session where sid=2653;
   
2.       2653        43327 7wgks43wrjtrz sqlplus@p0-cnpic-db1 (TNS V1-V3)                     db file sequential read
   
3. 
   
4. Elapsed: 00:00:00.00
   
5. SQL> /
   
6.       2653        43327 8szmwam7fysa3 sqlplus@p0-cnpic-db1 (TNS V1-V3)                     gc cr request
   
7. 
   
8. SQL> select sql_fulltext from v$sql where sql_id='&sql_id';
   
9. Enter value for sql_id: 8szmwam7fysa3
   
10. old   1: select sql_fulltext from v$sql where sql_id='&sql_id'
    
11. new   1: select sql_fulltext from v$sql where sql_id='8szmwam7fysa3'
    
12. insert into wri$_adv_objspace_trend_data select timepoint,  space_usage, space_a
    
13. lloc, quality from  table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, N
    
14. ULL,  NULL, 'FALSE', :5, 'FALSE'))
    
15. 
    
16. SQL> select sql_fulltext from v$sql where sql_id='&sql_id';
    
17. Enter value for sql_id: 7wgks43wrjtrz
    
18. old   1: select sql_fulltext from v$sql where sql_id='&sql_id'
    
19. new   1: select sql_fulltext from v$sql where sql_id='7wgks43wrjtrz'
    
20. SELECT U.SPACE_USED, U.SPACE_ALLOCATED FROM TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_
    
21. TBF( :B1 , :B2 , :B3 , 0, :B4 , 'TRUE', :B5 )) U

复制代码

admin

1. #!/bin/bash
   
2. 
   
3. # 查询所有非系统表空间
   
4. TABLESPACES=$(sqlplus -s / as sysdba<<EOF
   
5. set heading off
   
6. set feedback off
   
7. set pages 2000
   
8. select tablespace_name from dba_tablespaces where contents='PERMANENT' and tablespace_name not in ('SYSTEM', 'SYSAUX');
   
9. exit
   
10. EOF
    
11. )
    
12. 
    
13. # 循环处理每个表空间
    
14. for TABLESPACE in $TABLESPACES; do
    
15.     # 创建任务名称和描述
    
16.     TASK_NAME="SEGMENTADV_${TABLESPACE}"
    
17.     TASK_DESC="SEGMENTADV for tablespace ${TABLESPACE}"
    
18. 
    
19.     # 创建任务
    
20.     echo "$(date +'%Y%m%d %H:%M:%S')"
    
21.     echo "Creating task: $TASK_NAME"
    
22.     sqlplus -s / as sysdba<<EOF
    
23.     set heading off
    
24.     set feedback off
    
25.     DECLARE
    
26.         task_id number;
    
27.     BEGIN
    
28.         dbms_advisor.create_task('Segment Advisor', '$TASK_NAME', '$TASK_DESC', NULL);
    
29.         dbms_advisor.create_object( task_name => '$TASK_NAME', object_type => 'TABLESPACE', attr1 => '$TABLESPACE', attr2 => NULL, attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => NULL);
    
30.         dbms_advisor.set_task_parameter('$TASK_NAME', 'RECOMMEND_ALL', 'TRUE');
    
31.         dbms_advisor.execute_task('$TASK_NAME');
    
32.     END;
    
33.     /
    
34.     exit
    
35. EOF
    
36.     echo "Completed task: $TASK_NAME"  
    
37. done
    
38. 
    

复制代码