Search

OakieTags

Who's online

There are currently 0 users and 35 guests online.

Recent comments

Affiliations

Cool Stuff

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

    Asynchronous I/O for people

    Ok, I’ve wanted to write this blog entry for a long time – and now it’s time!

    Most of my blog readers (thank you!) are performance-minded computer enthusiasts, who care about efficiency and optimization. You’ve been tuning SQL execution plans, instance and OS configuration so that your sessions would achieve the same results with less work and also with less waiting!

    You probably know to appreciate why asynchronous I/O must be enabled for busy modern databases, so that your database sessions can do I/O (talk to the storage) without actually having to wait for the I/O operations to complete! You can increase the processing throughput, by not submitting every single I/O separately and waiting for it to complete, before being able to process the results and submit the next one. Asynchronous I/O is a crucial thing for good performance.

    Extended DISPLAY_CURSOR With Rowsource Statistics

    Introduction

    So this will be my Oracle related Christmas present for you: A prototype implementation that extends the DBMS_XPLAN.DISPLAY_CURSOR output making it hopefully more meaningful and easier to interpret. It is a simple standalone SQL*Plus script with the main functionality performed by a single SQL query. I've demoed this also during my recent "optimizer hacking sessions".

    DBMS_XPLAN.DISPLAY_CURSOR together with the Rowsource Statistics feature (enabled via SQL_TRACE, GATHER_PLAN_STATISTICS hint, STATISTICS_LEVEL set to ALL or controlled via the corresponding hidden parameters "_rowsource_execution_statistics" and "_rowsource_statistics_sampfreq") allows since Oracle 10g a sophisticated analysis of the work performed by a single SQL statement.

    How To Cancel A Query Running In Another Session

    This is not really anything new - in fact Tanel Poder has already blogged about it a while ago. Tanel has specifically covered the handling of "urgent" TCP packets and how this could be used to signal a "cancel" to another process, however this only works on Unix environments and not with Windows SQL*Plus clients. In Tanel's article it is also mentioned that there is an officially documented way of doing this via the Resource Manager if you happen to have an Enterprise Edition license.

    In my quick tests however the call to DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS using "CANCEL_SQL" as consumer group only errors out with ORA-29366 saying that the specified consumer group is invalid.

    Profiling trace files with preprocessor external tables in 11g and some parallel execution hacking

    If you work with SQL Trace files (and profile them) then you should check out the awesome novel use of the “external table preprocessor” feature explained by Adrian Billington here:

    Ironically just a day after writing my “Evil things” article, I noticed a note in MOS about how to enable an event 10384 at level 16384 to get a parallel plan to be executed in serial:

    Evil things are happening in Oracle

    Relax, I’m talking about the Oracle Database kernel here, not the corporation ;-)

    Here’s a couple of more reasons why not to play around with undocumented debug events unless you’re really sure why and how would they help to solve your specific problem (and you’ve gotten a blessing in some form from Oracle support too):

    V8 Bundled Exec call – and Oracle Program Interface (OPI) calls

    So, what he hell is that V8 Bundled Exec call which shows up in various Oracle 11g monitoring reports?!

    It’s yet another piece of instrumentation which can be useful for diagnosing non-trivial performance problems. Oracle ASH has allowed us to measure what is the top wait event or top SQLID for a long time, but now it’s also possible to take a step back and see what type of operation the database session is servicing. 

    I am talking about Oracle Program Interface (OPI) calls. Basically for each OCI call in the client side (like , OCIStmtExecute, OCIStmtFetch, etc) there’s a corresponding server side OPI function (like opiexe(), opifch2() etc). 

    Secret hacking session – full scans, direct path reads, object level checkpoints, ORA-8103s! (again)

    I’m mentioning this again just in case you missed the announcement (because I posted it on the weekend):

     

    There will be anotner free Secret hacking session – about full scans, direct path reads, object level checkpoints, ORA-8103s!

    It will happen tomorrow, Tuesday 9th August, online!

     

    Register here:

    See you soon!

     

    Training Schedule for 2011 and Public Appearances

    Online Seminars
    A lot of people have asked me about whether I’d be doing any more seminars in the future. And the answer is yes – at least this year (might be too busy running a company the next year ;-)
    I have finally put together the schedule for my 2011 seminars. In addition to the Advanced Oracle Troubleshooting seminar I will also deliver my Advanced Oracle SQL Tuning and Oracle Partitioning and Parallel Execution for Performance seminars, which I have done only onsite in past.
    So, check out the seminars page:
    Also don’t forget the Expert Oracle Exadata virtual conference next week!
    Public Appearances

    Oracle OpenWorld 2. October
    • I will talk about Large-Scale Consolidation onto Oracle Exadata: Planning, Execution, and Validation
    • Session ID 09355
    Maybe I’ll lurk around the UKOUG venue as well in december ;-)