Eclipse Community Forums: BIRT » Removing Oracle hints

Help

Home

Home » Archived » BIRT » Removing Oracle hints

Show: Today's Messages :: Show Polls :: Message Navigator

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

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

Re: Removing Oracle hints [message #224346 is a reply to message #224246]

Mon, 12 March 2007 21:12

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 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

Report message to a moderator

Re: Removing Oracle hints [message #225175 is a reply to message #224346]

Wed, 14 March 2007 17:46

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.

Report message to a moderator

Previous Topic:	BIRT 2.2M5 postscript emitter
Next Topic:	resource path syntax

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 15:29:16 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter