Hi Tom
You have replied that dbms_stats does not gather information on chained rows,unused data blocks and average free space available in blocks. Does it mean that to get the above mentioned information we have to run analyze command again? Also you mention that dbms_stats gathers statistics required by the CBO. Are there any special statistics that are gathered apart from number of rows, number of blocks occupied? If it is only the above two, then i believe that analyze command already accomplishes it. The only feature that is of use may be the set_stats procedure for tuning queries before using them in production.

Thanks & regards,
Ashok

When I "CREATE INDEX my_index ... COMPUTE STATISTICS" is the analyze being performed by the DBMS_STATS method, or the ANALYZE INDEX method?

If by the latter method, am I likely to gain by running DBMS_STATS post-CREATE or post-REBUILD mostly for partitioned segments (ref. your comments on gathering global statistics), with "ANALYZE ..." still being a reasonable choice at the moment for non-partitioned segments?

Tom,
You have mentioned that DBMS_STATS can analyze external tables. I tried to create an index on an external table and it came up with an error "not supported on external table".
My question is if dbms_stats could analyze external tables, why not allow an index on external table as well and analyze it with dbms_stats.

Tom,
The ongoing discussion has generated few more related queries in me. I am listing them below, for you crisp and clear answers :
1. What are different packages for running analyze (eg. dbms_utulity, dbms_stats, db_maintenance etc.) and what are the advantages of each?
2. What is the best way to analyze one particular table from CBO's perspective(BTW, what are all possible ways)?
3. If someone has to automate the analyze, what do you recommend and why - scheduling through cron or dba_jobs? (For that matter what is your opinion on scheduling any job for database- OS level or through Oracle jobs?)
4. This question is not related to analyze but to the Oracle supplied packages- Where in a databse one can get info about these packages (like v$ views etc.) or give me an url for reference please.

Thanks.

Tom
I did the following:
a) Using dbms_stats, analyzed all the tables in schema X of database A and using dbms_stats.export_schema_stats, exported the statistics into stattab.
b) Extracted the table/index creation scripts for schema X, excluding the stattab table.
c) Using Oracle export (exp), exported the stattab table.
d) Using the scripts in b), I created all the tables and indexes in schema Y of database B. These tables do not contain even a single row of data.
e) Using Oracle import(imp), imported the stattab into schema Y of database B.
f) Using dbms_stats.import_schema_stats, I imported all the statistics from stattab.

I cannot see the statistics like num_rows, avg_row_len etc. in user_tables. How does the CBO read these statistics?

Thanks

dbms_stats was designed to move the stats for user A from db1 to user A in db2.

It is the X to Y that is messing you up.  This demonstrates what you are seeing, but shows how to adapt it to work anyway:



ops$tkyte@ORA920LAP> drop user a cascade;
User dropped.

ops$tkyte@ORA920LAP> drop user b cascade;
User dropped.

ops$tkyte@ORA920LAP> grant dba to a identified by a;
Grant succeeded.

ops$tkyte@ORA920LAP> grant dba to b identified by b;
Grant succeeded.

ops$tkyte@ORA920LAP> @connect a/a

a@ORA920LAP> create table t as select * from all_objects;
Table created.

a@ORA920LAP> create index t_idx on t(object_id);
Index created.

a@ORA920LAP> analyze table t compute statistics
  2  for table for all indexes for all indexed columns;
Table analyzed.

a@ORA920LAP> exec dbms_stats.create_stat_table( user, 'st' );
PL/SQL procedure successfully completed.

a@ORA920LAP> exec dbms_stats.export_schema_stats( user, 'st' );
PL/SQL procedure successfully completed.

a@ORA920LAP> !exp userid=a/a tables=st

Export: Release 9.2.0.3.0 - Production on Fri May 2 20:08:56 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                             ST         78 rows exported
Export terminated successfully without warnings.


a@ORA920LAP> @connect b/b

b@ORA920LAP> create table t as select * from all_objects where 1=0;
Table created.

b@ORA920LAP> create index t_idx on t(object_id);
Index created.

b@ORA920LAP> !imp userid=b/b fromuser=a touser=b

Import: Release 9.2.0.3.0 - Production on Fri May 2 20:08:56 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by A, not by you

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. . importing table                           "ST"         78 rows imported
Import terminated successfully without warnings.

b@ORA920LAP> exec dbms_stats.import_schema_stats( user, 'st' );

PL/SQL procedure successfully completed.

b@ORA920LAP> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
ST
T

<b>that is what you currently see but:</b>

b@ORA920LAP> update st
  2  set c5 = USER
  3  /

78 rows updated.

b@ORA920LAP> exec dbms_stats.import_schema_stats( user, 'st' );

PL/SQL procedure successfully completed.

b@ORA920LAP> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
ST
T                                   30557

b@ORA920LAP>

<b>that'll "rename the user" in the stats tab so it'll be looking for B.T, not A.T
</b>
 

After updating c5 in stattab to new schema name, it works.
Thanks...!!

Tom
I am a bit unclear about the purpose of importing statistics from one database to another. My questions are:

a) If I set a table with 0 rows with the statistics of a 1 million row table, can I run a query against this 0 row table and actually see the same buffer gets as I would see against a 1 million row table?
b) When the query actually hits a 1 million row table, can Oracle change the plan?

There is much useful information on ANALYZE in this article. It is helpful. My scenario is like this, looks strange but it is.

In my production system, we have some problems in getting some reports from the application.
For one report query has 8 tables, out of which 2 tables have huge number of records, both these tables are not analyzed, rest all the tables are analyzed. This report is not launching.
We have now analyzed the entire schema. Then the report started working. The cost of the query before and after analyze has drastically come down. Some tables will be getting different kinds of data, and some tables will be getting same type of data. I am not sure how long this report will work and when I should analyze tables. I have read about the table monitoring feature and document says that Oracle will automatically analyze the table if it requires analyze.

My questions:
1) So enabling this feature, what will be the side effects in performance. I know that if we want some thing we should scarifice few things, but I would like to highlight these side effects to my boss.

2) If a query is having 5 tables and out of which 3 tables are not analyzed and 2 tables are analyzed, will the cost calculation be accurate? I mean will the optimizer gives a better plan.
Is it required that all the tables in the query are to be analyzed or all not to be analyzed.

Hi Tom:

As usual, your answers allows us (DBA's) to have a good night's sleep.

On the issue of monitoring...

What criteria does oracle use to determine that the stats are stale ?
Can these thresholds be changed/set by the user ?
Are the stats in the user_tab_modifications cumulative ?
If I need to preserve the stats in the above view, can you please suggest a procedure/script as to how I go about saving these. I am interested in preserving the amount of dml activity by segment.

Thanks
Mohsin

Hi Tom:

I have observed that the statistics section of the sql+ trace reports excessive recursive call count for queries agains the tables just after the tables have been analyzed.

What does this mean and what are the side effects of stats gathering ? Has the stats gathering invalidated the parsed sql in the shared pool ?

Would this be a good way of implementing my own thresholds:
I'd turn on monitoring for the segments.
Collect the dml activity from the user_tab_modifications to my own table and then have my procedure re-compute the stats after say x% of change to the segment and not use the gather stale option of the dbms_stats.
What is the timestamp column for ? If the values are cummulative then how does it determine the incremental from the last stale stats run ?

Thanks.

09:11:53 SQL> connect scott/tiger
Connected.
09:16:14 SQL> 
09:16:14 SQL> 
09:16:14 SQL> analyze table junk compute statistics for table for all indexed columns for all indexes;

Table analyzed.

09:17:06 SQL> show user
USER is "SCOTT"
14:36:01 SQL> set autotrace traceonly explain statistics;
14:36:21 SQL> 
14:36:23 SQL> select * from junk where owner='MMJ';

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=86)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'JUNK' (Cost=4 Card=1 Byt
          es=86)

   2    1     INDEX (RANGE SCAN) OF 'IDX_JUNK_OWNER' (NON-UNIQUE) (Cos
          t=3 Card=1)





Statistics
----------------------------------------------------------
        330  recursive calls
          0  db block gets
         73  consistent gets
          3  physical reads
          0  redo size
       1114  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

14:36:37 SQL> /

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=86)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'JUNK' (Cost=4 Card=1 Byt
          es=86)

   2    1     INDEX (RANGE SCAN) OF 'IDX_JUNK_OWNER' (NON-UNIQUE) (Cos
          t=3 Card=1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1114  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

14:36:58 SQL>  

analyzing the table will invalidate open cursors on that object in the shared pool.  the next parse against that object will be "hard".  hard does lots more recursive sql then "soft"

It is not the analyze so much as "you did something to cause hard parsing to have to take place"

here is an example using the rbo and EMP:


ops$tkyte@ORA817DEV> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1979  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

ops$tkyte@ORA817DEV> alter table emp add x number;

Table altered.

ops$tkyte@ORA817DEV> select * from emp;

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
        148  recursive calls
         15  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
       2042  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed


Of course, SHUTDOWN/STARTUP would have the same effect.

normal, natural, expected and one time -- you hard parse and soft after that. 

Hi Tom.

Here is some more info that might help you.

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


The junk table is a mirror of dba_objects view.

And there was no activity in the d/b between the time 9:16 and 14:36 in my sql+ session results I posted earlier.


Please let me know if you need more info.

Thanks
Mohsin

Tom,

You have mentioned in one of your replies that we should not analyze certain objects used in a query and leave the rest un-analyzed. I have read similar statements in lots of places, but i could not really understand why. Can you please explain why is it so dangerous to do so?

Hi Tom:

Excellent analogy about the CBO....

I will confirm your point, but I am positive nothing was happening on my d/b to have caused invalidation of my cursors resulting in hard parse. I conducted the test on my laptop, so I know what was happening.

Could the Oracle version have made the difference in this case?

I was using Oracle 9i Rel 2, and from your SQL+ prompt it seems you were using 8i.

Thanks
Mohsin

Sorry Tom:

I read and re-read your response. I get it.

Once in a while in between Swahili, Gujarati, Katchi, Urdu, Hindi and a sprinkling of Arabic I forget my English...

As they say in Swahili - Hakuna Matata (No Problem)

Kwaheri.

Thanks again.

In your original response you stated there are some statistics that DBMS_STATS does not collect:

"...but not the number of chained rows, average free
space, or number of unused data blocks"

I would like to switch to using DBMS_STATS for CBO statistics on my 9i database, but I also want to collect the statistics that DBMS_STATS does not; I have monitoring software that collects and uses chained rows, avg free space and unused data blocks.

Can I use Analyze first and then DBMS_STATS (in that order) to accomplish this?

The reasoning would be that first using Analyze will collect the data that DBMS_STATS does not; then DBMS_STATS will overwrite the CBO statistics that Analyze collected, but leave alone the other Analyze data.

Will that work?

Dear Tom,
Your answers have been comprehensive and helpful all the time, just another quick one ( hopefully) from me.

When we the Estimate_percent parameter and sent it to 0.00001 in DBMS_STATS.GATHER_TABLE_STATS , would it mean only that percent of the blocks be scanned for analysis ? Our gather_stats takes with estimate takes hours to complete , My Senior DBA is skeptical of the percentage option and reckons that it could be a bug , he concluded by watching V$session_longops ( as the same number of blocks were scanned ) . I have not checked it yet but apparently the percentage does work on non-partitioned tables . Is this true ? Is there a bug in oracle 9i release 1.0 software

Thanks and Regards
Vish

Hi
i think it is a best to have the percentage of minium to 10% . is that right if not pls explain.

Hi
i think it is a best to have the percentage of minium to 10% . is that right if not pls explain.

My experience was that DBMS_STATS is wonderful, but requires a lot of wrapping. Here is the wrapper proc I evolved:

CREATE OR REPLACE PROCEDURE analyze_schema(
p_schema_name IN VARCHAR2 := USER,
p_Estimate_Pct IN PLS_INTEGER := NULL) IS
-- like DBMS_UTILITY.ANALYZE_SCHEMA, but using DBMS_STATS
c_proc_name CONSTANT VARCHAR2(128) := UPPER('analyze_schema');
c_Def_Estimate_Pct CONSTANT PLS_INTEGER := 35;
c_Min_Degree CONSTANT PLS_INTEGER := 1;
c_Def_Degree CONSTANT PLS_INTEGER := LEAST(c_Min_Degree,DBMS_STATS.DEFAULT_DEGREE);
-- default degree is somehow 32767 at the moment
c_Def_Options CONSTANT VARCHAR2(30) := 'GATHER AUTO';
-- 'GATHER' means analyze all objects.
-- 'GATHER AUTO' means only objects that have changed.
v_Estimate_Pct PLS_INTEGER;
-- v_Estimate_Pct can also be set to:
-- DBMS_STATS.AUTO_SAMPLE_SIZE (Oracle sets this, but it has been known to be 0...)
-- NULL (which means, look at everything, e.g., 'COMPUTE')
v_schema_name ALL_TABLES.OWNER%TYPE := NVL(UPPER(p_schema_name),USER);
v_Msg_Text VARCHAR2(2000);
BEGIN
CASE p_Estimate_Pct
WHEN NULL THEN
v_Estimate_Pct := 100;
WHEN 100 THEN
v_Estimate_Pct := 100;
ELSE
IF DBMS_STATS.AUTO_SAMPLE_SIZE = 0 THEN
IF p_Estimate_Pct BETWEEN 1 AND 99 THEN
v_Estimate_Pct := p_Estimate_Pct;
ELSE
v_Estimate_Pct := c_Def_Estimate_Pct;
END IF;
ELSE
v_Estimate_Pct := DBMS_STATS.AUTO_SAMPLE_SIZE;
END IF;
END CASE;
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => v_schema_name,
estimate_percent => v_Estimate_Pct,
cascade => TRUE,
degree => c_Def_Degree,
options => c_Def_Options);
-- "cascade" means analyze indexes too.
v_Msg_Text := c_proc_name||'-> Schema['||v_schema_name||'], Option['||c_Def_Options||'], Sampling['||v_Estimate_Pct||'%]';
DBMS_OUTPUT.PUT_LINE(v_Msg_Text);
EXCEPTION
WHEN OTHERS THEN
v_Msg_Text := c_proc_name||'-> EXCEPTION: '||SQLERRM;
RAISE_APPLICATION_ERROR(-20199,v_Msg_Text);
END analyze_schema;

My questions are related to AUTO_SAMPLE_SIZE:
1. How come it is always 0? That's what I always see, generally in 9.2.0.3 and 9.2.0.4 on Solaris.
2. What should the default estimate percentage be, if DBMS_STATS can't tell me? Anecdotal evidence suggests that the ANALYZE/ESTIMATE percentage should be 35-40%, but then ANALYZE and DBMS_STATS are different...

requires....

OR "permits".

it requires NO WRAPPING.

It quite handily permits it easily.


you'll have to provide me a reproducible, small, simple, yet 100% complete test case to demonstrate what you mean by "0"

ops$tkyte@ORA9IR2>  exec dbms_stats.gather_table_stats( user,'BIG_TABLE',estimate_percent => dbms_stats.auto_sample_size );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sample_size from user_tables where table_name = 'BIG_TABLE';
 
SAMPLE_SIZE
-----------
     100000


(which also shows "does not require", but would "permit" wrapping.... ) 

Oracle 9.2.0.2
I observed that when estimate_percent is specified, the gather_schema_stats is still doing a compute statistics. The sample_size=num_rows. Am I incorrect in concluding that its ignoring the 25% and estimating 100% of rows ? If so, why ?
OR have I overlooked at any setting ?

Please look at the following Case :
------- # of rows from T --------
SQL> select count(*) from T
  COUNT(*)
  --------
    600980

---- Querying the dba_tables ---------
SQL> select table_name, monitoring, num_rows, sample_size, to_char(last_analyzed, 'HH24-mi-ss') ANALYZED from dba_tables;

TABLE_NAME            MON   NUM_ROWS SAMPLE_SIZE ANALYZED
--------------------- --- ---------- ----------- --------
T                     YES     600980      600980 15-39-16

------ inserting data ----------
SQL> insert into T select * from T

600980 rows created.

SQL> commit;
Commit complete.

--------- row count from T ---------
SQL> select count(*) from T;
  COUNT(*)
----------
   1201960

-------- Querying the dba_tables ---------
SQL> select table_name, monitoring, num_rows, sample_size, to_char(last_analyzed, 'HH24-mi-ss') ANALYZED from dba_tables;

TABLE_NAME            MON   NUM_ROWS SAMPLE_SIZE ANALYZED
--------------------- --- ---------- ----------- --------
T                     YES     600980      600980 15-39-16

---------- gather_schema_stats ---------
begin
  dbms_stats.gather_schema_stats(
       ownname          => 'TEST',
       estimate_percent => 25,
       method_opt       => 'FOR ALL INDEXED COLUMNS SIZE 1',
       degree           => 2,
       cascade          => FALSE,
       options          => 'GATHER AUTO'
       );
  end;
/ 
PL/SQL procedure successfully completed.

-------- Querying the dba_tables ---------
SQL> select table_name, monitoring, num_rows, sample_size, to_char(last_analyzed, 'HH24-mi-ss') ANALYZED from dba_tables;

TABLE_NAME            MON   NUM_ROWS SAMPLE_SIZE ANALYZED
--------------------- --- ---------- ----------- --------
T                     YES    1201960     1201960 15-48-45

----------------------------------- 

Tom, I understand it better now.
I took out the invalid parameters.
But my question still lingers :
Why is the gather_schema_stats analyzing all 100% of rows. I changed only 10%(50K of rows) out of 450K rows.
My dbms_job wakes up every few days to execute gather_schema_stats. Now, if I have 10-15 tables which have changed by more than 10%, then analyzing these tables 100%(compute) will take lot of time.
I know oracle automatically calculates how much percentage to analyze. But everytime why is it analyzing all 100% of the rows? Why is it not estimating ?

=========
begin
dbms_stats.gather_schema_stats(
ownname => 'TEST',
options => 'GATHER AUTO'
);
end;
/
==============

Hi Tom,
the thing am fighting is I like to use "stale" because I can specify the percentage to analyze and take advantage of the degree. AUTO is good because it automatically analyzes newly added tables.

Best of both worlds would be :
1) analyze newly added tables -> AUTO does this. STALE doesn't.

2) analyze only percentage of rows, not 100% -> AUTO is analyzing 100%. STALE can do percentage.

Is it possible ?

Thanks as always

sounds like you might want:

a) alter schema tab monitoring
b) gather empty
c) gather stale

for your degree of control.  consider:




ops$tkyte@ORA920PC> create table t1 as select * from all_users;
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec dbms_stats.alter_schema_tab_monitoring
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> select table_name, num_rows from user_tables;
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1
 
ops$tkyte@ORA920PC> exec dbms_stats.gather_schema_stats( user, options => 'gather empty' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> select table_name, num_rows from user_tables;
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                     45

<b>that picked it up, this</b>

 
ops$tkyte@ORA920PC> exec dbms_stats.gather_schema_stats( user, options => 'gather stale' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> select table_name, num_rows from user_tables;
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                     45

<b>did nothing..</b>

 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create table t2 as select * from all_users;
 
Table created.
 
ops$tkyte@ORA920PC> insert into t1 select * from t1;
 
45 rows created.
 
ops$tkyte@ORA920PC> insert into t1 select * from t1;
 
90 rows created.
 
ops$tkyte@ORA920PC> insert into t1 select * from t1;
 
180 rows created.
 
ops$tkyte@ORA920PC> commit;
 
Commit complete.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec dbms_stats.alter_schema_tab_monitoring
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> select table_name, num_rows from user_tables;
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                     45
T2
 
ops$tkyte@ORA920PC> exec dbms_stats.gather_schema_stats( user, options => 'gather empty' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> select table_name, num_rows from user_tables;
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                     45
T2                                     45

<b>that got t2 and...</b>
 
ops$tkyte@ORA920PC> exec dbms_stats.gather_schema_stats( user, options => 'gather stale' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC> select table_name, num_rows from user_tables;
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                    360
T2                                     45
 
ops$tkyte@ORA920PC>

<b>that got t1...</b>

 

pretty GOOD !
Thank you much.
Have a good weekend.

Tom,

Please let us know, when we need to do the following

execute dbms_stats.gather_schema_stats('SYS');

at what point of time, do you recommend us executing the above sql


Thanks a lot

Tom,

I've read you're advice about utilizing DBMS_STAT instead of ANALYZE statement. But, I had an experience that makes me get confused: with analyze, the cost got Higher, but the response time was the best; with DBMS_STATS, little tiny costs, but 12 times the response time (comparing TKPROF results). Is there any reason for that situation?

(TKPROF of ANALYZEd query)
********************************************************************************

SELECT a.seq_mov, a.empresa, a.filial, a.LOCAL, a.produto, a.qtde_mov,
a.vlr_tot_mov, a.num_docto, a.cod_oper, a.dt_mov, a.historico,
a.lote_cont, a.lcto_direto, a.disp_trigger, a.dt_registro,
a.qtde_val,
b.entrada_saida
FROM ce_movest a, ce_operacoes b
WHERE (:b5 IS NULL OR a.empresa = :b5)
AND (:b4 IS NULL OR a.filial = :b4)
AND (:b3 IS NULL OR a.produto = :b3)
AND (:b2 IS NULL OR a.dt_mov >= :b2)
AND (:b1 IS NULL OR a.dt_mov <= (TO_DATE (:b1) + .99999))
AND b.empresa = a.empresa
AND b.cod_oper = a.cod_oper
order by a.empresa, a.filial, a.produto, a.dt_mov, b.ajuste_estoque,
b.entrada_saida /*decode(b.entrada_saida, 'E', decode(b.transferencia, 'S', 3, 1), 2)*/,
a.local, a.qtde_mov

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.27 5.99 55 19061 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.27 5.99 55 19061 0 4

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 47

Rows Row Source Operation
------- ---------------------------------------------------
4 SORT ORDER BY (cr=19061 r=55 w=0 time=5997116 us)
4 HASH JOIN (cr=19061 r=55 w=0 time=5996843 us)
103 INDEX FULL SCAN CE_OPERACOES_AK1 (cr=1 r=0 w=0 time=288 us)(object id 553481)
4 TABLE ACCESS FULL CE_MOVEST (cr=19060 r=55 w=0 time=5992324 us)

********************************************************************************




(TKPROF of DBMS_STATSed)
********************************************************************************

SELECT a.seq_mov, a.empresa, a.filial, a.LOCAL, a.produto, a.qtde_mov,
a.vlr_tot_mov, a.num_docto, a.cod_oper, a.dt_mov, a.historico,
a.lote_cont, a.lcto_direto, a.disp_trigger, a.dt_registro,
a.qtde_val,
b.entrada_saida
FROM ce_movest a, ce_operacoes b
WHERE (:b5 IS NULL OR a.empresa = :b5)
AND (:b4 IS NULL OR a.filial = :b4)
AND (:b3 IS NULL OR a.produto = :b3)
AND (:b2 IS NULL OR a.dt_mov >= :b2)
AND (:b1 IS NULL OR a.dt_mov <= (TO_DATE (:b1) + .99999))
AND b.empresa = a.empresa
AND b.cod_oper = a.cod_oper
order by a.empresa, a.filial, a.produto, a.dt_mov, b.ajuste_estoque,
b.entrada_saida /*decode(b.entrada_saida, 'E', decode(b.transferencia, 'S', 3, 1), 2)*/,
a.local, a.qtde_mov

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 13.03 74.99 0 104482 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 13.03 75.00 0 104482 0 2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 47

Rows Row Source Operation
------- ---------------------------------------------------
2 SORT ORDER BY (cr=104482 r=0 w=0 time=74999502 us)
2 TABLE ACCESS BY INDEX ROWID CE_MOVEST (cr=104482 r=0 w=0 time=74999276 us)
1276536 NESTED LOOPS (cr=4901 r=0 w=0 time=29219344 us)
103 TABLE ACCESS FULL CE_OPERACOES (cr=3 r=0 w=0 time=728 us)
1276432 INDEX RANGE SCAN CE_MOVEST_FK3 (cr=4898 r=0 w=0 time=10600999 us)(object id 29012)

********************************************************************************

As we can see, the time for the query havin tables DBMS_STATSed were 75 seconds; when ANALYZEd, it took about 6 seconds... Is there a reason for that?

Thanks,
Bruno

I have a third party system that initially has no data. I need to load alot of data into this system. The product provides a number of interface tables into which I can load my data. I then initiate a third party supplied procedure that will pick up the data in the interface tables, validate and "interalize" the data.

I want the load to run efficiently. I believe my best bet is to re-generate stats (on the products internal tables) whilst the load is in progress (a 10-20 hrs process). Here are my questions:

Will open cursors necessarily be reparsed when the stats have been generated?

I plan to also generate system stats (io, cpu etc). I am also wondering about generating stats on 'SYS' and 'SYSTEM'. Is this recommended on 9.2.0.4.0?

Many Thanks.

to dbms_stats.gather_schema_stats:

method_opt => 'FOR ALL INDEXED COLUMNS'
and
method_opt => 'FOR ALL COLUMNS SIZE AUTO' ?

I just drastically improved the performance of a query by using the first. The first appears to have difference use different bucket sizes and generates more stats. Can you please comment on what should be "best practice" as far as generating stats is concerned?

Many Thanks.

I've noted the follwing difference in stats:

Name Null? Type
----------------------------------------- -------- ---------------

VMETERID NOT NULL NUMBER(38)
CUI_TID NOT NULL NUMBER(38)
INTERVAL_ENDDT NOT NULL DATE
INTERVAL_STARTDT NOT NULL DATE
VALUE FLOAT(126)
READ_TYPE_FLAG VARCHAR2(5)
TRANS_TID NUMBER(38)
TRANS_UID NUMBER(38)
TRANS_STARTTS DATE

method_opt => 'FOR ALL INDEXED COLUMNS':
=======================================

COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS DENSITY LAST_ANALYZED
------------------------------ ------------ ---------- ----------- ---------------- -------------------
CUI_TID 1 0 1 1.0000000000 05/28/2004 23:03:14
INTERVAL_ENDDT 796 0 1 .0012562814 05/28/2004 23:03:14
INTERVAL_STARTDT 792 0 1 .0012626263 05/28/2004 23:03:14
READ_TYPE_FLAG 3 0 1 .3333333333 05/28/2004 23:03:14
TRANS_STARTTS 7894 0 1 .0001266785 05/28/2004 23:03:14
TRANS_TID 1 0 1 1.0000000000 05/28/2004 23:03:14
TRANS_UID 1 0 1 1.0000000000 05/28/2004 23:03:14
VALUE 57746 0 1 .0000173172 05/28/2004 23:03:14
VMETERID 487822 0 1 .0000020499 05/28/2004 23:03:14

method_opt => 'FOR ALL COLUMNS SIZE AUTO':
=========================================

Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
VMETERID NOT NULL NUMBER(38)
CUI_TID NOT NULL NUMBER(38)
INTERVAL_ENDDT NOT NULL DATE
INTERVAL_STARTDT NOT NULL DATE
VALUE FLOAT(126)
READ_TYPE_FLAG VARCHAR2(5)
TRANS_TID NUMBER(38)
TRANS_UID NUMBER(38)
TRANS_STARTTS DATE


INDEXES FOR TABLE: VMC_DATA
================================================


INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
PKVMC_DATA VMETERID 1
CUI_TID 2
INTERVAL_STARTDT 3


TABLE STATISTICS FOR : VMC_DATA
===================================================


NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------------
4680488 35452 100 05/28/2004 22:27:10


COLUMN STATISTICS FOR : VMC_DATA
====================================================


COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS DENSITY LAST_ANALYZED
------------------------------ ------------ ---------- ----------- ---------------- -------------------
CUI_TID 1 0 1 1.0000000000 05/28/2004 22:27:10
INTERVAL_STARTDT 792 0 68 .0012626263 05/28/2004 22:27:10
VMETERID 487489 0 75 .0000031279 05/28/2004 22:27:10

Tom,

As you asked for (from my Review above), the exact commands were:

DBMS Stats
----------
exec dbms_stats.delete_table_stats('SAGE', 'CE_MOVEST');
exec dbms_stats.delete_table_stats('SAGE', 'CE_OPERACOES');

exec dbms_stats.gather_table_stats('SAGE', 'CE_MOVEST');
exec dbms_stats.gather_table_stats('SAGE', 'CE_OPERACOES');


ANALYZE
-------
exec dbms_stats.delete_table_stats('SAGE', 'CE_MOVEST');
exec dbms_stats.delete_table_stats('SAGE', 'CE_OPERACOES');

analyze table ce_movest delete statistics;
analyze table ce_operacoes delete statistics;

analyze table ce_movest estimate statistics for table for all indexes for all indexed columns;
analyze table ce_operacoes estimate statistics for table for all indexes for all indexed columns;


Note that my approach was, first, remove all relevant statistics for the method used and then gather them brand new.

Just can't understand why is it so different... Can you see any reason for it?

Thanks again,
Bruno.

Hi Tom,

Please can you clarify in what circumstances and why would you use the 3 different(listed below ) methods to analyze tables,

1). a@ORA920LAP> analyze table t compute statistics
for table for all indexes for all indexed columns;
2). a@ORA920LAP> analyze table t compute statistics;
3). dbms_stats(....

I,m using oracle8i.

Q1) Why would I need to compute stats for
all indexed columns when the whole table is getting
analyzed.

Q2) Does dbms_stats analyze the table, it's indexes and the indexed columns too or what exactly does it do.If I specify a schema then does it to all the above mentioned statistics.

Thanks lots as always Tom,
Regards,
Aru.

Tom,


For a partitioned table, if i say cascade=true. It will collect stats on all local indexes and global indexes?

If yes,i dont need to use GATHER_INDEX_STATS - right?

Please confirm.

Thanks a lot
Pravesh Karthik

...contd

and if yes for local and global indexes. what mode does it do for the indexes ?

"It's not recommended to compute index statistics in estimated mode, as the Cost Optimizer is almost based on index statistics, particularly on the CLUSTERING_FACTOR value. It needs the statistics would be as accurate as possible. "

above lines are from -- </code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=175258.1&p_database_id=NOT <code>

Thanks again ..

Hi Tom,
1)exec dbms_stats.gather_database_stats(method_opt=>'FOR ALL INDEXED COLUMNS',degree=>7,cascade=>true);

2)exec dbms_stats.gather_database_stats(method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',degree=>7,cascade=>true);

Is the first command will collecting histogram? If yes what is the different between first and second command?

thanks

see

they are very different.  size auto sort of needs a historical workload to peek at (queries) as well -- that is part of it's algorithm.  

consider:

ops$tkyte@ORA9IR2> create table t as select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx1 on t(object_id);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx2 on t(owner);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns', cascade => true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select column_name, count(*) from user_tab_histograms where table_name = 'T' group by column_name;
 
COLUMN_NAME            COUNT(*)
-------------------- ----------
OBJECT_ID                    76
OWNER                        22
 
<b>histograms collected for both...</b>

ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns size auto', cascade => true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select column_name, count(*) from user_tab_histograms where table_name = 'T' group by column_name;
 
COLUMN_NAME            COUNT(*)
-------------------- ----------
OBJECT_ID                     2
OWNER                         2

<b>no histograms -- just hi/lo values and such.  there were no queries that ever referenced either column so histograms were not collected</b>

 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns size skewonly', cascade => true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select column_name, count(*) from user_tab_histograms where table_name = 'T' group by column_name;
 
COLUMN_NAME            COUNT(*)
-------------------- ----------
OBJECT_ID                     2
OWNER                        22
 
<b>it looked at the columns and said "object id, unique -- no histograms needed there", owner -- not so unique and pretty skewed, lets get them</b>



But see that link, the meanings are defined there. 

Hi Tom,
Based on the link, size auto means:
It'll collect histograms in memory only for those columns which are used by your applications (those columns appearing in a predicate involving an equality, range, or like operators). we know that a particular column was used by an application because at parse time, we'll store workload information in SGA. Then we'll store histograms in the data dictionary only if it has skewed data (and it worthy of a histogram)
What i understand from the above definition is (please correct me if i'm wrong), oracle will only collect histogram on those tables' columns which are ever been used since database startup and has skewed data, it will not collect histogram for others tables' columns that never been used since database startup even skewed data exists. If my understanding is true, how long oracle will store those table parsing information in SGA (so that dbms_stats will know which column to analyze)? Does it means this option is not appropriate for a fresh database which is just starting up?
I see some of the oracle notes highly recommend to used this option to analyze the histogram information, is it true?

Tom,
I'm using dbms_stats.gather_schema_stats( ownname=>'XXX') to analyze a whole schema.
I dont want to analyze just a table in this schema. What I have to do ?.
I have been verifying all parameters, but and I didnt find any options yet.

Thanks,
Altieri

Tom, how to determine if the statistics were collected by ANALYZE command or by the package?

if you look at columns that are filled in by ANALYZE but not dbms_stats - avg_space is one - you might be able to tell if ANALYZE was at some time in the past used:

ops$tkyte@ORA9IR2> create table t as select * from all_Users;
 
Table created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select avg_space from user_tables where table_name = 'T';
 
 AVG_SPACE
----------
         0
 
ops$tkyte@ORA9IR2> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2> select avg_space from user_tables where table_name = 'T';
 
 AVG_SPACE
----------
      7488
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select avg_space from user_tables where table_name = 'T';
 
 AVG_SPACE
----------
      7488


but dbms_stats won't overwrite the value -- so it is not conclusive proof.
 

Hi Tom,
Do you find out any research on 3rd jan 2005 thread which relate to dbms_stats with size auto option?

thanks

I have the database setup (9i) to gather statistics however I am running into a strange error

BEGIN DBMS_STATS.GATHER_DATABASE_STATS(cascade=>TRUE); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9857
ORA-06512: at "SYS.DBMS_STATS", line 10041
ORA-06512: at "SYS.DBMS_STATS", line 10134
ORA-06512: at "SYS.DBMS_STATS", line 10114
ORA-06512: at line 1

I don't understand what
"ORA-00904: : invalid identifier" means with nothing between the 2 colon's. Any ideas? I think I know the schema its failing in.

Because I'm doing an analyze, isn't it an issue of object structure, not sql? Regardless, I am tracing it now.

after analyzing the tkprof for the failure I found that the table that was failing. What was interesting was that I found that the table being analyzed was failing on a specific index. One that is incredibly old (also note that table contains hourly transactions in the realm of only 6 million rows). Dropping the index and using dbms_stats again worked. Adding the index back and using dbms_stats worked.

TKPROF

********************************************************************************

select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring no_expand
index(t,"INDX_TAB_CAMP") */ count(*) as nrw,count(distinct
sys_op_lbid(30519,'L',t.rowid)) as nlb,count(distinct "CAMP") as ndk,
sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from
"KCAGENT"."TAB_ALL" sample block
(5.77406383513445589344056295551646142247) t where "CAMP" is not null


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 1 14 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 14 0 0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 43 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY (cr=0 r=0 w=0 time=0 us)
1 INDEX SAMPLE FAST FULL SCAN INDX_TAB_ALL_ID_DATE (cr=14 r=1 w=0 time=6851 us)(object id 49563)

********************************************************************************


But the index in the hint is different than the index in the explain.

Regardless, the immediate problem is solved, thank you.

I'm interested in knowing in the following

1.Lets say table t has 1000 rows

2.Generate statistics for table t and the associated indexes.

3.Now lets truncate table t.

--will the statistics be automatically be updated ?

4.In the next scenario , lets delete all the rows without truncating.

--will the statistics be automatically be updated ?

If they dont can we do something to update the statistics immedietly based on a certain operation.

Thanks

Thanks everyone.

My question - 10g's "black box" statistics collection mechanism, does it collect histograms and if so, through what parameters?

I've been reasonably satisfied with the execution plans we're getting and collecting system statistics in particular has been a boon. However there still are some zingers and they typically involve tables with what I know to be rather skewed data distribution on joined columns. I'd like to keep the process as "black box" as possible (our DBAs have bigger problems to worry about) so I've been wondering how I could verify and/or incorporate histogram collection into the black box, or whether I need to disable the auto collection and wrap up my own.

Thanks in advance - this forum is great!

Once a table is set up as Monitoring, how do I disable monitoring, if I need to?

ops$tkyte@ORA9IR2> alter table t monitoring;
                                                                                
Table altered.
                                                                                
ops$tkyte@ORA9IR2> alter table t NOmonitoring;
                                                                                
Table altered.
 

How do I disable MONITORING on a table?

DBMS_STATS seems very unaccurate at estimating number of distinct keys on nonunique indexes.

I think it could be a bug.

/*
Test case:
DBMS_STATS gives wrong DISTINCT_KEYS for nonunique index
*/

select * from V$VERSION;

/*
BANNER
------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
*/


create table NUMBERS as
select mod(N,2000001) N from (select ROWNUM N from DUAL connect by ROWNUM <= 6000000)
;

create index IX_N on NUMBERS (N) compute statistics;

select count(distinct N) from NUMBERS;
/*
COUNT(DISTINCTN)
----------------
2000001
*/

select i.DISTINCT_KEYS, i.SAMPLE_SIZE, i.LAST_ANALYZED
from USER_INDEXES i
where i.INDEX_NAME = 'IX_N'
;
/*
DISTINCT_KEYS SAMPLE_SIZE LAST_ANALYZED
------------- ----------- -------------------
2000001 6000000 2005-05-25 16:25:12
*/


-- begin bug ------------------------------------
begin
DBMS_STATS.GATHER_INDEX_STATS (
ownname => user,
indname => 'IX_N',
estimate_percent => 20,
degree => 1);
end;
/

select i.DISTINCT_KEYS, i.SAMPLE_SIZE, i.LAST_ANALYZED
from USER_INDEXES i
where i.INDEX_NAME = 'IX_N'
;
/*
DISTINCT_KEYS SAMPLE_SIZE LAST_ANALYZED
------------- ----------- -------------------
414785 1188855 2005-05-25 16:27:39
*/
-- end bug ---------------------------------------


analyze index IX_N estimate statistics sample 20 percent;

select i.DISTINCT_KEYS, i.SAMPLE_SIZE, i.LAST_ANALYZED
from USER_INDEXES i
where i.INDEX_NAME = 'IX_N'
;
/*
DISTINCT_KEYS SAMPLE_SIZE LAST_ANALYZED
------------- ----------- -------------------
2023171 1415367 2005-05-25 16:47:17
*/


begin
DBMS_STATS.GATHER_INDEX_STATS (
ownname => user,
indname => 'IX_N',
estimate_percent => NULL,
degree => 1);
end;
/

select i.DISTINCT_KEYS, i.SAMPLE_SIZE, i.LAST_ANALYZED
from USER_INDEXES i
where i.INDEX_NAME = 'IX_N'
;
/*
DISTINCT_KEYS SAMPLE_SIZE LAST_ANALYZED
------------- ----------- -------------------
2000001 6000000 2005-05-25 16:48:53
*/

We have lots of almost-unique indexes in our system.

Probably this is a simptom of bad design, but in development we were heavily constrained by the requirements, which are basically:
* a linear chain of four external systems that exchange messages bidirectionally about entities via Tibco bus; the systems do not employ an entity omogeneus unique key, but only entity unique keys specific for each system (i.e. each system marks an entity with its own key format), and correlation keys from system to system
* collect entities and related events from these systems intercepting the messages in near real-time
* aggregate the events every 1 hour
* make a entity flat warehouse table to be queried by a GUI; refresh it every 1 hour
* delete "dead" entities each day
* the average number of live entities is about 3.5 million per system

I don't remember a specific query which performs bad due to this problem.

We thought we'd create a DDL trigger to automatically turn on Monitoring for new tables, but this DID NOT work for External tables.
Is there some way, (considering it is an AFTER trigger) to know, for SURE, that the current table

1)Is NOT an External table and set them to monitoring
or
2)IS an External table and dont set Monitoring for it?

