Monat: März 2015

Lösung: Viele Hierarchien gleichzeitig navigieren

Zuerst Legen wir die nötigen Indexe an:


create index i1 on tree_emp( empno);
create index i2 on tree_emp( mgr);

Dannach definieren wir das pipe table package. Natürlich geht es auch effizienter, aber ich möchte hier das Prinzip zeigen.


create or replace package parallel_access
as

TYPE R_REC IS RECORD (empno tree_emp.empno%TYPE,
sum_salaries Number); — result record, change defintion according to your needs
TYPE refcur_t IS REF CURSOR RETURN R_REC;

TYPE result_Tab is TABLE OF R_REC;

FUNCTION passData (p_ref refcur_t) RETURN result_Tab
PIPELINED
PARALLEL_ENABLE(PARTITION p_ref BY ANY); — function will inherit parallelism from ref cursor

END parallel_access;
/

create or replace package body parallel_access
as
FUNCTION passData (p_ref refcur_t) RETURN result_Tab
PIPELINED PARALLEL_ENABLE(PARTITION p_ref BY ANY)
IS
out_rec r_rec;
BEGIN
execute immediate ‚alter session set „_old_connect_by_enabled“=true‘;
LOOP — for each parallel process
FETCH p_ref INTO out_rec;
EXIT WHEN p_ref%NOTFOUND;
SELECT sum(sal)
INTO out_rec.sum_salaries
FROM tree_emp
CONNECT BY PRIOR EMPNO = MGR
START WITH mgr = out_rec.empno;

PIPE ROW(out_rec);
END LOOP;
RETURN;
END passData;

END parallel_access;
/

Verwenden Können wir as Pacakge wie folgt. Parallel 4 ist nur ein Beispiel, man kann die Parallelität je nach Bedarf wählen.

SELECT b.* FROM
TABLE(parallel_access.passdata (CURSOR( select /*+ parallel (d 4) */ empno , null from tree_emp where mgr is null))) b;

Viele Hierarchien gleichzeitig navigieren

Nehmen wir an Sie hätten eine grosse Anzahl flacher Hierachien. Als Beispiel nehmen wir die emp table aus dem Scott Schema.
Daraus erzeugen wir eine Tabelle Tree_emp wie folgt:


create table tree_emp as select * from emp;

alter table tree_emp modify (empno number(18));
alter table tree_emp modify (mgr number(18));

INSERT INTO tree_emp
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
)
;
SELECT EMPNO+(10000*step.n) empno,
ENAME,
JOB,
MGR+(10000*step.n) mgr,
HIREDATE,
TRUNC(DBMS_RANDOM.value(low => 8, high => 50))*100 SAL,
COMM,
DEPTNO
FROM emp,
(SELECT rownum n FROM dual CONNECT BY rownum <= 1000000
) step
;

Weisen Sie für alle Presidenten (mgr is NUll) die Summe der Gehälter sämtlicher Untergebener aus. Schreiben Sie dau eine pipeline table function, die mehrere Bäume gleichzeitig navigieren kann. Verwenden Sie Oracle’s connect by Syntax.für die Baumnavigation.
Sie können nach Belieben indexieren.

Solution: Runtimestatistiken lesen

Runtime statistiken sind für mich das wichtigste Optimierungswerkzeug für nicht parallele Abfragen.
Runtime statistiken lassen sich schnell erzeugen und beinhalten in der Regel schon alle nötigen Informationen für eine Optimierung.
Ich wiederhole zunächst die Runtimestatistiken der Rätselfrage:

 

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:15.42 |    3540 |      3262 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:15.42 |    3540 |      3262 |
|*  2 |   TABLE ACCESS BY INDEX ROWID|   T1 |      1 |  52695 |     10 |00:00:15.42 |    3540 |      3262 |
|*  3 |    INDEX RANGE SCAN          |   I1 |      1 |     17M|   4577 |00:00:00.16 |      11 |         9 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("NVL("X1_STATUS",'A')<>'U' AND NVL("X2_STATUS",'A')<>'D' 
               AND NVL("X3_STATUS",'A')<>'D' AND NVL("X4_STATUS",'A')<>'D' 
               AND NVL("X5_STATUS",'A')<>'U'))
   3 - access("T1_ID"=:B2 AND "T2_ID"=:B4 AND "T3_ID"=:B1)

