performance problem

From: Geoffrey van Heerde <gheerde_at_dstm.nl>
Date: Mon, 18 Jun 2001 12:27:26 +0200
Message-ID: <9gkl5t$1bs6$1@scavenger.euro.net>

Hi,

A customer has a performance problem with an interface. Typically each day hundreds to thousands of records are inserted in several interface tables, processed, and deleted. The processing (and the deletes) are handled by a PL/SQL procedure that is started by an Oracle job. For all records to-be-processed (in several interface tables) a key is stored in one central table r5interface.
Running a trace showed that the bad performance could be attributed to one update and several delete statements: most deletes were accessing the table via the primary key and for each record to be deleted about 7000 blocks were read from disk.

Earlier the deletes were no problem but only the update statement (also requiring about 7000 blocks). Recently more and more deletes are growing to the '7000 blocks limit'?! So the problem with the update seems to spread to other statements.

Can anyone give a clue where to look for the real cause of this problem?

Some background info:
The customer is running Oracle 8.0.6 on a Siemens UNIX box. Still rule based (no statistics). Due to the many inserts and deletes the extents will be rather scarvely populated.

Some tkprof output to illustrate. First the update statement:

UPDATE R5ARCTRACKDATA
SET ATD_STATUS='D'
WHERE ATD_STATUS != 'P'
AND ATD_TRANSORGID = :b1
AND NOT EXISTS (SELECT NULL FROM R5INTERFACE WHERE INT_KEYFLD4 = :b1 )

Note: the table r5arctrackdata is still empty - functionality is usused so far; column int_keyfld4 is either null or unique so I would expect that this statement is fast. With some trial-and-error I removed the not exists clause (a workaround for as long as the related functionality remains unused) and behold! all performance problems had disappeared! I also tried to rebuild the index on column int_keyfld4 but this did not yield a noticeable improvement.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 0 0.00 0.00 0 0 0 0
Execute 396 667.22 746.99 2774264 2787198 1188 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 396 667.22 746.99 2774264 2787198 1188 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 29 (MP5PROD) (recursive depth: 2)

Rows Execution Plan
------- ---------------------------------------------------

      0  UPDATE STATEMENT   GOAL: CHOOSE
      0   UPDATE OF 'R5ARCTRACKDATA'
      0    FILTER
      0     TABLE ACCESS (BY INDEX ROWID) OF 'R5ARCTRACKDATA'
      0      INDEX (UNIQUE SCAN) OF 'R5PRIK_ATD' (UNIQUE)
      0     INDEX (RANGE SCAN) OF 'R5IDX2_INT' (NON-UNIQUE)

****************************************************************************

Now one of the deletes:

DELETE FROM R5PARTINTERFACE
WHERE
PIN_TRANSID = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 0 0.00 0.00 0 0 0 0
Execute 15 25.39 32.57 105057 105676 278 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 15 25.39 32.57 105057 105676 278 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 29 (MP5PROD) (recursive depth: 2)

Rows Execution Plan
------- ---------------------------------------------------

      0  DELETE STATEMENT   GOAL: CHOOSE
      0   DELETE OF 'R5PARTINTERFACE'
      0    INDEX (UNIQUE SCAN) OF 'R5PRIK_PIN' (UNIQUE)

****************************************************************************

Starting up ORACLE RDBMS Version: 8.0.6.0.0. System parameters with non-default values:

  processes                = 100
  timed_statistics         = TRUE
  shared_pool_size         = 50000000
  enqueue_resources        = 1000
  control_files            = /oracle/mp5/oradata1/control01.ctl,
/oracle/mp5/oradata3/control02.ctl, /oracle/mp5/oradata4/control03.ctl
  db_block_buffers         = 25000
  db_block_size            = 8192
  compatible               = 8.0.5.0.0
  log_archive_start        = TRUE
  log_archive_dest         = ?/oraarch/mp5arch
  log_buffer               = 32768
  log_checkpoint_interval  = 0
  log_checkpoint_timeout   = 3600
  db_files                 = 80
  db_file_multiblock_read_count= 8
  dml_locks                = 200
  rollback_segments        = r01, r02, r03, r04

sequence_cache_entries = 30
sequence_cache_hash_buckets= 23

  global_names             = FALSE
  sort_area_size           = 262144
  sort_direct_writes       = FALSE
  db_name                  = mp5
  open_cursors             = 256
  ifile                    = /oracle/mp5/dbs/configmp5.ora
  job_queue_processes      = 8
  job_queue_interval       = 60
  job_queue_keep_connections= TRUE
  background_dump_dest     = /oracle/mp5/admin/bdump
  user_dump_dest           = /oracle/mp5/admin/udump
  max_dump_file_size       = 10240
  core_dump_dest           = /oracle/mp5/admin/cdump

Received on Mon Jun 18 2001 - 05:27:26 CDT