Search

OakieTags

Who's online

There are currently 0 users and 24 guests online.

Recent comments

Affiliations

Cool Stuff

Do you like to watch TV? Watch Enkitec.TV from now on! :-)

I’m happy to announce Enkitec TV which is a video channel/aggregator of some of the coolest stuff we do here at Enkitec. I have uploaded some of my videos there, including the previously unpublished Oracle parameters infrastructure hacking session and Kerry’s & Cary’s E4 Exadata interview is available there as well!

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!