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.

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.

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:

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.

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.

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.

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.

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:

What to Learn from LinkedIn Password Hack as an Oracle DBA

One of the major news today was the hacking and resultant publishing of passwords in LinkedIn. Didn't hear about it? Well, read it here. In summary, someone smart but with head screwed a little askew decided to pull passwords from LinkedIn account using a little known flaw in the LinkedIn iOS app. LinkedIn later confirmed that leak and asked users to change the password. This created a major ripple effect all over the world. The news competed for attention with others such as Spain's economic reforms; but in the end it managed to rise to the top since many professionals and executives are members of the LinkedIn site and were affected.

Well, what is that to do with being an Oracle DBA - you may ask. Fair question. You see, there is a very important lesson to be learned here from this incident - a lesson commonly ignored by many DBAs, developers, architects and pretty much all users of an Oracle database. Let's see what that is.

Collaborate 2012 Sessions and Select Article

Thank you all who came to my sessions at #IOUG Collaborate 2012 #C12LV on April 22-24 in Las Vegas. I had four full sessions, two panels and one bootcamp. Quite a busy schedule, as you can see. I also worked on some urgent performance issues at work during the week.

You can download the the slides and scripts here. They are available from the IOUG site but I thought I would put them for download here as well.

• Rise of the Machines slides
• Secure Your Database in a Single Day slides scripts
• Beginning Oracle Performance Tuning slides scripts
• How Oracle Locking Works slides

My article Cache Fusion Demystified on SELECT Journal won the Editor's Choice award for 2011. This article is available for free download here.

I hope you like them. As always, feedback - good, bad and something in between - will be appreciated.

Update as of April 29th, 2012

I usually add more stuff to my slides after the session is over. This may result from a direct attendee feedback that some content was not clear, inadequate, misleading or even incorrect. This time is no exception. I added some more content to make the concepts clearer. Also many thanks to Charles Hooper for pointing out an Oracle documentation bug which makes two scripts in my session incorrect. I have since corrected the slides and re-uploaded. Please redownload the slides for How Oracle Locking Works and specifically look at the slides 14, 18, 24, 25 and 26. Also please download the scripts for Beginning Oracle Performance Tuning. More specifically, the script lock1.sql has changed.

The Arup Nanda Blog: AIOUG Webcast:Methodical Performance Tuning Part 2

The Arup Nanda Blog: AIOUG Webcast:Methodical Performance Tuning Part 2

AIOUG Webcast:Methodical Performance Tuning Part 2

Thank you all for attending the Part 2 of the Methodical Performance Tuning series. I hope you got something out of the 1 hour long session. You can download the slides and the scripts I used during the demo here.

As always, I will appreciate any feedback which helps me in designing future content.

Revived Boston Area DBA SIG Meeting

The DBA SIG of the Northeast Oracle User Group has been revived (thank you, Lyson and Jeane) and I was honored to be the speaker of the first session of what I hope will be a long list of very successful like the old days. 

I started at 7 PM and finished at midnight - a solid 5 hours later! Thank you for your patience. It just made my day to have you in the audience that late. I hope you found it useful.

Here is the slide deck and the scripts I used during my session. As in the past, I cherish the moments and will highly appreciate to have your feedback.

Revived NOUG DBA SIG Events

Congratulations to North East Oracle User Group in the Boston area who has restarted the DBA SIG. This was a highly successful program that used to be held after work hours on a weekday in the Oracle building in Burlington. I was privileged to have presented there from 2004 until its sad demise in 2009. Now, I am honored to be invited to be the first speaker in the revived program. I am presenting the session "Addressing Performance Issues during Change with Real Application Testing, Intelligent Stats and SQL Plan Baselines with Live Demos". More information here.

When: Nov 16th 6:30 PM to 9:00 PM
Where: Doubletree by Hilton at 5400 Computer Drive, Westborough, MA 01581
Food and drinks will be served.
The event is free to all NOUG members.

I will give away several Oracle books at the event for the best questions, etc.

If you plan on attending this, the organizers respectfully request that you RSVP to treasurer@noug.com immediately. They need some reasonably accurate headcount to order food and drinks, which is yet another reason to attend.

Abstract: Change is inevitable - be it applying a patchset or creating an index. In this session you will learn how to harness the power of three major features of Oracle database to improve the performance during any types of change, or at any other time. You will learn, with plenty of demos, how to configure and use Database Replay and SQL Performance Analyzer to predict performance, use extended statistics to make the optimizer more intelligent and use SQL Plan Baselines to make the performance consistent but open to further improvements.

As it has been the norm, I plan to explain these concepts and techniques with lots of live demos. If you are in the Boston area, I sincerely hope to see you all there. I have nothing but pleasant memories every one of the 5 times I have presented in that venue and expect nothing less this time.

