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

From: The NiteFrog.- <nitefrog_at_yahoo.com>
Date: 2000/07/26
Message-ID: <397ee14e.806050839@news.mindspring.com>#1/1

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.

Version of Oracle in enviroment test:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production running on NT as well.

Context:
How can I tell weither it is COST or RULE based?

Below is two query that are the simplest and fastest and still is slow in production.

---

This query takes 3 seconds in Production and 0 in test the table only has 10 records in it:

select PROGRAM from meddb.enrollment_codes where ENROLLMENTCODE = 'A'

---

This query takes 5 seconds in production and 0 seconds in test. Same number of records in both systems.

select distinct m.MPM_Name, m.MPM_DOB, t.Coverage_ID, t.CLINIC_ID

    from MEDDB.Tracking_View t, ICDB.MPM_Patient m

      where t.PatientId = 1235658
        and t.patientid = m.mpm_patient_id

Here is the plan for the above query:

Test Enviroment:

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (UNIQUE)

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TRACKING_VIEW'
   4    3         INDEX (RANGE SCAN) OF 'IDX_PSPI_TRACKINGVIEW'


(NON-UNIQUE)

   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'MPM_PATIENT'
   6    5         INDEX (RANGE SCAN) OF 'IDX_MPM_MPM_PATIENT_ID'

(NON-UNIQUE)
Statistics

---

          0  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
       1897  bytes sent via SQL*Net to client
        960  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
===============================================================================
===============================================================================

Production:
Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=100)    1 0 SORT (UNIQUE) (Cost=13 Card=1 Bytes=100)

   2    1     NESTED LOOPS (Cost=11 Card=6 Bytes=600)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'MPM_PATIENT' (Cost=3
Card=1 Bytes=89)
   4    3         INDEX (UNIQUE SCAN) OF 'SYS_C002171' (UNIQUE)


(Cost=2 Card=1)

   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'TRACKING_VIEW'


(Cost=8 Card=6 Bytes=66)

   6    5         INDEX (RANGE SCAN) OF 'IDX_PSPI_TRACKINGVIEW'

(NON-UNIQUE) (Cost=2 Card=6)

Statistics

---

          0  recursive calls
          0  db block gets
         12  consistent gets
          1  physical reads
          0  redo size
       1903  bytes sent via SQL*Net to client
        964  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

Thank you again, if there is any information I can give please let me know.

Again Thanks,

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