RULE Hint boost performance ...
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: RULE Hint boost performance ...

  1. #1
    Join Date
    Dec 2001
    Posts
    141

    Talking

    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 ...

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sometimes the CBO makes assumptions about your data that aren't true.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Dec 2001
    Posts
    141
    Thanks for you advices !

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

  8. #8
    Join Date
    Dec 2001
    Posts
    141

    Question

    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 ?




  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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]
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width