sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ken Krugler <kkrugler_li...@transpac.com>
Subject Re: [sqoop-user] Slow queries with table that has index when using OraOop
Date Sat, 03 Sep 2011 23:35:28 GMT
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




Mime
View raw message