Question on table statistics [message #535287] |
Tue, 13 December 2011 06:35 |
|
Jack14
Messages: 497 Registered: December 2011 Location: INDIA
|
Senior Member |
|
|
Hi,
By default the DBMS_STATS package runs once every 24 hours to collect statistics
for database objects and Oracle collects new statistics when enough of the data (about 10%) has changed.
My question here is how to check the table has changed 10% in database?
Thanks
|
|
|
|
|
|
|
|
|
|
Re: Question on table statistics [message #535421 is a reply to message #535390] |
Wed, 14 December 2011 01:45 |
|
Jack14
Messages: 497 Registered: December 2011 Location: INDIA
|
Senior Member |
|
|
Swan,
all_tab_modifications view is working in my database.
I queried the all_tab_modifications view and got the following output.
SQL> set lines 1000
SQL> set pagesize 10000
SQL> select table_owner,table_name,inserts,updates,deletes,timestamp
from all_tab_modifications
where table_owner='RIM' and table_name='DRAFT_TAB';
TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP
RIM DRAFT_TAB 79359 1076839 0 14-Dec-11
RIM DRAFT_TAB 346 231 0 13-Dec-11
RIM DRAFT_TAB 2158 2246 0 14-Dec-11
RIM DRAFT_TAB 647 1785 0 14-Dec-11
RIM DRAFT_TAB 6937 264719 0 14-Dec-11
RIM DRAFT_TAB 611 191 0 13-Dec-11
RIM DRAFT_TAB 11 4 0 14-Dec-11
RIM DRAFT_TAB 498 2776 0 14-Dec-11
RIM DRAFT_TAB 87 328 0 13-Dec-11
RIM DRAFT_TAB 904 336 0 13-Dec-11
RIM DRAFT_TAB 48 89 0 13-Dec-11
RIM DRAFT_TAB 514 49762 0 13-Dec-11
RIM DRAFT_TAB 24 9 0 14-Dec-11
RIM DRAFT_TAB 233 149 0 14-Dec-11
RIM DRAFT_TAB 497 150 0 13-Dec-11
RIM DRAFT_TAB 6 8 0 14-Dec-11
RIM DRAFT_TAB 15 9 0 13-Dec-11
Could you please tell me how to calculate the percentage of data got modified in DRAFT_TAB table recently.Bcoz i see many columns have retrieved for todays and yesterdays date.So how to calculate for today's day
Thank you
[Updated on: Wed, 14 December 2011 01:51] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: Question on table statistics [message #535440 is a reply to message #535421] |
Wed, 14 December 2011 03:00 |
John Watson
Messages: 8950 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You need to compare the figures in dba_tab_modifications with the num_rows column of dba_tables. Whenever the table is analyzed num_rows is re-calculated, and dba_tab_modificatrions is zeroed. Then compare dba_tables.last_analyzed with sysdate to get an idea of how many changes per day.
|
|
|
Re: Question on table statistics [message #535496 is a reply to message #535440] |
Wed, 14 December 2011 06:11 |
|
Jack14
Messages: 497 Registered: December 2011 Location: INDIA
|
Senior Member |
|
|
John,
I am not getting you.
My question is there are many rows returned for todays date when i issue below query.
select table_owner,table_name,inserts,updates,deletes,timestamp
from all_tab_modifications
where table_owner='RIM' and table_name='DRAFT_TAB';
And from the output i want to calculate the percentage of data got modified in table.
Thank you
[Updated on: Wed, 14 December 2011 06:16] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Question on table statistics [message #535512 is a reply to message #535505] |
Wed, 14 December 2011 07:42 |
John Watson
Messages: 8950 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
As I said, the number of I/U/Ds is tracked for each table. You are confused because your table is partitioned. Try this:drop user jon cascade;
grant dba to join identified by jon;
conn jon/jon
create table parts(c1 number) partition by range(c1)
(partition p1 values less than (10),
partition p2 values less than (20));
exec dbms_stats.gather_table_stats('JON','PARTS')
select table_name,partition_name,inserts,updates,deletes
from user_tab_modifications;
insert into parts values(5);
insert into parts values(15);
exec dbms_Stats.flush_database_monitoring_info
select table_name,partition_name,inserts,updates,deletes
from user_tab_modifications;
exec dbms_stats.gather_table_stats('JON','PARTS')
select table_name,partition_name,inserts,updates,deletes
from user_tab_modifications;
|
|
|
Re: Question on table statistics [message #535513 is a reply to message #535498] |
Wed, 14 December 2011 07:48 |
John Watson
Messages: 8950 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:You have what the view tells you the number of operations NOT the number of modifications; I don't agree, Michel (that is the first time!) Surely it is the number or rows, not the number of DML operations. As ever, I'm prepared to be proved wrong.
|
|
|
Re: Question on table statistics [message #535515 is a reply to message #535513] |
Wed, 14 December 2011 07:56 |
|
Michel Cadot
Messages: 68687 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You are right, I made a test and this is indeed the number of rows (always thought it was the number of operations).
Of course it can be misleading if the same rows are updated several times (you can have an UPDATES value greater than the number of rows!).
Regards
Michel
[Updated on: Wed, 14 December 2011 09:21] Report message to a moderator
|
|
|
Re: Question on table statistics [message #535517 is a reply to message #535515] |
Wed, 14 December 2011 08:06 |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 14 December 2011 13:56You are right, I made a test and this is indeed the number of rows (always thought it was the number of operations).
Of course it can be misleading if the same rows are updated several times (you can an UPDATES value greater than the number of rows!).
Regards
Michel
Hah. Almost exactly what I was about to post.
It'll show you a row count which has changed, but from a USER point of view, it doesn't show you what proprortion of the data has changed.
mkr02@ORA11GMK> create table foo (a number)
2 /
Table created.
Elapsed: 00:00:02.94
mkr02@ORA11GMK> exec dbms_stats.gather_table_stats(ownname=>'MKR02',tabname=>'FOO')
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.42
mkr02@ORA11GMK> insert into foo values (101)
2 /
1 row created.
Elapsed: 00:00:00.10
mkr02@ORA11GMK> commit;
Commit complete.
Elapsed: 00:00:00.00
mkr02@ORA11GMK> BEGIN
2 dbms_stats.flush_database_monitoring_info;
3 END;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.02
mkr02@ORA11GMK> select table_name, inserts, updates, deletes from dba_tab_modifications where table_name = 'FOO'
2 /
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
FOO 1 0 0
Elapsed: 00:00:00.01
mkr02@ORA11GMK> begin
2 for i in 1..1000 loop
3 update foo set a=101;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.13
mkr02@ORA11GMK> commit
2 /
Commit complete.
Elapsed: 00:00:00.00
mkr02@ORA11GMK> BEGIN
2 dbms_stats.flush_database_monitoring_info;
3 END;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.02
mkr02@ORA11GMK> select table_name, inserts, updates, deletes from dba_tab_modifications where table_name = 'FOO'
2 /
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
FOO 1 1000 0
Elapsed: 00:00:00.00
I was going to add more rows, but forgot. Here as far as the object is concerned, in the eyes of a user, there's been no change - not so to the system.
It depends on if question is aimed at "User" point of view, or "oracle" point of view.
imo
|
|
|
|