segment advisor
1.段指导需要的权限对数据库进行空间分析,主要是看表和索引的碎片回收率，建议使用oracle的段指导，如果不是sysdba，则需要授权，或者直接使用sys用户
grant advisor to zhyu;2.自动段指导是否开启
自动维护任务使用默认的窗口和调度，如果开启，可以直接查询分析过的任务
select client_name,window_group ,status from dba_autotask_client;
CLIENT_NAME WINDOW_GROUP STATUS
---------------------- ------------------ ------------------------------ --------
auto optimizer stats collection ORA$AT_WGRP_OS ENABLED
auto space advisor ORA$AT_WGRP_SA ENABLED
sql tuning advisor ORA$AT_WGRP_SQ ENABLED
指导任务可以针对以下类型进行操作
select * from dba_advisor_definitions;
ADVISOR_ID ADVISOR_NAME PROPERTY
---------- ------------------------------ ----------
1 ADDM 1
2 SQL Access Advisor 303
3 Undo Advisor 1
4 SQL Tuning Advisor 935
5 Segment Advisor 67
6 SQL Workload Manager 0
7 Tune MView 31
8 SQL Performance Analyzer 935
9 SQL Repair Advisor 679
10 Compression Advisor 3
11 SPM Evolve Advisor 935
12 Statistics Advisor 935
3.手动运行段指导
对单表或者多表做段分析
如果是多个表，则相应添加dbms_advisor.create_object行修改对应得表名就可以了。
DECLARE
taskname varchar2(100);
taskdesc varchar2(128);
task_id number;
object_id number;
timeLimit varchar2(25);
numDaysToRetain varchar2(25);
objectName varchar2(100);
objectType varchar2(100);
BEGIN
taskname := 'SEGMENTADV_01';
taskdesc :='SEGMENTADV';
numDaysToRetain :='30';
dbms_advisor.create_task('Segment Advisor',taskname,taskdesc,NULL);
dbms_advisor.create_object( task_name => taskname, object_type => 'TABLE', attr1 => 'ZHYU', attr2 => 'OBJS', attr3 => ' ', attr4 => NULL, attr5 => NULL,object_id => object_id);
dbms_advisor.set_task_parameter(taskname, 'RECOMMEND_ALL', 'TRUE');
dbms_advisor.set_task_parameter(taskname, 'DAYS_TO_EXPIRE', numDaysToRetain);
END;
DECLARE
taskname varchar2(100);
BEGIN
taskname := 'SEGMENTADV_01';
dbms_advisor.reset_task(taskname);
dbms_advisor.execute_task(taskname);
END;对表空间进行段分析
如果是多个表空间，则相应添加dbms_advisor.create_object行修改对应得表空间名就可以了。
DECLARE
taskname varchar2(100);
taskdesc varchar2(128);
task_id number;
object_id number;
timeLimit varchar2(25);
numDaysToRetain varchar2(25);
objectName varchar2(100);
objectType varchar2(100);
BEGIN
taskname := 'SEGMENTADV02';
taskdesc := 'SEGMENTADV';
numDaysToRetain :='30';
dbms_advisor.create_task('Segment Advisor',taskname,taskdesc,NULL);
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);
dbms_advisor.set_task_parameter(taskname, 'RECOMMEND_ALL', 'TRUE');
dbms_advisor.execute_task(taskname);
END;
4.查询运行状态
set lines 200
col DESCRIPTION for a50
SELECT TASK_NAME,
DESCRIPTION,
STATUS,
EXECUTION_START,
EXECUTION_START
FROM DBA_ADVISOR_TASKS
WHERE ADVISOR_NAME = 'Segment Advisor'
ORDER BY 4 DESC;5.查询任务结果
dba_advisor_xxx 视图保存了结果;dba_advisor_tasks可以查看task的状态，确定其是否完成;dba_advisor_findings
一个更好的办法是使用
DBMS_SPACE.ASA_RECOMMENDATIONS
SELECT
'Segment Advice --------------------------'
|| CHR(10)
|| 'TABLESPACE_NAME: '
|| tablespace_name
|| CHR(10)
|| 'SEGMENT_OWNER : '
|| segment_owner
|| CHR(10)
|| 'SEGMENT_NAME : '
|| segment_name
|| CHR(10)
|| 'ALLOCATED_SPACE: '
|| allocated_space
|| CHR(10)
|| 'RECLAIMABLE_SPACE: '
|| reclaimable_space
|| CHR(10)
|| 'RECOMMENDATIONS: '
|| recommendations
|| CHR(10)
|| 'SOLUTION 1 : '
|| c1
|| CHR(10)
|| 'SOLUTION 2 : '
|| c2
|| CHR(10)
|| 'SOLUTION 3 : '
|| c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));SET TERMOUT OFF
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 200
SET PAGESIZE 3000
SET TIMING OFF
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES
CLEAR COLUMNS BREAKS COMPUTES
col tablespace_name format a30 heading 'ts_name'
col segment_owner format a30 heading 'seg_owner'
col segment_name format a30 heading 'seg_name'
col allocated_space for 999,999,999,999 heading 'allo_mb'
col reclaimable_space for 999,999,999,999 heading 'used_mb'
col reclaimable_space for 999,999,999,999 heading 'recl_mb'
BREAK ON report
COMPUTE sum OF allo_mbON report
COMPUTE sum OF recl_mbON report
SELECT
tablespace_name
,segment_owner
,segment_name
,setment_type
,round(allocated_space/1024/1024,0) allo_mb
,roud(used_space/1024/1024,0) used_mb
,round(reclaimable_space/1024/1024,0) recl_mb
FROM
TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));set markup html on spool on
SPOOL ADVISOR.HTML
set pagesize 200
set echo on
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
from dba_advisor_findings af, dba_advisor_objects ao
where ao.task_id = af.task_id
and ao.object_id = af.object_id;
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
spool off
set markup html off spool off
7.执行段指导时，会不停重复这两个sql_id
SQL> select sid,serial#,sql_id,program,event,blocking_session from v$session where sid=2653;
2653 43327 7wgks43wrjtrz sqlplus@p0-cnpic-db1 (TNS V1-V3) db file sequential read
Elapsed: 00:00:00.00
SQL> /
2653 43327 8szmwam7fysa3 sqlplus@p0-cnpic-db1 (TNS V1-V3) gc cr request
SQL> select sql_fulltext from v$sql where sql_id='&sql_id';
Enter value for sql_id: 8szmwam7fysa3
old 1: select sql_fulltext from v$sql where sql_id='&sql_id'
new 1: select sql_fulltext from v$sql where sql_id='8szmwam7fysa3'
insert into wri$_adv_objspace_trend_data select timepoint,space_usage, space_a
lloc, quality fromtable(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, N
ULL,NULL, 'FALSE', :5, 'FALSE'))
SQL> select sql_fulltext from v$sql where sql_id='&sql_id';
Enter value for sql_id: 7wgks43wrjtrz
old 1: select sql_fulltext from v$sql where sql_id='&sql_id'
new 1: select sql_fulltext from v$sql where sql_id='7wgks43wrjtrz'
SELECT U.SPACE_USED, U.SPACE_ALLOCATED FROM TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_
TBF( :B1 , :B2 , :B3 , 0, :B4 , 'TRUE', :B5 )) U #!/bin/bash
# 查询所有非系统表空间
TABLESPACES=$(sqlplus -s / as sysdba<<EOF
set heading off
set feedback off
set pages 2000
select tablespace_name from dba_tablespaces where contents='PERMANENT' and tablespace_name not in ('SYSTEM', 'SYSAUX');
exit
EOF
)
# 循环处理每个表空间
for TABLESPACE in $TABLESPACES; do
# 创建任务名称和描述
TASK_NAME="SEGMENTADV_${TABLESPACE}"
TASK_DESC="SEGMENTADV for tablespace ${TABLESPACE}"
# 创建任务
echo "$(date +'%Y%m%d %H:%M:%S')"
echo "Creating task: $TASK_NAME"
sqlplus -s / as sysdba<<EOF
set heading off
set feedback off
DECLARE
task_id number;
BEGIN
dbms_advisor.create_task('Segment Advisor', '$TASK_NAME', '$TASK_DESC', NULL);
dbms_advisor.create_object( task_name => '$TASK_NAME', object_type => 'TABLESPACE', attr1 => '$TABLESPACE', attr2 => NULL, attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => NULL);
dbms_advisor.set_task_parameter('$TASK_NAME', 'RECOMMEND_ALL', 'TRUE');
dbms_advisor.execute_task('$TASK_NAME');
END;
/
exit
EOF
echo "Completed task: $TASK_NAME"
done
页:
[1]