Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Mystats utility

A variation on Jonathan Lewis's SNAP_MY_STATS package to report the resource consumption of a unit of work between two snapshots. Designed to work under constrained developer environments, this version has enhancements such as time model statistics and the option to report on specific statistics. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a free-standing SQL*Plus script (i.e. no installation/database objects needed). June 2007 (updated October 2011)

Runstats utility

A variation on Tom Kyte's invaluable RUNSTATS utility that compares the resource consumption of two alternative units of work. Designed to work under constrained developer environments and builds on the original with enhancements such as "pause and resume" functionality, time model statistics and the option to report on specific statistics. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a free-standing SQL*Plus script (i.e. no installation/database objects needed). January 2007 (updated October 2011)

Row Migration can Aggravate Contention on Cache Buffers Chains Latch

AWR Wait EventsOne of my customers has an Oracle based system with a large number of concurrent users. For time to time, the users would report that the system would 'grind to a halt'. Examination of AWR data showed lots of time spent waiting on latch: cache buffers chains (I discussed how to graph AWR data in Excel in a previous blog entry, which is how I produced this graph of database wait events).

They system had been able to go for weeks without an incident. More recently, as more users have been migrated onto the system, we would a series of days with spikes on this wait event, though they were normally quite short-lived. The incident shown in this chart was unusual in that it lasted most of a day. The chart also shows that a reasonable amount of time was lost on db file sequential read, this is consistent with blocks being loaded into the buffer cache, requiring access to the cache buffer chains, and hence requiring access to the latch that protects these chain.

Concurrent Database SessionsUsing a similar technique I was graph the number of database sessions over time, and I found a similar series of spikes.

By eye I could see that the spikes in the latch contention seemed to correspond to the spikes in the number of concurrent user sessions, and they had roughly the same shape.

I then graphed time waited for this latch against the number of concurrent user sessions and I got a strong, and surprisingly linear, correlation.Cache Buffers Chains Latch Wait -v- Concurrent Sessions

It is very clear that whenever we had more than about 90 concurrent user sessions, the system also lost time waiting on the cache buffers chains latch. Basically, this application won't scale any further!

So, why was the latch being held for such a long time? The buffer cache is 1Gb (with a block size of 8Kb), so it is large, but not excessively so. At this point somebody (and I can't claim the credit for this) thought to check for migrated rows on the tables involved in the longest running SQL statements during the periods of high latch contention, and discovered that a number of the most heavily use tables had a significant quantity of migrated rows.

A row migrates when the data is updated and there is no free space left in the data block to store the new data values. Oracle puts the row into a new block, but it does not update the indexes to point to the new block. Instead, it puts a forwarding pointer into the original block. Therefore, to read the data from a migrated row requires visits to two blocks. When retrieving data into the buffer cache your process need to update two buffer blocks, and must acquire the buffer chains latch twice. Row migration causes poor performance because Oracle must do twice as much work.

There are two factors that come together to cause row migration:

  • A process inserts a row, and then subsequently updates columns on that row, often from null to not null values, but sometimes with just more data. In my case, the application was storing XML structures in a clob (and most of the clobs were small enough to be stored in-line with the rest of the row).
  • There is insufficient free space left in the data blocks to allow for the subsequent updates to be stored in the original data block.

In this particular case, I was dealing with a third-party packaged application. So there was no possibility to change the way the application inserts and updats the data. However, we could and did rebuild the object to eliminate migration of existing rows and set a realistic PCTFREE to reserve free space for future rows.

AWR Wait, magnifiedThis chart shows the same data as the first one, except that I have changed the scale on the y-axis. The tables with the worst row migration were reorganised on the Tuesday night and the system ran on Wednesday under a normal load without any problem.

Contention on the buffer cache chains latch fell to trivial levels, just 408 seconds in 24 hours. Interestingly, the amount of db file sequential read also fell by 70%. This is due to better cache efficiency. Since eliminating much of the row migration, fewer blocks need to be loaded into cache, and so blocks stay in the buffer cache for longer.

This is a real-life example of:

  • why it is important, sometimes critically so, to set physical attributes on tables properly;
  • why it is necessary to understand how your application is updating the database;
  • and what are the potential implications of not doing so!

communication, clarity and the benefits of real names

--- just found this one. I wrote it in Jan 09 and didn't publish it: I think I planned on adding a lot of links and ran out of time. Publishing it under the original date, but if you've been here before, you didn't miss it the first time. I slipped it in ... oh no! I've cheated on the version control system !! :)For the past month, my work has been somewhat disconnected from the database.

