Hi,

I'm looking for a few opinions on an issue we are experiencing.

My company currently uses a 3rd party application. Up until a few
weeks ago this application consisted of Java code and a database layer
that used MySql.

Performance on MySql was adequate but as our primary DB platform is
Oracle we influenced the 3rd part to introduce an Oracle version (NB:
most of the sql code is stored in a java db layer - schema just
consists of tables, views and indexes).

To facilitate this process the vendor has used almost pure ANSI Sql so
that the same sql can be used for both oracle / mysql with minimum
conversion required.

When the system was released we were surprised to find that the sql
(and hence application) ran slower on oracle than MySql (the Oracle db
server is also considerably more powerful).

At a high level does this surprise anybody on this forum. Does Oracle
struggle to execute ANSI sql compared to MySql.

The orginal code was also written for MySql and converted so that
might explain why is is running slower.

Any thoughts would be appreciated.

Tanks in advance
John

> The orginal code was also written for MySql and converted so that
> might explain why is is running slower.
>
> Any thoughts would be appreciated.
>
> Tanks in advance
> John
>

Looks like you found one answer there!

Shakespeare

On Aug 21, 1:58 pm, Johne_uk <edg...@tiscali.co.uk> wrote:
> Hi,
>
> I'm looking for a few opinions on an issue we are experiencing.
>
> My company currently uses a 3rd party application. Up until a few
> weeks ago this application consisted of Java code and a database layer
> that used MySql.
>
> Performance on MySql was adequate but as our primary DB platform is
> Oracle we influenced the 3rd part to introduce an Oracle version (NB:
> most of the sql code is stored in a java db layer - schema just
> consists of tables, views and indexes).
>
> To facilitate this process the vendor has used almost pure ANSI Sql so
> that the same sql can be used for both oracle / mysql with minimum
> conversion required.
>
> When the system was released we were surprised to find that the sql
> (and hence application) ran slower on oracle than MySql (the Oracle db
> server is also considerably more powerful).
>
> At a high level does this surprise anybody on this forum. Does Oracle
> struggle to execute ANSI sql compared to MySql.
>
> The orginal code was also written for MySql and converted so that
> might explain why is is running slower.
>
> Any thoughts would be appreciated.
>
> Tanks in advance
> John

Oracle compared to MySQL <--> Apples to Pears.

The architecture is completely different.

'Porting' Sqlserver, or MySQL to Oracle NEVER works!!!!
If you read any book by Tom Kyte, you will know why.
Set up a database agnostic app and you will notice you don't use the
power of a database anymore, and you will primarily use it as an
expensive replacement of a system running on punch cards.

--
Sybrand Bakker
Senior Oracle DBA

"sybrandb" <sybrandb@gmail.com> schreef in bericht
news:1187699217.556380.259900@19g2000hsx.googlegro ups.com...
> On Aug 21, 1:58 pm, Johne_uk <edg...@tiscali.co.uk> wrote:
>> Hi,
>>
>> I'm looking for a few opinions on an issue we are experiencing.
>>
>> My company currently uses a 3rd party application. Up until a few
>> weeks ago this application consisted of Java code and a database layer
>> that used MySql.
>>
>> Performance on MySql was adequate but as our primary DB platform is
>> Oracle we influenced the 3rd part to introduce an Oracle version (NB:
>> most of the sql code is stored in a java db layer - schema just
>> consists of tables, views and indexes).
>>
>> To facilitate this process the vendor has used almost pure ANSI Sql so
>> that the same sql can be used for both oracle / mysql with minimum
>> conversion required.
>>
>> When the system was released we were surprised to find that the sql
>> (and hence application) ran slower on oracle than MySql (the Oracle db
>> server is also considerably more powerful).
>>
>> At a high level does this surprise anybody on this forum. Does Oracle
>> struggle to execute ANSI sql compared to MySql.
>>
>> The orginal code was also written for MySql and converted so that
>> might explain why is is running slower.
>>
>> Any thoughts would be appreciated.
>>
>> Tanks in advance
>> John
>
> Oracle compared to MySQL <--> Apples to Pears.
>
> The architecture is completely different.
>
> 'Porting' Sqlserver, or MySQL to Oracle NEVER works!!!!
> If you read any book by Tom Kyte, you will know why.
> Set up a database agnostic app and you will notice you don't use the
> power of a database anymore, and you will primarily use it as an
> expensive replacement of a system running on punch cards.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>

