1.低效的SQL
低效的SQL语句时发生cache buffers chains 锁存器争用的最重要原因。多个进程同时扫描大范围的索引或表时,可能广泛
地发生cache buffers chains latch争用。
低效的SQL语句时怎样引起cache buffers chain latch争用的,我们可以测试进一步进行了解。测试方案如下:
1) 创建cbc_test(id,name)表,对于ID列创建cbc_test_idx索引。ID列是唯一键,是选择性非常好的列。
2)多个会话同时通过cbc_test_idx索引查询扫描cbc_test表
3)广泛的索引扫描引起不必要的缓冲区查询的增加
----创建测试表
SQL> create table cbc_test(id number,name char(100));
表已创建。
SQL>insert into cbc_test(id,name) select rownum,object_name from dba_objects;
SQL> select count(*) from cbc_test;
COUNT(*)
----------
217740
---创建索引
SQL> create index cbc_test_idx on cbc_test(id);
索引已创建。
---扫描表cbc_test的Procedure
create or replace Procedure cbc_do_select is
begin
--不必要的索引扫描
for x in (select /*+ index(cbc_test cbc_test_idx)*/
*
from cbc_test
where id >= 0) loop
null;
end loop;
end;
---2个会话同时执行
SQL> var job_no number;
begin
for idx in 1 .. 20 loop
dbms_job.submit(:job_no,'cbc_do_select;');
commit;
end loop;
end;SQL> 2 3 4 5 6
7 /
PL/SQL 过程已成功完成。
查看ASH信息:
530 1021634 33 15-6?? -14 09.43.26.937 é??? f73c0sc1z2y9k latch: cache buffers chains 57 SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
531 1021634 83 15-6?? -14 09.43.26.937 é??? f73c0sc1z2y9k resmgr:cpu quantum SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
532 1021633 33 15-6?? -14 09.43.25.927 é??? f73c0sc1z2y9k latch: cache buffers chains 57 SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
533 1021633 83 15-6?? -14 09.43.25.927 é??? f73c0sc1z2y9k resmgr:cpu quantum SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
534 1021632 33 15-6?? -14 09.43.24.907 é??? f73c0sc1z2y9k latch: cache buffers chains 57 SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
535 1021632 83 15-6?? -14 09.43.24.907 é??? f73c0sc1z2y9k resmgr:cpu quantum SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
536 1021631 33 15-6?? -14 09.43.23.907 é??? f73c0sc1z2y9k latch: cache buffers chains 57 SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
537 1021631 83 15-6?? -14 09.43.23.907 é??? f73c0sc1z2y9k resmgr:cpu quantum SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
538 1021630 33 15-6?? -14 09.43.22.897 é??? f73c0sc1z2y9k latch: cache buffers chains 57 SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
539 1021630 83 15-6?? -14 09.43.22.897 é??? f73c0sc1z2y9k resmgr:cpu quantum SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
540 1021629 33 15-6?? -14 09.43.21.877 é??? f73c0sc1z2y9k latch: cache buffers chains 57 SELECT /*+ index(cbc_test cbc_test_idx)*/ * FROM CBC_TEST WHERE ID>=0
查看等待事件；
SID EVENT P1 P2 P3 p1raw
1 33 latch: cache buffers chains 758421644 150 0 000000002D34988C 0
2 83 latch: cache buffers chains 758421644 150 0 000000002D34988C 16
查看latch: cache buffers chains p1 p2 p3 参数:
latch: cache buffers chains 等待事件的3 个参数
p1 代表的是内存中latch锁的地址
p2 代表闩锁号
p3 没意义
SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH from x$bh where HLADDR='P1RAW';
SQL> SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH,HLADDR from x$bh where HLADDR LIKE '%2D34988C%';
DATA_OBJECT_ID FILE# DBABLK CLASS STATE TCH HLADDR
-------------- ---------- ---------- ---------- ---------- ---------- --------
75499 4 72988 1 1 0 2D34988C
75500 4 73454 1 1 0 2D34988C
75499 7 293294 1 1 0 2D34988C
75451 2 62537 1 1 2 2D34988C
SQL> SELECT object_id,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID IN (75499,75500,75499,75451);
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
75451 WRH$_ACTIVE_SESSION_HISTORY
75499 CBC_TEST
75500 CBC_TEST_IDX
HLADDR RAW(4) Hash Chain Latch Address