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
Solved

When it is safe to use Nologging ?

Posted on 2011-09-22
13
799 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
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.

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
data lookup in Oracle - need suggestions 55 121
Oracle sql query 7 74
Oracle DB monitor SW 21 60
oracle forms question 22 40
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

856 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