Statistiken - Voraussetzung für gute Performance

Statistiken sind Voraussetzungen für den Optimizer, um einen optimalen Ausführungsplan bestimmen zu können.

Überblick

Der Oracle Optimizer benötigt zur Bestimmung des Ausführungsplans statistische Informationen über die beteiligten Objekte. Folgende aktuelle Statistiken sollten vorhanden sein:

Oracle Data Dictionary Statistics
Fixed Table Statistics
Schema Statistics
System Statistics

Oracle empfiehlt, die fixed_objects_stats nur einmalig oder nach einer signifikanten Änderung im Workload der Applikation auszuführen.

Erstellen der Statistiken

Die Schema-Statistiken werden wie folgt erstellt:

SQL / PL/SQL

BEGIN
dbms_stats.gather_schema_stats(
	ownname          => 'SCOTT', -
	options          => 'GATHER AUTO',
	estimate_percent => dbms_stats.auto_sample_size,
	method_opt       => 'for all columns size repeat',
	cascade          => true,
	degree           => dbms_stats.auto_degree
);
END;

Für die Art der Erstellung stehen folgende Möglichkeiten zur Verfügung:

GATHER: Reanalyse des gesamten Schemas
GATHER EMPTY: Es werden nur Objekte ohne Statistiken analysiert
GATHER STALE: Es werden nur Objekte analysiert, die mehr als 10% Änderungen (insert, update, delete) haben.
GATHER AUTO: Es werden nur Objekte analysiert, die keine oder veraltete (stale) Statistiken haben.
method_opt repeat: Es werden nur Indexe mit existierenden Histogrammen analysiert und keine neuen Histogramme erstellt.

Achtung: Histogramme können auch einen negativen Effekt haben. Dieser kann bei der Nutzung von Bind-Variablen auftreten. Dabei muss Oracle das sogenannte 'Bind Variable Peeking' einsetzen. Sind die Verteilungen der Werte sehr inhomogen (skewed) ist es unter Umständen besser die Histogramme zu löschen.

Parallele Erstellung von Statistiken

Unter Oracle 11 gibt es die Möglichkeit, den Globalen Stats Parameter 'Concurrent' zu setzen. Damit erfolgt die Erstellung von Schema- oder Database Statistiken in parallel laufenden Jobs. Die Erstellung von Statistiken ist somit mit kürzeren Laufzeiten möglich.

SQL / PL/SQL

Begin
	DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
End;

Der SYSAUX Tablespace muss online sein da der Job Scheduler die internen Tabellen dort ablegt. Der Parameter job_queue_processes sollte auf mindestens 4 stehen.Zusätzlich sind die folgenden Datenbankprivilegien notwendig:

CREATE JOB
MANAGE SCHEDULER
MANAGE ANY QUEUE

Explain Plan

Mithilfe der Explain-Plan Funktionalität sehen sie den Ausführungsplan, den Oracle aktuell wählen würde. Voraussetzung für die Anzeige der Pläne ist eine aktuelle plan_table. Dieser kann mit dem Script $ORACLE_HOME/rdbms/admin/utlxplan.sql angelegt werden. Der Ausführungsplan für ein bestimmtes SQL wird wie folgt erstellt:

SQL*Plus

SQL> explain plan for select * from user_objects where object_name like '%B%';
Explained.
SQL>

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200

SELECT * FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',null,'ALL'));

PLAN_TABLE_OUTPUT

Plan hash value: 1594398656

