Thanks to all for your support. Many good ideas! But meanwhile I found a way using the Oracle "AFTER LOGON" Trigger where I am able to identify if it is a connection from solr and then switch to cursor sharing.
By the way cursor sharing reduced the import time from 12 to 6 hours !! So it would be really great if the Data Import Handler would be able to use binding variables in selects so that cursor sharing could be done easier. Regards, Birgit -----Original Message----- From: Mannott, Birgit [mailto:b.mann...@klopotek.de] Sent: Tuesday, August 15, 2017 4:58 PM To: solr-user@lucene.apache.org Subject: [bulk]: RE: [bulk]: Re: Optimizing Dataimport from Oracle; cursor sharing; changing oracle session parameters Yes, I'm using Data Import Handler and I would prefer a solution for this way of import because it's already tested and the imported data is ok and everything is fine. I just have to speed it up a little... But thanks for your info. Next time I'll try indexing with solrj. Regards, Birgit -----Original Message----- From: Erick Erickson [mailto:erickerick...@gmail.com] Sent: Tuesday, August 15, 2017 4:33 PM To: solr-user <solr-user@lucene.apache.org> Subject: [bulk]: Re: Optimizing Dataimport from Oracle; cursor sharing; changing oracle session parameters I presume you're using Data Import Handler? An alternative when you get into complex imports is to use a SolrJ client, here's a sample. That way you can use whatever tools the particular JDBC connector will allow and can be much faster. https://lucidworks.com/2012/02/14/indexing-with-solrj/ Best, Erick On Tue, Aug 15, 2017 at 7:09 AM, Mannott, Birgit <b.mann...@klopotek.de> wrote: > Hi, > > I'm using solr 6.6.0 and I have to do a complex data import from an oracle db > concerning 3.500.000 data rows. > For each row I have 15 additional entities. That means that more than 52 > Million selects are send to the database. > For every select that is done I optimized the oracle execution path by > creating indizes. > The execution plans are ok. > But the import still lasts 12 hours. > > I think, the main remaining problem is that oracle cursor sharing is not used > and that for every select a hard parse is done. > Solr does not use binding variables. This would be the easiest way to use > oracle cursor sharing. But I didn't find anything about influencing the way > solr builds select statements. > I could force oracle cursor sharing without binding variables but I have to > do this configuration for the session. I'm not allowed to change the > configuration of the whole database system. > > Is there a way to execute a command like "ALTER SESSION SET cursor_sharing = > FORCE;" after getting the connection for processing an entity? > > Thanks, > Birgit
