RE: Risk of RULE mode in 10g+

From: Rich Jesse <>
Date: Mon, 15 Mar 2010 14:43:22 -0500 (CDT)
Message-ID: <>

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 instances (with COMPATIBILITY set the same), this statement explains and runs *much* better with RULE:

        SELECT /*+ rule */
        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 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! :)


Received on Mon Mar 15 2010 - 14:43:22 CDT

Original text of this message