RAC中使用 Oracle Database 11gR2的结果缓存特性(4)
<DIV><STRONG><EM>实例 2:</EM></STRONG><P>在第二个实例上执行查询的执行计划表明,尽管逻辑 I/O 操作保持不变,但几乎没有磁盘 I/O。那么数据是如何进入数据库缓冲区缓存的?如下面的等待事件统计信息所示,这是利用缓存融合技术在专用互连上完成的。</P>
<P><SPAN style="FONT-FAMILY: Courier New">call count cpu elapsed disk query current rows<BR>------- ------ -------- --------- ---------- ---------- ---------- ----------<BR>Parse 1 0.00 0.01 0 0 0 0<BR>Execute 1 0.00 0.00 0 0 0 0<BR>Fetch 21 246.53 271.06 <STRONG>3 42907483</STRONG> 0 300<BR>------- ------ -------- -------------------- ---------- ---------- ----------<BR>total 23 246.54 271.07 <STRONG>3 42907483</STRONG> 0 300</SPAN></P>
<P><SPAN style="FONT-FAMILY: Courier New">Misses in library cache during parse: 1<BR>Optimizer mode: ALL_ROWS<BR>Parsing user id: 462 (TPCC)</SPAN></P>
<P><SPAN style="FONT-FAMILY: Courier New">Rows Row Source Operation<BR>------- ---------------------------------------------------<BR> 300 HASH GROUP BY (cr=42907483 pr=3 pw=0 time=271060491 us)<BR>21349787 NESTED LOOPS (cr=42907483 pr=3 pw=0 time=320262423 us)<BR>21349787 INDEX FULL SCAN IORDL (cr=207907 pr=3 pw=0 time=85413649 us)(object id 616250)<BR>21349787 INDEX UNIQUE SCAN ORDERS_I1 (cr=42699576 pr=0 pw=0 time=146357573 us)(object id 616287)</SPAN> </P>
<P>实例 1 (SSKY1) 实例 2 (SSKY2)</P>
<P>其上等待的事件 等待 其上等待的事件 等待<BR> <BR>--------------------------- 时间 ------------------- 时间<BR>library cache lock 2 library cache lock 1<BR>library cache pin 2 library cache pin 2<BR>row cache lock 19 row cache lock 19<BR>rdbms ipc reply 2 rdbms ipc reply 2<BR>SQL*Net message to client 21 SQL*Net message to client 21<BR>db file sequential read 26951 db file scattered read 1<BR>gc cr grant 2-way 18060 <BR>db file scattered read 26954<BR>gc cr multi block request 19055 gc cr multi block request 38621<BR>SQL*Net message from client 21 SQL*Net message from client 21<BR> gc remaster 9<BR> gcs drm freeze in enter server mode 8<BR> gc cr block 2-way 2<BR> gc current block 2-way 192925<BR> gc current block 3-way 3124</P>
<P><STRONG>注意</STRONG>:如果块当前不在本地实例的缓冲区缓存中但位于另一个实例(持有者)中,将发生 gc current block 2-way 等待事件,需要通过互连执行两跳操作将块传递给请求实例。</P>
<P>将此应用于上述讨论,块不在实例 2(请求者)的缓冲区缓存内。然而,由于之前有一个用户在实例 1(持有方)上执行过此查询,块必须通过互连传递给实例 2。</P>
<P><STRONG>注意</STRONG>:如果块当前不在本地实例(请求者)的缓冲区缓存内但在另一个实例(持有者)中,将发生 gc current block 3-way 等待事件,但块保留在第三个实例上,必须先执行三跳操作,请求实例才能接收到块。无论集群中有多少实例,这都是请求者在接收到块之前可发生的最大跳数。</P>
<P>对比 Oracle Database 11<EM>g</EM> 第 2 版与 Oracle Database 10<EM>g</EM> 第 2 版之间的逻辑 I/O 操作,显然 Oracle Database 10<EM>g</EM> 第 2 版中的逻辑 I/O 数量比 Oracle Database 11<EM>g</EM> 第 2 版中高得多。这是 Oracle Database 11<EM>g</EM> 第 2 版数据库优化器整合了改进的结果。</P>
<H4>方法 2:并行查询执行</H4>
<P>如果启用了并行操作,则上述缓存同步的整体行为都会发生变化。Oracle Database 11<EM>g</EM> 第 2 版引入了几个新参数。我们要重点讨论的参数是 PARALLEL_DEGREE_POLICY。该参数的默认值为 MANUAL。将它更改为 AUTO 将导致 Oracle RAC 跨多个实例产生从属进程,在可能的情况下无需并行查询提示即执行此查询。在一个或多个实例上产生的从属进程的数量是自动的,取决于资源的可用性。</P>
<P>另一个需要注意的参数是 PARALLEL_DEGREE_LIMIT。该参数的值可以是 I/O、CPU 或者一个指定最大并行度的整数值。</P>
<P>让我们在将并行度策略设置为 AUTO 的情况下再次尝试这些查询。</P>
<P><SPAN style="FONT-FAMILY: Courier New"><SPAN style="FONT-SIZE: smaller">call count cpu elapsed disk query current rows<BR>------- ------ -------- ---------- ---------- --------- ---------- ----------<BR>Parse 11 0.01 0.02 0 0 0 0<BR>Execute 11 19.17 86.67 181238 190696 0 0<BR>Fetch 21 0.03 8.34 0 0 0 300<BR>------- ------ -------- ---------- ---------- -------- ---------- ----------<BR>total 43 19.23 95.04 181238 190696 0 300<BR><BR>Misses in library cache during parse: 1<BR>Optimizer mode: ALL_ROWS<BR>Parsing user id: 89 (schema name) (recursive depth: 1)<BR><BR>Rows Row Source Operation<BR>------- ---------------------------------------------------<BR> 0 PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)<BR> 0 PX SEND QC (RANDOM) :TQ10003 (cr=0 pr=0 pw=0 time=0 us cost=10094 size=4950 card=150)<BR> 26 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=10094 size=4950 card=150)<BR> 260 PX RECEIVE (cr=0 pr=0 pw=0 time=345 us cost=10094 size=4950 card=150)<BR> 0 PX SEND HASH :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=10094 size=4950 card=150)<BR> 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=10094 size=4950 card=150)<BR> 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=10025 size=680920944 card=20633968)<BR> 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=305 size=22694870 card=2063170)<BR> 0 PX SEND HASH :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=305 size=22694870 card=2063170)<BR>247592 PX BLOCK ITERATOR (cr=1986 pr=1378 pw=0 time=82701 us cost=305 size=22694870 card=2063170)<BR>247592 INDEX FAST FULL SCAN ORDERS_I2 (cr=1986 pr=1378 pw=0 time=38108 us cost=305 size=22694870 card=2063170)(object id 86234)<BR> 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=9713 size=453947296 card=20633968)<BR> 0 PX SEND HASH :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=9713 size=453947296 card=20633968)<BR>2153776 PX BLOCK ITERATOR (cr=36697 pr=35311 pw=0 time=4484449 us cost=9713 size=453947296 card=20633968)<BR>2153776 INDEX FAST FULL SCAN IORDL (cr=36697 pr=35311 pw=0 time=4067280 us cost=9713 size=453947296 card=20633968)(object id 86202)</SPAN><BR></SPAN></P>
<P><BR>在这样的并行操作中,查询协调器识别集群中的各实例产生的从属进程。然后,每个从属进程都会检索数据子集,将其重新发回启动操作的实例以进行整合,最终向用户显示结果集。</P>
<P>值得注意的是,使用并行选项执行查询时,使用缓存融合技术通过互连将数据移至请求实例(也就是说,不使用新的 bypass reader 算法)。这是因为,与前述方法 1 的操作相比,检索和收集的数据数量更少。</P>
<P>通过以下部分查询输出,应注意到,PX 协调器进程会在参与集群操作的所有节点上产生从属进程,以便完成查询执行。</P><PRE>set linesize 140
col NAME FORMAT A28
col VALUE FORMAT 9999999999
break on inst_id on qcsid on server_set
SELECT stat.inst_id,stat.qcsid, stat.server_set, stat.server#, nam.name, stat.val
ue FROM gv$px_sesstat stat, gv$statname nam WHERE stat.inst_id = nam.inst_id AND
stat.statistic# = nam.statistic# AND nam.name = 'physical reads' ORDER BY 1,2,3;
Ins
ID QCSID SERVER_SET SERVER# NAME VALUE
--- ---------- ---------- ---------- ---------------------------- -----------
1 76 1 1 physical reads 0
physical reads 0
1083 1 8 physical reads 1452
9 physical reads 1300
7 physical reads 1348
2 8 physical reads 24832
7 physical reads 24832
9 physical reads 24448
physical reads 151
2 76 1 2 physical reads 0
1083 1 5 physical reads 1226
6 physical reads 1328
4 physical reads 1368
2 4 physical reads 29921
5 physical reads 29176
6 physical reads 29920
…………………………
</PRE>
<P><STRONG><BR></STRONG> </P>
<P><STRONG>注意</STRONG>:有关并行处理的更多背景信息,请参见<A href="http://www.oracle.com/technetwork/cn/articles/datawarehouse/twp-parallel-execution-fundamentals-133639.pdf" target=""><FONT color=#000000>此白皮书</FONT></A>。</P></DIV>
页:
[1]