Große Menge projizierter E / A mit Oracle, selbst wenn nur ein einzelner datasatz abgerufen wird

In meinen Oracle-Ausführungsplänen stoße ich oft auf folgende Situation:

Operation | Object | Order | Rows | Bytes | Projection ----------------------------+---------+-------+------+-------+------------- TABLE ACCESS BY INDEX ROWID | PROD | 7 | 2M | 28M | PROD.VALUE INDEX UNIQUE SCAN | PROD_PK | 6 | 1 | | PROD.ROWID 

Dies ist ein Auszug aus einem größeren Ausführungsplan. Im Wesentlichen greife ich auf eine Tabelle zu, indem ich den Primärschlüssel der Tabelle verwende. In der Regel gibt es eine weitere Tabelle ACCO mit ACCO.PROD_ID = PROD.ID , wobei PROD_PK der Primärschlüssel für PROD.ID . Natürlich kann man auf die Tabelle mit einem UNIQUE SCAN , aber sobald ich eine dumme Projektion auf dieser Tabelle habe, scheint es so, als ob die gesamte Tabelle (ungefähr 2 Millionen Zeilen) im memory gelesen werden soll. Ich bekomme eine Menge I / O und Puffer bekommt. Wenn ich die Projektion aus der größeren Abfrage entferne, verschwindet das Problem:

 Operation | Object | Order | Rows | Bytes | Projection ----------------------------+---------+-------+------+-------+------------- TABLE ACCESS BY INDEX ROWID | PROD | 7 | 1 | 8 | PROD.ID INDEX UNIQUE SCAN | PROD_PK | 6 | 1 | | PROD.ROWID 

Ich verstehe dieses Verhalten nicht. Was könnten die Gründe dafür sein? Hinweis: Ich kann die vollständige Abfrage nicht posten. Es ist ziemlich komplex und beinhaltet viele Berechnungen. Das Muster ist jedoch oft das gleiche.

UPDATE : Ich habe es geschafft, mein ziemlich komplexes Setup auf eine einfache Simulation zu reduzieren, die in beiden Fällen einen ähnlichen Ausführungsplan erzeugt (wenn PROD.VALUE projiziert PROD.VALUE oder wenn man es PROD.VALUE ):

Erstellen Sie die folgende database:

 -- products have a value create table prod as select level as id, 10 as value from dual connect by level < 100000; alter table prod add constraint prod_pk primary key (id); -- some products are accounts create table acco as select level as id, level as prod_id from dual connect by level < 50000; alter table acco add constraint acco_pk primary key (id); alter table acco add constraint acco_prod_fk foreign key (prod_id) references prod (id); -- accounts have transactions with values create table trxs as select level as id, mod(level, 10) + 1 as acco_id, mod(level, 17) + 1 as value from dual connect by level < 100000; alter table trxs add constraint trxs_pk primary key (id); alter table trxs add constraint trxs_acco_fk foreign key (acco_id) references acco (id); create index acco_i on acco(prod_id); create index trxs_i on trxs(acco_id); alter table acco modify prod_id not null; alter table trxs modify acco_id not null; 

