_serial_direct_read, direct path read和checkpoint

最新推荐文章于 2022-04-29 17:28:33 发布
最新推荐文章于 2022-04-29 17:28:33 发布
阅读量599 收藏 2
点赞数 1
 
_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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26684/viewspace-243701/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26684/viewspace-243701/

cuizhacha1211
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle关闭直接路径读,Oracle 直接路径读取等待事件
weixin_28366353的博客
04-03 1365
直接路径读取等待事件这个事件DPR在10G是没有的，是11G新等待事件，目的是让全表扫描和快速索引扫描不走SGA，直接从数据文件读到PGAdirect path read的优势1、采用直接路径读取后，总能保证读取的块数是多块读参数设置的大小，提高了读取的效率2、大大的降低了对于latch的使用，进而避免了可能导致的latch竞争(cbc latch等)3、降低了全表扫描对buffer cache的...
best_cityscapes_checkpoint.pth.txt
04-09
best_cityscapes_checkpoint.pth TXT中附带百度云下载地址，永久有效； Python requirements Currently, the code supports Python 3 numpy PyTorch (>=1.1.0) torchvision scipy scikit-image ...
11g中_serial_direct_read是动态参数，可以在线修改
msdnchina的专栏@JiNan,ShanDong
04-30 7952
11g中_serial_direct_read是动态参数，可以在线修改
【Oracle】11g direct path read介绍:10949 event、_small_table_threshold与_serial_direct_read
zclinux的博客
01-02 1611
转自刘相兵老师的博文：http://www.askmaclean.com/archives/11g-direct-path-read-10949-_small_table_threshold-_serial_direct_read.html在11g之前串行的扫描大表默认总是先将数据读取到Oracle高速缓冲中，其等待事件常为db file scattered read。 从11g开始Oracle通
11g direct path read介绍:10949 event、_small_table_threshold与_serial_direct_read
weixin_33802505的博客
12-02 103
在11g之前串行的扫描大表默认总是先将数据读取到Oracle高速缓冲中，其等待事件常为db file scattered read。 从11g开始Oracle通过内部算法来决定串行扫描大表是通过直接路径读direct path read，还是先读入到buffer cache中，此算法依据表的大小评估。 转载于:https://blog.51cto.com...
11g serial table scan特性和direct path read直接路径读
liuzhilong的博客
08-15 447
11g后引入serial table scan ，全表扫描至少超过5倍的_small_table_threshold值，数据库会使用direct path read而不是db file scattered read。 db file scattered read：从磁盘中读取多个块到SGA中不连续的buffer中，scattered read必须是从磁盘读取多个块到buffer cache中，且...
matplotlib整活-checkpoint_matplotib_checkpoint_
10-01
**标题解析：** "matplotlib整活-checkpoint_matplotlib_checkpoint_" 这个标题表明我们将探讨如何使用matplotlib库在Python中创建自定义的可视化图像，并且可能涉及到一些进阶或非标准的使用方法，"checkpoint"可能...
checkpoint_iter_370000.pth
03-04
lightweight-pytorch训练好的模型checkpoint_iter_370000.pth
convert_bert_tf_checkpoint_to_pytorch.py
06-26
将基于TensorFlow的谷歌发布的官方BERT模型转化为基于Pytorch的BERT模型
AMIBIOS8_Checkpoint_and_Beep_Code_List_1.9.rar_AMI CheckPoint_AM
09-21
标题中的“AMIBIOS8_Checkpoint_and_Beep_Code_List_1.9.rar”指的是一个关于AMIBIOS8的检查点和蜂鸣代码列表的压缩文件，版本为1.9。AMI CheckPoint是AMIBIOS8的一个特性，用于在系统启动过程中提供硬件状态的反馈...
等待事件Adaptive direct path read - 自适应的直接路径读
小楼一夜听春雨，深巷明朝卖杏花
08-02 325
在Oracle Database 11g中有一个新特性，全表扫描可以通过直接路径读的方式来执行（Direct Path Read），这是一个合理的变化，如果全表扫描的大量数据读取是偶发性的，则直接路径读可以避免大量数据对于Buffer Cache的冲击。   可是现实往往是残酷的：在很多业务系统中，全表扫描是普遍存在的常态，将大表的全表扫描全部转化为直接路径读，反而不如Cache在Buffer...
oracle set path,Oracle 11g全表扫描以Direct Path Read方式执行
weixin_32005771的博客
04-02 238
Oracle 11g全表扫描以Direct Path Read方式执行在Oracle Database 11g中有一个新特性，全表扫描可以通过直接路径读的方式来执行(Direct Path Read)，这是一个合理的变化，如果全表扫描的大量数据读取是偶发性的，则直接路径读可以避免大量数据对于Buffer Cache的冲击。当然对于小表来说，Oracle允许通过Buffer Cache来进行全表扫描...
declare 命令
AntHub的博客
04-29 761
文章目录declare选项说明用法示例 declare 说明: Set variable values and attributes. Declare variables and give them attributes. If no NAMEs are given,display the attributes and values of all variables. Using +' instead of -’ turns off the given attribute. -Variables
【转自Oracle ACE-盖国强】Oracle 11g全表扫描以Direct Path Read方式执行
msdnchina的专栏@JiNan,ShanDong
04-30 1275
Oracle 11g全表扫描以Direct Path Read方式执行 作者：eygle |English   【转载时请标明出处和作者信息】|【恩墨学院 OCM培训传DBA成功之道】 链接：http://www.eygle.com/archives/2012/05/oracle_11g_direct_path_read.html   在Oracle Database 1
Oracle 11g全表扫描以Direct Path Read方式执行
okhymok的博客
12-08 1081
在Oracle Database 11g中有一个新特性，全表扫描可以通过直接路径读的方式来执行（Direct Path Read），这是一个合理的变化，如果全表扫描的大量数据读取是偶发性的，则直接路径读可以避免大量数据对于Buffer Cache的冲击。 当然对于小表来说，Oracle允许通过Buffer Cache来进行全表扫描，因为这可能更快，也对性能影响不大。 小表受到隐含参数：_s
Oracle等待事件DFS lock handle
热门推荐
关注系统性能调优
10-27 1万+
在做性能压力测试，测试结果不能通过，获取现场一个小时的AWR报告，发现大量的等待事件，数据库是RAC，版本是11.2.0.4.0。 Snap Id Snap Time Sessions Cursors/Session Instances Begin Snap: 1607 21-10月-14 20:00:03 560
oracle process表,学习Oracle动态性能表-V$PROCESS
weixin_42429956的博客
04-08 186
转载:http://hi.baidu.com/ldm_myself/blog/item/b76a2d99899c580e6e068c96.htmlV$PROCESS本视图包含当前系统oracle运行的所有进程信息。常被用于将oracle或服务进程的操作系统进程ID与数据库session之间建立联系。在某些情况下非常有用：1.如果数据库瓶颈是系统资源(如：cpu,内存)，并且占用资源最多的用户总是停...
记一次数据库无法增删改趋于HANG住状态的故障诊断和处理
Robo's Blog【海阔凭鱼跃,天高任鸟飞】
03-21 1525
记记一次数据库无法增删改趋于HANG住状态的故障诊断和处理，生成并分析HANGANALYE和SYSTEMSTATE
oracle 11G direct path read 很美也很伤人
海鲨MYSQL精英架构师
08-12 4514
direct path read在11g中，全表扫描可能使用direct path read方式，绕过buffer cache，这样的全表扫描就是物理读了。在10g中，都是通过gc buffer来读的，所以不存在direct path read的问题。　　direct path read较高的可能原因有：　　1. 大量的磁盘排序操作，order by, group by, union, disti
checkpoint_path
最新发布
06-03
checkpoint_path是一个文件路径，通常用来存储模型在训练过程中的参数。在深度学习中，模型的参数通常是由神经网络的权重和偏差组成的。在训练过程中，这些参数会被不断地调整以使模型能够更好地拟合数据。 当我们训练模型时，我们通常会在每个epoch或每个batch结束时保存模型的参数。这是为了避免在训练过程中发生任何问题，例如程序崩溃或计算机断电，导致我们失去了模型的参数。如果我们保存了模型的参数，我们可以在训练过程中随时恢复模型，并从上次停止的地方继续训练。 checkpoint_path指定了保存模型参数的文件路径。在tensorflow中，我们可以使用tf.train.Saver()函数来保存模型参数，将参数保存在checkpoint_path指定的路径下。如果需要恢复模型，我们可以使用tf.train.Saver()函数从checkpoint_path指定的路径加载参数。 总之，checkpoint_path是一个文件路径，用于保存深度学习模型在训练过程中的参数，并在需要时恢复模型。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值