Prevent queries from killing my dev server

Álvaro González

I'm running Oracle 10g XE in a Windows Server 2003 box somewhere in our LAN to power a PHP application running on a local Apache/2.4 server in my Windows 7 PC. I've a couple of queries with inline views that —all of a sudden— apparently need several hours to complete, while they should run in a few seconds (and that's what they used to do until some revisions ago).

The problem is that I cannot even diagnose the issue because everything I try (run my app or even calculate the explain plan) stays running endlessly and prevents any other page from the site from loading until I log into the Windows Server box using Terminal Services and manually restart Oracle. The whole restart takes several minutes.

As far as I know there isn't a query timeout feature in the OCI8 PHP extension and my attempts to configure Oracle to kill long running queries automatically have hit a wall of endless technical documentation and custom PL/SQL scripts beyond my area of expertise.

Is there anything relatively simple I could do to mitigate this issue?

Sdhamoth-Oracle

May be you can try PHP function set_time_limit() or max_execution_time in php.ini.

http://php.net/manual/en/function.set-time-limit.php

Álvaro González

I hadn't through of that because I assumed it wouldn't work. I've just tried and I can confirm it doesn't work. Quoting from the documentation:

The maximum execution time is not affected by system calls, stream operations etc.