oracle performance

NOTE: There’s a link to the full article at the end of this post.

I recently submitted a manuscript to the EMC XtremIO Business Unit covering some compelling lab results from testing I concluded earlier this year. I hope you’ll find the paper interesting.

There is a link to the full paper at the bottom of this block post. I’ve pasted the executive summary here:

Data visualization is a useful method to identify performance patterns. In most cases, I pull custom performance metrics from AWR repository and use tableau to visualize the data. Of course, you can do the visualization using excel spreadsheet too.

Problem definition
We had huge amount of PX qref waits in a database:

Coming up on May 20, I'll be delivering a webinar entitled Oracle SQL Performance: Writing SQL Right. Registration is now open. Once again the webinar will be hosted by Embarcadero and there will be two sessions - one at 10am ET and one at 2pm ET.

Abstract:

Thank you all for coming to my session Cache Buffer Chains Demystified at Collaborate 14, especially for sticking around for a geeky topic like this to the very end. Much appreciated.

I was not aware that I would not be allowed to use my laptop; so I couldn't show all the demos I so carefully prepared. Please download the scripts and execute them yourself.

As promised, here are the materials I used in the session

The Slide Deck
The Whitepaper
The Scripts (this is a zip file; so right click and Save As ...)

Needless to say, your comments and feedback will be highly appreciated. And, yes, please don't forget to do the evaluation on the Collab Mobile App.

After collaborating with many performance engineers in a RAC database, I have come to realize that there are common pattern among the (mis)diagnosis. This blog about discussing those issues. I talked about this in Hotsos 2014 conference also.

Golden rules

Here are the golden rules of RAC performance diagnostics. These rules may not apply general RAC configuration issues though.

1. Beware of top event tunnel vision
2. Eliminate infrastructure as an issue
3. Identify problem-inducing instance
4. Review send-side metrics also
5. Use histograms, not just averages

Looks like, this may be better read as a document. So, please use the pdf files of the presentation and a paper. Presentation slide #10 shows indepth coverage on gc buffer busy* wait events. I will try to blog about that slide later (hopefully).

Thanks to everyone for attending today's Effective Indexing webinar sponsored by IOUG with Embarcadero. For attendees, IOUG will likely send out a link to the recording and PDF of the presentation, but I also wanted to post it here.

Presentation PDF
Webinar recording

Introduction

I blogged about DFS lock handle contention in an earlier blog entry. SV resources in Global Resource Directory (GRD) is used to maintain the cached sequence values. I will further probe the internal mechanics involved in the cached sequences. I will also discuss minor changes in the resource names to support pluggable databases (version 12c).

SV resources

Let’s create an ordered sequence in rs schema and then query values from the sequence few times.

create sequence rs.test_seq order cache 100;
select rs.test_seq.nextval from dual; -- repeated a few times.
...
/
21

Sequence values are permanently stored in the seq$ dictionary table. Cached sequence values are maintained in SV resources in GRD and SV resource names follows the naming convention to include object_id of the sequence. I will generate a string using a small helper script and we will use that resource name to search in the GRD.

A quick note, Expert Oracle RAC book co-written by me is available now: Expert Oracle RAC 12c. I have written about 6 chapters covering the RAC internals that you may want to learn :) I even managed to discuss the network internals in deep, after all, network is one of the most important component of a RAC cluster.

My next Embarcadero sponsored webinar will be on May 14 and is entitled Using Optimizer Hints for Oracle Performance Tuning.

Register now!

Thanks to everyone who attended my webinar and thanks to Embarcadero (@DBPowerStudio) for hosting it. The presentation and scripts can be downloaded from the following links:

Presentation (PDF)
Scripts (ZIP)