Perfomance issues just on production database

I'm having some performance issues when querying a table in a production database. While the query runs in 2.1 seconds in the test database (returning 8640 of 28 million records), at production, it takes 2.05 minutes (returning 8640 of 31 million records). I'm having a hard time to find the problem since I'm not an oracle expert. Since the explain plan in both databases shows the correct index usage, I'm inclined to think that the problem resides on the table/indexes creation. I've noticed some small differences between the SQL scripts used for the table creation:

Test database:

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
开发者_StackOverflow中文版  disable;

Production database:

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;

The production database puts the indexes in another tablespace. Another difference is the next 5M at the tablespace declaration (no value defined in the test database).

When looking at the index properties, I also see some differences:

Test database:

• AVG_DATA_BLOCKS_PER_KEY 1
• AVG_LEAF_BLOCKS_PER_KEY 1
• BLEVEL 2
• BUFFER_POOL DEFAULT
• CLUSTERING_FACTOR 611494
• COMPRESSION DISABLED
• DEGREE 1
• DISTINCT_KEYS 28568389
• DROPPED NO
• GENERATED N
• GLOBAL_STATS YES
• INDEX_NAME CP_TB_PONTO_ENE
• INDEX_TYPE NORMAL
• INITIAL_EXTENT 65536
• INI_TRANS 2
• INSTANCES 1
• IOT_REDUNDANT_PKEY_ELIM NO
• JOIN_INDEX NO
• LAST_ANALYZED 21/07/2010 22:08:34
• LEAF_BLOCKS 85809
• LOGGING YES
• MAX_EXTENTS 2147483645
• MAX_TRANS 255
• MIN_EXTENTS 1
• NUM_ROWS 28568389
• PARTITIONED NO
• PCT_FREE 10
• SAMPLE_SIZE 377209
• SECONDARY N
• STATUS VALID
• TABLESPACE_NAME TELEMEDICAO
• TABLE_NAME TB_PONTO_ENE
• TABLE_TYPE TABLE
• TEMPORARY N
• UNIQUENESS UNIQUE
• USER_STATS NO

Production database:

• AVG_DATA_BLOCKS_PER_KEY 1
• AVG_LEAF_BLOCKS_PER_KEY 1
• BLEVEL 2
• BUFFER_POOL DEFAULT
• CLUSTERING_FACTOR 10154395
• COMPRESSION DISABLED
• DEGREE 1
• DISTINCT_KEYS 14004395
• GENERATED N
• GLOBAL_STATS YES
• INDEX_NAME CP_TB_PONTO_ENE
• INDEX_TYPE NORMAL
• INITIAL_EXTENT 65536
• INI_TRANS 2
• INSTANCES 1
• JOIN_INDEX NO
• LAST_ANALYZED 05/03/2010 08:45:19
• LEAF_BLOCKS 42865
• LOGGING YES
• MAX_EXTENTS 2147483645
• MAX_TRANS 255
• MIN_EXTENTS 1
• NEXT_EXTENT 1048576
• NUM_ROWS 14004395
• PARTITIONED NO
• PCT_FREE 10
• PCT_INCREASE 0
• SAMPLE_SIZE 2800879
• SECONDARY N
• STATUS VALID
• TABLESPACE_NAME MEDICAO_NDX
• TABLE_NAME TB_PONTO_ENE
• TABLE_TYPE TABLE
• TEMPORARY N
• UNIQUENESS UNIQUE
• USER_STATS NO

Two other things has come to my attention: the explain plan for select count(*) from thetable shows that the index is used at the test database, but shows a full table scan at the production database. Which led me to another observation: the test database index has 160MB and the production db has more than 1GB (and we don't do deletes on this table). Can anyone point me to the solution?

UPDATE

Here are the execution plans:

Test database:

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

Production database

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

The production server is running Oracle 9.2.0.

UPDATE 3

Here are the statistics for the execution with the optimizer mode set to 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

The Test database indexes properties include IOT_REDUNDANT_PKEY_ELIM and DROPPED columns but not the production indexes. These columns were added in oracle 10g.

Is perhaps the production database running under the old 9i version and the test database under 10g ? If so, I'd consider that a more significant difference than anything else.

That said if "select count(*) from thetable" is not using a primary key index it is very odd. The index stats are very out of date (14,004,395 rows when you suggest there's over 30 million and last gathered in March). If the table has doubled in size in the last six months, and its stats are even older, then it might be an issue.

The autotrace plan for production says "RULE" optimizer. If you look at the Oracle Tuning document (9i) section RBO Path 15: Full table scan it clearly states full table scan will be used.