Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Location
    Istanbul
    Posts
    7

    Red face Unanswered: insert performance keeps dropping

    Hi there,
    I have worked for Sybase as a Performance and Tuning consultant. Very experienced at Sybase and generally at the relational theory. But my Oracle experience is near zero.
    Here I have a problem for which I think I must be missing something very basic:
    I have a rather plain, simple table with a numerical primary key and no other indexes. I have a client written in java and doing nothing but executing a prepared statement in a loop filling a few columns with random values. I am keeping time, say at every 1000 records batch and calculate a "records per second" rate.
    The client and server are on seperate PC's running on w2k.
    My problem is that, the rate decreases as # of records in the table increase. For example the average time for first 10 batches is around 300 records per second. at around 100th batch (that is 100,000 records are in the table) the rate is roughly 160. at 200,000 records the rate is 100. And finally after million records it drops to 30. At 2,000,000 it is 20. Keeps dropping.
    That clashes with all I know about B-Tree indexes as I would expect, the rate should be stabilized after a reasonable number of records was inserted like a few thousand records. Because index tree splits should occur rarely after that.
    Maybe it's nothing to do with indexes? Could you comment.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Are you doing any COMMIT?
    Does performance change with /* APPEND */ hint?

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    what about referential keys against that table?
    the more foreign key relations, the more checks oracle has to do before insert.

    if you are not loading in bulk, then perhaps you should look into
    whether you are binding variables and if you are parsing once, executing many times or not.

    run your loop and trace the session.
    tkprof the trace file and check the results.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: insert performance keeps dropping

    I wonder if this posting on AskTom is relevant?

    http://asktom.oracle.com/pls/ask/f?p...:3679447698936

  5. #5
    Join Date
    Dec 2003
    Location
    Istanbul
    Posts
    7
    - for committing, I have two runs that results the same pattern: first w/autocommit which commits after every insert, second w/manual commit at the end of the batch. the second is order of magnitude faster than the first but slowdown behaviour is constant.
    - no referential keys, referring or referred.
    - the article at asktom should be a different story. it both reads and writes database, CLOBs are involved and it is a procedure that is running in the same server. there may be many other factors within that case.
    However, I am happy to learn these things "tkprof", and "asktom" existed. I may require a few days for getting grips on these and may return with my findings.
    Many thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What happens when you use the /*+ APPEND */ hint on the INSERT?

  7. #7
    Join Date
    Dec 2003
    Location
    Istanbul
    Posts
    7
    Originally posted by anacedent
    What happens when you use the /*+ APPEND */ hint on the INSERT?
    i will check for that tomorrow

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I now this sounds really daft, you are putting sequential values into the primary key column and not random values? If random, it's possible the slowdown is actually multiple failures to insert rather than raw insert performance?

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  9. #9
    Join Date
    Dec 2003
    Location
    Istanbul
    Posts
    7
    Originally posted by billm
    Hi,

    I now this sounds really daft, you are putting sequential values into the primary key column and not random values? If random, it's possible the slowdown is actually multiple failures to insert rather than raw insert performance?

    Hth
    Bill
    The table is:
    create table TICK (
    SUBJECT_ID INT not null,
    SEQUENCE_ID NUMBER(14) not null,
    VALUE REAL,
    STATUS CHAR default ' ',
    constraint PK_TICK primary key (SUBJECT_ID, SEQUENCE_ID)
    );

    the SUBJECT_ID is a random number from 0 to 1,000,000,000-1 and SEQUENCE_ID is the number of milliseconds since 1.1.1970. The VALUE and STATUS are random generated as well.
    I don't ignore errors. Also it is a quite tiny possibility that within the same milisecond the system generates the same 9 figure random number.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    Good question Bill.

    use an oracle sequence number for the subject_id instead of a random number and let's see if there is any difference.

    heck, why not use two sequences for both?

    why do you need to use Epoch time?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Aug 2001
    Posts
    66
    Suspect this post may be of interest also.

    http://www.jlcomp.demon.co.uk/faq/slowdown.html
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

Posting Permissions

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