Re: Anybody more familiar with "_use_old_connect_by" than I am?
- From: Carel-Jan Engel <cjpengel.dbalert@xxxxxxxxx>
- To: dreveewee@xxxxxxxxx
- Date: Mon, 06 Nov 2006 01:44:43 +0100
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
>
>
Other related posts:
