Re: Database screaching to a halt and cant figure it out. Any advice??

"The NiteFrog.-" <nitefrog_at_yahoo.com> wrote in message news:397ee14e.806050839_at_news.mindspring.com...
> Hello Sybrand,
>
> Thank you for your response. The problem is that the DBA we are
> working with is not very helpful and it is hard to get an answer
> from him.
>
> The version of oracle for production is:
> Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
> running on NT platform.
>
> Any query that I run either simple or complex takes forever and
> a day to come back in the production enviroment.
>

Hi

One reason the DBA maybe 'not very helpful' might be that you are not giving enough information. In the first message you posted an example query which took 15 seconds and said that the environments were the same. However your followup message has two different explain plans utilising different indexes and is for a new select statement.

I conclude

1. The two environments are different.
2. Your problem definition is somewhat loose.

For message one it seemed to me that you perhaps had an opportunity to use bitmap indexes, since you were on 8i and has column names isX with values of 0 & 1 suggesting boolean fields. If you have a frequently queried columns with low cardinality which is never (or rarely) updated then you should definitely consider the use of bitmap indexes.

However, having now read message two it is clear that different execution plans are being chosen for the same query and therefore the environments are not the same. At the very least an analyze may need to be done if the cost based optimiser is being used.

I'd therefore look at getting the dev environment as close as poss to live as a first step.

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK