Search code examples
oracleexplain

Explain Plan in Oracle - how do I detect the most efficient query?

Intro

I am writing a query to compare 2 different oracle database tables containing hashes.

I want to find which hashes from location 1, have not been migrated over to location 2.

I have three different queries, and have written explain plan for statements for them.

However, the results don't tell me that much.

Question

How do I find which is the most efficient and fastest?

Current Guess

My suspicion however is that the first query is the fastest since it makes a one-off use of the remote link. But this is just a guess that is not supported by actual results.

Code

--------------------------

EXPLAIN PLAN
SET statement_id = 'ex_plan1' FOR
select* from document doc left outer join migrated_document@V2_PROD migrated on doc.hash = migrated.document_hash AND migrated.document_hash is null ;

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan1','BASIC'));

---------------------------------------------------
| Id  | Operation             | Name              |
---------------------------------------------------
|   0 | SELECT STATEMENT      |                   |
|   1 |  HASH JOIN RIGHT OUTER|                   |
|   2 |   REMOTE              | MIGRATED_DOCUMENT |
|   3 |   TABLE ACCESS FULL   | DOCUMENT          |
---------------------------------------------------


--------------------------

EXPLAIN PLAN
SET statement_id = 'ex_plan2' FOR
select* from document doc where not exists( select 1 from migrated_document@V2_PROD migrated where migrated.document_hash = doc.HASH ) ;

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));

------------------------------------------------
| Id  | Operation          | Name              |
------------------------------------------------
|   0 | SELECT STATEMENT   |                   |
|   1 |  FILTER            |                   |
|   2 |   TABLE ACCESS FULL| DOCUMENT          |
|   3 |   REMOTE           | MIGRATED_DOCUMENT |
------------------------------------------------


--------------------------

EXPLAIN PLAN
SET statement_id = 'ex_plan3' FOR
select* from document doc where doc.hash not in ( select migrated.document_hash from migrated_document@V2_PROD migrated) ;

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan3','BASIC'));

------------------------------------------------
| Id  | Operation          | Name              |
------------------------------------------------
|   0 | SELECT STATEMENT   |                   |
|   1 |  NESTED LOOPS ANTI |                   |
|   2 |   TABLE ACCESS FULL| DOCUMENT          |
|   3 |   REMOTE           | MIGRATED_DOCUMENT |
------------------------------------------------


--------------------------

Update

I updated the explain plan statements to get more results. Due to the remote operation... could something cost less but be more slow?

If I am reading the data correctly it seems that option 2 is best. But I still think option 1 is quicker.

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Bytes | Cost  | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |   105K|    51M|   194 |        |      |
|   1 |  HASH JOIN RIGHT OUTER|                   |   105K|    51M|   194 |        |      |
|   2 |   REMOTE              | MIGRATED_DOCUMENT |     1 |   275 |     2 | V2_MN~ | R->S |
|   3 |   TABLE ACCESS FULL   | DOCUMENT          |   105K|    23M|   192 |        |      |
-------------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost  | Inst   |IN-OUT|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |   105K|    23M|   104K|        |      |
|   1 |  FILTER            |                   |       |       |       |        |      |
|   2 |   TABLE ACCESS FULL| DOCUMENT          |   105K|    23M|   192 |        |      |
|   3 |   REMOTE           | MIGRATED_DOCUMENT |     1 |    50 |     1 | V2_MN~ | R->S |
----------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost  | Inst   |IN-OUT|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |   105K|    29M|   526 |        |      |
|   1 |  NESTED LOOPS ANTI |                   |   105K|    29M|   526 |        |      |
|   2 |   TABLE ACCESS FULL| DOCUMENT          |   105K|    23M|   192 |        |      |
|   3 |   REMOTE           | MIGRATED_DOCUMENT |     1 |    50 |     0 | V2_MN~ | R->S |
----------------------------------------------------------------------------------------
Solution

  • In a perfect world, you would choose the plan with the lowest cost. However I do not think your first query does what you want. It looks to me like no rows will join; you should be using a filter predicate rather than a join.

    Instead of 

    select * from document doc 
left outer join migrated_document@V2_PROD migrated 
  on doc.hash = migrated.document_hash 
AND migrated.document_hash is null

    it should be

    select * from document doc 
left outer join migrated_document@V2_PROD migrated 
  on doc.hash = migrated.document_hash 
WHERE migrated.document_hash is null