Rule is dead, long live Rule

A long day tuning SQL queries using Siebel 7.8 and Oracle 10gR2…

We used the Siebel recommended settings (TechNote 582). We used the Oracle recommended settings.

We gathered table statistics.

We gathered index statistics.

We gathered column histograms.

We dropped statistics on empty tables (Alert 1162).

We set some miscellaneous (magic) underscore parameters to encourage CBO to use the correct index.

We poured over 10053 trace files.

We used a 15 year old, deprecated, desupported optimizer technology to reduce a complex 27 table (outer) join query with a subquery to subsecond from an hourglass.

We opened an SR with Oracle Technical Support.

We opened an SR with Siebel Technical Support.

We used a stored outline.

We went home.