Streams Pool is only for Streams? Think Again!

If you don’t use the automatic SGA (i.e. set the sga_target=0) - something I frequently do - and don’t use Streams, you probably have set the parameter streams_pool_size to 0 or not set it at all, since you reckon that the pool is used for Streams alone and therefore would be irrelevant in your environment wasting memory.

But did you know that the Streams Pool is not just for Streams and it is used for other tools some of which are frequently used in almost any database environment? Take for instance, Data Pump. It uses Streams Pool, contrary to conventional wisdom. If Streams Pool is not defined, it is dynamically allocated by stealing that much memory from the buffer cache. And the size is not reset back to zero after the demand for the pool is over. You should be aware of this lesser known fact as it reduces the buffer cache you had allocated to the instance earlier.

Demonstration

Let’s examine this with an example. First, let’s check the various pools defined in the database instance right now:

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- -----
sga_target big integer 0

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- -----
db_cache_size big integer 300M

SQL> show parameter streams_pool_size

NAME TYPE VALUE
------------------------------------ ----------- -----
streams_pool_size big integer 0

Note carefully the values of the following parameters:

• sga_target = 0 --> this means the SGA is not auto tuned.
• db_cache_size = 300M --> this is the buffer cache
• streams_pool_size = 0 --> this is the stream pool, set to 0 as expected

Now kick off a Data Pump Export (expdp) job:

$ expdp directory=DATA_FILE_DIR tables=arup.t1

... output truncated ...

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:49:16

After the Data Pump job is complete, check the size of the buffer cache again:

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- -----
db_cache_size big integer 280M

The buffer cache got compressed from 300 MB earlier to 280 MB. But you didn’t do that; Oracle did it.

Well, where did the 20 MB of missing memory go? Now, check the size of the Streams Pool:

SQL> show parameter streams_pool_size

NAME TYPE VALUE
------------------------------------ ----------- -----
streams_pool_size big integer 20M

The Streams Pool was 0 earlier, as you intended it to be; but Oracle allocated 20 MB to it by stealing that much memory from the buffer cache. The reason: the Streams Pool was used for the Data Pump Export job, even though it does not sound intuitive. If you check the alert log, you will see the activity recorded there:

$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Thu Apr 18 14:44:46 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/opt/oracle"
adrci> set homepath diag/rdbms/d112d2/D112D2
adrci> show alert -tail –f

Here are the excerpts from the alert log:

2013-04-18 14:48:45.581000 -04:00
streams_pool_size defaulting to 20971520. Trying to get it from Buffer Cache for process 27378.

The next question you may be wondering about is – why did Oracle decide to give only 20 MB to the Streams Pool? Why not 100 MB, or 10 MB? Is it dependent on the size of the table being exported? The answer is no.

Oracle by default gives 10% of the size of the shared pool to the Streams Pool. Let me find out the size of the shared pool:

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- -----
shared_pool_size big integer 200M

The shared pool is 200 MB. 10% of that is 20 MB, which is how much was assigned to the Streams Pool. That size is not dependent on the size of the exported data; but the size of the shared pool.

It's important to understand that the shared pool is used to compute the default size of the streams pool; the actual memory is carved out of buffer cache; not the shared pool.

If you check the database’s operations, you will be able to confirm Oracle’s adjustment of the pools:

SQL> select component, oper_type, parameter, initial_size, target_size, final_size
2 from v$sga_resize_ops
3 order by start_time;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE
--------------- ------------- -------------------- ------------ ----------- ----------
DEFAULT buffer STATIC db_cache_size 0 314572800 314572800
cache
DEFAULT buffer SHRINK db_cache_size 314572800 293601280 293601280
cache
streams pool GROW streams_pool_size 0 20971520 20971520

The output has been truncated to show only the relevant records. From the output you can see clearly that the buffer cache was defined statically as 314572800, or 300 MB initially. Later the buffer cache shrank from 314572800 to 293601280 (about 280 MB). The amount of shrinkage was 314572800 - 293601280 = 20971520 (or, 20 MB), the exact amount the streams_pool_size was allocated.

Why this is a problem? Well, the biggest problem is that the buffer cache size is now reduced without your knowledge. The buffer cache lost 10% of the shared pool. But systems with large shared pool, it could be substantial. Worse, the amount allocated to Streams Pool remains there; it is not returned to the buffer cache as you might expect. You have to manually give it back:

SQL> alter system set streams_pool_size = 0;

In case of a RAC database, it’s possible that only one instance sees this change in Streams Pool size; the other instances will be unaffected.

