Here’s the Part 2 of the Oracle SQL plan execution hacking session (Part 1 is here)!

We’ll use a bit of DTracing to peek inside an Oracle SQL plan execution, plus some other random experiments. You can download the qer_trace.sh script from here. Note that this is not a “How to use DTrace” webinar, you have to learn DTrace yourself to understand (or enhance) this script.

Check out this bit from my Exadata speech at the previous Enkitec E4 conference. You don’t see much of my screen output there, but if you attended my Exadata hacking session (or have watched the videos), then you’ll recognize what I’m talking about.

Exadata Snapper (also called ExaSnapper or ExaSnap) is a new tool by Tanel Poder, which allows to “peek” into some SQL Execution-related cell metrics without leaving the Oracle database session / sqlplus prompt. The current version 0.7 is a beta version and will likely be much improved and changed once it reaches v1.0.

The tool itself will be downloadable from enkitec.com (will update this post once it’s there).

This hacking session’s topic is Oracle parameter infrastructure. We will look into some internals of Oracle instance, session and optimizer environment parameter management. This session does not attempt to explain what all the individual parameters do, but how parameters work in general.

This knowledge can be useful for explaining some seemingly weird things happening in Oracle

1) Did you know that there are multiple different types of configuration parameters inside Oracle?
2) Do you want to know why changing some session parameters causes Oracle to recompile a new child cursor, while some parameters (notably the _small_table_threshold and _serial_direct_read) don’t?
3) Where are the session-level parameter values kept?
4) How to see what Oracle parameters some other session is using at the moment?

NB!!! Note that as the name says, this is a free hacking session – informal, fun and unstructured online hacking with no slides and no particular sturcture – it’s all demos in sqlplus.

Note that I had some trouble with my recording software in the end, so part of the question #2 will be answered in a future video!

By the way, I didn’t mention this in the hacking session, but ORADEBUG DUMP PARAMETERS 1 allows you to dump all *modified* parameters of another session (if you connect to that session’s process with oradebug first):