通过段调优顾问帮助解决行链接问题
--解决行链接问题 SQL> create tablespace hzqtbs datafile '/u01/app/oracle/oradata/prod/hzqtbs.dbf' size 600m; Tablespace created. SQL> create user hzq identified by hzq default tablespace hzqtbs quota unlimited on hzqtbs account unlock; User created. SQL> grant create session to hzq; Grant succeeded. SQL> grant resource to hzq; Grant succeeded. SQL> conn hzq/hzq Connected. create table test (id number,name varchar2(100)); begin for i in 1..1000000 loop insert into test values(i,'user'||i); commit; end loop; end; / --通过更新使字段边长生成行链接 update test set name='userldsajflsadjfldsjfldsafdsahflsahfldsaflsajflkdajfjflajfldsakjflkdsajfldsajflsafjlasfadsafdsafsad'; --删除,重新收集手动运行顾问 DECLARE BEGIN dbms_advisor.delete_task ( task_name => 'MYTEST Advice' ); END; / DECLARE my_task_id number; obj_id number; my_task_name varchar2(100); my_task_desc varchar2(500); BEGIN my_task_name := 'MYTEST Advice'; my_task_desc := 'Manual Segment Advisor Run'; --------- -- Step 1 create a task --------- dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => my_task_id, task_name => my_task_name, task_desc => my_task_desc); --------- -- Step 2 create a object --------- dbms_advisor.create_object ( task_name => my_task_name, object_type => 'TABLE', attr1 => 'HZQ', attr2 => 'TEST', attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => obj_id); --------- -- Step 3 --------- dbms_advisor.set_task_parameter( task_name => my_task_name, parameter => 'recommend_all', value => 'TRUE'); --------- -- Step 4 --------- dbms_advisor.execute_task(my_task_name); END; / --查看看行链接问题 SELECT 'Task Name : ' || f.task_name || chr(10) || 'Segment Name : ' || o.attr2 || chr(10) || 'Segment Type : ' || o.type || chr(10) || 'Partition Name : ' || o.attr3 || chr(10) || 'Message : ' || f.message || chr(10) || 'More Info : ' || f.more_info TASK_ADVICE FROM dba_advisor_findings f ,dba_advisor_objects o WHERE o.task_id = f.task_id AND o.object_id = f.object_id ORDER BY f.task_name; select 'Task Name : ' || f.task_name || chr(10) || 'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) || 'Segment Name : ' || o.attr2 || chr(10) || 'Segment Type : ' || o.type || chr(10) || 'Partition Name : ' || o.attr3 || chr(10) || 'Message : ' || f.message || chr(10) || 'More Info : ' || f.more_info || chr(10) || '------------------------------------------------------' Advice FROM dba_advisor_findings f ,dba_advisor_objects o ,dba_advisor_executions e WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name; ADVICE ---------------------------- Task Name : MYTEST Advice Start Run Time : 18-may-16 22:53 Segment Name : TEST Segment Type : TABLE Partition Name : Message : The free space in the object is less than 10MB. More Info : Allocated Space:167772160: Used Space:165481507: Reclaimable Space :2290653: ------------------------------------------------------ Task Name : MYTEST Advice Start Run Time : 18-may-16 22:53 ADVICE ------------------------------------------------------- Segment Name : TEST Segment Type : TABLE Partition Name : Message : The object has chained rows that can be removed by re-org. More Info : 46 percent chained rows can be removed by re-org. --有46%的行链接可以被remove,解决行链接的方法是通过move表来实现,但是表move后表的索引会失效,需要重建索引,行移动需要很多的表空间,建议表空间空余空间大于表的大小。 SQL> create index idx_test on test(id); Index created. SQL> conn / as sysdba Connected. SQL> select owner,index_name,status from dba_indexes where table_name='TEST'; OWNER INDEX_NAME STATUS ------------------------------ ------------------------------ -------- HZQ IDX_TEST UNUSABLE SQL> alter index hzq.IDX_TEST rebuild; Index altered. --删除,重新收集手动运行顾问 DECLARE BEGIN dbms_advisor.delete_task ( task_name => 'MYTEST Advice' ); END; / DECLARE my_task_id number; obj_id number; my_task_name varchar2(100); my_task_desc varchar2(500); BEGIN my_task_name := 'MYTEST Advice'; my_task_desc := 'Manual Segment Advisor Run'; --------- -- Step 1 create a task --------- dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => my_task_id, task_name => my_task_name, task_desc => my_task_desc); --------- -- Step 2 create a object --------- dbms_advisor.create_object ( task_name => my_task_name, object_type => 'TABLE', attr1 => 'HZQ', attr2 => 'TEST', attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => obj_id); --------- -- Step 3 --------- dbms_advisor.set_task_parameter( task_name => my_task_name, parameter => 'recommend_all', value => 'TRUE'); --------- -- Step 4 --------- dbms_advisor.execute_task(my_task_name); END; / SQL> select 'Task Name : ' || f.task_name || chr(10) || 'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) || 'Segment Name : ' || o.attr2 || chr(10) || 'Segment Type : ' || o.type || chr(10) || 'Partition Name : ' || o.attr3 || ch 2 3 4 5 6 r(10) || 'Message : ' || f.message || chr(10) || 'More Info : ' || f.more_info || chr(10) || '------------------------------------------------------' Advice FROM dba_advisor_findings f ,dba_advisor_objects o ,dba_advisor_executions e WHERE o.task_id = f.ta 7 8 9 10 11 12 13 sk_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name; 14 15 16 17 18 ADVICE ---------------------------------------------------- Task Name : MYTEST Advice Start Run Time : 18-may-16 23:07 Segment Name : TEST Segment Type : TABLE Partition Name : Message : The free space in the object is less than 10MB. More Info : Allocated Space:134217728: Used Space:126002153: Reclaimable Space :8215575: ------------------------------------------------------ --结果没有显示行链接问题
坚持,专注