RE: Is important to GATHER_SYSTEM_STATS???

System stats give the CBO information about how fast your CPUs are, how long single and multi-block reads take, and what the disk throughput is. It can make a very big difference in the execution plans chosen (usually for the better).

In a recent case, I was able to smooth the transition from nested loops to hash joins by setting system stats to appropriate values. Prior to this, the CBO would switch from NL to hash joins much too early, resulting in a big discontinuity in the response time curve for certain queries as the number of rows processed increased.

Be sure to gather the stats during a load that represents the target state of your system. You can also set them manually to anything you like.

John Smiley

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes Pacheco
Sent: Friday, October 22, 2004 4:11 PM
To: oracle-l_at_freelists.org
Subject: Is important to GATHER_SYSTEM_STATS???

 Hi,
1) does you have some experience about using it, and its impact in CBO. 2) Do you get some benefit gathering it as normal gathering statistics process,
even when your database always is dss or always oltp

Thank you  

Juan Carlos Reyes Pacheco
OCP
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 22 2004 - 17:26:40 CDT