Statistiken sind Voraussetzungen für den Optimizer, um einen optimalen Ausführungsplan bestimmen zu können.
Der Oracle Optimizer benötigt zur Bestimmung das Ausführungsplans statistische Informationen über die beteiligten Objekte. Folgende aktuelle Statistiken stollten vorhanden sein:
Oracle empfiehlt, die fixed_objects_stats nur einmalig oder nach einer signifikanten Änderung im Workload der Applikation auszuführen.
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:
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.
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:
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));