Slow update statement

Question

we are dealing with a very slow update statement in an Oracle project.

Here is a little script to solve the problem:

drop table j_test;

CREATE TABLE J_TEST
(
  ID  NUMBER(10) PRIMARY KEY,
  C1   VARCHAR2(50 BYTE),
  C2   VARCHAR2(250 BYTE),
  C3   NUMBER(5),
  C4   NUMBER(10)
);

-- just insert a bunch of rows
insert into j_test (id)
select rownum 
from <dummy_table>
where rownum < 100000;

-- this is the statement that runs forever (longer than my patience allows)
update j_test
set C3 = 1,
    C1 = 'NEU';    

There are several environments in which Update-Statement takes about 20 seconds, and some of them work for several minutes. When using more lines, the problem becomes even worse.

We do not know what causes this behavior, and we would like to have an idea of ​​what is happening before proposing a solution.

Any ideas or suggestions? thanks Thorsten

Answer 1

. C3 C4 null, , . , Oracle .

, , :

CREATE TABLE J_TEST
(
  ID  NUMBER(10) PRIMARY KEY,
  C1   VARCHAR2(50 BYTE),
  C2   VARCHAR2(250 BYTE),
  C3   NUMBER(5),
  C4   NUMBER(10)
) PCTFREE 40;

... PCTFREE . - 10, , ( 8 16 db).

, :

SQL> CREATE TABLE J_TEST
  2  (
  3    ID  NUMBER(10) PRIMARY KEY,
  4    C1   VARCHAR2(50 BYTE),
  5    C2   VARCHAR2(250 BYTE),
  6    C3   NUMBER(5),
  7    C4   NUMBER(10)
  8  );

Table created.

SQL> insert into j_test (id)
  2  select rownum 
  3  from transactions
  4  where rownum < 100000;

99999 rows created.

SQL> update j_test
  2  set C3 = 1,
  3      C2 = 'NEU'
  4  /

99999 rows updated.

Elapsed: 00:01:41.60

SQL> analyze table j_test compute statistics;

Table analyzed.

SQL> select blocks, chain_cnt from user_tables where table_name='J_TEST';

    BLOCKS  CHAIN_CNT
---------- ----------
       694      82034

SQL> drop table j_test;

Table dropped.

SQL> CREATE TABLE J_TEST
  2  (
  3    ID  NUMBER(10) PRIMARY KEY,
  4    C1   VARCHAR2(50 BYTE),
  5    C2   VARCHAR2(250 BYTE),
  6    C3   NUMBER(5),
  7    C4   NUMBER(10)
  8  ) PCTFREE 40;

Table created.

SQL> insert into j_test (id)
  2  select rownum 
  3  from transactions
  4  where rownum < 100000;

99999 rows created.

SQL> update j_test
  2  set C3 = 1,
  3      C2 = 'NEU'
  4  /

99999 rows updated.

Elapsed: 00:00:27.74

SQL> analyze table j_test compute statistics;

Table analyzed.

SQL> select blocks, chain_cnt from user_tables where table_name='J_TEST';

    BLOCKS  CHAIN_CNT
---------- ----------
       232          0

, PCTFREE 40 27 81 , 232 694 82034 !

Answer 2

:

insert into j_test (id, C3, C4)
select rownum, 1, 'NEU'
from <dummy_table>
where rownum < 100000;

Answer 3

C4 NUMBER (10) "NEU"?

, :

UPDATE j_test
   SET c3 = 3
 WHERE c1 = 'NEU'

, . , . , , ..

, , PCTFREE, , .

Answer 4

, , "NEU" Number (10)? " " "NEU" (??) .

, - , 100k .

- /.

Answer 5

.

100% . . " " , . . .

EDIT: If this seems like a bad idea to some of you, just know that this is the technique recommended by Tom Keith.

Answer 6

Another possibility is that one UPDATE is waiting because the table is locked (for example, there is another uncommitted UPDATE in the table)
This link contains an SQL statement to display locks