Re: Straight SQL always put perform PL/SQL?

On Dec 10, 3:51 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 10, 2:11 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> > Antiquated utility to generate statistics; use
> > dbms_stats.gather_table_stats instead. And cascade that to the
> > indexes; your statistics don't include the index stats as you'll also
> > need to run analyze index to get those generated.
>
> It might be helpful for the OP to look at a DBMS_XPLAN to determine
> the step that is taking the longest in the single UPDATE SQL
> statement. As you pointed out, the OP is not analyzing the index.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

A small correction to my previous closing comment. It was pointed out to me in a private email that Oracle does analyze a table's indexes when ANALYZE TABLE is used. The notes that I have collected, as well as the Oracle documentation confirm that this is the default behavior of ANALYZE TABLE. Paraphrased from "Cost-Based Oracle Fundamentals" - there is also an Appendix section in the book that discusses statistics collection differences, but I don't have the book with me at the moment:
"GATHER_TABLE_STATS by default does not collect index stats, while the default ANALYZE command does collect index stats."

There have been a couple other write-ups by Jonathan Lewis, as well as others that indicate that the statistics collected by the two methods are different. See last post here:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/417430d64dbea278/a6fb8d17f20144d1 http://www.jlcomp.demon.co.uk/faq/dbms_or_analyze.html

There is a suggestion on this page that Oracle's Cost Based optimizer will eventually only use the statistics produced by DBMS_STATS: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/general002.htm

"Do not use the COMPUTE and ESTIMATE clauses of ANALYZE statement to collect optimizer statistics. These clauses are supported solely for backward compatibility and may be removed in a future release. The DBMS_STATS package collects a broader, more accurate set of statistics, and gathers statistics more efficiently." http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm

The question probably should have been asked, were the correct, accurate statistics gathered by the OP's ANALYZE command to allow Oracle to use the index access? Or was the index actually used, but there were also two full table scans that the OP saw and assumed that Oracle only used full table scans?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Dec 10 2007 - 20:16:01 CST