Why doesn’t ALTER SYSTEM SET EVENTS set the events or tracing immediately?

I received a question about ALTER SYSTEM in the comments section of another blog post recently.

Basically the question was that while ALTER SESSION SET EVENTS ‘10046 … ‘ enabled the SQL Trace for the current session immediately, ALTER SYSTEM on the other hand didn’t seem to do anything at all for other sessions in the instance.

There’s an important difference in the behavior of ALTER SYSTEM when changing paramters vs. setting events.

For example, ALTER SYSTEM SET optimizer_mode = CHOOSE would change the value of this parameter immediately, for:

  1. Your own session
  2. All new sessions that will log in will pick up the new parameter value
  3. All other existing sessions

However, when you issue an ALTER SYSTEM SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’, the event changes in only #1 and #2 will happen:

  1. Your own session
  2. All new sessions that will log in will pick up the new event settings

This means that the existing, already logged in sessions, will not pick up any of the events set via ALTER SYSTEM!

Update: Note that since Oracle 11g there’s a parameteter “_evt_system_event_propagation” (default TRUE) and it makes Oracle to propagate the “ALTER SYSTEM” events into existing sessions too. So this problem described above applies only up to Oracle 10.2.

This hopefully explains why sometimes the debug events don’t seem to work. But more importantly, this also means that when you disable an event (by setting it to “OFF” or to level 0) with ALTER SYSTEM, it does not affect the existing sessions who have this event enabled! So, you think you’re turning the tracing off for all sessions and go home, but really some sessions keep on tracing – until the filesystem is full (and you’ll get a phone call at 3am).

Continue reading

Posted in Oracle | 7 Comments

Advanced Oracle Troubleshooting Guide – Part 11: Complex Wait Chain Signature Analysis with ash_wait_chains.sql

Here’s a treat for the hard-core Oracle performance geeks out there – I’m releasing a cool, but still experimental script for ASH (or poor-man’s ASH)-based wait event analysis, which should add a whole new dimension into ASH based performance analysis. It doesn’t replace any of the existing ASH analysis techniques, but should bring the relationships between Oracle sessions in complex wait chains out to bright daylight much easier than before.

You all are familiar with the AWR/Statspack timed event summary below:

AWR top timed events
Similar breakdown can be gotten by just aggregating ASH samples by the wait event:

SQL> @ash/dashtop session_state,event 1=1 "TIMESTAMP'2013-09-09 21:00:00'" "TIMESTAMP'2013-09-09 22:00:00'"

%This  SESSION EVENT                                                            TotalSeconds        CPU   User I/O Application Concurrency     Commit Configuration    Cluster       Idle    Network System I/O  Scheduler Administrative   Queueing      Other MIN(SAMPLE_TIME)                                                            MAX(SAMPLE_TIME)
------ ------- ---------------------------------------------------------------- ------------ ---------- ---------- ----------- ----------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
  68%  ON CPU                                                                          25610      25610          0           0           0          0             0          0          0          0          0          0              0          0          0 09-SEP-13 PM                                                   09-SEP-13 PM
  14%  WAITING SQL*Net more data from client                                            5380          0          0           0           0          0             0          0          0       5380          0          0              0          0          0 09-SEP-13 PM                                                   09-SEP-13 PM
   6%  WAITING enq: HW - contention                                                     2260          0          0           0           0          0          2260          0          0          0          0          0              0          0          0 09-SEP-13 PM                                                   09-SEP-13 PM
   3%  WAITING log file parallel write                                                  1090          0          0           0           0          0             0          0          0          0       1090          0              0          0          0 09-SEP-13 PM                                                   09-SEP-13 PM
   2%  WAITING db file parallel write                                                    730          0          0           0           0          0             0          0          0          0        730          0              0          0          0 09-SEP-13 PM                                                   09-SEP-13 PM
   2%  WAITING enq: TX - contention                                                      600          0          0           0           0          0             0          0          0          0          0          0              0          0        600 09-SEP-13 PM                                                   09-SEP-13 PM
   1%  WAITING buffer busy waits                                                         560          0          0           0         560          0             0          0          0          0          0          0              0          0          0 09-SEP-13 PM                                                   09-SEP-13 PM
   1%  WAITING log file switch completion                                                420          0          0           0           0          0           420          0          0          0          0          0              0          0          0 09-SEP-13 PM                                                   09-SEP-13 PM
   1%  WAITING latch: redo allocation                                                    330          0          0           0           0          0             0          0          0          0          0          0              0          0        330 09-SEP-13 PM                                                   09-SEP-13 PM

The abovementioned output has one shortcoming in a multiuser (database) system – not all wait events are simple, where a session waits for OS to complete some self-contained operation (like an IO request). Often a session waits for another session (who holds some lock) or some background process who needs to complete some task before our session can continue. That other session may wait for a yet another session due to some other lock. The other session itself waits for a yet another one, thanks to some buffer pin (buffer busy wait). The session who holds the buffer pin, may itself be waiting for LGWR, who may in turn wait for DBWR etc… You get the point – sometimes we have a bunch of sessions, waiting for each other in a chain.

The V$WAIT_CHAINS view introduced in Oracle 11g is capable of showing such chains of waiting sessions – however it is designed to diagnose relatively long-lasting hangs, not performance problems and short (but non-trivial) contention. Usually the DIAG process, who’s responsible for walking through the chains and populating V$WAIT_CHAINS, doesn’t kick in after a few seconds of ongoing session waits and the V$WAIT_CHAINS view may be mostly empty – so we need something different for performance analysis.

Continue reading

Posted in Oracle | 12 Comments

I will be speaking at Oracle OpenWorld and Strata + HadoopWorld NY

I will be speaking at a few more conferences this year and thought to add some comments about my plans here too. Here’s the list of my upcoming presentations:

Oracle OpenWorld, 22-26 September 2013, San Francisco

  • Session: Moving Data Between Oracle Exadata and Hadoop. Fast.
  • When: Wednesday, Sep 25. 3:30pm
  • Where: Moscone South 305
  • What: I have been doing quite a lot of work on the optimal Oracle/Exadata <-> Hadoop connectivity and data migration lately, so thought that it’s worth sharing. The fat infiniBand pipe between an Exadata box and a Hadoop cluster running in the Oracle Big Data Appliance gives pretty interesting results.

OakTableWorld, 23-24 September 2013, San Francisco

  • Session: Hacking Oracle Database
  • When: Tuesday, Sep 24. 1:00pm
  • Where: Imagination Lab @ Creativity Museum near Moscone
  • What: The “secret” OakTableWorld event (once called Oracle Closed World :) is an unofficial, fun (but also serious) satellite event during OOW. Lots of great technical speakers and topics. Might also have free beer :) I will deliver a-yet-another hacking session without much structure – I’ll just show how I research and explore Oracle’s new low-level features and which tools & approaches I use etc. Should be fun.

Strata Conference + HadoopWorld NY, 28-30 October 2013, New York City, NY

I have also submitted abstracts to RMOUG Training Days 2014 and will deliver the Training Day at Hotsos Symposium 2014!

So see you at any of these conferences!

Posted in Announcement, Big Data, Exadata, Hadoop, Oracle | 3 Comments

Oracle Performance & Troubleshooting Online Seminars in 2013

In case you haven’t noticed, I will be delivering my Advanced Oracle Troubleshooting and Advanced Oracle Exadata Performance: Troubleshooting and Optimization classes again in Oct/Nov 2013 (AOT) and December 2013 (Exadata).

I have streteched the Exadata class to 5-half days as 4 half-days wasn’t nearly enough to deliver the amount of details in the material (and I think it’s still going to be a pretty intensive pace).

And that’s all for this year (I will write about conferences and other public appearances in a separate post).


Posted in Announcement, Exadata, Oracle | Leave a comment

Scalar Subqueries in Oracle SQL WHERE clauses (and a little bit of Exadata stuff too)

My previous post was about Oracle 12c SQL Scalar Subquery transformations. Actually I need to clarify its scope a bit: the previous post was about scalar subqueries inside a SELECT projection list only (meaning that for populating a field in the query resultset, a subquery gets executed once for each row returned back to the caller, instead of returning a “real” column value passed up from a child rowsource).

I did not cover an other use case in my previous post – it is possible to use scalar subqueries also in the WHERE clause, for filtering the resultset, so let’s see what happens in this case too!

Note that the tests below are ran on an Oracle database (not 12c as in the previous post), because I want to add a few Exadata details to this post – and as of now, 18th August 2013, Smart Scans don’t work with Oracle 12c on Exadata. This will of course change once the first Oracle 12c patchset will be released, but this will probably happen somewhere in the next year.

So, let’s look into the following simple query. The bold red part is the scalar subquery (well, as long as it returns 0 or 1 rows, if it returns more, you’ll get an error during query execution). I’m searching for “objects” from a test_objects_100m table (with 100 Million rows in it), but I only want to process the rows where the object’s owner name is whatever the subquery on test_users table returns. I have also disabled Smart Scans for this query so that the database would behave more like a regular non-Exadata DB for now:

SELECT /*+ MONITOR OPT_PARAM('cell_offload_processing', 'false') */
    SUM(LENGTH(object_name)) + SUM(LENGTH(object_type)) + SUM(LENGTH(owner))
    test_objects_100m o
    o.owner = (SELECT u.username FROM test_users u WHERE user_id = 13)

Note the equals (=) sign above, I’m simply looking for a single, noncorrelated value from the subquery – it’s not a more complex (and unpredictable!) IN or EXISTS subquery. Let’s see the execution plan, pay attention to the the table names and execution order below:

| Id  | Operation                              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                       |                   |     1 |    33 |   405K  (1)| 00:52:52 |
|   1 |  SORT AGGREGATE                        |                   |     1 |    33 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL            | TEST_OBJECTS_100M |  7692K|   242M|   405K  (1)| 00:52:52 |
|*  3 |    TABLE ACCESS STORAGE FULL FIRST ROWS| TEST_USERS        |     1 |    12 |     3   (0)| 00:00:01 |

   2 - filter("O"."OWNER"= (SELECT "U"."USERNAME" FROM "TEST_USERS" "U" WHERE "USER_ID"=13))
   3 - filter("USER_ID"=13)

That sure is a weird-looking execution plan, right?

Continue reading

Posted in Exadata, Oracle, Oracle 12c | 7 Comments

ShowMOS: How to get rid of the annoying “The Page has Expired” dialog in My Oracle Support

So, how many of you do hate the dialog below?

MOS page expired dialog

Good news – there is a fix! (or well, a hack around it ;)

Before showing the fix, you can vote & give your opinion here:

The fix is actually super-simple. The page expiration dialog that grays out the browser screen is just a HTML DIV with ID DhtmlZOrderManagerLayerContainer, overlaying the useful content. If you want it to disappear, you need to delete or hide that DIV in the HTML DOM tree. The javascript code is below (I just updated it so it should work properly on IE too):


Install the fix as a bookmarklet

For convenience, you just need to “install” this javascript code as a bookmarklet. So just drag this ShowMOS link to the bookmarks bar (don’t just click on it here, just drag it to its place).

So, next time you see a MOS page expired in one of your many open browser tabs, you just click on the ShowMOS bookmarklet in the bookmarks bar (instead of the OK button) and the grayed out dialog box disappears – without trying to reload the whole page (and failing). So you’ll still be able to read, copy and scroll the content.

Note 1: I didn’t try to prevent this (client-side) expiration from happening as you might still want to maintain your web session with MOS servers by clicking OK in the dialog.

Note 2: If you have some ad-blockers or javascript blockers enabled in your browser, the ShowMOS link may be re-written by them to “javascript:void(0);” – so the bookmarklet wouldn’t do anything. In this case either temporarily disable the blocker and refresh this page (and then drag the link to a bookmarklet position) or just add it manually with the code above.

Note 3: As I updated the bookmarklet to support IE too, the new code is a bit different. The old code, for those who are interested, is here: javascript:$(DhtmlZOrderManagerLayerContainer).remove();

Update: I have renamed this tool from original (and confusing) C-MOS to ShowMOS.


Posted in Cool stuff, Oracle, Productivity | 19 Comments

Oracle 12c: Scalar Subquery Unnesting transformation

I promised to write about Oracle 12c new features quite a while ago (when 12c got officially released), but I was actually on (a long) vacation then and so many cool 12c-related white-papers and blog entries started popping up so I took it easy for a while. I plan to be focusing on the less known low-level internal details anyway as you see from this blog entry.

As far as I can remember, Oracle has been able to unnest regular subqueries since 8i and merge views since Oracle 8.0.

First, a little terminology:

Update: I have changed the terminology section below a bit, thanks to Jason Bucata’s correction. A scalar subquery sure can also be used in the WHERE clause (as you can see in the comments). So, I clarified below that this blog post is comparing the “Scalar Subqueries in SELECT projection list” to “Regular non-scalar Subqueries in WHERE clause”. I also wrote a Part 2 to explain the scalar subqueries in WHERE clause.

  1. A “regular” subquery in Oracle terminology is a query block which doesn’t return rows back to the select projection list, but is only used in the WHERE clause for determining whether to return any rows from the parent query block, based on your specified conditions, like WHERE v IN (SELECT x FROM q) or WHERE EXISTS (SELECT x FROM inner WHERE inner.v=outer.v) etc.
    The subquery can return zero to many rows for the parent query block for evaluation.
  2. A scalar subquery is a subquery which can only return a single value (single row, single column) back to the parent block. It can be used both in the WHERE clause of the parent query or right in the SELECT list of the parent query instead of some column name. (In this post I am discussing only the case of a scalar subquery in SELECT clause). Whatever the scalar subquery returns, will be put in the “fake” column in the query result-set. If it returns more than one row, you will get the ORA-01427: single-row subquery returns more than one row error, if it returns no rows for the given lookup, the result will be NULL. An example is below.

I crafted a very simple SQL with a scalar subquery for demo purposes (the tables are just copies of all_users and all_objects views):

  , (SELECT MAX(created) FROM test_objects o WHERE o.owner = u.username)
    test_users u
    username LIKE 'S%'

Now, why would you want to write the query this way is a different story. I think it’s actually pretty rare when you need to use a scalar subquery, you usually can get away with an outer join. I have used scalar subqueries for populating some return values in cases where adding a yet another (outer) join to the query would complicate the query too much for my brain (as there are some limitations how you can arrange outer joins). I have only done this when I know that the query result-set (on which the scalar subquery is executed once for every row returned, unless the subquery caching kicks in!) will only return a handful of rows and the extra effort of running the scalar subquery once for each row is acceptable.

Nevertheless, non-experienced SQL developers (who come from the procedural coding world) write lots of scalar subqueries, even up to the point of having every single column populated by a scalar subquery! And this can be a serious problem as this breaks the query into separate non-mergeable chunks, which means that the CBO isn’t as free to move things around – resulting in suboptimal plans.

So, starting from Oracle 12c (and maybe even, the CBO transformation engine can unnest some types of the scalar subqueries and convert these to outer joins internally.
Continue reading

Posted in Oracle, Oracle 12c | 10 Comments

Oracle Database 12c R1 ( is finally released!

You can download it from http://edelivery.oracle.com

And now I (and other beta testers) can finally start writing about the new cool features in it! :)

Looks like only the Linux x86-64, Solaris x86-64 + SPARC ports are available first (as usual).

(just a screenshot below, you’ll need to go to http://edelivery.oracle.com to download the files)

Screen Shot 2013-06-25 at 9.34.22 PM


Update: Also the Oracle 12c Documentation is now available in the OTN website.


Posted in Announcement, Oracle | 9 Comments

Getting the Most Out of ASH online seminar

Just a little reminder – next week (10-11th June) I’ll be delivering my last training session before autumn – a short 1-day (2 x 0.5 days actually) seminar about Getting the Most Out of Oracle’s Active Session History. In the future it will act as sort of a prequel (or preparation) for my Advanced Oracle Troubleshooting class, as the latter one deliberately goes very deep. The ASH seminar’s 1st half is actually mostly about the GUI way of troubleshooting the usual performance problems (EM/Grid Control) and the 2nd half is about all my ASH scripts for diagnosing more complex stuff.

P.S. I’ll also have multiple very cool news in a few months ;-)

Posted in Announcement, Oracle | Leave a comment