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 Mon, 05 Sep 2011 23:37:35 GMT
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_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.
> 
> 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




Mime
View raw message