Oracle Hacking Session with Tanel Poder – Oracle Parameter Infrastructure

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):

*** 2012-04-12 21:18:41.094
 Processing Oradebug command ‘dump MODIFIED_PARAMETERS 1′
 timed_statistics = TRUE
 timed_os_statistics = 60
 nls_date_format = dd-MON-yy
 optimizer_index_caching = 11
 _serial_direct_read = ALWAYS
 _rowsource_execution_statistics= TRUE
 _rowsource_profiling_statistics= TRUE
 statistics_level = ALL
 _sqlmon_threshold = 5
 _timemodel_collection = TRUE



  1. Tanel
    Thanks for this video. Unfortunately, at around the 38th minutes, the full screen view became very small and hence I couldn’t follow smoothly the content of your sqlplus commands

    Is it possible to fix it?


  2. Hi Mohamed,

    Thanks for letting me know. I haven’t officially launched this video/site yet, so there’s still some tweaking to do. I will reupload the correct video this week…


  1. [...] 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 [...]

  2. [...] to compile a new cursor. The _direct_read_decision_statistics_driven parameter is not part of the optimizer environment, so a new child cursor would not be automatically created after the parameter change (the same [...]

Speak Your Mind