Sunday, November 29, 2009

UNDO Brain Damage

I "met" Mrs. Pedersen on the oracle-l mailing list. I can't remember what the topic was, but she supplied this detailed message full of stats and opinion. I immediately went out and looked for her blog, but couldn't find it. She didn't have one.

So I dropped her a line and offered her some space here.

I won't take (all) the credit, but she has since started her own blog, DBA Kevlar.

Automatic Undo can be one of those areas that make you go "hmmmm..." I am fully aware that the documentation that came with Oracle 9i new features for DBA's when Automatic Undo was introduced clearly claimed, "You won't receive another ORA-01555 again!" yet Oracle hasn't quite lived up to that promise in either 9i or 10g and I haven't had the chance to prove otherwise in 11g.

I had the opportunity to revisit my old friend, "ORA-01555" when one of our environments began to page a fellow DBA with the issue the other day. I became intrigued, as I often am by any database oddity and when I queried the amount of undo the process was consuming, it didn't add up with the amount of undo that was being consumed. In our environment, it is not uncommon for 800GB or more of undo to be utilized, but it became quickly apparent something was amiss when looking at the query below vs. the amount of undo allocation of 1.2TB that had been recently allocated by the DBA on call:
select distinct
vs.sid, vs.osuser,
round((vu.undoblks*32768) / (1024*1024*1024)) GB,
from v$undostat vu, v$session vs
where vu.maxqueryid in vs.sql_id
group by
order by vs.sid
TOTAL GB used from the query above: 45GB

The first aspect I chose to take into consideration is the "tuned undo retention", as many folks forget that the Oracle parameter UNDO_RETENTION can be over-ridden by the SMON if a process requires blocks from the undo to stay unexpired over the set retention period. I have found in my experience, that if there is a rollback issue with an existing process, inspecting the TUNED_UNDO_RETENTION is an important area to start as a DBA.

After verifying that there were over 7000 different maxquerid's in v$undostat, but that only 20% of them were returning when we were looking at active sessions, I queried to find out what queries have undo blocks set with expirations greater than the set time, (currently 43200 seconds) that were also not in active sessions.
select distinct 
v$undostat vu,
v$session vs
where vu.tuned_undoretention > 43200
and vu.maxqueryid not in vs.sql_id
group by
One SQL_ID came up as the offender with the majority of undo blocks retained over the retention period and not in active sessions: 1zuumhmqc93zj

The maxqueryid we are looking for isn't active, but if we searched historical SQL, then we found our statement:
select sql_text
where sql_id = '1zuumhmqc93aj';

select col1
from prod.Tbl1
where cnt = 1
Why would a select statement create undo blocks and how could it be using undo if it isn't a current running process and yet showing active undo blocks???

If the query is not active - what could it be?

select vs.sid
from v$undostat vu, v$session vs
where vu.max_queryid=’1zuumhmqc93aj’
and vu.maxqueryid = vs.prev_sql_id;

We now have the SID currently running that is the cause, why worry about the SQL anymore? It's not the current SQL, but a previous sql_id in an active undo process in v$undostat, so at this point, we need to focus on the SID.

So what transpired here?

Upon inspecting SID 817, it was found to be a background session for Oracle two weeks back where someone had issued a kill session command on a poorly coded process! Oracle decided that the select, which was a full scan on a 59GB table, used a cursor into a for loop, was going to take just over two weeks to rollback the killed session! The process to rollback was so sublime, that it hadn't come up as a red flag on any monitoring script or tool, including OEM!

Since no actual data block changes had been performed, it was perfectly safe to stop the rollback and execute a session kill. To free the undo vs. allowing the kill command to continue, we selected the OS Session from V$SESSION for SID 817, proceeded to kill the process at the OS level and issued a reset on the undo tablespace to clear the remaining undo blocks that were active and/or unexpired:
By resetting the undo, we were able to free up the undo that had been tuned to be retained and were able to disregard the rollback on a select statement that had been killed.

Once we reset the undo, we promptly informed the developer that he/she was never to attempt to use a 59GB non-partitioned table without indexes for a cursor ever, ever again!! :)

