Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Károly.

Asked: January 29, 2018 - 9:58 am UTC

Last updated: September 29, 2022 - 9:47 am UTC

Version: Oracle Database 11g, 11.2.0.2.0

Viewed 1000+ times

You Asked

We have a table called PROTOKOLL, with the following definition:
  CREATE TABLE "PROTOKOLL" 
   ( "UUID" CHAR(36 BYTE) NOT NULL ENABLE, 
 "VERSIONSNUMMER" VARCHAR2(20 BYTE), 
 "TYP" CHAR(1 BYTE) NOT NULL ENABLE, 
 "BENUTZER_ID" VARCHAR2(250 BYTE) NOT NULL ENABLE, 
 "SERVICE" VARCHAR2(250 BYTE) NOT NULL ENABLE, 
 "ERFOLG" NUMBER, 
 "STANDESAMT_NR" VARCHAR2(250 BYTE), 
 "REGISTER_ART" VARCHAR2(250 BYTE), 
 "EINTRAG_JAHR" VARCHAR2(5 BYTE), 
 "EINTRAG_NR" VARCHAR2(250 BYTE), 
 "ZEITPUNKT" TIMESTAMP (6) NOT NULL ENABLE, 
 "DATEN" "SYS"."XMLTYPE" , 
 "ANDERES_PROTOKOLL_UUID" CHAR(36 BYTE), 
 "MANDANT" VARCHAR2(250 BYTE), 
 "RESERVIERUNGSNACHWEIS" VARCHAR2(60 CHAR), 
 "VERFUEGUNGSSTATUS" NCHAR(1), 
 "BEZUG" VARCHAR2(1500 BYTE) DEFAULT NULL, 
  CONSTRAINT "PROTOKOLL_PK" PRIMARY KEY ("UUID")
   )  ;

"PROTOKOLL_BENUTZER_ID_IX1" ON "PROTOKOLL" ("BENUTZER_ID")  ;
"PROTOKOLL_EINTRAGJAHR_IX1" ON "PROTOKOLL" ("EINTRAG_JAHR")  ;
"PROTOKOLL_EINTRAGNR_IX1" ON "PROTOKOLL" ("EINTRAG_NR")  ;
"PROTOKOLL_MANDANT_ID_IX1" ON "PROTOKOLL" ("MANDANT")  ;
"PROTOKOLL_REGISTERART_IX1" ON "PROTOKOLL" ("REGISTER_ART")  ;
"PROTOKOLL_SERVICE_IX1" ON "PROTOKOLL" ("SERVICE")  ;
"PROTOKOLL_STANDESAMTNR_IX1" ON "PROTOKOLL" ("STANDESAMT_NR")  ;
"PROTOKOLL_TYP_IX1" ON "PROTOKOLL" ("TYP")  ;
"PROTOKOLL_TYP_IX2" ON "PROTOKOLL" ("ANDERES_PROTOKOLL_UUID", "TYP")  ;
"PROTOKOLL_ZEITPUNKT_IX1" ON "PROTOKOLL" ("ZEITPUNKT")  ;


The table contains ca. 10 million records.

We have the following query:
SELECT *
FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  
FROM (SELECT 
t0.*, t1.*
FROM PROTOKOLL t0, PROTOKOLL t1 
WHERE (((t0.BENUTZER_ID = 'A07BU0006') AND (t0.TYP = 'E')) AND ((t1.UUID = t0.ANDERES_PROTOKOLL_UUID) AND (t1.TYP = 'A'))) 
ORDER BY t0.ZEITPUNKT DESC) a WHERE ROWNUM <= 5000) WHERE rnum > 0;


So practically we join the table with itself through ANDERES_PROTOKOLL_UUID field, we apply simple filtering. The results are sorted with creation time and the number of the result record set is limited to 5000.

The elapsed time of the query is about 10 Minutes! --- which is not acceptable ☹
I already have the execution plan and statistic information in place and trying to figure out how to speed up the query:
------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | 
|   0 | SELECT STATEMENT                  |                         |     1 |    13 |  4903K  (1)| 16:20:44 | 
|   1 |  SORT AGGREGATE                   |                         |     1 |    13 |            |          |
|*  2 |   VIEW                            |                         |   446 |  5798 |  4903K  (1)| 16:20:44 |
|*  3 |    COUNT STOPKEY                  |                         |       |       |            |          | 
|   4 |     VIEW                          |                         |   446 |       |  4903K  (1)| 16:20:44 |   
|   5 |      NESTED LOOPS                 |                         |       |       |            |          |
|   6 |       NESTED LOOPS                |                         |   446 |   186K|  4903K  (1)| 16:20:44 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| PROTOKOLL               |   446 | 95444 |  4902K  (1)| 16:20:33 |
|   8 |         INDEX FULL SCAN DESCENDING| PROTOKOLL_ZEITPUNKT_IX1 |  6541K|       | 50286   (1)| 00:10:04 |
|*  9 |        INDEX UNIQUE SCAN          | PROTOKOLL_PK            |     1 |       |     1   (0)| 00:00:01 |
|* 10 |       TABLE ACCESS BY INDEX ROWID | PROTOKOLL               |     1 |   214 |     2   (0)| 00:00:01 |

Predicate Information (identified by operation id): 
   2 - filter("RNUM">0)   
   3 - filter(ROWNUM<=4999)
   7 - filter("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."BENUTZER_ID"='A07BU0006' AND "P"."TYP"='E') 
   9 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID") 
  10 - filter("P"."TYP"='A')
 
Statistics
         21  recursive calls  
          0  db block gets 
    2170736  consistent gets 
    1383094  physical reads
          0  redo size 
        527  bytes sent via SQL*Net to client 
        524  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed 


My first recognition, that the optimizer puts “"P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL” condition additionally to the where clause, but I do not know why. Is it a problem?

Or where are the bottleneck of the query?
Does it help if I create a second table with the UUID and ANDERES_PROTOKOLL_UUID fields and join the PROTOKOLL table together through that table?

and Chris said...

To help with SQL tuning, we really need to see what the query really did, i.e. it's execution plan.

You can get this running the query with the gather_plan_statistics, then calling dbms_xplan:

set serveroutput off
SELECT /*+ gather_plan_statistics */*
FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  
FROM (SELECT 
t0.*, t1.*
FROM PROTOKOLL t0, PROTOKOLL t1 
WHERE (((t0.BENUTZER_ID = 'A07BU0006') AND (t0.TYP = 'E')) AND ((t1.UUID = t0.ANDERES_PROTOKOLL_UUID) AND (t1.TYP = 'A'))) 
ORDER BY t0.ZEITPUNKT DESC) a WHERE ROWNUM <= 5000) WHERE rnum > 0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


Make sure this contains both E(stimated) rows and A(ctual) rows columns. Once you have the plan, paste it here and we'll see how we can help you.

You can read more about how to get these at:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

the optimizer puts “"P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL” condition additionally to the where clause, but I do not know why. Is it a problem?

No. The optimizer often generates extra predicates to help it. In this case, your query has:

t1.UUID = t0.ANDERES_PROTOKOLL_UUID


Which is only true when ANDERES_PROTOKOLL_UUID is not null (null = null returns unknown). So it can safely add this.

Finally, you've got the first rows hint. The correct form of this is:

first_rows (N)


Where N is the number of rows you expect your query to return. So, in this case, you'll want:

first_rows (5000)


Of course, 5,000 is still waaaaay more rows than a person will inspect! Are you sure you want this many? Can you select fewer?

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Intermediate response

László Károly, January 30, 2018 - 8:28 am UTC

I will gather the execution plan information - from the customer, it might take a little time.

first_rows vs. first_rows (N): yes, we know this problem, we inherited the code :-)

5000: this is a configuratio parameter which can be set by the user, it can be lower but it can even be higher as well
Does it have serious impact on the execution plan? We can do tests with different values....


Chris Saxon
January 30, 2018 - 11:30 am UTC

Does it have serious impact on the execution plan?

It's determining the upper limit for how many rows you fetch, so lower values could lead to different (better) plans.

Execution plan information arrived

László Károly, February 09, 2018 - 10:44 am UTC

