oracle RAC 環境解決sequence 不一致問題
Sequences in Oracle 10g RAC
Just recently I got a call from a developer. He had a table with a primary key populated by a sequence, a timestamp column with the current date and some other columns. He had a specific set of data that, when ordered by the primary key had out of order timestamps. He was puzzled how this could be. This is a RAC database and the sequence was created with the default values.
Not only the sequences cache was the default of 20, but it was “noordered”. Being “noordered” Oracle will not guarantee the order in which numbers are generated.
Example of “noorder” sequence in 10g RAC:
Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104
The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot. I wonder why Oracle doesnt make “ordered” the default for sequences.
So I explained to the developer how sequences work in RAC and how each node has its own “cache”.
We changed the sequence to “ordered” and increased the cache to 1000. Now selecting on either node gets the next number as he expected. I warned him that there would be some performance implications due to cluster synchronization. Him been a responsive developer, asked me what would be the impact, so I tested it out.
How does RAC synchronize sequences?
In Oracle 10g RAC, if you specify the “ordered” clause for a sequence, then a global lock is allocated by the node when you access the sequence.
This lock acquisition happens only at the first sequence access for the node (A), and subsequent uses of the sequence do not wait on this lock. If another node (B) selects from that sequence, it requests the same global lock and once acquired it returns the sequences next value.
The wait event associated with this activity is recorded as “events in waitclass Other” when looked in gv$system_event. So much for event groups, it couldnt be more obscure. That view shows overall statistics for the session.
However if you look in the gv$session_wait_history it shows as “DFS lock handle” with the “p1″ parameter been the object_id of the sequence. This second view has a sample of the last 10 wait events for a session.
In a SQL_TRACE with waitevents (10046 trace) it will be a “DFS lock handle” but in AWR or statspack reports it will be “events in waitclass Other”. So much for consistency.
How does that change our example?
Session 1 on node-A: nextval -> 101 (DFS Lock handle) (CR read)
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 103 (DFS Lock handle)
Session 1 on node-B: nextval -> 104
Session 1 on node-A: nextval -> 105 (DFS Lock handle)
Session 1 on node-A: nextval -> 106
(more selects)
Session 1 on node-A: nextval -> 998
Session 1 on node-B: nextval -> 999 (DFS Lock handle)
Session 1 on node-B: nextval -> 1000 (CR read)
The cache size also has some RAC synchronization implications. When the cached entries for the sequence are exhausted, the sequence object needs to be updated. This usually causes a remote CR (current read) over the interconnect for the block that has the specific sequence object. So a bit more activity here.
Test case:
create sequence test_rac;
declare
dummy number;
begin
for i in 1..50000 loop
select test_rac.nextval into dummy from dual;
end loop;
end;
/
Results:
50 000 loops with cache = 20 (default)
1 node = 5 seconds
2 nodes at same time = 14 seconds
2 nodes at same time ordered = 30 seconds
50 000 loops with cache = 1000
1 node = 1.5 seconds
2 nodes at same time = 1.8 seconds
2 nodes at same time ordered = 20 seconds
With a smaller cache, the “noordered” still has as significant impact as every 10 fetches (cache 20 divided by 2 nodes fetching) it has to synchronize between the 2 nodes
The conclusion
By default sequences in 10g RAC are created without ordering. Beware of using applications that rely on sequences to be ordered and using it in a RAC environment.
Consider changing all user sequences to “ordered” as a precaution and increasing the cache size.
The default cache value is still very low and even not-ordered sequences will cause contention in a highly-active sequence even in non-RAC and causing an additional block exchange every 20 values in RAC.
For high volume insert operations where ordering is not performed on the value returned from the sequence, consider leaving the sequence “noordered” but increasing the cache size significantly.
Either way, the sequence parameters should be reviewed, as chances are, the defaults are not what you need.
I remember reading somewhere that in Oracle 9i the “ordered” clause in RAC was equivalent to “nochache”. I cant imagine how bad that would be in concurrent selects from the same sequence.
It would be interesting if someone running 9i RAC performs the test case and I would appreciate if you post the results in the comments.
[@more@]來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/52450/viewspace-1055679/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 解決所有環境問題
- webpack(1)安裝環境與解決環境問題Web
- Fabric 環境搭建遇到問題及解決
- RAC環境下的SEQUENCE對應用的影響
- oracle rac asm 問題的官方解決辦法OracleASM
- ORACLE RAC環境下節點自動重啟問題總結Oracle
- oracle rac 環境檢測Oracle
- 行者APP適配國外環境問題解決APP
- ORACLE RAC 環境下修改IPOracle
- Oracle RAC + Data Guard 環境搭建Oracle
- 【PyCharm】解決虛擬環境pip無法使用問題PyCharm
- RAC環境下安裝部署OWB問題總結
- RAC中通過nkmod解決各個節點碟符不一致問題
- 【Oracle】 RAC 環境刪除oracle 之二Oracle
- 【Oracle】 RAC 環境刪除oracle 之一Oracle
- Oracle 10g RAC advanced replication同步問題解決Oracle 10g
- oracle rac RMAN-20242 問題解決思路Oracle
- Oracle RAC環境修改字符集遇到ORA-00205解決方法Oracle
- CentOS環境下mysql遠端連線和問題解決CentOSMySql
- Oracle 12cR2 RAC安裝配置及問題解決Oracle
- Spark 環境問題記錄和解決方法Spark
- WINDOWS環境下ORACLE啟動的問題WindowsOracle
- laravel線上環境表單驗證重定向問題解決方案Laravel
- ngrok+express解決本地環境中微信介面除錯問題Express除錯
- 解決NLTK包下載出錯問題及NLP環境測試
- 分散式環境下利用快取解決重複性問題分散式快取
- WSL中配置EDA環境:遇到的問題以及解決辦法
- Oracle RAC 環境下的連線管理Oracle
- ORACLE RAC環境下刪除節點Oracle
- MyBatis 解決欄位名不一致的問題MyBatis
- Laravel 在 Docker 環境下訪問 storage 靜態資源 404 問題解決LaravelDocker
- vmware server 解決rac中共享磁碟問題Server
- 解決WAMP環境修改apache配置檔案不生效問題(Laravel public)ApacheLaravel
- Oracle 解決鎖表問題Oracle
- oracle 鎖問題的解決Oracle
- Oracle RAC之--安裝過程中碰到的問題及解決方法Oracle
- 安裝Oracle RAC時,不能驗證ASMSNMP密碼問題的解決OracleASM密碼
- 【故障處理】DBCA建庫詭異問題處理--rac環境不能建立rac庫