stats collection on a table with data and we specifying the number of rows
Follow up by mail Click here


Subject: stats collection on a table with data and we specifying the number of rows
Author: raghavendra rao yella, United States
Date: Jan 03, 2019, 20:29, 12 days ago
Os info: Solaris 10
Oracle info: 11.2.0.3
Message: We have a job where it loads one of the table and deletes 98% of of the data from it..
2% of the data is still left in these tables

During the above process.. table is queried utilizing existing indexes.
Stats on these tables are pretty static most of the time (rarely gets updated through automatic stats collection job)

My question is –

If we are planning on truncating this table.... and if we collect stats... stats might be way off.

If i'm recollecting this correctly.. there is a way to tell stats collection job that the table has n number of rows.. even though table might not have n number of records.

any thoughts on stats collection script for above?

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: stats collection on a table with data and we specifying the number of rows
Author: raghavendra rao yella, United States
Date: Jan 03, 2019, 22:46, 12 days ago
Message: found the solution..

exec dbms_stats.set_table_stats( user, ‘TEST’, numrows => 10000, numblks => 1000 );

https://decipherinfosys.wordpress.com/2007/07/31/dbms_statsset_table_stats/
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: stats collection on a table with data and we specifying the number of rows
Author: Philip Wisse, Netherlands
Date: Jan 04, 2019, 08:58, 11 days ago
Message: Hi,

You can also freeze the stats:
https://oraclespin.com/2008/10/09/how-to-lockunlock-statistics-on-a-table/

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: stats collection on a table with data and we specifying the number of rows
Author: Bruno Vroman, Belgium
Date: Jan 06, 2019, 13:31, 9 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Raghavendra,

a little "parallel" answer:
    job loads one table and deletes 98% of of the data from it..
Maybe you can load in another staging table: instead of
  TRUNCATE TABLE a; (assumed)
  INSERT 1M rows INTO a;
  DELETE 9.8M rows FROM a;
do something like
  TRUNCATE TABLE staging_a;
  INSERT 1M rows INTO staging_a;
  TRUNCATE TABLE a; (assumed)
  INSERT INTO a SELECT 2% FROM staging_a;
(one of the benefits is that your table a is 50 times smaller)

Now about the question: I second Philip: if the "state" of table is OK at a time "t", refresh statistics at this time and then freeze them.

Other option: include the "gather_table_stats" in your process (just before actually using the table);
  in_a_way_or_another load the "2%" in table a
  dbms_stats.gather_table_stats
  use "a" in the following steps of the job, with accurate statistics.
That might be better than fixing the num_rows (Oracle statistics contain much more information than the number of rows, like for example at column level: numbe distinct values, null values, min value, max value... -without speaking of histograms- all these might be very useful for the optimizer)

Best regards,

Bruno Vroman
P.S.: better late than never: https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=92186
8a ing likeis still left in thesetables
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: stats collection on a table with data and we specifying the number of rows
Author: raghavendra rao yella, United States
Date: Jan 07, 2019, 15:36, 8 days ago
Message: Thanks for your response on this...

sorry for getting back on this little late...

to help you understand more...

I'm not sure if stats on table with "daily data load+existing data" is good (current stats on table)
for example.,
1 million records daily load + 2.3 million existing data

OR

purging the table followed with daily data load and stats collection.
Example 1 million records

That's the reason for not locking table stats.

Currently stats are getting collected using Oracle Automatic stats collection process(stale stats).. but rarely gets updated as... table size is not growing with huge difference.

Liked your suggestion on truncate and loading some temporary data and collecting stats...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: stats collection on a table with data and we specifying the number of rows
Author: Bruno Vroman, Belgium
Date: Jan 07, 2019, 17:20, 8 days ago
Message: Oh,
my assumption that the table was truncated every day before load is wrong...
So I understand that you have something like:

 before start: table has 2.3M rows
 then:
  insert 1M new rows
  delete 98% of these new rows (we keep 2% of 1M rows, so 20K new rows)
 the result being a table with 2.32M rows (and next day we repeat the same kind of process, ending with 2.34M rows...)

