Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Removing Oracle hints
Removing Oracle hints [message #222750] Tue, 06 March 2007 22:28 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 doesn’t 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 – didn’t 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 Go to previous messageGo to next message
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 doesn’t 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 – didn’t help.
> 2) running report in designer and runtime engine – no difference

> Thanks,
> Yevgeniy
Re: Removing Oracle hints [message #224346 is a reply to message #224246] Mon, 12 March 2007 21:12 Go to previous messageGo to next message
Linda Chan
Messages: 845
Registered: July 2009
Senior Member
This appears to be a bug in the BIRT oda.jdbc driver. It strips out comments
before passing a SQL query text to the JDBC driver:

Found in org.eclipse.birt.report.data.oda.jdbc's Statement.prepare method:
/*
* call the JDBC Connection.prepareStatement(String) method to get
* the preparedStatement
*/
this.preStat = conn.prepareStatement( SQLFormatter.formatQueryText( command )
);

Please submit a BIRT bug in Bugzilla on this issue.

In the mean time, a work-around is to add a driverBridge extension that
overrides the oda.jdbc's Statement.prepare method. A sample implementation of
driverBridge extension is in the connection pooling example that Jason has
posted (also attached in Bugzilla 135246).

Linda


Mustafa wrote:

> 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 doesn’t 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 – didn’t help.
> > 2) running report in designer and runtime engine – no difference
>
> > Thanks,
> > Yevgeniy
Re: Removing Oracle hints [message #225175 is a reply to message #224346] Wed, 14 March 2007 17:46 Go to previous message
Sunitha Kambhampati is currently offline Sunitha Kambhampati
Messages: 53
Registered: July 2009
Member
Linda Chan wrote:
> This appears to be a bug in the BIRT oda.jdbc driver. It strips out comments
> before passing a SQL query text to the JDBC driver:
>
> Found in org.eclipse.birt.report.data.oda.jdbc's Statement.prepare method:
> /*
> * call the JDBC Connection.prepareStatement(String) method to get
> * the preparedStatement
> */
> this.preStat = conn.prepareStatement( SQLFormatter.formatQueryText( command )
> );
>
> Please submit a BIRT bug in Bugzilla on this issue.
>
Thanks Linda for your comments.

I was looking at the code in the birt.report.data.oda.jdbc directory and
realized once this issue is fixed, we can also cleanup the SQLFormatter
class.

Opened a bugzilla : https://bugs.eclipse.org/bugs/show_bug.cgi?id=177381

regards,
Sunitha.
Previous Topic:BIRT 2.2M5 postscript emitter
Next Topic:resource path syntax
Goto Forum:
  


Current Time: Fri Oct 31 13:46:56 GMT 2014

Powered by FUDForum. Page generated in 0.01988 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software