Hmm. While server side result cache has a host of issues, perhaps this is one
it can solve. I *think* dual is at least tied for the most stable and
unchanging table in the database.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Martin Berger
Sent: Wednesday, August 03, 2016 10:29 AM
To: dramirezr@xxxxxxxxx
Cc: Woody McKay; Oracle-L Freelists
Subject: 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@xxxxxxxxx>:
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@xxxxxxxxx> 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@xxxxxxxxx> 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