If the 980K rows deleted are part of the 1M new rows, I suggest to work with a staging table so that the main table supports only "+20K" instead of "+1M-980K"
But maybe things are more complex and deleted rows are also part of the "old rows", and maybe your "insert" is in fact a "merge"... I don't know the situation.

About automatic statistics: note that even if the total number of rows changes only from 2.5M to 2.52M, Oracle will also take care of the total number of inserts, deletes, updates (so: inserts and deletes are high numbers compared to the table cadinality) and should refresh the stats during next maintenance window as they are clearly considered as "STALE".
You might have a look at DBA_TAB_MODIFICATIONS (or ALL_TAB_MODIFICATIONS)... (and to have "fresh data" you might use "EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO") -more information in Oracle Documentation... So if the stats are not refreshed very often, try to find why (maybe they are locked already?). (Remark: if you go to my proposal of using a staging table so that the main table will only see the "+20K" then indeed (assuming that there are not much other operations on the table) the stats will be considered as OK... It can be that forcing a refresh of stats is a good idea but it depends of the data added to the table and of the way the table is used during next operations, vague and general advice is useless here.

If you want, give more clarifications and correct my new understanding if not OK.

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: stats collection on a table with data and we specifying the number of rows
Author: raghavendra rao yella, United States
Date: Jan 07, 2019, 23:03, 8 days ago
Message: the 2% or 20k records that you were talking are bad records.
Valid records are loaded and deleted.. incorrect data seems be lying in the table.

This table is similar to Queue table for order processing.

----

Lets have second look at this issue.
Basically APPDEV was thinking and even i'm to some extent.. frequent inserts/deletes of table requires maintenance(table growing big or even fragmentation)...

Either perform RE-ORG of table or Truncate the table.

They are not seeing any issues at present.. but wanted to see if at some point they want to truncate.. how to handle stats..

Hope that helps on why they are planning on performing these truncates and about stats
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: stats collection on a table with data and we specifying the number of rows
Author: Bruno Vroman, Belgium
Date: Jan 08, 2019, 09:54, 7 days ago
Message: Hello,

<<the 2% or 20k records that you were talking are bad records.
Valid records are loaded and deleted.. incorrect data seems be lying in the table.
>>

Sorry, I don't understand.
Now I think that your table contains 2.3M rows but should be empty because theses are old "bad records" and every day you do "load 1M rows, delete 980K rows, remaining 20K rows are bad records" hence the table grows each day by 20K bad records... Hmmm... What do you call "bad records" ? When we load records from a file, the bad records are the one that cannot be inserted in a table... Here the table is in fact a kind of staging table? You load in the table whatever the "quality" of the data, then a process deletes most rows (the "valid ones") (probably after having inserted them somewhere else) and the "bad ones" remain in the table?
If it is actually a staging table, it should be emptied (truncated) after use, no?

About the size, if the daily volumes are stable, the max size of the table should also remain stable and you will have no benefit in reducing its size every day:

-a- reduce the size of table when it contains few or no rows, the size becomes "s"
-b- insert 1M rows, this causes a growth of the table to size "S"
-c- delete 98% of the rows, the size remains "S"
and repeat next day (alternative: reduce the size after the deletion of 98% of rows)

or: skip "reduce the size": size of table is always ~S

no benefit in the size reduction (maybe even the contrary) -so even if you do a TRUNCATE, you might use the option "REUSE STORAGE" to keep the size to its "useful value".


As I can't see the complete picture I'm afraid I can't help more.

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: stats collection on a table with data and we specifying the number of rows
Author: raghavendra rao yella, United States
Date: Jan 08, 2019, 20:47, 7 days ago
Message: Thanks for chiming in Bruno...

I just double confirmed this.. the left over data is from failed load jobs.

At any time there might be more than one load process.. as such they are not able to purge the table before the load process.

I'll keep the option of exporting/importing of stats for any truncates that we plan to perform at some point.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here