-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |    51 | 10608 |     6   (0)| 00:00:01 |
|   1 |  VIEW                          | USER_OBJECTS |    51 | 10608 |     6   (0)| 00:00:01 |
|   2 |   UNION-ALL                    |              |       |       |            |          |
|*  3 |    FILTER                      |              |       |       |            |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID| OBJ$         |    53 |  3816 |     5   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | I_OBJ2       |    53 |       |     1   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| IND$         |     1 |     8 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID | LINK$        |     1 |    26 |     1   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN           | I_LINK1      |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1 / USER_OBJECTS@SEL$1
   2 - SET$1
   3 - SEL$2
   4 - SEL$2 / O@SEL$2
   5 - SEL$2 / O@SEL$2
   6 - SEL$4 / I@SEL$4
   7 - SEL$4 / I@SEL$4
   8 - SEL$5 / L@SEL$5
   9 - SEL$5 / L@SEL$5

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

   3 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
              "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
   4 - filter(BITAND("O"."FLAGS",128)=0)
   5 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME" LIKE '%B%' AND "O"."LINKNAME" IS NULL AND
              "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_')
   6 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
   7 - access("I"."OBJ#"=:B1)
   9 - access("L"."OWNER#"=USERENV('SCHEMAID'))
       filter("L"."NAME" LIKE '%B%')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "USER_OBJECTS"."SUBOBJECT_NAME"[VARCHAR2,30],
       "USER_OBJECTS"."OBJECT_ID"[NUMBER,22], "USER_OBJECTS"."DATA_OBJECT_ID"[NUMBER,22],
       "USER_OBJECTS"."OBJECT_TYPE"[VARCHAR2,57], "USER_OBJECTS"."CREATED"[DATE,7],
       "USER_OBJECTS"."LAST_DDL_TIME"[DATE,7], "USER_OBJECTS"."TIMESTAMP"[VARCHAR2,57],
       "USER_OBJECTS"."STATUS"[VARCHAR2,21], "USER_OBJECTS"."TEMPORARY"[VARCHAR2,3],
       "USER_OBJECTS"."GENERATED"[VARCHAR2,3], "USER_OBJECTS"."SECONDARY"[VARCHAR2,3]
   2 - STRDEF[128], STRDEF[30], STRDEF[22], STRDEF[22], STRDEF[19], STRDEF[7],
       STRDEF[7], STRDEF[19], STRDEF[7], STRDEF[1], STRDEF[1], STRDEF[1]
   3 - "O"."OBJ#"[NUMBER,22], "O"."DATAOBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."SUBNAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22], "O"."CTIME"[DATE,7],
       "O"."MTIME"[DATE,7], "O"."STIME"[DATE,7], "O"."STATUS"[NUMBER,22],
       "O"."FLAGS"[NUMBER,22]
   4 - "O"."OBJ#"[NUMBER,22], "O"."DATAOBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."SUBNAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22], "O"."CTIME"[DATE,7],
       "O"."MTIME"[DATE,7], "O"."STIME"[DATE,7], "O"."STATUS"[NUMBER,22],
       "O"."FLAGS"[NUMBER,22]
   5 - "SYS_ALIAS_2".ROWID[ROWID,10], "O"."NAME"[VARCHAR2,30],
       "O"."SUBNAME"[VARCHAR2,30]
   6 - "I".ROWID[ROWID,10], "I"."TYPE#"[NUMBER,22]
   7 - "I".ROWID[ROWID,10]
   8 - "L"."NAME"[VARCHAR2,128], "L"."CTIME"[DATE,7]
   9 - "L".ROWID[ROWID,10], "L"."NAME"[VARCHAR2,128]

71 rows selected.

Vorsicht ist jedoch geboten, da dieser Ausführungsplan zur Laufzeit des Codes abweichend sein kann. Hier bieten sich mehrere Möglichkeiten. Zu aller erst kann der Ausführungsplan sowie wesentliche Faktoren für die Performance über die 'set autotrace on' Funktion ausgegeben werden:

SQL*Plus

SQL> set autotrace on;
SQL> select count(*) from (select * from user_objects where object_name like '%B%');

  COUNT(*)
----------
         3

Execution Plan
----------------------------------------------------------
Plan hash value: 1724887145

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 |    66 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                 |              |     1 |    66 |            |          |
|   2 |   VIEW                          | USER_OBJECTS |    51 |  3366 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL                    |              |       |       |            |          |
|*  4 |     FILTER                      |              |       |       |            |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID| OBJ$         |    53 |  2120 |     5   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | I_OBJ2       |    53 |       |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| IND$         |     1 |     8 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | I_IND1       |     1 |       |     1   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN            | I_LINK1      |     1 |    18 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   4 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
              "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
   5 - filter(BITAND("O"."FLAGS",128)=0)
   6 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME" LIKE '%B%' AND "O"."LINKNAME" IS NULL AND
              "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_')
   7 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
   8 - access("I"."OBJ#"=:B1)
   9 - access("L"."OWNER#"=USERENV('SCHEMAID'))
       filter("L"."NAME" LIKE '%B%')

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Um den SQL Plan eines Statements im SQL-Area anzeigen zu lassen kann man folgendes Verfahren nutzen:

SQL / PL/SQL

SELECT * FROM
	(SELECT     sql_fulltext,
		sql_id,     child_number,
		disk_reads,     executions,
		first_load_time,
		last_load_time
	FROM
	v$sql
	ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10 ;

--
-- show execution plan
--

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child));