Re: Performace problem after upgraded from 8.0.5 to 8.1.7

From: Burt Peltier <burtpelt_at_bellsouth.net>
Date: Thu, 28 Mar 2002 23:51:52 -0600
Message-ID: <mXSo8.36861$Q7.23045093_at_e3500-atl1.usenetserver.com>


Just a couple of questions come to mind ...

Have you run the explain plan utility ? Is this query running rule-based or cost-based (explain plan would tell you)?

Did you upgrade to 8i by doing the migrate or doing export/import ?

"Sam" <hinfun_at_hotmail.com> wrote in message news:e53193a9.0203272000.3cb7bbf3_at_posting.google.com...
> I hv 4 tables (created by the vendor) that returns estimated 170,000
> rows.
>
> Table A
> --------
> product_id numeric(9), (pk)
> parentproductid numeric(9),
> description varchar2(128),
> makerid numeric(9), (fk to table B)
> unitid numeric(9) (fk to table c)
>
> Table B
> -------
> vendorid numeric(9), (pk)
> addr varchar2(128),
>
>
> Table C
> -------
> unitid numeric(9), (pk)
> description varchar2(50)
>
> select a.product_id,
> a.parentproductid,
> d.description,
> a.description,
> a.makerid,
> a.unit,
> b.addr,
> c.description
> from tablea a, tableb b, tablec c, tablea d
> where a.parentproductid = d.product_id (+)
> and a.makerid = b.vendorid (+)
> and a.unitid = c.unitid (+)
> order by a.product_id ;
>
> It used to be 40sec to return all sets in Oracle 8.0.5 but after
> upgraded it took 1min 15 sec to complete.
> We used PowerBuilder 7 Native Driver as ODBC are even slower.
>
> Thanks
>
> Sam
>
>
> "Jon Armstrong" <jma00002_at_rochester.rr.com> wrote in message
 news:<3Cro8.27847$8%1.10716797_at_typhoon.nyroc.rr.com>...
> > Sam,
> >
> > Details of the query in question and DDL (schema description)
> > would be helpful, along with some indication of the volume of
> > data involved.
> >
> > Regards... Jon
> >
> > Jon Armstrong (Java, C, C++, Oracle, MySQL and much more)
> > [ 24/7 Solutions - Any problem, on any platform, any time. ]
> >
> > "Sam" <hinfun_at_hotmail.com> wrote in message
> > news:e53193a9.0203270529.ccce008_at_posting.google.com...
> > > I have a performance problem that used to be 40 sec now comes to 90
> > > sec after upgraded to 8i. Generally i have 1 table that outer join to
> > > other four tables that is giving the performance penalty and i have
> > > noded that transferring of rows from server to client is slower on
> > > 8.1.7
> > >
> > > The hardware platforms is the same except I hv allocated lots of
> > > memory to 8i and still plenty for the OS (NT 4 SP6).
> > >
> > > Is there any tricks in tunning up the server ?
> > >
> > > Thanks
> > >
> > > Sam
Received on Fri Mar 29 2002 - 06:51:52 CET

Original text of this message