CREATE OR REPLACE TRIGGER object_change AFTER
CREATE ON SCHEMA
DECLARE
alt_mont varchar2(1000);
BEGIN
if (ora_sysevent in ('CREATE') and
ora_dict_obj_type = 'TABLE') then
alt_mont :='ALTER TABLE ' ||ora_dict_obj_name||' MONITORING ';
execute immediate alt_mont;
end if;
end;
/

Thanks, accept catching exception is a brilliant solution, but can't find anything in the Data Dictionery that suggests the table being created is an External one.
By looking at xxx_objects or xxx_tables, what is the DEFINITIVE way to categorise Tables?

ops$tkyte@ORA9IR2> select * from user_external_tables;
 
no rows selected
 

Currently using:
DBMS_UTILITY.ANALYZE_SCHEMA('ESD', 'COMPUTE');
It takes about 70 minutes to run on about 30 Gb of Data warehouse weekly.

Would like to replace with:

dbms_stats.gather_schema_stats(ownname=>'ESD',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'AUTO', cascade=>TRUE);

1. I'm struggling with how to assess impact on the production server. I can run the new
query on an RMAN restore of the data, without a representative connection load. Is that a fair test per your earlier recommendations?

2. Does the old analyze data need to be dropped before I run the new statement?

3.Looking at the DBMS_STATS statement above, as its specified
It will only gather stale statistics from objects the CBO is using?

1) I'd be looking to compare plans.  Biggest thing will be auto in the method_opt, in order for that to work, you need to have had some queries going over time.  auto (you have the syntax wrong, see below) will not work "good" until it knows the predicates you use:

ops$tkyte-ORA9IR2> create table ttt as select object_name, owner, object_type from all_objects;
 
Table created.
 
ops$tkyte-ORA9IR2> create index t_idx1 on ttt(owner);
 
Index created.
 
ops$tkyte-ORA9IR2> exec dbms_stats.gather_schema_stats( ownname=> user, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>'for all columns size AUTO',cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> set autotrace traceonly explain
ops$tkyte-ORA9IR2> select * from ttt t1 where owner = 'SCOTT';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1271 Bytes=48298)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TTT' (Cost=12 Card=1271 Bytes=48298)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=3 Card=1271)
 
<b>see the card = 1271</b> 
 
ops$tkyte-ORA9IR2> set autotrace off
ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> select column_name, count(*) from user_tab_histograms where table_name = 'TTT' group by column_name;
 
COLUMN_NAME                      COUNT(*)
------------------------------ ----------
OBJECT_NAME                             2
OBJECT_TYPE                             2
OWNER                                   2
 
<b>only two buckets, no histograms really</b>

ops$tkyte-ORA9IR2> exec dbms_stats.gather_schema_stats( ownname=> user, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>'for all columns size AUTO',cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte-ORA9IR2> select column_name, count(*) from user_tab_histograms where table_name = 'TTT' group by column_name;
 
COLUMN_NAME                      COUNT(*)
------------------------------ ----------
OBJECT_NAME                             2
OBJECT_TYPE                             2
OWNER                                  22
 
<b>but it saw the predicate now, so it got them</b>

ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> set autotrace traceonly explain
ops$tkyte-ORA9IR2> select * from ttt t2 where owner = 'SCOTT';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=40 Bytes=1520)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TTT' (Cost=2 Card=40 Bytes=1520)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=1 Card=40)
 
 
 
ops$tkyte-ORA9IR2> set autotrace off
ops$tkyte-ORA9IR2> spool off

<b>and that card= is more accurate</b>



2) no

3) no, it'll gather them all.  you would need to use the alter table T monitoring command and then do a gather stale option. 

How does the method_opt=>'for all column size AUTO' update as the CBO parses queries? Your example showed cardiality decreasing as more queries run on the table, correct?

Hello Tom,

let's say i want to transport statistics from one big table to another.

create table t2 as select * from big_table

big table has up to date statistics and i want to transport them to t2

import_table_stats will not work, because it uses same table name.

thanks for your option

thank you Tom,

we have large reporting table (no updates) with 15+ million rows par partition, created with CTAS. Statistics should be very similar for my new table copy.

Tom,

Every week we are analyzing the database schema using DBMS_STAT package as a batch process in our production environment.
But the this job is getting failed often with ORA-01555 Snapshot too old.We could understand the cause of the problem that is,this analyze job and another job which is schedule in the batch running parallely getting clashed at some point and end with ORA-01555 error.Here my question is,if we re-run the DBMS_STAT package after it failed,its analyzing all the objects from the begining.But we want to analyze objects from the point it failed previously.Is there any option in DBMS_STAT package?.Pls advice.I hope you understand the scenario .

Lately we are experiencing an issue with DBMS_STATS package when we try to analyze the table.

This is what we did:
BEGIN dbms_stats.gather_table_stats(ownname => 'EURO_TSTAT', estimate_percent => 15, tabname => 'CALL_SYSTEM_EURO', block_sample => FALSE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', degree => 4, granularity => 'ALL', cascade => TRUE ); END;


What are the things we need to see while dbms_stats are in hung state?
Any steps to resolve this?

thanks,

Tom,

I have noted that gather schema stats would not analyze indexes until we specify cascade=>true,
is it true?
here the procesure I'm using,kindly give suggestion on this to do the normal process with indexes as well..
exec dbms_stats.gather_schema_stats( -
ownname => 'OMSTNX', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
degree => DBMS_STATS.DEFAULT_DEGREE -
)

Hi Tom,

We have this as a job run Weekly (Sunday, Early AM):
DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'HT4'
, estimate_percent => 40
, block_sample => FALSE
, method_opt => 'FOR ALL COLUMNS SIZE 1'
, degree => NULL
, granularity => 'DEFAULT'
, cascade => TRUE
);

Now, I came into work Monday and the performance was 'yukky', so I did this:
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ANALYZE TABLE PAT_INSURANCE ESTIMATE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS FOR ALL INDEXES';
EXECUTE IMMEDIATE 'ANALYZE TABLE PAT_PROCEDURE ESTIMATE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS FOR ALL INDEXES';
EXECUTE IMMEDIATE 'ANALYZE TABLE PAT_PROCEDURE_BALANCE ESTIMATE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS FOR ALL INDEXES';
EXECUTE IMMEDIATE 'ANALYZE TABLE PAT_FINANCIAL_TRANSACTION ESTIMATE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS FOR ALL INDEXES';
EXECUTE IMMEDIATE 'ANALYZE TABLE ALL_NAME ESTIMATE STATISTICS FOR TABLE FOR ALL INDEXED COLUMNS FOR ALL INDEXES';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

... And all was good again. Any ideas what is incorrect perhaps on the DBMS_STATS.GATHER_SCHEMA_STATS parameter settings, and could you suggest a good method using this procedure?

Thanks!

Thanks Tom.

The Exception handler was quick adhoc code and I don't usually code that way.

Anyway, this is what I've done:

1) turned table monitoring on
2) Via JOB at 1AM:
/* Gather Stale Stats Nightly */
DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'HT4',
estimate_percent => 40, block_sample => FALSE, method_opt =>
'FOR ALL INDEXED COLUMNS', degree => NULL, granularity =>
'DEFAULT', options => 'GATHER STALE', cascade => TRUE);

3) Once per week (Sunday Early AM):
/* Gather All Stats Weekly */
DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'HT4',
estimate_percent => 40, block_sample => FALSE, method_opt =>
'FOR ALL INDEXED COLUMNS', degree => NULL, granularity =>
'DEFAULT', options => 'GATHER', cascade => TRUE);

This seems to give me a more consistent performance.

Thanks!

running the package mentioned above in my previous review

begin
dbms_stats.gather_schema_stats(ownname=> 'ESD',estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'for all columns size
AUTO',cascade=>true );
commit;
end;

I can't seem to verify with
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#33117 <code>
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,
TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS')
FROM DBA_TABLES
WHERE TABLE_NAME IN ('SO_LINES_ALL','SO_HEADERS_ALL','SO_LAST_ALL');
I get 0 rows
thinking I'm missing something about initially gathering stats, I looked at ch6 of effective oracle by design also
the procedure only takes 1 sec to run, it can't be gathering stats quite that fast or can it?

Hi Tom,
How Statistics gathering is useful for the Optimizer??
In what ways it uses the statistics??

Please provide a detailed answer.

Bye!!

Hi Tom,

Both you and the Oracle documentation (9i) state the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS.

Do you know when this will be the case?

Also, is the DBMS_DDL.ANALYZE_OBJECT procedure equivalent to ANALYZE TABLE?

Thanks!

I ran the sql below to gather stats:

begin
dbms_stats.gather_schema_stats(ownname=> 'schema',
estimate_percent=>dbms_stats.auto_sample_size,
cascade=>true);
commit;
end;

This caused several of the plans for procedures that are "unhinted" and were using indexes to stop using them and do full table scans, what would you suggest to look at to find out why.

Thanks again for the great book from the OTN lounge at OOW05

In a followup above you state:
----------------
analyzing the table will invalidate open cursors on that object in the shared pool. the next parse against that object will be "hard". hard does lots more recursive sql then "soft"
-----------------

I don't fully understand what you mean by 'invalidate open cursors". An anaylze of a table will not cause some other session's executing sql to fail, will it?

For complicated reasons I will need to run a bunch of dbms_stats.gather_table_stats after an application upgrade, and there will be real users on the application at that point. I am assuming that as the stats are gathered, any parse (soft or hard) of any sql on an analyzed object will cause complete re-optimization, but that in no case will an executing statement fail because of the analyze. Is this correct?

The goal here, obviously, is to get users on the application as quickly as possible after the upgrade, even if performance isn't what we'd like; then gather the stats as the system runs to bring it back to its 'normal' statistics state and 'normal' performance.

Tom, please correct me if i'm wrong, but by analyzing the table, its corresponding open cursors will be invalidated in the shared_pool and the only difference is that the cursors have to do a hard parse instead of a soft parse . The process will complete, but will be less effective, this time. Regards, catalin .

9.2.0.6 DB
We did some testing,(see general question above, sorry about that one, panic'd)Here is a more specific one.

We are now using the DBMS_STATS package for collecting stats. We were previously using the old DBMS_UTILITY.ANALYZE_SCHEMA method.

The following is the new method we are using to collect stats:


dbms_stats.gather_schema_stats(

ownname=>’XXX',

estimate_percent=>dbms_stats.auto_sample_size,

method_opt=>'for all columns size AUTO',

cascade=>true);


Since we changed over to the new statistics gathering, we have seen significant degradation in some of our existing queries. In analyzing one of the queries, we found some strange behavior by the CBO when evaluating a TO_DATE conversion in the where predicate:


When the date string includes a 4-digit year such as this:


WHERE OA.CREATE_DATE >= TO_DATE('7/28/2005', 'MM/DD/RR')


It uses the index on the CREATE_DATE column which is desired.



When the date string uses a 2-digit year such as this:



WHERE OA.CREATE_DATE >= TO_DATE('7/28/05', 'MM/DD/RR')



It does NOT use the index on the CREATE_DATE column even though this also converts the string to 7/28/2005.


The difference in execution times between these two execution plans is less than a second when the index is used to minutes when the index is not used.

This is quite confusing and was hoping you could shed some light on this.

Hope you can read this, my email cut and paste got rid of the formatting, need a clarification
statement that the 2-digit year is an unknown. I am issuing the query today and by Oracle's rules that would mean an 05 year with an RR date format elelment would return year 2005. Tha makes it a constant as of right now doesn't it?


Using a 4-digit year in the to_date string:






Cost
Cardinality
Bytes

SELECT STATEMENT, GOAL = CHOOSE


3102
207
40986

SORT ORDER BY


3102
207
40986

HASH JOIN OUTER


3094
207
40986

HASH JOIN OUTER


2717
206
30488

NESTED LOOPS


2714
206
29046

HASH JOIN OUTER


2302
206
26162

NESTED LOOPS OUTER


2227
206
23484

NESTED LOOPS


2227
206
21836

HASH JOIN


2021
206
16686

TABLE ACCESS BY INDEX ROWID
WAREHOUSE
ORDER_AGGREGATE
1988
206
14214

INDEX RANGE SCAN
WAREHOUSE
OA_CREATE_DATE_IDX
30
5041


TABLE ACCESS FULL
WAREHOUSE
PUBLISHER_PROFILE
32
11898
142776

TABLE ACCESS BY INDEX ROWID
ESD
PUBLISHERS
1
1
25

INDEX UNIQUE SCAN
ESD
PUBLISHERS_PK

1


INDEX UNIQUE SCAN
ESD
SALES_AGENT_ID_PK

1
8

TABLE ACCESS FULL
WAREHOUSE
FULFILLMENTS
74
48036
624468

TABLE ACCESS BY INDEX ROWID
WAREHOUSE
ORDER_EXT_AGGREGATE
2
1
14

INDEX UNIQUE SCAN
WAREHOUSE
ORDERS_EXT_AGG_PK
1
1


TABLE ACCESS FULL
WAREHOUSE
CURRENCIES
2
169
1183

VIEW
WAREHOUSE

375
38293
1914650

SORT GROUP BY


375
38293
1799771

HASH JOIN


66
38293
1799771

TABLE ACCESS FULL
WAREHOUSE
RETURN_REASONS
3
696
20880

TABLE ACCESS FULL
WAREHOUSE
RETURNS
62
38293
650981




Using a 2-digit year in the to_date string:



05, RR






SELECT STATEMENT, GOAL = CHOOSE


21420
234
46332

SORT ORDER BY


21420
234
46332

FILTER






HASH JOIN OUTER


21411
234
46332

HASH JOIN OUTER


21034
233
34484

NESTED LOOPS


21031
233
32853

HASH JOIN OUTER


20565
233
29591

NESTED LOOPS OUTER


20490
233
26562

NESTED LOOPS


20490
233
24698

HASH JOIN


20257
233
18873

TABLE ACCESS FULL
WAREHOUSE
ORDER_AGGREGATE
20224
233
16077

TABLE ACCESS FULL
WAREHOUSE
PUBLISHER_PROFILE
32
11898
142776

TABLE ACCESS BY INDEX ROWID
ESD
PUBLISHERS
1
1
25

INDEX UNIQUE SCAN
ESD
PUBLISHERS_PK

1


INDEX UNIQUE SCAN
ESD
SALES_AGENT_ID_PK

1
8

TABLE ACCESS FULL
WAREHOUSE
FULFILLMENTS
74
48036
624468

TABLE ACCESS BY INDEX ROWID
WAREHOUSE
ORDER_EXT_AGGREGATE
2
1
14

INDEX UNIQUE SCAN
WAREHOUSE
ORDERS_EXT_AGG_PK
1
1


TABLE ACCESS FULL
WAREHOUSE
CURRENCIES
2
169
1183

VIEW
WAREHOUSE

375
38293
1914650

SORT GROUP BY


375
38293
1799771

HASH JOIN


66
38293
1799771

TABLE ACCESS FULL
WAREHOUSE
RETURN_REASONS
3
696
20880

TABLE ACCESS FULL
WAREHOUSE
RETURNS
62
38293
650981

Hi Tom,

Someone asked me this today, but I haven't been able to figure out the answer.

Is there anyway you can tell whether someone analyzed a table using "DBMS_STATS" vs "ANALZE TABLE"?

My gut call is no.

Thanks,
-Peter

it won't be "definitive", but you can take an educated guess.

I'll use dbms_stats, then analyze - compare, then dbms_stats and recompare..


ops$tkyte@ORA10GR2> create table t ( x int, y varchar2(4000), z varchar2(4000) );
Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into t (x) select rownum from all_objects where rownum <= 10;
10 rows created.

ops$tkyte@ORA10GR2> commit;
Commit complete.

ops$tkyte@ORA10GR2> update t set y = rpad('*',4000,'*'), z = rpad('*',4000,'*');
10 rows updated.

ops$tkyte@ORA10GR2> commit;
Commit complete.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> create table tstats as
  2  select 'DBMS_STATS' name, a.* from user_tables a where table_name = 'T';

Table created.

ops$tkyte@ORA10GR2> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA10GR2> insert into tstats
  2  select 'ANALYZE' name, a.* from user_tables a where table_name = 'T';

1 row created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> column cname format a20
ops$tkyte@ORA10GR2> column dbms_stats format a25
ops$tkyte@ORA10GR2> column analyze format a25
ops$tkyte@ORA10GR2> select a.cname, a.val dbms_stats, b.val analyze
  2    from table( cols_as_rows( q'|select *
  3                                   from tstats
  4                                  where name = 'DBMS_STATS' |' ) ) a,
  5         table( cols_as_rows( q'|select *
  6                                   from tstats
  7                                  where name = 'ANALYZE' |' ) ) b
  8       where a.cname = b.cname
  9             and decode( a.val, b.val, 0, 1 ) = 1
 10  /

CNAME                DBMS_STATS                ANALYZE
-------------------- ------------------------- -------------------------
NAME                 DBMS_STATS                ANALYZE
EMPTY_BLOCKS         0                         3
AVG_SPACE            0                         1878
CHAIN_CNT            0                         10
AVG_ROW_LEN          8005                      8022
GLOBAL_STATS         YES                       NO

6 rows selected.

<b>dbms_stats won't gather things not used by the CBO such as chain_cnt/avg_space in particular.  If they are 0 or null - it is unlikely that analyze has been used in the past - dbms_stats would be the one.  

But global_stats for a non-partitioned table could be a "tell", avg_space, etc is not "good enough" since:</b>

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> delete from tstats where name = 'DBMS_STATS';

1 row deleted.

ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> insert into tstats
  2  select 'DBMS_STATS' name, a.* from user_tables a where table_name = 'T';

1 row created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select a.cname, a.val dbms_stats, b.val analyze
  2    from table( cols_as_rows( q'|select *
  3                                   from tstats
  4                                  where name = 'DBMS_STATS' |' ) ) a,
  5         table( cols_as_rows( q'|select *
  6                                   from tstats
  7                                  where name = 'ANALYZE' |' ) ) b
  8       where a.cname = b.cname
  9             and decode( a.val, b.val, 0, 1 ) = 1
 10  /

CNAME                DBMS_STATS                ANALYZE
-------------------- ------------------------- -------------------------
NAME                 DBMS_STATS                ANALYZE
AVG_ROW_LEN          8005                      8022
GLOBAL_STATS         YES                       NO

<b>dbms_stats won't 'reset' them, their being set indicates analyze was used at some time in the past - but since analyze and dbms_stats set and reset global_stats - that could be a "tell" for many normal tables...</b>

 

Perfect...

Thanks again Tom.

Hi

Since dbms_stats doesnt get CHAIN_CNT for us, if we want to find out if we have chained rows on whic tablas how can we achieve it? By analyzing all tablas manually and overwrtting statistics gathered by dbms_stats?

ops$tkyte@ORA10GR2> @?/rdbms/admin/utlchain

Table created.

ops$tkyte@ORA10GR2> analyze table t list chained rows;

Table analyzed.

ops$tkyte@ORA10GR2> desc chained_rows
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 OWNER_NAME                                        VARCHAR2(30)
 TABLE_NAME                                        VARCHAR2(30)
 CLUSTER_NAME                                      VARCHAR2(30)
 PARTITION_NAME                                    VARCHAR2(30)
 SUBPARTITION_NAME                                 VARCHAR2(30)
 HEAD_ROWID                                        ROWID
 ANALYZE_TIMESTAMP                                 DATE

 

Tom,

What is the reason for dbms_stats not gathering info about chained rows whereas analyze command does?

Thanks.

Tom,
How setting "mbrc" through dbms_stats and parameter db_file_multiblock_read_count influence differently.
Is that some thing like "mbrc" used during cost evaluation phase and db_file_multiblock_read_count used during execution phase.
Thanks for your time.
Ramesh.

Guess I am really tired and my eyes cant catch what I am missing here..Could anyone please help?

Issue: The index associated with Primary key Constraint never gets updated with correct statistics and hence my query is throwing wrong results

Commodity_DIM is a table with 700+ rows

<b>-- Version check </b>
SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

<b>-- Table details </b>
SQL> select table_name,num_rows,sample_size,last_analyzed from user_tables
  2  where table_name='COMMODITY_DIM'
  3  /

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----------- ---------
COMMODITY_DIM                         730         730 21-FEB-06

SQL> select count(*) from commodity_dim;

  COUNT(*)
----------
         0

<b>-- Index details </b>

SQL> select index_name,num_rows,DISTINCT_KEYS,status from user_indexes
  2  where table_name='COMMODITY_DIM';

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS STATUS
------------------------------ ---------- ------------- --------
SYS_C009071                             0             0 VALID

<b>-- Index associates to Primary Key constraint in the table </b>

SQL> select constraint_name,constraint_type from user_constraints
  2  where table_name='COMMODITY_DIM' and index_name='SYS_C009071';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C009071                    P

<b>-- and the constraint is Valid </b>

SQL> select constraint_name,constraint_type,status from user_constraints
  2  where table_name='COMMODITY_DIM' and index_name='SYS_C009071';

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
SYS_C009071                    P ENABLED

<b>-- analyzing the index - no help </b>

SQL> analyze index SYS_C009071 compute statistics;

Index analyzed.

SQL> select index_name,num_rows,DISTINCT_KEYS,status from user_indexes
  2  where table_name='COMMODITY_DIM';

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS STATUS
------------------------------ ---------- ------------- --------
SYS_C009071                             0             0 VALID

<b>-- dbms_stats ; perform compute  </b>

SQL> exec dbms_stats.gather_index_stats(ownname=>'GOLDDEMO',-
> indname=>'SYS_C009071',estimate_percent=>NULL);

PL/SQL procedure successfully completed.

SQL> select index_name,num_rows,DISTINCT_KEYS,status from user_indexes
  2  where table_name='COMMODITY_DIM';

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS STATUS
------------------------------ ---------- ------------- --------
SYS_C009071                             0             0 VALID

SQL> desc commodity_dim
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMM_DIM_PK                               NOT NULL NUMBER
 COMM_LOW_CATG_CODE                        NOT NULL VARCHAR2(10)
 COMM_LOW_CATG_DESC                                 VARCHAR2(50)
 COMM_MID_CATG_CODE                        NOT NULL VARCHAR2(50)
 COMM_MID_CATG_DESC                                 VARCHAR2(50)
 COMM_HI_CATG_CODE                         NOT NULL VARCHAR2(50)
 COMM_HI_CATG_DESC                                  VARCHAR2(50)
 COMM_ALL_CATG_CODE                                 VARCHAR2(10)
 COMM_ALL_CATG_DESC                                 VARCHAR2(50)

<b>-- Here is the problem </b>

SQL> select count(*) from commodity_dim;

  COUNT(*)
----------
         0

SQL> select count(distinct comm_dim_pk) from commodity_dim;

COUNT(DISTINCTCOMM_DIM_PK)
--------------------------
                         0

SQL> select /*+ FULL(c)*/
  2         count(distinct comm_dim_pk) from commodity_dim c;

COUNT(DISTINCTCOMM_DIM_PK)
--------------------------
                       730

<b>-- Verifying Optimizer plans  </b>

SQL> explain plan for select count(*) from commodity_dim;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 818636992

------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |             |     1 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C009071 |   730 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------


SQL> explain plan for 
  2  select /*+ FULL(c)*/
  3         count(distinct comm_dim_pk) from commodity_dim c;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 3961897095

-------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| T
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |     4 |     8   (0)| 0
|   1 |  SORT GROUP BY     |               |     1 |     4 |            |  
|   2 |   TABLE ACCESS FULL| COMMODITY_DIM |   730 |  2920 |     8   (0)| 0
---------------------------------------------------------------------------

<b>-- parameter listing , if this helps  </b>

SQL> show parameter opt

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
filesystemio_options                 string
object_cache_optimal_size            integer     102400
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
plsql_optimize_level                 integer     2 

Thanks Tom.. Looks like there is much bigger problem here.

SQL> analyze table commodity_dim validate structure;

Table analyzed.

SQL> analyze table commodity_dim validate structure cascade;
analyze table commodity_dim validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

I 'll follow-up this in metalink.

Thanks for your pointers, as always.
 

SQL> analyze table commodity_dim validate structure cascade;
analyze table commodity_dim validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file


SQL> alter table commodity_dim move;

Table altered.

SQL> select index_name from user_indexes
  2  where table_name='COMMODITY_DIM';

INDEX_NAME
------------------------------
SYS_C009071

SQL> alter index SYS_C009071 rebuild ;

Index altered.

SQL> analyze table commodity_dim validate structure cascade;

Table analyzed.

SQL> select count(*) from commodity_dim;

  COUNT(*)
----------
       730

SQL> explain plan for select count(*) from commodity_dim;

Explained.


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2809477265

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C009071 |   730 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

 

This is a great discussion, it has brought to light the concept of histograms and generating statistics overall for me. The argument for how bright that light is, is still up in the air ;)

After reading the portion, dated Jan 05, that gave examples of the differences between the different method_opt choices, I decided to experiment a little for myself. I am using a test data warehouse that is composed of only 7 tables, each of which have a primary key index and two of which have an additional one column non-unique index. I show those below.

I found my results to be un-expectantly different from yours and I wondered if you could help me to understand why. 

The first test was using 'FOR ALL INDEXED COLUMNS'. The second test was with 'FOR ALL INDEXED COLUMNS SIZE AUTO'. Finally, the third test was using 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY'. The third test gave the most confusing results to me. I will include my observation and question following the results from each test, within the spooled listing below. 

SQL> show user
USER is "DMR"

SQL> select USER || '.' || table_name, index_name, column_name
  2  from user_ind_columns;

USER||'.'||TABLE_NAME INDEX_NAME             COLUMN_NAME
--------------------- ---------------------- -------------------------
DMR.BRANCH_BO         BRANCH_BO_1            REPORT_DATE 
DMR.BRANCH_BO         BRANCH_BO_1            PRODUCT  
DMR.BRANCH_BO         BRANCH_BO_1            BRANCH_PLANT
DMR.BRANCH_BO         BRANCH_BO_1            TRX_CLASS 

DMR.INV_FACT          INV_FACT_1             REPORT_DATE  
DMR.INV_FACT          INV_FACT_1             PRODUCT 
DMR.INV_FACT          INV_FACT_1             BRANCH_PLANT 
DMR.INV_FACT          INV_FACT_1             CATEGORY      
DMR.INV_FACT          INV_FACT_1             OBSOLETE 

DMR.ITEM              ITEM_1                 PRODUCT 
DMR.ITEM              ITEM_2**               D_BUS_UNIT

DMR.ITEM_BRANCH       ITEM_BRANCH_1          PRODUCT 
DMR.ITEM_BRANCH       ITEM_BRANCH_1          BRANCH_PLANT

DMR.ITEM_FILL         ITEM_FILL_1            REPORT_DATE 
DMR.ITEM_FILL         ITEM_FILL_1            PRODUCT  
DMR.ITEM_FILL         ITEM_FILL_2**          PRODUCT 

DMR.OSB_FACT          OSB_FACT_1             REPORT_DATE 
DMR.OSB_FACT          OSB_FACT_1             PRODUCT
DMR.OSB_FACT          OSB_FACT_1             TRX_CLASS 

DMR.PROD_FACT         PROD_FACT_1            REPORT_DATE
DMR.PROD_FACT         PROD_FACT_1            PRODUCT 
DMR.PROD_FACT         PROD_FACT_1            BRANCH_PLANT

DMR.TIME              TIME_1                 CURDATE 

** signifies non unique indexes/columns

23 rows selected.

############## 
The following shows the skewdness of d_bus_unit, curdate, and obsolete
##############


SQL> select count(distinct D_BUS_UNIT)
  2  from item;
 
COUNT(DISTINCTD_BUS_UNIT)
-------------------------
                       12
 
SQL> select count(*)
  2  from item;
 
  COUNT(*)
----------
     65119
 
SQL> select D_BUS_UNIT, count(*)
  2  from item
  3  group by D_BUS_UNIT;
 
D_BUS_UNIT                       COUNT(*)
------------------------------ ----------
                                      617
Acute Care                          29298
Corporate                              10
Drop Ship Fee                           1
Freight                                 2
Min Order Qty Fee                       1
NonStock Expense Item                   8
OEM                                  1920
Patient Care                        30547
REBATE CREDIT                           1
Raw Material Samples                    2
 
D_BUS_UNIT                       COUNT(*)
------------------------------ ----------
Wound Care                           2699
 
12 rows selected.
SQL> select count(distinct obsolete)
  2  from inv_fact;
 
COUNT(DISTINCTOBSOLETE)
-----------------------
                      2
 
SQL> select count(*)
  2  from inv_fact;
 
  COUNT(*)
----------
   2743569
 
SQL> select obsolete, count(*)
  2  from inv_fact
  3  group by obsolete;
 
O   COUNT(*)
- ----------
N    2649249
Y      94320

SQL> select count(*)
  2  from time;
 
  COUNT(*)
----------
       156
 
SQL> select count(distinct curdate)
  2  from time;
 
COUNT(DISTINCTCURDATE)
----------------------
                   156

#################################

SQL> exec dbms_stats.delete_schema_stats( 'DMR' );

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, count(*)
  2  from user_tab_histograms
  3  group by table_name, column_name;

no rows selected

SQL> exec dbms_stats.gather_schema_stats
    ( ownname         =>'DMR', 
      cascade         => TRUE, 
      estimate_percent     => 10, 
      method_opt         => 'FOR ALL INDEXED COLUMNS');

PL/SQL procedure successfully completed.

SQL> l
  1  select table_name, column_name, count(*)
  2  from user_tab_histograms
  3* group by table_name, column_name
SQL> /

TABLE_NAME            COLUMN_NAME     COUNT(*)             
--------------------- --------------  ----------       
ITEM                  PRODUCT         2             
ITEM                  D_BUS_UNIT      7             
TIME                  CURDATE         76             
INV_FACT              PRODUCT         76             
INV_FACT              CATEGORY        2             
INV_FACT              OBSOLETE        2             
INV_FACT              REPORT_DATE     74             
INV_FACT              BRANCH_PLANT    25             
OSB_FACT              PRODUCT         76             
OSB_FACT              TRX_CLASS       6             
OSB_FACT              REPORT_DATE     74             
BRANCH_BO             PRODUCT         76             
BRANCH_BO             TRX_CLASS       6             
BRANCH_BO             REPORT_DATE     62             
BRANCH_BO             BRANCH_PLANT    24             
ITEM_FILL             PRODUCT         76             
ITEM_FILL             REPORT_DATE     3             
PROD_FACT             PRODUCT         76             
PROD_FACT             REPORT_DATE     44             
PROD_FACT             BRANCH_PLANT    32             
ITEM_BRANCH           PRODUCT         76             
ITEM_BRANCH           BRANCH_PLANT    33             

22 rows selected.

######### OBSERVATIONS/QUESTIONS ########
When you did this test on table "T" that was built from 
create table t as select * from all_objects
you got a 76 bucket histogram on object_id, a unique column, and a 22 bucket histogram on owner, a non-unique column.

In my sample item.product is the primary key, I would have expected more than a two bucket histogram.
ITEM.D_BUS_UNIT is non-unique and very skewed but yet I only got a 7 bucket histogram.
What could be the cause of this? It seems like I got the opposite of what your example shows.
################# END of Q1


SQL> exec dbms_stats.delete_schema_stats( 'DMR' );

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, count(*)
  2  from user_tab_histograms
  3  group by table_name, column_name;

no rows selected

SQL> exec dbms_stats.gather_schema_stats
    ( ownname        => 'DMR', 
      cascade         => TRUE, 
      estimate_percent     => 10, 
      method_opt         => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, count(*)
  2  from user_tab_histograms
  3  group by table_name, column_name;

TABLE_NAME                     COLUMN_NAME                 COUNT(*)             
------------------------------ ------------------------- ----------             
ITEM                           PRODUCT                            2             
ITEM                           D_BUS_UNIT                         2             
TIME                           CURDATE                            2             
INV_FACT                       PRODUCT                            2             
INV_FACT                       CATEGORY                           2             
INV_FACT                       OBSOLETE                           2             
INV_FACT                       REPORT_DATE                        2             
INV_FACT                       BRANCH_PLANT                       2             
OSB_FACT                       PRODUCT                            2             
OSB_FACT                       TRX_CLASS                          2             
OSB_FACT                       REPORT_DATE                        2             
BRANCH_BO                      PRODUCT                            2             
BRANCH_BO                      TRX_CLASS                          2             
BRANCH_BO                      REPORT_DATE                        2             
BRANCH_BO                      BRANCH_PLANT                       2             
ITEM_FILL                      PRODUCT                            2             
ITEM_FILL                      REPORT_DATE                        2             
PROD_FACT                      PRODUCT                            2             
PROD_FACT                      REPORT_DATE                        2             
PROD_FACT                      BRANCH_PLANT                       2             
ITEM_BRANCH                    PRODUCT                            2             
ITEM_BRANCH                    BRANCH_PLANT                       2             

22 rows selected.


##########################
No confusion here, got the exact results as your sample. This is a test db and hasn't had any sql ran for some time.
###########################

SQL> exec dbms_stats.delete_schema_stats( 'DMR' );

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, count(*)
  2  from user_tab_histograms
  3  group by table_name, column_name;

no rows selected

SQL> exec dbms_stats.gather_schema_stats
    ( ownname        =>'DMR', 
      cascade         => TRUE, 
      estimate_percent     => 10, 
      method_opt         => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

SQL> select table_name, column_name, count(*)
  2  from user_tab_histograms
  3  group by table_name, column_name;

TABLE_NAME                     COLUMN_NAME                 COUNT(*)             
------------------------------ ------------------------- ----------             
ITEM                           PRODUCT                            2             
ITEM                           D_BUS_UNIT                         2             
TIME                           CURDATE                           76             
INV_FACT                       PRODUCT                          201             
INV_FACT                       CATEGORY                           2             
INV_FACT                       OBSOLETE                           2             
INV_FACT                       REPORT_DATE                       99             
INV_FACT                       BRANCH_PLANT                      48             
OSB_FACT                       PRODUCT                          201             
OSB_FACT                       TRX_CLASS                          6             
OSB_FACT                       REPORT_DATE                      100             
BRANCH_BO                      PRODUCT                          201             
BRANCH_BO                      TRX_CLASS                          6             
BRANCH_BO                      REPORT_DATE                       89             
BRANCH_BO                      BRANCH_PLANT                      24             
ITEM_FILL                      PRODUCT                          102             
ITEM_FILL                      REPORT_DATE                        3             
PROD_FACT                      PRODUCT                          201             
PROD_FACT                      REPORT_DATE                      108             
PROD_FACT                      BRANCH_PLANT                      31             
ITEM_BRANCH                    PRODUCT                          201             
ITEM_BRANCH                    BRANCH_PLANT                      57             

22 rows selected.
################################
Here's where I really get confused. I listed some statements at the top that shows the skewdness of
item.d_bus_unit, time.curdate, and inv_fact.obsolete. item.product is the primary key of item, so it's unique, and from example I would expect the 2 bucket histogram, but d_bus_unit is non_unique and very skewed. Why no histograms here? Time.curdate is unique, 
why does it have histograms? inv_fact.obsolete is non-unique and very skewed, but yet again, no histograms. What could be causing this?

Also, I might be a little confused with the terminology and referring to that count as buckets. I saw where you were saying that 2 meant no histograms, just hi/lo endpoints, and that >2 meant histograms.

Thank you for any explanations you could give me, and let me know if you need to see other settings "maybe init.ora" or anything else.


 

Sorry, I forgot to include the banner information

SQL> select banner from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production 

sorry, again for the lack of data. I did think of something else that might impact this.
In my init.ora the compatible parameter is set to 8.1.0 BUT the optimizer_features_enable is set to 9.2.0

I just recently inherited the database so I can't give you a reason why these are the way they are.

I re-created your exact example by using the all_objects table. 
Those stats were almost exact. I think the difference with using skewonly is probably due to my db having fewer schemas than the one you were using. 

I'm still confused on some things with my specific data tables. 

The time table has a record for each day. with curdate being the actual date and primary key. The other fields are for day, month, year, day of week, day of year, etc. This is a test schema so there may not be a record for every day begining to end. Could this cause the need for histograms on that column? For example the min date could be 1/1/03 and the max 1/1/06 but it could be missing three months in between or even all of 2005. 



SQL> show user
USER is "DMR"
SQL> create table t as select * from all_objects;

Table created.

SQL> create index t_idx1 on t(object_id);

Index created.

SQL> create index t_idx2 on t(owner);

Index created.

SQL> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns', cascade => true );

PL/SQL procedure successfully completed.

SQL> col column_name for a20
SQL> select column_name, count(*)
  2  from user_tab_histograms
  3  where table_name = 'TIME';
  4  group by column_name
SQL> /

COLUMN_NAME            COUNT(*)                                                 
-------------------- ----------                                                 
OBJECT_ID                    76                                                 
OWNER                         4                                                 

SQL> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns size auto', cascade => true );

PL/SQL procedure successfully completed.

SQL> /

COLUMN_NAME            COUNT(*)                                                 
-------------------- ----------                                                 
OBJECT_ID                     2                                                 
OWNER                         2                                                 

SQL> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns size skewonly', cascade => true );

PL/SQL procedure successfully completed.

SQL> /

COLUMN_NAME            COUNT(*)                                                 
-------------------- ----------                                                 
OBJECT_ID                     2                                                 
OWNER                         4                                                 

SQL> exec dbms_stats.gather_table_stats( user, 'TIME', method_opt=> 'for all indexed columns size skewonly', cascade => true );

PL/SQL procedure successfully completed.

SQL> select column_name, count(*)
  2  from user_tab_histograms
  3  where table_name = 'TIME';
  4  group by column_name
SQL> /

COLUMN_NAME            COUNT(*)                                                 
-------------------- ----------                                                 
CURDATE                      76 

I tried to explain in the last paragraph at the top before the example. I went through and recreated your example with almost exact results. I then posted numbers from a TIME.CURDATE field that is a primary key that holds the date. In production there is a record for every day, but the test instance has some gaps. I was asking if that is why I would get histograms on that column.

If I am running dbms_stats.gather_schema_stats(user, 'TIME', method_opt=> 'for all
indexed columns size skewonly', cascade => true );
Why would a single column primary key of varchar2 datatype get a histogram generated. That could explain a lot to me. I thought if a column is unique it would only need 1 bucket. Is that not true with varchar2 columns.
If, on a smaller scale, my column looks like:
10-245
10-456
10-984
10-999
15-875
50-3000
50-450
50-451
50-780
90-800
That is a unique column but would it get histograms generated if I used "for all indexed columns size skewonly"?


On the other hand, I also have a char(1) column that is either Y or N. Most of the 100,000 records have an N but yet I only get 1 bucket for this column. I wouldn't think that is sufficient. example:
N
N
N
Y
N
N
N
N
N
Y
Y
N
N
I am only getting a 1 bucket histogram for a column like this, of course on a much larger scale.

If one value out of two possible makes up less than 5% or 10% are histograms not created?

this is good and useful but I need more detail with
example like if we are gathering stats every week
so how to make scheduling with dbms_jobs,
can you give me with complete examples
hope you got the point......

Dear Tom,

We have the following script running daily (AT 2.00 AM) to gather statistics on a specific schema.

[ dbms_stats.gather_schema_stats(ownname=>'ABCDOWNER',cascade=>true); ]

We are facing a problem with this script. The table analyze stops at a specific table
(say 'Problem_tbl') and it will not proceed any further. It gives no error message or updates any logs. It stays there for ever. When checking through OEM, or sqlplus, we can see 'last analyzed date' updated for all tables before 'Problem_tbl' table, in this schema.
This “problem_tbl” is not very big in size (only16,000 rows) and we have many big ones in this schema.

When we analyze this table individually, we got same result. It stays for ever.
[ dbms_stats.gather_table_stats(ownname=> 'ABCDOWNER', tabname=> 'PROBLEM_TBL', partname=> NULL); ].

We can analyze all other tables without any problem. To rule out any table corruption, we exported and imported this table to our development server. The data seems OK.

Once we restart database, this problem goes away for few weeks before it reappear.

Any suggestions / thoughts in this respect will be highly appreciated.


O/S: Windows 2003.
Oracle : 9i (9.2.0.6.0)

Tom,

Two questions:

Does ANALYZE or DBMS_STATS against a table, lock the table in any mode? I came across an old Oracle press documentation which said so, but could not simulate it (10g R2). Was it true in older versions of Oracle?


If the table is locked by another session, would analyze/dbms_stats wait for that lock to be released?

Thanks,

Thanks a lot Tom.

I have some related questions.

1. Why do we have parameter force for gather_table_stats? This is FALSE by default. This means, dbms_stats.gather_table_stats errors out (or does it wait?) if the table is locked?

2. no_invalidate clause -- The documentation says "Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE."
Invalidating dependent cursors means, all the queries that have used this table and are present in the shared pool will be invalidated and will be hard parsed if executed again?

Was this the default behaviour with ANALYZE?

3. DBMS_STATS.GATHER_TABLE_STATS

BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(
OWNNAME => 'TEST'
,TABNAME => 'RTEST'
,ESTIMATE_PERCENT => 1
,GRANULARITY => 'GLOBAL'
,CASCADE => FALSE
);
END;

This table has 140 partitions, 6018191841 rows and 80538932 blocks.

The above command takes 80 minutes.

If i change block_sample => TRUE, it completes in less than a minute.

I found this query (modified) to be the most time consuming one and waits on "db file scattered reads"

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring */ *
from rtest sample (1) t

When the block_sample is made TRUE, query changes to

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring */ *
from rtest sample block (1) t

and is much faster.

Tom, i found sample rows and sample block to return different amount of rows. Which is more accurate... when used within dbms_stats , and why?

Thanks a lot for your time.

Thanks,

Many thanks Tom for the very fast and precise response.

When row sampling (1%) is used, oracle reads every row from the table, but considers only 1 out of every hundred rows. In effect the whole table is read.

The difference between this and COMPUTE statistics would then be that COMPUTE considers all the rows whereas sample rows considers only 1% of the rows. But, in both cases the entire table is read.

So, we save on CPU mostly by using sample rows when compared to COMPUTE? Am i correct in saying so?


Also, the Oracle documentation says "Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated." How is random block sampling more efficient?

Iam sorry for asking too many questions, Tom, but these are doubts for which very few people have an answer to.

> Maybe they could have called it "freezing" statistics
> instead of locking them.

It's worth mentioning the different behaviour when trying to gather stats on a locked table versus the schema/db that contains it:

dellera@ORACLE10> exec dbms_stats.lock_table_stats (user, 't');

PL/SQL procedure successfully completed.

dellera@ORACLE10> exec dbms_stats.gather_table_stats (user, 't');
BEGIN dbms_stats.gather_table_stats (user, 't'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1

dellera@ORACLE10> exec dbms_stats.gather_schema_stats (user);

PL/SQL procedure successfully completed.

The first is definetely a "lock" as in "you bang your head into the locked door" ;) - as a non-native speaker would express it ...

>> But it might get entirely the wrong answer by accident since it reads 1% of the
blocks and could be subject to skew (load sorted data and it'll see lots of "the
same values" on any given block which could be unnatural)

It's also sensitive to skewed numbers of rows per block, and the 1% sample is by no means guaranteed

</code> http://oraclesponge.blogspot.com/2005/06/statistics-by-block-sampling.html <code>

Tom,

This is 10g R2.

I noticed that when gathering stats on 10g using dbms_stats (specifying partname, granularity => 'PARTITION' and estimate_percent => <some number>)
performance on IOTs are very poor when compared to tables of similar/much bigger size.

The SQL it is waiting for is

INSERT /*+ append*/ into sys.<temp table>
SELECT .... FROM <iot> sample (0.001) t
WHERE TBL$OR$IDX$PART$NUM(<iot>,0,4,0,ROWID) = :objn

It is waiting on 'db_file_sequential_reads' and in the P1 column of v$session_wait, it shows up almost every datafile that this IOT spans across. This includes other partitions too. My question is that the granularity keyword seems to have no impact on IOTs.

Is this how it should be?

This command is taking more than an hour to run. We have around 100 partitions.


Tom, whenever granularity of PARTITION is specified for a table, index or IOT, shouldnt it go against that partition alone?

In cases when the granularity => 'PARTITION', would Oracle attempt to gather global statistics also by simply rolling up from individual partitions each time?


Please let us know.

Hi Tom,
A basic question but I am bit confused about it. I create one schema with some tables in it. Do I need to gateher stats for them before using them? Or Oracle will take care once they start getting populated? I am bit confused if we do need to take the stats for empty tables. This I am asking as I was asked to do so.May be I did understand it properly and I might have to go back and confirm.

Thx
Santosh.

Hi Tom,

We are currently managing a database worth 461G worth of data. This is on the database server hosted on Linux machine having 4 CPU's , 8 disk devices supported by 4 disk controllers and 16 G of RAM.

We have been running the package to collect the statistics which will help the optimizer to generate the optimised execution plans using dbms_stats.gather_database_stats, but recently data being growing everyday to the tune of 1G the capturing of statistics has really slowed down and is taking 5 hrs to complete the job.

Create or replace procedure gather_db_stats AS
BEGIN
declare
Path VARCHAR2(100) ;
Filename VARCHAR2(100) := 'Dbmsstats.txt';
Output_file utl_file.file_type;
l_Start NUMBER;
l_tname VARCHAR2(30);
l_partition_name VARCHAR2(30);
rebuild_partition_index VARCHAR2(500);
err_num NUMBER;
err_msg VARCHAR2(500);
BEGIN
Select value into path from v$parameter where name = 'utl_file_dir';
output_file := utl_file.fopen (path,filename, 'a');

for x in (Select * from DWUSER.Rolling_tables where status = 'Y') loop
l_tname := x.table_name;
for table_specific_partitions in (Select all_ind_partitions.partition_name part_name from all_indexes,all_ind_partitions
Where all_indexes.index_name = all_ind_partitions.index_name
And all_indexes.table_name = l_tname
And all_ind_partitions.Status = 'UNUSABLE') loop
begin
l_partition_name := table_specific_partitions.part_name;
rebuild_partition_index := 'Alter table ' || l_tname || ' modify partition ' || l_partition_name || ' rebuild unusable local indexes';
execute immediate rebuild_partition_index;
EXCEPTION
When OTHERS then
err_num := SQLCODE;
err_msg := Substr(SQLERRM,1,500);
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || ' Error generated while rebuilding the UNUSABLE local indexes with an error message ' || err_msg);
utl_file.fclose(output_file);
return;
end;
end loop;
end loop;

for x in (select Username from dba_users where username not in ('SYS','SYSTEM'))
lOOP
l_start := dbms_utility.get_time;
dbms_stats.gather_schema_stats(ownname => x.username,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
degree => 16,
cascade => true,
options => 'GATHER AUTO');
utl_file.put_line (output_file,'Date : ' || to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss') || ' The total time spent to gather stats for the schema owner : ' || x.username || ' is ' || round((dbms_utility.get_time - l_start)/100,2) || 'seconds');
end loop;
END;
END;
/

We just don't have the bandwidth of 5 hrs to get this done. We have 4 partitioned tables which do have most of the data (could be 350M).

Can we use gather_table_stats instead of gather_schema_stats for only those partitions which have the LAST_ANALYZED_DATE as NULL, since our application is such which doesn't perforn any delete or insert activity once the End of day completes. We have Range partitition based on Date and have daily partitoins.

Will this help, or let us know if we can configure some of the parameter values for the gather_schema_stats to get the job done in a much faster way. We don't need to build histograms for the columns.

Thanks in anticipation

Dear Tom,

We have around 150 fast growing/changing transactional data tables in our production system.

Database is 10gR2.
Growth of these tables is from 0.1 million to 1 million in six months.
A few tables are partitioned tables.

Which way is good to analyze these fast growing tables? Dynamic sampling? or scheduling the script run using dbms_stats for these tables? Pl. suggest.

Regards.

I ran into the following problem on XE, which I find difficult to explain. I had the following query:

select count(*),urban
from case@caps s
inner join res_address@caps r ON r.res_address_id = s.res_address_id
inner join ref.good_postal_code p ON p.postal_code=r.postal_code
WHERE s.adults=1 and s.children > 0 -- is single parent
AND exists (select * from payment@caps p
WHERE s.case_id = p.case_id
AND p.payment_date between to_date('200605','yyyymm') and to_date('200606','yyyymm')-1)
GROUP BY urban

which worked fine. Note that there is one table on XE and the other two tables are remote. I then modified the query to take out the two items that restricted the query to single parents. I got a 3113 error running an explain plan. I found that adding in some restrictions (e.g. s.adults in (1,2,3)) would allow the query to run -- even though this in no way restricts the query - all records would satisfy this. I also found that running an analyze table on ref.good_postal_code allowed the query to run.

Since I wasn't sure if it was possible to delete stats I then did a CTAS to dreate a new table exactly the same as good_postal_code but with no stats. Surprisingly the query ran no problem.

I read somewhere that resource limits can generate 3113s. Is that what is happening here? i.e. the explain plan process decides that the query is going to be too big and returns the 3113?

system@oip1> create or replace procedure ods_analyze
2 is
3 begin
4 execute immediate 'execute DBMS_STATS.GATHER_SCHEMA_STATS(''ODS'')';
5 end;
6 /

Procedure created.

system@oip1> exec ods_analyze;
BEGIN ods_analyze; END;

*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "SYSTEM.ODS_ANALYZE", line 4
ORA-06512: at line 1

Tom, why did I get the error?

thanks Tom, now i got the procedure compiled.
But got the new:
system@oip1> exec ods_analyze
BEGIN ods_analyze; END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 7649
ORA-06512: at "SYS.DBMS_STATS", line 7779
ORA-06512: at "SYS.DBMS_STATS", line 7830
ORA-06512: at "SYS.DBMS_STATS", line 7809
ORA-06512: at "SYSTEM.ODS_ANALYZE", line 4
ORA-06512: at line 1


system@oip1> exec DBMS_STATS.GATHER_SCHEMA_STATS('ODS');

PL/SQL procedure successfully completed.

But i could do it directly.

Hi Tom,

1. Can a table be ANALYZE during (to collect statistics) the DML (INSERT,
UPDATE, DELETE) operations?

2. If the column in WHERE caluse has an index on it, whether the statistics are
much concerns to use the index during DMLs?

3. Having statistics and not having statisttics will affect the use of the
index during DMLs? how?

4. If we use (within the same database) ANALYZE for few tables and DBMS_STATS
package to gather statistics for some other tables, will there be a performance
issue by using these two together?


Thanks,
Ravi

Hi Tom,

Below is the test case which shows the wrong information with DBMS_STATS in Oracle 10g R2. It has the correct information in Oracle 9i R2. As per metalink, CBO will get exact information when we will go by COMPUTE STATISTICS where NUM_ROWS and SAMPLE_SIZE columns will point same values. And, I also checked out on query execution.

SQL> create table t2 as select * from t1;

Table created.

SQL> select count(1) from t2;

  COUNT(1)
----------
    735392

SQL>  exec DBMS_STATS.GATHER_TABLE_STATS(user, TABNAME => 'T2',GRANULARITY => 'DEFAULT' ,METHOD_OPT => 'FOR COLUMNS');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, sample_size from user_tables where table_name='T2';

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE
------------------------------ ---------- -----------
T2                                 735392       51451

SQL> REM Here is the wrong number of rows compare to 735392 and 735392.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> select table_name, num_rows, sample_size from user_tables where table_name='T2';

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE
------------------------------ ---------- -----------
T2                                 735392      735392

SQL> REM Here all values are accurate.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>

======================
Oracle 9i R2
======================

SQL> select count(1) from t1;

  COUNT(1)
----------
  27525120

Elapsed: 00:01:07.05
SQL>  exec DBMS_STATS.GATHER_TABLE_STATS(user, TABNAME => 'T1',GRANULARITY => 'DEFAULT' ,METHOD_OPT => 'FOR COLUMNS', DEGREE=>8);

PL/SQL procedure successfully completed.

Elapsed: 00:00:25.22
SQL> select table_name, num_rows, sample_size from user_tables where table_name='T1';

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE
------------------------------ ---------- -----------
T1                               27525120    27525120

Elapsed: 00:00:00.08
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

Elapsed: 00:00:00.08
albprod@ALBBPM>


Could you please, elaborate the difference of DBMS_STATS in Oracle 10g ?

Thanking you.

Regards,
Suvendu

 

that is not "wrong"

That is "correct" actually.

If you check out the docs for dbms_stats, you'll find the specification of gather table stats in 10gr2 is:

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);


and that:

ops$tkyte%ORA10GR2> select dbms_stats.get_param( 'estimate_percent' ) from dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
-------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE



you are using the auto_sample_size, therefore, it is free to sample.


It performed as documented - but things change (and the fact that the row count is pretty much dead on in 10gr2....) 

Hello,

This query is taking more than 12 hrs to execute. Can you let me know which areas I need to look to tune.

Thanks
Anil P

SQL> select z.IND_ID,
  2         z.BLK_OPEN_DT,
  3         z.ORG_SRC_CD,
  4         z.PRM_SRC_CD,
  5         c.calendar_month_name_abbr ACC_OPEN_MTH_ABR,
  6         c.calendar_month ACC_OPEN_MTH,
  7         c.calendar_year ACC_OPEN_YEAR
  8    from mrtlookup.calendar_date c,
  9         (select ind.individual_id IND_ID,
 10                 x.BLK_OPEN_DT,
 11                 ind.original_source_cd ORG_SRC_CD,
 12                 ind.primary_source_cd PRM_SRC_CD
 13            from mrtcustomer.individual ind,
 14                 (select prp.individual_id IND_ID, min(prp.open_dt) BLK_OPEN_DT
 15                    from mrtcustomer.proprietary_account prp,
 16                         mrtcustomer.individual_segment isg,
 17                         (select (c.calendar_month_end_dt + 1) BLK_OPEN_START_DT,
 18                                 (select max(c.calendar_month_end_dt)
 19                                    from mrtlookup.calendar_date c,
 20                                         (SELECT PERIOD_DATES.WEEK_END_DT computation_dt
 21                                            FROM MRTLOOKUP.PERIOD_DATES PERIOD_DATES) e
 22                                   where c.calendar_month_end_dt <=
 23                                         e.computation_dt) END_DT
 24                            from mrtlookup.calendar_date c
 25                           where c.calendar_date =
 26                                 (select min(ind.individual_create_dt)
 27                                    from mrtcustomer.individual ind
 28                                   where ind.original_source_cd = 'TEL')) y
 29                   where y.BLK_OPEN_START_DT <= prp.open_dt
 30                     and y.END_DT >= prp.open_dt
 31                     and prp.division_ind is not null
 32                     and isg.individual_id = prp.individual_id
 33                     and isg.marketing_segment_cd = 'MAILABLE'
 34                   group by prp.individual_id) x
 35           where ind.primary_source_cd = 'GEC'
 36             and ind.individual_id = x.IND_ID) z
 37   where c.calendar_date = z.BLK_OPEN_DT;

1143156 rows selected.



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=204854 Card=37843
          Bytes=2194894)

   1    0   HASH JOIN (Cost=204854 Card=37843 Bytes=2194894)
   2    1     TABLE ACCESS (FULL) OF 'CALENDAR_DATE' (TABLE) (Cost=68
          Card=14640 Bytes=278160)

   3    1     NESTED LOOPS (Cost=204785 Card=37843 Bytes=1475877)
   4    3       VIEW (Cost=166771 Card=37843 Bytes=832546)
   5    4         SORT (GROUP BY) (Cost=166771 Card=37843 Bytes=200567
          9)

   6    5           NESTED LOOPS (Cost=58475 Card=37843 Bytes=2005679)
   7    6             NESTED LOOPS (Cost=27419 Card=31019 Bytes=108566
          5)

   8    7               TABLE ACCESS (BY INDEX ROWID) OF 'CALENDAR_DAT
          E' (TABLE) (Cost=2 Card=1 Bytes=16)

   9    8                 INDEX (UNIQUE SCAN) OF 'PK_CD' (INDEX (UNIQU
          E)) (Cost=1 Card=1)

  10    9                   SORT (AGGREGATE)
  11   10                     PARTITION RANGE (ALL) (Cost=107721 Card=
          1302415 Bytes=15628980)

  12   11                       TABLE ACCESS (FULL) OF 'INDIVIDUAL' (T
          ABLE) (Cost=107721 Card=1302415 Bytes=15628980)

  13    7               TABLE ACCESS (FULL) OF 'PROPRIETARY_ACCOUNT' (
          TABLE) (Cost=27417 Card=31019 Bytes=589361)

  14   13                 SORT (AGGREGATE)
  15   14                   NESTED LOOPS (Cost=78 Card=732 Bytes=11712
          )

  16   15                     TABLE ACCESS (FULL) OF 'PERIOD_DATES' (T
          ABLE) (Cost=9 Card=1 Bytes=8)

  17   15                     TABLE ACCESS (FULL) OF 'CALENDAR_DATE' (
          TABLE) (Cost=69 Card=732 Bytes=5856)

  18    6             PARTITION RANGE (ITERATOR) (Cost=1 Card=1 Bytes=
          18)

  19   18               INDEX (UNIQUE SCAN) OF 'PK_ISEG_A' (INDEX (UNI
          QUE)) (Cost=1 Card=1 Bytes=18)

  20    3       PARTITION RANGE (ITERATOR) (Cost=2 Card=1 Bytes=17)
  21   20         TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'INDIVIDUAL'
          (TABLE) (Cost=2 Card=1 Bytes=17)

  22   21           INDEX (UNIQUE SCAN) OF 'PK_IND' (INDEX (UNIQUE)) (
          Cost=1 Card=1)






Statistics
----------------------------------------------------------
        274  recursive calls
          4  db block gets
   14526033  consistent gets
    1299590  physical reads
        232  redo size
   38571751  bytes sent via SQL*Net to client
     838832  bytes received via SQL*Net from client
      76212  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
    1143156  rows processed
 

Hello Tom,
Yes it's latest and realistic status. And it's a production db on Oracle 10g. Here's the latest explain plan output.

This query is been generated by a cognos tool.

Can you tell me which areas needs to be looked into?

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3257 Card=1 Bytes=
103)

1 0 FILTER
2 1 SORT (GROUP BY) (Cost=3257 Card=1 Bytes=103)
3 2 MERGE JOIN (CARTESIAN) (Cost=1344 Card=445 Bytes=45835
)

4 3 NESTED LOOPS (Cost=1281 Card=1 Bytes=77)
5 4 NESTED LOOPS (Cost=1280 Card=1 Bytes=59)
6 5 NESTED LOOPS (Cost=369 Card=455 Bytes=15925)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'CALENDAR_DAT
E' (TABLE) (Cost=2 Card=1 Bytes=16)

8 7 INDEX (UNIQUE SCAN) OF 'PK_CD' (INDEX (UNIQU
E)) (Cost=1 Card=1)

9 8 SORT (AGGREGATE)
10 9 TABLE ACCESS (FULL) OF 'INDIVIDUAL' (TAB
LE) (Cost=1847 Card=81250 Bytes=975000)

11 6 TABLE ACCESS (FULL) OF 'PROPRIETARY_ACCOUNT' (
TABLE) (Cost=367 Card=455 Bytes=8645)

12 11 SORT (AGGREGATE)
13 12 NESTED LOOPS (Cost=66 Card=740 Bytes=11840
)

14 13 TABLE ACCESS (FULL) OF 'PERIOD_DATES' (T
ABLE) (Cost=2 Card=1 Bytes=8)

15 13 TABLE ACCESS (FULL) OF 'CALENDAR_DATE' (
TABLE) (Cost=64 Card=740 Bytes=5920)

16 5 TABLE ACCESS (BY INDEX ROWID) OF 'INDIVIDUAL' (T
ABLE) (Cost=2 Card=1 Bytes=24)

17 16 INDEX (UNIQUE SCAN) OF 'PK_IND' (INDEX (UNIQUE
)) (Cost=1 Card=1)

18 4 INDEX (UNIQUE SCAN) OF 'PK_ISEG' (INDEX (UNIQUE))
(Cost=1 Card=1 Bytes=18)

19 3 BUFFER (SORT) (Cost=1344 Card=14792 Bytes=384592)
20 19 TABLE ACCESS (FULL) OF 'CALENDAR_DATE' (TABLE) (Co
st=63 Card=14792 Bytes=384592)


Thanks
Anil

Hi Tom,

I am doing a massif initial load. During this initial load I am inserting into several tables and in the same time I am selecting from theses tables. This is why I kept indexes usable. In addition, when I am in 50% of my initial load I compute statistics using:

DBMS_STATS.GATHER_SCHEMA_STATS (shema_name, 30)

I wonder if you will advise me to do this or not?

I want to suppress this call to DBMS_STATS but want to be sure that this will improve performance of my process

Thanks for your advise

Hi Tom,


I have a question for you regarding the GATHER STALE option of DBMS_STATS. Here is a quick little background about my database setup: I have 4 very large composite-partitioned tables -- with 50 partitions and 64 (hash) sub-partitions in each range partition. Each one of these 4 tables has on an average around 120 Million rows, with somewhere around 250 columns each.

Now, the data in these 50 partitions is pretty skewed, in terms of no. of records in them. And each one of these partitions has its own peculiar update cycle -- some states are updated more (much more) than others and some are inserted more than others -- Now what I want to do is that instead of indiscriminately issuing
"Exec dbms_stats.gather_schema_stats( user, options => 'gather stale' );" I want to get just the listing of the skewed partitions within the tables and selectively analyze these partitions which require them the most. Is there a system table which can give me the information about the particular partitions which are STALE in a table??


Thanks so much for your help,
Neeraj

Sir,
Please clarify my doubt.

q1) Why is CUSLED_KEY_1 Index being used instead of CUSLED_PK Primary Key index? 
q2) Why its filtering ACCOUNT and AGE_PERIOD instead of using the PK?
q3) Or whatever is happening is correct? 
But when I disable CUSLED index it uses CUSLED_PK index. 


SQL> select index_name, substr(column_name,1,40) cname from all_ind_columns
  2  where table_name = 'CUSLED';

INDEX_NAME                     CNAME
------------------------------ ----------------------------------------
CUSLED_PK                      LEDGER
CUSLED_PK                      ACCOUNT
CUSLED_PK                      AGE_PERIOD
CUSLED_PK                      DOC_REF
CUSLED_KEY_1                   LEDGER
CUSLED_KEY_1                   DOC_REF
CUSLED_KEY_2                   LEDGER
CUSLED_KEY_2                   ACCOUNT_REF
CUSLED_KEY_3                   LEDGER
CUSLED_KEY_3                   CHEQUE_NO
CUSLED_ACCOUNT_FK              ACCOUNT

SQL> SELECT *
  2  FROM CUSLED
  3  WHERE CUSLED.LEDGER = 'UD'
  4  AND CUSLED.ACCOUNT = '0000070101'
  5  AND CUSLED.AGE_PERIOD = 'A510'
  6  AND CUSLED.DOC_REF = '30001013'
  7  AND CUSLED.REC_STA != 'X';

Execution Plan
----------------------------------------------------------
Plan hash value: 2871354732

--------------------------------------------------------------------------------
------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |

--------------------------------------------------------------------------------
------------

|   0 | SELECT STATEMENT            |              |     1 |   405 |     2   (0)
| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| CUSLED       |     1 |   405 |     2   (0)
| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | CUSLED_KEY_1 |     3 |       |     1   (0)
| 00:00:01 |

--------------------------------------------------------------------------------
------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CUSLED"."ACCOUNT"='0000070101' AND "CUSLED"."AGE_PERIOD"='A510' A
ND

              "CUSLED"."REC_STA"<>'X')
   2 - access("CUSLED"."LEDGER"='UD' AND "CUSLED"."DOC_REF"='30001013')



SQL> ALTER INDEX CUSLED_KEY_1 UNUSABLE;

Index altered.

SQL> SELECT *
  2  FROM CUSLED
  3  WHERE CUSLED.LEDGER = 'UD'
  4  AND CUSLED.ACCOUNT = '0000070101'
  5  AND CUSLED.AGE_PERIOD = 'A510'
  6  AND CUSLED.DOC_REF = '30001013'
  7  AND CUSLED.REC_STA != 'X';

Execution Plan
----------------------------------------------------------
Plan hash value: 4156143435

--------------------------------------------------------------------------------
---------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

--------------------------------------------------------------------------------
---------

|   0 | SELECT STATEMENT            |           |     1 |   405 |     2   (0)| 0
0:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| CUSLED    |     1 |   405 |     2   (0)| 0
0:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | CUSLED_PK |     1 |       |     1   (0)| 0
0:00:01 |

--------------------------------------------------------------------------------
---------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CUSLED"."REC_STA"<>'X')
   2 - access("CUSLED"."LEDGER"='UD' AND "CUSLED"."ACCOUNT"='0000070101' AND
              "CUSLED"."AGE_PERIOD"='A510' AND "CUSLED"."DOC_REF"='30001013')


Thanks 

Still awaiting for your reply

Tom,

Thanks for your answer to my question -- titled "GATHER STALE" asked on September 15, 2006. Breifly searching through your website, I came across this procedure that lists the STALE TABLES -- changed since the last-analyzed date -- but I am more interested in finding out the STALE PARTITIONS rather than just the STALE TABLE. How can one display the names of the STALE PARTITIONS using the following script.

declare
l_objList dbms_stats.objectTab;
begin
dbms_stats.gather_schema_stats
( ownname => USER,
options => 'LIST STALE',
objlist => l_objList );
for i in 1 .. l_objList.count
loop
dbms_output.put_line( l_objList(i).objType );
dbms_output.put_line( l_objList(i).objName );
end loop;
end;





Appreciate Your Help,
Neeraj

Thanks Tom, Yes ..it was my shortsighted search on the topic!

Neeraj Nagpal

Tom,

Very useful - Thank You.

I have seen in your examples the use of:
analyze table t compute statistics for table for all indexes for all indexed columns

but then you'll say something like
In real life, however, one would want to use DBMS_STATS instead.

Could you please supply the equivalent statement using dbms_stats ?

Thanks

Tom,

Somewhere up there in this thread, you said “certain variations of analyze can lock (to validate structures and such), but dbms_stats just runs queries basically." 

However while running the following command, 

        DBMS_STATS.gather_table_stats (
        ownname=> 'ADBPROD',
        tabname=> ‘OP_LAYER’,
        estimate_percent=> dbms_stats.auto_sample_size,
        partname=> ‘CALIFORNIA’,
        granularity=> 'PARTITION');

I noticed intermittently that the table 'OP_LAYER' was being locked by the Gather_Table_Stats command, and a few users, who wanted to acquire locks on this table, had to wait.

See the screen dump.

(Pardon the bad text formatting here …)

SQL> Select a.username, b.owner||'.'||b.name,b.type, b.mode_held,b.mode_requested 
         from v$session a, dba_ddl_locks b
         where a.sid=b.session_id
        and  b.mode_held = 'None' and b.mode_requested <> 'None'
11:29:19   5  /  

USERNAME                       B.OWNER||'.'||B.NAME
------------------------------ -------------------------------------------------------------
TYPE                                     MODE_HELD MODE_REQU
---------------------------------------- --------- ---------
ADBPROD                        ADBPROD.OPINION_LAYER
Table/Procedure/Type                     None      Share  &#61663; (<-Share lock requested on the table)

ADBPROD                        ADBPROD.OPINION_LAYER
Table/Procedure/Type                     None      Share &#61663; (<-Share lock requested on the table)


ADBPROD                        ADBPROD.OPINION_LAYER
Table/Procedure/Type                     None      Share &#61663; (<-Share lock requested on the table)


ADBPROD                        ADBPROD.OPINION_LAYER
Table/Procedure/Type                     None      Share &#61663; (<-Share lock requested on the table)


ADBPROD                        ADBPROD.OPINION_LAYER
Table/Procedure/Type                     None      Share &#61663; (<-Share lock requested on the table)




11:37:24 ADBPROD@PROD SQL> Select a.username, b.owner||'.'||b.name,b.type, b.mode_held,b.mode_reques
ted
12:02:21   2  from v$session a, dba_ddl_locks b
12:02:21   3  where a.sid=b.session_id
12:02:21   4  and  b.mode_held = 'None' and b.mode_requested <> 'None'
12:02:21   5  /

no rows selected  <-NO LOCKS ON THE TABLE, AFTER A SHORT INTERVAL 

Could you please tell me that is it one of those “certain variations” of the DBMS_STATS which can lock the table being analyzed???





 

Well I did check the blockers/waiters using the query below
, but got back -- no rows returned --

SELECT sid, DECODE( block, 0, 'NO', 'YES' ) BLOCKER, DECODE( request, 0, 'NO', 'YES' ) WAITER FROM v$lock WHERE request > 0 OR block > 0 ORDER BY block DESC;

Although I did'nt see any blockers OR waiters in the database at that time, the users were still getting breifly locked (like for 2-3 minutes and then released) on the table OP_LAYER. Not sure -- why. And I know that only process accessing the table 'OP_LAYER' was the DBMS_STATS.

HI TOM,
We are analyzing the schema in following way :
for rec_table_name in (
select table_name from user_tables
minus
select table_name from user_external_tables
) loop
dbms_stats.gather_table_stats('XXX',''||rec_table_name.TABLE_NAME||'',estimate_percent=>40,DEGREE=>4,CASCADE => TRUE);
end loop;

Now according to you if I put commit after each Loop iteration , than can it reduce overhead on UNDO/REDO/TEMP tablespace or its nothing to relate with it.

according to me, it would be a waste of time, since dbms_stats already commits.

ops$tkyte%ORA10GR2> create table t1 ( x int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> rollback;

Rollback complete.

ops$tkyte%ORA10GR2> select * from t1;

         X
----------
         1



and there is no such thing as "overhead on redo/undo/temp" - I am not familar with what you might be thinking. 

Hi Tom ,
I need your help on this . As I have mentioned in my above question .We have one Analyze Schema stats process in Production system since 3 years . Now suddenly this process fails with below error :
Error While Analyzing Schema tables :

declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$"
too small
ORA-06512: at line 12

Can you please tell me on it , that during stats gathering large amount of Redo generated . I am not sure but I am not been able to find any conviencing document on it. And the error is typical snapshot too old error , how it came during Analyze table stats ?

Parallel with this process no other process is executing .


Regards
Maulesh

We are handling massive volumes of data in our warehouse. Most of the fact tables will be appended with more than 200 million rows each month, and in the steady state the facts will have close to 2.5 billion rows.

The end-users of our tables are some analysts -- so to give these guys a low downtime (or, sleepless nights - speaking dysphemistically), we came up with the following strategy:

* We'll have two sets of tables
-> Temporary tables - in which we will load each month's data; these tables will not be partitioned
-> Final tables - each month we'll append Temporary tables' content to these tables through partition-exchange; Final tables will we range-partitioned on Month_ID

* As indexing is a time-intensive process -- we will build all the indexes in the world on the set of Temporary tables and have 'including indexes' clause in the partition-exchange statement

Now, here's where I am stumped (assuming you have played cricket)
* As my tables are pretty big in size (though their sheer size makes them ugly), and they will be used by analysts -- it's almost mandatory to gather statistics for these tables. Unfortunately, there is no option for a clause like 'including statistics' in partition-exchange statement -- by which I'll gather statistics for the Temporary table and carry the stats to the Final table (it doesn't make much sense either). So, the only option I am left with is to dump data in the Final table, and analyze the entire Final table for statistics -- but with 2.5 billion rows it'll be eons before it gets analyzed. Or is there a way of escaping this?, which'll help me realize my sadistic objectives.

Thanks Tom.


* We load each month's data in a Temporary table
* We build all the indexes we want on the temporary table
* Partition-exchange Temporary table with the Final table

from above:

...
you can import/export/set statistics directly with dbms_stats
.....
                      ^^^


the partition stats will go, you'll just roll up the global stats you want to roll up - using your knowledge of the data



ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int not null,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2> insert into t select to_date( '12-mar-2003','dd-mon-yyyy')+mod(rownum,3), user_id, username from all_users;

31 rows created.

ops$tkyte%ORA10GR2> create index t_idx on t(x) local;

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
        31

ops$tkyte%ORA10GR2> select partition_name, num_rows from user_tab_partitions where table_name =
  2  'T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
JUNK                                   10
PART1                                  10
PART2                                  11

ops$tkyte%ORA10GR2> select num_rows from user_indexes where table_name = 'T';

  NUM_ROWS
----------
        31

ops$tkyte%ORA10GR2> select partition_name, num_rows from user_ind_partitions where index_name =
  2  'T_IDX';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
JUNK                                   10
PART1                                  10
PART2                                  11

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table temp (dt date, x int not null, y varchar2(30) );

Table created.

ops$tkyte%ORA10GR2> insert into temp
  2  select to_date( '12-mar-2003'), user_id, username from all_users;

31 rows created.

ops$tkyte%ORA10GR2> create index temp_idx on temp(x);

Index created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'TEMP' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t
  2  exchange partition part1
  3  with table temp
  4  including indexes
  5  without validation
  6  /

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for x in (select sum(num_rows) nrows from user_tab_partitions where table_name = 'T')
  3          loop
  4                  dbms_stats.set_table_stats( user, 'T', numrows => x.nrows );
  5                  dbms_stats.set_index_stats( user, 'T_IDX', numrows => x.nrows );
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
        52

ops$tkyte%ORA10GR2> select partition_name, num_rows from user_tab_partitions where table_name = 'T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
JUNK                                   10
PART1                                  31
PART2                                  11

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select num_rows from user_indexes where table_name = 'T';

  NUM_ROWS
----------
        52

ops$tkyte%ORA10GR2> select partition_name, num_rows from user_ind_partitions where index_name =
  2  'T_IDX';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
JUNK                                   10
PART1                                  31
PART2                                  11
 

Tom,

Consider this scenario:

My table has 12 partitions, with ~200 million rown in each partition. I have statistics gathered for each individual partition, but don't have the Global statistics for the table.

Can I expect to achieve a significant increase in query performance with Global indexes gathered?

Or, gathering Global stats will only be too consuming without much improvement in query response (as I already have stats for individual partitions)?

I understand this question is a bit abstract, and that I can test it myself -- but I don't enough data or resources to test it.

Please share your invaluable views.
Thanks

...
Can I expect to achieve a significant increase in query performance with Global 
indexes gathered? 

....

one of three things can be expected:

a) increase in performance
b) decrease in performance (unlikely, but possible)
c) no change at all in performance

in other words - IT DEPENDS.

if your queries all include the partition key in such a way that the optimizer knows "only one partition will be hit", local statistics will be used to optimize that query.

global statistics may never be used (meaning C is likely)

on the other hand, if you run queries that do not use partition elimination down to a single partition - then global statistics will be used...


Now, are you sure you don't have SOME global stats :)


ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select to_date( '12-mar-2003','dd-mon-yyyy')+mod(rownum,3), object_id, object_name
  3    from all_objects;

50122 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', partname => 'PART1', granularity => 'PARTITION' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------


ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', partname => 'PART2', granularity => 'PARTITION' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------


ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', partname => 'JUNK', granularity => 'PARTITION' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
     50122

ops$tkyte%ORA10GR2> select partition_name, num_rows from user_tab_partitions where table_name = 'T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
JUNK                                16707
PART1                               16707
PART2                               16708

 

"What gets us into trouble is not what we don't know;
it's what we know for sure that just ain't so."

-- Twain

Sir,

Today I was reading a chapter "The Clustering Factor" written by Mr. Jonathan Lewis in his new book (Chapter - 5) and have found one function "sys_op_countchg ()". I have found this explanation rather opaque due to my poor knowledge on Oracle. Could you kindly explain its usage with your inimitable style for us?
Why and How to use this function in live or production database Oracle 9i and 10g?

Hi TOM,
Thanks for the support and sharing knowledge about Oracle. We have implemented Schedule Job for statistics gathering . For which we are using Analyze statement .
But currently we come to know that values for Last_analyzed is not geeting updated daily and it is just remain as it is . Can you please help me on this ?

Thanks

Oracle 10G
Solaris 9


Regarding the "out of the box" gathering of system statistics, I'm wondering why this query is so slow. It takes well over 1 minute to run and there's not that many connections/processes (< 500).

select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time,
substr(sa.sql_text,1,540) txt
from v$process p, v$session s, v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
order by 1,2,7



The explain plan is:
====================

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1091464685

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 738 | 100 (4)| 00:00:02 |
| 1 | SORT ORDER BY | | 1 | 738 | 100 (4)| 00:00:02 |
|* 2 | HASH JOIN OUTER | | 1 | 738 | 99 (4)| 00:00:02 |
| 3 | NESTED LOOPS | | 1 | 204 | 73 (2)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 191 | 49 (3)| 00:00:01 |
|* 5 | FIXED TABLE FULL | X$KSUSE | 1 | 142 | 24 (0)| 00:00:01 |
|* 6 | FIXED TABLE FULL | X$KSUPR | 1 | 49 | 24 (0)| 00:00:01 |
|* 7 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 13 | 24 (0)| 00:00:01 |
|* 8 | VIEW | GV$SQLAREA | 100 | 53400 | 25 (4)| 00:00:01 |
|* 9 | FILTER | | | | | |
| 10 | SORT GROUP BY | | 100 | 72300 | 25 (4)| 00:00:01 |
| 11 | FIXED TABLE FULL | X$KGLOB | 100 | 72300 | 24 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("S"."KSUSESQL"="ADDRESS"(+) AND "S"."KSUSESQH"="HASH_VALUE"(+))
4 - access("ADDR"="S"."KSUSEPRO")
5 - filter("S"."KSUUDLNA" IS NOT NULL AND "S"."INST_ID"=:B1 AND
BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
6 - filter("INST_ID"=:B1 AND BITAND("KSSPAFLG",1)<>0)
7 - filter("S"."KSUSEOPC"="E"."INDX")
8 - filter("INST_ID"(+)=:B1)
9 - filter(SUM(DECODE("KGLOBT09",0,"KGLOBT02",0))<>0)

30 rows selected.

SQL> select count(*) from X$KSUSE;

COUNT(*)
----------
1200

SQL> select count(*) from X$KSUPR;

COUNT(*)
----------
700

SQL> select count(*) from X$KSLED;

COUNT(*)
----------
812


The last count(*) took a long time!
====================================

SQL> set timing on
SQL> select count(*) from X$KGLOB;

COUNT(*)
----------
933900

Elapsed: 00:01:08.83

Hi Tom,

Some of my team members think that we should have a wrapper (package) that would call DBMS_STATS to gather statistics instead of using the DBMS_STATS directly. What is your opinion about this approach.


Regards

Tom,
i used dbms_stats.gather_database_stats as :
begin sys.DBMS_STATS.GATHER_DATABASE_STATS (); end;

and found out some of the schemas ware not gathered.

does it gather all the schemas in the database as supposed?

TIA

it's 9.2.0.7
the job completed via dbms_job and also checked no errors in alert.log. the job have a few time after Feb 25.

the schemas ware not completed:
SQL> select owner, count(*) not_c from dba_tables where last_analyzed <'24-FEB-07' group by owner;

OWNER NOT_C
------------------------------ ---------
BIM 61
BIS 102
ENI 46
FII 76
GL 1
HRI 53
ISC 10
JTF 6
OPI 24
OSM 1
POA 36
PORTAL30_DEMO 1
SYS 316

13 rows selected.

the follwoing t_count is the total no. of the tables owned by the schema.

SQL> select b.owner, count(*) t_count from dba_tables b where b.owner in (select a.owner from dba_ta
bles a
2 where a.last_analyzed <'24-FEB-07') group by b.owner;

OWNER T_COUNT
------------------------------ ---------
BIM 261
BIS 460
ENI 140
FII 278
GL 176
HRI 185
ISC 37
JTF 403
OPI 122
OSM 198
POA 138
PORTAL30_DEMO 9
SYS 350

13 rows selected.

SQL>

those are not LOBs. not sure why dbms_stats.gather_database_stats does not pick up those.
analyze table does work for those...

SQL> select b.owner, b.object_name, b.object_type from
2 dba_objects b, dba_tables a
3 where a.owner=b.owner
4 and b.object_name=a.table_name
5 and a.last_analyzed <'24-FEB-07' and a.owner='SYS'
6 and rownum<3
7 /

OWNER OBJECT_NAME OBJECT_T
-------- ------------------------- --------
SYS FILE$ TABLE
SYS BOOTSTRAP$ TABLE

SQL> desc BOOTSTRAP$
ERROR:
ORA-04043: object BOOTSTRAP$ does not exist


SQL> desc sys.BOOTSTRAP$
Name Null? Type
----------------------------------------------------- -------- -----------------------
LINE# NOT NULL NUMBER
OBJ# NOT NULL NUMBER
SQL_TEXT NOT NULL VARCHAR2(4000)

SQL> select last_analyzed from dba_tables where table_name='BOOTSTRAP$';

LAST_ANAL
---------
07-OCT-06

SQL> analyze table sys.BOOTSTRAP$ compute statistics;

Table analyzed.

SQL> select last_analyzed from dba_tables where table_name='BOOTSTRAP$';

LAST_ANAL
---------
27-FEB-07

SQL>

SQL> select job
2 , log_user
3 , priv_user
4 , schema_user
5 , last_date
6 , last_sec
7 , this_date
8 , this_sec
9 , next_date
10 , next_sec
11 , total_time
12 , broken
13 , interval
14 , failures
15 , what
16 from sys.dba_jobs where log_user='SYS'
17 /

LOG PRIV SCHEMA
JOB USER USER USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC
------- -------- -------- -------- --------- -------- --------- --------
TOTAL
NEXT_DATE NEXT_SEC TIME B INTERVAL FAIL WHAT
--------- -------- ------ - -------------------- ---- --------------------

40704 SYS SYS SYS 26-FEB-07 19:33:42
04-MAR-07 19:33:42 41484 N sysdate + 6 0 begin sys.DBMS_STATS
.GATHER_DATABASE_STA
TS (); end;

use portal30_demo as example:
SQL> select table_name ,last_analyzed from dba_tables where owner='PORTAL30_DEMO';

TABLE_NAME LAST_ANAL
------------------------------ ---------
EMP 27-FEB-07
DEPT 27-FEB-07
MLOG$_EMP 14-NOV-04
RUPD$_EMP
EMP_SNAPSHOT 27-FEB-07
PEOPLE_INFO$ 27-FEB-07
TASK_CATEGORY$ 27-FEB-07
TASK_DEVELOPER$ 27-FEB-07
TASK$ 27-FEB-07

there are two tables were not picked by dbms_stats.gather_database_stats.

Hi Tom,

Thanks for sharing your vast experience and knowledge with us.

From discussions about dynamic sampling above, I gathered the following information-
1)In 9i we have to collect the stats ourself,
2)In 10g, Oracle calc the stats for us (when we first fire the query during its hard parse)

Assuming point 2 above is right, I tried a test in 10g R2
But what I have noticed is dynamic sampling is not kicked in automatically as you can see in Area 1 below ?
and other issues in area 2 and area 3

10g Rel 2
=========================================================
AREA 0)
SQL> create table temp
2 (a number,
3 b number);

Table created.

SQL> declare
2 begin
3 for i in 1..100000
4 loop
5 insert into temp values(i,i+1);
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.
============================================================
AREA 1)

SQL> explain plan for select a from temp;
Explained.

SQL> select * from table( dbms_xplan.display );
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEMP |
----------------------------------
Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
12 rows selected.
===========================================================
So in Above it is not using dynamic_sampling automatically because it is using RBO and not CBO

But if I hint it to use dynamic sampling (as follows) then it shift to CBO and Rows are guesses nearly Right
=======================================
AREA 2)
SQL> explain plan for select /*+ dynamic_sampling (t 10) */ a from temp;

Explained.

SQL> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------

----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 83932 | 1065K| 57 |
| 1 | TABLE ACCESS FULL| TEMP | 83932 | 1065K| 57 |
----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

11 rows selected.
========================================================
Now dynamic sampling is done in previous step so it should hard parsed and plan is ready in shared pool. This same plan should be used if I fire syntactically same query again but again it is going back to RBO (as follows)
===============================================
AREA 3)
SQL> explain plan for select a from temp;

Explained.

SQL> select * from table( dbms_xplan.display );

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEMP |
----------------------------------

Note
-----
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)

12 rows selected.
================================================

Tom,
This is 10gr2. We use the following command to gather statistics:
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'ABC',GRANULARITY=>'ALL',CASCADE
=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS size 1',DEGREE=>DBMS_STATS.AUTO_DEGREE);

The tables are in monitoring mode. We were told by Oracle support that the above method will ignore tables with stale statistics. Here is is quote from analyst:
=============
It is because some of the objects become stale and are ignored while gathering schema statistics. It is better to use
options=>'GATHER STALE' in the procedure.
=============

I always thought that if I do not mention any option, it will gather statistics for all tables in the schema, stale or not.

Thanks

Tom,

I am using the following piece of PL-SQL code to analyze a 20-column table which has ~650 million rows.

<SQL>
begin
dbms_stats.gather_table_stats(ownname => 'ops\$datamart', tabname => 'FCT_SALES', estimate_percent => 15 ,
granularity => 'ALL', cascade => true, force => true);
end;
/
</SQL>

The problem is that the analyze process is taking close to 21 hours!, although we have a very good architecture.

What would be your take on this?

Thanks in anticipation

Hi Tom,

We have one partitioned table with 3986259 rows, we have script scheduled (twice a week) to gather stats on this table. This table usually takes maximum 10Mins to gather stats without any issues. But yesterday this job did not finished at all, we had to kill that process after 14Hours. When this was running any query on this table was hanging, even "desc" from sqlplus hung. As soon as this process was killed all was normal. Due to this reason any application that was trying to access this table was just freezing and brought down the whole system.


DBMS_STATS.gather_table_stats ('AUSER',
'ATABLE',
estimate_percent => 5,
CASCADE => TRUE
);

Do you know what could be the problem?

Many Thanks

Sorry forgot to mention that this Oracle 9.2.0.6 RAC on Sun Solaris

Thanks

Thanks

Tom,

Thanks a lot for all your help, and sorry for following-up a bit late: the system as well the building it's housed in have a wonderful architecture :-)

* 8 CPUs
* 32 GB RAM
* Oracle 10g R2 - both client and server run on Linux
* 1071, Fifth Avenue at 89th Street

Here are the details of how we load out fact table:
* The table is partitioned by MONTH (date format)
* In every refresh we first truncate the entire table
* Then using SQL loader we populate historical as well as current month's data [and in a given month we load about 200 million rows]
* Then we build 12 local bitmap indexes on 12 non-fact columns
* Then we would gather the statistics of the table using the following procedure:

<SQL>
begin
dbms_stats.gather_table_stats(ownname => 'ops\$datamart', tabname => 'FCT_SALES', estimate_percent => 15 ,
granularity => 'ALL', cascade => true, force => true);
end;
/
</SQL>

...but it would take close to a million years (~21 hours)

I came to you and as per your suggestion I removed 'force => true' option, but Oracle is misbehaving and is still gathering statistics for the indexes?! such that the overall and the time taken is still ~20 hours. Moreover, it's index analyzation that is eating most of the time - we found that out of 20 odd hours that the entire process takes, Oracle spends close to 18 hrs to gather stats for indexes...and quite obviously it's not a good idea as - as you remarked - in 10g index stats are automatically gathered when the index is built.

Seems it's an Oracle bug? What are your thoughts?

Thanks in anticipation

that's what happens when you write your mails in anticipation of St. Patrick's day...

I simply removed 'cascade=>true' (and not 'force=>true' - as I miswrote) and did not explicitly write 'cascade=>false', in the fond hope that default value for cascade parameter is 'false'

I tried your 'block sampling' suggestion and found that it did not affect analyze time substantially - index-analyzation is the killer

Tom,

Thanks *A LOT* again.

I noticed recently that if you use DBMS_STATS.GATHER_TABLE_STAS with CASCADE=>TRUE then it gathers stats for the table and each index in turn. This can take a long time, especially if there are a lot of indexes on the table (eg. in a warehouse). One way to speed this up is to use DBMS_SCHEDULER to create and run jobs for each index on the table simultaneously. Scheduler and the resource manager should ensure the database is not swamped and the jobs all complete as efficiently as possible. The following code indicates the idea :

begin
  for r in (select ui.index_name
                  ,uip.partition_name
                  ,dbms_scheduler.generate_job_name('IXAZ_') as jobname
              from user_indexes ui
            inner join user_ind_partitions uip
             on (uip.index_name = ui.index_name)
            where ui.table_name = 'ASSET'
              and uip.partition_name = 'P200703'
              order by ui.index_name,uip.partition_name )
  loop
  
        execute immediate
'BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name             => :jobname, 
   job_type             => :jobtype,
   job_action           => :jobaction,
   start_date           => NULL,
   repeat_interval      => NULL, 
   end_date             => NULL,
   enabled              => TRUE,
   auto_drop            => TRUE,
   comments             => :jobcomments);
END;'
using 
        r.jobname
      ,'PLSQL_BLOCK'
      ,'begin dbms_stats.gather_index_stats(
         ownname=>'''||user||'''
         ,indname=>'''||r.index_name||'''
         ,partname=>'''||r.partition_name||''''||');end;'
      ,'user='||user||',index='||r.index_name||',ptn='||r.partition_name;
      
end loop;

end;

The code above only considers the indexes for one partition on the table, but the total time to analyze all the indexes will be approx the time taken for the largest index if analyzed individually.

One suggestion I'd make regarding the DBMS_SCHEDULER wrapper to DBMS_STATS is not to specify a degree of parallelism > 1 when analyzing. Using a large degree of parallelism seems to confuse the resource manager - I guess because it thinks it's executing one job but this then spawns multiple sessions for the additional degrees of parallelism - and you can quickly exhaust the supply of available sessions.

Hi Tom,

We have huge patririoned tables which are continually loaded on a daily basis based on Range partition, and we do maintain 30 days worth of retention. The data for the past 29 days is static for the 30th day and we don't want to re-compute the partition level statistics again for them as they have become stale.

However we drop the old partition and adds a new partition on a daily basis , what is termed as Rolling Window.

Now please provide your suggestions:

1. We are doing gather_table_stats in a loop for the complete schema using

dbms_stats.gather_table_stats(ownname=> l_username,
tabname=> l_table_name,
estimate_percent => 5,
method_opt => l_method_opt,
degree => 16,
cascade => true);

Where l_method_opt is :
'for columns ' || p_col_name || ' SIZE 1'; for the partitioned tables and p_col_name is the column on which the data is partitioned.

However if the table is non-partitioned then
l_method_opt is :
'for all indexed columns SIZE 1';

Now this complete gathering of stats takes around 3 hrs and for us it is quite significant.

Can you please provide us suggestions as to how we can capture global and partition level statistics without recomputing them all over again and again.

Your advice/help is appreciated.

Hi Tom,

Since every query which comes to us has to have the WHERE clause suppoted by the PARTITIONED COLUMN we thought we don't need to gather stats on any other column, since this will drive the rows flowing from this step.

I am pretty okay in gathering only the stats for PARTITITIONED COLUMN and not on all the columns of the table as is done by Gather Stats by default. One reson is the time it takes to gather stats is very high on nearly a 1TB of database.

I am also thinking to gather the partition level stats only for the latest partition and not capture global level stats if Oracle's CBO can guess it 90% correct, I am happy with it.

Thanks,
Vikas khanna

for a particular table iam able to use the ANALYZE command but not DBMS_STATS.

see the test case below.

Enter user-name: sysadm/sysadm@inlabtst

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(1) from HX_SSN_GEN_STG_TBL;

  COUNT(1)
----------
    162439

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSADM', tabname => 'HX_SSN_GEN_STG_TBL');
BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSADM', tabname => 'HX_SSN_GEN_STG_TBL'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYSADM"."HX_SSN_GEN_STG_TBL", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13046
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1


SQL> ANALYZE TABLE HX_SSN_GEN_STG_TBL COMPUTE STATISTICS;

Table analyzed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSADM', tabname => 'HX_NAMES_STG');

PL/SQL procedure successfully completed.

just for that one table iam not able to use DBMS_STATS for other tables i can. but i can use ANALYZE command on that table. any idea what could be the problem.

Sorry for posting the pervious one tom. After some search in orafaq got the answer..

SQL>  select username, privilege from user_sys_privs;

USERNAME                       PRIVILEGE
------------------------------ ----------------------------------------
SYSADM                         UNLIMITED TABLESPACE
SYSADM                         CREATE TABLE
SYSADM                         CREATE SESSION
SYSADM                         ANALYZE ANY

SQL> GRANT SELECT ANY TABLE TO SYSADM;

Grant succeeded.

SQL> select username, privilege from user_sys_privs;

USERNAME                       PRIVILEGE
------------------------------ ----------------------------------------
SYSADM                         UNLIMITED TABLESPACE
SYSADM                         SELECT ANY TABLE
SYSADM                         CREATE TABLE
SYSADM                         CREATE SESSION
SYSADM                         ANALYZE ANY

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'SYSADM', tabname => 'HX_SSN_GEN_STG_TBL');

PL/SQL procedure successfully completed.

On a 10g box.

Will running an analyze table overwrite the statistics gathered by dbms_stats earlier ?

Dbms_stats ran at 10am
analyze table ran at 11am

Do both (analyze table and DBMS_Stat) store statistics at the same location ? (hence the earlier question of overwritting).

Hi, Tom,

I was wondering what happen if we kill a DBMS_STATS running job, by some reason, in the middle of gathering statistics of a large/huge table.

That table would contain stale, last analyzed or no stats ?

My concern is if the dbms_stats:
- Delete stats before collecting;
- Collect before overwriting;

Thanks!

hi tom,

i analyzed one table, one time with ANALYZE the other time with DBMS_STATS.GATHER_TABLE_STATS (commands below). i got different executions plans for the same query. good with analyze, bad with DBMS_STATS.GATHER_TABLE_STATS. when comparing all statistics i found they to be the same with the excpetion of the histograms having different slot sizes (see below). 


analyze table gfm.SUBCONFIG_CONFIGURABLE_OBJ_T estimate statistics
for table
for all indexes
for all indexed columns
sample 40 percent;


exec dbms_stats.gather_table_stats ( -
  'GFM', 'SUBCONFIG_CONFIGURABLE_OBJ_T', estimate_percent=>40 -
 ,method_opt=>'for all indexed columns', cascade=>true);


SQL> select * from (
  2  select 'ANALYZE' meth, a.* from system.t_analyze_hist a
  3  UNION ALL
  4  select 'DBMSSTATS', s.* from system.t_stats_hist s
  5  ) where column_name = 'SUBCONFIG_ID'
  6  order by 1;

METH       OWNER      TABLE_NAME                     COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ------------------------------ -------------------- --------------- ----------
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                     782              0
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    1662              1
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    2546          19371
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    3423          19390
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    4315          19411
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    5191          19471
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    6075          19490
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    6943          19510
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    7826          19531
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    8720          19571
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    9636          19591
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   10531          19611
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   11414          19631
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   12301          19651
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   13184          19671
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   14056          19673
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   14930          19691
ANALYZE    GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   15816          19711
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    1526              0
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    3033              1
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    4396          19371
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    5745          19390
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    7119          19411
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    8486          19471
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                    9894          19490
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   11234          19510
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   12627          19531
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   13996          19571
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   15363          19591
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   16761          19611
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   18149          19631
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   19540          19651
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   20926          19671
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   22320          19673
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   23722          19691
DBMSSTATS  GFM        SUBCONFIG_CONFIGURABLE_OBJ_T   SUBCONFIG_ID                   25042          19711

36 rows selected.

Q1) could the slot size difference of the histograms be the reason for the different plan?
Q2) what should i do in the future? Going back to ANALYZE?

hi tom,

thanks for your answer. while doing further investigations on ANALYZE versus DBMS_STATS i found that i get the same histograms on COMPUTE statistics, but different ones on ESTIMATE SAMPLE x percent.

i would assume now that the internal sampling is done differently by ANALYZE than DBMS_STATS. correct?

Hi Tom,

We are going to delete tons of rows from a table. We will do the following:

1) create a temp table;
2) copy the good data from old table (A) to the temp table;
3) create index and trigger in the temp table;
4) rename old table to A_archive table;
5) rename temp table to A old table;
6) trucate A_archive table;

Thw question is after the temp is rename to A old table ( the same name). Whether Oralce will use the old statistic from the old table? in that case we need to analyze the new table right way?

ops$tkyte%ORA10GR2> create table t1 as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> create table t2 as select * from t where owner = 'SCOTT';

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select table_name, num_rows from user_tables where table_name in ( 'T1', 'T2' );

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                  49904
T2                                     16

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table T1 rename to A_archive;

Table altered.

ops$tkyte%ORA10GR2> alter table T2 rename to T1;

Table altered.

ops$tkyte%ORA10GR2> truncate table A_archive;

Table truncated.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select table_name, num_rows from user_tables where table_name in ( 'T1', 'T2', 'A_ARCHIVE' );

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
A_ARCHIVE                           49904
T1                                     16

Thank you very much Tom!

I have the following questions.

1) After creating a table T1 and doing work for a while.

a: Without anylyze it : select table_name, num_rows from user_tables where table_name in = 'T1';

No rows returned. But we can see the statistics from ORACLE enterprise manager console.

b: Anylyze it : select table_name, num_rows from user_tables where table_name in = 'T1';

Row will be returned.

Does this mean after creating a table and never analyze that table manually, Oracle database still getting the related statistics for it?

2) If I want to calculate the table size using user_extents, whether I need to analyze the table in order to get the correct number? Whether the statisitcs by analyze or dbms_ stats is only for execution plan?

Thank you again for your wisdom.

Best regards, Yuna

Hi Tom,

Please see the following scripts about creating table without statistics in user_tables before analyzing.


SQL S3 >create table temptest2 as select * from JCL_SCHEDULE;

SQL S3 >select count(*) from temptest2;

COUNT(*)
----------
10

SQL S3 >select NUM_ROWS from user_tables where TABLE_NAME ='TEMPTEST2';

NUM_ROWS
----------


SQL S3 >analyze table temptest2 compute statistics for table for all indexed columns for all
indexes; 2

Table analyzed.

SQL S3 >select NUM_ROWS from user_tables where TABLE_NAME ='TEMPTEST2';

NUM_ROWS
----------
10

Thank you very much for your comments.

Best regards

Please read the process again. There is no record for the first time run of :

SQL S3 >select NUM_ROWS from user_tables where TABLE_NAME ='TEMPTEST2';

NUM_ROWS
----------


Thank you!

Best regards, Yuna

ops$tkyte%ORA9IR2> select null from dual where 1=0;

no rows selected

ops$tkyte%ORA9IR2> select null from dual where 1=1;

N
-


ops$tkyte%ORA9IR2>

Hi Tom,

Thank you for the explanation. maybe my question can be satted as this way, Why before analyzing the table ( with 10 rows in that table), running "select NUM_ROWS from user_tables where TABLE_NAME ='TEMPTEST2';" returns NUll ( no records) from the SQLPLUS, but the result will show in DB console. After analyzing the table and run the same query, it will return the real num_rows to the SQLPLUS and db console?

SQL S3 >create table temptest2 as select * from JCL_SCHEDULE;

SQL S3 >select count(*) from temptest2; ( this is the real number)

COUNT(*)
----------
10

SQL S3 >select NUM_ROWS from user_tables where TABLE_NAME ='TEMPTEST2'; ( NULL returned before analyzing)

NUM_ROWS
----------


SQL S3 >analyze table temptest2 compute statistics for table for all indexed columns for all
indexes; 2

Table analyzed.

SQL S3 >select NUM_ROWS from user_tables where TABLE_NAME ='TEMPTEST2'; ( the real number returned).

NUM_ROWS
----------
10


Thank you!
Best regards, Yuna

Thank you very much Tom for the answer.

I am sorry that I gave you wrong info about the db console. I could not see the stats either until I use dbms_stats to gather the infomation (exec dbms_stats.gather_table_stats(user, 'TEMPTEST2'). There was another long existing table before. I could get info from the dbconsole, but not from the user_tables. So I applied that for the newly created table.

the step is schema-table-and the specific table.

So I could not get the stats either from user_tables or the DB console before analyzing the newly created table.

Best regards, Yuna

Hi Tom,

I'd like to know what specific privilege is used to analyze a table?

Thank you!

Best regards, Yuna

Tom:

Does materialized view log tables need to be analyzed or gather stats (dbms_stats)? Thanks

Thanks Tom.

In Oracle 9i,Does MV log table always needs to be analyzed with current stats because after MVs refreshed, MV log will be emptied anyway. ?

Will there be performance problem if MV log tables are not analyzed ?

Thanks for your insight.


2) In oracle 9.2.0.6, When estimate_percent parameter is not specified, Will Oracle gather 100% table stats or ?

Example:

exec dbms_stats.gather_table_stats('CUR_DBA2', tabname => 'CTBILLINGTABLE', degree => dbms_stats.default_degree, cascade =>
true ) ;

ops$tkyte%ORA9IR2> create table t ( x int primary key, y int );

Table created.

ops$tkyte%ORA9IR2> insert into t select rownum, rownum from all_users;

35 rows created.

ops$tkyte%ORA9IR2> create materialized view log on t;

Materialized view log created.

ops$tkyte%ORA9IR2> select object_name, object_type from user_objects order by object_name;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MLOG$_T                        TABLE
RUPD$_T                        TABLE
SYS_C004615                    INDEX
T                              TABLE

ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into mlog$_t
  2  select rownum, sysdate, 'x', 'x', utl_raw.cast_to_raw( rpad('x',255,'x') ) from all_objects;

30662 rows created.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> delete from mlog$_t;

30662 rows deleted.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> show parameter optimizer_dynamic_sampling

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     1
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select * from t, mlog$_t where t.x = mlog$_t.x;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=161)
   1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=161)
   2    1     TABLE ACCESS (FULL) OF 'MLOG$_T' (Cost=2 Card=1 Bytes=155)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=6)
   4    3       INDEX (UNIQUE SCAN) OF 'SYS_C004615' (UNIQUE)



ops$tkyte%ORA9IR2> alter session set optimizer_dynamic_sampling = 0;

Session altered.

ops$tkyte%ORA9IR2> select * from t, mlog$_t where t.x = mlog$_t.x;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=13202)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=13202)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=35 Bytes=210)
   3    1     TABLE ACCESS (FULL) OF 'MLOG$_T' (Cost=2 Card=82 Bytes=12710)



ops$tkyte%ORA9IR2> set autotrace off

Tom,

I just need to clarify:

estimate_percent => null (default value) is equivalent to
estimate_percent=> 100 in dbms_stats.gather_table_stats ?

Thanks

Tom:

my goal is to analyze the table 100%.
Can you suggest , Should i use default "compute" or estimate_percent => 100 for any tables ?

Is there any difference between Estimate(100) and Compute in the dbms_stats.gather_table_stats analyze ?

Under what circumstance, one should use "compute" over estimate_percent => 100.

Which data dictionary view contains the info about whether a table or a schema's statistics is currently locked?

ops$tkyte%ORA10GR2> select stattype_locked from user_tab_statistics where table_name = 'T';

STATT
-----


ops$tkyte%ORA10GR2> exec dbms_stats.lock_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select stattype_locked from user_tab_statistics where table_name = 'T';

STATT
-----
ALL

Hey Tom:

I created a table.

SQL> select count(*) from MYBITMAP;

COUNT(*)
----------
1270064

SQL> analyze table mybitmap estimate statistics;

Table analyzed.

Elapsed: 00:00:01.44
SQL> select NUM_ROWS, blocks, empty_blocks,PCT_FREE,PCT_USED,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name='MYBITMAP';

NUM_ROWS BLOCKS EMPTY_BLOCKS PCT_FREE PCT_USED CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- ---------- -----------
1279153 31156 204 10 0 172


SQL> exec dbms_stats.gather_table_stats(ownname => 'JOSH', tabname => 'MYBITMAP', estimate_percent => 10);

PL/SQL procedure successfully completed.


SQL> select NUM_ROWS, blocks, empty_blocks,PCT_FREE,PCT_USED,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name='MYBITMAP';

NUM_ROWS BLOCKS EMPTY_BLOCKS PCT_FREE PCT_USED CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- ---------- -----------
1267080 31156 204 10 0 169

So how come the number of rows are different from analyze and the dbms_stat package, and also neither of them is the same with the real number of rows in the table.


Thank you very much!

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', estimate_percent=> 10 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
     49600

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', estimate_percent=> 10 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
     50730

Sorry, I get it now, it is because they are only estimating the stats, after I do "compute" the number of rows are correct. but why the average row length from package is different from analyze though?

SQL> analyze table mybitmap compute statistics;

Table analyzed.

Elapsed: 00:02:28.66
SQL>  select NUM_ROWS, blocks, empty_blocks,PCT_FREE,PCT_USED,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name='MYBITMAP';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS   PCT_FREE   PCT_USED  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- ---------- -----------
   1270064      31156          204         10                     0         172

Hi Tom,

If stats are being gathered for one partition, and there is no index on table. Can oracle use degree of parallelism? if yes, what is happening in parallel?

Thanks

Hi Tom,

Thanks.

If table is not defined with parallel clause, and degree of parallellism is not defined in dbms_stats clause. Does oracle automatically uses degree based upon spfile parameters?

Regards,

Tom,

Will following do the same thing ?

ANALYZE TABLE <tab_name> COMPUTE STATISTICS;
and
DBMS_STATS.GATHER_TABLE_STATS (
ownname => <owner>,
tabname =><tab_name>,
estimate_percent => 100 ); --> 100%

I am concerned because one of our application requires full statistics on certain IOT table. But few IOT tables are getting errored out(ora-600) due to bug# 4763768

So I am thinking to user dbms_stats with estimate_percent =100.

Will that be the same case as ANALYZE with COMPUTE STATISTICS?

Hi Tom,

In our database :

Ver : 9.2.0.6
Optmiser Mode: FIRST_ROWS


Day#1: dbms_stats were used to gather stats and query "A" is taking 10 sec to get completed.
Day #2 : dbms_job failed . and no stats gathred ..query "A" is taking 10 sec to get completed.

....
....

day #50: and no stats gathred till date after day#1..query "A" is taking 10 sec to get completed.

day #51 : Then we used analyze command (analyze table .... compute statistics) to gather stats - compute stats.

day#52 : we noticed a query "A" finishing in 10 sec earlier now taking 1 hr.

day#52 : Added the hint /*+ Rule */ in quer "A" and now taking 12 sec.

Question:

a) If we gather stats today on day#52, using dbms_stats will the same query will get finished in ~12 sec?
b) CBO could not use efficiently the stats gatherred using analyze table .... compute statistics..? Your comments Please.

Hi Tom

We have gathered stats using the dbms_stats ( gather auto) and it has finished gathering the stats.

There are 1000 tables in our schema. All were analyzed using "Analyze" command on day#52 as mentioned above.

Now after running the dbms_stats.gather_schema_stats ( gather auto) we found the last_analyzed on today only 10 tables.

Question :

a) Whether all the necessary stats which optimiser looks for would have been gathered.?

If not,

b) whether we need to delete statistics and then run dbms_stat again to gather the schema stats.

c) Query "A" still take 1 hr to complete.

continued.....
d)If I
exe dbms_stats.delete_schema_stats('SCHEMA_OWNER')
and then run
exe dbms_stats.gather_schema_stats with gather auto on the above schema.
Then what stats optimiser will take after delete and before create statistics?

e) is it safe to do d) above in Live server?

Regards.