"With fetch limitation (5000), with /*+ FIRST_ROWS */ hint"
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:13:11.23 | 2267K| 1557K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:13:11.23 | 2267K| 1557K|
|* 2 | VIEW | | 1 | 446 | 5000 |00:07:53.51 | 2267K| 1557K|
|* 3 | COUNT STOPKEY | | 1 | | 5000 |00:07:53.49 | 2267K| 1557K|
| 4 | VIEW | | 1 | 446 | 5000 |00:07:53.49 | 2267K| 1557K|
| 5 | NESTED LOOPS | | 1 | | 5000 |00:07:53.49 | 2267K| 1557K|
| 6 | NESTED LOOPS | | 1 | 446 | 5000 |00:07:51.68 | 2262K| 1554K|
|* 7 | TABLE ACCESS BY INDEX ROWID| PROTOKOLL | 1 | 446 | 5000 |00:07:46.68 | 2252K| 1549K|
| 8 | INDEX FULL SCAN DESCENDING| PROTOKOLL_ZEITPUNKT_IX1 | 1 | 6541K| 3033K|00:00:21.60 | 23703 | 23145 |
|* 9 | INDEX UNIQUE SCAN | PROTOKOLL_PK | 5000 | 1 | 5000 |00:00:05.05 | 10003 | 4831 |
|* 10 | TABLE ACCESS BY INDEX ROWID | PROTOKOLL | 5000 | 1 | 5000 |00:00:01.68 | 5000 | 2955 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("RNUM">0)
3 - filter(ROWNUM<=5000)
7 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."BENUTZER_ID"='A07BU0006' AND "P"."TYP"='E'))
9 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
10 - filter("P"."TYP"='A')

Immediately, I gathered the exc. plans for modified queries as well:
"With fetch limitation (5000), without /*+ FIRST_ROWS */ hint"

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:07.69 | 48788 | 26669 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:07.69 | 48788 | 26669 | | | |
|* 2 | VIEW | | 1 | 446 | 5000 |00:00:07.69 | 48788 | 26669 | | | |
|* 3 | COUNT STOPKEY | | 1 | | 5000 |00:00:07.69 | 48788 | 26669 | | | |
| 4 | VIEW | | 1 | 446 | 5000 |00:00:07.68 | 48788 | 26669 | | | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 446 | 5000 |00:00:07.68 | 48788 | 26669 | 337K| 337K| 299K (0)|
| 6 | NESTED LOOPS | | 1 | | 10543 |00:00:08.10 | 48788 | 26669 | | | |
| 7 | NESTED LOOPS | | 1 | 446 | 10543 |00:00:08.00 | 38245 | 26482 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| PROTOKOLL | 1 | 446 | 10543 |00:00:01.41 | 17157 | 16725 | | | |
|* 9 | INDEX RANGE SCAN | PROTOKOLL_BENUTZER_ID_IX1 | 1 | 1805 | 21086 |00:00:00.02 | 460 | 458 | | | |
|* 10 | INDEX UNIQUE SCAN | PROTOKOLL_PK | 10543 | 1 | 10543 |00:00:05.85 | 21088 | 9757 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | PROTOKOLL | 10543 | 1 | 10543 |00:00:00.11 | 10543 | 187 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - filter("RNUM">0)
3 - filter(ROWNUM<=5000)
5 - filter(ROWNUM<=5000)
8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
9 - access("P"."BENUTZER_ID"='A07BU0006')
10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")

PLAN_TABLE_OUTPUT
------------------------------------
11 - filter("P"."TYP"='A')

I also made test with different fetch buffer size (1000, 10000) and without FIRST_ROW hint, the execution plan remains the same - the second one :-).

Highly appreciate the exc. plan's explanation. And the key question is whether the query without hint is optimal or we can still increase the performance?

Chris Saxon
February 09, 2018 - 11:36 am UTC

Well, you can almost always make a query faster. But at some point the effort of doing so isn't worth the benefit!

So, first question: do you need this query to be faster?

If so, some observations:

- Step 9 returns 21,086 from the index on BENUTZER_ID. But step 8 filters ~ half of these away based on:

"P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'

So an index on:

BENUTZER_ID, TYP, ANDERES_PROTOKOLL_UUID

may be useful.

- You also have:

ORDER BY t0.ZEITPUNKT DESC

So it's possible that extending this further to:

BENUTZER_ID, TYP, ANDERES_PROTOKOLL_UUID, ZEITPUNKT DESC

Could help by avoiding a sort.

- You're selecting all the columns from both tables. Do you really need them all?

Selecting just the columns you need may help. Particularly if you can avoid a table access by making an index accessing only these columns.

A reader, February 13, 2018 - 2:22 pm UTC

Many thanks for your suggestions so far.

Indeed, we have to focus on the second exec. plan. The only difference between the actual/original query and the second one is, that the /* + FIRST_ROW */ hint has been removed. According to the measurement, query completely without /* + FIRST_ROW */ hint provides the same execution plan like the query with /* + FIRST_ROW(5000) */. Is it a valid observation?
I'll ask the developers to modify the code.

My actual challenge is, that:
- we have a test environment with a certain amount of data
- customer has also their test environment with a certain amount of data
- customer's DBAs manage the production environment with a significant larger amount of data.
I do not have access to the production environment.

We can suppose, that the HW, SW environments and indexes are the same.

What I've recognized already, that the exec. plans on our test systems differ from the exec. plans of the PRODUCTION system.
This makes my life a bit complicated....

Is the any best practice how to manage this situation?
Chris Saxon
February 14, 2018 - 5:22 pm UTC

If you observed it, then it's valid. Provided you did look of course ;)

A QA environment that is an exact clone of live is the ideal situation. But this isn't always possible or practical.

To help you get the same plans as production, you could export the stats from there, then load them into test. And lock them, so the default job doesn't overwrite them!

While it's likely runtimes will differ between the environments, you should be able to get the same plans. It becomes trickier when it comes to creating and testing new indexes because the data distributions will be different.

If you're licensed for it, SQL Plan Management can help here. By locking key plans you hope to improve with a new index, you can evaluate whether it does before allowing the optimizer to use it.

Or you could have a policy of creating new indexes as invisible.

In both cases, you still have the overhead of maintaining the index. So if your database is write heavy, you could still encounter issues...

Laszlo Karoly, February 19, 2018 - 7:02 pm UTC

I've made some progress on collecting execution plans. Before summarizing my observations, let me explain the frame conditions I'm facing to and challenges I have to resolve.
We have two customers. They have more or less the same SW architecture (op., applic. server, db server). The exactly the same application is installed on both sites. DB sizes differs (of course :-)). We can assume exactly the same DB indices.
We have the table with ind. as described earlier. We have basically three groups of filters the users can apply to get the reports: BENUTZER, EINTRAG, SERVICES. So far, I presented the result from one customer and only for the BENUTZER filter group. Now I have further information....

1. Customer-1, BENUTZER filter:
You can find in the prev. post, in the "With fetch limitation (5000), without /*+ FIRST_ROWS */ hint" section

2. Customer-2, BENUTZER filter:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:47.20 | 177K| 87854 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:47.20 | 177K| 87854 | | | |
|* 2 | VIEW | | 1 | 5000 | 5000 |00:01:47.22 | 177K| 87854 | | | |
|* 3 | COUNT STOPKEY | | 1 | | 5000 |00:01:47.21 | 177K| 87854 | | | |
| 4 | VIEW | | 1 | 44757 | 5000 |00:01:47.21 | 177K| 87854 | | | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 44757 | 5000 |00:01:47.20 | 177K| 87854 | 372K| 372K| 330K (0)|
| 6 | NESTED LOOPS | | 1 | 44757 | 39203 |00:03:17.95 | 177K| 87854 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| PROTOKOLL | 1 | 44764 | 39203 |00:01:23.64 | 60279 | 52018 | | | |
|* 8 | INDEX RANGE SCAN | PROTOKOLL_BENUTZER_ID_IX1 | 1 | 179K| 78406 |00:00:05.20 | 1890 | 1871 | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| PROTOKOLL | 39203 | 1 | 39203 |00:02:03.47 | 117K| 35836 | | | |
|* 10 | INDEX UNIQUE SCAN | PROTOKOLL_PK | 39203 | 1 | 39203 |00:01:53.00 | 78432 | 33898 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - filter("RNUM">0)
3 - filter(ROWNUM<=5000)
5 - filter(ROWNUM<=5000)
7 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
8 - access("P"."BENUTZER_ID"='Hoefer')
9 - filter("P"."TYP"='A')
10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")


I think, the two ex.plans are very similar, same indices are used. I see only one NESTED LOOP in Cust.-1.
Q1: What might be the reason?

