Oracle

ANALYZE TABLE..VALIDATE STRUCTURE 永遠執行

  • May 25, 2016

因此，正如我之前提到的，我是一名 SQL Server DBA/顧問，正在幫助客戶使用他們的 Oracle 數據庫。大多數情況下，我已經能夠弄清楚我需要做什麼，並在文件和Google的幫助下完成工作，但我目前正在嘗試處理一些似乎難以理解的事情。

其中一個數據庫是 Oracle 10g LIMS 數據庫，它已經存在（併升級）了至少 10 年（可能是 20 年）。這是一個關鍵的應用程序，但在過去的幾年裡，他們遇到了許多可靠性問題。為了試圖弄清它的真正問題以及我們可以做些什麼，我們讓託管/MS 提供商在另一台（相同的）伺服器上製作了它的物理副本。（我很確定提供商將其解釋為“RMAN 備份和恢復”）。

我的想法是，然後我可以進行任何分析、調查、修復嘗試，並使用我想要的任何工具，而不會有任何影響生產的危險。到目前為止，一切都很好。我對所有數據文件執行了 DBVerify ( dbv)，沒有問題。

然後我ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE ONLINE;在數據庫中的每個表上執行。對於其中兩個表，該命令比其他 2500 個表（組合）花費的時間要長得多，所以我取消了它們並讓其他表完成，他們這樣做沒有問題，也沒有報告錯誤等。

事實證明，這兩個表也是最大的應用程序數據表。然後我從最大的表（23GB，36M 行）開始，並決定按部分分析它（沒有 CASCADE），首先是表本身，然後是索引。該表在 30-60 分鐘內完成（我不記得確切），但ANALYZE INDEX .. VALIDATE STRUCTURE ONLINE;第一個索引永遠不會完成。

我決定讓它執行一段時間，看看我是否能找到一種方法來監控它的進度。Google搜尋我發現許多（舊的）聲稱可以監控 ANALYZE TABLEv$session_longops但查看它從未顯示我的命令中的任何內容，我得出的結論是這必須僅適用於 Stats 收集功能，而不是 Validate 功能。在嘗試了一堆事情之後，我最終發現通過查看v$session執行命令的行，我可以使用 P1* 和 P2* 列來跟踪正在讀取的文件和塊。

這向我表明它確實是從預期的數據文件中讀取，並且正在從它們中讀取不同的塊。這一事實以及命令執行時伺服器上的磁碟使用率為 100%（僅此而已）這一事實使我確信它確實在做某事並且沒有被阻塞/鎖定/掛起。所以我只是讓它執行。

現在已經超過 3 天（+72 小時）並且沒有顯示完成的跡象，我在這裡有點不知所措。

附加資訊：

表的 DDL：

