Re: performance problem

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Thu, 21 Jun 2001 12:40:08 +0200
Message-ID: <3B31CF08.6AA5790@0800-einwahl.de>

Hi Geoffrey,

this reads as if you were doing in a 396 row loop the update statement. As far as I can see the not exists is not related to the data to be updated. If you have a unique index on r5interface (int_keyfld4) (which you should have according by your statement down) ou can use an updatable subquery with

UPDATE (
select d.ATD_STATUS
from R5ARCTRACKDATA d, R5INTERFACE i
WHERE d.ATD_STATUS != 'P'

AND d.ATD_TRANSORGID = :b1
and i.INT_KEYFLD4 (+) = d.ATD_TRANSORGID
and i.int_keyfld4 is null	-- antijoin

) x
set x.ATD_STATUS='D'
/

However, I saw from your execution plan that the index R5IDX2_INT is non-unique. Why? If it can be null or unique you very well can create a unique index (but not a primary key). See my following example:

SQL>
SQL> create table r5arctrackdata (

  2  	     atd_status varchar2 (1)
  3  	     , atd_transorgid number

  4 )
  5 /

Table created.

SQL>
SQL> create unique index
  2 R5PRIK_ATD
  3 on r5arctrackdata (atd_transorgid)
  4 /

Index created.

SQL>
SQL> create table r5interface (
  2 int_keyfld4 number
  3 )
  4 /

Table created.

SQL>
SQL> create unique index
  2 R5IDX2_INT
  3 on r5interface (int_keyfld4)
  4 /

Index created.

SQL> 
SQL> variable b1 number
SQL> 
SQL> explain plan for

  2 UPDATE --+ rule
  3 (
  4 select d.ATD_STATUS
  5 from R5ARCTRACKDATA d, R5INTERFACE i   6 WHERE d.ATD_STATUS != 'P'

  7  AND d.ATD_TRANSORGID = :b1
  8  and i.INT_KEYFLD4 (+) = d.ATD_TRANSORGID
  9  and i.int_keyfld4 is null	     -- antijoin

 10 ) x
 11 set x.ATD_STATUS='D'
 12 /

Explained.

SQL> 
SQL> @plan
SQL> -- plan.sql for reading the execution plan stored in plan_table.
SQL> 
SQL> -- Martin Haltmayer, 17th July 1999
SQL> 
SQL> -- Last change:
SQL> 
SQL> set linesize 126
SQL> set pagesize 0
SQL> 
SQL> column id format 9999
SQL> column cardinality format 9g999g999g999
SQL> column operation format a78 wrap
SQL> column object format a32 wrap
SQL> 
SQL> select
  2  	     cardinality
  3  	     , lpad (' ', 2 * (level - 1)) || operation || decode (options, null,

null, ' (' || options || decode (other_tag, null, null, ' (' || other_tag || ')') || ')') as operation,
  4 decode (object_owner, null, null, decode (object_owner, user, null, '"' || object_owner || '".')) || decode (object_name, null, null, '"' || object_name || '"') as object
  5 from plan_table
  6 start with id = 1
  7 connect by prior id = parent_id
  8 ;

              
UPDATE                                                                        
"R5ARCTRACKDATA"
                 FILTER
                   NESTED LOOPS (OUTER)
                     TABLE ACCESS (BY INDEX
ROWID)                                            "R5ARCTRACKDATA"
                       INDEX (UNIQUE
SCAN)                                                    "R5PRIK_ATD"
                     INDEX (UNIQUE
SCAN)                                                      "R5IDX2_INT"

6 rows selected.

SQL>
SQL> delete from plan_table;

7 rows deleted.

SQL> 
SQL> -- exit
SQL> 
SQL> spool off

Martin

Geoffrey van Heerde wrote:
>
> 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 Thu Jun 21 2001 - 05:40:08 CDT