1

I have an Oracle 19c database (19.0.0.0.0), and two tables with lots of columns, I'll mention only the relevant ones:

table1:

  • createtime date
  • contact_key char(96 byte)

table2:

  • contact_key char(96 byte)
  • csi_tfid varchar2(300 byte)

table1 has 210 million rows, and is partitioned yearly. table2 has 6.8 million records and is not partitioned.

The following query runs very fast, around 0,05 - 0,1 second:

select * from table1 m, table2 c
where
c.contact_key = m.contact_key
and c.csi_tfid = '1234567';

But as soon as I add a where condition to get only the last few records (which is relevant for the application), execution speed drops to 1 minute, or even slower:

select * from table1 m, table2 c
where
c.contact_key = m.contact_key
and c.csi_tfid = '1234567'
and m.createtime >= (sysdate-30);

I tried to put a hardcoded date there as to_date('2024-04-09', 'YYYY-MM-DD'), same result.

I have individual indexes on all of the columns in this query:

table1:

  • ik_table1_contact_k non unique
  • ik_table1_createtime non unique

table2:

  • ik_table2_contact_key unique
  • ik_table2_csi_tfid non unique

I tried to add compound index to both tables, to include contact_key and createtime (for table1) and contact_key and csi_tfid (for table2), seemingly to no effect.

For the fast query, Oracle generates this plan:

enter image description here

Plan hash value: 323565418
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                     |     3 |  1857 |   118   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                               |                     |     3 |  1857 |   118   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED       | TABLE2              |     3 |   966 |     7   (0)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN                         | IK_TABLE2_CSI_TFID  |     3 |       |     3   (0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE ALL                       |                     |     1 |   297 |    37   (0)| 00:00:01 |     1 |    12 |
|   5 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE1              |     1 |   297 |    37   (0)| 00:00:01 |     1 |    12 |
|*  6 |     INDEX RANGE SCAN                        | IK_TABLE1_CONTACT_K |     1 |       |    36   (0)| 00:00:01 |     1 |    12 |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("C"."CSI_TFID"='1234567')
   6 - access("C"."CONTACT_KEY"="M"."CONTACT_KEY")

For the slow query, with the createtime condition, it is very different:

enter image description here

Plan hash value: 1504517877
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                       |     2 |  1238 |    10   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                               |                       |     2 |  1238 |    10   (0)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE ITERATOR                  |                       |     3 |   891 |     6   (0)| 00:00:01 |   KEY |    12 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE1                |     3 |   891 |     6   (0)| 00:00:01 |   KEY |    12 |
|*  4 |     INDEX RANGE SCAN                        | IK_TABLE1_CREATETIME  |     3 |       |     3   (0)| 00:00:01 |   KEY |    12 |
|*  5 |   TABLE ACCESS BY INDEX ROWID               | TABLE2                |     1 |   322 |     2   (0)| 00:00:01 |       |       |
|*  6 |    INDEX UNIQUE SCAN                        | IK_TABLE2_CONTACT_KEY |     1 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("M"."CREATETIME">=SYSDATE@!-30)
   5 - filter("C"."CSI_TFID"='1234567')
   6 - access("C"."CONTACT_KEY"="M"."CONTACT_KEY")
 
Note
-----
   - this is an adaptive plan

It shows the cost as very low for both queries, so it is strange to me how this single condition can add so much complexity.

In table1, contact_key will be basically unique, there is no unique constraint but the application logic pretty much guarantees it is unique, so I guess that the fast query uses this column for search which will be fast. But the slow query uses the createtime column to search in this table which will be slower as there will be a lot of records where creatime is > sysdate - 30.

I tried to workaround this way, hoping that I can do the fast query as subquery, and then only filter the results of that:

select sq.* from (
  select * from table1 m, table2 c
  where
  c.contact_key = m.contact_key
  and c.csi_tfid = '1234567'
) sq
where sq.createtime >= (sysdate-30);

But I got the same slow query, with the same execution plan. What else can I try? The application has no logic currently to filter the results, it relies on the SELECT statement.


-- UPDATE from the DBA

The DBA checked some statistics and history, and it seems this query started "misbehaving" around May 1st. Before that, it was using another index, similarly to what @Andy DB Analyst suggested (another index, I didn't mention it above). I checked all indexes, the statistics, last analyze date etc are all almost identical.

2 Answers 2

1

Looks like you have stale statistics in table1. Optimizer expects 3 rows for m.createtime >= (sysdate-30). Just gather stats and it should be ok.

If for some reasons you can't gather stats try using optimizer hints:

select /*+leading(c m) use_nl_with_index(m IK_TABLE1_CONTACT_K) */
*
from table1 m, table2 c 
where c.contact_key = m.contact_key 
and c.csi_tfid = '1234567' 
and m.createtime >= (sysdate-30);
2
  • That is accurate. I get 3 records for the sysdate-30 query, and 10 records without filtering. But you are right, forcing the index will make it fast. I will update my question with some new info I got from the DBA who manages this DB. Commented May 9 at 17:37
  • Well, eventually we didn't change the query, but the DBA somehow forced the DB to use the same execution plan, so that's it. Thanks for this tip, I sent this to the DBA but not sure if he used it or not. Commented May 22 at 7:23
0

Well, the final solution was that the DBA managed to tweak the database somehow to force it into the same execution plan that the query from @Andy DB Analyst was also using, and now it's OK. I didn't get any further info as that what they tweaked, or what caused the execution plan to change on May 1st.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.