Re: Performace problem after upgraded from 8.0.5 to 8.1.7

"Burt Peltier" <burtpelt_at_bellsouth.net> wrote in message news:<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

If you are not using rule based optimization analyze all those tables and check out the time again. Received on Mon Apr 01 2002 - 18:04:12 CEST