Oracle-L: Re: Oracle position on hints

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 10 Mar 2003 14:21:16 -0800
Message-ID: <F001.00565603.20030310142116@fatcity.com>

Star transformations still seem to be disabled by bind variables in predicates against the dimension tables, even in 9.2. (I have an idea that materialized views have the same problem. but I haven't tested that).

Also, bind variable peeking isn't much help with histograms as the peek only takes
on the first (hard) parse - then the same execution plan is used for all subsequent executions, even if the bound values merit a completely different plan.

By the way, generating histograms isn't entirely pointless when using bind variables, as the column density is adjusted to a certain degree to factor out some of the impact of recognised popular values.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 10 March 2003 21:39

I thought that went out the window with 9i because it can snoop at the bind
variables??

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI

USA -----Original Message-----
Sent: Monday, March 10, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L

Well, I wouldn't say you should *always* use bind variables.

Many situations in a database warehouse preclude that.

Potentially long running queries may need to have literals to help the CBO make the right choice.

Star transforms don't work with bind variables, and histograms can't be used with bind variables.

Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Mar 10 2003 - 16:21:16 CST