Re: select 'x' from dual problem

David, if you really Hit the library latch issue and can not fix/change the code, probably you can mitigate the impact: by artificially change the optimizer environment you will have many copies of your statements in library cache and different instances are not fighting for the same latch.

Martin

^∆x

Am 01.08.2016 19:34 schrieb "David Ramírez Reyes" <dramirezr_at_gmail.com>:

> Yes, am also thoght that something similar could have happening; the main
> app is connected to a PLC and interacting with a robot, that answer would
> make sense.
>
> I'll check with the developers of the app.
>
> Thanks
>
> David Ramírez Reyes
> Profesión: Padre de Familia y DBA en mis ratos libres
> Profession: Parent and DBA in my spare time
>
>
> On 1 August 2016 at 12:24, Woody McKay <woody.mckay_at_gmail.com> wrote:
>
>> I've know some old apps that issues "select 'x' from dual" every time
>> they wanted to use the connection (in a connection pool) to ensure that the
>> connection was hot and alive. In performance testing, this select was
>> executed so much that it showed up in top 10 and consumed considerable
>> resources. I talked with the Java architect and that was their solution to
>> test the connection before using it... I couldn't get them to eliminate
>> it, but was able to get them to not do it every single time... maybe every
>> 100...
>>
>>
>>
>> On Mon, Aug 1, 2016 at 1:18 PM, David Ramírez Reyes <dramirezr_at_gmail.com>
>> wrote:
>>
>>> Hello all,
>>>
>>> Windows Server 2012 STD (physical server), Oracle DB 11g R2 Std.
>>>
>>> Since 4 days ago, there are some cpu pick problems on the server (1 cpu
>>> 3 cores, 32 GB in RAM); they have been happening 2 or 3 times during the
>>> day and take around 2-5 mins (the cpu picks to 100%, 99 of it used by the
>>> Oracle Service).
>>>
>>> I checked the threads running on it with Process Explorer, but there's
>>> no specific query consuming it, but many consuming less than 5% of cpu;
>>> when looking at the DB to find out the SQL text, I found that the query is
>>> select 'x' from dual
>>>
>>> AWR is disabled and no OEM console is configured (the DB was installed
>>> by a vendor for Engineering team, I don't have so much information about
>>> this specific setup).
>>>
>>> Have you ever have had problems with " select 'x' from dual" query?,
>>> tried to google some but haven't found something similar.
>>>
>>> Thanks in advance.
>>>
>>> Regards
>>>
>>> David Ramírez Reyes
>>> Profesión: Padre de Familia y DBA en mis ratos libres
>>> Profession: Parent and DBA in my spare time
>>>
>>>
>>
>>
>> --
>> Sincerely,
>>
>> WoodyMcKay
>>
>
>

--
http://www.freelists.org/webpage/oracle-l