-- Unable to render TABLE DDL for object O$LIMS.N__RESULTS with DBMS_METADATA attempting internal generator.
CREATE TABLE O$LIMS.N__RESULTS 
(
 SAMPLE_ID NUMBER(10, 0) NOT NULL 
, SUBMISSION_ID NUMBER(10, 0) NOT NULL 
, RESULT_ID NUMBER(10, 0) NOT NULL 
, RESULT_VERSION NUMBER(3, 0) NOT NULL 
, TASK_ID NUMBER(10, 0) NOT NULL 
, TASK_REPETITION NUMBER(3, 0) NOT NULL 
, TASK_VERSION NUMBER(3, 0) NOT NULL 
, REQUIRED VARCHAR2(1 BYTE) NOT NULL 
, METHOD_DATAGROUP VARCHAR2(40 BYTE) NOT NULL 
, COMPONENT VARCHAR2(40 BYTE) NOT NULL 
, MEASURE VARCHAR2(40 BYTE) NOT NULL 
, UNITS VARCHAR2(40 BYTE) NOT NULL 
, STATUS VARCHAR2(20 BYTE) NOT NULL 
, PLANNED_RESULT VARCHAR2(3 BYTE) NOT NULL 
, RESULT_ORIGIN VARCHAR2(1 BYTE) NOT NULL 
, CONDITION VARCHAR2(20 BYTE) NOT NULL 
, CONDITION_LEVEL VARCHAR2(20 BYTE) 
, VALUE_TYPE VARCHAR2(20 BYTE) NOT NULL 
, NUMBER_VALUE NUMBER 
, TEXT_VALUE VARCHAR2(80 BYTE) 
, TIME_VALUE DATE 
, REASON VARCHAR2(40 BYTE) 
, INLIMIT VARCHAR2(3 BYTE) 
, INDETECTION VARCHAR2(3 BYTE) 
, INSPEC VARCHAR2(3 BYTE) 
, ENTRY_USERID VARCHAR2(20 BYTE) 
, ENTRY_DATE DATE 
, SPEC_ID NUMBER(10, 0) 
, SPEC_VERSION NUMBER(3, 0) 
, DETECTION_ID NUMBER(10, 0) 
, DETECTION_VERSION NUMBER(3, 0) 
, LIMIT_ID NUMBER(10, 0) 
, LIMIT_VERSION NUMBER(3, 0) 
, CUSTOMER_DATAGROUP VARCHAR2(40 BYTE) 
, ANALYST VARCHAR2(20 BYTE) 
, REPORT VARCHAR2(3 BYTE) 
, TIMESTAMP DATE 
, USERSTAMP VARCHAR2(20 BYTE) 
, MEASURE_LINK O$LIMS.N_UT_MEASURE 
, RESULT_PLAN_LIST_LINK O$LIMS.N_UT_RESULT_PLAN_LIST 
, TEXT O$LIMS.N_TT_TEXT 
, ATTRIBUTES O$LIMS.N_TT_ATTRIBUTES 
, SEQUENCE NUMBER(4, 0) 
, CONSTRAINT N_C_RESULTS_1 PRIMARY KEY 
 (
   RESULT_ID 
 , RESULT_VERSION 
 )
 USING INDEX 
 (
     CREATE UNIQUE INDEX O$LIMS.N_C_RESULTS_1 ON O$LIMS.N__RESULTS (RESULT_ID ASC, RESULT_VERSION ASC) 
     LOGGING 
     TABLESPACE "SQLLIMS_INDEX" 
     PCTFREE 10 
     INITRANS 2 
     STORAGE 
     ( 
       INITIAL 311296 
       NEXT 1048576 
       MINEXTENTS 1 
       MAXEXTENTS UNLIMITED 
       BUFFER_POOL DEFAULT 
     ) 
     NOPARALLEL 
 )
 ENABLE 
) 
LOGGING 
TABLESPACE "SQLLIMS_RESULTS" 
PCTFREE 25 
INITRANS 1 
STORAGE 
( 
 INITIAL 566231040 
 NEXT 1048576 
 MINEXTENTS 1 
 MAXEXTENTS UNLIMITED 
 BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NOPARALLEL 
NESTED TABLE TEXT STORE AS RESULT_TEXT RETURN AS VALUE  
NESTED TABLE ATTRIBUTES STORE AS RESULT_ATTRIBUTES RETURN AS VALUE

目前正在分析的索引的 DDL：

-- Unable to render INDEX DDL for object O$LIMS.SYS_C0010496 with DBMS_METADATA attempting internal generator.
CREATE UNIQUE INDEX O$LIMS.SYS_C0010496 ON O$LIMS.N__RESULTS (ATTRIBUTES ASC) 
LOGGING 
TABLESPACE "SQLLIMS_RESULTS" 
PCTFREE 10 
INITRANS 2 
STORAGE 
( 
 INITIAL 65536 
 NEXT 1048576 
 MINEXTENTS 1 
 MAXEXTENTS UNLIMITED 
 BUFFER_POOL DEFAULT 
) 
NOPARALLEL

我的問題：

  1. 當 ANALYZE TABLE 最多只需要 1 小時時， ANALYZE INDEX 需要這麼長時間是否合理？
  2. 在這一點上我有什麼選擇？假設我殺了這個，我該如何繼續檢查（邏輯）損壞等？我可以做些什麼來讓這個命令在合理的時間內完成，或者我可以使用其他一些工具或方法嗎？

版本等資訊：

  • 主機：Windows 2003 伺服器，sp2
  • 甲骨文版本：10g r2 ps3 10.2.0.4.0
  • 兼容性：10.2.0.3.0

以下是相關的 SGA 參數（我認為）：

  • sga_target: 7012876288 (6688M)
  • sga_max_size: 10015997952 (9552M)
  • db_block_size: 8192
  • db_cache_size: 2885681152 (2752M)
  • db_*k_cache_size：全部為零

我認為 Raj 引用的文章 ( https://www.pythian.com/blog/analyze-index-validate-structure-dark-side/ ) 很好地描述了這一點。“分群因子”也是我在閱讀問題描述時的第一個猜測。我也更喜歡使用 RMAN 來檢查損壞情況。

RMAN> backup check logical validate database;

之後，您可以查詢V$DATABASE_BLOCK_CORRUPTION任何損壞塊的詳細資訊。

通常有一個巨大的集群的原因。您可以檢查以下詳細資訊：

  • 索引有多少行？
  • 它消耗了多少塊？

這可以幫助您確定 clustering_factor 是否很高。您還可以查詢dba_extents以確定 clustering_factor。

  • 表空間使用什麼類型的管理？我想這不是 ASSM，因為數據庫很舊並且升級了好幾次。

為什麼集群對性能不利？當 Oracle 從磁碟或記憶體中讀取數據時，它總是按塊讀取。如果塊是半空的，你會失去 50% 的閱讀性能。如果您進行全表或全索引掃描，Oracle 會掃描屬於該段（索引或表）的所有塊。它不檢查塊是否為空。Oracle 從第一個塊讀取到最後一個塊 (HWM)。如果您的索引有 1000 萬個塊，但它只需要 100 萬個塊，那麼 Oracle 會讀取 900 萬個垃圾塊。

ASSM（自動段空間管理）有助於減少/防止集群。如果可能，您應該將 Manual Segment Space Managed Tbs 遷移到 ASSM Tbs。

為什麼要檢查腐敗？我總是通過從生產數據庫（不是測試/開發數據庫）收集 ASM 或 statspack 報告來開始分析數據庫。這不會傷害任何人，並且會為您提供有關數據庫的許多詳細資訊，並且是小秘密。損壞通常不會影響性能，因為數據庫不會嘗試修復它——它只會使目前查詢崩潰。

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

相關問答

Oracle

計數很慢 Oracle

  • October 19, 2016
檢視問答
Oracle

更有效地使用聯合

  • December 2, 2015
檢視問答
Oracle

數據載入到 Oracle 中的速度異常緩慢

  • January 2, 2015
檢視問答
Oracle

如何知道哪個程序導致 DB 上的記憶體使用率高？

  • June 4, 2014
檢視問答
Oracle

為 Oracle 數據庫生成範例數據

  • October 31, 2011
檢視問答
Oracle

有人可以幫助解釋為什麼 DBA 會反對數據庫集群的細節嗎？

  • August 9, 2022
檢視問答