Migrate from Windows XP 64bit to Ubuntu Intrepid Ibex 8.10 64bit

I’ve been using the newest and the greatest version of Ubuntu (Intrepid 8.10) for almost a month now and I’m happy with it </p />
</p></div>

    	  	<div class=

As Doug hinted

As Doug hinted in a recent comment, I have been invited to join the Oak Table and I have enthusiastically accepted. It's quite an honor and also, somewhat ironic.At different times in my career, I have found myself in employment discussions with Oracle. However, like star crossed lovers, when one of us was interested, the other was involved elsewhere. At some point, I decided it just wasn't

God rest ye merry gentlemen ...

Unbelievable ... it's already been two weeks since I returned from UKOUG. I enjoyed this year's conference even more than last year, in part because I paced myself better. I was reunited with many wonderful friends and made some new ones too. Add to that a week of top notch presentations, some excellent dinners with good company and it was absolutely perfect.So here are a few of the

Teaching a horse not to eat

My friend Ole told me this story many moons ago - and many times since:

A man decided to teach his horse not to eat anymore. Gradually he reduced the amount of food the horse got each day, and the programme worked really well.

Unfortunately, just as he had finally taught the horse not to eat at all something unforeseen and tragic happened to the horse and it died suddenly.

I was reminded of the story the other day when I was studying this article in my beloved The Economist (I've subscribed non-stop since 1983):

Stopping in a hurry

Dec 11th 2008
From The Economist print edition

Cars are getting better at avoiding collisions. Before long they may be communicating with each other to make roads safer

.... and somewhere in the article this is stated:

"Jan Ivarsson, head of safety at Volvo, believes it should be possible to build a car in which people will not be killed or injured."

On the other hand I can read in various newspapers that Volvo is not doing too well, and may in fact soon be either sold or closed, just like Saab. Or maybe Sweden will try to put those two together and create a small (by international standards) entity that might survive with state funding and what have you.

So you have this carmaker - Volvo - who has been making cars safer and safer and safer over the last several decades, and JUST as they're sensing the possibility of making the perfectly safe car - in which people will not get killed - the carmaker Volvo unfortunately died. Like the horse.

In my own, little world I have also been witnessing how perfect the databases are getting, how much they can do, how much stuff you can put into them in order to save on the application coding and development side - and how coders, developers and programmers have stopped using them. Just as databases were getting damn near perfect ... people stopped using them.

I have for several years now claimed that any computer technology that reached a state of perfection, a plateau of predictability & stability and a high level of usefulness ... will be replaced with something more chaotic and hence much less productive. I have seen no exceptions.

I now realise it is connected: Technology reaching maturty, car safety reaching its logical conclusion - and feeding of horses.

Introduction to advanced queuing

A high-level tutorial on Oracle's Advanced Queuing. July 2005

Miracle's fight for intellectual property rights in China

Here's a conversation I've been having over the last several weeks. Hope you enjoy it... Debra Lilley told me to put it on this blog. I always do what she tells me to do. Mogens.

Dear CEO,

We are EUWIN Network Service Co., Ltd in China, which is responsible for the registration of internet trademark and domain name of global enterprises overseas. Today we received an formal application that an international company named "Robe GmbH" wants to apply to register "miracleas" as its own Internet Trademark and Asia domain name in China. According to registration process, we will do a check to their application, and during the process, our computer database displays that the name is being used by you.
Because the final registration relates to the copyright of the name, to avoid unnecessary disputes of intellectual property rights, so we want to confirm your opinion. Looking forward to your quick reply. If you have any questions, please contact us by telephone or email as soon as possible.

Karen Feng
Principal of Checking Department
Overseas Registration Organization
Tel:+(86)731-8187 719
Fax:+(86)731-8187 739

Mobile:+(86)158 7339 0351
Skype:internetbrand
web:www.euwin.com.cn

==============================================
Hello China-business-girl,

How much for Miracle.China.Com? Very interesting CEO happy day.

Moans Longballs Nogood.
==============================================

Dear CEO,

Thank you for your reply. In order to deal the matter better, I will tell you the price now:the com.cn domain name is 30EUR per year, the aisa domain name is 40EUR per year, and the internet trademark is 100EUR per year.

During our checking period, the owner of the name has the priority to register "miracleas" internet trademark and com.cn domain name. If you want to register it, according to our dispute registration principle, please provide written document(business licence or trademark certificate registered in the local place)to prove you are the owner of "miracleas". We will keep the priority for you during the checking period.

Look forward your early reply. Thank you for your cooperation.

==============================================

But how much for Miracle.China.Com??? Please email in written statement for good happyness here and everywhere for you!

Also, with respect to your irrevocable demands and undisputed popularity will we pay in Icelandic Kroner cheap cheap?
==============================================

Dear Moans Longballs Nogood,

Thank you for your reply. The Miracle.China.Com is 20EUR per year. Meanwhile as the procedure of register:

.I will send a application form to you, please find the attachment.

2.Please choose the domain name and internet trademark whice you wan t to register, then fill in and fax to me as soon as possible.

Please contact with me if you have any problem, It my pleasure to help you. Look forward your early reply!

==============================================

Dear Karen Fang-SAN,

Thank you much. This also will good for Miracle Japan, no?

I have question 100 Euro for Miracle China and 20 Euro for Miracle China is good, yes?

My pleasure your pleasure. It is very early in Dinamarca now, so very early reply! You should go home and eat your children, no?

Happy eating!

Moans Longballes Nogood.

==============================================

Dear Moans Longballes Nogood,

Thank you for your reply, I am terribly sorry to bother you so early. In fact: register the internet trademark and china.com is very good and necessary for your company. You can protect your intellectul property right by it!

I hope you can fax the application to me early, so I can help you to deal the matter better. Wish you have a good rest. And now it is 9:40a.m in China. Ask a question:what the means of "eat your children"? My English is not very good, sorry!

Thank you for your cooperation. Look forward your reply.

==============================================
Dear Karen Euwin Feng,

My Mother's name was Longballs, not Longballes. But OK. I am very tired now, but register is very important, so I must not sheep. I know I must protect intelletual property night and day, no!

You are very helpful and good with deal the matter. Thank you so much! It is now 03.42 in Denmark and I am sleepy. We do not eat children here, what it means is that we must protect children in Darfour, yes! They get killed by Chinese weapons, but guns don't kill bullets do. I understand. We all friends.

Chinese love.

Moans.
==============================================

Dear Moans Longballs Nogood,

Thank you for your reply. I am sorry for my mistake. Register is very important, but the rest is important too. Rest good in order to work well, yes? You can raply me when you in the office, and you can have a rest now.

About the children get killed by chinese weapons, I do not know the matter at all, and I feel very sorry about it, sorry!!! Of course we all friends.

Thank you for your cooperation. Look forward your reply.

==============================================

Dear Karen-SAN,

Rest is neccessary to work good, you are right. Thank you for letting me sheep.

We must both rest now with the children. I have big time thank you for your help.

==============================================

Dear Moans Longballs Nogood,

In order to deal the matter better, could you tell me whether yo have received the application form from me?

If you have received it, please fill in and fax to me as soon as possible, and I ca help you to register the domian name and internet trademark better.

Thank you for your cooperation, look forward your early reply!
==============================================

Dear Karin Feng -

Yes, many applications here in Danmark. CRM, ERP, backend, frontend, office, retail - MANY MANY applications! They must all protected for property now?! This is very serious. I will contact rest of Danmark NOW.

It is now 06.20 here, so early reply.

PS: My real name is Magnus Ling Ming. My father big King Ling Ming Chinese come from Greenland 42 years ago. You know him?

Moans.

Also sell mail and femail stones if interest. You have MANY stones in China, no?
==============================================

Dear Moans,

Thank you for your early reply. I am sorry I do not know how to call you. I think the property's protect is become more and more important,

and we should strengthen our awareness of protecting our right, otherwise it will effect the development. I hope you can make decision early,

so I can help you to deal the matter better. Do you received the application form?

I am sorry I don't know your father, maybe I'm too ignorant, but if you tell more details maybe I can know better. Stones? yes, there are many

stones in China.

Look forward your early decision. Thank you for your time. Wish you have a good weekend!

PS:I'm sorry to bother your so early many times.

Karen Feng
==============================================


My very dear happy Karen,

I am much pleased. My father was belonged to Muslim minority in China (the Han Muslim Minority in Beijing Province he told me many times over and over again when he talked about his home country China).

He is famous! I have photo of my father in front of BIG BIG unhappy tank in Tianenmen Square! That was my father! Reason for my middlename Longballs when the tank hit him!

I think he died later, but not sure. My mother is no big talker.

But I am very proud and happy of him and I smile with happiness when I visit his unknown grave. It has good karma. Big man! I think some things on my body are big because of him, so very very happy.

So how much money for the intellectual rights again? I have not received any papers! Must be very urgent now. Can you fax them? Or send a letter? My country blocks all emails with documents from China. Why I don't know yet.

Just send letter to:

Moans Longballs Nogood
C/O Miracle ASS
Borupvang 2C
2750 Ballerup
Dänemark

and I think I will receive it. Can you send it hurry hurry urgent with jetplane, no?

Love,

Moans

PS: I am in love with you! You are very clever!
==============================================

Dear Moans,

Thank you for your reply. I'm very you can tell me so many details about your father, thanks! The Miracle.China.Com is 20EUR per year, the internet trademark is 100EUR per year. I have send a application form to you, but very pity you didn't receive it. I'll send it again, I hope you can receive it this time. If you can't receive, or I will fax to you later, please give me a early reply! Thank you!

Best regards!

Karen

==============================================
Dear Karen-san,

It is with love I finally write you and your all family (I hope for happiness for all and good luck for the rest! Always!).

I have been very busy in these days. I have a goat that was ill, so I have slept with it for three nights now, and it is better now (love is good healer, no?). I cannot afford to lose goat again. My father always told me to be careful with goats and chicks and not have too many babies. Hahaha. But he is not here anymore, so I do what I must do many more times.

So sorry for not talking to your many four or five emails to me so kind and so loveful.

My mother is a small talker. She say I MUST check on http://www.cnnic.cn/en/index/index.htm to find you and so you are not fake and trying to steal fortune and luck from me!

My mother not understand that I trust and love you, Karen-san, so I must ask you to tell her that it is OK that you are not offical company for me and my many wives?!

My mother's name Karin Ming, just like yours! But she is OK just not big talker. Very very secret woman.

I must protect my company!

I love you more and more and much.

Moans.
==============================================

Dear Moans,

How are you? We have keep the priority for you for a long time, but I don't get any reply from you about the registration.

And today our management give us the last notice, after we tell you once again five days, if we

don't get your any reply, then we will consider that you give up the owner of name "milanplast", meanwhile, we

will obey the registration principles:first application, first possession to accept the Robe company's application,
I hope you can understand our position. In our auditing period, if you have any suspect about their application,

please contact us directly both by email and by phone. Thank you.
Wish you have a nice day.

Karen Feng

==============================================

My Loving Dearest Karen,

I have been very very very busy digging big holes, so no answer you in long days and nights. Sorry, sorry. You forgive me?

My love goat died suddenly. Perhaps the pressure from sleeping with many of my friends? So BIG hole for it in my garden. Big hole! It is Winter here, so ground is frozen. Very very hard work to put goat to the ground.

But also my dear mother died! Same day! She never was big talker, but now she never talks again. She small woman! So not so big hole in frozen garden this time. I decide to let her stand up in hole instead of lying flat on stomach. Much better. And smaller hole.

I am very sorry about ownership MilanPlast. I think perhaps only Miracle register, yes? Now also MilanPlast and Robe company? I'm do not like this. Can I why not just register everything in China and we can all sleep together? Not cheap-cheap, perhaps, but very easy and good for future business, yes no?

Your Moans, My Moans

PS: I travel to Taiwan with my mother and make big hole for her there so we can meet and have dinner-sleep?
==============================================

Dear Moans,
Thank you for your reply. I'm very sorry I have make a mistake, I means the ownership of Miracle but not MilanPlast,
sorry about it. If you want to register domain name and internet trademark, please fill in and fax the application form
to me as soon as possible, so I can help you to register it timely.
Look forward your early reply, thank you for your cooperation.
I'm sorry to hear that your mother was dead, don't be too sad.
Best regards!
Karen Feng
==============================================
Dear Moans Longballs Nogood,
Hope you enjoy your day. I'm very sorry to bother you again, because my management hasten me to deal with the matter earlier. I'm afraid I can't keep the priority for you for any more. So if you want to register the domain name and internet trademark, please give me a reply as soon as possible. Otherwise we'll obey the registration principle to accept the third party's application, help they to register the domain name and internet trademark. Please understand.
Thank you for your cooperation!
Wish you have a good weekend!
Karen Feng
==============================================