• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1548
  • Last Modified:

DBMS_STATS

I have scheduled a DBMS_JOB that runs DBMS_STATS on a particular schema in Oracle.  I set it to run every day at 2am (trunc(sysdate)+1+2/24).  I noticed, however, that after a run has completed, my indexes list an "last analyzed" that suggests that the dbms_stats run did not hit them.  Am I doing something wrong?
0
rnicholus
Asked:
rnicholus
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
jwittenmCommented:
What is the syntax of your dbms_stats command?  Did you include 'FOR ALL INDEXES' or 'FOR ALL INDEXED COLUMNS'?
0
 
slightwv (䄆 Netminder) Commented:
What subprogram of dbms_stats are you using?

Please post the dbms_stats call.
0
 
catchmeifuwantCommented:
Do you have cascade option enabled for the table?

------- For gather_Table_Stats
cascade
 Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS procedure on each of the table's indexes.
 ---------
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
catchmeifuwantCommented:
If you are using Gather_Schema_Stats, cascade option holds good too.
0
 
rnicholusAuthor Commented:
SET SERVEROUTPUT ON
DECLARE
  v_job  NUMBER;
BEGIN
  DBMS_Job.Submit(v_job,
                  'BEGIN DBMS_Stats.Gather_Schema_Stats(''MYSCHEMA''); END;',
                  Sysdate,
                  'trunc(sysdate)+1+2/24');
  COMMIT;
  DBMS_Output.Put_Line('Job: ' || v_job);
END;
0
 
catchmeifuwantCommented:
SET SERVEROUTPUT ON
DECLARE
  v_job  NUMBER;
BEGIN
  DBMS_Job.Submit(v_job,
                  'BEGIN DBMS_Stats.Gather_Schema_Stats(ownname=>''MYSCHEMA'',cascade=>true); END;',
                  Sysdate,
                  'trunc(sysdate)+1+2/24');
  COMMIT;
  DBMS_Output.Put_Line('Job: ' || v_job);
END;
0
 
jwittenmCommented:
You need to include options to analyze the indexes.  Something like:

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
      ownname => 'ARCP001',
      method_opt => 'FOR ALL INDEXED COLUMNS',
      cascade => TRUE,
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      granularity => 'ALL');
END;
/
0
 
rnicholusAuthor Commented:
catchmeifuwant:
your suggestion seems to work.  thanks!

jwittenm:
why do I need to do this?  What is wrong with simply using "catchmeifyouwant"'s example?
0
 
catchmeifuwantCommented:
Pleasure !
0
 
jwittenmCommented:
You don't!  Nothing at all!  catchmeifyouwant's post wasn't there when I started posting or I would not have bothered, as it had the answer you needed :)  I don't post if I think you already have the answer.

The 'cascade' is the important part.  I was just showing you some other options that you may or may not want to use.  The granularity is for partitioned objects.  The 'for all indexed columns' often provides better query performance by making the optimizer aware of indexes that do not have an even spread of values.
0
 
rnicholusAuthor Commented:
Ah, I see.  Thank you for this info as well.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now