Re: select 'x' from dual problem

From: Steve Karam <oraclealchemist_at_gmail.com>
Date: Mon, 1 Aug 2016 13:32:54 -0400
Message-Id: <990AD0F7-E303-48E3-A7AA-9C09BE1D9F7D_at_gmail.com>



David,

I’ve definitely run into issues with excessive DUAL usage, caused by the application just as Woody mentioned. It’s a very common bad practice.

In the worst case I’ve seen, the issue was that the excessive DUAL queries were being soft parsed every time they ran (46 times/second), and running up CPU usage via library cache latch waits. Here’s a writeup I did at the time: http://www.oraclealchemist.com/news/the-overly-shared-pool/ <http://www.oraclealchemist.com/news/the-overly-shared-pool/>

Steve Karam
OracleAlchemist.com

> On Aug 1, 2016, at 1:24 PM, 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 <mailto: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
Received on Mon Aug 01 2016 - 19:32:54 CEST

Original text of this message