Re: Anybody more familiar with "_use_old_connect_by" than I am?

The typo explains why there was not a lot of info on Metalink :-. But now I know how to spell it, and searching for "_old_connect_by_enabled" you can see things like :

Bug 5400311 CBO: Some connect by queries that the use old connect by (_old_connect_by_enabled has been set to true), can receive unexpected results and / or errors (such as ORA-1436).

this bug is listed (Note:342443.1) as fixed in 10.2.0.2 patch 8

This was the reason for asking other people's experiences.:

Regards,
Andre

2006/11/6, Andre van Winssen <dreveewee_at_gmail.com>:
>
> Hi Carel-Jan,
>
> as pointed out by Fairlie I made a typo (copied it from Metalink SR). The
> parameter I used is called "_OLD_CONNECT_BY_ENABLED". Try that one
>
> Rgds,
> Andre
>
>
> 2006/11/6, Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl>:
> >
> > Hi Andre,
> >
> > I'm in the middle of a 'connect by' battle too. My CT is using
> > 10.1.0.3.0. I tested it on my laptop on 10.2.0.1.0 with identical
> > (problematic) results. A SQL statement like:
> >
> > SELECT CONT_NO FROM CONTAINER WHERE PARENT_CONT_NO IS NULL START WITH
> > ROWID = CHARTOROWID('AAACkPAAIAAAAOXAAY') CONNECT BY PRIOR PARENT_CONT_NO =
> > CONT_NO;
> >
> > ends up with FTS, whilst replacing the ROWID =
> > CHARTOROWID('AAACkPAAIAAAAOXAAY') by CONT_NO = 100115121 made it work
> > fast. It went down from 1011 to 9 consistent gets. Of course it is strange
> > that accessing the starting row through PK leads to a faster result than
> > accessing through rowid. Christian Antognini was able to reproduce it on
> > 10.2.0.2.0, and there it worked correctly. He could reproduce on
> > 10.1.0.4.0.
> >
> > Your post triggered me to try _use_old_connect_by on my 10.2.0.1.0database. Alas, the parameter was unsupported. We will probably decide to
> > rewrite the statements (have the vendor rewriting the statements in the
> > app).
> >
> > Best regards,
> >
> > Carel-Jan Engel
> >
> > ===
> > If you think education is expensive, try ignorance. (Derek Bok)
> > ===
> > On Sun, 2006-11-05 at 23:24 +0100, Dree VeeWee wrote:
> >
> > Hello,
> >
> >
> >
> > anybody have used the setting "_use_old_connect_by" ?
> > We had to use it (instance level) in our migrated 9.2.0.7 to 10.2.0.2database (win32) because certain hierarchical queries were chewing up all
> > the cpu. After we set "_use_old_connect_by" to TRUE, cpu usage went back to
> > normal. This suggestion was made by the metalink support analyst as one of
> > many attempts to fix the cpu problem which almost brought down the oracle
> > server.
> > Appearantly it influences the execution of connect-by queries in 10g.
> > But since Metalink doesn't have much on this setting I am not sure which
> > unwanted side effects to expect (eg, wrong resultsets, influence on
> > other type of queries, other bugs)
> >
> >
> >
> > Kind regards,
> >
> > Andre v Winssen
> >
> >
> >
> >
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l