Search

OakieTags

Who's online

There are currently 0 users and 22 guests online.

Recent comments

Affiliations

11g

warning: Invalid argument supplied for foreach() in /www/oaktable/sites/all/modules/cck/content.module on line 1284.

Data visualization, px qref waits, and a kernel bug!

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:

New Version Of XPLAN_ASH Utility

A new version 4.1 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version in particular supports now the new 12c "Adaptive" plan feature - previous versions don't cope very well with those if you don't add the "ADAPTIVE" formatting option manually.

Here are the notes from the change log:

- GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR can now be customized in the
settings as table names in case you want to use your own custom monitoring repository that copies data from GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR in order to keep/persist monitoring data. The tables need to have at least those columns that are used by XPLAN_ASH from the original views

n/a

Rolling upgrads using logical standby database.

Couple of weeks ago there was a Twitter discussion started by Martin Bach (@MartinDBA) about cases for logical standby implementation. A rolling upgrade was mentioned by Tim Gorman (@timothyjgormanas) as one of potential recommendations for using this rare use product. I have been involved in such project in the past and I prepared an instruction and did quite large number of rolling upgrades from 11.1 into 11.2.

There are couple of my “gotchas”

Oracle 11gR2 and 12cR1 on Oracle Linux 7 beta

I’ve been having a play with Oracle Linux 7 beta over the weekend. Not surprisingly my first thoughts were to install the Oracle database on it.

As expected, the installations were almost identical or Fedora 19.

Analysing Parallel Execution Skew - Without Diagnostics / Tuning Pack License

This is the third part of the video tutorial "Analysing Parallel Execution Skew". In this part I show how to analyse a parallel SQL execution regarding Parallel Execution Skew.

If you don't have a Diagnostics / Tuning Pack license the options you have for doing that are quite limited, and the approach, as demonstrated in the tutorial, has several limitations and shortcomings.

Here is the video:

Analysing Parallel Execution Skew - Data Flow Operations (DFOs) And DFO Trees

This is the second part of the video tutorial "Analysing Parallel Execution Skew". In this part I introduce the concept of "Data Flow Operations (DFOs)" and "DFO Trees", which is what a Parallel Execution plan is made of. DFOs / DFO Trees are specific to Parallel Execution and don't have any counterpart in a serial execution plan.

Understanding the implications of DFOs / DFO Trees is important as prerequisite for understanding some of the effects shown in the later parts of the video tutorial, hence I covered this as a separate topic.

Note that this tutorial also demonstrates some new 12c features regarding Parallel Execution, in particular how Oracle 12c now lifts many of the previous limitations that lead to the generation of multiple DFO Trees.

Here is the video:

New Version Of XPLAN_ASH Utility

A minor update 4.01 to the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

These are the notes from the change log:

- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data

- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part of the PX_FLAGS column) on 11.2.0.2+

- Since version 4.0 added from 11.2.0.2 on the PX *MAX* DOP in the "SQL statement execution ASH Summary" based on the new PX_FLAGS column of ASH it makes sense to add a PX *MIN* DOP in the summary to see at one glance if different DOPs were used or not

Golden rules of RAC performance diagnostics

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).

Upgrades to 11g are finally complete

Just a little slice of reality to cut through all the 12c stuff that is floating around at the moment. I’ve just moved the last of our databases to 11g. Yay! As well as upgrading, we’ve been culling or consolidating old and unused stuff, which has drastically reduced and simplified our Oracle database landscape.

We currently have four projects running databases on HP-UX on Itanium (spit), one project on Solaris and the rest on Oracle Linux under VMware. If I had my way we would kick out HP-UX and Solaris and do everything on Oracle Linux.

We’ve still got one project on 11gR1, but that is being held back intentionally because of some issues with the vendor of the application that runs against it. Hopefully that will soon be on 11gR2 also.