AIOUG Webcast: Methodical Performance Tuning

A big thank you to all those you attended my session today. I sincerely hope you got something out of it. Here are the scripts I used in the demo. And, here is the slide deck, if you are interested.

Remember, this was just the beginner's session. We will have intermediate and advanced ones in near future. Stay tuned through the AIOUG site.

Migration to Exadata Session at #OOW11

Considering it was the last session of #OOW11 I was surprised to see a sizable number of folks showing up for my 3rd and final session slated for 3 to 4 PM on Thursday. Thank you for attending and for your questions.

Here is the slide deck. Note: please do not click on the link. Instead, right click on it, save the file and open it. It's a Powerpoint show; not a PPT. You can download free Powerpoint player to watch it, if you don't have Powerpoint installed.

Rise of the Machines

This is the penultimate day of #OOW11 and I am here at the hotel lobby trying to put some order around the myriads of nuggets of information I have had over the last several days.

The announcements this year have been centered around introduction of various new products from Oracle - Oracle Database Cloud, Cloud Control, Database Appliance, Big Data Appliance, Exalytics, T4 Super cluster and so on. One interesting pattern that emerges from the announcements that is different  from all the previous years is the introduction of several engineered and assembled systems that perform some type of task - specialized or generic. In the past Oracle announced machines too; but not so many at the same time, leading to an observation by April Sims (Executive Editor, Select Journal) that this year can be summed up in one phrase - Rise of the Machines.

But many of the folks I met in person or online were struggling to put their head around the whole lineup. It's quite clear that they were very unclear (no pun intended) how these are different and what situation each one would fit in. It's perfectly normal to be little confused about the sweet spots of each product considering the glut of information on them and seemingly overlapping functionalities. In the Select Journal Editorial Board meeting we had earlier this morning, I committed to writing about the differences between the different systems announced at #OOW11 and their usages in Select Journal 2012 Q1 edition. I didn't realize at that time what a tall order that is. I need to reach out to several product managers and executives inside Oracle to understand the functionality differences in these machines. Well, now that I have firmly put my feet in mouth, I will have to do just that. [Update on 4/29/2012: I have done that. Please see below]

In the demogrounds I learned about Oracle Data Loader for Hadoop and Enterprise-R, two exciting technologies that will change the way we collect and analyze large data sets, especially unstructured ones. Another new technology, centered around Cloud Control, was the Data Subsetting. It allows you to pull a subset of data from the source system to create test data, mask it if necessary and even find sensitive data based on some format. The tool was due for quite some time.

Again, I really need to collect my thoughts and sort through all that information overload I was subjected to at OOW. This was the best OOW ever.


Update on April 29th, 2011

I knew I had to wrap my head around these announcements and sort through the features available in the engineered machines. And I did exactly that. I presented a paper in the same name - Rise of the Machines - in Collaborate 2012, the annual conference of the Independent Oracle Users Group. Here is the presentation. In that session I explained the various features of 6 machines - Oracle Database Appliance, Exadata, Exalogic, Sparc Super Cluster, Exalytics and Big Data Appliance, the differences between them and where each one should be used. Please download the session if you want to know more about the topic.

Unicode Migration Assistant for Oracle

When you want to convert a database created in the default characterset to a multibyte characterset, there were two basic approaches - the safe export/import and the not-for-the-faint-of-the-heart alter database convert internal. In either case you had to follow a string of activities - checking the presence of incompatible values by running csscan, etc.

There is a new tool from Oracle to make the process infinitesimally simpler - Migration Assistant for Unicode. It's a GUI tool that you can install on the client. A server side API (installed via a patch)  does all the heavy lifting with the client GUI providing a great intuitive interface. You have the steps pretty much laid out for you. But the main strength of the tool is not that. There are two primary differentiators for the tool.

1. When you do have a bad character, what can you really do? You can truncate the part of the data. But how do you know how much to truncate? If you truncate aggressively, you may shave off a chunk and lose valuable data; but be miserly and you risk having the bad data in place. This tool will show the data in a separate window allowing you to correct only the affected data; nothing less, nothing more.
2. When users copy and paste data from some unicode compliant system to Oracle, e.g. from MS Word to a VARCHAR2 field in the database, the characters may look garbled; but given proper characterset they become meaningful. This tool allows you to see the data in many charactersets to identify which one was used to create it in the first place. After that it's a simple matter to reproduce that characters in the proper characterset.

With these two differentiators in place, the tool has great future. Check out everything on this tool at http://www.oracle.com/technetwork/database/globalization/dmu/overview/index-330958.html or just visit the booth at #OOW Demogrounds in Moscone South.

Oh, did I mention that the tool is free?