性能问题仅适用于生产环境数据库-Java 学习之路

在查询生产环境数据库中的表时遇到一些性能问题 . 虽然查询在测试数据库中运行2.1秒（返回2800万条记录中的8640条），但在生产环境时，需要2.05分钟（返回3100万条记录中的8640条） . 我很难找到问题因为我不是oracle专家 . 由于两个数据库中的解释计划都显示了正确的索引用法，我倾向于认为问题在于表/索引的创建 . 我注意到用于表创建的SQL脚本之间存在一些细微差别：

测试数据库：

create table TB_PONTO_ENE
(
  cd_ponto          NUMBER(10) not null,
  cd_fonte          NUMBER(10),
  cd_medidor        NUMBER(10),
  cd_usuario        NUMBER(10),
  dt_hr_insercao    DATE,
  dt_hr_instante    DATE not null,
  dt_hr_hora        DATE,
  dt_hr_dia         DATE,
  dt_hr_mes         DATE,
  dt_hr_instante_hv DATE,
  dt_hr_hora_hv     DATE,
  dt_hr_dia_hv      DATE,
  dt_hr_mes_hv      DATE,
  vl_eneat_del      FLOAT,
  vl_eneat_rec      FLOAT,
  vl_enere_del      FLOAT,
  vl_enere_rec      FLOAT,
  vl_eneat_del_cp   FLOAT,
  vl_eneat_rec_cp   FLOAT,
  vl_enere_del_cp   FLOAT,
  vl_enere_rec_cp   FLOAT
)
tablespace TELEMEDICAO
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

alter table TB_PONTO_ENE
  add constraint CP_TB_PONTO_ENE primary key (CD_PONTO, DT_HR_INSTANTE)
  using index 
  tablespace TELEMEDICAO
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table TB_PONTO_ENE
  add constraint CE_PENE_CD_FONTE foreign key (CD_FONTE)
  references TB_FONTE (CD_FONTE) on delete set null;
alter table TB_PONTO_ENE
  add constraint CE_PENE_CD_MEDIDOR foreign key (CD_MEDIDOR)
  references TB_MEDIDOR (CD_MEDIDOR) on delete set null;
alter table TB_PONTO_ENE
  add constraint CE_PENE_CD_PONTO foreign key (CD_PONTO)
  references TB_PONTO (CD_PONTO) on delete cascade;
alter table TB_PONTO_ENE
  add constraint CE_PENE_CD_USUARIO foreign key (CD_USUARIO)
  references TB_USUARIO (CD_USUARIO) on delete set null
  disable;

生产环境数据库：

create table TB_PONTO_ENE
(
  cd_ponto          NUMBER(10) not null,
  cd_fonte          NUMBER(10),
  cd_medidor        NUMBER(10),
  cd_usuario        NUMBER(10),
  dt_hr_insercao    DATE,
  dt_hr_instante    DATE not null,
  dt_hr_hora        DATE,
  dt_hr_dia         DATE,
  dt_hr_mes         DATE,
  dt_hr_instante_hv DATE,
  dt_hr_hora_hv     DATE,
  dt_hr_dia_hv      DATE,
  dt_hr_mes_hv      DATE,
  vl_eneat_del      FLOAT,
  vl_eneat_rec      FLOAT,
  vl_enere_del      FLOAT,
  vl_enere_rec      FLOAT,
  vl_eneat_del_cp   FLOAT,
  vl_eneat_rec_cp   FLOAT,
  vl_enere_del_cp   FLOAT,
  vl_enere_rec_cp   FLOAT
)
tablespace TELEMEDICAO
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

alter table TB_PONTO_ENE
  add constraint CP_TB_PONTO_ENE primary key (CD_PONTO, DT_HR_INSTANTE)
  using index 
  tablespace MEDICAO_NDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
alter table TB_PONTO_ENE
  add constraint CE_PENE_CD_FONTE foreign key (CD_FONTE)
  references TB_FONTE (CD_FONTE) on delete set null;
alter table TB_PONTO_ENE
  add constraint CE_PENE_CD_MEDIDOR foreign key (CD_MEDIDOR)
  references TB_MEDIDOR (CD_MEDIDOR) on delete set null;
alter table TB_PONTO_ENE
  add constraint CE_PENE_CD_PONTO foreign key (CD_PONTO)
  references TB_PONTO (CD_PONTO) on delete cascade;
alter table TB_PONTO_ENE
  add constraint CE_PENE_CD_USUARIO foreign key (CD_USUARIO)
  references TB_USUARIO (CD_USUARIO) on delete set null;

生产环境数据库将索引放在另一个表空间中 . 另一个区别是表空间声明中的 next 5M （测试数据库中没有定义值） .

在查看索引属性时，我也看到了一些差异：

测试数据库：

AVG_DATA_BLOCKS_PER_KEY 1
AVG_LEAF_BLOCKS_PER_KEY 1
BLEVEL 2
BUFFER_POOL 默认
CLUSTERING_FACTOR 611494
COMPRESSION 禁用
DEGREE 1
DISTINCT_KEYS 28568389
DROPPED 没有
GENERATED N.
GLOBAL_STATS 是的
INDEX_NAME CP_TB_PONTO_ENE
INDEX_TYPE 正常
INITIAL_EXTENT 65536
INI_TRANS 2
INSTANCES 1
IOT_REDUNDANT_PKEY_ELIM 没有
JOIN_INDEX 没有
LAST_ANALYZED 21/07/2010 22:08:34
LEAF_BLOCKS 85809
LOGGING 是的
MAX_EXTENTS 2147483645
MAX_TRANS 255
MIN_EXTENTS 1
NUM_ROWS 28568389
PARTITIONED 没有
PCT_FREE 10
SAMPLE_SIZE 377209
SECONDARY N.
STATUS 有效
TABLESPACE_NAME TELEMEDICAO
TABLE_NAME TB_PONTO_ENE
TABLE_TYPE 表
TEMPORARY N.
UNIQUENESS 独特
USER_STATS 没有

