_serial_direct_read, direct path read和checkpoint (妖精指数:2)
这个不是我遇到的问题，是同事遇到的。不过蛮有意思。
一条简单的语句，从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 ct=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 bj=0 p='COUNT STOPKEY '
STAT #1 id=2 cnt=1 pid=1 pos=1 bj=0 p='PARTITION RANGE ALL PARTITION: START=1 STOP=25 '
STAT #1 id=3 cnt=1 pid=2 pos=1 bj=16071 p='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
一条简单的语句，从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 ct=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 bj=0 p='COUNT STOPKEY '
STAT #1 id=2 cnt=1 pid=1 pos=1 bj=0 p='PARTITION RANGE ALL PARTITION: START=1 STOP=25 '
STAT #1 id=3 cnt=1 pid=2 pos=1 bj=16071 p='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