lucene-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Karl Zweimüller (JIRA) <>
Subject [jira] [Commented] (SOLR-11772) Use JDBC-bind variables for DIH to improve performance with oracle db
Date Wed, 20 Dec 2017 08:04:00 GMT


Karl Zweimüller commented on SOLR-11772:

Hi Shawn!
No, I don't have a patch - I didn't analyze the DIH-Code. I found out by monitoring the oracle
Changing the sql-statements to statements with bind-variables should be jdbc-standard and
not oracle-driver-specific. Therefore it should work with all sql-databases. But I don't know
if other databases would also benefit in performance.

To set the cursor-sharing-parameter in the jdbc-url is not possible. 
But to place the parameter in the after-logon-trigger of the db-user is a working solution
- but a workaround.This modified db-user should be used only for Solr-DIH!

Rearranging the statements (using joins) would be possible, but I think would raise the overall

I don't have a problem when DIH won't change to jdbc-bind-variables, as I know a workaround
for us now.
Maybe this Issue can help others to solve Solr performance-issues with DIH and oracle-db.


> Use JDBC-bind variables for DIH to improve performance with oracle db
> ---------------------------------------------------------------------
>                 Key: SOLR-11772
>                 URL:
>             Project: Solr
>          Issue Type: Improvement
>      Security Level: Public(Default Security Level. Issues are Public) 
>          Components: contrib - DataImportHandler
>            Reporter: Karl Zweimüller
>            Priority: Minor
> I just reduced the time for my full-import (solr 6.0.1) on an oracle-database for 1.4mio
documents from 36 hours to 5 hours by setting the oracle session-parameter "CURSOR_SHARING=FORCE".
> Here I found one with the same problem:
> I have 1.4 mio documents and for every document i need 12 queries to collect sub-information
for the actual document.
> This makes about 17mio sql-Statements to oracle for a full-import.
> As DIH doesn't use bind-variables (,
every select looks "different" for oracle and a full parse (analyze statement, get optimal
query-plan,..) has to be done 17mio times.
> By setting the session parameter "CURSOR_SHARING=FORCE", which can be done in an on_logon_trigger,
oracle replaces all literals ins SQL with bind-variables and then can skip the hard-parse.
> This reduced my full-import-time from 36 hours to 5 hours. (With this you get only 13
different sql-statements compared to 17mio different statements before.
> As oracle states, that setting the CURSOR_SHARING=FORCE is only a workaround, it would
be fine when DIH would use bind-variables for the variables.
> Charly

This message was sent by Atlassian JIRA

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message