• 段指导-示例

    RDBMS 19.20

    参考文档：

    Database Administrator’s Guide
    19 Managing Space for Schema Objects
    19.3.2.4 Running the Segment Advisor Manually

    针对表SOE.CUSTOMERS进行段指导
     

    -- 创建段指导

    1. variable id number;
    2. begin
    3.   declare
    4.   name varchar2(100);
    5.   descr varchar2(500);
    6.   obj_id number;
    7.   begin
    8.   name:='Manual_Employees';
    9.   descr:='Segment Advisor Example';
    10.  
    11.   dbms_advisor.create_task (
    12.     advisor_name     => 'Segment Advisor',
    13.     task_id          => :id,
    14.     task_name        => name,
    15.     task_desc        => descr);
    16.  
    17.   dbms_advisor.create_object (
    18.     task_name        => name,
    19.     object_type      => 'TABLE',
    20.     attr1            => 'SOE',
    21.     attr2            => 'CUSTOMERS',
    22.     attr3            => NULL,
    23.     attr4            => NULL,
    24.     attr5            => NULL,
    25.     object_id        => obj_id);
    26.  
    27.   dbms_advisor.set_task_parameter(
    28.     task_name        => name,
    29.     parameter        => 'recommend_all',
    30.     value            => 'TRUE');
    31.  
    32.   dbms_advisor.execute_task(name);
    33.   end;
    34. end; 
    35. /

    -- 查看段指导是否运行完毕

    1. select task_name, status
    2.   from dba_advisor_tasks
    3.  where task_name = 'Manual_Employees'
    4.    and advisor_name = 'Segment Advisor';

    -- 查看段指导对应的结果

    1. select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 
    2.   from dba_advisor_findings af, dba_advisor_objects ao
    3.   where ao.task_id = af.task_id
    4.   and ao.object_id = af.object_id
    5.   and af.task_name = 'Manual_Employees'
    6.  
    7. select task_name,message,more_info from dba_advisor_findings  where task_name = 'Manual_Employees'
    8. --select * from DBA_ADVISOR_RECOMMENDATIONS where task_name = 'Manual_Employees'
    9. --select * from DBA_ADVISOR_ACTIONS where task_name = 'Manual_Employees'
    10. select task_name,command,attr1,attr2,attr3 from DBA_ADVISOR_ACTIONS where task_name like '%SYS_AUTO_SPCADV%'
    11. select * from DBA_ADVISOR_OBJECTS where task_name = 'Manual_Employees'

    -- 查看近期的段指导建议

    1. select tablespace_name,
    2.        segment_name,
    3.        segment_type,
    4.        partition_name,
    5.        recommendations,
    6.        c1
    7.   from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

    --部分结果示例

    1. SYS@test>select task_name,message,more_info from dba_advisor_findings  where task_name = 'Manual_Employees'
    2.   2  ;
    3.  
    4. TASK_NAME
    5. --------------------------------------------------------------------------------
    6. MESSAGE
    7. --------------------------------------------------------------------------------
    8. MORE_INFO
    9. --------------------------------------------------------------------------------
    10. Manual_Employees
    11. The free space in the object is less than 10MB.
    12. Allocated Space:180355072: Used Space:178803055: Reclaimable Space :1552017:
    13.  
    14.  
    15. SYS@test>
    1. SYS@test>select task_name,command,attr1,attr2,attr3 from DBA_ADVISOR_ACTIONS where task_name like '%SYS_AUTO_SPCADV%';
    2.  
    3. TASK_NAME
    4. --------------------------------------------------------------------------------
    5. COMMAND
    6. ----------------------------------------------------------------
    7. ATTR1
    8. --------------------------------------------------------------------------------
    9. ATTR2
    10. --------------------------------------------------------------------------------
    11. ATTR3
    12. --------------------------------------------------------------------------------
    13. SYS_AUTO_SPCADV126200902092023
    14. SHRINK SPACE
    15. alter table "OGG"."GGS_DDL_HIST" shrink space
    16.  
    17. TASK_NAME
    18. --------------------------------------------------------------------------------
    19. COMMAND
    20. ----------------------------------------------------------------
    21. ATTR1
    22. --------------------------------------------------------------------------------
    23. ATTR2
    24. --------------------------------------------------------------------------------
    25. ATTR3
    26. --------------------------------------------------------------------------------
    27. alter table "OGG"."GGS_DDL_HIST" shrink space COMPACT
    28. alter table "OGG"."GGS_DDL_HIST" enable row movement
    29.  
    30.  
    31. TASK_NAME
    32. --------------------------------------------------------------------------------
    33. COMMAND
    34. ----------------------------------------------------------------
    35. ATTR1
    36. --------------------------------------------------------------------------------
    37. ATTR2
    38. --------------------------------------------------------------------------------
    39. ATTR3
    40. --------------------------------------------------------------------------------
    41. SYS_AUTO_SPCADV126200902092023
    42. ENABLE COMPRESSION
    43. alter table "SOE"."ORDERS"  compress for oltp
    44.  
    45. TASK_NAME
    46. --------------------------------------------------------------------------------
    47. COMMAND
    48. ----------------------------------------------------------------
    49. ATTR1
    50. --------------------------------------------------------------------------------
    51. ATTR2
    52. --------------------------------------------------------------------------------
    53. ATTR3
    54. --------------------------------------------------------------------------------
    55. alter table "SOE"."ORDERS"  move
    56.  
    57.  
    58.  
    59. TASK_NAME
    60. --------------------------------------------------------------------------------
    61. COMMAND
    62. ----------------------------------------------------------------
    63. ATTR1
    64. --------------------------------------------------------------------------------
    65. ATTR2
    66. --------------------------------------------------------------------------------
    67. ATTR3
    68. --------------------------------------------------------------------------------
    69. SYS_AUTO_SPCADV126200902092023
    70. ENABLE COMPRESSION
    71. alter table "SOE"."CUSTOMERS"  compress for oltp
    72.  
    73. TASK_NAME
    74. --------------------------------------------------------------------------------
    75. COMMAND
    76. ----------------------------------------------------------------
    77. ATTR1
    78. --------------------------------------------------------------------------------
    79. ATTR2
    80. --------------------------------------------------------------------------------
    81. ATTR3
    82. --------------------------------------------------------------------------------
    83. alter table "SOE"."CUSTOMERS"  move
    84.  
    85.  
    86.  
    87. TASK_NAME
    88. --------------------------------------------------------------------------------
    89. COMMAND
    90. ----------------------------------------------------------------
    91. ATTR1
    92. --------------------------------------------------------------------------------
    93. ATTR2
    94. --------------------------------------------------------------------------------
    95. ATTR3
    96. --------------------------------------------------------------------------------
    97. SYS_AUTO_SPCADV126200902092023
    98. ENABLE COMPRESSION
    99. alter table "OGG"."GGS_DDL_HIST"  compress for oltp
    100.  
    101. TASK_NAME
    102. --------------------------------------------------------------------------------
    103. COMMAND
    104. ----------------------------------------------------------------
    105. ATTR1
    106. --------------------------------------------------------------------------------
    107. ATTR2
    108. --------------------------------------------------------------------------------
    109. ATTR3
    110. --------------------------------------------------------------------------------
    111. alter table "OGG"."GGS_DDL_HIST"  move
    112.  
    113.  
    114.  
    115. TASK_NAME
    116. --------------------------------------------------------------------------------
    117. COMMAND
    118. ----------------------------------------------------------------
    119. ATTR1
    120. --------------------------------------------------------------------------------
    121. ATTR2
    122. --------------------------------------------------------------------------------
    123. ATTR3
    124. --------------------------------------------------------------------------------
    125. SYS_AUTO_SPCADV258251302092023
    126. SHRINK SPACE
    127. alter table "OGG"."GGS_DDL_HIST" shrink space
    128.  
    129. TASK_NAME
    130. --------------------------------------------------------------------------------
    131. COMMAND
    132. ----------------------------------------------------------------
    133. ATTR1
    134. --------------------------------------------------------------------------------
    135. ATTR2
    136. --------------------------------------------------------------------------------
    137. ATTR3
    138. --------------------------------------------------------------------------------
    139. alter table "OGG"."GGS_DDL_HIST" shrink space COMPACT
    140. alter table "OGG"."GGS_DDL_HIST" enable row movement
    141.  
    142.  
    143. TASK_NAME
    144. --------------------------------------------------------------------------------
    145. COMMAND
    146. ----------------------------------------------------------------
    147. ATTR1
    148. --------------------------------------------------------------------------------
    149. ATTR2
    150. --------------------------------------------------------------------------------
    151. ATTR3
    152. --------------------------------------------------------------------------------
    153. SYS_AUTO_SPCADV616480909092023
    154. SHRINK SPACE
    155. alter table "OGG"."GGS_DDL_HIST" shrink space
    156.  
    157. TASK_NAME
    158. --------------------------------------------------------------------------------
    159. COMMAND
    160. ----------------------------------------------------------------
    161. ATTR1
    162. --------------------------------------------------------------------------------
    163. ATTR2
    164. --------------------------------------------------------------------------------
    165. ATTR3
    166. --------------------------------------------------------------------------------
    167. alter table "OGG"."GGS_DDL_HIST" shrink space COMPACT
    168. alter table "OGG"."GGS_DDL_HIST" enable row movement
    169.  
    170.  
    171. TASK_NAME
    172. --------------------------------------------------------------------------------
    173. COMMAND
    174. ----------------------------------------------------------------
    175. ATTR1
    176. --------------------------------------------------------------------------------
    177. ATTR2
    178. --------------------------------------------------------------------------------
    179. ATTR3
    180. --------------------------------------------------------------------------------
    181. SYS_AUTO_SPCADV705541309092023
    182. SHRINK SPACE
    183. alter table "OGG"."GGS_DDL_HIST" shrink space
    184.  
    185. TASK_NAME
    186. --------------------------------------------------------------------------------
    187. COMMAND
    188. ----------------------------------------------------------------
    189. ATTR1
    190. --------------------------------------------------------------------------------
    191. ATTR2
    192. --------------------------------------------------------------------------------
    193. ATTR3
    194. --------------------------------------------------------------------------------
    195. alter table "OGG"."GGS_DDL_HIST" shrink space COMPACT
    196. alter table "OGG"."GGS_DDL_HIST" enable row movement
    197.  
    198.  
    199. TASK_NAME
    200. --------------------------------------------------------------------------------
    201. COMMAND
    202. ----------------------------------------------------------------
    203. ATTR1
    204. --------------------------------------------------------------------------------
    205. ATTR2
    206. --------------------------------------------------------------------------------
    207. ATTR3
    208. --------------------------------------------------------------------------------
    209. SYS_AUTO_SPCADV705541309092023
    210. ENABLE COMPRESSION
    211. alter table "OGG"."GGS_DDL_HIST"  compress for oltp
    212.  
    213. TASK_NAME
    214. --------------------------------------------------------------------------------
    215. COMMAND
    216. ----------------------------------------------------------------
    217. ATTR1
    218. --------------------------------------------------------------------------------
    219. ATTR2
    220. --------------------------------------------------------------------------------
    221. ATTR3
    222. --------------------------------------------------------------------------------
    223. alter table "OGG"."GGS_DDL_HIST"  move
    224.  
    225.  
    226.  
    227. 8 rows selected.
    228.  
    229. SYS@test>

    END

  • 相关阅读:
    设计LRU/LFU缓存结构
    【Java面试】面试自閟了！工作5年的小伙伴今天面试被吊打问我，并行和并发有什么区别？
    文心一言 VS CHATGPT
    【Linux-day11-线程的创建与同步】
    万字长文详解HBase读写性能优化
    springboot+rediss实现分布式定时任务
    【pandas数据分析】pandas功能和操作简单示例
    202311.13 windows通过vscode ssh远程连接到Ubuntu 连接失败 waiting for server log
    网络传输 Tips 小记
    掌握系统性思维
  • 原文地址：https://blog.csdn.net/xxzhaobb/article/details/132850986