Hello
I have two SQL scripts that are producing the exact same result (returning approx. 30000 rows) but the first one does it for 30min. and the second one for 2min. The slower query has (by my understatnding) the better explain plan (because it has lower cost).
How can I determine the faster query based on these two explain plans?
Should I also include the two queries? I can not make a test case since these are specific for our database.
I have 9 months of experience with Oracle and I have little or no knowledge with explain plans (but a great desire to build on).
Here is the explain plan for the slower query:
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 209 | 10035 (1)| 00:00:01 | | | | |
| 1 | HASH GROUP BY | | 1 | 209 | 10035 (1)| 00:00:01 | | | | |
|* 2 | FILTER | | | | | | | | | |
|* 3 | HASH JOIN | | 1 | 209 | 10034 (1)| 00:00:01 | | | | |
| 4 | NESTED LOOPS | | 1 | 191 | 9791 (1)| 00:00:01 | | | | |
| 5 | NESTED LOOPS | | 1 | 191 | 9791 (1)| 00:00:01 | | | | |
| 6 | NESTED LOOPS | | 1 | 147 | 9790 (1)| 00:00:01 | | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 100 | 9789 (1)| 00:00:01 | | | | |
| 8 | NESTED LOOPS | | 1 | 91 | 9788 (1)| 00:00:01 | | | | |
| 9 | NESTED LOOPS | | 299 | 17342 | 7754 (1)| 00:00:01 | | | | |
| 10 | NESTED LOOPS | | 37552 | 1246K| 239 (1)| 00:00:01 | | | | |
|* 11 | TABLE ACCESS FULL | ZAEHLWERKSTYPEN | 1 | 14 | 2 (0)| 00:00:01 | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | ZAEHLWERKE | 37858 | 739K| 237 (1)| 00:00:01 | | | | |
|* 13 | INDEX RANGE SCAN | IDX_ZAEH_ZAEWTYP_ID | 38326 | | 14 (0)| 00:00:01 | | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | INDIVID_ARTIKEL | 1 | 24 | 1 (0)| 00:00:01 | | | | |
|* 15 | INDEX UNIQUE SCAN | INDART_PK | 1 | | 1 (0)| 00:00:01 | | | | |
| 16 | PARTITION HASH ALL | | 1 | 33 | 7 (0)| 00:00:01 | 1 | 64 | | |
|* 17 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ABRECHNUNGSZYKLEN | 1 | 33 | 7 (0)| 00:00:01 | 1 | 64 | | |
|* 18 | INDEX RANGE SCAN | ABRECHNUNG_INDART_FK_I | 3 | | 6 (0)| 00:00:01 | 1 | 64 | | |
| 19 | TABLE ACCESS BY INDEX ROWID | ABLESEMETHODEN | 1 | 9 | 1 (0)| 00:00:01 | | | | |
|* 20 | INDEX UNIQUE SCAN | ABLM_PK | 1 | | 1 (0)| 00:00:01 | | | | |
| 21 | PARTITION HASH ITERATOR | | 1 | 47 | 1 (0)| 00:00:01 | KEY | KEY | | |
|* 22 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | ZAEHLERSTAENDE | 1 | 47 | 1 (0)| 00:00:01 | KEY | KEY | | |
|* 23 | INDEX RANGE SCAN | ZST_AZ_DATUM_IDX | 1 | | 1 (0)| 00:00:01 | KEY | KEY | | |
|* 24 | INDEX RANGE SCAN | ZAEHLPKT_HAUSANSCHL_FK_I | 1 | | 1 (0)| 00:00:01 | | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | ZAEHLPUNKTE | 1 | 44 | 1 (0)| 00:00:01 | | | | |
| 26 | REMOTE | HM_ZP | 64737 | 1137K| 243 (1)| 00:00:01 | | | DWH_D~ | R->S |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
And here is the explain plan for the faster query (I used a "WITH" clause for joining some of the tables):
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 201 | 12229 (1)| 00:00:01 | | | | |
| 1 | HASH GROUP BY | | 1 | 201 | 12229 (1)| 00:00:01 | | | | |
|* 2 | FILTER | | | | | | | | | |
|* 3 | HASH JOIN | | 1 | 201 | 12228 (1)| 00:00:01 | | | | |
| 4 | NESTED LOOPS | | 1 | 183 | 11985 (1)| 00:00:01 | | | | |
| 5 | NESTED LOOPS | | 1 | 183 | 11985 (1)| 00:00:01 | | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 139 | 11984 (1)| 00:00:01 | | | | |
| 7 | NESTED LOOPS | | 1 | 130 | 11983 (1)| 00:00:01 | | | | |
| 8 | NESTED LOOPS | | 1 | 106 | 11982 (1)| 00:00:01 | | | | |
|* 9 | HASH JOIN | | 1012 | 73876 | 11779 (1)| 00:00:01 | | | | |
| 10 | NESTED LOOPS | | 37552 | 953K| 239 (1)| 00:00:01 | | | | |
| 11 | NESTED LOOPS | | 38326 | 953K| 239 (1)| 00:00:01 | | | | |
|* 12 | TABLE ACCESS FULL | ZAEHLWERKSTYPEN | 1 | 14 | 2 (0)| 00:00:01 | | | | |
|* 13 | INDEX RANGE SCAN | IDX_ZAEH_ZAEWTYP_ID | 38326 | | 14 (0)| 00:00:01 | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | ZAEHLWERKE | 37858 | 443K| 237 (1)| 00:00:01 | | | | |
| 15 | PARTITION HASH ALL | | 126K| 5809K| 11539 (1)| 00:00:01 | 1 | 64 | | |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ZAEHLERSTAENDE | 126K| 5809K| 11539 (1)| 00:00:01 | 1 | 64 | | |
|* 17 | INDEX RANGE SCAN | ZAEHLERSTA_DATUM_I | 126K| | 48 (0)| 00:00:01 | 1 | 64 | | |
|* 18 | TABLE ACCESS BY GLOBAL INDEX ROWID | ABRECHNUNGSZYKLEN | 1 | 33 | 1 (0)| 00:00:01 | ROWID | ROWID | | |
|* 19 | INDEX UNIQUE SCAN | ABRECHNUNG_PK | 1 | | 1 (0)| 00:00:01 | | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | INDIVID_ARTIKEL | 1 | 24 | 1 (0)| 00:00:01 | | | | |
|* 21 | INDEX UNIQUE SCAN | INDART_PK | 1 | | 1 (0)| 00:00:01 | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | ABLESEMETHODEN | 1 | 9 | 1 (0)| 00:00:01 | | | | |
|* 23 | INDEX UNIQUE SCAN | ABLM_PK | 1 | | 1 (0)| 00:00:01 | | | | |
|* 24 | INDEX RANGE SCAN | ZAEHLPKT_HAUSANSCHL_FK_I | 1 | | 1 (0)| 00:00:01 | | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | ZAEHLPUNKTE | 1 | 44 | 1 (0)| 00:00:01 | | | | |
| 26 | REMOTE | HM_ZP | 64737 | 1137K| 243 (1)| 00:00:01 | | | DWH_D~ | R->S |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Thanks
Encho Donchev
How can I determine the faster query based on these two explain plans?The short answer is: you can't!
The only way to know for sure how long your query will take is to run it.
The database assigns costs to choose between plans for a given statement. It chooses the plan with lowest overall cost. So it's a way of ranking plans for that statement.
In an ideal world a higher cost => slower query. But there are no guarantees. A lower cost plan can still be quicker for various reasons. Including:
- Bad statistics (garbage in => garbage out)
- Optimizer limitations
- Whether the rows are already in memory or need to be fetched from disk
When it comes to figuring out whether a plan is "good", you need to look at its execution plan.
See more on this at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9534189900346120939