R12.2 online patching – What are the hidden costs?

This post originally appeared at the Pythian blog. If you ‘d like to leave a comment, please comment on the original post here.

One of the hot topics at the UKOUG 2011 Technology and E-Business Suite Conference last December was the upcoming release of Oracle e-Business Suite R12.2. The new release will bring us lots of new features, usability improvements and new versions of technology stack components (Oracle Database 11g R2 and Oracle Fusion Middleware 11g R1 as the application server), but the most important and impressive new feature of course will be online patching. Online patching is supposed to change the game completely. All owners of E-Business Suite environments know that patching requires downtime. Although it can be reduced with various techniques (e.g. staged APPL_TOP), some downtime is still required to apply a number of changes. Online patching will not eliminate downtime completely, but will reduce it significantly by using  “Edition Based Redefinition” (EBR) at the database level and using a secondary applications file system for online patching. In fact, all patching activity will be an online operation; downtime will be required only to switch from one version to another.

Back to School: Elementary Physics for DBAs

This post originally appeared at the Pythian blog. If you ‘d like to leave a comment, please comment on the original post here.

“Hello World!” I guess that’s the most appropriate way to start my 1st blog post under pythian.com domain. I’m going to start slow, but hopefully will pick up speed and have at least couple of posts each month to share with you. I’ve been blogging at http://appsdbalife.wordpress.com until now and I haven’t decided yet what the future will be for my previous blog, I wouldn’t like it to become some kind of a zombie page that’s been long dead but still wandering around the internet world.
Enough intros, let’s get to business! I hope this blog post doesn’t get lost in the huge amount of posts related to OOW 2011.

A few days ago I was asked to estimate how much space needed to be added to the ASM diskgroup to handle the database growth for one year without additional need of adding disks. Obviously, to estimate the disk space that needed to be added I had to know what the DB size will be in one year from now. It looked like an easy task as I knew we were recording the size of the database every day.

xx_perf_watcher for monitoring waits during short time intervals

A while ago I worked on a performance troubleshooting case where frequent short time degradation of IO performance on NetApp storage was suspected to be the root cause. The problem was to get some proof as looking at the averages of IO service time was not alarming enough. I decided to write a tool that could be used to monitor wait times for any DB wait event in short intervals, e.g. so I could get measures for db file sequential/scattered read performance each second. I thought for a while and figured out the requirements:

• It should be lightweight and easy to set up;
• the results have to be visible real-time;
• it should be possible to spool results to file;
• it should allow monitoring any wait event;
• it should allow to define interval length between measurement points.

In the end I came up with the solution – a pipelined function, with interval size and name of wait event as parameters, that “queries” the performance metrics using a simple select statement, making it possible to spool the results into a file and seeing results real-time. You can take a look at it in the video below. Continue reading if you’re interested in seeing the source code and reading some explanations on key implementation tricks that made this possible.

How to find tables containing lots of unused space?

Have you ever wondered why the table segment consumes as much space as it does and how does one know if the space allocated by each of the segments is actually used for storing data and is not mostly empty? Those question did bother me time to time and I was looking for a method that would not require to license any packs (like Diagnostic Pack for Segment Advisor, because it requires AWR) and which would not do lots of IOs by scanning the segments. In the end I found a simple solution for this…

How to back up a table properly before installation of an online datafix?

Do you give direct read-write access to the production databases for the functional support and maintenance teams? We try not to and one of the reasons is to test every change before it’s deployed on production. This in turn means that DBAs are the ones who apply all the changes including small datafixes. We have some procedures on how the datafix changes have to be delivered to the DBAs for installation (scripts along with at least approximate information on how many rows should be changed, as well as some evidence that the script has been tested on pre-prod database).

Last week we got a request to install a datafix (replace some values of one column in a particular table ) on a running production database and create a backup of the table before doing the changes. It seems a very simple approach can be used:

1. Create a backup of the table using CTAS statement (create table as select…)
2. Run the provided update statement
3. commit

…but if you think about that, it’s not so straight forward afterall…

Can an autoextensible data file grow bigger then dba_data_files.maxbytes? Sure!

Yesterday I got some alarms about a tablespace running out of free space, closer look revealed that those were false alarms and there was still enough free space to allow segments grow. Probably that is a well known thing for you, but I didn’t know that “bytes” in dba_data_files can get larger then “maxbytes”. That was the case, the script was checking “maxbytes” for autoextensible datafiles despite the fact that the real size (“bytes”) is larger. Continue reading to find out how one can get into this kind of situation.

Why is the context index on the MV so large?

I’ m not saying the context indexes on MVs are always large, but sometimes they can be. Today we found an issue in production database where the token information table (named DR$<index_name>$I)  of the context index named <index name> was ~1.7Gb in size, but the MV table segment which the index was created on was only ~8 Mb. I decided to dig and and find out what the hell is going on there. After a short while it was discovered that there are ~500 sets of token information in the index table for each row in the materialized view. The following demo illustrates how that happened:

UKOUG 2010 presentation “Surviving the Crisis with the Help of Oracle Database Resource Manager”

Here are the slides from my 2nd UKOUG Technology & E-Business Suite 2010 presentation. “UKOUG_TEBS2010_SurvivingTheCrisisWithTheHelpOfOracleDatabaseResourceManager_MarisElsins“. As I mentioned during the presenattion, If you find you have additional questions about the topic when you come back home from The UKOUG 2010 conerence, this is the place to ask them! I’ll do my best to answer them

UKOUG 2010 presentation “Internals of Concurrent Managers”

First of all, thanks to those who attended my presentation at UKOUG Conference Series Technology & E-Business Suite 2010, was nice to talk for such an interested audiance.

As I promised, here is the presentation “UKOUG_TEBS2010_InternalsOfConcurentManagers_MarisElsins“. The post will be updated later with more details, but if you have any additional questions you can meet me at the “Meet the Speakers” events at UKOUG2010 or just leave a comment here.

Is redo size affected by nologging indexes?

Yury recently raised a topic about nologging segments in e-Business Suite database. The discussion later evolved to on how indexes are affected by the nologging setting (is it worth putting indexes in nologging mode to reduce the redo generation). I wasn’t quite sure about the answer, so I decided to test it myself. Read on for results of 22 short testcases (I think the results turned out to be quite interesting and worth knowing).