FAQ: What Does (Sel: ) Mean in Oracle's Explain Plan?

Answer

When reviewing an Oracle tkprof file or autotrace output from SQL*Plus, one might notice the string (Sel: <value>) after the operation DOMAIN INDEX.

The Sel: <value> represents the selectivity the optimizer was provided by the st_domain_stats type. This number is derived from the input envelope, the given operator and an execution plan chosen when the statement was parsed.

The following example demonstrates the output one would see using SQL*Plus and autotrace:

Code:
SQL> set autotrace traceonly explain

SQL> SELECT owner, address, pin
     FROM parcels
     WHERE st_envintersects(shape, 10,10,20,20) = 1;

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  |   Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   289 |   10258 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | PARCELS |   290 |   10258 |     3   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX (Sel: .162684)| A20_IX1 |       |         |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("SDE"."ST_ENVINTERSECTS"("SHAPE",10,10,20,20)=1)

The value .162684 represents the selectivity calculated by the st_domain_stats type based upon the input envelope from the st_envintersects operator. The optimizer uses selectivity to represent the fraction of rows which are returned by the statement. The number of rows returned by the statement, 290 in above case, is the cardinality for the statement or predicate. Cardinality is simply calculated by multiplying the number of rows in the table by the selectivity -

178,321 parcels * (.162684/100) = 290 rows (rounded).

The selectivity value is also input used by the optimizer to calculate the cost of using an index compared to performing a full table scan. If a high selectivity is derived, then its very possible the cost of using the index is greater then the cost of performing the full table scan.

Potentially one might see other <values> for selectivity -

(Sel: Default - No Stats)
(Sel: Default - Undefined)
(Sel: 0)

If (Sel: Default - No stats) is reported it indicates there are no statistics for the spatial index. Once the statistics are captured using ArcCatalog, dbms_stats.gather_table_stats or dbms_stats.gather_index_stats the value for selectivity should then be reported.

If (Sel: Default - Undefined) is reported it may indicate that there was an error generating statistics, or that the operator has been disassociated from the statistical type, such as the st_envintersects operator.

In the above cases, when no statistics are present or are undefined, the optimizer uses default values for selectivity (1 percent) and a default cost (3). When the optimizer's default values are used, the indexes low cost becomes the likely access path. But, potentially the high selectivity (1 percent) may result in a very high cardinality and in turn impact the optimizer in choosing an optimal join plan.

When (Sel: 0) is reported then the selectivity for the layer is 0. This may happen when statistics are gathered prior to any features existing in the layer. Always ensure your statistics accurately represent the true representation of the data.

The selectivity value reported is rounded to the 7th decimal.

Article ID:000009227

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

What does (Sel: ) mean in Oracle's explain plan?

Answer

Discover more on this topic