|Strange "SELECT" sql got somehow repeatly executed [message #430138]
||Mon, 09 November 2009 02:08
Registered: November 2009
We usually execute "select" then "insert" on a bss_trade table.|
About 3 times a month, we suffer from the problem that the "select" sql instead of running once, oracle repeatedly execute it as in a dead loop. the detail code are as follows:
conn = GetConnection(); //get from conn pool of otl_connect*
std::string TradeSql =
"SELECT * FROM bss_trade WHERE ref_group = '1666447945873' AND trade_side = 'B' ";
otl_stream i(50, TradeSql.c_str(), *conn);//<-dead loop either here
if (!i.eof()) //<-dead loop or here
std::string UpdTradeSql = "INSERT INTO bss_trade VALUES ('3800','1666447945873','B','10000','1.86' )";
otl_rc = otl_cursor::direct_exec(*conn), UpdTradeSqlc_str());
The Oracle's CPU resources are all used up. the insert statement isn't executed and according to Oracle's v$sql, it keep running the "Select" statement. And there is no other application/thread trying to update this table.
Our previous DBA suggested using
alter system set "_table_lookup_prefetch_size"=0 scope=spfile;
which really improve the situation to become once 2 months.
Is there any ways to work around to help to improve further? Thanks.
[Updated on: Mon, 09 November 2009 02:13]
Report message to a moderator