Bei dem Problem fällt zunächst einmal auf, dass die Schätzung des Optimizers beim Indexzugriff in Operation 3 weit daneben liegt. Hierzu werden die „estimated rows“  (E-rows) mit den „actual rows“ (A-Rows) verglichen. Da es sich um eine sehr einfach zusammengesetzte Bedingung mit lauter „=“ Vergleichen handelt liegt es nahe, hier eine funktionale Abhängigkeit zu vermuten.

Die entsprechende Gegenmassnahme wären extended statistics.
Jedoch wird die meiste Zeit in der Operation 2 verloren. Hier fallen die „actual rows“ von 4577 Datensätzen in Operation 3 auf 10 Datensäte ab. Verantwortlich dafür ist die Filterbedingung in Operation 2:

2 - filter(("NVL("X1_STATUS",'A')<>'U' AND NVL("X2_STATUS",'A')<>'D' 
               AND NVL("X3_STATUS",'A')<>'D' AND NVL("X4_STATUS",'A')<>'D' 
               AND NVL("X5_STATUS",'A')<>'U'))

Gemäss der Tuning Technik „Throw-away“ erfunden vom Oak Table Mitglied Martin Berg müssen wir zusätzliche Spalten in den Index hinzuzufügen um den „throw-away“ in operation 2 zu vermeiden.

Ich habe sehr kluge Empfehlungen in meinem Kommentaren gefunden. Allerdings muss man in diesem Falle nicht sehr kompliziert vorgehen. Wir haben die Status Felder getestet und herausgefunden welche die Selektivität des Indexes erhöhen. Diese haben wir einfach in den Index eingefügt. Fälschlicherweise war der Entwickler der Applikation der Meinung die Statusfelder würden Nichts nützen, weil bei einem ungleich Vergleich kein Index verwendet werden kann.

Das ist nicht korrekt. Die Baumstruktur kann nicht verwendet werden. Jedoch wird bei einem Index Range Scan über die Baumstruktur nur der erste Leaf Block gesucht. Ab dann wird serial in den Leaf Blöcken weiter gesucht.

Bei dieser seriellen Suche kann über beliebige Vergleichsoperatoren gefiltert werden. Wie wir anhand der unten stehenden Runtime Statistiken sehen, reicht das schon um eine gute Performance zu gewährleisten. Der Verbesserungsfaktor liegt bei run 700.

Nicht einmal die Verbesserung der Statistiken ist für diesen Fall letztlich erforderlich, da der Optimizer zwar falsch schätzt, aber die zusätzlichen Spalten dennoch als Vorteil wahrnimmt.

Natürlich wäre die extened statistics unabhängig davon trotzdem sinnvoll.

 

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |      1 |        |      1 |00:00:00.02 |   12 |         4 |
|   1 |  SORT AGGREGATE              |                     |      1 |      1 |      1 |00:00:00.02 |   12 |         4 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| MM_SALES_DELIVERIES |      1 |  52695 |     10 |00:00:00.02 |   12 |         4 |
|*  3 |    INDEX RANGE SCAN          | PR_SALE_DEL_03      |      1 |  52695 |     10 |00:00:00.01 |    6 |         0 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((NVL("MM_DISTRIBUTION_DEL_STATUS",'H')<>'D' AND NVL("MM_OUTBOUND_DEL_STATUS",'H')<>'D' AND
              NVL("MM_OUTBOUND_STATUS",'H')<>'U'))
   3 - access("MM_WAREHOUSE_ID"=:B4 AND "MM_FIRM_ID"=:B1 AND "MM_ITEM_ID"=:B2 AND "MM_FROM_LOCATION_ID"=:B3)
       filter((NVL("MM_DISTRIBUTION_STATUS",'H')<>'U' AND NVL("MM_DIRECT_DEL_STATUS",'H')<>'D'))