Occasionally, my office's OLAP Express Server would suddenly suffer a
drop in performance, a usual data loading would take over 10 hours
instead of 2-3 hours.

There is no one with strong database administrative skill in our
company, and I got a hard time isolating the source of the poor
performance.

My HPUX RISC9000 server was 3 separate instances with 4GB of memory
with 3 CPU. Database cube is relatively small at about 100-150GB each
instance each containing 3 years of sales info.

Is there a way I can list out all the user session without the GUI
Session Manager interface?

My guess is that there are defunct or runways process when a user
failed to logout properly when their PC stalled.
Usually I would stop the instance individually to identify the culprit
instance, but is there a appropriate approach without incurring cost of
getting tools?

I am not a DBA, had only limited knowledge in SQL scripts.

Anyone?

Thanks?

Hi

While there is not much info to go on here, assuming you are comparing
like jobs, it does sound as if something else - some other process on
the server - is taking resources that usually are there for Express to
use. I would recommend that you contact Oracle Support who should be
able to help you diagnose what is going on.

Helpful information to help them will include :
1) the log files created by the build/refresh processes - a 'normal' 2-3
hour run, and an example bad one'. Depending on the application/tools
you are using to buid the cubes, I would expect there to be a detailed
logfile created with every step of the processing and detailed timings
etc. This will give some clues as to what your Express job atleast was
doing when things went slow.
2) if you can provide it, statistics from the Server regarding what was
happening with CPU's Memory, Disk and other processes during the times
when your build went slow.
3)version information (what version of Express Server, what
application/tool is building/refreshing your multidimensional cubes etc)

If you are worried that there could be old user sessions still running
when you do your builds/incremental refreshes - then you may consider
stopping and restarting the instances briefly immediately before running
the build - this would make 100% sure you drop any errant user sessions
that are taking resource, and/or may have locks on parts of your
database.

You can do this in a number of ways, including from the Unix Command
line if you have priviledges to do that (check out OESMGR command in the
Administration Guide documentation it allows you to stop and start
Express Server instances from the operating system).
eg > oesmgr stop [immediate] <servicename>
eg > oesmgr start <servicename>

An extension to this idea is to configure different instances - eg one
tuned for end user query access and large numbers of concurrent users
etc, and one tuned for fast builds and loads (eg one or a small number
of sessions doing 'bigger' things like data loading, aggregation and
calculation.).
Then, in the unix script that runs your batch process, stop the 'user'
instance, start the 'update' instance, run the batch job, stop the
'update' instance and start the 'user' instance. Sounds complicated
but it is 4 lines in your Unix script and will ensure that your database
is updated and maintained efficiently.

Let me know offline (replace the 'dot' with a '.') if you need help
contacting Oracle Support.

Regards, Kevin (@ Oracle)

iamverykiasu (AT) hotmail (DOT) com wrote:

I take it your servers are uing RAID architecture

sometimes versions of unix running under a combination of RISC and RAID
cause issues with RAID trying to copy temporary express files.

this is very difficult to track as it will only occur on random
occasions with no pattern, as it it will require RAID to try and access
a very large temporary file.

This has happened with a couple of our customers, but I have not heard
about it for a long time, again it may depend upon the versions of
Express and RAID.

you could try disabling the RAID processing the next time it happens
and try running your EXPRESS load again, it may point you in the right
direction

regards

Graham

Kevin Lancaster wrote: