This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jun 2, 2008 1:20 AM by Jonathan Lewis RSS

a lot of INSERTs with incremented sequence causes performance degradation

user480682 Newbie
Currently Being Moderated
Hi,
can someone explain me how PK is verified (a lot of IOs)?
I've observed performance degradation when many inserts is done to 200mln rows table with huge PK index. Looks like whole index is read for pk veryfication , or maybe I'm wrong.
Database is 10.2.0.3 EE.
Regards
Grzegorz
  • 1. Re: a lot of INSERTs with incremented sequence causes performance degradati
    damorgan Oracle ACE Director
    Currently Being Moderated
    You should be wrong about the entire index being read.

    Other than the amount of time the insert is taking what leads you to think this and how are you measuring "a lot of IOs?"

    With 200M rows you, and in EE, you should have purchased the Partitioning Option: Did you? Is the table partitioned?
  • 2. Re: a lot of INSERTs with incremented sequence causes performance degradati
    user480682 Newbie
    Currently Being Moderated
    I've read that, whole pk index is read into db cache when a row is inserted.
    You re right about partitioning, that's first think which I've proposed but Im curious about theory behind pk validating.\Regards
    Grzegorz
  • 3. Re: a lot of INSERTs with incremented sequence causes performance degradati
    Justin Cave Oracle ACE
    Currently Being Moderated
    I've read that, whole pk index is read into db cache when a row is inserted.
    Where did you read that? Either the source was incorrect or you misread the information. Oracle does have to read a few blocks from the index to validate the primary key-- enough to ensure that the primary key isn't duplicated, but it certainly doesn't have to read the whole thing into memory every time there is an insert.

    Justin
  • 4. Re: a lot of INSERTs with incremented sequence causes performance degradation
    108476 Journeyer
    Currently Being Moderated
    Hi Grzegorz,

    Oracle uses a unique index to enforce a PK, and the overhead is about the same whether or not the unique index is used to enforce a primary key.

    You mentioned a sequence. Coud that be your issue? Are you using sequece caching?

    http://www.dba-oracle.com/t_sequence_caching.htm

    If this is a maintenance batch load, you might look at droppin indexes, loading, and rebuilding indexes/constraints after:

    http://www.dba-oracle.com/t_optimize_insert_sql_performance.htm

    --------------------

    Hope this helps. . .

    Donald K. Burleson
    Oracle Press author
    Author of "Oracle Tuning: The Definitive Reference":
    http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
  • 5. Re: a lot of INSERTs with incremented sequence causes performance degradation
    mbobak Oracle ACE
    Currently Being Moderated
    An index does not necessarily need to be unique to be used for PK enforcement.

    -Mark
  • 6. Re: a lot of INSERTs with incremented sequence causes performance degradati
    damorgan Oracle ACE Director
    Currently Being Moderated
    Minor correction: Oracle uses unique indexes to enforce primary keys ONLY if they are non-deferrable. Deferrable primary keys always use a non-unique index.
  • 7. Re: a lot of INSERTs with incremented sequence causes performance degradati
    311441 Employee ACE
    Currently Being Moderated
    Hi Daniel

    Of course Don is incorrect but a minor correction to your minor correction. Oracle can use a unique index or a non-unique index to enforce a PK if they're non-deferrable. Deferrable and non-validated PKs use a non-unique index.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 8. Re: a lot of INSERTs with incremented sequence causes performance degradation
    575729 Newbie
    Currently Being Moderated
    you need to check on the following grounds

    Did you cache your sequences. I usually cache 20
    did you have any row migration problem.
    are you indexing on the key which is genernated by sequence (yes i guess)
    then are you delete a lots of records before an insert ( if yes then)
    then you may need to rebuild your indexes after each delete.

    regards
    Nick
  • 9. Re: a lot of INSERTs with incremented sequence causes performance degradation
    108476 Journeyer
    Currently Being Moderated
    Hi Mark,

    The OP asked: "Can someone explain me how PK is verified?"

    You responded "An index does not necessarily need to be unique to be used for PK enforcement."

    Um, I've never seen a case where a "alter table . . . add constraint . . . primary key" does not create a unique index.
    ORA-02437: cannot validate <name> - primary key violated

    Cause: attempted to validate a primary key with duplicate values or null values.

    Action: remove the duplicates and null values before enabling a primary key.
  • 10. Re: a lot of INSERTs with incremented sequence causes performance degradati
    108476 Journeyer
    Currently Being Moderated
    Minor correction: Oracle uses unique indexes to enforce primary keys ONLY if they are non-deferrable.
    Fair enough. The OP sounded like a beginner, and I did not want to confuse them with TMI . . . .
  • 11. Re: a lot of INSERTs with incremented sequence causes performance degradation
    311441 Employee ACE
    Currently Being Moderated
    Um, I've never seen a case where a "alter table . . .
    add constraint . . . primary key" does not create a
    unique index.
    Hi Don
    SQL> create table bowie as select rownum id, 'BOWIE' text from dual connect by level <=1000;
      
    Table created.
      
    SQL> create index bowie_idx on bowie(id);
      
    Index created.
      
    SQL> select index_name, uniqueness from user_indexes where index_name = 'BOWIE_IDX';
      
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    BOWIE_IDX                      NONUNIQUE
     
    SQL> alter table bowie add constraint bowie_pk primary key (id);
     
    Table altered.
     
    SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.co
    nstraint_type from user_ind_columns ic, user_indexes i, user_constraints c where
    ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =
    'BOWIE_IDX';
     
    INDEX_NAME COLUMN_NAME  UNIQUENES CONSTRAINT_NAME                C
    ---------- ------------ --------- ------------------------------ -
    BOWIE_IDX  ID           NONUNIQUE BOWIE_PK                       P
    You have now :)

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 12. Re: a lot of INSERTs with incremented sequence causes performance degradation
    26741 Oracle ACE
    Currently Being Moderated
    ..... the rest of this thread seems to have gone on many different tracks
    (about uniqueness and PK definitions, constraints and deferred constraints etc).

    However, to answer your question , the issue would be the index, not the
    sequence.

    See http://www.jlcomp.demon.co.uk/faq/slowdown.html
  • 13. Re: a lot of INSERTs with incremented sequence causes performance degradati
    damorgan Oracle ACE Director
    Currently Being Moderated
    Let me show you another one:
    SQL> CREATE TABLE t (col NUMBER);

    Table created.

    SQL> ALTER TABLE t
      2  ADD CONSTRAINT pk_t
      3  PRIMARY KEY (col)
      4  INITIALLY DEFERRED DEFERRABLE;

    Table altered.

    SQL> SELECT index_name, index_type, uniqueness
      2  FROM user_indexes
      3  WHERE table_name = 'T';

    INDEX_NAME                     INDEX_TYPE                  UNIQUENES
    ------------------------------ --------------------------- ---------
    PK_T                           NORMAL                      NONUNIQUE

    SQL>
  • 14. Re: a lot of INSERTs with incremented sequence causes performance degradation
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    You responded "An index does not necessarily need to
    be unique to be used for PK enforcement."

    Um, I've never seen a case where a "alter table . . .
    add constraint . . . primary key" does not create a
    unique index.
    ORA-02437: cannot validate <name> - primary key
    violated

    Cause: attempted to validate a primary key with
    duplicate values or null values.

    Action: remove the duplicates and null values before
    enabling a primary key.
    Non-unique indexes supporting primary key (or just unique) constraints has been a possibility since at least 8i, and quoting an irrelevant error message doesn't change that fact.

    Remember, there's an important difference between "I've never seen it", and "It doesn't happen".

    Please try to bear that in mind the next time you get the urge to spout your silly comments about "empirical" DBAs.

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
1 2 Previous Next