主页 / dba / 问题 / 138503
Accepted
RBarryYoung
Asked: 2016-05-14 10:08:25 +0800 CST

ANALYZE TABLE..VALIDATE STRUCTURE 永远运行

  • 772

因此，正如我之前提到的，我是一名 SQL Server DBA/顾问，正在帮助客户使用他们的 Oracle 数据库。大多数情况下，我已经能够弄清楚我需要做什么，并在文档和谷歌的帮助下完成工作，但我目前正在尝试处理一些似乎难以理解的事情。

其中一个数据库是 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;第一个索引永远不会完成。

我决定让它运行一段时间，看看我是否能找到一种方法来监控它的进度。谷歌搜索我发现许多（旧的）声称可以监控 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：全部为零
oracle performance

2 个回答

  1. 更新：什么有效...

    因此，在阅读了@Raj 的链接并阅读了@ora-600 的答案后，我尝试使用 RMAN 命令验证数据库backup check logical validate database;。虽然这很好用，但很明显它并没有查看ANALYZE INDEX命令会查看的所有内容。

    在尝试了许多不同的变体之后，我终于发现这个命令可以工作：

    ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE offline;

    是的，只是切换到离线似乎可以解决它。这最终导致我在这个页面上发现了这个错误# 5752105：http : //www.eygle.com/case/10204_buglist.htm 。我现在无法证明这一点（暂时无法应用任何补丁），但我强烈怀疑这就是我遇到的问题。

    因此，虽然问题没有完全回答，但我会将@ora-600 非常有帮助的答案标记为正确，以便他可以收集 Paul White 非常慷慨的赏金。

    • 3
  2. Best Answer

    我认为 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 报告来开始分析数据库。这不会伤害任何人，并且会为您提供有关数据库的许多详细信息，并且是小秘密。损坏通常不会影响性能，因为数据库不会尝试修复它——它只会使当前查询崩溃。

    • 2

相关问题