Oracle Sequence Nocache
預設情況下,建立Sequence時,緩衝(cache)是20.意即:20次.nextval才會觸發一次對sys.seq$的更新(update)操作。這樣就能提高SQL執行效能。
當使用者指定nocache時,如:
create sequence test_seq nocache;
每一次test_seq.nextval都會觸發一次sys.seq$更新(update)。
我們可以比較下cache 20 和nocache的效能:
create sequence test_seq1 nocache;
create sequence test_seq2 cache 20;
create table tt1 (id number);
create table tt2 (id number);
declare
v_start_time number :=dbms_utility.get_cpu_time;
v_time1 number;
begin
insert into tt1 select test_seq1.nextval from dual connect by level <= 100000;
v_time1 := dbms_utility.get_cpu_time;
dbms_output.put_line('Nocache sequence: ' || (v_time1 - v_start_time) / 100 || ' seconds');
insert into tt2 select test_seq2.nextval from dual connect by level <= 100000;
dbms_output.put_line('Caceh 20 sequence: ' || (dbms_utility.get_cpu_time - v_time1) / 100 || ' seconds');
end;
anonymous block completed
Nocache sequence: 28.37 seconds
Caceh 20 sequence: 1.83 seconds
效能差異還是相當明顯的。
當使用者指定nocache時,如:
create sequence test_seq nocache;
每一次test_seq.nextval都會觸發一次sys.seq$更新(update)。
我們可以比較下cache 20 和nocache的效能:
create sequence test_seq1 nocache;
create sequence test_seq2 cache 20;
create table tt1 (id number);
create table tt2 (id number);
declare
v_start_time number :=dbms_utility.get_cpu_time;
v_time1 number;
begin
insert into tt1 select test_seq1.nextval from dual connect by level <= 100000;
v_time1 := dbms_utility.get_cpu_time;
dbms_output.put_line('Nocache sequence: ' || (v_time1 - v_start_time) / 100 || ' seconds');
insert into tt2 select test_seq2.nextval from dual connect by level <= 100000;
dbms_output.put_line('Caceh 20 sequence: ' || (dbms_utility.get_cpu_time - v_time1) / 100 || ' seconds');
end;
anonymous block completed
Nocache sequence: 28.37 seconds
Caceh 20 sequence: 1.83 seconds
效能差異還是相當明顯的。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/638844/viewspace-1062022/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle hint_cache_nocacheOracle
- ORACLE SEQUENCEOracle
- ORACLE SEQUENCE用法Oracle
- Oracle - Sequence序列Oracle
- Oracle序列sequenceOracle
- Oracle之Sequence(序列)Oracle
- Oracle Sequence Audses$研究Oracle
- oracle sequence語法Oracle
- oracle sequence 試用Oracle
- oracle的scn及sequenceOracle
- Oracle中Sequence的使用Oracle
- oracle中sequence使用的限制Oracle
- sequence to sequence模型模型
- Oracle -- 批次更新sequence的儲存Oracle
- Oracle -- 批量更新sequence的儲存Oracle
- ORACLE SEQUENCE的簡單介紹Oracle
- Oracle Sequence Cache 引數說明Oracle
- Oracle中sequence cache的測試Oracle
- Oracle中sequence的使用方法Oracle
- 不同於Oracle:SEQUENCE的區別Oracle
- 【小竅門tip】oracle sequence 修改增量值Oracle
- Oracle 基本操作之 建立自增欄位方法-ORACLE SEQUENCEOracle
- Sequence recognition
- Oracle建立自增欄位方法-ORACLE SEQUENCE的簡單介紹Oracle
- [20171117]nocache的編譯.txt編譯
- [20220321]探究oracle sequence.txtOracle
- 在Hibernate中關於Oracle sequence的使用KHOracle
- Oracle 12c新特性之Sequence的Session特性OracleSession
- [20170221]nocache工具的小測試.txt
- Oracle中Nextval用法SEQUENCE與SYS_GUID()OracleGUI
- 【oracle】ORA-16038: log 2 sequence# 98 cannot be archivedOracleHive
- PostgreSQL 序列(Sequence)SQL
- PostgreSQL sequence (一)SQL
- Oracle建立自增欄位方法-ORACLE SEQUENCE的簡單介紹(轉帖)Oracle
- 【轉】MySQL中增加sequence管理功能(模擬建立sequence)MySql
- python sequence序列Python
- mysql實現sequenceMySql
- 3. Swift SequenceSwift