sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Robson <David.Rob...@quest.com>
Subject RE: [sqoop-user] Slow queries with table that has index when using OraOop
Date Mon, 05 Sep 2011 00:27:34 GMT
Hi Ken,

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? 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.

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.



From: Ken Krugler [mailto:kkrugler_lists@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.

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.


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?


-- 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
custom big data solutions & training
Hadoop, Cascading, Mahout & Solr

View raw message