OPTIMIZER-Problem

From: Ralf Korell <ralf_at_hydrogen.emi.de>
Date: 1995/08/10
Message-ID: <40d47h$377_at_unlisys.unlisys.net>

Hi, DB-Gurus !

I have three major problems, which are not easy to unsterstand, therefore I have decided to post every problem in a single mail.

Q 1: OPTIMIZER-Problem
Q 2: Performance-Problem (Hardware ?) 
Q 3: failsave database ? 
________________________________________________________________

General Information : we use oracle V 7.1.4.1.0, for performance testing I involved SQL*Plus: Release 3.1.3.2.1 OS is HP-UX V. 9.05

---

so far, let's start with Q 1:

OPTIMIZER Problem :

Caused by some performance-problems, I had to tune some SQL-statements in several program-units (all written in Pro*C). Tuning by hand was very hard and sometimes the desired effect was not guaranteed, so we decided to force the optimizer mode to 'COST'. This resulted into the first problem, because the init.ora parameter OPTIMIZER_MODE won't run with 'COST' (ORA Server Concepts , 13-29), but it runs with 'FIRST_ROWS'.

Q 1a : is this the same ?

we perform with this mode an ANALYZE once a week on our developer-machine (HP 9000/715, HP-UX 9.05). Some statements became much faster and everything seems to be o.k.. Then the surprise : as we put the tuned application on our customers's machine (an HP 9000/887, HP-UX 9.05 ) and force the optimizer mode to FIRST_ROWS, the application SLOWS DOWN (analyze IS performed) !

Q 1b: has anybody an idea about this ?

We forced the optimizer back to RULE and the application is as slow as before but not as slow as with FIRST_ROWS.

for example following statement :

SELECT TO_CHAR(DAT_ZEIT_VON,'DD.MM., HH24:MI'),AUFTRAG.TYP, PATIENT.NACHNAME,   PATIENT.VORNAME, PATIENT.PAT_ID, AUFTRAG.ATG_ID, AUFTRAG.AUFTRAGSART ,   GERAET.BEZEICHNUNG
FROM
  PATIENT, AUFENTHALT, BELEGUNG, AUFTRAG, TERMIN, GERAET   WHERE
  (

  	TERMIN.ATG_ID = AUFTRAG.ATG_ID  AND              
  	TERMIN.FOREIGN_ID = '435953' AND                  
  	AUFTRAG.STATUS = 'T' AND                         
  	AUFTRAG.AUFTRAGSART =  'LABORUNTERSUCHUNG' AND    
  	AUFTRAG.AUFH_ID = AUFENTHALT.AUFH_ID AND          
  	AUFENTHALT.aufn_status  in ('A','T')  AND         
  	AUFENTHALT.PAT_ID = PATIENT.PAT_ID AND            
  	BELEGUNG.AUFH_ID =  AUFENTHALT.AUFH_ID AND        
  	BELEGUNG.STAT_ID = 435599 AND                     
  	BELEGUNG.BEL_STATUS =  'A' AND                   
  	GERAET.GRT_ID = TERMIN.FOREIGN_ID                

  )
ORDER BY AUFTRAG.TYP, GERAET.BEZEICHNUNG, to_char(termin.dat_zeit_von,'YYYY.MM.DD HH24:MI')

I got the following execution plan from tkprof utility:

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   OPTIMIZER HINT: FIRST_ROWS
      1    SORT (ORDER BY)
      1      NESTED LOOPS
      1        NESTED LOOPS
     35          NESTED LOOPS
    156            NESTED LOOPS
   9603              NESTED LOOPS
      1                TABLE ACCESS   OPTIMIZER HINT: ANALYZED (BY ROWID) OF


'GERAET'


      1                  INDEX   OPTIMIZER HINT: ANALYZED (UNIQUE SCAN) OF


'GERAET_GRT_ID_PCON' (UNIQUE)


   9604                INDEX (RANGE SCAN) OF 'TERMIN_FKEY_ATG_VON_IDX' (UNIQUE)
   9603              TABLE ACCESS   OPTIMIZER HINT: ANALYZED (BY ROWID) OF


'AUFTRAG'


   9603                INDEX   OPTIMIZER HINT: ANALYZED (UNIQUE SCAN) OF


'AUFTRAG_ATG_ID_PCON' (UNIQUE)


    156            TABLE ACCESS   OPTIMIZER HINT: ANALYZED (BY ROWID) OF


'AUFENTHALT'


    156              INDEX   OPTIMIZER HINT: ANALYZED (UNIQUE SCAN) OF


'AUFENTHALT_AUFH_ID_PCON' (UNIQUE)


    203          TABLE ACCESS   OPTIMIZER HINT: ANALYZED (BY ROWID) OF


'BELEGUNG'


    238            INDEX   OPTIMIZER HINT: ANALYZED (RANGE SCAN) OF


'BELEGUNG_AUFH_ID_IDX' (NON-UNIQUE)


      1        TABLE ACCESS   OPTIMIZER HINT: ANALYZED (BY ROWID) OF 'PATIENT'
      1          INDEX   OPTIMIZER HINT: ANALYZED (UNIQUE SCAN) OF

'PATIENT_PAT_ID_PCON' (UNIQUE)
the plan is exactly the same when I change the order of columns in the WHERE-clause in any way (I've permuted all !).

In optimizer-mode RULE I got a different execution plan :

EXPLAIN_PLAN

---

  SORT ORDER BY
    NESTED LOOPS

      NESTED LOOPS
        NESTED LOOPS
          NESTED LOOPS
            NESTED LOOPS
              TABLE ACCESS BY ROWID AUFTRAG
                INDEX RANGE SCAN AUFTRAG_AUFTRAGSART_IDX
              TABLE ACCESS BY ROWID AUFENTHALT
                INDEX UNIQUE SCAN AUFENTHALT_AUFH_ID_PCON
            TABLE ACCESS BY ROWID PATIENT
              INDEX UNIQUE SCAN PATIENT_PAT_ID_PCON
          TABLE ACCESS BY ROWID TERMIN
            INDEX RANGE SCAN TERMIN_ATG_ID_IDX
        TABLE ACCESS BY ROWID GERAET
          INDEX UNIQUE SCAN GERAET_GRT_ID_PCON
      TABLE ACCESS BY ROWID BELEGUNG
        INDEX RANGE SCAN BELEGUNG_AUFH_ID_IDX

Q 1c : How can I change the statement to force the RDBMS to use
       the first execution plan whithout using the COST-based 
       optimizer ?

Any comments, suggestions, or questions are appreciated !

Please e-mail me directly and I will post a digest in the newsgroup.

-- 
                              ,,,
                             (o o)
~*~~*~~*~~*~~*~~*~~*~~*~~oOO~~(_)~~OOo~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~
       Please apologize but english is not my native language

           R. Korell, EMi GmbH (Medical Informatics)
         Karl-Marx-Str. 204 , D 12055 Berlin, Germany
 Internet: korell_at_emi.de     Compuserve: 100601.3532_at_compuserve.com
    phone:    +49 (30) 6814032  fax:        +49 (30) 6814031
~*~~*~~*~~*~~*~~*~~*~~*~~(___)~*~(___)~*~~*~~*~~*~~*~~*~~*~~*~~*~~*~
The opinions you read here are mine and not necessarily those of my
employers. Although, I must say they are the truth, the whole truth,
and nothing like the truth. As far as I'm concerned anyway...

Received on Thu Aug 10 1995 - 00:00:00 CEST