Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

learning about type of dml activity on an oracle table

bob hollis
Frequent Advisor

learning about type of dml activity on an oracle table

I am trying to manage an 8.1.7 oracle database that supports an application.
Some of Oracle's management suggestions depend on how a table is used - just inserts - lots of updates, etc.

Is there some way to collect information about a table over time, and then be able to make some educated guesses about the type of changes?

What I've started to do is weekly analyzes, then run a script to collect some information from dba_tables and dba_indexes. Our application has been running for over 2 years, and I'm going to be rebuilding the database in a month or so. I'd like to know what tables might benefit from changes to pctfree or initrans or other changes. I've been able to identify tables with no records, tables with no growth, and tables that grow. Now I just need to take it a bit farther. What tables have pretty much all inserts with few updates? What tables have a lot of updates?
Suggestions?
Thanks
4 REPLIES
Michael Kelly_5
Valued Contributor

Re: learning about type of dml activity on an oracle table

Bob,
have a look at the V$SQL* views in the database. You might be able to extract nad postprocess the data you want.

HTH,
Michael.
The nice thing about computers is that they do exactly what you tell them. The problem with computers is that they do EXACTLY what you tell them.
Alexander M. Ermes
Honored Contributor

Re: learning about type of dml activity on an oracle table

Hi there.
Ever thought about using the Oracle Enterprise Manager ? Uselful tool.
What about doing an export of the database structure ( full export with no rows / compress extents =y ) ?
That might give you an idea, how much space you will need.
You can edit the export file and extract the create statements for the tablespaces.
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Steven Gillard_2
Honored Contributor

Re: learning about type of dml activity on an oracle table

In 8.1.7 you can configure a table for monitoring using:

SQL> alter table monitoring;

Then, use the dbms_stats package to periodically collect table statistics. The dba_tab_modifications view will be updated with this information (number of inserts, updates, deletes etc). Under 8i this information is only updated every 3 hours so you may have to wait for a while to see the data.

If you've got metalink access have a look at note 102334.1 for full details.

Regards,
Steve
bob hollis
Frequent Advisor

Re: learning about type of dml activity on an oracle table

Thanks for all the suggestions, I'm checking them out.
Just to clarify my issue.

I need a way to look at a table and recognize that
this table is pretty much all inserts with few updates
where another table would have a lot of updates.

Like I said before, I am collecting periodic statistics, so I
have a good idea which tables are growing. I'm trying to find out
what tables have a lot of chaining or migration. The same for indexes,
which ones need to be rebuilt, or have parameters changed?

There is a lot of information on metalink, but it's dependant on knowing
the characteristics of a particular table - how do I find that out?

Thanks again