Oracle Segment Advisor
來源:http://www.cnblogs.com/einyboy/archive/2012/08/10/2631363.html
Oracle內建空間管理工具-Segment Advisor
資料表上頻繁的進行插入、更新和刪除動作會產生表空間碎片。Oracle可在表或索引上執行Segment shrink。使得segment的空閒空間可用於表空間中的其它segment,可改善DML效能。
呼叫Segment Advisor對指定segment執行增長趨勢分析以確定哪些Segment受益於Segment shrink。
執行shrink操作,Segment Advisor推薦啟用表的ROW MOVEMENT
SQL> alter table wind enable row movement;
Table altered
配置Segment Advisor對錶進行分析,使用如下PL/SQL塊:
SQL> variable task_id number;
SQL> declare
2 name varchar2(100);
3 descr varchar2(500);
4 obj_id number;
5 begin
6 name := '';
7 descr := 'Check WIND.ShrinkTable Table';
8 dbms_advisor.create_task('Segment Advisor',:task_id,name,descr,NULL);
9 dbms_advisor.create_object(name,'TABLE','WIND','SHRINKTABLE',NULL,NULL,obj_id);
10 dbms_advisor.set_task_parameter(name,'RECOMMEND_ALL','TRUE');
11 dbms_advisor.execute_task(name);
12 end;
13 /
PL/SQL procedure successfully completed
task_id
---------
357
dbms_advisor.create_task: 指定型別,返回唯一的作業ID並自動生成執行的程式名.
dbms_advisor.create_object: 指定分析物件資訊.
dbms_advisor.set_task_parameter: 這裡指定獲取所有關於表的建議.
dbms_advisor.execute_task: 執行
以下為獲取表建議資訊:
SQL> select owner,task_id,task_name,type,messagel,more_info
2 from dba_advisor_findings
3 where task_id=357;
OWNER TASK_ID TASK_NAME TYPE MESSAGE MORE_INFO
------- ------- ---------- ----------- -------------------------------------- -------------------------------------------------------------
WIND 357 TASK_357 INFORMATION 進行壓縮, 估計可以省出 10655319 位元組。
分配空間:226492416: 已用空間:215837097: 可回收空間:10655319:
SQL> select owner,task_id,task_name,benefit_type
2 from dba_advisor_recommendations
3 where task_id=357;
OWNER TASK_ID TASK_NAME BENEFIT_TYPE
---------- ----------- --------------- --------------------------------------
WIND 357 TASK_357 進行壓縮, 估計可以省出 10655319 位元組。
SQL> select owner,task_id,task_name,command,attr1
2 from dba_advisor_actions
3 where task_id=357;
OWNER TASK_ID TASK_NAME COMMAND ATTR1
---------- ---------- ---------------- ------------------- ---------------------------------------
WIND 357 TASK_357 SHRINK SPACE alter table "WIND"."THRINKTABLE" shrink space
SQL> alter table wind.thrinktable shrink space;
注意兩個限制:
一. 沒法在基於Oracle 10g上的LOB Segment上工作.
二. 不允許在包含任何函式索引規則的表進行操作,不管是使用10g或11g.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15187685/viewspace-758784/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle實用sql(2)--segment advisorOracleSQL
- Oracle Undo SegmentOracle
- zt_Segment Advisor監控物件的碎片程度並回收物件
- [Oracle Script] Rollback Segment UsageOracle
- Oracle-Segment space managementOracle
- Oracle Data Recovery AdvisorOracle
- Advisor Webcasts for Oracle DatabaseWebASTOracleDatabase
- ORACLE LOB SEGMENT常規管理Oracle
- How to Shrink Undo Segment In Oracle DatabaseOracleDatabase
- oracle addm segment tuningOracle
- oracle實用sql(8)--segment show spaceOracleSQL
- oracle segment space management and extent management幾則Oracle
- 聊聊Oracle Data Recovery Advisor(DRA)(上)Oracle
- 聊聊Oracle Data Recovery Advisor(DRA)(下)Oracle
- oracle實用sql(4)--undo advisorOracleSQL
- oracle concept——data block,extent,segment-01OracleBloC
- Oracle優化案例-關閉auto space advisor和sql tuning advisor(十九)Oracle優化SQL
- ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- Oracle佇列鎖enq:TS,Temporary Segment (also TableSpace)Oracle佇列ENQ
- Oracle 11g的Deferred Segment CreationOracle
- Oracle 11 sql tuning advisor sql access advisor關閉以及job檢視與停止OracleSQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- oracle 11g 新特性 data recover AdvisorOracle
- oracle實用sql(3)--sql tuning advisorOracleSQL
- 水煮oracle33---關於oracle中segment、schema和user區別Oracle
- Oracle OCP IZ0-053 Q432(Segment Shrink)Oracle
- Oracle SQL優化之sql tuning advisor(STA)OracleSQL優化
- [原創]ORACLE SQL TUNING ADVISOR 使用方法OracleSQL
- New redo log sizing advisor in Oracle10gOracle
- SQL Access Advisor 與SQL Tuning AdvisorSQL
- 查詢user使用那些roll segment以及等待roll segment
- oracle undo segment header 事務表transaction table系列一OracleHeader
- assm下oracle為什麼為segment提供了shrink功能SSMOracle
- oracle SQL最佳化器SQL Tuning Advisor (STA)OracleSQL
- Oracle SQL最佳化之sql tuning advisor(STA)OracleSQL
- SQL Access Advisor、SQL Tuning Advisor 測試SQL
- 讀書筆記-高階owi與oracle效能調整-segment筆記Oracle
- Oracle效能最佳化之Rollback(undo)Segment最佳化(轉)Oracle