Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    4

    Unanswered: Slow Concurrent inserts

    I have a table of row length = 500 Bytes with one unique index
    which is based on a sequence.The table is presently empty.
    I am trying to get some performance nos.
    I have a script that does inserts into this table.
    When the script runs alone i get about 150 inserts per second.
    With 5 concurrent executions of the script i get about 30/sec.
    What can i do to improve the speed for concurrent inserts ??

    On TOAD i see a lot of row exclusive locks on the table..
    How do i find out if the lock is on the table or index ??
    How can i reduce contention on index pages..

    Thanks in advance
    Raghu
    Last edited by vrraghuraman; 09-14-02 at 08:38.

  2. #2
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    To improve the speed and reduce the index contention you can check the v$session_wait to find out if there are any "buffer busy wait" events. If so you can try to reduce the number of rows per block by increasing PCTFREE, reduce PCTUSED, increase INITRANS, increase FREELISTS.

    If that doesn't help then changing the db block size might be a option. Also find out which are the busiest tables and configure them to be kept in the keep pool.


    Hope that helps,

    clio_usa
    OCP - DBA

    .
    .
    .

  3. #3
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: Slow Concurrent inserts

    You can hit many problems, some of them are:
    1. incorrect coding of the application
    2. not cached sequence, lock contention involving key generation
    3. index block dml contention
    4. freelist contention (both index and table)
    5. redo log latch or lgwr IO problems or CPU too busy or ... endless

    Reducing data density leads the db to do more IOs, when you have to read the table: pay attention to find the exact problem so you can put in place the right solution.

    Keep in mind that concurrent inserts towards different blocks (using different freelists) leads to a poorly clustered index, so CBO will probably not use the index for scans, bu only for key lookup.

    If it's a freelist contention or a block contention, You can afford the load with this storage options -recreate objects-:
    (FREELIST GROUPS 2 FREELISTS k INITRANS k)
    where k is the max degree of concurent inserts (5 in your test).

    If you can provide more information about your batch
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

  4. #4
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: Slow Concurrent inserts

    in addition to the current replies: you can always use bulkinsert to speed up.

    if you were not familiar with bulk insert, have a look at the following pl/sql coding sample:

    drop table parts;
    CREATE TABLE parts (pnum NUMBER, pname varCHAR2(15));

    create or replace
    PROCEDURE get_time (t OUT NUMBER) IS
    BEGIN
    SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual;
    END get_time;
    /
    show errors;

    create or replace procedure bulktest (pMax in integer default 10000) is
    TYPE NumTab IS TABLE OF parts.pnum%type INDEX BY BINARY_INTEGER;
    TYPE NameTab IS TABLE OF parts.pname%type INDEX BY BINARY_INTEGER;
    Type PartTab is table of parts%rowtype; -- index by binary_integer;
    vNums NumTab;
    vNames NameTab;
    vParts PartTab;
    vPart parts%rowtype;
    t1 NUMBER(5);
    t2 NUMBER(5);
    t3 NUMBER(5);
    t4 number;
    ii binary_integer;
    jj binary_integer;
    BEGIN
    FOR jj IN 1..pMax LOOP -- load index-by tables
    vNums(jj) := jj;
    vNames(jj) := 'Part No. ' || TO_CHAR(jj);
    vPart.pnum := jj;
    vPart.pname:= vNames(jj);
    vParts(jj) := vPart;
    END LOOP;
    get_time(t1);
    FOR ii IN 1..pMax LOOP -- use FOR loop
    INSERT INTO parts VALUES (vNums(ii), vNames(ii));
    END LOOP;
    get_time(t2);

    --/******
    -- you need ora81 for this ...
    FORALL ii IN 1..pMax -- use FORALL statement
    INSERT INTO parts VALUES (vNums(ii), vNames(ii));
    --******/
    get_time(t3);

    FOR ii IN 1..pMax LOOP -- use FOR loop
    INSERT INTO parts VALUES (vParts(ii).pNum, vParts(ii).pName);
    END LOOP;DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
    DBMS_OUTPUT.PUT_LINE('---------------------');
    DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));
    DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR(t3 - t2));
    END bulktest;
    /
    show errors;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •