Re: Oracle vs MySql Performance

On 21 Aug, 17:34, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Aug 21, 7:58 am, Johne_uk <edg..._at_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 Received on Tue Aug 21 2007 - 12:01:15 CDT