Our users are experiencing the performance problem using Oracle 8.1.7 database. We are using Business object to display the data as required.

This is the below script using BO.

Can you please suggest me technically how to get resolved this issue?

Please let me know if you need any more information.

Thanks

This is an example of the typical type of query I use, it works o.k. but I think some indexing could make it run much quicker.

I think the TEST.SH_TYS____COMPL.CLOSED_TIME & TEST.SH_TYS____COMPL.CANCELLED_TIME are key fields that could be indexed to speed up the report.

Also I tend to do a lot of reports using the TEST.TYS__COMPL_CONFIGURATION.OWNING_CENTRE as a filter, this isn't that large a table but
uses a table join on TEST.TYS____COMPL.CID = TEST.TYS__COMPL_CONFIGURATION.CID so indexing the TEST.TYS____COMPL.CID field could
provide an improvement.

The TEST.TYS____COMPL.CREATE_DATE is another key field when we want to identify tickets raised during a period.

The TEST.TYS____COMPL.SUBCASE_OF is another key field we filter by.

The TEST.TYS____COMPL.CLEAR_CODE_OBJECT_FAMILY,TEST.TYS____COMPL.CLEAR_CODE_OBJECT & TEST.TYS____COMPL.CLEAR_CODE_ACTION fields may also be useful.

First run the query in for example TOAD, check how long it takes to execute. (Best to count the rows)

select count(*) from (
--here comes your own query
)

Let's say it takes 500ms. Then the problem is not your query but BO. The reason why I mention this is because I've had similar problems.

If it is indeed the query that is slow. Then you can indeed add indexes. Don't forget an index can help you but also slow you down.
Oracle says that the best place the create an index is on the column that has the most distinct values.

But it all depends, it is not that easy. It depends weather your database is using the rule or cost based optimizer. When in cost based, are your statistics up to date, and so on.

So my advice is first check if your reports are slow due to your sql statement and not an other factor.

If so check if your database is in Rule based or cost based, and slowly add some indexes. Starting with the column that has the most distinct values.

It could be that your query is still slow, but at least you will have a better view of what is going on.