Q2: How is the "Order by ZEITPUNKT Desc" business req. implemented? Honestly, I can not derive this from the exec. plan. I do not see any connection to ZEITPUNKT field.


2. Customer-1, REGISTER filter:
Basically the same ex. plan will be applied, the differences are on the used indices.
Predicate Information (identified by operation id):
2 - filter("RNUM">0)
3 - filter(ROWNUM<=5000)

PLAN_TABLE_OUTPUT
5 - filter(ROWNUM<=5000)
8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."STANDESAMT_NR"='11007007307' AND "P"."REGISTER_ART"='E' AND "P"."TYP"='E'))
12 - access("P"."EINTRAG_NR"='1317')
14 - access("P"."EINTRAG_JAHR"='1961')
15 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
16 - filter("P"."TYP"='A')

Q3: In this case, "BITMAP CONVERSION FROM ROWIDS" is used. Is it a problem?

What I also observed, that query "With fetch limitation (5000), with /*+ FIRST_ROWS(5000) */ hint" format produced always lower A-Time parameter than query "With fetch limitation (5000), without /*+ FIRST_ROWS */ hint".
However, they have exactly the same exec. plan. The only difference I can observe was: Predicate Information and PLAN_TABLE_OUTPUT section. First type of query has two predicate conditions the second one has only one, the second condition appears in PLAN_TABLE_OUTPUT section.

Q4: Does it explain the difference on the A-Time parameter?
Chris Saxon
February 20, 2018 - 1:45 pm UTC

A1: Look at the E-rows column for PROTOKOLL_BENUTZER_ID_IX1. In the first plan this is 1,805. For the second it's 179,000. That's a huge difference!

This will be a key reason you have a difference. But to really understand you need to dig into the 10053 trace.

A2: It depends on what the plan does. In one plan you have:

INDEX FULL SCAN DESCENDING| PROTOKOLL_ZEITPUNKT_IX1


So the database is reading the rows from the index ordered by zeitpunkt desc. So there's no need to do any extra sorting.

But in other plans you have:

SORT ORDER BY STOPKEY


Which is likely where this happens.

A3: You're probably getting "BITMAP CONVERSION FROM ROWIDS" because the optimizer combines the results from two indexes on the same table. Then uses the output this combination to access the table.

Is this a problem? Well that depends - is this step in your plan the slowest part?

A4: Maybe? Share both full plans for us to help further.

And please: put plans in < code > tags with the original formatting! This makes them much easier to read.

Károly László, February 20, 2018 - 8:41 pm UTC

Hi Chris,

Note to A1: I think, it depends on the value and the selectivity of the filter as well, because "access("P"."BENUTZER_ID"='Hoefer')" results 179K E-rows and 78 406 A-rows. Today I tested the query with "access("P"."BENUTZER_ID"='Kroeger')" filter (of course on the same production database) and I got EXACTLY the same exec plan, with 3555 E-Rows and 18972 A-rows. And there is also significant difference on A-Time: 00:01:47.20 vs. 00:00:00.14.

Q: Does it really depend on the value of the applied filter?

The other select with "access("P"."BENUTZER_ID"='A07BU0006')" and with the additional NESTED LOOP in the exec plan was measured at the other customer. Here the A-Time 00:00:07.69 value is pretty good.

As a conclusion: I'm happy, that having the same set of indices on two completely separated database provides basically the same execution plan. The only concern is, that the exec. plans on our test environment also depends on the value of the applied filter.
e.g.
Plan 1:
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT                   |                           |      1 |        |      1 |00:00:00.01 |      31 |       |       |          |
|   1 |  SORT AGGREGATE                    |                           |      1 |      1 |      1 |00:00:00.01 |      31 |       |       |          |
|*  2 |   VIEW                             |                           |      1 |      1 |      5 |00:00:00.01 |      31 |       |       |          |
|*  3 |    COUNT STOPKEY                   |                           |      1 |        |      5 |00:00:00.01 |      31 |       |       |          |
|   4 |     VIEW                           |                           |      1 |      1 |      5 |00:00:00.01 |      31 |       |       |          |
|*  5 |      SORT ORDER BY STOPKEY         |                           |      1 |      1 |      5 |00:00:00.01 |      31 |  2048 |  2048 | 2048  (0)|
|   6 |       NESTED LOOPS                 |                           |      1 |        |      5 |00:00:00.01 |      31 |       |       |          |
|   7 |        NESTED LOOPS                |                           |      1 |      1 |      5 |00:00:00.01 |      26 |       |       |          |
|*  8 |         TABLE ACCESS BY INDEX ROWID| PROTOKOLL                 |      1 |      1 |      5 |00:00:00.01 |      14 |       |       |          |
|*  9 |          INDEX RANGE SCAN          | PROTOKOLL_BENUTZER_ID_IX1 |      1 |      3 |     10 |00:00:00.01 |       4 |       |       |          |
|* 10 |         INDEX UNIQUE SCAN          | PROTOKOLL_PK              |      5 |      1 |      5 |00:00:00.01 |      12 |       |       |          |
|* 11 |        TABLE ACCESS BY INDEX ROWID | PROTOKOLL                 |      5 |      1 |      5 |00:00:00.01 |       5 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("RNUM">0)
   3 - filter(ROWNUM<=5000)
   5 - filter(ROWNUM<=5000)
   8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))

PLAN_TABLE_OUTPUT                                                                                                                                                                   
---------------------------------------------------------
   9 - access("P"."BENUTZER_ID"='<b>VorhandenerBenutzer</b>')
  10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
  11 - filter("P"."TYP"='A')

Plan 2:
 
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT                   |                   |      1 |        |      1 |00:00:00.32 |   88653 |     14 |       |       |          |
|   1 |  SORT AGGREGATE                    |                   |      1 |      1 |      1 |00:00:00.32 |   88653 |     14 |       |       |          |
|*  2 |   VIEW                             |                   |      1 |   5000 |   5000 |00:00:00.32 |   88653 |     14 |       |       |          |
|*  3 |    COUNT STOPKEY                   |                   |      1 |        |   5000 |00:00:00.32 |   88653 |     14 |       |       |          |
|   4 |     VIEW                           |                   |      1 |   9513 |   5000 |00:00:00.32 |   88653 |     14 |       |       |          |
|*  5 |      SORT ORDER BY STOPKEY         |                   |      1 |   9513 |   5000 |00:00:00.32 |   88653 |     14 |   372K|   372K|  330K (0)|
|   6 |       NESTED LOOPS                 |                   |      1 |        |  18949 |00:00:00.29 |   88653 |     14 |       |       |          |
|   7 |        NESTED LOOPS                |                   |      1 |   9513 |  18949 |00:00:00.24 |   69704 |     14 |       |       |          |
|*  8 |         TABLE ACCESS BY INDEX ROWID| PROTOKOLL         |      1 |   9513 |  18949 |00:00:00.17 |   44259 |     14 |       |       |          |
|*  9 |          INDEX SKIP SCAN           | PROTOKOLL_TYP_IX2 |      1 |  21218 |  43283 |00:00:00.06 |     977 |     14 |       |       |          |

PLAN_TABLE_OUTPUT                                                                                                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------
|* 10 |         INDEX UNIQUE SCAN          | PROTOKOLL_PK      |  18949 |      1 |  18949 |00:00:00.05 |   25445 |      0 |       |       |          |
|* 11 |        TABLE ACCESS BY INDEX ROWID | PROTOKOLL         |  18949 |      1 |  18949 |00:00:00.04 |   18949 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("RNUM">0)
   3 - filter(ROWNUM<=5000)
   5 - filter(ROWNUM<=5000)
   8 - filter("P"."BENUTZER_ID"='<b>Shakedown-Standesbeamter'</b>)

PLAN_TABLE_OUTPUT        
--------------------------------------------------------------------------------------------------------------------------------------------------------
       filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
  10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
  11 - filter("P"."TYP"='A')


The first plan is very similar to the plans we received at the customers. I think, I will use the first filter criteria, I will create the new index you proposed in our test environment and hope, that if I can speed up the query in our test system, the new index will provide the same result at the customers as well. Obviously, I'll test and try it....

Q: In the second plan, again unclear for me how is the BENUTZER filtering implemented? None of the applied indexes contain the BENUTZER field.

Note to A2: My uncertainty was in the second case. I suspected also the "SORT ORDER BY STOPKEY" part, I just want to understand how does it really operate...

