Is there a way to automatically kill long running SQL statements at the database after a pre-determined maximum waiting time ?
these SQLs will be triggered by inbound web service calls, which represent 60% of the expected transaction volume in this customer specific scenario.
the driver for the question is performance
SOLUTION
No, there is nothing like that.
Timeouts are usually related to inactivity time.
They are either set at the client application, who is waiting for a response and decides it waited too long and simply moves on to something else.
Or set at the siebel object manager, but again, they´d be for a session that is open but simply inactive for too long.
The scenario described is different : the session would be actively doing some work that took too long. Even if the client disconnects, it´d continue running at the siebel object manager.
The suggestion for such scenario is : Most of the time the task is slow due to a runaway query (Slow SQL Statement).
You may work with your DBA to create an Oracle Profile to limit how many seconds a query can run. E.g: 180 sec. Then associated that profile to the user that runs the integration.
NOTE: you may want to use a user different than SADMIN for this.
This will also require that you log in to server manager command line and issue the command :
srvrmgr> change parameter DSDisableExecuteRetry=True for named subsystem ServerDataSrc
But the solution depicted here is Oracle specific as it is composed of Oracle profile + DSDisableExecuteRetry.
This is a feature available in siebel 8.0.x that prevents the object manager from re-sending a query to the database layer if an error, such as network error or sesion kill occurs.
You should also consider an analysis of areas in the application where Long Running queries were triggered and provide a functional solution to users to avoid those queries or simply modifyi the application / integration.