Analyzing tables in a schema?

I want to analyze some tables in a schema that have not been analyzed in a few days. What is the quickest non time consuming method or command to analyze tables in a schema without consuming alot time waiting on this analyze procedure to finish?
Who is Participating?
SujithConnect With a Mentor Data ArchitectCommented:
"quickest non time consuming method "
I dont think that you can save the time for analyzing a table using any method. Oracle will take the time it needs to analyze the table.

-- You can go for estimating the statistics rather than computing the statistics. Which will take a sample of data and arrive at the statistics. The syntax goes like "analyze table x estimate statistics" (You should be aware of this as you have a periodic routine to analyze tables.) But this statistics wont be as accurate as "analyze table x compute statistics".

-- if your tables are partitioned, analyze only the partition that contains the recent data. like:
analyze table x partition(px) compute statistics
Omega002Author Commented:
FYI Oracle database for this situation is Oracle 9i.
Naveen KumarProduction Manager / Application Support ManagerCommented:
you can analyze all the tables in a schema by using dbms_stats package. Use the procedure/function which is appropriate for you.

But in case there are only 3 tables/indexes ( assuming total objects can be 100 in your schema including your indexes as well ) in the last 2 days which are not analyzed, then doing it manually will be the fast way.



sujith80's answer is right, but "analyze table..." is not as recommended as using the dbms_stats package.

you can try :
    estimate_percent=>10,  -- analyze based on 10% of table
    block_sample=>TRUE,  -- use block sampling, not row sampling
    CASCADE=>TRUE,   -- include indexes
    method_opt=> 'FOR ALL COLUMNS SIZE AUTO',  -- can specify which columns to analyze, do all of them
    options=> 'GATHER EMPTY');  -- empty stats only

    method_opt=> 'FOR ALL COLUMNS SIZE AUTO',
    options=> 'GATHER STALE');  -- stale stats only

this will gather stats for tables that do not have any, and for tables which have had over 10% of changes since the last time the stats were gathered...

good luck :)
Stephen LappinSenior TechnologistCommented:
You can write an Oracle job to gather stats (in the background). This example analyses all tables overnight on Sunday.

    ( job       => X
     ,what      => 'DBMS_STATS.GATHER_SCHEMA_STATS (ownname => ''SCHEMA_NAME'', estimate_percent =>100,granularity=>''ALL'',cascade=>TRUE);'
     ,next_date => to_date('10/12/2006 00:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'NEXT_DAY(TRUNC(SYSDATE), ''SUNDAY'')'
     ,no_parse  => TRUE
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.