I believe it's used by the server as a JDBC heart beat to verify/maintain connections between the server and database. Although it appears frequently it should not be causing any load issues as it returns no data.
Unfortunately it is causing issues because during maintenance windows it seems only..or rather mostly Oracle tasks should be running. We verified that by killing that query, the performance improves instantly.
I know that the uppercase query is used by MyIT servers to verify connections to DB. However I cannot find any information that the lowercase query would be used by AR servers for such a verification.
What I am looking for is, firstly, to identify where this is configured for AR servers and secondly to modify in order for the query to not run every second, but at longer defined intervals.
Unfortunately I don't believe there's any option to configure/disable this but I'll check and confirm.
1 of 1 people found this helpful
Maybe there is some relation to
SW00516453 Hard parsing causing library cache contention on SELECT to T5619 table (SHR:Union_OverviewConsole form); subsequent feedback from Oracle is identifying 'select 1 from dual' SQL more likely cause (Note: See the Additional Instructions section for this defect above)
Thanks Andreas Mitterdorfer I checked the defect and the query was not found to be relevant, it was frequent refreshes of the Overview Console that was at fault.
I've done some testing and can see the same queries on my test system but they are only showing up at the database, they're not recorded in the AR SQL log. They are run by each thread to test connectivity to the database and they can't be configured/disabled.
Hi Mark, Andreas,
Indeed we tried applying that hotfix even with a higher delay (10 minutes) but the situation did not improve. Unfortunately this issue is impacting the DB during the Oracle Maintenance Window (nighttime CET) which is causing serious performance degradation for our systems. The ticket we opened to BMC for this did not provide any resolution yet...and we are kind of stuck in the investigation.
I don't believe there are any options you can set to change the behaviour of this query. If you want to send me a message with the BMC case number I'll follow up and see if I can get this confirmed.
Which ARS/ITSM version are you using and have you captured the select in server side api/sql/filter logs yet?
Unfortunately that particular query (originating from AR Servers) does not appear in the logs. The only query that does indeed appear in the logs is in uppercase, originating from MyIT servers. But that query has a different ID and is not the one responsible for the performance issues observed.
The AR Server version is 9.1 (build 201611080448).
1 of 1 people found this helpful
We had a similar issue that had been bothering us and our DBAs for some time, we were seeing "select 1" in the database alert logs every minute and it was producing parse errors like this:
WARNING: too many parse errors, count=100 SQL hash=0xbbcb647d
PARSE ERROR: ospid=15188, error=923 for statement:
Additional information: hd=47cc12400 phd=472eba400 flg=0x28 cisid=112 sid=112 ciuid=112 uid=112
I finally stumbled upon where this query was coming from, which is Openfire within SmartIT.
I was able to correct it by editing the <\Smart_IT_MyIT>\openfire\conf\openfire.xml file
Changed the following line
<testSQL>select 1 from dual</testSQL>
There is also a connectionTimeout setting that may be adjusted to stop the every minute calls to the database but we haven't tested that since our problem was with the bad formatted query.