Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘serial direct path read

Interesting combination of RAC and serial direct path read

with 7 comments

For who are not aware, Oracle 11g has introduced a serial direct path read which enables the efficient direct path read for the serial table scan.

Disabling Direct path read for the serial full table scan-11g

A couple of days ago, I was reported a weird performance bias between 2 nodes in 11g RAC.

The query has the same execution plan on both nodes.

---------------------------------------------------------
| Id  | Operation                       | Name         | 
---------------------------------------------------------
|   0 | SELECT STATEMENT                |              | 
|   1 |  NESTED LOOPS                   |              | 
|*  2 |   TABLE ACCESS FULL             | 			   | 
|*  3 |   VIEW                          |              | 
|   4 |    UNION ALL PUSHED PREDICATE   |              | 
|*  5 |     FILTER                      |              | 
|   6 |      TABLE ACCESS BY INDEX ROWID| 			   | 
|*  7 |       INDEX RANGE SCAN          | 			   | 
|*  8 |     FILTER                      |              | 
|   9 |      TABLE ACCESS BY INDEX ROWID| 			   | 
|* 10 |       INDEX RANGE SCAN          | 			   | 
---------------------------------------------------------

But on node A, it takes 12 sec to complete while takes only 2 sec on node B.

-- node A
all  count    cpu  elapsed    disk    query  current    rows 
------- ------ -------- ---------- ---------- ---------- ---------- ---------- 
Parse    1    0.06    0.06      0      0      0      0 
Execute    1    0.00    0.00      0      0      0      0 
Fetch    2    1.31    10.48    90421    90437      0      2 
------- ------ -------- ---------- ---------- ---------- ---------- ---------- 
total    4    1.37    10.55    90421    90437      0      2 
Elapsed times include waiting on following events: 
Event waited on                  Times  Max. Wait Total Waited 
----------------------------------------  Waited ---------- ------------ 
library cache lock                    1    0.00      0.00 
library cache pin                    1    0.00      0.00 
SQL*Net message to client              2    0.00      0.00 
enq: KO - fast object checkpoint          2    0.00      0.00 
reliable message                    1    0.00      0.00 
direct path read                  1579    0.00      0.01 
SQL*Net message from client              2    23.81      23.81

-- node B
call  count    cpu  elapsed    disk    query  current    rows 
------- ------ -------- ---------- ---------- ---------- ---------- ---------- 
Parse    1    0.03    0.03      0      0      0      0 
Execute    1    0.00    0.00      0      0      0      0 
Fetch    2    2.01    2.01      0    90450      0      2 
------- ------ -------- ---------- ---------- ---------- ---------- ---------- 
total    4    2.04    2.05      0    90450      0      2 

Elapsed times include waiting on following events: 
Event waited on                  Times  Max. Wait Total Waited 
----------------------------------------  Waited ---------- ------------ 
SQL*Net message to client              2    0.00      0.00 
gc cr block 2-way                    1    0.00      0.00 
SQL*Net message from client              2    23.53      23.53 
library cache lock                    1    0.00      0.00 
library cache pin                    1    0.00      0.00

This performance bias was always reproduced. It took always 12 sec on node A and 2 sec on node B.

The hint was direct path read wait event which means that the direct path read was being performed.

  • Because this is not a parallel execution, we have a strong chance of the serial direct path read.
  • Nocache LOB can’t be a reason here. If the nocache LOB is the issue, both nodes should have the same performance feature.
  • But we should be careful when interpreting the wait count and wait time of direct path read event. We can’t rely on these values due to the mechanism of the direct path read.

The customer was suggested of the 10949 diagnostics event as a workaround and the problem was gone(I know that this event is undocumented and unsupported, but… )

It seems that node A and node B had different environments(different cache size and/or different size of preload blocks …) and as a result, Oracle chose the different ways of executing FTS – direct vs. conventional.

Can this be classified as another dark side(side effect) of the automization by Oracle? I’m not sure but the unexpected bias between the multiple nodes is always likely to embarrass the DBAs.

Written by Dion Cho

June 9, 2010 at 4:31 am

Posted in I/O

Tagged with

Serial Direct Path Read and Block Cleanout(11g)

with 2 comments

Chris Antognini posted an article about the serial direct path read and repeated block cleanout problem here. I’d just like to add my own interpretation on this phenomenon.

In summary,

  1. Oracle needs to cleanout the dirty blocks to determine the status of the block and rolls them back if necessary.
  2. But it does not mean that Oracle modifies the dirty blocks. It just checks the status of the dirty blocks.
  3. So, it does not generate redo and does not physically cleanout the dirty blocks.

Following is the the result of block dump which helps you to understand exactly when Oracle physically modifies the dirty block.

-- before update
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.00a.000036ed  0x00c0032b.1929.08  C---    0  scn 0x07df.2427c615
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

-- after update
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.00a.000036ed  0x00c0032b.1929.08  C---    0  scn 0x07df.2427c615
0x02   0x0006.001.0000480a  0x00c010b1.17f3.02  ----    1  fsc 0x0000.00000000 (cleanout is delayed)

-- after direct path read
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.00a.000036ed  0x00c0032b.1929.08  C---    0  scn 0x07df.2427c615
0x02   0x0006.001.0000480a  0x00c010b1.17f3.02  ----    1  fsc 0x0000.00000000 (wasn't cleaned out)

-- after conventional path read
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.00a.000036ed  0x00c0032b.1929.08  C---    0  scn 0x07df.2427c615
0x02   0x0006.001.0000480a  0x00c010b1.17f3.02  C---    0  scn 0x07df.2427ffae (was cleaned out)

Statistics shows another insight.
(block count# is 6010 in my test case)

-- after direct path read
consistent gets from cache                      6,025
consistent gets - examination                   6,011
immediate (CR) block cleanout applicatio        6,010
ns                                                   
cleanout - number of ktugct calls               6,010
cleanouts only - consistent read gets           6,010
commit txn count during cleanout                6,010
redo size                                       4,112

-- after conventional read
redo size                                     438,084
session logical reads                          12,103
consistent gets                                12,076
consistent gets from cache                     12,076
redo entries                                    6,025
consistent gets - examination                   6,016
cleanout - number of ktugct calls               6,016
cleanouts only - consistent read gets           6,010
immediate (CR) block cleanout applicatio        6,010
ns                                                   
commit txn count during cleanout                6,010
consistent gets from cache (fastpath)           5,668

We can tell that Oracle internally cleans out the dirty blocks from the statistics. But the serial direct path read does not load the current version of the block into the buffer cache. So it cannot modify the current block. It just reads the disk-version of the blocks from the data file and refers to the undo to cleanout the dirty blocks.

I think that it is unfair to call this a problem, but under certain situation it might cause problems like ORA-01555 error or repeated reads on the undo.

Footnote1:The traditional term of cleanout meant the modification on the dirty block to me, but I realized that I need more sophiscated terminology.

Footnote2: I believe that the parallel direct path read has the same design and feature with regard to the cleanout mechanism.

Written by Dion Cho

July 29, 2009 at 4:15 am

Posted in I/O, Misc.

Tagged with ,

Follow

Get every new post delivered to your Inbox.

Join 61 other followers