RE: Risk of RULE mode in 10g+

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Mon, 15 Mar 2010 14:43:22 -0500 (CDT)
Message-ID: <59f1d9f053115fd1d04c64de76e021c7.squirrel_at_society.servebeer.com>



Hey Dick,

> OH Dear, I'll try to find the url that says this for you, but in 10g
> the rule based optimizer isn't deprecated, it's dead period. You can
> put rule in there all you want, but it will run in cost mode. Isn't
> suppose to cause problems with the result set that I've heard of, nor
> does it cause a problem with executing the sql.

On my 10.1.0.5.0 instances (with COMPATIBILITY set the same), this statement explains and runs *much* better with RULE:

        SELECT /*+ rule */
                COUNT(*)
        FROM v$rman_status
        WHERE row_type = 'COMMAND'
                AND operation = 'BACKUP'
                AND status = 'COMPLETED'
                AND start_time >= SYSDATE-1/24
                AND command_id LIKE '%ARCH%'
                AND sid = 0;


On my sole 10.2.0.3.0 instance, I don't perceive a difference although autotrace consistently shows 13 memory sorts when using RULE versus only 1 without, giving empirical evidence that RULE is still (barely?) alive in 10g.

I'd be interested to see that article! :)

Rich

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 15 2010 - 14:43:22 CDT

Original text of this message