Simple queries locking up DB Server CPU

[andyp222]

Hi,

We are using version 4.5 of OTM and have 10 or so basic queries that populate users Business Monitors. Under normal circumstances each query takes 10-30 secs to execute but occasionally one of these queries will lock up the database server taking close to 100% CPU until the SQL session is killed (and the system becomes unusable until this happens). The client has a very short timeout on their HTTP proxy (2 minutes) and the problem seems to be related to when a timeout occurs during population of the BM (or during Finder set searches). Oracle suggest that the queries are too complex and should be simplified - one recent example query was 'select count(*) from ob_order_base'. I am still awaiting a response to exactly how this query can be simplified!

Has anyone seen anything similar or have any ideas as to what the issue might be.

Any thoughts greatly appreciated. Solutions or explanation even more welcome.

[Srivathsana]

Re: Simple queries locking up DB Server CPU

Hi Andy,

I would simplify the mentioned query as below :

select count(order_base_gid) from ob_order_base

I am also attaching a screenshot which shows the difference in elapsed time between count(*) and count(column_name).
This difference could become very significant in terms of sql performance when many number of queries are running in the system.It is also a possibility that the queries run for a long time and lock the DB,if run on unindexed tables at peak business hours where the application gets hit with lots of user queries.
A humble suggestion would be to analyse all the queries running in BMs and tune them down.

[andyp222]

Re: Simple queries locking up DB Server CPU

Thanks for the input - interesting to see the improvement when you specify a column name. Unfortunately it is OTM that is generating queries similar to this when it is populating the BM numbers so we have no control over this. Its does seem to be when we have lots of user's running queries that causes a particular query to suddenly lock up the DB.

[Srivathsana]

Re: Simple queries locking up DB Server CPU

Hi Andy,

By BM i meant Business Monitors.Sorry had i made you confuse it with Business Numbers.I hope you could get a significant improvement by checking the queries running inside Business Monitors.
It's worth a try.

[cunninghamg]

Re: Simple queries locking up DB Server CPU

I believe Andy is also talking about Business Monitors..

When the BM is opened the sql in the specified query is converted by OTM into a 'Select count(*) ' query to populate the number of records.

We have also seen runaway queries - where the query is simple and would normally execute in miliseconds but seems to hog CPU until the process is killed.

Gary