It would be prudent to note here that this surprise occurs when you do not use automatic SGA settings. When auto SGA is used, i.e. sga_target is set to a non-zero value, you give up complete control to Oracle to manipulate the memory structures. In that case Oracle juggles the memory between various pools – including Streams Pool - without your control anyway.

While it is not very well known, this behavior is not undocumented. It’s mentioned in the Utilities Guide at http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_perf.htm#SUTIL973.

Conclusion

Just because you haven’t defined the streams_pool_size parameter as you don’t use Streams doesn't mean that Oracle will not assign some memory to Streams Pool. Data Pump, which is frequently used in many databases, uses the Streams Pool and Oracle will assign it as 10% of the size of the shared pool and reduce the buffer cache by that amount to fund the memory for the Streams Pool. So you should configure the Streams Pool, even if you don’t use Streams, so that Data Pump can use a precisely allocated pool it rather than stealing it from the Buffer Cache. If you don’t do that now, or don’t intend to do it, then regularly check the streams_pool_size value and set it to zero if it is not so.

Application Design is the only Reason for Deadlocks? Think Again

[Updated on 4/20/2013 after feedback from Charles Hooper, Jonathan Lewis, Laurent Schneider and Mohamed Houri and with some minor cosmetic enhancements of outputs]

Have you ever seen a message “ORA-00060: Deadlock detected” and automatically assumed that it was an application coding issue? Well, it may not be. There are DBA-related issues and you may be surprised to find out that INSERTs may cause deadlock. Learn all the conditions that precipitate this error, how to read the "deadlock graph" to determine the cause, and most important: how to avoid it.

Introduction

I often get a lot of questions in some form or the other like the following:

• What's a Deadlock
• How can I prevent it
• Why would an INSERT cause deadlock
• Why would I need to index FK columns
• Is ON DELETE CASCADE FK constraint a good idea?

Deadlock is one of those little understood and often misinterpreted concepts in the Oracle Database. The word rhymes with locking, so most people assume that it is some form of row locking. Broadly speaking, it’s accurate; but not entirely. There could be causes other than row level locking. This is also often confused by people new to Oracle technology since the term deadlock may have a different meaning in other databases. To add to the confusion, Oracle’s standard response to the problem is that it’s an application design issue and therefore should be solved through application redesign. Well, in a majority of cases application design is a problem; but not in all cases. In this post, I will describe:

1. Why Deadlocks Occur
2. Primer on Oracle Latching, Locking
3. How to Interpret Deadlock Traces
4. Various Cases of Deadlocks
5. Some Unusual Cases from My Experience

Deadlocks Explained

With two Oracle sessions each locking the resource requested by the other, there will never be a resolution because both will be hanging denying them the opportunity to commit ot rollback and therefore releasing the lock. Oracle automatically detects this deadly embrace and breaks it by forcing one statement to roll back abruptly (and releasing the lock) and letting the other transaction to continue.

Here is how a deadlock occurs. Two sessions are involved, doing updates on different rows, as shown below:

Step Session 1           Session 2
---- ------------------- -----------------
1.   Update Row1
     (Does not Commit)
2.                       Update Row2
                        (Does not Commit)
3.   Update Row2
4.   Waits on TX Enqueue
5.                       Update Row1

At the step 5 above since Row1 is locked by session1, session2 will wait; but this wait will be forever, since session1 is also waiting and can’t perform a commit or rollback until that wait is over. But session 1's wait will continue to exist until session 2 commits or rollback - a Catch 22 situation. This situation is a cause of deadlock and Oracle triggers the ststement at Step 3 to be rolled back (since it detected that deadlock). Note that only the statement that detected the deadlock is rolled; the previous statements stay. For instance, update row1 in Step 1 stays.

This is the most common cause of deadlocks and is purely driven by application design and can only be solved by reducing the possibility of occurence of that scenario. Now that you understand how a deadlock occurs, we will explore some other causes of deadlocks. But before that, we will explore different types of locks in Oracle.

Types of Locks

Database locks are queue-based, i.e. the session first waiting for the lock will get it first, before another session which started waiting for the same resource after the first session. The requesters are placed in a queue, hence locks are also called Enqueues. There are several types of enqueues; but we will focus on row locking, and specifically only two type of them:

• TM – this is related to database structural changes. Suppose someone is executing some query against a table, such as SELECTing from it. The table structure should remain the same in that period. TM locks protect the table structure so that someone does not add a column during that query. TM locks allow multiple queries and DMLs, but not DDL against the table.
• TX – this is the row level locking. When a row is locked by a session, this type of lock is acquired.

Anatomy of a Deadlock Trace

When a deadlock occurs and one of the statements gets rolled back, Oracle records the incident in the alert log. Here is an example entry:

 ORA-00060: Deadlock detected. More info in file
/opt/oracle/diag/rdbms/odba112/ODBA112/trace/ODBA112_ora_18301.trc.

Along with the alert log entry, the incident creates a tracefile (as shown above). The trace file shows valuable information on the deadlock and should be your first stop in diagnosis. Let's see the various sections of the tracefile:

Deadlock Graph

The first section is important; it shows the deadlock graph. Here are the various pieces of information on the graph. Deadlock graph tells you which sessions are involved, what types of locks are being sought after, etc. Let's examine the deadlock graph, shown in the figure below:

Row Information

The next critical section shows the information on the rows locked during the activities of the two sessions. From the tracefile you can see the object ID. Using that, you can get the object owner and the name from the DBA_OBJECTS view. The information in on rowID is also available here. You can get primary key information from the object using that rowID.

Process Information

The tracefile also shows the Oracle process information which displays the calling user. That information is critical since the schema owner may not be the one that issued the statement.


With the information collected from various sections of the deadlock graph, you now know the following:

• The session that caused it
• The session that was the victim
• The Oracle SID and process ID of the sessions
• The object (the table, materialized view, etc.) whose row was in the deadlock
• The exact row that was so popular to cause the deadlock.
• The SQL statement that caused the deadlock.
• The machine the session came from with the module, program (e.g. SQL*Plus) and userid information

Now it is a cinch to know the cause of that deadlock and which specific part of the application you need to address to fix it.

Other Causes

The case described above is just one type of locking scenario causing deadlocks; but this is not the only one. Other types of locks also cause deadlocks. These scenarios are usually difficult to identify and diagnose and are often misinterpreted. Well, not for you.You will learn how to diagnose these other causes in this post. These causes include:

1. ITL Waits
2. Bitmap Index Update
3. Direct Path Load
4. Overlapping PK Values

Deadlocks due to ITL Shortage

You can read how ITL works in another of my blogposts - How Oracle Locking Works. In summary, when a session locks a row, it does not go to a central lock repository and get a lock from there. Instead, the session puts the information on the lock in the header of the block, called Interested Transaction List (ITL). Each ITLslot takes up 24 bytes. Figure 1 below shows an empty block with just one ITL slot. When rows are inserted, from bottom of the block upwards, the free space gradually drops.


When a session - session1 - wants to lock the row1, it uses the slot#1 of the ITL, as shown in Figure 3 below. Later, another session – session2 – updates row2. Since there is no more ITL slot, Oracle creates a new slot – slot#2 – for this transaction. However, at this stage, the block is almost packed. If a third transaction comes in, there will be no more room for a third ITL slot to be created; causing the session to wait on ITL. Remember, this new session wants to lock row3, which is not locked by anyone and could have been locked by the session; but it’s artificially prevented from being locked due to the absence of an ITL slot.

Checking for ITL Shortage

You can check for ITL shortage by issuing this query:

select owner, object_name, value
from v$segment_statistics
where statistic_name = 'ITL waits'
and value > 0

Here is a sample output:

OWNER       OBJECT_NAME                    VALUE
----------- ------------------------- ----------
SYSMAN      MGMT_METRICS_1HOUR_PK             19
ARUP        DLT2                              23
ARUP        DLT1                             131

If you check the EVENT column of V$SESSION to see which sessions are experiencing it right now, you will see that the sessions are waiting with the event: enq: TX - allocate ITL entry.

Deadlock Scenario

Here is the scenario where two sessions cause a deadlock due to ITL shortage. Imagine two rows – row1 and row2 – are in the same block. The block is so tightly packed that only two ITL slots can be created.

Step	Session1	Session2
1	Update Table1 Row1 (1 ITL slot is used; no more free ITL slots and no room in the block to create one)
2		Update Table2 Row1 (One ITL slot is gone. There are no more free ITL slots and no room to create one)
3	Update Table2 Row2 (Lack of ITL slots; so this will hang)
4		Update Table1 Row2 (Lack of ITL slots will make this hang as well. Deadlock!)

At Step 4 Session 2's hang can't be resolved until session 1 releases the lock, which is not possible since it itself is hanging. This never ending situation is handled by Oracle by detecting it as a deadlock and killing one of the sessions.

Deadlock Graph

To identify this scenario as the cause of deadlock, look at the deadlock graph. This is how a deadlock graph looks like when caused by ITL waits.



