Home » Developer & Programmer » Precompilers, OCI & OCCI » Strange "SELECT" sql got somehow repeatly executed (Oracle 10.0.2.0 + OTL v.4.0.128)
Strange "SELECT" sql got somehow repeatly executed [message #430138] Mon, 09 November 2009 02:08
arthurlcc
Messages: 1
Registered: November 2009
Junior Member
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
return FALSE;
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.

Regards,
Arthur Lai

[Updated on: Mon, 09 November 2009 02:13]

Report message to a moderator

Previous Topic: Need help on Pro*C compilation
Next Topic: PLX,PLL to c++ Compile
Goto Forum:
  


Current Time: Thu Mar 28 19:36:10 CDT 2024