Question on table statistics [message #535287] |
Tue, 13 December 2011 06:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7587f1e1dc7836c6b81ad21d234a2aad?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7587f1e1dc7836c6b81ad21d234a2aad?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8938 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7587f1e1dc7836c6b81ad21d234a2aad?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8938 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8938 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68664 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). ![Smile](images/smiley_icons/icon_smile.gif)
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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). ![Smile](images/smiley_icons/icon_smile.gif)
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 ![Smile](images/smiley_icons/icon_smile.gif)
|
|
|
|