Statistics — the prerequisite for good performance

Without current statistics the optimizer cannot determine a good execution plan.

Overview

To determine an execution plan, the Oracle optimizer needs statistical information about the objects involved. The following statistics should be present and current:

Oracle data dictionary statistics
Fixed table statistics
Schema statistics
System statistics

Oracle recommends gathering fixed_objects_stats once only, or after a significant change in the application's workload.

Gathering statistics

Schema statistics are gathered as follows:

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;

The following options are available:

GATHER: Re-analyses the entire schema.
GATHER EMPTY: Only objects without statistics are analysed.
GATHER STALE: Only objects with more than 10 percent change (insert, update, delete) are analysed.
GATHER AUTO: Only objects with no statistics or stale statistics are analysed.
method_opt repeat: Only indexes with existing histograms are analysed, and no new histograms are created.

Caution: histograms can also have a negative effect. This can occur when bind variables are used, because Oracle then has to resort to bind variable peeking. If the distribution of values is heavily skewed, dropping the histograms may be the better option.

Gathering statistics in parallel

From Oracle 11 onwards the global statistics preference CONCURRENT can be set. Schema and database statistics are then gathered in jobs that run in parallel, which shortens the overall runtime considerably.

SQL / PL/SQL

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

The SYSAUX tablespace has to be online, because the job scheduler stores its internal tables there, and job_queue_processes should be set to at least 4. The following database privileges are required in addition:

CREATE JOB
MANAGE SCHEDULER
MANAGE ANY QUEUE

Explain plan

Explain plan shows you the execution plan Oracle would currently choose. Displaying plans requires a current plan_table, which can be created with the script $ORACLE_HOME/rdbms/admin/utlxplan.sql. The execution plan for a given SQL statement is produced like this:

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.

Be careful, though: the actual plan at runtime can differ from this one. Several options are available. First of all, the execution plan and the key performance figures can be output with set autotrace on:

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>

To display the plan of a statement that is in the SQL area, the following approach can be used:

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