Re: sqr using 90% cpu

[Kenneth LO <kenlo@HK.SUPER.NET>]

Bob Shiflett wrote:
>
> We are having a major problem with some of our sqr reports taking up to 90%
> of the cpu.  We have run the reports through SQL-LAB and explain.sqr and
> they seem to be o.k, No full table scans, using the indexes.  We tried
> moving the tables around in the where clause and this seemed to bring one
> report in particular to 40% but this is still to high. We are using SQR
> V3.0.12.1  on a HP-UX 9000.

In my experience the SQRs can take up all the 6 CPUs on a Sparc1000 so
the server just drop to a standstill. The o/s (solaris 2.x) manual said
that it can happen for database operations. I can't remember the exact
wordings but essentailly it is saying that the longer a database
operation runs the larger time slice it will acquire. The solutions
tried :-
a) tune Orcale (not much use)
b) tune SQLs in SQRs (not much use)
c) bind SQRs that have been running for more than 5 minutes to a
dedicated
   cpu -- quite useful if you wish to maintain delivery of quick SQRs
and
   server's response to interactive sessions. Slow SQRs can still be run
but
   will take longer to complete.
d) schedule really large SQR jobs to run at night

Other solutions considerd but not tried :-
e) bind all SQRs to a dedicated CPU -- obviously not as good as (c)
f) create a queue of SQR jobs and fire them one by one i.e. at any one
time
   there is only one SQR job on the server