Search

OakieTags

Who's online

There are currently 0 users and 35 guests online.

Recent comments

Affiliations

performance

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

Parallel Execution Analysis Using ASH - The XPLAN_ASH Tool

Preface

Note: This blog post actually serves three purposes:

  1. It introduces and describes my latest contribution to the Oracle Community,  the "XPLAN_ASH" tool

  • It accompanies a future OTN article on Parallel Execution that will be published some time in the future

  • It is supposed to act as a teaser for my upcoming "Parallel Execution Masterclass" that will be organized by Oracle University and can be booked later this year
  • Table Of Contents

    Introduction

    Real-Time SQL Monitoring Overview

    Real-Time SQL Monitoring Shortcomings

    Adaptive STAT lines in SQL trace

    Lately I’ve been using SQL runtime execution statistics combined with SQL monitor for performance diagnostics and, honestly, almost forgot about SQL trace. So this note is not very useful to me but it might be to someone: along with ALL_EXECUTIONS option appeared in (I believe) 11gR2, there’s a new option starting with 11.2.0.2 which can significantly decrease amount of data in the trace files for STAT lines compared to ALL_EXECUTIONS, still providing some of them from time to time.

    Here is a case to demonstrate:

    Parallel Parking – What is causing the Query to be Executed in Parallel?

    July 31, 2012 I encountered an interesting OTN thread that caused me to stop and think for a couple of minutes.  The OP (original poster) in the OTN thread wondered why parallel execution was used for a query when the parallel degree for the table accessed by the query and its indexes were specified to have [...]

    Proactive Tuning

    There’s no such thing as proactive tuning. Either you do it right before it goes into production (in which case it’s called “design”) or you react to the nasty surprises you get after it’s gone live.

    Even if you do something about the problems before anyone has complained that’s still “reactive” not “proactive” – and the trick is to be reactive as soon as possible. For most companies the best strategy is probably to stick a big screen on the wall with the “Top Activity” page from OEM (or equivalent) so that the key players (DBAs and Developers) can keep a casual eye out for resource hogs and prioritise dealing with them as they appear.

    Parallel Execution Challenge – It’s About Time

    July 8, 2012 As I was reading a book, I saw a comment essentially stating that with multiple CPUs/cores, parallel execution will be faster than serial execution of the same query.  The book provided a test case - I decided to change around the test case a bit so that the results were a bit more fair, [...]

    n/a

    Speaking at E4!

    I’ll be co-speaking with Randy Johnson (one of the authors of Expert Oracle Exadata) at E4 to share about the war stories and detail on a bunch of technical stuff on a Peoplesoft and BIEE consolidation project we had on one of our clients. See the abstract below:

    The effect of ASM redundancy/parity on read/write IOPS – SLOB test case! for Exadata and non-Exa environments

    Last week I had a lengthy post at oracle-l that tackles Calibrate IO, Short Stroking, Stripe size, UEK kernel, and ASM redundancy effect on IOPS Exadata which you can read here
    followed by interesting exchange of tweets with Kevin Closson here (see 06/21-22 tweets) which I was replying in between games at UnderwaterHockey US Nationals 2012 which we won the championship for the B division ;) I have my awesome photo with the medal here

    Different Execution Plans when Converting SQL from SELECT to UPDATE … IN SELECT, ROWNUM Related Bug in 11.2.0.2

    June 28, 2012 (Modified June 28, 2012) Sometimes it is possible to build a very effective test case to reproduce a particular problem.  Sometimes the test case drifts a bit offtrack, in the process revealing other information that is not directly related to the original problem that the test case attempted to simulate.  You will [...]

    Website Performance

    A short update on the how, what and where. I got really annoyed with the website performance, so hereby a different look and feel that hopefully (trimmed and all) will provide a faster load time of pages and other material. I also introduced some caching mechanisms that hopefully will be helpful. A faster reference to

    Read More…