Oracle query: date filter gets really slow

user_465588 0
Viewed 901

I have this oracle query that takes around 1 minute to get the results:

SELECT TRUNC(sysdate - data_ricezione) AS delay
  FROM notifiche@fe_engine2fe_gateway n
 WHERE NVL(n.data_ricezione, TO_DATE('01011900', 'ddmmyyyy')) =
       (SELECT NVL(MAX(n2.data_ricezione), TO_DATE('01011900', 'ddmmyyyy'))
          FROM notifiche@fe_engine2fe_gateway n2
         WHERE n.id_sdi = n2.id_sdi)
--AND sysdate-data_ricezione > 15

Basically i have this table named "notifiche", where each record represents a kind of update to another type of object (invoices). I want to know which invoice has not received any update in the last 15 days. I can do it by joining the notifiche n2 table, getting the most recent record for each invoice, and evaluate the difference between the update date (data_ricezione) and the current date (sysdate).

When i add the commented condition, the query takes then infinite time to complete (i mean hours, never saw the end of it...)

How is possibile that this simple condition make the query so slow?

How can I improve the performance?

oracle sql performance
2 Answers

Try to keep data_ricezione alone; if there's an index on it, it might help.

So: switch from

and sysdate - data_ricezione > 15

to

and -data_ricezione > 15 - sysdate / * (-1)

to

and data_ricezione < sysdate - 15

As everything is done over the database link, see whether the driving_site hint does any good, i.e.

select /*+ driving_site (n) */                --> "n" is table's alias
  trunc(sysdate-data_ricezione) as delay
from
  notifiche@fe_engine2fe_gateway n
...
user_90979060

Use an analytic function to avoid a self-join over a database link. The below query only reads from the table once, divides the rows into windows, finds theMAX value for each window, and lets you select rows based on that maximum. Analytic functions are tricky to understand at fist, but they often lead to code that is smaller and more efficient.

select id_sdi, data_ricezion
from
(
    select id_sdi, data_ricezion, max(data_ricezion) over (partition by id_sdi) max_date
    from notifiche@fe_engine2fe_gateway
)
where sysdate - max_date > 15;

As for why adding a simple condition can make the query slow - it's all about cardinality estimates. Cardinality, the number of rows, drives most of the database optimizer's decision. The best way to join a small amount of data may be very different than the best way to join a large amount of data. Oracle must always guess how many rows are returned by an operation, to know which algorithm to use.

Optimizer statistics (metadata about the tables, columns, and indexes) are what Oracle uses to make cardinality estimates. For example, to guess the number of rows filtered out by sysdate-data_ricezione > 15, the optimizer would want to know how many rows are in the table (DBA_TABLES.NUM_ROWS), what the maximum value for the column is (DBA_TAB_COLUMNS.HIGH_VALUE), and maybe a break down of how many rows are in different age ranges (DBA_TAB_HISTOGRAMS).

All of that information depends on optimizer statistics being correctly gathered. If a DBA foolishly disabled automatic optimizer statistics gathering, then these problems will happen all the time. But even if your system is using good settings, the predicate you're using may be an especially difficult case. Optimizer statistics aren't free to gather, so the system only collects them when 10% of the data changes. But since your predicate involves SYSDATE, the percentage of rows will change every day even if the table doesn't change. It may make sense to manually gather stats on this table more often than the default schedule, or use a /*+ dynamic_sampling */ hint, or create a SQL Profile/Plan Baseline, or one of the many ways to manage optimizer statistics and plan stability. But hopefully none of that will be necessary if you use an analytic function instead of a self-join.

user_4091720
Related