About _serial_direct_read

How to bypass buffer cache for full table scans

From: http://sai-oracle.blogspot.com/2007/12/how-to-bypass-buffer-cache-for-full.html
By Saibabu Devabhaktuni

How to reduce impact of full table scans:

Full table scans (FTS) have their place, even on OLTP databases, and they are not evil when used properly. FTS are unavoidable on most DW databases. So apart from popular belief of possible increased response time and increased I/O, is there any impact on the database?

FTS on a large table can effectively pollute the buffer cache by aging out blocks in the buffer cache otherwise needed by other sessions, even though the FTS blocks are placed at LRU end. It will also have increased latch activity (cache buffer chains, cache buffer lru, etc.). It can also create many CR cloned buffers, if there are any blocks for that table already in the cache.

All these problems can be solved by setting one hidden parameter "_serial_direct_read = true" for that session or at the system level. The beauty of this parameter is that once execution plan is generated for any sql with this setting on, same behavior will be exhibited even when it is not set as long as the cursor is not re-parsed.

"_serial_direct_read = true" basically use "direct path reads" for single threaded multi block read operations like FTS. Oracle will first issue fast checkpoint at object level to write all dirty buffers of that object to the disk and perform direct path reads in to the PGA of shadow process.
This will be especially useful for batch jobs.

Oracle introduced event 10379 to do the same for rowid range scans but never really implemented it fully. Of course you can always use parallel query option to achieve the same thing with multiple threads, but you will be consuming additional resources on the system.

00:50:19 SQL> alter session set "_serial_direct_read" = true;
Session altered.

00:50:19 SQL> select avg(id) from test;

00:50:32 SQL> select event, total_waits from v$session_event
00:50:40 2 where sid = (select sid from v$mystat where rownum = 1)
00:50:40 3 and event like '%read%';

EVENT TOTAL_WAITS
------------------------------ -----------
direct path read 124

_serial_direct_read effect

From: http://www.orafaq.com/usenet/comp.databases.oracle.server/2005/02/06/0510.htm

Hi ,
I've a situation that I need to read a big table very fast. I'm using FTS, but I don't want to use parallel slave (not to over load the system). I need to read this data only once

I've tried to play with _serial_direct_read but I've found any differnt.
Alought I think it should reduce cpu usage and latches (not going on cache).

My other option is to use recycle pool of none-standard block size.

My questions are :

Is it good to use _serial_direct_read ?
does someone has experince with this parameter ?

My test is as follow:

I've create a table big_table -
SQL> exec show_space('BIG_TABLE')

Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          60,564
Total Blocks............................         101,888
Total Bytes.............................     834,666,496
Total MBytes............................             796
Unused Blocks...........................          40,981
Unused Bytes............................     335,716,352
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          69,768
Last Used Block.........................             491

And run the following test:

set serveroutput on size 1000000
declare a number;
begin
  runstats_pkg.rs_start;
  execute immediate 'alter session set "_serial_direct_read"=false';   select sum(id) into a from big_table;
  runstats_pkg.rs_middle;
  execute immediate 'alter session set "_serial_direct_read"=true';   select sum(id) into a from big_table;
  runstats_pkg.rs_stop;
end;
/

status is :

Run1 ran in 1792 hsecs
Run2 ran in 1896 hsecs
run 1 ran in 94.51% of the time

I've run on 10.1.0.3 . on my pc .

Can

Name                                Run1      Run2      Diff
STAT...recursive calls                 7         6        -1
STAT...enqueue requests                1         0        -1
STAT...enqueue releases                1         0        -1
LATCH.JS slv state obj latch           2         1        -1
LATCH.session timer                    6         7         1
LATCH.PL/SQL warning settings          0         1         1
LATCH.ncodef allocation latch          1         0        -1
LATCH.transaction branch alloc         1         0        -1
LATCH.KTF sga enqueue                  0         1         1
STAT...redo ordering marks             5         6         1
LATCH.event group latch                0         1         1
LATCH.session switching                1         0        -1
LATCH.process allocation               0         1         1
STAT...parse count (hard)              1         0        -1
STAT...cursor authentications          0         1         1
LATCH.ktm global data                  0         1         1
LATCH.simulator lru latch             18        17        -1
STAT...calls to kcmgas                 5         6         1
STAT...hot buffers moved to he         1         0        -1
LATCH.job_queue_processes para         0         2         2
LATCH.SQL memory manager worka       408       410         2
LATCH.cursor bind value captur         0         2         2
STAT...consistent gets            60,591    60,594         3
STAT...cleanout - number of kt        11        14         3
STAT...active txn count during         4         7         3
LATCH.session idle bit                 3         6         3
LATCH.sort extent pool                 4         1        -3
STAT...calls to kcmgcs                 4         7         3
STAT...consistent gets from ca    60,591    60,594         3
STAT...consistent gets - exami        11        14         3
LATCH.library cache pin alloca         0         4         4
LATCH.compile environment latc         0         4         4
LATCH.library cache lock alloc         0         4         4
STAT...recursive cpu usage           147       153         6
STAT...consistent changes            670       676         6
LATCH.channel operations paren        61        67         6
STAT...db block changes            1,346     1,353         7
STAT...Cached Commit SCN refer    60,432    60,425        -7
STAT...db block gets                 682       690         8
STAT...db block gets from cach       682       690         8
LATCH.active service list             25        33         8
LATCH.active checkpoint queue         12        21         9
STAT...session logical reads      61,273    61,284        11
LATCH.file cache latch                11         0       -11
STAT...physical reads             58,355    58,367        12
STAT...physical reads cache pr    54,676    54,688        12
STAT...physical reads cache       58,355    58,367        12
LATCH.library cache load lock          0        12        12
STAT...free buffer requested      58,360    58,373        13
LATCH.threshold alerts latch          14         0       -14
LATCH.In memory undo latch            14         0       -14
LATCH.JS queue state obj latch       108       144        36
LATCH.lgwr LWN SCN                     7        48        41
LATCH.Consistent RBA                   6        47        41
LATCH.mostly latch-free SCN            7        49        42
LATCH.cache buffers lru chain     58,378    58,420        42
LATCH.checkpoint queue latch         155       198        43
LATCH.enqueues                       285       347        62
LATCH.object queue header oper   116,750   116,819        69
STAT...user I/O wait time          1,696     1,783        87
LATCH.redo allocation                 34       143       109
LATCH.child cursor hash table          7       129       122
STAT...free buffer inspected      58,305    58,432       127
LATCH.redo writing                    44       177       133
LATCH.messages                       132       266       134
STAT...redo size                  91,196    91,344       148
LATCH.hash table column usage          0       150       150
LATCH.dml lock allocation              1       274       273
LATCH.undo global data                37       484       447
LATCH.simulator hash latch        14,949    15,471       522
LATCH.row cache objects              152       680       528
LATCH.enqueue hash chains            287       898       611
LATCH.session allocation               2       864       862
LATCH.library cache lock              14     1,909     1,895
LATCH.shared pool                     54     2,287     2,233
LATCH.library cache pin               55     2,652     2,597
LATCH.cache buffers chains       183,048   187,099     4,051
LATCH.library cache                   79     4,131     4,052

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct

382,630 401,740 19,110 95.24% Received on Sun Feb 06 2005 - 16:40:11 CST

From: http://www.orafaq.com/usenet/comp.databases.oracle.server/2005/02/07/0523.htm

One of the oddities about serial_direct_read is that it seems to become an attribute of the cursor. This means that you won't see a difference in your test because the second run of the SQL uses the cursor opened in the first part of the test, and therefore does not do direct reads. You need to have two slightly different statements if you want to see the effects in the stats and latches.

Your theory about less CPU because of the reduction of buffer and latch activity is correct - but the effect may not be significant (The same is true for a change in block size).

It is not 'good' to use the parameter, because it is a hidden parameter - and these, more or less by definition, are not good parameters to use on production systems without the direct approval of Oracle support.

Regards

Jonathan Lewis

_serial_direct_read, direct path read和checkpoint

From: http://oracledba.spaces.live.com/blog/cns!57D0C396BA028F14!263.entry

这个不是我遇到的问题，是同事遇到的。不过蛮有意思。

一条简单的语句，从plan上来看也没有什么问题。

类似于这样的语句:

SQL> explain plan for select * from test where rownum=1;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation            | Name       | Rows | Bytes | Cost |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    75 |    45 |
|* 1 | COUNT STOPKEY       |             |       |       |       |
|   2 |   TABLE ACCESS FULL | TEST        | 23440 | 1716K|    45 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM=1)

Note: cpu costing is off

15 rows selected.

可是执行时间确达到了15秒左右。

做10046 trace 结果如下：

PARSING IN CURSOR #1 len=59 dep=0 uid=25 oct=3 lid=25 tim=1657038831 hv=37103042 ad='52cd5c98'
select xxxxxxxx from xxxxxx where rownum=1
END OF STMT
PARSE #1:c=0,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=3,tim=1657038831
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=1657038831
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='rdbms ipc reply' ela= 0 p1=5 p2=21474836 p3=0
WAIT #1: nam='enqueue' ela= 307 p1=1413677062 p2=65743 p3=0
WAIT #1: nam='enqueue' ela= 156 p1=1413677062 p2=65743 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='direct path read' ela= 2 p1=26 p2=35843 p3=16
FETCH #1:c=8,e=474,p=64,cr=2,cu=5,mis=0,r=1,dep=0,og=3,tim=1657039305
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=1657039305
WAIT #1: nam='direct path read' ela= 1 p1=26 p2=35859 p3=16
WAIT #1: nam='direct path read' ela= 2 p1=26 p2=35875 p3=16
WAIT #1: nam='direct path read' ela= 0 p1=26 p2=35891 p3=16
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
*** 2005-11-29 02:58:28.211
WAIT #1: nam='SQL*Net message from client' ela= 1817 p1=1650815232 p2=1 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=0 obj=0 op='COUNT STOPKEY '
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='PARTITION RANGE ALL PARTITION: START=1 STOP=25 '
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=16071 op='INDEX FAST FULL SCAN PARTITION: START=1 STOP=25 '

首先发现10046中出现了direct path read, 但是没有使用PQ的情况下默认是不会走direct path read的。去查看隐藏参数_serial_direct_read，果然设置为true。在这个参数设置为true时，执行全表扫描就会发生 direct path read。

但是direct path read并不是慢的原因，只能说是起因。

为什么会这么慢呢？trace文件中已经很清楚的显示了上面很多rdbms ipc reply。rdbms ipc reply是等待后台进程完成的意思。通过P1=5查看v$process后台进程为checkpoint进程。这个问题之前有遇到过，就是direct path read进行scatter read的时候如果有block在data buffer中首先会触发checkpoint，将buffer中的block写回到disk。所以时间主要是等待在checkpoint上。

问题找到了，解决方法有两种
1. 避免全表扫描
2. 修改参数_serial_direct_read=false

About _serial_direct_read

Categories:

Tags:

Categories

Monthly Archives

Pages

Search

About this Entry