Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

When it is safe to use Nologging ?

Posted on 2011-09-22
13
Medium Priority
?
804 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 668 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 664 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 668 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

715 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