Peter Hall 2011-09-02, 00:21
Ken Krugler 2011-09-03, 23:35
-RE: [sqoop-user] Slow queries with table that has index when using OraOop
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:[EMAIL PROTECTED]]
Sent: Sunday, 4 September 2011 9:35 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqoop-user] Slow queries with table that has index when using OraOop
On Sep 1, 2011, at 5:21pm, Peter Hall wrote:
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?
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?
custom big data solutions & training
Hadoop, Cascading, Mahout & Solr
Ken Krugler 2011-09-05, 23:37