Benefits and consequences of the NOLOGGING option

I still find confusion out there about the LOGGING and NOLOGGING clauses when performing DML and DDL operations, the reality is that the NOLOGGING clause will work only on particular conditions, but all regular inserts,updates and deletes will still log the operations.

The benefits of the NOLOGGING option are:

  • Will save disk space when the archive option is enabled.
  • Will largely reduce I/O on the redologs.
  • Will reduce the time it takes to complete the operation.

Please note that NOLOGGING operations will only reduce -not eliminate- the logging.

Lets see an example –


--   First: we create an empty table with the nologging clause
SQL>  create table logging_example nologging as select * from dba_objects where 1=2;

Table created.

--Now let's enable the statistics and perform a couple of tests:

SQL> set autotrace on statistics
SQL> set timing on

-- insert the records the traditional way
SQL> alter system flush buffer_cache;  --clean the cache to compare the speeds in equal conditions

System altered.

Elapsed: 00:00:01.49

SQL> insert into logging_example select * from dba_objects;

50864 rows created.

Elapsed: 00:00:01.59

Statistics
----------------------------------------------------------
 0  recursive calls
 5250  db block gets
 6766  consistent gets
 982  physical reads
5636712  redo size --without the APPEND hint
 670  bytes sent via SQL*Net to client
 586  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 50864  rows processed

-- insert the records with the APPEND hint (nologging)
SQL> alter system flush buffer_cache;  --clean the cache to compare the speeds in equal conditions

System altered.

Elapsed: 00:00:01.06

SQL> insert /*+ append */  into logging_example select * from dba_objects;

50864 rows created.

Elapsed: 00:00:00.59

Statistics
----------------------------------------------------------
 0  recursive calls
 743  db block gets
 5374  consistent gets
 944  physical reads
2200  redo size --with the APPEND hint
 654  bytes sent via SQL*Net to client
 604  bytes received via SQL*Net from client
 3  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 50864  rows processed

We can see that there is a big difference on the redo size generated by each insert, there are many post and articles on the internet  that show the speed benefits when using the NOLOGGING option, but here I mainly want to clarify that a regular insert (no APPEND hint) will still generate redologs even if the table have been created with the NOLOGGING option.

What happens to the data after a restore when a nologging operation was performed on it?
Continue reading

Advertisements
Posted in Backup & Recovery, Oracle Server, SQL | Tagged , , , , , , , | 9 Comments

Oracle Widgets for the Grid Control / Oracle Enterprise Manager

Hi to all
I found on the Oracle Website 3 desktop widgets for the Oracle OEM and I am starting to find them useful.
The first widget will allow you to search OEM targets on your desktop and open their page on your browser.
The second one will show the top 5 high-load databases(ordered by the Average Active Sessions performance or load metric) and the third one is for monitoring the health and availability.
In order to work you will have to provide them the URL console and the account username and password.
Enjoy!
Posted in Monitoring, Oracle Server, Performance & Tuning | Tagged , , , , , , | Leave a comment

Finding a locking session

How to identify lockers

This article will explain about locks on rows and on objects in ORACLE.

Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s).

When there is a lock on a row there is also a lock on the dependent objects, if we want to perform a DDL on a locked object we will get an ORA-00054 error.

Scenario 1:

Terminal A is locking a row and Terminal B is waiting on it:
Continue reading

Posted in Oracle Server, Performance & Tuning | Tagged , , , , , | 4 Comments

expdp error: 31623 and ORA-12805 in cluster 10.2.0.4

I was getting an error with expdp in our cluster 10.2.0.4 running on Red-Hat

UDE-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2772
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3886
ORA-06512: at line 1

ORA-12805: parallel query server died unexpectedly

I found the solution in metalink, it is actually a bug and Oracle recommends to run these scripts, they will rebuild Datapump’s dictionary objects and the last one will recompile the invalid ones:

-

@$ORACLE_HOME/rdbms/admin/catdph.sql

@$ORACLE_HOME/rdbms/admin/prvtdtde.plb

@$ORACLE_HOME/rdbms/admin/catdpb.sql

@$ORACLE_HOME/rdbms/admin/dbmspump.sql

@$ORACLE_HOME/rdbms/admin/utlrp.sql

After I ran all the scripts I had to bounce the database, but at least I have my exports up and running again.
be careful with the utlrp.sql, because if there are other DDLs running you might get deadlocks

Posted in Backup & Recovery, Linux/Unix, Oracle Server | Tagged , , , | 1 Comment

Commonly used srvctl commands

What is srvctl? it is the Server Control Utility, we use SRVCTL to start and stop the database and instances, manage configuration information, and to add, move or remove instances and services.

These are some of the srvctl commands I frequently use, this is not a complete reference guide.

I organized the commands as follow:

  • To start a rac database
  • To stop a rac database
  • To check status and configurations
  • To start and stop instances
  • To start, stop and manage services

Continue reading

Posted in High-Availability, Linux/Unix, Oracle Server | Tagged , , , , , , , , , , | 9 Comments

Oracle + Sun strategy – Live Webcast

Oracle now have a very broad range of products: Hardware + OS + Java + Database + Virtualization (Oracle and Sun Virtual Box), application servers, ORM, etc,etc,etc

We also now that major companies loves and appreciate to talk to a single company for support.

So what will be the strategy here? probably Oracle will start selling attractive “combos” or they will play with the licensing prices when using Sparc CPUs?

Register the live Webcast with Larry Ellison to learn about the strategy Oracle-Sun (on January 27)

Posted in News | Tagged , , , | Leave a comment

UNDO management, Cannot shrink the UNDO tablespace.

One common question we can  find in the internet is how to shrink the undo tablespace, because even if we see it empty we can get an ORA 3297 error (see here how to deal with ORA 3297 error), therefore, to resize it we need to drop it and recreate it.

Lets take a closer look at it:

1st.- Create a new UNDO tablespace (UNDO2).

SQL> CREATE UNDO TABLESPACE undo2 DATAFILE 'C:\Oracle\Oradata\testdb\undo2_01.dbf' SIZE 100M;

2nd.- We need to tell the instance to start using our new UNDO tablespace

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=undo2 scope=both;

3rd.- Remeber the UNDO_RETENTION parameter? this means that until that time expires and all transactions are finished we will not be able to drop the old UNDO tablespace (UNDO1)

Lets check for UNEXPIRED UNDO extents on our old UNDO tablespaces:

SELECT tablespace_name, status, COUNT (*)
FROM SYS.dba_undo_extents
GROUP BY tablespace_name, status
order by 1,2;

TABLESPACE_NAME    STATUS        COUNT(*)
UNDO1              EXPIRED         23
UNDO1              UNEXPIRED        5

Status are:
ACTIVE: There are active transactions right now using the UNDO tablespace.
UNEXPIRED: These are extents that are not from an active transactions but are needed for the UNDO_RETENTION.
EXPIRED: Extents that are free to be reused.

While there are UNEXPIRED extents it won’t be possible to drop the UNDO1 tablespace. Once all extents are EXPIRED then we can dropt it.

4th.- Drop the old tablespace

SQL> DROP TABLESPACE undo1 including contents and datafiles;
Posted in Oracle Server, SQL | Tagged , , , , | 2 Comments