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

From: <sybrandb_at_my-deja.com>
Date: 2000/07/26
Message-ID: <8lme9v$9u6$1@nnrp1.deja.com>#1/1

In article <397e9adf.788019882_at_news.mindspring.com>,   nitefrog_at_yahoo.com wrote:
> Hello All,
>
> Here is the problem and I have no clue as to why this is happening.
> WE have a production enviroment that is Oracle 8i. The table has
> 77,000 records in it. This query:
> select patientid, patientname, population
> from tracking_view
> where iscompliant = 0
> and status = 1
> and critical = 1
> and patientclaims_completed = 0
> and patientid = 117890;
>
> takes almost 15 seconds to return the data. In my test enviroment
> this comes back instant. Same number of records and same data.
> The columns are indexed. I am using a concated index on PATIENTID,
> STATUS, PATIENTCLAIMS_COMPLETED, ISCOMPLIANT and a single index on the
> rest. This table is heavily indexed as it is for reporting and never
> really recives any inserts.
>
> For the life of me I cannot speed there damn database up! What is
> going ON!!!! Everything on their sever runs slow but on MY test
> machine which is not as good runs lightning quick. HELP! PLEASE!
>
> ALso I am not sure if the tablespaces are the issues but here is how
> they have it configured:
>
> Name SIZE USED
> ICDB_DATA 8183.828 7942.242
> ICDB_NDX 9130.000 9117.734
> RBS 7241.609 7241.523
> SYSTEM 1519.094 1452.156
> USERS 342.844 326.211
> USR 11997.852 9127.219
> TEMP 50.000 .008
> TEMPORARY 8000.000 88.391
>
> Now I told the DBA that he needed to increase the tablspaces as this
> might be a reason everything is running slow he told me I was FULL of
> it. In either case I am not sure what the problem is and maybe
> someone could shed some light. But to me the system, rbs, ICDB_NDX,
> ICDB_DATA and maybe USR could be the problem as they look almost full
> to me.
>
> If this is ok is there a rule as to when you should increase the
> tablespace?
>
> Thanks for any help as always it is appreciated,
>
> Kev.-
>

This is the usual vague complaint, which can be summarized as: It doesn't work.
Currently we have pretty little clues about the context in which this is happening, so you really should do something to assist us to isolate the problem.
Make sure you have an execution plan of this statement in both databases. Either use set autotrace on explain stats or trace your session.
The size and allocation of tablespaces have NOTHING to do with your problem, unless your statement is sorting on disk. What is more likely is

- higher number of concurrent users on the production database
- Insufficient hitrate
- Insufficient shared pool

etc, etc, etc.
Could you please provide more clues pertaining to Oracle version, platform, cost based or rule based optimizer.

Regards,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Jul 26 2000 - 00:00:00 CDT