RULE Hint boost performance ...

[htanga]

Hi everyone !

I have a question about the RULE Hint.
One procedure does an "INSERT ... (SELECT ...)" in one table.
The select is really "heavy" (lots of tables, lots of joins).
The statistics are collected everynight one the whole schema.
The insert takes almost 2 minutes.

I put the hint RULE in the SELECT :

INSERT INTO ... (SELECT /*+ RULE */ ...)

And the insert takes just 3 seconds !
How could you explain that the rule based optimiser is more efficient than the cost based optimiser with fresh statistics ?
Thanks a lot in advance for your ideas ...

[marist89]

Sometimes the CBO makes assumptions about your data that aren't true.

[TimHall]

Make sure your stats are up to date.

Compare the explain plans of the CHOOSE and RULE options. Use hints to make the execution plans the same without using the RULE hint. The RBO will not be with us forever so you better get used to not using it.

Cheers

[htanga]

Thanks for you advices !

[gandolf989]

Originally posted by TimHall
Make sure your stats are up to date.

Compare the explain plans of the CHOOSE and RULE options. Use hints to make the execution plans the same without using the RULE hint. The RBO will not be with us forever so you better get used to not using it.

Cheers

I think before Oracle stops supporting FBO they should get CBO working properly on the Solaris platform. I haven't been able to get CBO working on any of the Solaris machines that I have worked with. i.e. Oracle 8.1.7.3 on Solaris 8.

[TimHall]

Wow! I've had no major problem on Solaris, HP-UX, NT or W2K so long as I don't gather stats for the SYS schema.

Cheers

[gandolf989]

Originally posted by TimHall
Wow! I've had no major problem on Solaris, HP-UX, NT or W2K so long as I don't gather stats for the SYS schema.

Cheers

I'll try that. Thanks.

[htanga]

Do the stats on SYS schema have impact on the behavior of CBO ?
Because the stats are gathered every night by the dbms_utility.analyze_database procedure on our databases !
So, could the stats on SYS schema bother queries on tables in others schemas ?

[marist89]

Originally posted by htanga
Do the stats on SYS schema have impact on the behavior of CBO ?
Because the stats are gathered every night by the dbms_utility.analyze_database procedure on our databases !
So, could the stats on SYS schema bother queries on tables in others schemas ?

I've seen it both ways. Until recently, I always have observed queries against the Data Dictionary are faster when the SYS tables were not analyzed. However, I recently saw a white paper (although I can't put my browser on it) stating that the SYS schema can and SHOULD be analyzed with late 8i and 9i databases. I ran some quick tests on a 9.2 database and didn't see a difference either way. My opinion is still mixed...

[TimHall]

I never use dbms_utility.analyze_database or dbms_stats.gather_database_stats. Every time Ihave my server has run like a complete dog. If I've subsequently deleted the stats from the SYS schema (using dbms_stats) all has been fine. I would suggest changing your nightly script to run dbms_utility.analyze_schema or dbms_stats.gather_schema_stats for all relevant schemas.

You should consider switching to dbms_stats since this is the current Oracle recommendation.

Since Oracle claim they will get rid of the RBO in future releases analysing the SYS schema should become possible without problems in the near future.

Cheers

PS. Just seen marist89's note. I'll have to give it a go on some 8.1.7.4 instances and see. I've already seen some problems on a 9.0.1 instance, but not tried on 9.2 yet.

[Edited by TimHall on 08-28-2002 at 08:35 AM]