Still one could expect the system to run at least as fast as the punch
cards...

Advise: have an Oracle DBA to look at the server and the SQL to analyze this
problem. For example: did you run statistics on the Oracle DB? It might even
be a problem in the communication between the (java) application layer and
the database. Did you take a closer look at the indexes? Some of them might
not be optimal for Oracle. Are all foreign key relations well-defined,
indexed etc? What version of the Oracle DB are you using anyway?

Porting an application from MySql to Oracle is never optimal, but the port
could be a starting point to rebuild (parts of) your application, eg by
porting code (takes reprogramming) from your application to the database.

Shakespeare

On 21 Aug, 13:46, "Shakespeare" <what...@xs4all.nl> wrote:
> "sybrandb" <sybra...@gmail.com> schreef in berichtnews:1187699217.556380.259900@19g2000hsx.go oglegroups.com...
>
>
>
>
>
> > On Aug 21, 1:58 pm, Johne_uk <edg...@tiscali.co.uk> wrote:
> >> Hi,
>
> >> I'm looking for a few opinions on an issue we are experiencing.
>
> >> My company currently uses a 3rd party application. Up until a few
> >> weeks ago this application consisted of Java code and a database layer
> >> that used MySql.
>
> >> Performance on MySql was adequate but as our primary DB platform is
> >> Oracle we influenced the 3rd part to introduce an Oracle version (NB:
> >> most of the sql code is stored in a java db layer - schema just
> >> consists of tables, views and indexes).
>
> >> To facilitate this process the vendor has used almost pure ANSI Sql so
> >> that the same sql can be used for both oracle / mysql with minimum
> >> conversion required.
>
> >> When the system was released we were surprised to find that the sql
> >> (and hence application) ran slower on oracle than MySql (the Oracle db
> >> server is also considerably more powerful).
>
> >> At a high level does this surprise anybody on this forum. Does Oracle
> >> struggle to execute ANSI sql compared to MySql.
>
> >> The orginal code was also written for MySql and converted so that
> >> might explain why is is running slower.
>
> >> Any thoughts would be appreciated.
>
> >> Tanks in advance
> >> John
>
> > Oracle compared to MySQL <--> Apples to Pears.
>
> > The architecture is completely different.
>
> > 'Porting' Sqlserver, or MySQL to Oracle NEVER works!!!!
> > If you read any book by Tom Kyte, you will know why.
> > Set up a database agnostic app and you will notice you don't use the
> > power of a database anymore, and you will primarily use it as an
> > expensive replacement of a system running on punch cards.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Still one could expect the system to run at least as fast as the punch
> cards...
>
> Advise: have an Oracle DBA to look at the server and the SQL to analyze this
> problem. For example: did you run statistics on the Oracle DB? It might even
> be a problem in the communication between the (java) application layer and
> the database. Did you take a closer look at the indexes? Some of them might
> not be optimal for Oracle. Are all foreign key relations well-defined,
> indexed etc? What version of the Oracle DB are you using anyway?
>
> Porting an application from MySql to Oracle is never optimal, but the port
> could be a starting point to rebuild (parts of) your application, eg by
> porting code (takes reprogramming) from your application to the database.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Thanks for the prompt replies guys, guess you are really telling what
what i already know.

The phyiscal db structure (indexes etc.) was coverted as is from
Mysql. The bulk of initial performance problems were removed by
gathering stats on oracle.

I guess we need to look at the issues from two plans of attack. First,
look at optimisations for the oracle schema and second, take a look at
the java layer and its interaction with Oracle.

rgds

