说明:测试RAC的cache fusion对数据块访问效率的影响
第一步,创建测试表
BYS@ bysrac1>create table test9 as select * from dba_objects;
Table created.
################################
第二步:在节点1进行更新不提交并查询
BYS@ bysrac1>conn / as sysdba
Connected.
SYS@ bysrac1>select instance_name from v$instance;
INSTANCE_NAME
—————-
bysrac1
SYS@ bysrac1>select distinct sid from v$mystat;
SID
———-
53
col spid for a10
SYS@ bysrac1>col machine for a15
SYS@ bysrac1>select b.spid,a.sid,a.serial#,a.machine from v$session a,v$process b where a.paddr =b.addr and a.sid=’53’;
SPID SID SERIAL# MACHINE
———- ———- ———- —————
15241 53 104 bysrac1.bys.com
SYS@ bysrac1>alter session set tracefile_identifier=test9_1; 给TRACE语句加个标识test9_1
Session altered.
SYS@ bysrac1>
exec sys.dbms_system.set_ev(’53’,’104′,10046,12,”);
打开TRACE语句
PL/SQL procedure successfully completed.
SYS@ bysrac1>update bys.test9 set subobject_name = ‘test’ where object_id
16914 rows updated.
SYS@ bysrac1>
set autotrace traceonly stat
让只输出统计信息,不输入数据。
SYS@ bysrac1>select * from bys.test9 where object_id
16914 rows selected.
Statistics
———————————————————-
0 recursive calls
0 db block gets
1342 consistent gets
0 physical reads
0 redo size
855042 bytes sent via SQL*Net to client
12816 bytes received via SQL*Net from client
1129 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16914 rows processed
SYS@ bysrac1>exec sys.dbms_system.set_ev(’53’,’104′,10046,0,”);
PL/SQL procedure successfully completed.
SYS@ bysrac1>exit
############################################################
第三步:在节点2对同一表进行查询
SQL> conn / as sysdba
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
bysrac2
SQL> select distinct sid from v$mystat;
SID
———-
37
col spid for a10
SQL> col machine for a15
SQL> select b.spid,a.sid,a.serial#,a.machine from v$session a,v$process b where a.paddr =b.addr and a.sid=’37’;
SPID SID SERIAL# MACHINE
———- ———- ———- —————
17047 37 447 bysrac2.bys.com
SQL>
alter session set tracefile_identifier=test9_2;
给TRACE语句加个标识test9_2
Session altered.
SQL>
exec sys.dbms_system.set_ev(’37’,’447′,10046,12,”);
打开TRACE语句
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly stat
SQL> select * from bys.test9 where object_id
16914 rows selected.
Statistics
———————————————————-
0 recursive calls
0 db block gets
1342 consistent gets
0 physical reads
0 redo size
855042 bytes sent via SQL*Net to client
12816 bytes received via SQL*Net from client
1129 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16914 rows processed
SQL> exec sys.dbms_system.set_ev(’37’,’447′,10046,0,”);
PL/SQL procedure successfully completed.
SQL> exit
####################################
第四步:分别在两个节点上使用tkprof将TRACE文件生成方便阅读的格式
节点1:
[oracle@bysrac1 trace]$ ls
alert_bysrac1.log
bysrac1_ora_15241_TEST9_1.trc
bysrac1_ora_15241_TEST9_1.trm
[oracle@bysrac1 trace]$ cd $ORACLE_HOME/bin
[oracle@bysrac1 bin]$
tkprof /u01/diag/rdbms/bysrac/bysrac1/trace/bysrac1_ora_15241_TEST9_1.trc /home/oracle/test9_1.trc
TKPROF: Release 11.2.0.1.0 – Development on Thu Oct 24 12:48:22 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[oracle@bysrac1 bin]$ cd
[oracle@bysrac1 ~]$ ls
alert_bysrac1.log Desktop fullback.sh oradiag_oracle racfull_5_1 racfull_6_1
test9_1.trc
#####################
节点2:
[oracle@bysrac2 ~]$ cd –
/u01/diag/rdbms/bysrac/bysrac2/trace
[oracle@bysrac2 trace]$ ls
alert_bysrac2.log
bysrac2_ora_17047_TEST9_2.trc
bysrac2_arc2_5210.trc bysrac2_ora_17047_TEST9_2.trm
bysrac2_arc2_5210.trm
[oracle@bysrac2 trace]$ cd $ORACLE_HOME/bin
[oracle@bysrac2 bin]$ tkprof /u01/diag/rdbms/bysrac/bysrac2/trace/bysrac2_ora_17047_TEST9_2.trc /home/oracle/test9_2.trc
TKPROF: Release 11.2.0.1.0 – Development on Thu Oct 24 12:49:01 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[oracle@bysrac2 bin]$ cd
[oracle@bysrac2 ~]$ ls
alert_bysrac2.log inita.ora oradiag_oracle
test9_2.trc
######################################
第五步:查看重新格式化的TRACE文件转储中信息如下
节点1:—内容较多,仅截取了查询语句部分
********************************************************************************
SQL ID: 6ns41jwh1fz2u
Plan Hash: 1544281142
select *
from
bys.test9 where object_id
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1129 0.06 0.15 0 1342 0 16914
——- —— ——– ———- ———- ———- ———- ———-
total 1131 0.06 0.15 0 1342 0 16914
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
——- —————————————————
16914 TABLE ACCESS FULL TEST9 (cr=1342 pr=0 pw=0 time=184505 us cost=67 size=3484845 card=16835)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 1129 0.00 0.01
SQL*Net message from client 1129 0.00 0.49
********************************************************************************
节点2:—内容较多,仅截取了查询语句部分。–可以看到有gc cr multi block request等待事件,同时可以对比出节点2执行此查询所需CPU及查询总时间较多。
gc cr multi block request实际就是global cache cr multi block request,10G以后global cache被简称为gc,在RAC应用系统里面,这是一个常见的等待事件。
工作原理:
当进程请求数据库块时,首先会在本地的CACHE里面查看是否存在,这种查看是根据DBA (Data Block Address) 转化为cache buffers chains,然后再从hash bucket确认是否存在。
如果在本地没发现有块的CACHE,进程就会请求resource master授予共享访问给数据块,然后再去获取数据块的CACHE。
如果请求的BLOCK CACHE在远程的节点,resource master就会使用内部通讯把远程的CACHE传输到本地。当请求的CACHE BUFFER是共享模式的,远程节点就会克隆一个然后传输到本地。非则,就建立PI映像,然后传输到本地。
********************************************************************************
SQL ID: 6ns41jwh1fz2u
Plan Hash: 1544281142
select *
from
bys.test9 where object_id
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1129 0.09 0.65 0 1342 0 16914
——- —— ——– ———- ———- ———- ———- ———-
total 1131 0.09 0.65 0 1342 0 16914
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
——- —————————————————
16914 TABLE ACCESS FULL TEST9 (cr=1342 pr=0 pw=0 time=92380 us cost=67 size=3484845 card=16835)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 1129 0.00 0.01
Disk file operations I/O 1 0.00 0.00
ges message buffer allocation 21 0.00 0.00
gc cr multi block request 25 0.04 0.44
SQL*Net message from client 1129 0.00 0.35
********************************************************************************
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.e1idc.net