Skip to Main Content
  • Questions

  • Create table NoLogging will help during the huge insert into table ( table is for test purpose not criticle )

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rai.

Asked: December 08, 2004 - 9:57 am UTC

Last updated: December 13, 2004 - 10:09 am UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

hi,

My DB size 250Gb (sales data ), there are 4 main tables, my bussiness requirment is that they want the details for how many records insert/update or delete in a day on these four tables.

We tried many things but we dont want to effect the performance of database, finally we decided to fire trigger on each event ( update, insert, delete ) and insert rows in temp tables . if i create temp table with nologging option will it help in performance, these tables data is not cricle for us. Please suggest.

Regrads

Rai

and Tom said...

adding a trigger to do this is going to have a pretty massive effect on your performance if this table is heavily modified.


Since you will be tracking things at the row level, "nologging" will not have any affect. Nologging only affects direct path inserts ( insert /*+ append */ into t select.... inserts) -- not normal "inserts"

if this data is not critical, i would think really hard before doing this. That trigger will have a material affect on your performance.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about enabling MONITORING?

Satish, December 08, 2004 - 11:20 pm UTC

Will not MONITORING on the table help ? one can use xxx_Tab_modifications to track the changes to the table.

Tom Kyte
December 09, 2004 - 1:09 pm UTC

it will be not exactly even close to precise....

Can you pls elaborate?

Satish, December 09, 2004 - 10:53 pm UTC

Would be great if you can demonstrate with an example.

Tom Kyte
December 10, 2004 - 1:03 am UTC

example of what?

Example

Satish, December 13, 2004 - 4:01 am UTC

Sorry Tom for not being specific. I was wondering why enabling monitoring wouldn't track Inserts, Updates and Deletes accurately. It would be great if you can elaborate on this.

Tom Kyte
December 13, 2004 - 10:09 am UTC

in order to make it efficient, the counters are incremented in a non-latched fashion -- meaning counts can be missed. It is just an "estimation that about N% of the table was changed", it is not precise by design, it didn't have to be.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library