1

There was a situation of 108,368,168 executions along with 7,373s elapsed time for "update seq$ set increment$=:........" statement while loading massive(i.e. populating million rows) data in the table. It means - it definitely spending some fraction of the time during each call to acquire the latch to be able to update the data dictionary when a new set of values need to be generated.

I diagnose the situation and found that it was happening because of Sequence NOCACHE and started to increase its CACHE value until it's not included in the SQL ordered by CPU/Elepsed in AWR report, finally it's settled when CACHE value set to 100000. Now there is no query in AWR related to Sequence and concluded that CACHE value is given positive performance gain.

Can anyone tell me what are the items I can check in Oracle to confirm such a large sequence cache is not back fired in the database.

Improve this question
2
  • don't suppose you can use SYS_GUID() for your primary key? No caching required then...
    – kevinskio
    Commented Jan 15, 2015 at 13:37
  • Understand the relative benefits of Guids, both sequence and GUID have their own pros and cons. I am not convinced to use guid in my context with following proc/cons: Cons-SYS_GUID is a RAW(16) taking up 16 bytes, whereas as an ID populated by a sequences typically is a NUMBER 6/9/12. A reasonable average is NUMBER(9) which takes up only 4 bytes. Cons - The round trip between Oracle and the client for every Guid is likely slower. Pros-GUID gives benefits with primary keys, especially clustered primary keys benefit from sequential data because the algorithms are optimised to avoid rebalancing.
    – Suresh Gautam
    Commented Jan 16, 2015 at 4:36

1 Answer 1

Reset to default
1

No it wont backfire. If there is confusion, you can read the manual to see the effect of caching. It means first time you select from sequence, it will cache next 999999 values in sga. It also means, for next 999999 sequence nextval calls you will not be updating seq$ table.

The downside of this is, if your instance or db crashes, those cached (and unused) values are lost. So if you need to have sequential values with no sequence loss, this will not be helpful. Along with caching, you perhaps want to use ORDER option to ensure sequences will be distributed in a ORDERED manner.

Improve this answer
2
  • 1
    It's not only when the instance crashes. Even if you cleanly shutdown the instance the cached values will be gone.
    – user1822
    Commented Jan 15, 2015 at 14:19
  • In addition to what was already said, yes you can lose cached values, but since generating sequence values over millions of rows can increase contention having a high cache value can help. It should not matter if there are gaps in the sequence values in the table. But you would be better off finding a natural key for your table as it will better scale to a higher volume of data.
    – Gandolf989
    Commented Jan 15, 2015 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.