# Slow SQL with timestamp on Oracle

**URL:** <https://discuss.elastic.co/t/slow-sql-with-timestamp-on-oracle/369819>\
**Category:** Logstash\
**Tags:** jdbc\
**Created:** [October 30, 2024, 2:00pm UTC](https://discuss.elastic.co/t/slow-sql-with-timestamp-on-oracle/369819 "2024-10-30T14:00:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Wolfram\_Haussig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wolfram_haussig/32/70528_2.png) [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)\
**Post date:** [October 30, 2024, 2:00pm UTC](https://discuss.elastic.co/t/slow-sql-with-timestamp-on-oracle/369819/1 "2024-10-30T14:00:24Z")

</div>

Hello all,

We have a Logstash Pipeline with `jdbc` input that ran really slow. I investigated and found something where I am not sure if this could be improved on Logstash side or if this is something we need to handle somehow.

I will try to be as detailed as possible.

Target DB: Oracle 19c  
Table size: about 39mio entries (9GB)  
Tracking Column definition: TIMESTAMP(6)  
Index on Tracking Column: yes  
Logstash input config (removed unnecessary information):

```auto
input {
    jdbc {
      jdbc_driver_library => "/opt/elastic/logstash/jdbc/ojdbc8_21.5/ojdbc8-21.5.0.0.jar"
      jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
      jdbc_validate_connection => true
      jdbc_fetch_size => 1000
      schedule => "* * * * *" 
      statement => "SELECT * FROM TRANSFER_ORDER WHERE LASTWORKSTATECHANGE > :sql_last_value ORDER BY LASTWORKSTATECHANGE ASC"
      use_column_value => true
      tracking_column => "lastworkstatechange"
      tracking_column_type => "timestamp"
    }
}

```

The SQL logged into the pipeline log was:

```auto
SELECT * FROM TRANSFER_ORDER WHERE LASTWORKSTATECHANGE > TIMESTAMP '2024-10-30 13:33:05.308000 +00:00' ORDER BY LASTWORKSTATECHANGE ASC

```

The SQL execution was between 46 and 217 seconds despite the index so I checked the explain plan:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/7/477d878bc2c0d459de856e5dec92c0d9bea6f6ef.png)

You can see that the index cannot be used because Oracle uses functions on the column value.

As a test, I tried to modify the SQL so the timestamp function is done on the `sql_last_value` by changing the timestamp to string and back again - the final SQL is now:

```auto
SELECT * FROM TRANSFER_ORDER WHERE LASTWORKSTATECHANGE > TO_TIMESTAMP(TO_CHAR(TIMESTAMP '2024-10-30 13:42:37.477000 +00:00', 'DD-Mon-RR HH24:MI:SS.FF'), 'DD-Mon-RR HH24:MI:SS.FF') ORDER BY LASTWORKSTATECHANGE ASC

```

Now the SQL completes in 2ms! The explain plan is now:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/7/47822524a46dc7f9a3cd220a0af05f9bfb4468b7.png)

Has anyone an idea what I can do so I don't need that ugly workaround? Maybe the `jdbc` input can be improved here?

Best regards  
Wolfram

---

<div class="post-metadata">

**Author:** ![eMitch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emitch/32/93607_2.png) [@eMitch](https://discuss.elastic.co/u/eMitch)\
**Post date:** [October 30, 2024, 4:58pm UTC](https://discuss.elastic.co/t/slow-sql-with-timestamp-on-oracle/369819/2 "2024-10-30T16:58:02Z")

</div>

I've only ever run into ugly workarounds when it comes to Logstash and :sql\_last\_value 🤷

I've also had issues where the last value went missing and then we're potentially loading much more data than expected.

What i always end up doing (in both MSSQL and Oracle) is to create a separate table to store the last value - a config table if you will.

Then I use a stored proc to run the query looking for the new data as well as updates the last value checked.

Still ugly? yea? but at least you maintain control in your db instead of a logstash file.

---

<div class="post-metadata">

**Author:** ![Wolfram\_Haussig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wolfram_haussig/32/70528_2.png) [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)\
**Post date:** [October 31, 2024, 8:44am UTC](https://discuss.elastic.co/t/slow-sql-with-timestamp-on-oracle/369819/3 "2024-10-31T08:44:10Z")

</div>

Hello Eddie,

Glad that I am not the only one 😉 Yes, a stored procedure would work but I prefer to see what happens in a pipeline - especially if it is as simple as this case.

Let's see if someone from the Logstash team has input here.