Removing Oracle hints [message #222750] |
Tue, 06 March 2007 22:28 |
Eclipse User |
|
|
|
Originally posted by: ybarkalov.yahoo.com
I'm developing report and using a lot of Oracle hints like MATERIALIZE,
NO_MERGE and USE_HASH() for performance reasons. And it seems that BIRT
strips out ALL the hints when executing this query. At least Oracle
Enterpise Manager shows SQL without any hits at all. And execution plan
that it chooses also matches to the query with hits striped out. And with
all the hints gone, the query never completes with execution path chosen
by Oracle
The same query runs fast from SQL Plus and other client tools. The
execution plan is exactly as hints define it. And the execution plan is
different from the one shown in Enterpise Mananger for BIRT session.
Is this the standard behavoir for BIRT to remove hints? Is there a way to
leave hits in the query?
Thanks,
Yevgeniy
|
|
|
Re: Removing Oracle hints [message #223043 is a reply to message #222750] |
Thu, 08 March 2007 01:27 |
Eclipse User |
|
|
|
Originally posted by: jasonweathersby.alltel.net
Yevgeniy wrote:
> I'm developing report and using a lot of Oracle hints like MATERIALIZE,
> NO_MERGE and USE_HASH() for performance reasons. And it seems that BIRT
> strips out ALL the hints when executing this query. At least Oracle
> Enterpise Manager shows SQL without any hits at all. And execution plan
> that it chooses also matches to the query with hits striped out. And
> with all the hints gone, the query never completes with execution path
> chosen by Oracle
>
> The same query runs fast from SQL Plus and other client tools. The
> execution plan is exactly as hints define it. And the execution plan is
> different from the one shown in Enterpise Mananger for BIRT session.
>
> Is this the standard behavoir for BIRT to remove hints? Is there a way
> to leave hits in the query?
>
> Thanks,
> Yevgeniy
>
>
>
Can you set you log level to FINEST and run the report again.
Look for QueryText in the log and verify it is the proper query.
Jason
|
|
|
Re: Removing Oracle hints [message #223711 is a reply to message #223043] |
Fri, 09 March 2007 19:04 |
Eclipse User |
|
|
|
Originally posted by: ybarkalov.yahoo.com
Hello Jason,
Thanks for the reply. I set logging to FINEST and can see that SQL in Birt
log has hints. But the SQL executed by Oracle doesn't have. Comments in
SQL are gone as well. I think the problem comes from the fact that Oracle
hints have a format similar to comments, for example:
/*+ NO_MERGE */ is a hint
/* NO_MERGE */ is a comment
For some reason, something in the process strips out SQL block comments
(/*...*/) and it doesnt distinguish between comments and Oracle hints. It
seems that the issue could be in DTP module and our programmers are now
looking through the source code.
Do you know about the conditions in which the comments are removed?
I also tried the following things:
1) re-typing the entire SQL in the text box in Birt thinking that some
unrecognized invisible symbol would cause this behavior didnt help.
2) running report in designer and runtime engine no difference
Thanks,
Yevgeniy
|
|
|
Re: Removing Oracle hints [message #224246 is a reply to message #223711] |
Mon, 12 March 2007 18:06 |
Eclipse User |
|
|
|
Originally posted by: test.tes3456t.com
Could someone please give any additional pointer on this ? We are
currently stuck due to this. Any suggestion/workaround is highly
appreciated.
Mustafa
Yevgeniy wrote:
> Hello Jason,
> Thanks for the reply. I set logging to FINEST and can see that SQL in Birt
> log has hints. But the SQL executed by Oracle doesn't have. Comments in
> SQL are gone as well. I think the problem comes from the fact that Oracle
> hints have a format similar to comments, for example:
> /*+ NO_MERGE */ is a hint
> /* NO_MERGE */ is a comment
> For some reason, something in the process strips out SQL block comments
> (/*...*/) and it doesnt distinguish between comments and Oracle hints. It
> seems that the issue could be in DTP module and our programmers are now
> looking through the source code.
> Do you know about the conditions in which the comments are removed?
> I also tried the following things:
> 1) re-typing the entire SQL in the text box in Birt thinking that some
> unrecognized invisible symbol would cause this behavior didnt help.
> 2) running report in designer and runtime engine no difference
> Thanks,
> Yevgeniy
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.03748 seconds