The absence of row information on one of the sessions is a dead giveaway that this is a block level issue; not related to specific rows. Here are the clues in this deadlock graph:

• The lock type is TX (row lock) for both the sessions
• The holders held the lock in "X" (exclusive) mode (this is expected for TX locks)
• However, only one of the waiters is waiting in the "X" mode. The other is waiting with the "S" (shared) mode, indicating that it's not really a row lock the session is waiting for.
• One session has the row information; the other doesn't.

These clues give you the confirmation that this is an ITL related deadlock; not because of the application design. Further down the tracefile we see:



As you can see, it’s not 100% clear from the tracefile that the deadlock was caused by ITL. However by examining the tracefile we see that the locks are of TX type and the wait is in the “S” (shared) mode. This usually indicates ITL wait deadlock. You can confirm that is the case by checking the ITL shortages on that segment from the view V$SEGMENT_STATISTICS as shown earlier.

Update on 4/19/2013: [Thanks, Jonathan Lewis] Occasionally you may see two rows here as well, as a result of a previous wait (e.g. buffer busy wait) on the block which has not been cleaned out yet. In such a case you will see information on two rows; but there are some other clues that may point to this cause. The row portion of the rowid will be 0, meaning it was not a row but the block. The other clue might be that the row information points to a row that has nothing to do with the SQL statement. For instance, you may find the row information pointing to a row in table Table1 whereas the SQL statement is "update Table2 set col2 = 'X' where col1 = 2".

The solution is very simple. Just increase the INITRANS value of the table. INITRANS determines the initial number of ITL slots. Please note, this value will affect only the new blocks; the old ones will still be left with the old values. To affect the old ones you can issue ALTER TABLE TableName MOVE to move the tables to nw blocks and hence new structure.

Deadlock due to Foreign Key