Note to Q4:
With fetch limitation (5000), without /*+ FIRST_ROWS */ hint
Plan hash value: 1814544890
-------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                          | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem | 
-------------------------------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                   |                           |      1 |        |      1 |00:00:07.69 |   48788 |  26669 |       |       |          | 
|   1 |  SORT AGGREGATE                    |                           |      1 |      1 |      1 |00:00:07.69 |   48788 |  26669 |       |       |          | 
|*  2 |   VIEW                             |                           |      1 |    446 |   5000 |00:00:07.69 |   48788 |  26669 |       |       |          | 
|*  3 |    COUNT STOPKEY                   |                           |      1 |        |   5000 |00:00:07.69 |   48788 |  26669 |       |       |          | 
|   4 |     VIEW                           |                           |      1 |    446 |   5000 |00:00:07.68 |   48788 |  26669 |       |       |          | 
|*  5 |      SORT ORDER BY STOPKEY         |                           |      1 |    446 |   5000 |00:00:07.68 |   48788 |  26669 |   337K|   337K|  299K (0)| 
|   6 |       NESTED LOOPS                 |                           |      1 |        |  10543 |00:00:08.10 |   48788 |  26669 |       |       |          | 
|   7 |        NESTED LOOPS                |                           |      1 |    446 |  10543 |00:00:08.00 |   38245 |  26482 |       |       |          | 
|*  8 |         TABLE ACCESS BY INDEX ROWID| PROTOKOLL                 |      1 |    446 |  10543 |00:00:01.41 |   17157 |  16725 |       |       |          | 
|*  9 |          INDEX RANGE SCAN          | PROTOKOLL_BENUTZER_ID_IX1 |      1 |   1805 |  21086 |00:00:00.02 |     460 |    458 |       |       |          | 
|* 10 |         INDEX UNIQUE SCAN          | PROTOKOLL_PK              |  10543 |      1 |  10543 |00:00:05.85 |   21088 |   9757 |       |       |          | 
|* 11 |        TABLE ACCESS BY INDEX ROWID | PROTOKOLL                 |  10543 |      1 |  10543 |00:00:00.11 |   10543 |    187 |       |       |          | 
-------------------------------------------------------------------------------------------------------------------------------------------------------------- 

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

   2 - filter("RNUM">0) 
   3 - filter(ROWNUM<=5000)
   5 - filter(ROWNUM<=5000)
   8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
   9 - access("P"."BENUTZER_ID"='A07BU0006')
  10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")

PLAN_TABLE_OUTPUT 
------------------------------------
  11 - filter("P"."TYP"='A')



With fetch limitation (5000), with /*+ FIRST_ROWS(5000) */ hint
Plan hash value: 1814544890
-----------------------------------------------------------------------------------------------------------------------------------------------------          
| Id  | Operation                          | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |          
-----------------------------------------------------------------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT                   |                           |      1 |        |      1 |00:00:00.19 |   48788 |       |       |          |          
|   1 |  SORT AGGREGATE                    |                           |      1 |      1 |      1 |00:00:00.19 |   48788 |       |       |          |          
|*  2 |   VIEW                             |                           |      1 |    446 |   5000 |00:00:00.20 |   48788 |       |       |          |          
|*  3 |    COUNT STOPKEY                   |                           |      1 |        |   5000 |00:00:00.19 |   48788 |       |       |          |          
|   4 |     VIEW                           |                           |      1 |    446 |   5000 |00:00:00.19 |   48788 |       |       |          |          
|*  5 |      SORT ORDER BY STOPKEY         |                           |      1 |    446 |   5000 |00:00:00.19 |   48788 |   337K|   337K|  299K (0)|          
|   6 |       NESTED LOOPS                 |                           |      1 |        |  10543 |00:00:00.18 |   48788 |       |       |          |          
|   7 |        NESTED LOOPS                |                           |      1 |    446 |  10543 |00:00:00.15 |   38245 |       |       |          |          
|*  8 |         TABLE ACCESS BY INDEX ROWID| PROTOKOLL                 |      1 |    446 |  10543 |00:00:00.08 |   17157 |       |       |          |          
|*  9 |          INDEX RANGE SCAN          | PROTOKOLL_BENUTZER_ID_IX1 |      1 |   1805 |  21086 |00:00:00.01 |     460 |       |       |          |          
|* 10 |         INDEX UNIQUE SCAN          | PROTOKOLL_PK              |  10543 |      1 |  10543 |00:00:00.06 |   21088 |       |       |          |          
|* 11 |        TABLE ACCESS BY INDEX ROWID | PROTOKOLL                 |  10543 |      1 |  10543 |00:00:00.02 |   10543 |       |       |          |          
-----------------------------------------------------------------------------------------------------------------------------------------------------          
                                                                                                                                                               
Predicate Information (identified by operation id):                                                                                                            
---------------------------------------------------                                                                                                            
                                                                                                                                                               
   2 - filter("RNUM">0)                                                                                                                                        
   3 - filter(ROWNUM<=5000)                                                                                                                                    
   5 - filter(ROWNUM<=5000)                                                                                                                                    
   8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))                                                                                    
   9 - access("P"."BENUTZER_ID"='A07BU0006')                                                                                                                   
  10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")                                                                                                         

PLAN_TABLE_OUTPUT                                                                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  11 - filter("P"."TYP"='A')


So the question is: What is the significant difference of the A-Time value? I recognized, that the same Plan hash value is used. Does it mean, that in the second case the plane has been used which was generated in the first case?

Chris Saxon
February 21, 2018 - 11:43 am UTC

Does it really depend on the value of the applied filter?

Yes. The fact you've got different E-rows values for different filters suggests you have a histogram on this column.

But even without a histogram, different filters will almost certainly return different numbers of rows. The more data you access, the longer your query will take...

"SORT ORDER BY STOPKEY" means it doesn't have to store the whole sorted data set. Just up to N.

What is the significant difference of the A-Time value? I recognized, that the same Plan hash value is used. Does it mean, that in the second case the plane has been used which was generated in the first case?


Not sure what you're getting at here.

Károly László, February 20, 2018 - 8:58 pm UTC

On more question. I learned: A-time: The accumulated time spent in this operation – including time spent in its descendants.

I have the following exec. plan.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                           |      1 |        |      1 |00:00:08.20 |   43586 |  24130 |       |       |          |
|   1 |  SORT AGGREGATE                   |                           |      1 |      1 |      1 |00:00:08.20 |   43586 |  24130 |       |       |          |
|*  2 |   VIEW                            |                           |      1 |    889 |   9486 |00:00:08.23 |   43586 |  24130 |       |       |          |
|   3 |    COUNT                          |                           |      1 |        |   9486 |00:00:08.22 |   43586 |  24130 |       |       |          |
|   4 |     VIEW                          |                           |      1 |    889 |   9486 |00:00:08.21 |   43586 |  24130 |       |       |          |
|   5 |      SORT ORDER BY                |                           |      1 |    889 |   9486 |00:00:08.20 |   43586 |  24130 |   372K|   372K|  330K (0)|
|   6 |       NESTED LOOPS                |                           |      1 |    889 |   9486 |00:00:41.07 |   43586 |  24130 |       |       |          |
|*  7 |        TABLE ACCESS BY INDEX ROWID| PROTOKOLL                 |      1 |    889 |   9486 |00:00:15.47 |   15126 |  13130 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | PROTOKOLL_BENUTZER_ID_IX1 |      1 |   3555 |  18972 |00:00:01.47 |     264 |    261 |       |       |          |
|*  9 |        TABLE ACCESS BY INDEX ROWID| PROTOKOLL                 |   9486 |      1 |   9486 |00:00:23.97 |   28460 |  11000 |       |       |          |
|* 10 |         INDEX UNIQUE SCAN         | PROTOKOLL_PK              |   9486 |      1 |   9486 |00:00:21.40 |   18974 |   9301 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("RNUM">0)
   7 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
   8 - access("P"."BENUTZER_ID"='Kroeger')
   9 - filter("P"."TYP"='A')
  10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")


How can I interpret the definition of the A-time and the 41 sec, 15 sec, 23 sec data against to the 8 sec A-Time of the SELECT STATEMENT?

You suggested the BENUTZER_ID, TYP, ANDERES_PROTOKOLL_UUID index based on the plan for the same query but from the other customer. Do you think, that the same index might be useful for that query as well? Honestly, I find the plan quite balanced.....
Chris Saxon
February 21, 2018 - 11:22 am UTC

