Thursday, January 14, 2010

Interesting Optimizer Result, Part II

My post on a tacky query I did to solve a problem garnered lots of comments.

I can't disagree with Gary Myers who said in the comments that this wasn't a straightforward solution. It's not that I didn't have reservations about implementing a GROUP BY that did nothing.  In fact, when I had to explain it to two different developers I kind of knew I went down the wrong path.

Some of the alternate workarounds and their results were:
1. Gary and Anonymous suggested that I duplicate the business logic of some_funky_package_call in the query itself. Good thought, but because some_funky_package_call is used in multiple places, I wouldn't want to have to go back and fix multiple queries when the underlying logic of some_funky_package_call changed.  In circumstances where some_funky_package_call was only used for this query, that method would work.

2. Craig Martin had two suggestions for me. The first one I tried was using the +MATERIALIZE hint.  This seemed like the easiest to implement, but resulted in an error I've never seen:

ERROR at line 6:
ORA-12840: cannot access a remote table after parallel/insert direct load txn
ORA-06512: at "MYUSER.SOME_FUNKY_PACKAGE_CALL", line 24
ORA-06512: at line 1

Oh well, the easiest solution isn't necessarily the best anyway.

3. Then I tried Craig's second solution; use ROWNUM to force Oracle to evaluate the inline view first. This actually worked very well.  Both the trace and the elapsed time were at least twice as good as my GROUP BY query.

So a big THANK YOU to Gary, Craig, and Anonymous for helping me out.  I now have a more straightforward solution that works faster than my original "fix".

3 comments:

Craig Martin said...

Glad I could help. I don't really know what the ORA-12840 is all about. I guess that is what I deserve for suggesting undocumented hints. You should thank Tom Kyte for the rownum trick as I am pretty sure I picked that up from him at a conference a few years back.

Anonymous said...

It's me Anonymous !
The solution Craig proposed is quite neat, I'll add it to my toolbox.

You're right when you say that " I wouldn't want to have to go back and fix multiple queries when the underlying logic of some_funky_package_call changed". That's why I said my solution is good when you only look at the performance problem and forgot about maintenance and such.

By the way, I just found your blog and added it to my blog list. Hopefully, I'll see nice blogging from you in the near futur !

Anonymous said...

Oh and I forgot. I don't use the materialize hint that much, I found some strange stuff happening with that hint, especially with parallele execution.

Instead, I use the /*+ NO_MERGE (alias_of_inline_view) */.

I.E.

SELECT /*+ NO_MERGE (bal) */ id, description, bal
FROM (
SELECT
a.id,
b.description,
some_funky_package_call(a.id, b.description) bal
FROM
tableA a,
tableB b
WHERE a.id = b.id
)
WHERE bal <> 0