"Johne_uk" <edgarj@tiscali.co.uk> schreef in bericht
news:1187706614.786978.29690@d55g2000hsg.googlegro ups.com...
> On 21 Aug, 13:46, "Shakespeare" <what...@xs4all.nl> wrote:
>> "sybrandb" <sybra...@gmail.com> schreef in
>> berichtnews:1187699217.556380.259900@19g2000hsx.go oglegroups.com...
>>
>>
>>
>>
>>
>> > On Aug 21, 1:58 pm, Johne_uk <edg...@tiscali.co.uk> wrote:
>> >> Hi,
>>
>> >> I'm looking for a few opinions on an issue we are experiencing.
>>
>> >> My company currently uses a 3rd party application. Up until a few
>> >> weeks ago this application consisted of Java code and a database layer
>> >> that used MySql.
>>
>> >> Performance on MySql was adequate but as our primary DB platform is
>> >> Oracle we influenced the 3rd part to introduce an Oracle version (NB:
>> >> most of the sql code is stored in a java db layer - schema just
>> >> consists of tables, views and indexes).
>>
>> >> To facilitate this process the vendor has used almost pure ANSI Sql so
>> >> that the same sql can be used for both oracle / mysql with minimum
>> >> conversion required.
>>
>> >> When the system was released we were surprised to find that the sql
>> >> (and hence application) ran slower on oracle than MySql (the Oracle db
>> >> server is also considerably more powerful).
>>
>> >> At a high level does this surprise anybody on this forum. Does Oracle
>> >> struggle to execute ANSI sql compared to MySql.
>>
>> >> The orginal code was also written for MySql and converted so that
>> >> might explain why is is running slower.
>>
>> >> Any thoughts would be appreciated.
>>
>> >> Tanks in advance
>> >> John
>>
>> > Oracle compared to MySQL <--> Apples to Pears.
>>
>> > The architecture is completely different.
>>
>> > 'Porting' Sqlserver, or MySQL to Oracle NEVER works!!!!
>> > If you read any book by Tom Kyte, you will know why.
>> > Set up a database agnostic app and you will notice you don't use the
>> > power of a database anymore, and you will primarily use it as an
>> > expensive replacement of a system running on punch cards.
>>
>> > --
>> > Sybrand Bakker
>> > Senior Oracle DBA
>>
>> Still one could expect the system to run at least as fast as the punch
>> cards...
>>
>> Advise: have an Oracle DBA to look at the server and the SQL to analyze
>> this
>> problem. For example: did you run statistics on the Oracle DB? It might
>> even
>> be a problem in the communication between the (java) application layer
>> and
>> the database. Did you take a closer look at the indexes? Some of them
>> might
>> not be optimal for Oracle. Are all foreign key relations well-defined,
>> indexed etc? What version of the Oracle DB are you using anyway?
>>
>> Porting an application from MySql to Oracle is never optimal, but the
>> port
>> could be a starting point to rebuild (parts of) your application, eg by
>> porting code (takes reprogramming) from your application to the database.
>>
>> Shakespeare- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks for the prompt replies guys, guess you are really telling what
> what i already know.
>
> The phyiscal db structure (indexes etc.) was coverted as is from
> Mysql. The bulk of initial performance problems were removed by
> gathering stats on oracle.
>
> I guess we need to look at the issues from two plans of attack. First,
> look at optimisations for the oracle schema and second, take a look at
> the java layer and its interaction with Oracle.
>
> rgds
>
>
>

You're welcome. I hope your database did not end up with column names in
qoutes and things like that, and uses varchar2 columns and not char.... and
other stuff like that....

Plans of Attack seem ok to me...

Wish you good luck and success,

Shakespeare

