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

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.- Received on Wed Jul 26 2000 - 00:00:00 CDT