Uploaded image for project: 'Solr'
  1. Solr
  2. SOLR-11772

Use JDBC-bind variables for DIH to improve performance with oracle db

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:
      None

      Description

      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:
      http://lucene.472066.n3.nabble.com/Optimizing-Dataimport-from-Oracle-cursor-sharing-changing-oracle-session-parameters-td4350601.html

      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 (https://docs.oracle.com/cd/B28359_01/appdev.111/b28765/addfunc.htm#TDPJD210), 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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                charly_at Karl Zweim├╝ller
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: