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