"Shakespeare" <whatsin@xs4all.nl> schreef in bericht
news:46caf84b$0$240$e4fe514c@news.xs4all.nl...
>
> "Johne_uk" <edgarj@tiscali.co.uk> schreef in bericht
> news:1187706614.786978.29690@d55g2000hsg.googlegro ups.com...
>> On 21 Aug, 13:46, "Shakespeare" <what...@xs4all.nl> wrote:
>>> "sybrandb" <sybra...@gmail.com> schreef in
>>> berichtnews:1187699217.556380.259900@19g2000hsx.go oglegroups.com...
>>>
>>>
>>>
>>>
>>>
>>> > On Aug 21, 1:58 pm, Johne_uk <edg...@tiscali.co.uk> wrote:
>>> >> Hi,
>>>
>>> >> I'm looking for a few opinions on an issue we are experiencing.
>>>
>>> >> My company currently uses a 3rd party application. Up until a few
>>> >> weeks ago this application consisted of Java code and a database
>>> >> layer
>>> >> that used MySql.
>>>
>>> >> Performance on MySql was adequate but as our primary DB platform is
>>> >> Oracle we influenced the 3rd part to introduce an Oracle version (NB:
>>> >> most of the sql code is stored in a java db layer - schema just
>>> >> consists of tables, views and indexes).
>>>
>>> >> To facilitate this process the vendor has used almost pure ANSI Sql
>>> >> so
>>> >> that the same sql can be used for both oracle / mysql with minimum
>>> >> conversion required.
>>>
>>> >> When the system was released we were surprised to find that the sql
>>> >> (and hence application) ran slower on oracle than MySql (the Oracle
>>> >> db
>>> >> server is also considerably more powerful).
>>>
>>> >> At a high level does this surprise anybody on this forum. Does Oracle
>>> >> struggle to execute ANSI sql compared to MySql.
>>>
>>> >> The orginal code was also written for MySql and converted so that
>>> >> might explain why is is running slower.
>>>
>>> >> Any thoughts would be appreciated.
>>>
>>> >> Tanks in advance
>>> >> John
>>>
>>> > Oracle compared to MySQL <--> Apples to Pears.
>>>
>>> > The architecture is completely different.
>>>
>>> > 'Porting' Sqlserver, or MySQL to Oracle NEVER works!!!!
>>> > If you read any book by Tom Kyte, you will know why.
>>> > Set up a database agnostic app and you will notice you don't use the
>>> > power of a database anymore, and you will primarily use it as an
>>> > expensive replacement of a system running on punch cards.
>>>
>>> > --
>>> > Sybrand Bakker
>>> > Senior Oracle DBA
>>>
>>> Still one could expect the system to run at least as fast as the punch
>>> cards...
>>>
>>> Advise: have an Oracle DBA to look at the server and the SQL to analyze
>>> this
>>> problem. For example: did you run statistics on the Oracle DB? It might
>>> even
>>> be a problem in the communication between the (java) application layer
>>> and
>>> the database. Did you take a closer look at the indexes? Some of them
>>> might
>>> not be optimal for Oracle. Are all foreign key relations well-defined,
>>> indexed etc? What version of the Oracle DB are you using anyway?
>>>
>>> Porting an application from MySql to Oracle is never optimal, but the
>>> port
>>> could be a starting point to rebuild (parts of) your application, eg by
>>> porting code (takes reprogramming) from your application to the
>>> database.
>>>
>>> Shakespeare- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>> Thanks for the prompt replies guys, guess you are really telling what
>> what i already know.
>>
>> The phyiscal db structure (indexes etc.) was coverted as is from
>> Mysql. The bulk of initial performance problems were removed by
>> gathering stats on oracle.
>>
>> I guess we need to look at the issues from two plans of attack. First,
>> look at optimisations for the oracle schema and second, take a look at
>> the java layer and its interaction with Oracle.
>>
>> rgds
>>
>>
>>
>
> You're welcome. I hope your database did not end up with column names in
> qoutes and things like that, and uses varchar2 columns and not char....
> and other stuff like that....
>
> Plans of Attack seem ok to me...
>
> Wish you good luck and success,
>
> Shakespeare
>

One more hint:
contact your supplier and see if they ever had specific performance problems
with MySQL, and optimized their code for it. They should remove these
optimizations from their code. Start with cleaned code!

Shakespeare.

On 21 Aug, 15:35, "Shakespeare" <what...@xs4all.nl> wrote:
> "Johne_uk" <edg...@tiscali.co.uk> schreef in berichtnews:1187706614.786978.29690@d55g2000hsg.go oglegroups.com...
>
>
>
>
>
> > On 21 Aug, 13:46, "Shakespeare" <what...@xs4all.nl> wrote:
> >> "sybrandb" <sybra...@gmail.com> schreef in
> >> berichtnews:1187699217.556380.259900@19g2000hsx.go oglegroups.com...
>
> >> > On Aug 21, 1:58 pm, Johne_uk <edg...@tiscali.co.uk> wrote:
> >> >> Hi,
>
> >> >> I'm looking for a few opinions on an issue we are experiencing.
>
> >> >> My company currently uses a 3rd party application. Up until a few
> >> >> weeks ago this application consisted of Java code and a database layer
> >> >> that used MySql.
>
> >> >> Performance on MySql was adequate but as our primary DB platform is
> >> >> Oracle we influenced the 3rd part to introduce an Oracle version (NB:
> >> >> most of the sql code is stored in a java db layer - schema just
> >> >> consists of tables, views and indexes).
>
> >> >> To facilitate this process the vendor has used almost pure ANSI Sql so
> >> >> that the same sql can be used for both oracle / mysql with minimum
> >> >> conversion required.
>
> >> >> When the system was released we were surprised to find that the sql
> >> >> (and hence application) ran slower on oracle than MySql (the Oracle db
> >> >> server is also considerably more powerful).
>
> >> >> At a high level does this surprise anybody on this forum. Does Oracle
> >> >> struggle to execute ANSI sql compared to MySql.
>
> >> >> The orginal code was also written for MySql and converted so that
> >> >> might explain why is is running slower.
>
> >> >> Any thoughts would be appreciated.
>
> >> >> Tanks in advance
> >> >> John
>
> >> > Oracle compared to MySQL <--> Apples to Pears.
>
> >> > The architecture is completely different.
>
> >> > 'Porting' Sqlserver, or MySQL to Oracle NEVER works!!!!
> >> > If you read any book by Tom Kyte, you will know why.
> >> > Set up a database agnostic app and you will notice you don't use the
> >> > power of a database anymore, and you will primarily use it as an
> >> > expensive replacement of a system running on punch cards.
>
> >> > --
> >> > Sybrand Bakker
> >> > Senior Oracle DBA
>
> >> Still one could expect the system to run at least as fast as the punch
> >> cards...
>
> >> Advise: have an Oracle DBA to look at the server and the SQL to analyze
> >> this
> >> problem. For example: did you run statistics on the Oracle DB? It might
> >> even
> >> be a problem in the communication between the (java) application layer
> >> and
> >> the database. Did you take a closer look at the indexes? Some of them
> >> might
> >> not be optimal for Oracle. Are all foreign key relations well-defined,
> >> indexed etc? What version of the Oracle DB are you using anyway?
>
> >> Porting an application from MySql to Oracle is never optimal, but the
> >> port
> >> could be a starting point to rebuild (parts of) your application, eg by
> >> porting code (takes reprogramming) from your application to the database.
>
> >> Shakespeare- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks for the prompt replies guys, guess you are really telling what
> > what i already know.
>
> > The phyiscal db structure (indexes etc.) was coverted as is from
> > Mysql. The bulk of initial performance problems were removed by
> > gathering stats on oracle.
>
> > I guess we need to look at the issues from two plans of attack. First,
> > look at optimisations for the oracle schema and second, take a look at
> > the java layer and its interaction with Oracle.
>
> > rgds
>
> You're welcome. I hope your database did not end up with column names in
> qoutes and things like that, and uses varchar2 columns and not char.... and
> other stuff like that....
>
> Plans of Attack seem ok to me...
>
> Wish you good luck and success,
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Thankfully the Orcale schema is pretty much standard fayre with
Varchar2 etc and no quoted columns etc. Good point about the Mysql
optimisations - I'll follow this up. The main problem is getting them
to admit there are issues with the application sql code (I have
captured some code and its is often poor quality).

