dbms_scheduler创建的job，有些不明白的地方

Yong Huang

> 这个job已经运行了一个多星期了，为什么还是正在运行状态呢？
...
> 我通过 select * from gv$session where inst_id='2' and sid='1052'   这个会话里的sql_id 查询到了运行的SQL语句...

Does gv$session.status say "ACTIVE"? What's the wait event and p1, p2, p3? If event name doesn't change, does seq# go up? Also, go the other direction: find the SQL in v$sql (or gv$sql where inst_id=2) and check users_executing and buffer_gets. Is users_executing > 0 and buffer_gets still going up?

pineapple_lch

Yong Huang 发表于 2014-2-11 04:20
> 这个job已经运行了一个多星期了，为什么还是正在运行状态呢？
...
> 我通过 select * from gv$session  ...

gv$session 中 status=ACTIVE ,state=WAITING ，event and p1,p2,p3 的值如下图：


目前SEQ#=44609,之前没有注意看不知道有没有增长。
gv$sql 中users_executing=1 ,buffer_gets=2,optimizer_mode=ALL_ROWS,optimizer_cost=55194 ,之前也没有注意看这些参数，不知道buffer_gets是否是一直在增长。

Yong Huang

The SQL is waiting for data from a remote database. Can you check if the remote DB is still up and running? Can you manually run a simple query against the remote table or view? Is it possible the network between the two databases timed out for some reason, such as due to firewall timeout?

pineapple_lch

Yong Huang 发表于 2014-2-11 11:46
The SQL is waiting for data from a remote database. Can you check if the remote DB is still up and r ...

远程数据库确实正常运行着呢，用db link 也可以查询到相应的表。那意思是出现这样的问题因为两个数据库之间的网络可能存在过问题吗（某个时间断了后又通了）？ 在另外一个客户那也有这样的job ,不过是利用dbms_job创建的并县没有用RAC，在网络不稳定时这个语句会执行不过，长时间后会提示错误，然后继续下一个操作，没有出现过现在讨论的这个现象。这跟RAC有关系吗？

Yong Huang

If your OS is Linux, find the server process ID and on command line, run

strace -f -p <pid>

and see what it's doing. If it's UNIX, use truss instead. If it's Windows, Process Monitor can do something similar.

On the remote database server, check to see if the socket is still open on this connection (based on port number of netstat -an run on your source database server). Go into the database and see if the session created on behalf of the DB connection still exists and if yes, what it's doing.

pineapple_lch

Yong Huang 发表于 2014-2-11 22:24
If your OS is Linux, find the server process ID and on command line, run

strace -f -p

真是见笑了，我对操作系统不熟悉！本地的数据库服务器用的是HP_UX
select * from dba_scheduler_running_jobs  此语句查出来的slave_process_id=21,slave_os_process_id=10416 ，用truss -p <pid> 命令时pid是不是应该用操作系统的10416啊？我在本地服务器上输入命令truss -p 10416 时提示找不到，如下图



执行netstat -an 命令时会显示好多页好多页，我也看不太明白，下图是其中的一部分


还有就是你下边说的如何找远程数据库相应的会话我也没有太明白，能不能给我举个例子呢？

pineapple_lch

Yong Huang 发表于 2014-2-11 04:20
> 这个job已经运行了一个多星期了，为什么还是正在运行状态呢？
...
> 我通过 select * from gv$session  ...

这些参数的值一直是跟我上次回复你时的值一样，没有变化。
gv$session 中 status=ACTIVE ,state=WAITING ，SEQ#=44609
gv$sql 中users_executing=1 ,buffer_gets=2

pineapple_lch

up up up ....