生产环境数据库：

AVG_DATA_BLOCKS_PER_KEY 1
AVG_LEAF_BLOCKS_PER_KEY 1
BLEVEL 2
BUFFER_POOL 默认
CLUSTERING_FACTOR 10154395
COMPRESSION 禁用
DEGREE 1
DISTINCT_KEYS 14004395
GENERATED N.
GLOBAL_STATS 是的
INDEX_NAME CP_TB_PONTO_ENE
INDEX_TYPE 正常
INITIAL_EXTENT 65536
INI_TRANS 2
INSTANCES 1
JOIN_INDEX 没有
LAST_ANALYZED 05/03/2010 08:45:19
LEAF_BLOCKS 42865
LOGGING 是的
MAX_EXTENTS 2147483645
MAX_TRANS 255
MIN_EXTENTS 1
NEXT_EXTENT 1048576
NUM_ROWS 14004395
PARTITIONED 没有
PCT_FREE 10
PCT_INCREASE 0
SAMPLE_SIZE 2800879
SECONDARY N.
STATUS 有效
TABLESPACE_NAME MEDICAO_NDX
TABLE_NAME TB_PONTO_ENE
TABLE_TYPE 表
TEMPORARY N.
UNIQUENESS 独特
USER_STATS 没有

我还注意到另外两件事： select count(*) from thetable 的解释计划显示索引在测试数据库中使用，但在生产环境数据库中显示完整的表扫描 . 这引出了另一个观察：测试数据库索引有160MB，生产环境数据库有超过1GB（我们不对此表进行删除） . 有谁能指出我的解决方案？

UPDATE

以下是执行计划：

测试数据库：

Execution Plan
----------------------------------------------------------
Plan hash value: 1441290166

-------------------------------------------------------------------------------------

| Id  | Operation             | Name                | Rows  | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                     |     1 | 18767   (4)| 00:03:46 |

|   1 |  SORT AGGREGATE       |                     |     1 |            |          |

|   2 |   INDEX FAST FULL SCAN| IDX_HV_TB_PONTO_ENE |    28M| 18767   (4)| 00:03:46 |

-------------------------------------------------------------------------------------



Statistics
----------------------------------------------------------
        111  recursive calls
          0  db block gets
      83586  consistent gets
      83533  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

生产环境数据库

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TB_PONTO_ENE'




Statistics
----------------------------------------------------------
          1  recursive calls
          3  db block gets
     605327  consistent gets
     603698  physical reads
        180  redo size
        201  bytes sent via SQL*Net to client
        242  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

UPDATE 2

生产环境服务器正在运行Oracle 9.2.0 .

UPDATE 3

以下是优化程序模式设置为 CHOOSE 时执行的统计信息：

SQL> SELECT dt_hr_instante, vl_eneat_del,vl_eneat_rec,vl_enere_del, vl_enere_rec  FROM tb_ponto_ene WHERE cd_ponto = 31 AND dt_hr_instante BETWEEN to_date('01/06/2010 00:05:00','dd/mm/yyyy hh24:mi:ss') AND  to_date('01/07/2010 00:00:00', 'dd/mm/yyyy hh24:mi:ss');

8640 rows selected.

Elapsed: 00:01:49.51

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=36)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TB_PONTO_ENE' (Cost=4 Card=1 Bytes=36)
   2    1     INDEX (RANGE SCAN) OF 'CP_TB_PONTO_ENE' (UNIQUE) (Cost=3 Card=1)




Statistics
----------------------------------------------------------
        119  recursive calls
          0  db block gets
       9169  consistent gets
       7438  physical reads
          0  redo size
     308524  bytes sent via SQL*Net to client
       4267  bytes received via SQL*Net from client
        577  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
       8640  rows processed

2 回答

2

Test数据库索引属性包括IOT_REDUNDANT_PKEY_ELIM和DROPPED列，但不包括生产环境索引 . 这些列在oracle 10g中添加 .

也许生产环境数据库在旧的9i版本和10g以下的测试数据库下运行？如果是这样，我会认为这比其他任何事情都要重要 .

也就是说，如果“ select count(*) from thetable ”没有使用主键索引，那就很奇怪了 . 指数统计数据非常过时（如果您建议超过3000万行，最后一次在3月收集，则为14,004,395行） . 如果该表在过去六个月中的尺寸增加了一倍，并且其统计数据甚至更旧，则可能是一个问题 .

回复于 2024-04-29T09:27:50+08:00
0

生产环境的autotrace计划是"RULE"优化器 . 如果您查看Oracle Tuning document (9i)部分RBO路径15：全表扫描，它将清楚地表明将使用全表扫描 .

回复于 2024-04-29T09:27:50+08:00

性能问题仅适用于 生产环境 数据库

2 回答

相关问题

性能问题仅适用于生产环境数据库