Onwards and upwards :-)

On Aug 21, 7:58 am, Johne_uk <edg...@tiscali.co.uk> wrote:
> Hi,
>
> I'm looking for a few opinions on an issue we are experiencing.
>
> My company currently uses a 3rd party application. Up until a few
> weeks ago this application consisted of Java code and a database layer
> that used MySql.
>
> Performance on MySql was adequate but as our primary DB platform is
> Oracle we influenced the 3rd part to introduce an Oracle version (NB:
> most of the sql code is stored in a java db layer - schema just
> consists of tables, views and indexes).
>
> To facilitate this process the vendor has used almost pure ANSI Sql so
> that the same sql can be used for both oracle / mysql with minimum
> conversion required.
>
> When the system was released we were surprised to find that the sql
> (and hence application) ran slower on oracle than MySql (the Oracle db
> server is also considerably more powerful).
>
> At a high level does this surprise anybody on this forum. Does Oracle
> struggle to execute ANSI sql compared to MySql.
>
> The orginal code was also written for MySql and converted so that
> might explain why is is running slower.
>
> Any thoughts would be appreciated.
>
> Tanks in advance
> John

Capture a 10046 trace at level 12 of the session that is used by the
application. Look at the wait events in the raw trace file for clues
as to what may need to be adjusted. Look at the row source operations
in the trace file to make certain that indexes are used when
appropriate. It could be that the instance is not properly tuned for
the application. Cary Millsap's book will help with the
interpretation of the 10046 trace file.

Also, make certain that statistics were gathered for tables AND
indexes. In SQLPlus, that can be accomplished with a command that
looks like this:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=> 'SCHEMA_OWNER_HERE',
CASCADE=> TRUE);

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On 21 Aug, 17:34, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On Aug 21, 7:58 am, Johne_uk <edg...@tiscali.co.uk> wrote:
>
>
>
>
>
> > Hi,
>
> > I'm looking for a few opinions on an issue we are experiencing.
>
> > My company currently uses a 3rd party application. Up until a few
> > weeks ago this application consisted of Java code and a database layer
> > that used MySql.
>
> > Performance on MySql was adequate but as our primary DB platform is
> > Oracle we influenced the 3rd part to introduce an Oracle version (NB:
> > most of the sql code is stored in a java db layer - schema just
> > consists of tables, views and indexes).
>
> > To facilitate this process the vendor has used almost pure ANSI Sql so
> > that the same sql can be used for both oracle / mysql with minimum
> > conversion required.
>
> > When the system was released we were surprised to find that the sql
> > (and hence application) ran slower on oracle than MySql (the Oracle db
> > server is also considerably more powerful).
>
> > At a high level does this surprise anybody on this forum. Does Oracle
> > struggle to execute ANSI sql compared to MySql.
>
> > The orginal code was also written for MySql and converted so that
> > might explain why is is running slower.
>
> > Any thoughts would be appreciated.
>
> > Tanks in advance
> > John
>
> Capture a 10046 trace at level 12 of the session that is used by the
> application. Look at the wait events in the raw trace file for clues
> as to what may need to be adjusted. Look at the row source operations
> in the trace file to make certain that indexes are used when
> appropriate. It could be that the instance is not properly tuned for
> the application. Cary Millsap's book will help with the
> interpretation of the 10046 trace file.
>
> Also, make certain that statistics were gathered for tables AND
> indexes. In SQLPlus, that can be accomplished with a command that
> looks like this:
> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=> 'SCHEMA_OWNER_HERE',
> CASCADE=> TRUE);
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Thanks Charles

Yes I've made sure stats were gathered at both table / index level
(using code below).

begin
dbms_utility.analyze_schema('SCHEMA','COMPUTE');
end;

I'm going to start digging into the schema at a lower level as you
suggest with 10046 trace. At this stage I've limited digging to
explain plans and indexes do appear to be used in "most" cases.

regards
John