Forum Stats

  • 3,838,643 Users
  • 2,262,385 Discussions
  • 7,900,715 Comments

Discussions

Monitoring Disco Usage during the working day

StuartHibbert
StuartHibbert Member Posts: 13
edited Sep 26, 2008 8:24AM in Discoverer
Hello everyone,

In my organisation we use Discoverer 10g (10.1.2) and have a large userbase (approx 1000), typically perfomance suffers at month end a peak usage time for the system.

Last month we approached users and asked them to record their experiences in timings when running and exporting the reports, this has been good in getting a general view of how things are but not giving me what I ideally want in terms of detail.

I was wondering if anyone could give me direction in how to obtain the kind of information I am wanting including:

1. A view on activity at any point - see what queries are running, by who and how long they have ran for.
2. See how many user Disco sessions are in progress.
3. Any detail on times against individual reports in terms of running, exporting and estimates given.

I have just recently been given SQL access to our system and have a smalll knowlege of SQL so would appreicate any help that can be given to help me get at this info as I am sure it is somewhere in the system.

I am in the process of getting our 3rd party guys to install the EUL workbooks and make these available to me - request has taken 2 months so far though so if I can get this data through SQL myself it would make my life much easier.

if anyone has any general comments on performance and ways to monitor I would be intersted to hear them!

Answers

  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Hi,

    For information about what is currently happening on the system the v$sessions dynamic view is the starting place. This query will give you the Discoverer users:

    select username, status, program, module
    from v$session vs
    where module like 'Disco10%'

    This query will give you the SQL that they are running:

    select username, status, vsql.sql_text
    from v$session vs
    , v$sql vsql
    where vs.module like 'Disco10%'
    and vs.sql_id = vsql.sql_id
    and vs.sql_hash_value = vsql.hash_value

    We have developed a set of Discoverer monitoring reports based on the dynamic views which shows us what reports are being run by which users.

    If you want information about what Discoverer reports have been run then you need to ensure that the Discoverer environment has statistics collection switched on. Then monitor the EUL5_QPP_STATS table in the EUL schema to get all the information on what reports have been run and how long they took.

    Rod West
This discussion has been closed.