Re: Oracle vs MySql Performance

On Aug 21, 1:01 pm, Johne_uk <edg..._at_tiscali.co.uk> wrote:
> 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

You may want to switch to using DBMS_STATS.GATHER_SCHEMA_STATS. Fairly old references:
 http://orafaq.com/maillist/oracle-l/2001/06/06/0805.htm

http://www.oracle.com/technology/oramag/oracle/02-jan/o12asktom.html "DBMS_UTILITY.ANALYZE_SCHEMA is left over from Oracle7 Release 7.x. This was before the DBMS_STATS package and its functionality was introduced. I recommend that you use DBMS_STATS and stop using DBMS_ UTILITY. DBMS_UTILITY exists for backward compatibility, but DBMS_STATS is the proper package to use now."

What Oracle version are you working with, for example: 10.2.0.3, 9.2.0.1, 8.1.7.3

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Aug 21 2007 - 12:48:33 CDT