Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Anybody more familiar with "_use_old_connect_by" than I am?

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

From: Carel-Jan Engel <>
Date: Mon, 06 Nov 2006 01:44:43 +0100
Message-Id: <>

Hi Andre,

I'm in the middle of a 'connect by' battle too. My CT is using I tested it on my laptop on 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, and there it worked correctly. He could reproduce on

Your post triggered me to try _use_old_connect_by on my database. 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 to
> database (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

Received on Sun Nov 05 2006 - 18:44:43 CST

Original text of this message