Excel-Zugriff auf Oracle

Beim Zugriff von Excel auf Oracle via OLEDB habe ich zuletzt beobachtet, dass sich die Zugriffszeiten für den Aufbau der Liste verfügbarer Objekte zwischen den Oracle-Versionen 10.2.0.4 und 11.1.0.7 massiv erhöht haben. Aber der Reihe nach. Das Vorgehen bei der Bestimmung der Effekte sah in meiner deutschsprachigen Oberfläche folgendermaßen aus:

• Excel 2007 starten
• Menü: Daten
• Menü: Aus anderen Quellen
• Auswahl: vom Datenverbindungsassistenten: importiert Daten ... über OLEDB
• Auswahl: Microsoft Data Access - OLEDB-Anbieter für Oracle
• Angabe von Anmeldeinformationen für Oracle-DB

Das Ergebnis ist eine Auswahlliste mit allen für den gewählten Nutzer sichtbaren Tabellen und Views. Die dabei zum Server geschickte Query ist zwar nicht besonders hübsch, aber zumindest relativ übersichtlich:

SELECT *
FROM   (SELECT NULL                             table_catalog,
               Decode (o1.owner, 'PUBLIC', NULL,
                                 o1.owner)      table_schema,
               o1.object_name                   table_name,
               Decode(o1.owner, 'SYS',
               Decode(o1.object_type, 'TABLE', 'SYSTEM TABLE',
                                      'VIEW', 'SYSTEM VIEW',
                                      o1.object_type),
                                'SYSTEM',
               Decode(o1.object_type, 'TABLE', 'SYSTEM TABLE',
                                      'VIEW', 'SYSTEM VIEW',
                                      o1.object_type),
                                o1.object_type) table_type,
               NULL                             table_guid,
               NULL                             description,
               NULL                             table_propid,
               NULL                             date_created,
               NULL                             date_modified
        FROM   all_objects o1
        WHERE  ( ( o1.object_type = 'TABLE'
                   AND o1.generated != 'Y' )
                  OR o1.object_type = 'VIEW' )
        UNION
        SELECT NULL                        table_catalog,
               Decode (o2.owner, 'PUBLIC', NULL,
                                 o2.owner) table_schema,
               o2.object_name              table_name,
               o2.object_type              table_type,
               NULL                        table_guid,
               NULL                        description,
               NULL                        table_propid,
               NULL                        date_created,
               NULL                        date_modified
        FROM   all_objects o2,
               all_objects o3,
               all_synonyms s
        WHERE  o2.object_type = 'SYNONYM'
               AND ( ( o3.object_type = 'TABLE'
                       AND o3.generated != 'Y' )
                      OR o3.object_type = 'VIEW' )
               AND o2.owner = s.owner
               AND o2.object_name = s.synonym_name
               AND s.table_owner = o3.owner
               AND s.table_name = o3.object_name) tables
ORDER  BY 4,
          2,
          3  ;

ALL_OBJECTS erscheint hier also dreifach, daneben noch einmal ALL_SYNONYMS. Nun zur Performance des Aufbaus der Auswahlliste:

• 10.2.0.4 (DBA): 4 sec. (19.786 rows, 321.193 consistent gets)
• 10.2.0.4 (CONNECT): 72 sec. (2.258 rows, 2.899.642 consistent gets)
• 11.1.0.7 (DBA): 39 sec. (23.152 rows, 383.950 consistent gets)
• 11.1.0.7 (CONNECT): 562 sec. (3.152 rows, 2.728.757 consistent gets)

In beiden Datenbanken wurden zunächst die Statsitiken für das Schema SYS und die Fixed Tables aktualisiert. Auffällig an den Ergebnissen ist, dass die Queries in 11.1.0.7 deutlich länger laufen als in 10.2.0.4 und dass der Benutzer mit DBA-Rechten jeweils deutlich schneller an seine Resultate kommt, als der weniger privilegierte Connect-User, obwohl dieser deutlich weniger Objekte sehen kann, zu deren Ermittlung aber die zehnfache Menge an Blockzugriffen durchführen muss.Verantwortlich für die Laufzeitunterschiede zwischen den Releases ist offenbar vor allem die View-Definition für ALL_OBJECTS, die sich für Version 10 in der Datei catalog.sql und für Version 11 in cdcore.sql findet, wobei in 11 einige Ergänzungen hinzugekommen sind (no_expand hint, neue Spalten: namespace, edition, new object_types for mining und olap elements). Die Zugriffspläne kann man sich natürlich auch noch anschauen, aber ich muss gestehen, dass ich bei komplexen dictionary queries da nicht allzu viel erkennen kann - höchstens, dass in 10 anscheinend mehr NL-Operationen durchgeführt werden, was für einen exakten Zugriff womöglich flotter ist als ein HASH Join; aber den Plan im Detail zu untersuchen ist mir zu mühsam ...

Ich habe den Fall in Charles Hoopers Blog angesprochen und dort den Vorschlag bekommen, die Query über DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE umzuleiten, was wahrscheinlich funktionieren würde. Dabei könnte man mit CTEs für den ALL_OBJECTS-Zugriff arbeiten, was vor allem für den extrem langsamen Fall mit 11.1.0.7 und Connect vermutlich lohnend wäre. Alternativ verweist der Herr Hooper auf Tom Kytes Motto "that the fastest way to do something is to not do it at all" - das wäre unter Umständen auch ein guter Workaround.