Re: Straight SQL always put perform PL/SQL?

On Dec 10, 3:51 pm, Charles Hooper <hooperc2...@xxxxxxxxx> wrote:
On Dec 10, 2:11 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> 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.
.

Relevant Pages

  • Re: Oracle managed statistics
    ... I have a vendor who is nervous about the Oracle managed statistics ... Change the frequency/agressiveness of the auto analyze. ... Oracle 10.2.0.3 on Red Hat 3. ...
    (comp.databases.oracle.server)
  • Re: good/bad statistics
    ... Instead of using indexes the optimizer chose full table scans. ... When looking at the statistics I thought that the optimizer should ... The delete statistics with the analyze command may not be necessary ...
    (comp.databases.oracle.server)
  • Re: good/bad statistics
    ... Instead of using indexes the optimizer chose full table scans. ... When looking at the statistics I thought that the optimizer should ... The delete statistics with the analyze command may not be necessary ...
    (comp.databases.oracle.server)
  • Re: good/bad statistics
    ... Instead of using indexes the optimizer chose full table scans. ... When looking at the statistics I thought that the optimizer should ... The delete statistics with the analyze command may not be necessary ...
    (comp.databases.oracle.server)
  • Re: Why index Scan in this case ?
    ... analyze table t compute statistics for table for all tables for all ... Whey you tried with histograms it pick the full table scan. ... Oracle knows the distinct no of columns - Index Stats ...
    (comp.databases.oracle.server)