Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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 <cjpengel.dbalert_at_xs4all.nl>
Date: Mon, 06 Nov 2006 01:44:43 +0100
Message-Id: <1162773883.10699.109.camel@dbalert199.dbalert.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.0 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 9.2.0.7 to 10.2.0.2
> 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
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Nov 05 2006 - 18:44:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US