Test cache buffers chains in read-only mode of Oracle databases

OracleDatabase read-only modeCACHE BUFFERS CHAINSTesting is what we will introduce in this article. Although the read-only mode cache buffers chains is introduced from Oracle 9i, the cache buffers chains will still be requested when obtaining the buffer lock, but the situation is not as serious as 8i. Next we will test it.

The test procedure is as follows:

1. Create a table first.

 
 

  
  
create table test (a int,b varchar2(20)); 
 
 

2. INSERT 50000 rows of data into the table;

 
 

  
  
declare  
  
  
i int;  
  
  
begin  
  
  
for i in 1..50000  
  
  
loop  
  
  
insert into test values(i,'adsfsafsa');  
  
  
end loop;  
  
  
end; 
 
 

3. Create an INDEX in the column;

 
 

  
  
create index test_i on test(i); 
 
 

4. Compile a stored procedure and perform a large number of index scanning operations.

 
 

  
  
create or replace procedure test_k is  
  
  
begin  
  
  
for i in (select /*+ INDEX(TEST TEST_I) */ * from test where a>20000)  
  
  
loop  
  
  
null;  
  
  
end loop;  
  
  
end; 
 
 

5. Open 2000 sessions to execute the stored procedure;

 
 

  
  
var i number;  
  
  
begin  
  
  
for j in 1..2000  
  
  
loop  
  
  
dbms_job.submit(:i,'TEST_K;');  
  
  
commit;  
  
  
end loop;  
  
  
end; 
 
 

6. the query waits for the event to find latch: cache buffers chains is very serious, and the CPU is running most of the time in SYS mode.

 
 

  
  
Cpu(s): 16.3%us, 83.7%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st 
 
 

Conclusion:Even if the cache buffers chains LATCH obtained in read-only mode cannot completely solve the LATCH contention problem, the competition is not so serious.

This article introduces the knowledge about the cache buffers chains test in the read-only mode of Oracle databases. I hope this introduction will help you gain some benefits!