Wird der Oracle-Optimizer mehrere Hinweise im selben SELECT verwenden?

Question

Ich versuche, die Abfrageleistung zu optimieren und musste auf die Verwendung von Optimizer-Hinweisen zurückgreifen. Aber ich habe nie gelernt, ob der Optimierer mehr als einen Hinweis gleichzeitig verwendet.

z.B.

SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/
       /*+ LEADING(i vol) */ 
       /*+ ALL_ROWS */ 
       i.id_number,
       ...
  FROM i_table i
  JOIN vol_table vol on vol.id_number = i.id_number
  JOIN zu_einer_reihe_von_anderen_Tabellen...
 WHERE i.solicitor_id = '123'
   AND vol.solicitable_ind = 1;

Der Explain-Plan zeigt die gleichen Kosten, aber ich weiß, dass das nur eine Schätzung ist.

Bitte nehmen Sie an, dass alle Tabellen- und Indexstatistiken berechnet wurden. FYI, der Index dcf_vol_prospect_ids_idx befindet sich in der Spalte i.solicitor_id.

Danke,

Stew

Answer 1

Versuchen Sie, alle Hinweise in einem einzigen Kommentarblock anzugeben, wie in diesem Beispiel aus der wunderbaren Oracle-Dokumentation (http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm).

16.2.1 Festlegen eines vollständigen Satzes von Hinweisen

Beim Verwenden von Hinweisen müssen Sie in einigen Fällen möglicherweise einen vollständigen Satz von Hinweisen angeben, um den optimalen Ausführungsplan zu gewährleisten. Wenn Sie beispielsweise eine sehr komplexe Abfrage haben, die aus vielen Tabellen-Joins besteht und wenn Sie nur den INDEX-Hinweis für eine bestimmte Tabelle angeben, muss der Optimierer die verbleibenden Zugriffspfade und die entsprechenden Join-Methoden bestimmen. Daher kann es sein, dass der Optimierer den INDEX-Hinweis nicht unbedingt verwendet, weil er möglicherweise festgestellt hat, dass der angeforderte Index aufgrund der vom Optimierer ausgewählten Join-Methoden und Zugriffspfade nicht verwendet werden kann.

In Beispiel 16-1 gibt der LEADING-Hinweis die genaue Join-Reihenfolge an, die auf den verschiedenen Tabellen verwendet werden soll; auch die Join-Methoden für die verschiedenen Tabellen sind angegeben.

Beispiel 16-1 Festlegen eines vollständigen Satzes von Hinweisen

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk)
           USE_MERGE(j) FULL(j) */
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal  
FROM employees e1, employees e2, job_history j
WHERE e1.employee_id = e2.manager_id
  AND e1.employee_id = j.employee_id
  AND e1.hire_date = j.start_date
GROUP BY e1.first_name, e1.last_name, j.job_id   ORDER BY total_sal;

Answer 2

Oracle 19c hat das Feature für die Berichterstellung zur Verwendung von Hinweisen eingeführt:

EXPLAIN PLAN FÜR
SELECT /*+ INDEX(i dcf_vol_prospect_ids_idx)*/
       /*+ LEADING(i vol) */ 
       /*+ ALL_ROWS */ 
       i.id_number,
       ...
  FROM i_table i
  JOIN vol_table vol on vol.id_number = i.id_number
  JOIN to_a_bunch_of_other_tables...
 WHERE i.solicitor_id = '123'
   AND vol.solicitable_ind = 1;

SELECT * FROM table(DBMS_XPLAN.DISPLAY(FORMAT=>'BASIC +HINT_REPORT'));
                                                     --============

Es zeigt einen weiteren Abschnitt Hinweisbericht:

Hinweisbericht (identifiziert nach Vorgang ID / Abfrageblockname / Objektkurzname):
Gesamtzahl der Hinweise für die Aussage: ...
---------------------------------------------------
...

Answer 3

Tatsächlich ist die Empfehlung von Jonathan Lewis, Autor von Cost-Based Oracle Fundamentals, dass wenn der CBO nicht in der Lage ist, den richtigen Plan zu finden, Sie die Aufgabe des CBO übernehmen und "Hints" hinzufügen müssen - im Durchschnitt zwei Hinweise pro Tabelle in der Abfrage.

Der Grund dafür ist, dass ein Hinweis möglicherweise zu einem noch schlechteren Plan führen könnte als der CBO allein erhalten würde. Wenn der CBO falsch liegt, müssen Sie ihm den gesamten Plan geben, nicht nur einen Schubs in die richtige Richtung.