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 184.108.40.206 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))
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:
Good news – there is a fix! (or well, a hack around it ;)
Before showing the fix, you can vote & give your opinion here:
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.
Update: I have renamed this tool from original (and confusing) C-MOS to ShowMOS.
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.
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.
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)
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 220.127.116.11?), the CBO transformation engine can unnest some types of the scalar subqueries and convert these to outer joins internally. Continue reading →
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 ;-)
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:
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
Run your query in serial, but force the serial direct path reads by setting _serial_direct_read = TRUE (or ALWAYS in 18.104.22.168+)
Here are the valid options for this parameter in 22.214.171.124+
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.
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:
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:
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 :)