Re: Why are optimizer hints required?

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 7 Mar 2005 17:26:14 -0800
Message-ID: <1110245174.125336.148570@o13g2000cwo.googlegroups.com>

The House Dawg wrote:

snip

> The DBA's
> calculate statistics every weekend using a 20% sample size on all
> tables in the schema

This strategy is probably the cause of the problem. ( It will work for some applications, not all. DBA's sometimes get in the habit of trying to enforce a standard that works for most everything. The gather everything at 20 percent was kind of a defacto standard several years or more ago but has been superceded in many shops (my opinion)).

You can set MONITORING on for a table (or all tables in a schema). and then use DBMS_STATS package to do a GATHER STALE.

Look at the ask tom site and look at hits for GATHER STALE and GATHER AUTO as well as other relevant keywords. Received on Mon Mar 07 2005 - 19:26:14 CST