Moral of this story
If you have significant undo retained by non-active, non-transactional processes, you should inspect them carefully to verify that they are valid utilization of your undo tablespace. If not, you could receive ORA-01555, snapshot too old errors on transactions that would normally complete successfully, causing incomplete transactions, more rollback occurrence and users asking you why their processes didn't finish.

Kellyn Pedersen is a Performance Tuning and Operations Database Administration Specialist with 10 years of DBA experience in Oracle, SQL Server and MySQL. She is a Database Administrator and Developer at I-Behavior in Louisville, CO, is busy writing articles, sharing tips on her own blog, DBA Kevlar, and will be presenting at RMOUG 2010 in Denver, CO.

She lives in Westminster, CO with her husband and children - who are wonderfully willing to put up with her strange fascination with Oracle's Cost Based Optimizer.

[ kellyn dot pedersen at gmail | Twitter | LinkedIn ]

Teaching APEX

Tomorrow I'll be leaving for Dallas, TX for a 4 day teaching assignment on Application Express.

I'm a bit nervous for multiple reasons.
1. I've never had a problem (well, not in my 30's anyway) getting up and speaking in front of people. My style is light on slides and heavy on winging it. I can do that because I don't often give talks on things I don't know intimately. Don't get me wrong, I have a basic outline to follow, but I have never liked lectures and tried to avoid them at all costs.
2. Leaving the family. The last time I was away from my family for more than a day was 3 years ago, when I moved down to Tampa. Before that, it was 2002 when I went to OOW. I was homesick both times. a strange thing for me. I grew up as an only child and moved 8 I was always the new kid with no friends. I went to college 2000 miles away from home (CO --> FL). It wasn't until I had a family did I first experience the phenomenon.
3. I have to get on a plane. Yes, it's irrational...I know this. You are far more likely to die on the roads (in the US anyway). Again, this has something to do with the family thing. A feeling of mortality? I don't know. I've probably flown 100 times in my life, I used to love flying...the people watching in the terminal, the feeling of takeoff, landing...

In fact, when I was at UF, I rode this plane countless times...without fear. (And I swear there was duct tape on the propellors...)

I've just been out of the habit of flying. No more Peter Pan Invincibility syndrome for me I guess.

I know lots of you travel quite frequently and are non-plussed by it. I will be thinking of all the people that I know that travel on a regular basis. It might require a beer or 3 to get on board this "first" time.

4. Almost forgot, I get to meet OracleNude. That could be scary too. :)

I guess this post should have been titled, A Fear of Flying, would have been more appropriate.

Friday, November 27, 2009

APEX: Manage Applications

Home --> Administrative Services

This is the section where you manage your instance wide application attributes. The only functionality that exists in this section, other than mere reporting, is the ability to change an application from Run and Build Application to Run Application Only.

Application Attributes
In the section you can view the following application attributes:
  • Workspace
  • Application - the number assigned which will be part of the URL
  • Parsing Schema
  • Application Name
  • Last Updated - The user (workspace admin or developer) who last updated this application.
  • Date - Time since the last update
  • Pages - Number of pages in the application
  • Language

Build Status
From here you can view the Build Status of your applications. It will either be Run and Build Application, which means it can be edited by developers and workspace administrators AND viewed by end users. This is typically the status in a non-production environment; or, it will be Run Application Only which is what the status should be in a production environment. It can not be modified in any way but it can be viewed by the end users.

Edit Build Status
The page where you change the Build Status.

Parsing Schemas
Finally, this tells you what the parsing schema is for the application.

Tuesday, November 24, 2009

Playing With PARALLEL

I'm trying to learn about PARALLEL and how it affects SELECT statements in a DW/DSS (non OLTP) environment. I can't really say I know what I'm doing or what I'm looking at, but I'm trying.

I had a DBA strongly discourage me from using PARALLELism on reporting tables (i.e. facts or dimensions). I think he even tried to explain it to me...but it was even more over my head at that time. I think it had something to do with all the processes kicked off then multiplied by the number of users running queries on those tables. "It will bring the database to its knees" he use to say. I never fought much on that because 1, he explained it to me 2, I trusted him and 3, he showed me once all the processes spawned during a typical data load operation. (I thought that was really cool by the way, all those neat little processes doing little chunks of work).

He did however let me (us) use them on staging tables. This is typically where our transformations took place. INSERTs and/or partition swapping.

Anyway, I was reading the 10gR2 docs, the Data Warehousing Guide to be exact on Using Parallel Execution. Lots of neat pictures...which is good for me.

Then I decided to see if I could see what the run times on a SELECT COUNT(*) would be given different levels of parallelism. If you want to know if a table or index has PARALLEL enabled, you need to look at the DEGREE column in %_TABLES and %_INDEXES. I spent about 20 minutes looking for PARALLEL in those tables before I remembered that.
CREATE TABLE big_table 
FROM dba_objects;
I know have 74,431 records to play with. It's not a lot, I know, I just want to see what happens. I then created an anonymous block to:
1. loop through and change the PARALLELism starting with 1 up to 8
2. get the start time from DBMS_UTILITY.GET_TIME
3. loop doing 20 SELECT COUNT(*)s on table table
4. get the end time
5. get the total time
6. print it out
7. put it in a spreadsheet to compare the results side by side
l_start NUMBER;
l_end NUMBER;
l_total NUMBER;
l_dummy INTEGER;
FOR j IN 1..8 LOOP
dbms_output.put_line( 'PARALLEL (DEGREE): ' || j );
FOR i IN 1..20 LOOP
l_start := dbms_utility.get_time;

INTO l_dummy
FROM big_table;

l_end := dbms_utility.get_time;

l_total := l_end - l_start;

dbms_output.put_line( 'Run ' || LPAD( i, 2, '0' ) || ': ' || LPAD( l_total, 3, '0' ) );
And here are my results:

Sorry that it's an image...I can't seem to get decently formatted text into Blogger lately.

For this silly, unscientific test, PARALLEL set to 1 (i.e. NOPARALLEL) was the fastest all the way down. I don't know what it means or if it's even valid, but hey, I'm trying. I figured that if I posted something way above my head all you nice folks out there would happily correct me if I mistated something. I am avoiding any statements, if you couldn't tell.

Sunday, November 22, 2009


Today I made it official...I filed my paperwork with the State of Florida to incorporate. It seemed fairly obvious what the name should be.

If you read the previous post, you probably gathered that little fact.

All of this is related to the fact that I have a new job. I'm not quite ready to talk about the details yet but I believe there is quite a bit of opportunity here. For one, I'll be working with OBIEE again. It's been about 9 months since I last worked with and I was just getting started. There may also be some work with APEX, which would be great. Finally, there's a very outside shot that I may get to work with Exadata v2 in the somewhat near future.

Maybe you're wondering why I incorporated? I don't know, just seemed like fun. Actually, I'll be working as a consultant. This is my first real trek down that road. I have to learn about billable hours, successful travel planning...and probably most importantly, budgeting.

I'm very excited about this opportunity. I will have to travel a bit, but I'll also get to work from home quite a bit so I believe in the long run, family time will probably be greater than when I was going to that "9 to 5" type job where I was actually away from home more than 60 hours a week. We'll see.

How To Apply For an EIN

EIN being an Employer Identification Number.

You can find the start page here at

This is the second time I have done this, well, second time I have had an EIN. I'm pretty sure the lawyer did it the first time around. If I remember correctly, that cost me a whopping $500, 5 years ago. Of course that included incorporation as well.

I remember how daunting it was back then, starting your own business. I felt like I had to hire a lawyer to do it. The truth is, it's very easy to do. Especially if you consider the sheer volume of documentation that exists on the Oracle database...this is a piece of cake.

It took 10 minutes to complete, but most of that time was spent doing screen captures. Twenty steps in all.

Landing page from the link above:

Begin Application:

Choose the type of business:

"Sub" Corporations:

Confirm your selection:

Why do you need an EIN?

Principal Officer?

Individual details:

Location of corporation:

Verify physical location:

"Accept Database Version" is funny. Do most people understand what that would mean? I guessed that's the USPS version of my address...but I tend to work with these types of scenarios.

Corporation details:

More about the corporation:

What does your corporation do?

I selected other because there was no IT type category on there.

What kind of Other?

What kind of Consulting?

How do you want this delivered?


EIN assigned!

Additional information:

Thank you!

Pretty easy all in all. I bet is was a bit harder 5 years ago (i.e. not automated).

Amusingly, this form is only available at certain times of the day. I tried this morning and was told the online assistant didn't work until 7 PM on Sundays. WTF?

Saturday, November 21, 2009

APEX: Create Workspace

Home --> Administrative Services --> Manage Workspaces --> Create Workspace

Since no where I go seems to have heard of Application Express, I have to demo it (locally) and then convince the DBAs to install it. As such, I do tell them that the APEX Administrator (account) has very powerful privileges, including CREATE TABLESPACE.

I don't think it ever truly registered what this means. In talking to my friend and (current) colleague Daniel McGhan (APEX nerd extraordinaire), he reminded me that when you create a new workspace in APEX that it creates a new tablespace as well (if you don't set "Re-user existing schema?" to "Yes") which means a datafile is also created.

Of course I had to see if for myself:
CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/testing/system01.dbf
2 /u01/app/oracle/oradata/testing/sysaux01.dbf
3 /u01/app/oracle/oradata/testing/undotbs01.dbf
4 /u01/app/oracle/oradata/testing/users01.dbf
5 /u01/app/oracle/oradata/testing/example01.dbf

5 rows selected.
That's what I currently have. I'll create a new workspace now to demonstrate. Starting from the Manage Workspace page I select Create Workspace:

I give it a name, APEX_TEST:

On Step 2, I set "Re-user existing schema?" to "No"

Step 3:

Step 4, confirming details:

Confirmation page:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/testing/system01.dbf
2 /u01/app/oracle/oradata/testing/sysaux01.dbf
3 /u01/app/oracle/oradata/testing/undotbs01.dbf
4 /u01/app/oracle/oradata/testing/users01.dbf
5 /u01/app/oracle/oradata/testing/example01.dbf
6 /u01/app/oracle/oradata/testing/FLOW_1172420773490155.dbf

6 rows selected.
As you can see, the datafile FLOW_1172420773490155.dbf was created which is mapped to the like named FLOW_1172420773490155 tablespace.

This can obviously be controlled by selecting "Yes" for "Re-user existing schema?", but I just thought it important enough to point out.

Wednesday, November 18, 2009

APEX: Manage Service > Manage Environment Settings

Home --> Administrative Services --> Manage Service

As I mentioned previously, this is probably the most important area for administering your APEX instance.


There are 2 types of messages you can create, one is the Login message and one is the System Message

The Login message appears at the top of the Workspace Login screen:

The System messages appears after you have logged in.