This is a really tricky one; but not impossible to identify. When a key value in parent table is updatd or a row is deleted, Oracle attempts to takes TM lock on the entire child table. If an index is present on the foreign key column, then Oracle locates the corresponding child rows and locks only those rows. The documentation in some versions may not very clear on this. There is a documentation bug (MOS Bug# 2546492). In the absense of the index, a whole table TM lock may cause a deadlock. Let's see the scenario when it happens.

Scenario

Here is the scenario when this deadlock occurs.

Step	Session1	Session2
1	Delete Chaild Row1
2		Delete Child Row2
3	Delete Parent Row1 (Waits on TM Enqueue)
4		Delete Parent Row2 (Waits on TM Enqueue) Deadlock!

Deadlock Graph

This is how the deadlock graph looks like when caused by unindexed foreign key. As you can see, the deadlock graph does not clearly say that the issue was to do with Foreign Key columns not being indexed.Instead, the clues here are:

• TM locks for both the sessions, instead of TX. Remember: TM are metadata related, as opposed to TX, which is a row related lock.
• The lock type of holders is Share Exclusive (SX) as opposed to Exclusive (X)
• Sessions do not show any row information

These three clues together show that this deadlock is due to FK contention rather than the conventional row locks.

So, what do you do? Simple - create the indexes on those FKs and you will not see this again. As a general rule you should have indexes on FKs anyway; but there are exceptions, e.g. a table whose parent key is never updated or deleted infrequently (think a table with country codes, state codes or something pervasive like that). If you see a lot of deadlocks in those cases, perhaps you should create indexes on those tables anyway.

Deadlock due to Direct Load

Direct Load is the fastest way to load data into a table from another source such as a table or a text file. It can be effected in two ways – the APPEND hint in INSERT statement ( insert /*+ append */ ) or by using DIRECT=Y option in SQL*Loader. When a table is loaded with Direct Path, the entire table is locked from further DMLs, until committed. This lock may cause deadlocks, when two sessions try to load into the same table, as shown by the scenario below.

Scenario

Step	Session1	Session2
1	Direct Path Load into Table1
2		Direct Path Load into Table2
3	Direct Path Load into Table2 (Hangs with TM Enqueue; since Session2 has the lock)
4		Direct Load into Table1 TM lock on Table1 prevents this operation Deadlock!

Deadlock Graph

As usual, the deadlock graph confirms this condition. Here is how the deadlock graph looks like:
Both sessions do not show any row information; and subsequent parts of the tracefile do not show any other relevant information. The key to identify this deadlock as caused by Direct Path is to look for the type of lock mode – X. This type of lock mode exists for row level locking as well. However the deadlock graph shows row information in that case. So, the clues for this type of deadlock are:

• Lock type is TM (as shown in the Resource Name)
• Lock mode for both the holders and waiters is X (indicating a row lock)
• No row information (since it is not really row-related)

Deadlock due to Bitmap Index Contention

Bitmap Index is a special type of index that stores bitmaps of actual values and compare bitmaps to bitmaps, e.g. instead of comparing literals such as "A" = "A", Oracle converts the value to a bitmap and compares against the stored bitmap values. For instance “A” might be represented as "01011"; so the comparison will be “01011” = "01011". Index searches are way faster compared to literal comparison.

However, there is a price to pay for this performance. Unlike a regular b*tree index, when a row is updated, the index piece of the bitmap index is locked until the transaction is committed. Therefore udates to any of the rows covered by that index piece hangs. When two sessions update two different rows covered by the same index piece, they wait for each other. Here is the scenario when this condition arises.

Scenario                         

Step	Session1	Session2
1	Update Row 1 (Bitmap index piece is locked)
2		Update Row2 (Hangs for TX Row Lock)
3	Update Row2 (Hangs as bitmap index piece is locked by session2 and can't release until it commits) Deadlock!

Deadlock Graph

You can confirm this occurrence from readling the deadlock graph.



The clues that show this type of deadlock:

• The lock type is TX (as shown in the Resource Name)
• The lock wait mode is “S” (shared) but the type of lock is TX rather than TM.
• The waiter waits with mode "S" instead of "X"
• The row information is available but the object ID is not the ID of the table; but the bitmap index.

The solution to this deadlock is really simple – just alter the application logic in such a way that the two updates will not happen in sequence without commits in between. If that’s not possible, then you have to re-evaluate the need for a bitmap index. Bitmap indexes are usually for datawarehouse only; not for OLTP.

Deadlock due to Primary Key Overlap

This is a very special case of deadlock, which occurs during inserts; not updates or deletes. This is probably the only case where inserts cause deadlocks. When you insert a record into a table but not commit it, the record goes in but a further insert with the same primary key value waits. This lock is required for Oracle because the first insert may be rolled back, allowing the second one to pass through. If the first insert is committed, then the second insert fails with a PK violation. But in the meantime-before the commit or rollback is issued-the transaction causes the second insert to wait and that causes deadlock. Let's examine the scenario:

Scenario

Step	Session1	Session2
1	Insert PK Col value = 1 (Doesn't commit)
2		Insert PK Col value = 2 (Doesn't commit)
3	Insert PK Col = 2 (Hangs, until Session2 commits)
4		Insert PK Col = 1 (Hangs and Deadlock)

Deadlock Graph

The deadlock graph looks like the following.


The key clues are:

• The lock type is TX (row lock)
• The holders are holding the lock in "X" (exclusive) mode
• The waiters are waiting for locks in “S” mode, even when the locks type TX.
• The subsequent parts of the tracefile don’t show any row information.

However, the latter parts of the tracefile shows the SQL statement, which should be able to point to the cause of the deadlock as the primary key deadlock. Remember, this may be difficult to diagnose first since there is no row information. But this is probably normal since the row is not formed yet (it's INSERT, remember?).

Special Cases

I have encountered some very interesting cases of deadlocks which may be rather difficult to diagnose. Here are some of these special cases.

Autonomous Transactions

Autonomous transactions are ones that are kicked off form inside another transaction. The autonomous one follows its own commit, i.e. it can commit independently of the outer transaction. The autonomous transaction may lock some records the parent transaction might be interested in and vice versa – a perfect condition for deadlocks. Since the autonomous transactions is triggered by its parent, the deadlocks are usually difficult to catch.

Here is how the deadlock graph looks like (exceprted from the tracefile)

---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0005002d-00001a40 17 14 X 17 14 X
session 14: DID 0001-0011-00000077
session 14: DID 0001-0011-00000077
Rows waited on:
Session 14: obj - rowid = 000078D5 - AAAHjVAAHAAAACOAAA
(dictionary objn - 30933, file - 7, block - 142, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.

Here are the interesting things about this deadlock graph, which are clues to identifying this type of deadlock:

• The lock type is TX (row lock) and the mode is "X", which is exclusive. This indicates a simple row lock.
• Remember, deadlocks are always as a result of two transactions; not one. However, the deadlock graph shows only one session. The other session information is not even there.The presence of only one session indicates that the other transaction originated from the same session - hence only one session was recorded. The only way two transactions could have originated from the same session is when the transaction is an autonomous one.
• The row information is not there because the autonomous transaction acts independently of the parent.

If you see a deadlock graph like this, you can be pretty much assured that autonomous transactions are to blame.

Update on 4/19/2013. [Thanks, Mohamed Houri] The above cause is not limited to TX locks; it could happen in TM locks as well. The diagnosis remains the same.

Deadlocks among the PQ slaves

Consider a procedural logic like this:

 LOOP

   SELECT /*+ PARALLEL */ … FOR UPDATE

 END LOOP

This code locks the rows selected by the parallel query slaves. Since the select is done in parallel, the PQ slaves distribute the rows to be selected. Therefore the locking is also distributed among the PQ slaves. Since no two rows are updated by the same PQ slave (and hence the same session), there is no cause for deadlocks.

However, assume the code is kicked off more than once concurrently. This kicks off several PQ slaves and many query coordinators. In this case there is no guarantee that two slaves (from different coordinators) will not pick up the same row. In that case, you may run into deadlocks.

Triggers firing Autonomous Transactions

If you have triggers firing Autonomous Transactions, they may cause deadlocks, in the same line described in the section on autonomous transactions.

Freelists

In case of tablespaces defined with manual segment space management, if too many process freelists are defined, it's possible to run out of transaction freelists, causing deadlocks.

In Conclusion

The most common cause of deadlocks is the normal row level locking, which is relatively easy to find. But that's not the only reason. ITL Shortage, Bitmap Index Locking, Lack of FK Index, Direct Path Load, PK Overlap are also some of the potential causes. You must check the tracefile and interpret the deadlock graph to come to a definite conclusion on the cause of the deadlock. Some of the causes, e.g. ITL shortage, are to do with the schema design; not application design and are quite easy to solve. Some causes, as in the case of the PK overlap case, INSERTs cause deadlocks.

I hope you found it useful in diagnosing the deadlock conditions in your system. As always, your feedback is very much appreciated.

Exadata Article as NYOUG's Article of the Year 2012

The Editors of New York Oracle User Group (NYOUG) publication - TechJournal - chose my article Exadata Demystified as the Article of the Year. Here is the snippet from the Editorial:

And the Award Goes To …

The Editor’s Choice Award for 2013 (for papers written and/or presented in 2012) is awarded to Arup Nanda, author of the paper, Exadata Demystified, published in the current issue of the NYOUG Tech Journal.  Arup presented this topic at the December 2012 NYOUG User Group meeting.  A long-time DBA (17 years, so far), Arup is a consummate database professional, two-time recipient of Oracle Magazine’s annual excellence awards (DBA of the year, 2003, and Technologist of the Year, 2012), prolific author (coauthor of 4 books and author of more than 300 articles), and a tireless mentor.  Arup consistently delivers well-researched and engaging papers and presentations, and is a marvelous educator.
His attention to detail and clear expository style help to make each one of his articles an informative read and his presentations an enjoyable educational experience.  If Arup’s Exadata paper had not been chosen for the Editor’s Choice award, his paper, Partitioning: What, When, Why and How, (also published in the current issue) would have taken its place. Whether you agree with my choice of which of his papers is actually more useful for your (or general) purposes, I think you will agree that both are well worth a read, and a re-read, and a forwarding-on. It is a pleasure to have Arup Nanda associated with the NYOUG.

Thank you, per usual, for all of your huge contributions to the Oracle community.

Thank you, Melanie Caffrey - the Editor of TechJournal. I am humbled and without words. Your recognition of my work is very much appreciated.

If you are not a member of NYOUG, would you like to read the paper? Well, Melanie (and NYOUG) has graciously provided the permission for me to reproduce this article on my blog. Here it is. Please feel to download and read. As always, I would very much like to know what you thought.

While on that topic, you may also want to check out my four article series on Exadata Command Reference on Oracle Technology Network. It describes various tools, utilities and commands to become an expert Database Machine Administrator (DMA) of Exadata.

Switching Back to Regular Listener Log Format

Did you ever miss the older listener log file format and want to turn off the ADR-style log introduced in 11g? Well, it's really very simple.

Problem

Oracle introduced the Automatic Diagnostic Repository (ADR) with Oracle 11g Release 1. This introduced some type of streamlining of various log and trace files generated by different Oracle components such as the database, listener, ASM, etc. this is why you didn't find the alert log in the usual location specified by the familiar background_dump_dest initialization parameter but in a directory specified by a diferent parameter - ADR_BASE. Similarly listener logs now go in this format:

$ADR_BASE/tnslsnr//listener/alert/log.xml

Remember, this is in the XML format; not the usual listener.log. The idea was to present the information in the listener log in a consistent, machine readable format instead of the usually cryptic inconsistent older listener log format. Here is an example of the new format:

<msg  time='2013-03-31T13:17:22.633-04:00' org_id='oracle' comp_id='tnslsnr'
  type='UNKNOWN' level='16' host_id='oradba2'
  host_addr='127.0.0.1' version='1'
>
  <txt>31-MAR-2013 13:17:22 * service_update * D112D2 * 0</txt>
</msg>
<msg> time='2013-03-31T13:17:25.317-04:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='oradba2'
 host_addr='127.0.0.1'
>
 <txt>WARNING: Subscription for node down event still pending </txt>
</msg>

Being in XML format, many tools now can be made to read the files unambiguously since the data is now enclosed within meaningful tags. Additionally the listener log files (the XML format) is now rotated. After reaching a certain threshold value the file is renamed to log_1.xml and a new log.xml is created - somewhat akin to the archived log concept in the case of redo log files.
While it proved useful for new tools, there was also the presence of myriads of tools that read the older log format perfectly. So Oracle didn't stop the practice of writing to the old format log. The old format log was still called listener.log but the directory it is created in is different - $ADR_BASE/tnslsnr/Hostname/listener/trace. Unfortunately there is no archiving scheme for this file so this simply kept growing.
In the pre-11g days you could temporarily redirect the log to a different location and archive the old one by setting the following parameter in listener.ora:

log_directory = tempLocation

However, in Oracle 11g R1 and beyond, this will not work; you can't set the location of the log_directory.

Solution

So, what's the solution? Simple. Just set the following parameter in listener.ora:

diag_adr_enabled_listener = off

This will disable the ADR style logging for the listener. Now, suppose you want to set the directory to /tmp and log file name to listener_0405.log, add the following into listener.ora (assuming the name of the listener is "listener"; otherwise make the necessary change below):

log_file_listener = listener_0405.log
log_directory_listener = /tmp

That's it. the ADR style logging will be permanently be gone and you will be reunited with your highly missed pre-11g style logging. You can confirm it:

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                26-NOV-2012 16:50:58
Uptime                    129 days 15 hr. 33 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /tmp/listener_0405.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradba2)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

... output truncated ...

Happy logging.

P.S. By the way, you can also change the values by issuing set commands from LSNRCTL command prompt:

LSNRCTL> set log_file '/tmp'

However, if you have heeded my advice earlier, you might have set admin_restrictions to ON; so can't use the set command. Instead, you would put the value in listener.ora and reload the listener for the desired effect.

My Sessions in RMOUG 2013

Many thanks for attending my sessions (I had a whopping four of them!) ar Rocky Mountain Oracle User Group Training Days 2013. I was pleasantly surprised to find some attending all four. Thank you. You all made my day.

 

Here you can download the slides and the demo scripts I used. Sorry about the delay. I didn't get a chance to post these earlier.

 

1. Exadata for Oracle DBAs
2. RAC for Beginners
3. Beginning Performance Tuning
4. Stats with Confidence
5. Stats with Intelligence

 

 As always, your feedback will be greatly appreciated.

Boston DBA SIG Feb 6, 2013 Meeting Materials

Thank you all for coming to my session - Exadata from Beginner to Advanced in 3 Hours - on an evening in the dead of winter and staying for 4 hours. It was an amazing experience for me to see the sheer volume of interaction, which can only mean the depth of engagement of the attendees - a dream for any speaker. Thank you.

Here is the deck for the session which contains the scripts I used in the demo. Hope you enjoy them. As always, I will appreciate any feedback - good, ugly and anything in between.

Congratulation to those who won the upcoming book from Apress: Expert Exadata Recipes by John Clarke. It's the latest, and arguably the best book on Exadata so far. No; I am not the author and I have no incentive to promote this book in any way. But I have had the privilege to be the technical reviewer and I have first hand account of the quality of the book.

Thanksgiving Thoughts

Today is Thanksgiving Day in the US. For those who are not familiar with American holidays and traditions, Thanksgiving is a really tribute to cooperation and camaraderie. There are many theories and folklore surrounding the concept; but I think one trumps over the rest. More than four scores ago when the pilgrims from England set foot in North America and decided to call it their home, they didn't encounter a Welcome to America sign at JFK airport. Harsh New England weather was just one of the many sobering reminders of the fate of things to come for the visitors. The pilgrims didn't have a Walmart and a credit card to swipe for potatoes and cereal; they had to grow their own food. Well, they did that in their own country; but they had no idea how to do that in this strange land.

Fortunately for them, they got help from the Native Americans who taught them farming in this unfamiliar terrain. Had it not been for those helpful locals, the pilgrims would have perished in the first winter cold and perhaps there would not have been a United States of America later. To show their appreciation, the newly minted “Americans” (of course, a term yet to be used) organized a feast for their comrades and called it a symbol of giving their thanks. It would take hundred and fifty more years before George Washington, the first president of the new nation called USA formally declare the Thanksgiving Holidays as a firmly footed American tradition; but the spirit of the Thanksgiving has been in American hearts since early 17th century. This is time of the year we explicitly give thanks to all those who we benefited from, derive our joys from and to those who define who and what we are today.

I was not born in this country. I came when I was 22 - young and stupid; with a sense of adventure and trepidation at the same time - in some ways like the pilgrims. I was yet another immigrant into the melting pot called the USA; but there is a not a day goes by I think about the wonderful people who helped me through, made me feel at ease - taking me to grocery shopping to ballgames, regaling me with stories of hunting and trekking, and dropping down in the foot-deep snow to change the flat tire of my car. All little to big acts - but all hallmarks of this great land and the people who live in it. I am thankful to all those who have have held my hand in the darkest of the times and sweetest of the moments - and made me who I am today; are doing it and I am sure will continue to do it for the rest of my life.

Happy Thanksgiving!

Presentations at Philadelphia Area Oracle User Group 2012

Thank you all those who came to my sessions at PHLOUG in November. You can download the presentations and the demo scripts I used here.

OOW12: Beginning Performance Tuning

Thank you very much for coming to my session "Beginning Performance Tuning" on the #IOUG track at #OOW12 Oracle Open World 2012. It makes the day for any speaker to see the room filled to capacity even at 9 AM on a Sunday morning. Much, much appreciated.

Here are the slides and the scripts I used in the demos. Please feel free to reuse the slides and scripts for for any purpose. All I ask is to give due credit to me. I imply no warranty and support for the materials. Use your discretion while using.

Quiz: Mystery of Create Table Statement

Happy Friday! I thought I would jumpstart your creative juices with this little, really simple quiz. While it's trivial, it may not be that obvious to many. See if you can catch it. Time yourself exactly 1 minute to get the answer. Tweet answer to me @arupnanda

Here it goes. Database is 11.2.0.3. Tool is SQL*Plus.

The user ARUP owns a procedure that accepts an input string and executes it. Here is the procedure.

create or replace procedure manipulate_arup_schema
(
        p_input_string  varchar2
)
is
begin
        execute immediate p_input_string;
end;
/

The user ARUP have granted EXECUTE privileges on this to user SCOTT. The idea is simple: SCOTT can create and drop tables and other objects in ARUP's schema without requiring the dangerous create any table system privilege.

With this, SCOTT tries to create a table in the ARUP schema:

SQL> exec arup.manipulate_arup_schema ('create table abc (col1 number)')

PL/SQL procedure successfully completed.

The table creation was successul. Now SCOTT tries to create the table in a slightly different manner:

SQL> exec arup.manipulate_arup_schema ('create table abc1 as select * from dual');

It fails with an error:

BEGIN bus_schema.manipulate_bus_schema ('create table abc as select * from dual'); END;

*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at "ARUP.MANIPULATE_ARUP_SCHEMA", line 18
ORA-06512: at line 1

Huh? After checking you did confirm that the user indeed doesn't have the quota on tablespace USERS, so the error is genuine; but how did the first table creation command go through successfully?

Tweet me the answer @arupnanda. Aren't on Twitter? Just post the answer here as a comment. I will post the answer right here in the evening. Let's see who posts the first answer. It shouldn't take more than 5 minutes to get the answer.

Have fun.

Update at the end of the Day. Here is the answer:

Oracle 11g R2 introduced a new feature called deferred segment creation. Segments are stored data objects such as tables, views and materialized views. Prior to Oracle 11gR2, when you created a table, a segment was automatically created. The segment was empty; but created it was. From 11gR2, the table is created only in data dictionary, if there is no data. In the second case, the create table statement used create table as select format, which pulled the data from dual to create the table. However the user didn't have quota on tablespace users; so the statement failed. In the first case, the create table statement merely created the table in dictionary; not the segment. Since there was no segment, there was no space consumption; so the unavailability of quota in the tablespace didn't matter and the statement was successful.

It was a simple puzzle; but I have seen many DBAs, even seasoned ones, stumble over. Eventually they get it; but, well..., they should have taken just a few minutes. From all the responses I got - on twitter and this blog - Yasin Baskan (@yasinbaskan) was the first one to get back with correct answer. Several others did eventually; but Yasin takes the honor of being the first one.

Congratulations, Yasin and thank you all who twitted and posted comments here.