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

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

Forcing Smart Scans on Exadata – is the _serial_direct_read parameter safe to use in production?

One of the most common Exadata performance problems I see is that the direct path reads (and thus also Smart Scans) don’t sometimes kick in when running full scans in serial sessions. This is because in Oracle 11g onwards, the serial full segment scan IO path decision is done dynamically, at runtime, for every SQL execution – and for every segment (partition) separately. Whether you get a direct path read & smart scan, depends on the current buffer cache size, how big segment you’re about to scan and how much of that segment is actually cached at the moment. Note that the automatic IO path decision for index fast full scans is slightly different from table scans.

This dynamic decision unfortunately can cause unexpected surprises and variance in your report/batch job runtimes. Additionally, it looks like the SELECT part of your UPDATE/DELETE statements (the select part finds the rows to update/delete) does not ever automatically get direct path read/smart scan chosen – by design! So, when your SELECT statement may use smart scan and be really fast, the same select operation in an INSERT SELECT (or UPDATE/DELETE) context will not end up using smart scans by default. There’s even a bug explaining that – closed as “not a bug” (Exadata Smartscan Is Not Being Used On Insert As Select[Article ID 1348116.1]).

To work around these problems and force a direct path read/smart scan, you can either:

  1. Run your query in parallel as parallel full segment scans will use direct path reads, unless your parallel_degree_policy = AUTO, then you may still get buffered reads thanks to the dynamic in-memory parallel execution decision of Oracle 11.2
  2. Run your query in serial, but force the serial direct path reads by setting _serial_direct_read = TRUE (or ALWAYS in

Here are the valid options for this parameter in

Continue reading

Posted in Exadata, Oracle | 1 Comment

Debugger Dangers – Part 2

About 5 years ago I wrote about the risks that connecting to Oracle processes via debuggers may cause and what are (in my opinion) the safer and less safer options for taking stack samples from running Oracle processes.

In the end of that article I listed different options for getting a stack traces and whether they were safe or not.

For example, ORADEBUG-based process stack traces (DUMP ERRORSTACK, SHORT_STACK and event the process/system state dumps (at level 256 or higher) are not 100% safe – because they alter the execution path of the process they attached to. Your process may crash or get some error if you hit a bug (of course once you patch/fix the bug, you’ll be fine again – until you may hit the next bug).

An example bug is this:


LGWR may hang when SIGUSR interrupts libaio (that’s what LGWR uses for asynchronous IO). And if LGWR is hung – your whole instance will be hung pretty soon. But what does the SIGUSR signal have to do with Oracle? Well, that’s exactly the signal what ORADEBUG sends to the process it has attached to, to notify it about some debug/dump work it needs to do. And apparently receiving or handling such a signal (jumping into some stack dump function), when being in libaio codepath causes an issue.

Continue reading

Posted in Oracle | Leave a comment

List Exadata Storage Cell disk summary with cellpd.sql and cellpdx.sql scripts

In the previous post I explained how to list Exadata disk layout and topology details with the exadisktopo scripts, in this post I’ll introduce one celldisk overview script, which I use to quickly see the celldisk configuration, specs and error statuses. The cellpd.sql script (Cell Physical Disk) will show the following output:

SQL> @exadata/cellpd.sql
Show Exadata cell versions from V$CELL_CONFIG....

-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
FlashDisk           normal                      183         23          8
FlashDisk           not present                 183         23          8                     3
FlashDisk           normal                      366         23         16
FlashDisk           normal                      366         23         16
HardDisk            normal                    22352       1863         12
HardDisk            normal                    22352       1863         12
HardDisk            normal                    22352       1863         12

Above you see that some of my Flash Disks are missing and before they went completely missing (because the Oracle Ninja removed them :) these disks indicated the “poor performance” status.

The cellpdx.sql script will display extended info for each celldisk, including disk model, firmware version, serial number and various error metrics. Warning, lots of wide output coming again:

Continue reading

Posted in Exadata, Oracle | 1 Comment

List Exadata Disk Layout and Topology with the exadisktopo scripts

Here are two more Exadata scripts for listing the end-to-end ASM<->Exadata disk topology from V$ASM_ views and from V$CELL_CONFIG. These scripts see both the ASM level layout and the storage cell-level disk topology.

The exadisktopo.sql script shows all disks starting from the ASM diskgroup layer, going deeper and deeper all the way to the OS disk device level in the storage cells. It uses outer joins, so will show celldisks even if there are no corresponding grid disks allocated on them (or if there are no ASM disks using them). It also shows the Flash cards used as flash cache, thus there are no ASM disks on them usually.

The output should be self-explanatory, you just read the data from left to right (from ASM diskgroup to ASM disk to Grid Disk level etc), the more rightwards you scroll, the “deeper” you go in the Exadata IO stack layers. Warning, lots of wide output coming :)

Continue reading

Posted in Exadata, Oracle | 3 Comments

V$CELL_THREAD_HISTORY – “ASH” for Exadata Storage Cells

Did you know that there’s something like Active Session History also in the Exadata storage cells? ;-)

The V$CELL_THREAD_HISTORY view is somewhat like V$ACTIVE_SESSION_HISTORY, but it’s measuring thread activity in the Exadata Storage Cells:

SQL> @desc v$cell_thread_history
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      CELL_NAME                                VARCHAR2(1024)
    2      SNAPSHOT_ID                              NUMBER
    3      SNAPSHOT_TIME                            DATE
    4      THREAD_ID                                NUMBER
    5      JOB_TYPE                                 VARCHAR2(32)
    6      WAIT_STATE                               VARCHAR2(32)
    7      WAIT_OBJECT_NAME                         VARCHAR2(32)
    8      SQL_ID                                   VARCHAR2(13)
    9      DATABASE_ID                              NUMBER
   10      INSTANCE_ID                              NUMBER
   11      SESSION_ID                               NUMBER
   12      SESSION_SERIAL_NUM                       NUMBER

It keeps about 10 minutes worth of samples of Exadata Storage Cell thread activity:

SQL> @minmax snapshot_time v$cell_thread_history
Show min/max (low/high) values in column "snapshot_time" of table v$cell_thread_history...

----------------- -----------------
20130419 14:42:15 20130419 14:52:54

Note that it’s not the V$ view or the database instance which stores this array – it’s the storage cells themselves. If you query the v$cell_thread_history view, your Oracle database session is going to “gather” this instrumentation data from all the required cells and present it to you, that’s why the “cell statistics gather” wait event shows up:

PARSING IN CURSOR #140596385017248 len=42 dep=0 uid=0 oct=3 lid=0 tim=1366404896817011 hv=4063158547 ad='19e452578' sqlid='63awy1gg
select count(*) from v$cell_thread_history
PARSE #140596385017248:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4272803188,tim=1366404896817010
EXEC #140596385017248:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4272803188,tim=1366404896817127
WAIT #140596385017248: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1366404896817152
WAIT #140596385017248: nam='cell statistics gather' ela= 283 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896818846
WAIT #140596385017248: nam='cell statistics gather' ela= 352 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896819317
WAIT #140596385017248: nam='cell statistics gather' ela= 376 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896820929
WAIT #140596385017248: nam='cell statistics gather' ela= 326 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896822198
WAIT #140596385017248: nam='cell statistics gather' ela= 580 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896823620

Now, how frequently do the cells sample their “ASH” data:

SQL> SELECT DISTINCT (snapshot_time - LAG(snapshot_time) 
                      OVER (ORDER BY snapshot_time)) * 86400 lag_seconds
     FROM (SELECT distinct snapshot_time 
           FROM v$cell_thread_history WHERE cell_name = '');



Looks like the sampling is done exactly once per second!

So, great, what can we do with this data?

Here’s an Exadata performance script (cth.sql) which uses V$CELL_THREAD_HISTORY to dig into cell activity from the database layer.

Continue reading

Posted in Exadata, Oracle | 1 Comment