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 am writing 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
    u.username
  , (SELECT MAX(created) FROM test_objects o WHERE o.owner = u.username)
FROM
    test_users u
WHERE
    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 11.2.0.4?), the CBO transformation engine can unnest some types of the scalar subqueries and convert these to outer joins internally.
Continue reading

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle, Oracle 12c | 8 Comments

Oracle Database 12c R1 (12.1.0.1.0) 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.

 

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Announcement, Oracle | 10 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 ;-)

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

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 11.2.0.2+)

Here are the valid options for this parameter in 11.2.0.2+

Continue reading

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

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:

Bug 15677306 : SUNBT6994922 ORACLE LOGWRITER HARD HANG WHEN SIGUSR INTERRUPTS LIBAIO

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

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle | 1 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....

DISKTYPE             CELLNAME             STATUS                 TOTAL_GB     AVG_GB  NUM_DISKS   PREDFAIL   POORPERF WTCACHEPROB   PEERFAIL   CRITICAL
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
FlashDisk            192.168.12.3         normal                      183         23          8
FlashDisk            192.168.12.3         not present                 183         23          8                     3
FlashDisk            192.168.12.4         normal                      366         23         16
FlashDisk            192.168.12.5         normal                      366         23         16
HardDisk             192.168.12.3         normal                    22352       1863         12
HardDisk             192.168.12.4         normal                    22352       1863         12
HardDisk             192.168.12.5         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

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

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

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Exadata, Oracle | 2 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...

MIN(SNAPSHOT_TIME MAX(SNAPSHOT_TIME
----------------- -----------------
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
t2xs8m'
select count(*) from v$cell_thread_history
END OF STMT
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 = '192.168.12.3');

LAG_SECONDS
-----------

          1

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

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Exadata, Oracle | 1 Comment

Oracle Database 11.2 Upgrade and Migration slides

Check out the extensive slide deck (over 500 slides) about upgrading techniques to Oracle 11.2, by Oracle Corp (Roy Swonger and Mike Dietrich):

It has lots of examples (from real customer upgrade cases) in it.

Thanks to Randolf Geist for telling me about this.

You can also download other interesting presentations from that page (check the right hand side download section), like the Oracle Database Behavior Changes 8.0 through 11.2 doc. Pretty cool!

 

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle | 5 Comments

How to Compose New Gmail Messages in Full Screen (instead of the tiny compose box of new Gmail)

I’m writing this (unusual) post as I am a long time Gmail user and recently I’ve seen plenty of people & articles complain about the Gmail’s new compose window (the one that shows up as a small hovering window in the bottom right of your screen):

gmail_new_compose3

The top google hits so far only return tips to disable the new editor completely, but I want to use the new one, just in a bigger window! There is a very easy workaround for that – and there’s no need to switch back to the old compose mode at all!

If you are using your mouse, then just:

  1. Hold down SHIFT key when clicking the Compose button to get a stand-alone new window for composing a message.
  2. Hold down CTRL (on Windows) or Command (on Mac OSX) key to get a full-screen new browser tab for composing a message.

Examples 1 & 2 below:

gmail_new_window_composegmail_full_screen_compose

If you use Gmail keyboard shortcuts for productivity (they’re awesome!), then you can just:

  1. Press “c” for the new (small) compose box
  2. Press SHIFT-C (capital “C”) for the stand-alone new window for composing a message
  3. Press D for a full screen new browser tab for composing a message

Note that you can view the Gmail shortcuts reference, if this feature is enabled, just by pressing the ? (question mark) key:
gmail_vim_navigation_keys
So, there’s no need to switch back to the old compose mode completely, just remember SHIFT, CTRL and the “D” keys :)

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Productivity | 32 Comments