Führen Sie die folgende Abfrage aus

 select v2.* from ( select -- This calculates the balance for every transaction as a -- running total, subtracting trxs.value from the product's value -- -- This is the "projection" I mentioned that causes I/O. Leaving it -- away (setting it to 0), would improve the execution plan prod.value - v1.total balance, acco.id acco_id from ( select acco_id, sum(value) over (partition by acco_id order by id rows between unbounded preceding and current row) total from trxs ) v1 join acco on v1.acco_id = acco.id join prod on acco.prod_id = prod.id ) v2 -- This is the single-row access predicate. From here, it is -- clear that there can only be 1 acco and 1 prod where v2.acco_id = 1; 

Analysieren

Bei der Analyse von Ausführungsplänen für die obige Abfrage (mit oder ohne prod.value Projektion) kann ich beim Zugriff auf die prod Tabelle eine übermäßige Menge an Zeilen / Bytes im Plan reproduzieren.

Ich habe einen Workaround für dieses Problem gefunden . Aber ich bin wirklich interessiert an einer Erklärung darüber, was schief läuft und wie ich dieses Problem korrigieren könnte, ohne die Abfrage zu sehr zu ändern

Aktualisieren

OK, nach viel mehr Analyse, muss ich sagen, dass die tatsächliche problematische I / O aufgrund eines falschen Index wurde irgendwo anders verwendet. Leider war dies in den Gesamtstatistiken (bzw. im Ausführungsplan) nicht gut genug zu prognostizieren.

Was diese Frage betrifft, bin ich immer noch neugierig auf die projizierte I / O im Ausführungsplan, da dies unseren DBA (und mich) immer wieder zu verwirren scheint. Und manchmal ist es wirklich die Ursache von I / O-Problemen …

Es könnte interessant sein zu bemerken, dass ich verschiedene Szenarien überprüft habe, einschließlich einer spezifischen Lösung für das spezifische Beispiel. Ersetzen Sie die Beispielabfrage so, dass sie das Problem in diesem Fall lösen würde:

 select -- Explicitly project value in a nested loop. This seems to be much cheaper -- in this specific case (select value from prod where id = v2.prod_id) - v2.balance, v2.acco_id from ( select -- Now, balance is only a running total, not the running total -- added to PROD.VALUE v1.total balance, acco.id acco_id, acco.prod_id prod_id from ( select acco_id, sum(value) over (partition by acco_id order by id rows between unbounded preceding and current row) total from trxs ) v1 -- The JOIN of PROD is no longer needed join acco on v1.acco_id = acco.id ) v2 where v2.acco_id = 1; 

Aber ich verstehe immer noch nicht, warum Oracle so viel I / O in seinen Ausführungsplan projizieren würde, wenn ich mich früher bei dieser Abfrage stoße …

Wenn Sie v1.total auswählen, lösen Sie eine no_merge-view aus.

Wenn die Analysefunktion in Unterauswahlen verwendet wird, müssen die Unterauswahlen aufgetriggers werden, bevor sie mit dem Rest verbunden werden. In diesem Fall wird daher v1 vollständig ausgeführt und die gesamte Ergebnismenge wird "abgerufen", bevor sie verbunden wird. Und wenn man sich die Abfrage anschaut, bedeutet das, dass man einen vollständigen Scan auf trxs + eine sorting nach der analytischen function durchführt

Beim Auskommentieren von v1.total führt der Optimierer die view zusammen und ignoriert die function vollständig, da sie sieht, dass sie nicht verwendet wird.

Aktualisieren

Ich habe Ihre Probe verwendet, hier ist die Geige für Ihre ursprüngliche Abfrage und für Ihre Lösung . Erklären Pläne unterscheiden sich in der "einzigartigen Scan von Prod". Explain Plan hat keine Möglichkeit, die Kosten von Abfragen in der SELECT-Klausel genau zu schätzen, es zeigt, wie es beim Abrufen einer Zeile ausgeführt wird, aber es gibt nicht an, wie oft es ausgeführt wird, und es nicht es kosten. Die Kosten, die Sie dort sehen, sind nur die Kosten, um die erste Zeile zu holen, aber die Abfrage wird jedes Mal ausgeführt, wenn Sie eine Zeile abrufen, und der Ausführungsplan hat keine Ahnung, wie viele Sie abrufen werden. Das sollte die Kosten- und IO-Vorhersageunterschiede erklären.

Nebenbei: Abfragen in der Select-Klausel werden nicht skaliert, es sei denn, Sie sind sicher, dass die over-Abfrage eine endliche, vorhersehbare und überschaubare Anzahl von Zeilen zurückgibt. Vermeiden Sie es, sie zu verwenden. Sie werden kommen und dich später beißen 🙂

y