不使用分析索引命令檢查索引碎片？

我研究過很多腳本需要先使用analyze index命令。有沒有其他方法可以檢查索引碎片而不填充表中的那些列index_stats？一些替代腳本？

編輯：我問它的原因是因為analyze index validate structure鎖定了 DML 操作的表。

命令有什麼問題analyze？

是空間嗎？Oracle已經收集了所有analyze生成的元數據；執行該命令僅根據索引的目前狀態更新該元數據。

還是時間/性能？您可以使用 SQL 查詢來檢查索引的問題，但對於任何問題的具體分析，您仍然需要執行analyze. 從文件中：

18.2.4 使用查詢對錶和索引進行交叉驗證

在某些情況下，ANALYZE 語句需要花費過多的時間才能完成。在這些情況下，您可以使用 SQL 查詢來驗證索引。

如果查詢確定表和索引之間存在不一致，則可以使用 ANALYZE 語句對索引進行徹底分析。由於通常數據庫中的大多數對像都沒有損壞，因此您可以使用此快速查詢來消除許多表作為損壞的候選對象，並且只對可能損壞的表使用 ANALYZE 語句。

要驗證索引，請執行以下查詢：

SELECT /*+ FULL(ALIAS) PARALLEL(ALIAS, DOP) */ SUM(ORA_HASH(ROWID))
   FROM table_name ALIAS 
   WHERE ALIAS.index_column IS NOT NULL 
      MINUS SELECT /*+ INDEX_FFS(ALIAS index_name) 
      PARALLEL_INDEX(ALIAS, index_name, DOP) */ SUM(ORA_HASH(ROWID)) 
   FROM table_name ALIAS WHERE ALIAS.index_column IS NOT NULL;

執行查詢時，進行以下替換：

• 輸入 table_name 佔位符的表名。
• 輸入 index_column 佔位符的索引列。
• 輸入 index_name 佔位符的索引名稱。

如果查詢返回任何行，則可能存在不一致，您可以使用 ANALYZE 語句進行進一步診斷。

如果需要深入分析索引，那麼analyze命令是唯一的方法。

但是，如果您嘗試收集優化器統計資訊，還有其他選擇：

注意：使用 ANALYZE 收集優化器統計資訊已過時。

如果要收集優化器統計資訊，請使用 DBMS_STATS包，它允許您並行收集統計資訊、分區對象的全域統計資訊，並幫助您以其他方式微調統計資訊收集。有關 DBMS_STATS 包的更多資訊，請參閱 Oracle 數據庫 PL/SQL 包和類型參考。

僅在以下情況下使用 ANALYZE 語句：

• 使用 VALIDATE 或 LIST CHAINED ROWS 子句
• 收集有關 freelist 塊的資訊

請參閱Oracle Database SQL Tuning Guide以了解如何管理優化器統計資訊。

使用最新的表統計資訊，您可以使用DBMS_SPACE.CREATE_INDEX_COST來查找要創建索引的估計大小，然後可以將其與實際大小進行比較，看看是否有顯著差異。

範例：https ://stackoverflow.com/questions/17679033/how-to-tell-the-amount-of-tablespace-that-would-be-freed-by-rebuilding-indexes

另一個例子：https ://carlos-sierra.net/2017/07/12/script-to-identify-index-rebuild-candidates-on-12c/

----------------------------------------------------------------------------------------
--
-- File name: indexes_2b_shrunk.sql
--
-- Purpose: List of candidate indexes to be shrunk (rebuild online)
--
-- Author: Carlos Sierra
--
-- Version: 2017/07/12
--
-- Usage: Execute on PDB
--
-- Example: @indexes_2b_shrunk.sql
--
-- Notes: Execute connected into a PDB.
-- Consider then:
-- ALTER INDEX [schema.]index REBUILD ONLINE;
--
---------------------------------------------------------------------------------------

-- select only those indexes with an estimated space saving percent greater than 25%
VAR savings_percent NUMBER;
EXEC :savings_percent := 25;
-- select only indexes with current size (as per cbo stats) greater then 1MB
VAR minimum_size_mb NUMBER;
EXEC :minimum_size_mb := 1;

SET SERVEROUT ON ECHO OFF FEED OFF VER OFF TAB OFF LINES 300;

COL report_date NEW_V report_date;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS') report_date FROM DUAL;
SPO /tmp/indexes_2b_shrunk_&&report_date..txt;

DECLARE
l_used_bytes NUMBER;
l_alloc_bytes NUMBER;
l_percent NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('PDB: '||SYS_CONTEXT('USERENV', 'CON_NAME'));
DBMS_OUTPUT.PUT_LINE('---');
DBMS_OUTPUT.PUT_LINE(
RPAD('OWNER.INDEX_NAME', 35)||' '||
LPAD('SAVING %', 10)||' '||
LPAD('CURRENT SIZE', 20)||' '||
LPAD('ESTIMATED SIZE', 20));
DBMS_OUTPUT.PUT_LINE(
RPAD('-', 35, '-')||' '||
LPAD('-', 10, '-')||' '||
LPAD('-', 20, '-')||' '||
LPAD('-', 20, '-'));
FOR i IN (SELECT x.owner, x.index_name, SUM(s.leaf_blocks) * TO_NUMBER(p.value) index_size,
REPLACE(DBMS_METADATA.GET_DDL('INDEX',x.index_name,x.owner),CHR(10),CHR(32)) ddl
FROM dba_ind_statistics s, dba_indexes x, dba_users u, v$parameter p
WHERE u.oracle_maintained = 'N'
AND x.owner = u.username
AND x.tablespace_name NOT IN ('SYSTEM','SYSAUX')
AND x.index_type LIKE '%NORMAL%'
AND x.table_type = 'TABLE'
AND x.status = 'VALID'
AND x.temporary = 'N'
AND x.dropped = 'NO'
AND x.visibility = 'VISIBLE'
AND x.segment_created = 'YES'
AND x.orphaned_entries = 'NO'
AND p.name = 'db_block_size'
AND s.owner = x.owner
AND s.index_name = x.index_name
GROUP BY
x.owner, x.index_name, p.value
HAVING
SUM(s.leaf_blocks) * TO_NUMBER(p.value) > :minimum_size_mb * POWER(2,20)
ORDER BY
index_size DESC)
LOOP
DBMS_SPACE.CREATE_INDEX_COST(i.ddl,l_used_bytes,l_alloc_bytes);
IF i.index_size * (100 - :savings_percent) / 100 > l_alloc_bytes THEN
l_percent := 100 * (i.index_size - l_alloc_bytes) / i.index_size;
DBMS_OUTPUT.PUT_LINE(
RPAD(i.owner||'.'||i.index_name, 35)||' '||
LPAD(TO_CHAR(ROUND(l_percent, 1), '990.0')||' % ', 10)||' '||
LPAD(TO_CHAR(ROUND(i.index_size / POWER(2,20), 1), '999,999,990.0')||' MB', 20)||' '||
LPAD(TO_CHAR(ROUND(l_alloc_bytes / POWER(2,20), 1), '999,999,990.0')||' MB', 20));
END IF;
END LOOP;
END;
/

SPO OFF;

這不會像 中那樣為您提供已刪除行的實際比率ANALYZE INDEX ... VALIDATE STRUCTURE，INDEX_STATS只是一個估計值，但這對您來說可能已經足夠了。

引用自：https://dba.stackexchange.com/questions/315934