English

FAQ: What is the correct Oracle execution plan for an ArcSDE 8.2 version query?

Question

What is the correct Oracle execution plan for an ArcSDE 8.2 version query?

Answer

With ArcSDE, and the versioned geodatabase, the performance can be measured in how fast the database can return the results of the queries back to the client who is asking for the data. It is critical for performance, that the SQL queries are executed efficiently and have the correct execution plans in the database.

The ArcSDE version query /*+ Normal_filter */, spatial or attribute query is written with specific hints to assist the Oracle Optimizer in selecting the most efficient execution plan. The execution plan is the access path the Oracle instance uses to retrieve rows from a table. Hints provided in the query are merely "suggestions" for the Optimizer and are not guaranteed. But, if the Oracle instance has been configured based upon the ArcSDE for Oracle Configuration guide, it is likely that the Optimizer will choose the optimal execution plan. The reason as to why the Optimizer may not use the optimal plan is do to the influence of various init.ora parameters incorrectly set or lack of accurate table statistics.

The following query and execution plan detail the optimal plan an ArcSDE "normal filter" would expect to see (execution plans can be obtained by capturing an Oracle trace and using the tkprof command's explain option). If your Oracle Optimizer is not choosing the same execution plan, further investigation is required to determine why (places to check are missing table statistics and various init.ora parameters).

Spatial Query /*+ Normal_filter */
---------------------------------
SELECT /*+ USE_NL(V__<#>) INDEX (SHAPE F<#>_UK1) */ SHAPE, V__<#>.eminx, V__<#>.eminy, V__<#>.emaxx, V__<#>.emaxy,
SHAPE.fid, SHAPE.numofpts, SHAPE.entity, SHAPE.points, SHAPE.rowid FROM

(SELECT /*+ ORDERED USE_NL(b) INDEX (b <owner>.A<#>_IX1) INDEX (b <owner>.R<#>_SDE_ROWID_UK) */
b.OBJECTID, b.SHAPE, b.<user_columns>, S_.sp_fid, S_.eminx, S_.eminy, S_.emaxx, S_.emaxy FROM

(SELECT /*+ INDEX(SP_ S<#>_IX1) */ DISTINCT sp_fid, eminx,eminy, emaxx, emaxy FROM
<owner>.S<#> SP_ WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3 AND SP_.gy <= :4 AND
SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >= :7 AND SP_.emaxy >= :8)

S_, <owner>.<business_table> b WHERE S_.SP_FID = b.SHAPE AND b.OBJECTID NOT IN

(SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID FROM <owner>.D<#> WHERE DELETED_AT IN
(SELECT /*+ USE_HASH (l) INDEX_DESC (l lineages_pk) */ l.lineage_id FROM SDE.state_lineages l
WHERE l.lineage_name = :lineage_name AND l.lineage_id <= :state_id1))

UNION ALL

SELECT /*+ ORDERED USE_NL(a) INDEX (a <owner>.A<#>_IX1_A) INDEX (a <owner>.A<#>_SDE_STATE_ID) */
a.OBJECTID, a.SHAPE, a.<user_columns>, S_.sp_fid, S_.eminx, S_.eminy, S_.emaxx, S_.emaxy FROM

(SELECT /*+ INDEX (SP_ S<#>_IX1) */ DISTINCT sp_fid, eminx, eminy, emaxx, emaxy FROM
HOU.S<#> SP_ WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >= :3 AND SP_.gy <= :4 AND
SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx >= :7 AND SP_.emaxy >= :8)

S_, <owner>.A<#> sde.state_lineages sl a WHERE S_.SP_FID = a.SHAPE AND
(a.OBJECTID, a.SDE_STATE_ID) NOT IN

(SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID, SDE_STATE_ID FROM <owner>.D<#> WHERE
DELETED_AT IN (SELECT /*+ USE_HASH (l) INDEX_DESC (l lineages_pk) */ l.lineage_id FROM
SDE.state_lineages l WHERE l.lineage_name = :lineage_name AND l.lineage_id <= :state_id1))
AND a.SDE_STATE_ID = sl.lineage_id AND sl.lineage_name = :lineage_name AND SL.lineage_id <= :state_id1)

V__<#>, <owner>.F<#> SHAPE WHERE V__<#>.SHAPE = SHAPE.FID
Execution Plan
---------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
NESTED LOOPS
VIEW
UNION-ALL
HASH JOIN (ANTI)
NESTED LOOPS
VIEW
SORT (UNIQUE)
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'S<#>_IX1' (UNIQUE)
TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF '<buisness_table>'
INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'A<#>_IX1' (UNIQUE)
VIEW OF 'VW_NSO_2'
HASH JOIN
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'LINEAGES_PK1' (UNIQUE)
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'D<#>_PK' (UNIQUE)
NESTED LOOPS
HASH JOIN (ANTI)
NESTED LOOPS
VIEW
SORT (UNIQUE)
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'S<#>_IX1' (UNIQUE)
TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'A<#>'
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'A<#>_IX1_A' (NON-UNIQUE)
VIEW OF 'VW_NSO_1'
HASH JOIN
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'LINEAGES_PK1' (UNIQUE)
INDEX GOAL: ANALYZED (RANGE SCAN) OF 'D<#>_PK' (UNIQUE)
INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'LINEAGES_PK1' (UNIQUE)
TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'F<#>'
INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'F<#>_UK1' (UNIQUE)

S<#>_IX1 = Spatial table composite index
A<#>_IX1 = Business table SHAPE column index
D<#> = Deletes Table
D<#>_PK = Deletes Table DELETED_AT, SDE_DELETES_ROW_ID, SDE_STATE_ID columns index
A<#> = Adds Table
A<#>_IX1_A = Adds Table SHAPE column index
F<#> = Feature Table
F<#>_UK1 = Feature Table FID column index

If an execution plan contains any FULL TABLE SCANS this is an immediate indicator that the table might be missing a key index or the table statistics are not set.