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 das Ausführungsplans statistische Informationen über die beteiligten Objekte. Folgende aktuelle Statistiken stollten vorhanden sein:

  • Oracle Data Dictionary Statistics
  • Fixed Tabel 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-Statisten werden wir folgt erstellt:

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
Reanalyzes 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 Historgramme analysiert und keine neuen Historgramme erstellt.

Achtung: Histogramme können auch einen negativen Effekt habe. 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 Historgramme zu löschen.

Parallele Erstellung von Statistiken

Unter Oracle 11 gibt es die Mögliochkeit, 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ürzern Laufzeiten möglich.

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 ob_queue_processes sollte auf mindestens 4 stehen.Zusätzlich sind die folgenden Datenbankprivilegioen 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> 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> 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:

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));