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,
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-lReceived on Mon Mar 15 2010 - 14:43:22 CDT