Hi David, On Sep 4, 2011, at 5:27pm, David Robson wrote:
> Could you please send the explain plans from both databases now you are using > the ALTER SESSION statement? Are you using OEM to monitor the database? The DBAs are monitoring load with some tool, not sure how. > Could you send a screenshot of the SQL Details screen for the SQL on the > Activity tab as well for both servers – be interesting to see the difference > in waits. Most of this info would need to get cleared through the security team, so that will take a while. But I'll see if I can get some generic details sooner. > One other thing – could you take one of the SQL statements OraOop runs and > add the NO_INDEX hint to it and then just run it – you could put it in a > PL/SQL loop that just does nothing then you could confirm if we add the > NO_INDEX hint it will definitely fix your issue? If it looks like it will > definitely fix your issue I can start work on a patch – then we can get back > to you with an approximate timeframe. Let me see if I can get one of the DBAs to try this out. Thanks, -- Ken > From: Ken Krugler [mailto:kkrugler_li...@transpac.com] > Sent: Sunday, 4 September 2011 9:35 AM > To: sqoop-user@incubator.apache.org > Subject: Re: [sqoop-user] Slow queries with table that has index when using > OraOop > > Hi Peter, > > On Sep 1, 2011, at 5:21pm, Peter Hall wrote: > > > Hi Ken, > > OraOop should be using the NO_INDEX hint, we'll get that fixed for the next > release. > > Until then you may be able to work around this issue with session > initialization statements. > > Try > ALTER SESSION SET optimizer_index_cost_adj=10000; > to tell oracle to not use the index. You may need to experiment with this a > little to find a value that will have the desired effect. > > [snip] > > It's much faster with that change, thanks for that suggestion! > > Though it's still not as fast as the other DB that doesn't have the composite > index - 3M rows/min vs. 13M rows/min. > > It would be great to get a version that has the NO_INDEX hint soon - any idea > when that might be released? > > Thanks, > > -- Ken > > We ran into an interesting performance issue recently, using OraOop to pull > data. > > We've got two tables in two different Oracle DBs. They use identical schemas, > but one of the tables has a composite index on a number of columns, one of > which is used in our WHERE clause. > > The table without this composite index has good performance - about 10M > rows/minute using 8 mappers. And very low load on the DB server. > > The table access is via "TABLE ACCESS BY ROWID RANGE" > > The table with the composite index has really bad performance - only 33K > rows/minute using 8 mappers. And very high load. > > The table access is via "TABLE ACCESS BY LOCAL INDEX ROWID" and then by > "INDEX SKIP SCAN" on the composite index. > > It looks like we need to provide an SQL hint > (http://ss64.com/ora/select_hints.html) that tells Oracle to avoid using an > index scan. E.g. > > SELECT /*+ NO_INDEX(<table name>) */ <columns> FROM <table owner>.<table > name> WHERE ... > > Though it would seem like OraOop should always provide this hint, as > otherwise accessing rows by id will have really bad performance, yes? > > If this isn't the case, then where should this hint support be added - in > Sqoop (and picked up by OraOop), or just in OraOop? > > -------------------------- > Ken Krugler > +1 530-210-6378 > http://bixolabs.com > custom big data solutions & training > Hadoop, Cascading, Mahout & Solr > > > -------------------------- Ken Krugler +1 530-210-6378 http://bixolabs.com custom big data solutions & training Hadoop, Cascading, Mahout & Solr