Feature Configuration
  • Application Development

    1. Allow PL/SQL Program Unit Editing - This pertains to the SQL Workshop. Will you allow end-users (Developers/Administrators), who have access to SQL Workshop, to edit PL/SQL.
    2. Create demonstration objects in new workspaces - By default, when you create a new workspace, the sample application is automatically installed in the schema that it is mapped to. In my case, CJUSTICE. There are database objects included with this object as well. If you would like to see those database objects and need a script to remove them, read here.

      I don't like that it gets created automatically so I typically turn it off.

  • SQL Workshop

    1. SQL Commands Maximum Inactivity in minutes - "Identify the maximum amount of time a transactional command in the SQL Command Processor waits before timing out."
    2. SQL Scripts Maximum Script Output Size in bytes - "Identify the maximum amount of output a single SQL Script can generate. SQL scripts are run from the SQL Workshop."
    3. SQL Scripts Maximum Workspace Output Size in bytes - "Identify the maximum amount of space all scripts within a workspace may consume. SQL Script results are the output generated when running SQL Scripts from the SQL command processor."
    4. SQL Scripts Maximum Script Size in bytes - "Identify the maximum size of a SQL script used within the SQL Workshop."
    5. Enable Transactional SQL Commands - "Enable Transactional SQL Commands for the entire instance. Enabling this feature permits users of SQL Commands to issue multiple SQL commands within the same physical database transaction."

  • Monitoring

    1. Enable Database Monitoring - "Enable or disable monitoring within the SQL Workshop."

    Honestly, I have no idea what kind of monitoring is done here. I haven't been able to find any related reports...which doesn't mean they don't exist, just that I can't find them.
  • Security

    1. Set Workspace Cookie
    2. Disable Administrator Login - Defaults to No (otherwise, how would you log in?)
    3. Disable Workspace Login - Defaults to No
    4. Allow Public File Upload
    5. Restrict Access by IP Address


    1. Require HTTPS - Use a comma delimited list of IP Addresses to restrict, can be either 1 or up to 4 values

  • Session Timeout

    1. Maximum Session Length in Seconds
    2. Maximum Session Idle Time in Seconds

  • Excluded Domains

    1. Domain Must Not Contain - "Enter domains (not including the port), separated by colons, that should not be allowed in regions of type URL or accessed as a Web service. "

  • Account Login Control

    1. Require User Account Expiration and Locking - "Select Yes to enable Application Express user account expiration and locking features across all workspaces. This selection prevents the same feature from being disabled at the workspace level.

      Select No to allow individual workspaces to enable or disable this feature independently.

      Application Express user account expiration and locking features apply to end-user accounts created using the Application Express end-user account management interface."
    2. Maximum Login Failures Allowed - "This setting applies to accounts used to access the Application Express administration and development environment only. It does not apply to end-user accounts used by applications developed in Application Express.

      However, this setting is used as the default workspace-level "Maximum Login Failures Allowed" preference, which workspace administrators can change. The workspace-level preference applies to the end-user accounts within that workspace."
    3. Account Password Lifetime (days) - Self Explanatory

  • Workspace Password Policy

    1. Minimum Password Length - Self Explanatory
    2. Minimum Password Differences - "Enter the number of differences required between old and new passwords. The passwords are compared character by character, and each difference that occurs in any position counts toward the required minimum difference.

      This setting applies to accounts for workspace administrators, developers, and end use"
    3. Must Contain At Least One Alphabetic Character - Self Explanatory
    4. Must Contain At Least One Numeric Character - Self Explanatory
    5. Must Contain At Least One Punctuation Character - Self Explanatory
    6. Must Contain At Least One Upper Case Character - Self Explanatory
    7. Must Contain At Least One Lower Case Character - Self Explanatory
    8. Must Not Contain Username - Self Explanatory
    9. Must Not Contain Workspace Name - Self Explanatory
    10. Must Not Contain - colon seperated list of words that cannot be used in the password, default words are: "oracle:hello:welcome:guest:user:database"
    11. Alphabetic Characters - default: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
    12. Punctuation Characters - default: "!"#$%&()``*+,-/:;<=>?_"

  • Service Administrator Password Policy

    1. Policy - You can either define the Workspace Password Policy (above) or just use the default strong password policy. For my sandbox versions, I tend to make it as lax as possible so I do not have to remember complex passwords when putting things like this together.
Instance Settings

For most organizations, the only 3 areas you need to worry about here are Email, Report Printing and Wallet.
  • Self Service

    1. Provisioning Status - 3 options here: Manual, Request and Email Verification. For most organizations Manual will do just fine. That will allow your end-users to follow the normal protocols set forth in your organization. For hosted sites, like, Email Verification is the way to go.
    2. Development Service URL - Used in conjunction with Request or Email Verification above.

  • Email Provisioning

    1. Email Provisioning - By default, is is enabled, but without the other settings it is useless. I turn this off...but it doesn't really matter for me since I use a sandbox.

  • Schema

    1. Require New Schema - Do you want to require the user to obtain a new schema or reuse an existing one?

  • Email

    1. SMTP Host Address - This is where you set up your internal mail server. All APEX mail calls will use this server by default.
    2. SMTP Host Port - Self Explanatory
    3. Administration Email Address - Self Explanatory
    4. Notification Email Address - Self Explanatory

  • Wallet

    1. Wallet Path - Path to the wallet on the database server.
    2. Wallet Password - Self Explanatory

  • Report Printing

    1. Print Server - Standard is the Default. To enable pdf printing you need BI Publisher and this is where you enter the server information.
    2. Print Server Protocol
    3. Print Server Host Address
    4. Print Server Port
    5. Print Server Script

    For a How To on setting up APEX and BI Publisher, check out the RittmanMead blog entry Oracle ApEx and BI Publisher.

  • New Workspace Request Size

    If using the Self Service module, this is where you'll set the sizes (and the default) of the workspaces to choose from. You can enable them all or just a few.

  • Workspace Change Request Size

    Just like the New Workspace Request Size section, you can modify the size of workspaces you are offering.