oracle 索引重建提示指令碼

zhouxianwang發表於2014-01-08

點選(此處)摺疊或開啟

  1. REM =============================================================
  2. REM
  3. REM rebuild_indx.sql
  4. REM
  5. REM Copyright (c) Oracle Software, 1998 - 2000
  6. REM
  7. REM Author : Jurgen Schelfhout
  8. REM
  9. REM The sample program in this article is provided for educational
  10. REM purposes only and is NOT supported by Oracle Support Services.
  11. REM It has been tested internally, however, and works as documented.
  12. REM We do not guarantee that it will work for you, so be sure to test
  13. REM it in your environment before relying on it.
  14. REM
  15. REM This script will analyze all the indexes for a given schema
  16. REM or for a subset of schema\'s. After this the dynamic view
  17. REM index_stats is consulted to see if an index is a good
  18. REM candidate for a rebuild or for a bitmap index.
  19. REM
  20. REM Database Version : 7.3.X and above.
  21. REM
  22. REM NOTE: If running this on 10g, you must exclude the
  23. REM objects in the Recycle Bin
  24. REM cursor c_indx is
  25. REM select owner, table_name, index_name
  26. REM from dba_indexes
  27. REM where owner like upper(\'&schema\')
  28. REM and table_name not like \'BIN$%\'
  29. REM and owner not in (\'SYS\',\'SYSTEM\');
  30. REM
  31. REM Additional References for Recycle Bin functionality:
  32. REM Note.265254.1 Flashback Table feature in Oracle Database 10g
  33. REM Note.265253.1 10g Recyclebin Features And How To Disable it(_recyclebin)
  34. REM
  35. REM =============================================================

  36. prompt
  37. ACCEPT spoolfile CHAR prompt \'Output-file : \';
  38. ACCEPT schema CHAR prompt \'Schema name (% allowed) : \';
  39. prompt
  40. prompt
  41. prompt Rebuild the index when :
  42. prompt - deleted entries represent 20% or more of the current entries
  43. prompt - the index depth is more then 4 levels.
  44. prompt Possible candidate for bitmap index :
  45. prompt - when distinctiveness is more than 99%
  46. prompt

  47. spool &spoolfile;
  48. set serveroutput on;
  49. set verify off;
  50. set linesize 140;
  51. declare
  52.   c_name INTEGER;
  53.   ignore INTEGER;
  54.   height index_stats.height%TYPE := 0;
  55.   lf_rows index_stats.lf_rows%TYPE := 0;
  56.   del_lf_rows index_stats.del_lf_rows%TYPE := 0;
  57.   distinct_keys index_stats.distinct_keys%TYPE := 0;
  58.   cursor c_indx is
  59.     select owner, table_name, index_name
  60.       from dba_indexes
  61.      where owner like upper(\'&schema\')
  62.        and owner not in (\'SYS\', \'SYSTEM\');
  63. begin
  64.   dbms_output.enable(1000000);
  65.   dbms_output.put_line(\'Owner Index Name % Deleted Entries Blevel Distinctiveness\');
  66.   dbms_output.put_line(\'-------------- --------------------------------- ------------ ----- -----\');

  67.   c_name := DBMS_SQL.OPEN_CURSOR;
  68.   for r_indx in c_indx loop
  69.     DBMS_SQL.PARSE(c_name,
  70.                    \'analyze index \' || r_indx.owner || \'.\' ||
  71.                    r_indx.index_name || \' validate structure\',
  72.                    DBMS_SQL.NATIVE);
  73.     ignore := DBMS_SQL.EXECUTE(c_name);

  74.     select HEIGHT,
  75.            decode(LF_ROWS, 0, 1, LF_ROWS),
  76.            DEL_LF_ROWS,
  77.            decode(DISTINCT_KEYS, 0, 1, DISTINCT_KEYS)
  78.       into height, lf_rows, del_lf_rows, distinct_keys
  79.       from index_stats;
  80.     /*
  81.     - Index is considered as candidate for rebuild when :
  82.     - - when deleted entries represent 20% or more of the current entries
  83.     - - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
  84.     - Index is (possible) candidate for a bitmap index when :
  85.     - - distinctiveness is more than 99%
  86.     */
  87.     if (height > 5) OR ((del_lf_rows / lf_rows) > 0.2) then
  88.       dbms_output.put_line(rpad(r_indx.owner, 16, \' \') ||
  89.                            rpad(r_indx.index_name, 40, \' \') ||
  90.                            lpad(round((del_lf_rows / lf_rows) * 100, 3),
  91.                                 17,
  92.                                 \' \') || lpad(height - 1, 7, \' \') ||
  93.                            lpad(round((lf_rows - distinct_keys) * 100 /
  94.                                       lf_rows,
  95.                                       3),
  96.                                 16,
  97.                                 \' \'));
  98.     end if;

  99.   end loop;
  100.   DBMS_SQL.CLOSE_CURSOR(c_name);
  101. end;
  102. /
  103. spool off;
  104. set verify on;

  105. [oracle@svn zxw]$ cat rebuild_indx.sql
  106. REM =============================================================
  107. REM
  108. REM rebuild_indx.sql
  109. REM
  110. REM Copyright (c) Oracle Software, 1998 - 2000
  111. REM
  112. REM Author : Jurgen Schelfhout
  113. REM
  114. REM The sample program in this article is provided for educational
  115. REM purposes only and is NOT supported by Oracle Support Services.
  116. REM It has been tested internally, however, and works as documented.
  117. REM We do not guarantee that it will work for you, so be sure to test
  118. REM it in your environment before relying on it.
  119. REM
  120. REM This script will analyze all the indexes for a given schema
  121. REM or for a subset of schema\'s. After this the dynamic view
  122. REM index_stats is consulted to see if an index is a good
  123. REM candidate for a rebuild or for a bitmap index.
  124. REM
  125. REM Database Version : 7.3.X and above.
  126. REM
  127. REM NOTE: If running this on 10g, you must exclude the
  128. REM objects in the Recycle Bin
  129. REM cursor c_indx is
  130. REM select owner, table_name, index_name
  131. REM from dba_indexes
  132. REM where owner like upper(\'&schema\')
  133. REM and table_name not like \'BIN$%\'
  134. REM and owner not in (\'SYS\',\'SYSTEM\');
  135. REM
  136. REM Additional References for Recycle Bin functionality:
  137. REM Note.265254.1 Flashback Table feature in Oracle Database 10g
  138. REM Note.265253.1 10g Recyclebin Features And How To Disable it(_recyclebin)
  139. REM
  140. REM =============================================================

  141. prompt
  142. ACCEPT spoolfile CHAR prompt \'Output-file : \';
  143. ACCEPT schema CHAR prompt \'Schema name (% allowed) : \';
  144. prompt
  145. prompt
  146. prompt Rebuild the index when :
  147. prompt - deleted entries represent 20% or more of the current entries
  148. prompt - the index depth is more then 4 levels.
  149. prompt Possible candidate for bitmap index :
  150. prompt - when distinctiveness is more than 99%
  151. prompt

  152. spool &spoolfile;
  153. set serveroutput on;
  154. set verify off;
  155. set linesize 140;
  156. declare
  157.   c_name INTEGER;
  158.   ignore INTEGER;
  159.   height index_stats.height%TYPE := 0;
  160.   lf_rows index_stats.lf_rows%TYPE := 0;
  161.   del_lf_rows index_stats.del_lf_rows%TYPE := 0;
  162.   distinct_keys index_stats.distinct_keys%TYPE := 0;
  163.   cursor c_indx is
  164.     select owner, table_name, index_name
  165.       from dba_indexes
  166.      where owner like upper(\'&schema\')
  167.        and owner not in (\'SYS\', \'SYSTEM\');
  168. begin
  169.   dbms_output.enable(1000000);
  170.   dbms_output.put_line(\'Owner Index Name % Deleted Entries Blevel Distinctiveness\');
  171.   dbms_output.put_line(\'-------------- --------------------------------- ------------ ----- -----\');

  172.  c_name := DBMS_SQL.OPEN_CURSOR;
  173.   for r_indx in c_indx loop
  174.     DBMS_SQL.PARSE(c_name,
  175.                    \'analyze index \' || r_indx.owner || \'.\' ||
  176.                    r_indx.index_name || \' validate structure\',
  177.                    DBMS_SQL.NATIVE);
  178.     ignore := DBMS_SQL.EXECUTE(c_name);

  179.     select HEIGHT,
  180.            decode(LF_ROWS, 0, 1, LF_ROWS),
  181.            DEL_LF_ROWS,
  182.            decode(DISTINCT_KEYS, 0, 1, DISTINCT_KEYS)
  183.       into height, lf_rows, del_lf_rows, distinct_keys
  184.       from index_stats;
  185.     /*
  186.     - Index is considered as candidate for rebuild when :
  187.     - - when deleted entries represent 20% or more of the current entries
  188.     - - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
  189.     - Index is (possible) candidate for a bitmap index when :
  190.     - - distinctiveness is more than 99%
  191.     */
  192.     if (height > 5) OR ((del_lf_rows / lf_rows) > 0.2) then
  193.       dbms_output.put_line(rpad(r_indx.owner, 16, \' \') ||
  194.                            rpad(r_indx.index_name, 40, \' \') ||
  195.                            lpad(round((del_lf_rows / lf_rows) * 100, 3),
  196.                                 17,
  197.                                 \' \') || lpad(height - 1, 7, \' \') ||
  198.                            lpad(round((lf_rows - distinct_keys) * 100 /
  199.                                       lf_rows,
  200.                                       3),
  201.                                 16,
  202.                                 \' \'));
  203.     end if;

  204.   end loop;
  205.   DBMS_SQL.CLOSE_CURSOR(c_name);
  206. end;
  207. /
  208. spool off;
  209. set verify on


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27036311/viewspace-1068278/,如需轉載,請註明出處,否則將追究法律責任。

相關文章