OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » NOLOGGING and INDEX

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NOLOGGING and INDEX [message #64340]

Fri, 14 January 2005 02:01

Shikha
Messages: 36
Registered: January 2002

Member

What is the use of NOLOGGING in an index?

I know that using NOLOGGING while CREATING an
index can make it really faster. I have seen
upto 40% increase in speed.

But once created, what is the use of NOLOGGING
in an index?

Does it make the same sense the way NOLOGGING does
to a TABLE?

Thanks,

Shikha

Report message to a moderator

Re: NOLOGGING and INDEX [message #64342 is a reply to message #64340]

Fri, 14 January 2005 03:57

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

NOLOGGING ( in table or index) is not recomended in an production environment, becauase in case of a disaster a media recovery is near impossible.
As you said, the option can be used during index cration.
It would speed the updates to tables (if it is a BULK operation, becuase updating the index/tables will not generate logs and so it may be a little faster). But after such an update / changes ( if the objects are left in NOLOGGING state) one should backup IMMEDIATELY.

Report message to a moderator

Re: NOLOGGING and INDEX [message #64361 is a reply to message #64342]

Mon, 17 January 2005 04:07

Shikha
Messages: 36
Registered: January 2002

Member

Thanks Mahesh,

Can anyone tell me why the DML operations are so drastically slow in my database?

Note1: currently, somehow I am the only user. There are no other programmers / users logged in into this database.

Note2: No triggers, external constraints.

Note3: The queries are running extremely fine. Tables are analyzed regularly. In fact the query results are showing up in a matter of milliseconds. But any update/ delete upon even 10K records is taking a really long time.

In the previous posting I had tried moving the table and also the tablesace to NOLOGGING (I am in development schema). But still no avail.

Take the simplest of example:

DELETE FROM MATERIALS_MASTER
WHERE ROWNUM < 20001;
-- materials_master has about 3 million records, no
-- partitions 35 fields and 5 single field indexes

This used to take about 30 seconds to 1 min... But now it runs for the whole night...

No DBA support I have, when I tried to use server statistics in TOAD I found one msg as
"REDO space wait ratio too high"...
(other messages like
"Chained fetch ratio high"
"High Parse to execute ratio"
"DBWR Average Scan Depth")

What would be the ways to tackle this problem?

Thanks a lot...

Thanks a lot...

Shikha

Report message to a moderator

Re: NOLOGGING and INDEX [message #64362 is a reply to message #64361]

Mon, 17 January 2005 04:27

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You said tables are analyzed.
Are indexes been analyzed too?

>>DELETE FROM MATERIALS_MASTER
>>WHERE ROWNUM < 20001;

why are you doing this?
If you are trying to delete the first 20,000 rows then
YOU MAY BE WRONG.

rownum is just a serial number generated during the display.
It is NOT the generated in the same way as it is inserted.

Report message to a moderator

Re: NOLOGGING and INDEX [message #64363 is a reply to message #64362]

Mon, 17 January 2005 04:44

Shikha
Messages: 36
Registered: January 2002

Member

Yep,
I was just giving an example.

But I had to delete about 50 K records from that
table where ACTIVE_STATUS = 'Y'.

And I was apprehensive to delete even 50K records in one go... So I was deleting 20K at a time... And even that was hanging for a long time...

So the query should have an additional condition:
AND ACTIVE_STATUS = 'Y' -- It's bitmap.

(something to do with bitmap?)

But note the following:

1. I do have such problems even with other deletes/ inserts.

2. I do not analyze indexes separately !!!
(Which I thought has the same LAST_ANALYZED value as the table itself amd hence I dot not explicitly require to analyze individual indexes as well)

Hoping that I am clear enough, request you to now kindly re-look back at the earlier query posted by me and suggest me some diagnostics.

Thanks,

Shikha

Report message to a moderator

Previous Topic:	max_extents problem
Next Topic:	Oracle 8i to Oracle9i Migration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 03:20:11 CST 2025