Solved

When it is safe to use Nologging ?

Posted on 2011-09-22
13
796 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
  • 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 76

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 65
oracle rollup query 3 51
passing parameters to sql script oracle 4 27
Oracle SQL Select unique values from two columns 4 33
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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now