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.
We had huge amount of PX qref waits in a database:
A new version 4.1 of the XPLAN_ASH utility is available for download.
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
There are couple of my “gotchas”
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.
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:
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:
A minor update 4.01 to the XPLAN_ASH utility is available for download.
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 18.104.22.168+
- Since version 4.0 added from 22.214.171.124 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
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.
Here are the golden rules of RAC performance diagnostics. These rules may not apply general RAC configuration issues though.
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).
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.