Solved

When it is safe to use Nologging ?

Posted on 2011-09-22
13
801 Views
Last Modified: 2012-05-12
I have an application, where in I use to create table dynamically inside the plsql block,
which is taking more time to execute.

I just want to know, when and where it is safe to use Nologging? so that I can save sometime.
0
Comment
Question by:sakthikumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +4
13 Comments
 
LVL 5

Expert Comment

by:vaska94
ID: 36584813
Use the NOLOGGING option only on temporary/working/staging tables.
its not recommended to use nologging, but it can save you some space, and also increase speed....
BTW Read : http://oracleexamples.wordpress.com/2010/08/28/benefits-and-consequences-of-the-nologging-option/
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 167 total points
ID: 36584822
- operation such as CREATE TABLE .. AS SELECT, CREATE INDEX and any BULK INSERT operation can be run with NOLOGGING as you can re-create/run it again should it fail.

- the following Oracle doc explain when to /not to use NOLOGGING:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#i1106265
http://jakub.wartak.pl/blog/?page_id=107 - he collects the NOLOGGING info and compile it into one which is worth a read.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36586867
>>I have an application, where in I use to create table dynamically inside the plsql block,

Do you really need a dynamic table or can you use a Global Tempory Tae work for you?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36587730
>> create table dynamically inside the plsql block, which is taking more time to execute.

1. What is the size of this temp table. It may be possible that the query is taking time and the slowness may not be because of segment contention.
2. Also check total number of extents allocated to that table. It may be possible that table is created with small extent and oracle has to allocate more and is wasting time on that part.
3. To address actual problem, please provide your create temp table query or whole pl block.
0
 
LVL 6

Expert Comment

by:Greg Clough
ID: 36588506
+1 for a global temporary table.  No logging, and automatically disappears when the session closes.

Also, is your database, or tablespace in "Force Logging" mode?  If so, then using NOLOGGING on the table won't change anything.  Force Logging is generally used in DataGuard environments, where you must ensure that the redo stream has all changes.

If you do choose to go down the nologging route, then any data in that table is unrecoverable until the next backup... so use it wisely.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 36594274
Like to know

1.  How often you call the block which creates table ?
2.  If quite often, means it creates that many tables for each time the block is called?
3.  How do you manage when 2 users are tying to call at same time?
4.  Do you drop these tables created? If yes, are you dropping at end of the block?

If you are creating and dropping in same block, you should be using GTT (Global Temporary Table) instead as suggested by other experts
0
 

Author Comment

by:sakthikumar
ID: 36595171
in every specific time period,  the tables will be created. these were not temporoary tables.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 36595174
Ok, do you query these tables at later date or time, once  the block is done its execution?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36598865
If these are not temporary tables and must remain around, can you easily re-create them at a later date in the event you lose your database?

Can you take a backup immediately after creating them?

If not, you should not use nologging.

If you can provide a little more detail about your requirements we can offer better advice.
0
 

Author Comment

by:sakthikumar
ID: 36600567
backup --> I dont know like when the backup will be created, it is in DBA's hands, and am only a developer

can you easily recreate them --> I can recreate it, but the problem is if I continuously do this for all the tables subsequently, then there will not be any table to recreate.

query these tables --> sure, these table will be queried often.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 36600573
I would advise you to NOT use nologging.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 36600622
Few thinks I can think of:

1.  If you create the table externally, how much time it takes?
2.  Are you 100% sure that dynamic sql of creating table takes time and no other part of sql?
3.  Can you logically separate two parts: Creating table and other part of sql?
4.  I assume your whole code is using dynamic sql, but you need to zero down the portions that take time to execute
5. Can you share the code with us?
0
 
LVL 6

Assisted Solution

by:Greg Clough
Greg Clough earned 167 total points
ID: 36601678
backup --> I dont know like when the backup will be created, it is in DBA's hands, and am only a developer

+1 for NOT using nologging... but if you are still considering it, then the person you need to speak with is your DBA.  You both need to work together to solve this problem and ensure there are no "surprises" later on, you can't do it in isolation.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question