Yes, the A-time figures should be cumulative for those below it + its own time. Same for reads and buffers.

So clearly there's an error in your report. Try generating getting the plan using session tracing + tkprof.

Hard to say exactly what will happen on your other database if you add the (BENUTZER_ID, TYP, ANDERES_PROTOKOLL_UUID) index. It's best if you can create it to keep differences between databases as small as possible.

But the optimizer may switch to using it and give you a slower plan than the one you have...

If this happens you could:

- Make the index invisible
- Use SQL Plan Management to lock the "correct" plan

Károly László, February 21, 2018 - 4:22 pm UTC

RE: What is the significant difference of the A-Time value?
Not sure what you're getting at here.

Sorry, the I should ask the question this way:
What is the reason of the significant difference between the two A-time values?

What I've made:
I've executed the query (see at the beginning of this post) twice:
firstly: SELECT /*+ gather_plan_statistics */*
FROM (SELECT a.*, ROWNUM rnum
FROM (SELECT
t0.*, t1.*......., namely without /*+ FIRST_ROWS */ hint.
secondly:SELECT /*+ gather_plan_statistics */*
FROM (SELECT /*+ FIRST_ROWS(5000) */ a.*, ROWNUM rnum
FROM (SELECT
t0.*, t1.*......., namely with the FIRST_ROWS(5000) hint. If you check the A-Time results, you will see the significant difference.

So, what is the reason of the significant difference between the two A-time values?

RE: your suggestion to "session tracing + tkprof", I'll investigate and do it, thanks.

At the very beginning, you suggested to use an index on BENUTZER_ID, TYP, ANDERES_PROTOKOLL_UUID fields and after that you ask whether I could extend the sort order:
Instead of "ORDER BY t0.ZEITPUNKT DESC" rather use "BENUTZER_ID, TYP, ANDERES_PROTOKOLL_UUID, ZEITPUNKT DESC". This is unfortunately not possible, we have to stick to time ordering in the first position.

RE: SORT ORDER BY STOPKEY problem: I understand, that here is the record limitation implemented. But how is ensured, that the records have been sorted by ZEITPUNKT field? One of the used indices in the plan contain the ZEITPUNKT field. I have a suspicion: the PK field of the table (UUID) is filled up with data generated by sys_guid() function. Is the time somehow included in the generated value? If we sort the results by UUID field, do we receive the same sequence order as if the order had made by ZEITPUNKT field?


Chris Saxon
February 21, 2018 - 5:01 pm UTC

There's different numbers of rows for those steps with different A-Time, isn't there? If not, it could be a reporting error.

you ask whether I could extend the sort order

I meant extend the index. Make an index on

"BENUTZER_ID, TYP, ANDERES_PROTOKOLL_UUID, ZEITPUNKT DESC"

Leave the order by as is.

But how is ensured, that the records have been sorted by ZEITPUNKT field?

Because you have "ORDER BY t0.ZEITPUNKT DESC". That's what guarantees it!

Strategy

Gh, February 21, 2018 - 5:43 pm UTC

Well all these investigation are good.
Why not trying to make physical relation ship between ANDERES_PROTOKOLL_UUID and UUID?
in some case can use fk or connect by??
Or maybe a Mv for type 'A' . And join the source table to the mv after appropriate indexes or perhaps partitioning the mv ??

I believe that this related to bad conception.
Chris Saxon
February 21, 2018 - 8:58 pm UTC

Yep, it's worth asking if the query can be rewritten.

Back to the baseline

A reader, March 10, 2018 - 2:07 pm UTC

Sorry, I have to go back to the base line.
The reason is:
- surely we will remove the FIRST_ROW hint from the query, it seems, we are not able to put the "n" value to the "FIRST_ROW(n)" hint :-(
- apart from the BENUTZER_ID, there are additional filters as well, e.g. MANDANT_ID

So the query looks like this way:
SELECT *
FROM (SELECT a.*, ROWNUM rnum
FROM (SELECT
t0.*, t1.*
FROM PROTOKOLL t0, PROTOKOLL t1
WHERE ((((t0.BENUTZER_ID = 'Buenn') AND (t0.MANDANT_ID = '01002110')) AND (t0.TYP = 'E')) AND ((t1.UUID = t0.ANDERES_PROTOKOLL_UUID) AND (t1.TYP = 'A')))
ORDER BY t0.ZEITPUNKT DESC) a WHERE ROWNUM <= 5000) WHERE rnum > 0;

We might have the following combinations:
With BENUTZER_ID, with MANDANT_ID
Without BENUTZER_ID, with MANDANT_ID

Of course, we have different values of the filters....

WHERE (t0.BENUTZER_ID = 'Buenn') AND (t0.MANDANT_ID = '01002110')
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |      1 |        |      1 |00:08:04.44 |     176K|    134K|       |       |          |
|   1 |  SORT AGGREGATE                         |                           |      1 |      1 |      1 |00:08:04.44 |     176K|    134K|       |       |          |
|*  2 |   VIEW                                  |                           |      1 |    705 |   5000 |00:08:04.45 |     176K|    134K|       |       |          |
|*  3 |    COUNT STOPKEY                        |                           |      1 |        |   5000 |00:08:04.44 |     176K|    134K|       |       |          |
|   4 |     VIEW                                |                           |      1 |    705 |   5000 |00:08:04.44 |     176K|    134K|       |       |          |
|*  5 |      SORT ORDER BY STOPKEY              |                           |      1 |    705 |   5000 |00:08:04.44 |     176K|    134K|   372K|   372K|  330K (0)|
|   6 |       NESTED LOOPS                      |                           |      1 |        |  22998 |00:05:42.78 |     176K|    134K|       |       |          |
|   7 |        NESTED LOOPS                     |                           |      1 |    705 |  22998 |00:04:38.01 |     153K|    121K|       |       |          |
|*  8 |         TABLE ACCESS BY INDEX ROWID     | PROTOKOLL                 |      1 |    705 |  22998 |00:02:24.42 |     107K|    106K|       |       |          |
|   9 |          BITMAP CONVERSION TO ROWIDS    |                           |      1 |        |  22998 |00:02:20.11 |   84637 |  84405 |       |       |          |
|  10 |           BITMAP AND                    |                           |      1 |        |      3 |00:04:06.30 |   84637 |  84405 |       |       |          |
|  11 |            BITMAP CONVERSION FROM ROWIDS|                           |      1 |        |      4 |00:00:05.04 |     772 |    768 |       |       |          |
|* 12 |             INDEX RANGE SCAN            | PROTOKOLL_BENUTZER_ID_IX1 |      1 |  89529 |  45996 |00:00:01.61 |     772 |    768 |       |       |          |
|  13 |            BITMAP CONVERSION FROM ROWIDS|                           |      1 |        |     18 |00:01:16.65 |    7072 |   7059 |       |       |          |
|* 14 |             INDEX RANGE SCAN            | PROTOKOLL_MANDANT_ID_IX1  |      1 |  89529 |    228K|00:00:14.97 |    7072 |   7059 |       |       |          |
|  15 |            BITMAP CONVERSION FROM ROWIDS|                           |      1 |        |    212 |00:00:56.81 |   76793 |  76578 |       |       |          |
|* 16 |             INDEX RANGE SCAN            | PROTOKOLL_TYP_IX1         |      1 |  89529 |   3571K|00:05:01.24 |   76793 |  76578 |       |       |          |
|* 17 |         INDEX UNIQUE SCAN               | PROTOKOLL_PK              |  22998 |      1 |  22998 |00:02:04.76 |   46016 |  15627 |       |       |          |
|* 18 |        TABLE ACCESS BY INDEX ROWID      | PROTOKOLL                 |  22998 |      1 |  22998 |00:01:10.18 |   22998 |  12679 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("RNUM">0)
   3 - filter(ROWNUM<=5000)
   5 - filter(ROWNUM<=5000)
   8 - filter("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL)
  12 - access("P"."BENUTZER_ID"='Buenn')
  14 - access("P"."MANDANT"='01002110')
  16 - access("P"."TYP"='E')
  17 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
  18 - filter("P"."TYP"='A')


WHERE (t0.MANDANT_ID = '01002110')
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                         |      1 |        |      1 |00:07:01.77 |     211K|    110K|
|   1 |  SORT AGGREGATE                   |                         |      1 |      1 |      1 |00:07:01.77 |     211K|    110K|
|*  2 |   VIEW                            |                         |      1 |   5000 |   5000 |00:03:50.29 |     211K|    110K|
|*  3 |    COUNT STOPKEY                  |                         |      1 |        |   5000 |00:03:50.28 |     211K|    110K|
|   4 |     VIEW                          |                         |      1 |   5000 |   5000 |00:03:50.28 |     211K|    110K|
|   5 |      NESTED LOOPS                 |                         |      1 |        |   5000 |00:03:50.27 |     211K|    110K|
|   6 |       NESTED LOOPS                |                         |      1 |   5000 |   5000 |00:03:42.04 |     206K|    108K|
|*  7 |        TABLE ACCESS BY INDEX ROWID| PROTOKOLL               |      1 |  89529 |   5000 |00:03:16.51 |     196K|    106K|
|   8 |         INDEX FULL SCAN DESCENDING| PROTOKOLL_ZEITPUNKT_IX1 |      1 |    635K|    302K|00:00:17.83 |    2261 |   2156 |
|*  9 |        INDEX UNIQUE SCAN          | PROTOKOLL_PK            |   5000 |      1 |   5000 |00:00:20.63 |   10006 |   2655 |
|* 10 |       TABLE ACCESS BY INDEX ROWID | PROTOKOLL               |   5000 |      1 |   5000 |00:00:06.47 |    5000 |   1691 |
--------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("RNUM">0)
   3 - filter(ROWNUM<=5000)
   7 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."MANDANT"='01002110' AND "P"."TYP"='E'))
   9 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
  10 - filter("P"."TYP"='A')


Comments:
- you see the long A-Time values in both cases
- I see, that without the 5000 limit, the query with the first condition would return 23K rows, it's OK
- but with the second condition should return even more, because only the MANDAT_ID filter is applied, but I can see only 302K in the A-Rows and after that the 5000 limit only
- and a comoletly different exec. plan, even without the usage any index which contains MANDANT_ID field

Let me change the filter parameters now!
WHERE (t0.BENUTZER_ID = 'wiese') AND (t0.MANDANT_ID = '01055110')
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |      1 |        |      1 |00:00:00.84 |   10590 |    152 |       |       |          |
|   1 |  SORT AGGREGATE                         |                           |      1 |      1 |      1 |00:00:00.84 |   10590 |    152 |       |       |          |
|*  2 |   VIEW                                  |                           |      1 |      1 |   2205 |00:00:00.85 |   10590 |    152 |       |       |          |
|*  3 |    COUNT STOPKEY                        |                           |      1 |        |   2205 |00:00:00.84 |   10590 |    152 |       |       |          |
|   4 |     VIEW                                |                           |      1 |      1 |   2205 |00:00:00.84 |   10590 |    152 |       |       |          |
|*  5 |      SORT ORDER BY STOPKEY              |                           |      1 |      1 |   2205 |00:00:00.84 |   10590 |    152 |   142K|   142K|  126K (0)|
|   6 |       NESTED LOOPS                      |                           |      1 |        |   2205 |00:00:00.13 |   10590 |    152 |       |       |          |
|   7 |        NESTED LOOPS                     |                           |      1 |      1 |   2205 |00:00:00.12 |    8385 |    151 |       |       |          |
|*  8 |         TABLE ACCESS BY INDEX ROWID     | PROTOKOLL                 |      1 |      1 |   2205 |00:00:00.09 |    3973 |     94 |       |       |          |
|   9 |          BITMAP CONVERSION TO ROWIDS    |                           |      1 |        |   4410 |00:00:00.06 |     323 |      7 |       |       |          |
|  10 |           BITMAP AND                    |                           |      1 |        |      1 |00:00:00.06 |     323 |      7 |       |       |          |
|  11 |            BITMAP CONVERSION FROM ROWIDS|                           |      1 |        |      1 |00:00:00.04 |     180 |      5 |       |       |          |
|* 12 |             INDEX RANGE SCAN            | PROTOKOLL_BENUTZER_ID_IX1 |      1 |   3555 |   4422 |00:00:00.01 |     180 |      5 |       |       |          |
|  13 |            BITMAP CONVERSION FROM ROWIDS|                           |      1 |        |      1 |00:00:00.02 |     143 |      2 |       |       |          |
|* 14 |             INDEX RANGE SCAN            | PROTOKOLL_MANDANT_ID_IX1  |      1 |   3555 |   4720 |00:00:00.01 |     143 |      2 |       |       |          |
|* 15 |         INDEX UNIQUE SCAN               | PROTOKOLL_PK              |   2205 |      1 |   2205 |00:00:00.48 |    4412 |     57 |       |       |          |
|* 16 |        TABLE ACCESS BY INDEX ROWID      | PROTOKOLL                 |   2205 |      1 |   2205 |00:00:00.01 |    2205 |      1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("RNUM">0)
   3 - filter(ROWNUM<=5000)
   5 - filter(ROWNUM<=5000)
   8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
  12 - access("P"."BENUTZER_ID"='wiese')
  14 - access("P"."MANDANT"='01055110')
  15 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
  16 - filter("P"."TYP"='A')


WHERE (t0.MANDANT_ID = '01055110')

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |      1 |        |      1 |00:00:01.30 |   11146 |    348 |       |       |          |
|   1 |  SORT AGGREGATE                    |                          |      1 |      1 |      1 |00:00:01.30 |   11146 |    348 |       |       |          |
|*  2 |   VIEW                             |                          |      1 |   1376 |   2360 |00:00:01.30 |   11146 |    348 |       |       |          |
|*  3 |    COUNT STOPKEY                   |                          |      1 |        |   2360 |00:00:01.30 |   11146 |    348 |       |       |          |
|   4 |     VIEW                           |                          |      1 |   1376 |   2360 |00:00:01.30 |   11146 |    348 |       |       |          |
|*  5 |      SORT ORDER BY STOPKEY         |                          |      1 |   1376 |   2360 |00:00:01.29 |   11146 |    348 |   160K|   160K|  142K (0)|
|   6 |       NESTED LOOPS                 |                          |      1 |        |   2360 |00:00:00.04 |   11146 |    348 |       |       |          |
|   7 |        NESTED LOOPS                |                          |      1 |   1376 |   2360 |00:00:00.03 |    8786 |    346 |       |       |          |
|*  8 |         TABLE ACCESS BY INDEX ROWID| PROTOKOLL                |      1 |   1377 |   2360 |00:00:00.02 |    4064 |    256 |       |       |          |
|*  9 |          INDEX RANGE SCAN          | PROTOKOLL_MANDANT_ID_IX1 |      1 |   5507 |   4720 |00:00:00.01 |     143 |      0 |       |       |          |
|* 10 |         INDEX UNIQUE SCAN          | PROTOKOLL_PK             |   2360 |      1 |   2360 |00:00:00.66 |    4722 |     90 |       |       |          |
|* 11 |        TABLE ACCESS BY INDEX ROWID | PROTOKOLL                |   2360 |      1 |   2360 |00:00:00.01 |    2360 |      2 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("RNUM">0)
   3 - filter(ROWNUM<=5000)
   5 - filter(ROWNUM<=5000)
   8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
   9 - access("P"."MANDANT"='01055110')
  10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
  11 - filter("P"."TYP"='A')


Comments:
- A-Time values are fine :-)
- I see, that the queries return 2200-2300 rows only
- the first exec plan are pretty same
- the second are not, in the second case I see the exec. plan which I would have expect: usage of PROTOKOLL_MANDANT_ID_IX1

But the main problem is the significant difference on the A-Time values depending on the values of the applied filter....

Q: What can you suggest to speed up the queries with the first filters?

Chris Saxon
March 14, 2018 - 2:00 pm UTC

the first query's plan includes:

   9 |          BITMAP CONVERSION TO ROWIDS    |                           |      1 |        |  22998 |00:02:20.11 |   84637 |  84405 |       |       |          |
|  10 |           BITMAP AND                    |                           |      1 |        |      3 |00:04:06.30 |   84637 |  84405 |       |       |          |
|  11 |            BITMAP CONVERSION FROM ROWIDS|                           |      1 |        |      4 |00:00:05.04 |     772 |    768 |       |       |          |
|* 12 |             INDEX RANGE SCAN            | PROTOKOLL_BENUTZER_ID_IX1 |      1 |  89529 |  45996 |00:00:01.61 |     772 |    768 |       |       |          |
|  13 |            BITMAP CONVERSION FROM ROWIDS|                           |      1 |        |     18 |00:01:16.65 |    7072 |   7059 |       |       |          |
|* 14 |             INDEX RANGE SCAN            | PROTOKOLL_MANDANT_ID_IX1  |      1 |  89529 |    228K|00:00:14.97 |    7072 |   7059 |       |       |          |
|  15 |            BITMAP CONVERSION FROM ROWIDS|                           |      1 |        |    212 |00:00:56.81 |   76793 |  76578 |       |       |          |
|* 16 |             INDEX RANGE SCAN            | PROTOKOLL_TYP_IX1         |      1 |  89529 |   3571K|00:05:01.24 |   76793 |  76578 |       |       |          |

...

  12 - access("P"."BENUTZER_ID"='Buenn')
  14 - access("P"."MANDANT"='01002110')
  16 - access("P"."TYP"='E')


So presumably you've got single column indexes on BENUTZER_ID, MANDANT and TYP.

In which case, a single composite index on some combination of:

(BENUTZER_ID, MANDANT, TYP)

Will likely help.

Query optimization help

A reader, September 21, 2022 - 5:50 am UTC

Hi Connor,

Is there any better way to rewrite this query.. ?

CES.F_CUR_PTYLD_B  - Eff_dt_start is the partition key
CES.CUR_B - Eff_dt_start is the partition key

INDEX_OWNER     INDEX_NAME                               COLUMN_NAME                    COLUMN_POSITION
--------------- ---------------------------------------- ------------------------------ ---------------
CES          F_CUR_PT_YLD_BI_PK                          C_ENTY_ID                       1
CES          F_CUR_PT_YLD_BI_PK                EFF_DT_START                    2
CES          F_CUR_PT_YLD_BI_PK                TENOR_ID                        3
CES          F_CUR_PT_YLD_BI_PK                RECORD_DT_START                 4



INDEX_OWNER     INDEX_NAME                               COLUMN_NAME                    COLUMN_POSITION
--------------- ---------------------------------------- ------------------------------ ---------------
CES          CUR_B_PK                              C_ENTY_ID                                1
CES          CUR_B_PK                              EFF_DT_START                             2
CES          CUR_B_PK                              RECORD_DT_START                          3
CES          C_EXTID_IDX                          EXTERNAL_ID                               1
CES          C_EXTID_IDX                          EFF_DT_START                              2
CES          C_EXTNAME_IDX                        EXTERNAL_NAME                             1
CES          C_EXTNAME_IDX                        EFF_DT_START                              2

SELECT /*+ gather_plan_statistics */ DISTINCT (EXTERNAL_ID)                AS EXTERNAL_ID,
                  EXTERNAL_NAME                AS EXTERNAL_NAME,
                  CPYB.EFF_DT_START     AS EFF_DT_START,
                  TN.TENOR_SHORT_DESC          AS TENOR,
                  CPYB.TENOR_DATE               AS TENOR_DATE,
                  CPYB.PAR_YIELD                AS PAR_YIELD,
                  CPYB.BENCHMARK_YIELD          AS BENCHMAKRK_YIELD,
                  CPYB.Z_SPREAD                 AS Z_SPREAD,
                  CPYB.ASSET_SWAP_SPREAD        AS ASSET_SPREAD,
                  CPYB.ZERO_YIELD               AS ZERO_YIELD,
                  CPYB.DISCOUNT_FACTOR          AS DISCOUNT_FACTOR,
                  CPYB.FORWARD_RATE             AS FORWARD_RATE,
                  CPYB.RATE_SIDE                AS RATE_SIDE,
                  CPYB.C_ENTY_ID          AS ENTY_ID
    FROM (  SELECT DISTINCT
                   T1.C_ENTY_ID,
                   TRUNC (T1.EFF_DT_START)     AS CURVE_DATE,
                   T1.EFF_DT_START             AS SNAP_TS
              FROM CES.F_CUR_PTYLD_B T1
                   INNER JOIN CES.MSTONE M
                       ON (    M.MILESTONE_START = T1.EFF_DT_START
                           AND M.MILESTONE_NAME = :v_MILESTONE_NAME)
             WHERE     T1.C_ENTY_ID = :v_C_ENTY_ID
                   AND T1.RECORD_DT_END > SYS_EXTRACT_UTC (SYSTIMESTAMP)
                   AND M.RECORD_DT_END > SYS_EXTRACT_UTC (SYSTIMESTAMP)
                   AND T1.EFF_DT_START <  to_timestamp(:v_LATEST_DATE) + 1
                   AND T1.EFF_DT_START >= to_timestamp(:v_EARLIEST_DATE)
          ORDER BY 3 DESC) SNP_DATE
         INNER JOIN CES.F_CUR_PTYLD_B CPYB
             ON (    SNP_DATE.C_ENTY_ID = CPYB.C_ENTY_ID
                 AND SNP_DATE.SNAP_TS = CPYB.EFF_DT_START)
         INNER JOIN CES.TNOR_NM TN ON (CPYB.TENOR_ID = TN.TENOR_ID)
         INNER JOIN CES.CUR_B C
             ON (    SNP_DATE.C_ENTY_ID = C.C_ENTY_ID
                 AND SNP_DATE.SNAP_TS = C.EFF_DT_START
                 AND C.RECORD_DT_END > SYS_EXTRACT_UTC (SYSTIMESTAMP))
   WHERE     CPYB.RECORD_DT_END > SYS_EXTRACT_UTC (SYSTIMESTAMP)
         AND ((TN.TENOR_SHORT_DESC = :v_TENOR) OR :v_TENOR IS NULL)
ORDER BY EFF_DT_START DESC, TENOR_DATE ASC
;



Plan hash value: 1934643052

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                           | Name                         | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                    |                              |      1 |        | 13996 (100)|       |       |    104K|00:00:09.18 |    4380K|       |       |          |
|   1 |  SORT ORDER BY                                      |                              |      1 |    134 | 13996   (1)|       |       |    104K|00:00:09.18 |    4380K|    16M|  2351K|   14M (0)|
|   2 |   HASH UNIQUE                                       |                              |      1 |    134 | 13995   (1)|       |       |    104K|00:00:09.08 |    4380K|    22M|  4513K|   21M (0)|
|*  3 |    FILTER                                           |                              |      1 |        |            |       |       |   3149K|00:00:08.44 |    4380K|       |       |          |
|*  4 |     HASH JOIN                                       |                              |      1 |    134 | 13994   (1)|       |       |   3149K|00:00:08.16 |    4380K|  3969K|  3969K| 5323K (0)|
|*  5 |      TABLE ACCESS STORAGE FULL                      | TNOR_NM                     |      1 |     12 |     3   (0)|       |       |    244 |00:00:00.01 |    6    |  1025K|  1025K|          |
|   6 |      NESTED LOOPS                                   |                              |      1 |    879 | 13991   (1)|       |       |   3149K|00:00:07.33 |    4380K|       |       |          |
|   7 |       NESTED LOOPS                                  |                              |      1 |    879 | 13991   (1)|       |       |   4580K|00:00:02.41 |     203K|       |       |          |
|*  8 |        HASH JOIN RIGHT SEMI                         |                              |      1 |    600 | 11591   (1)|       |       |    104K|00:00:00.35 |     157K|  4267K|  4267K| 5459K (0)|
|*  9 |         TABLE ACCESS STORAGE FULL                   | MSTONE                       |      1 |   5253 |   239   (1)|       |       |   5253 |00:00:00.01 |     821 |  1025K|  1025K|          |
|  10 |         NESTED LOOPS                                |                              |      1 |   3051 | 11351   (1)|       |       |    104K|00:00:00.29 |     156K|       |       |          |
|  11 |          NESTED LOOPS                               |                              |      1 |   3051 | 11351   (1)|       |       |    151K|00:00:00.11 |   19634 |       |       |          |
|  12 |           PARTITION RANGE ITERATOR                  |                              |      1 |   2082 |  3022   (1)|   KEY |   KEY |   3555 |00:00:00.02 |    8612 |       |       |          |
|* 13 |            TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CUR_B                        |    491 |   2082 |  3022   (1)|   KEY |   KEY |   3555 |00:00:00.02 |    8612 |       |       |          |
|* 14 |             INDEX RANGE SCAN                        | CUR_B_PK                     |    491 |   2087 |   983   (0)|   KEY |   KEY |   7115 |00:00:00.01 |    1506 |  1025K|  1025K|          |
|  15 |           PARTITION RANGE AND                       |                              |   3555 |      1 |     3   (0)|KEY(AP)|KEY(AP)|    151K|00:00:00.08 |   11022 |       |       |          |
|* 16 |            INDEX RANGE SCAN                         | F_CUR_PT_YLD_BI_PK           |   3555 |      1 |     3   (0)|KEY(AP)|KEY(AP)|    151K|00:00:00.05 |   11022 |  1025K|  1025K|          |
|* 17 |          TABLE ACCESS BY LOCAL INDEX ROWID          | F_CUR_PTYLD_B                |    151K|      1 |     4   (0)|     1 |     1 |    104K|00:00:00.17 |     136K|       |       |          |
|  18 |        PARTITION RANGE AND                          |                              |    104K|      1 |     3   (0)|KEY(AP)|KEY(AP)|   4580K|00:00:01.72 |   46464 |       |       |          |
|* 19 |         INDEX RANGE SCAN                            | F_CUR_PT_YLD_BI_PK           |    104K|      1 |     3   (0)|KEY(AP)|KEY(AP)|   4580K|00:00:01.11 |   46464 |  1025K|  1025K|          |
|* 20 |       TABLE ACCESS BY LOCAL INDEX ROWID             | F_CUR_PTYLD_B                |   4580K|      1 |     4   (0)|     1 |     1 |   3149K|00:00:04.44 |    4176K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$FD2822A5")
      MERGE(@"SEL$57B7B03E" >"SEL$6")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$57B7B03E")
      MERGE(@"SEL$9B19626D" >"SEL$5")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$9B19626D")
      MERGE(@"SEL$5AD7FC28" >"SEL$4")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$5AD7FC28")
      MERGE(@"SEL$2361996F" >"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2361996F")
      MERGE(@"SEL$2" >"SEL$58B2FD6B")
      OUTLINE(@"SEL$58B2FD6B")
      ELIMINATE_OBY(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      INDEX_RS_ASC(@"SEL$FD2822A5" "C"@"SEL$5" ("CUR_B"."C_ENTY_ID" "CUR_B"."EFF_DT_START" "CUR_B"."RECORD_DT_START"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$FD2822A5" "C"@"SEL$5")
      INDEX(@"SEL$FD2822A5" "T1"@"SEL$2" ("F_CUR_PT_YLD_BI"."C_ENTY_ID" "F_CUR_PT_YLD_BI"."EFF_DT_START" "F_CUR_PT_YLD_BI"."TENOR_ID"
              "F_CUR_PT_YLD_BI"."RECORD_DT_START"))
      FULL(@"SEL$FD2822A5" "M"@"SEL$2")
      INDEX(@"SEL$FD2822A5" "CPY"@"SEL$1" ("F_CUR_PT_YLD_BI"."C_ENTY_ID" "F_CUR_PT_YLD_BI"."EFF_DT_START" "F_CUR_PT_YLD_BI"."TENOR_ID"
              "F_CUR_PT_YLD_BI"."RECORD_DT_START"))
      FULL(@"SEL$FD2822A5" "TN"@"SEL$4")
      LEADING(@"SEL$FD2822A5" "C"@"SEL$5" "T1"@"SEL$2" "M"@"SEL$2" "CPY"@"SEL$1" "TN"@"SEL$4")
      USE_NL(@"SEL$FD2822A5" "T1"@"SEL$2")
      NLJ_BATCHING(@"SEL$FD2822A5" "T1"@"SEL$2")
      USE_HASH(@"SEL$FD2822A5" "M"@"SEL$2")
      USE_NL(@"SEL$FD2822A5" "CPY"@"SEL$1")
      NLJ_BATCHING(@"SEL$FD2822A5" "CPY"@"SEL$1")
      USE_HASH(@"SEL$FD2822A5" "TN"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$FD2822A5" "M"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$FD2822A5" "TN"@"SEL$4")
      USE_HASH_AGGREGATION(@"SEL$FD2822A5")
      PARTIAL_JOIN(@"SEL$FD2822A5" "M"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   3 - filter(TO_TIMESTAMP(:V_EARLIEST_DATE)<TO_TIMESTAMP(:V_LATEST_DATE)+1)
   4 - access("CPY"."TENOR_ID"="TN"."TENOR_ID")
   5 - storage((:V_TENOR IS NULL OR "TN"."TENOR_SHORT_DESC"=:V_TENOR))
       filter((:V_TENOR IS NULL OR "TN"."TENOR_SHORT_DESC"=:V_TENOR))
   8 - access("M"."MILESTONE_START"="T1"."EFF_DT_START")
   9 - storage(("M"."MILESTONE_NAME"=:V_MILESTONE_NAME AND "M"."MILESTONE_START">=TO_TIMESTAMP(:V_EARLIEST_DATE) AND "M"."MILESTONE_START"<TO_TIMESTAMP(:V_LATEST_DATE)+1 AND
              "M"."RECORD_DT_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6))))
       filter(("M"."MILESTONE_NAME"=:V_MILESTONE_NAME AND "M"."MILESTONE_START">=TO_TIMESTAMP(:V_EARLIEST_DATE) AND "M"."MILESTONE_START"<TO_TIMESTAMP(:V_LATEST_DATE)+1 AND
              "M"."RECORD_DT_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6))))
  13 - filter("C"."RECORD_DT_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
  14 - access("C"."C_ENTY_ID"=:V_C_ENTY_ID AND "C"."EFF_DT_START">=TO_TIMESTAMP(:V_EARLIEST_DATE) AND "C"."EFF_DT_START"<TO_TIMESTAMP(:V_LATEST_DATE)+1)
  16 - access("T1"."C_ENTY_ID"=:V_C_ENTY_ID AND "T1"."EFF_DT_START"="C"."EFF_DT_START")
       filter(("T1"."EFF_DT_START">=TO_TIMESTAMP(:V_EARLIEST_DATE) AND "T1"."EFF_DT_START"<TO_TIMESTAMP(:V_LATEST_DATE)+1))
  17 - filter("T1"."RECORD_DT_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
  19 - access("CPY"."C_ENTY_ID"=:V_C_ENTY_ID AND "T1"."EFF_DT_START"="CPY"."EFF_DT_START")
       filter(("CPY"."EFF_DT_START">=TO_TIMESTAMP(:V_EARLIEST_DATE) AND "CPY"."EFF_DT_START"<TO_TIMESTAMP(:V_LATEST_DATE)+1))
  20 - filter("CPY"."RECORD_DT_END">SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))


120 rows selected.

Chris Saxon
September 27, 2022 - 3:49 pm UTC

What's with the DISTINCT operations in the top-level query and subquery? These are often a sign of either:

- Problems with the data model
- Queries that should be changed (e.g. to use IN/EXISTS)

Also many of the row estimates are low. e.g. line 16 estimates it'll get 1 row via the index. For 3,555 executions this should yield around 4,000 rows. But it actually gets 151,000 rows! I'd look into what's causing this.

Query optimization

A reader, September 29, 2022 - 3:20 am UTC

Thanks Connor.

>> What's with the DISTINCT operations in the top-level query and subquery? These are often a sign of either:
- Problems with the data model
- Queries that should be changed (e.g. to use IN/EXISTS)


Any suggestions on improving the query with IN/EXISTS. ?

>> Also many of the row estimates are low. e.g. line 16 estimates it'll get 1 row via the index. For 3,555 executions this should yield around 4,000 rows. But it actually gets 151,000 rows! I'd look into what's causing this.

Could you provide some pointers on troubleshooting this if this issue is stats related or would creating extended stats may help here ?


Thanks!
Chris Saxon
September 29, 2022 - 9:47 am UTC

It's Chris here, but you're welcome ;)

Any suggestions on improving the query with IN/EXISTS. ?

I don't understand the data model, so it's hard for me to make suggestions.

The question to answer is: Which values are duplicated and why? This should lead you to whatever is necessary to rewrite the query without DISTINCT.

Could you provide some pointers on troubleshooting this if this issue is stats related or would creating extended stats may help here ?

Some questions to help you:

Have the stats been gathered recently?
Are they reasonably accurate? (e.g. number of rows, distinct values, etc.)
Do you have skew in your data (some values more popular than others or gaps)? If so, do you have histograms on these columns?
Is there a high level of correlation in the columns you're joining/filtering on? If so, extended stats over these may help

Finally - I've just noticed that F_CUR_PTYLD_B is listed twice in the query. Can you rewrite it to access this once?

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.