SGA bigger than than the amount of HugePages configured (Linux –

I just learned something new yesterday when demoing large page use on Linux during my AOT seminar.

I had 512 x 2MB hugepages configured in Linux ( 1024 MB ). So I set the USE_LARGE_PAGES = TRUE (it actually is the default anyway in This allows the use of large pages (it doesn’t force, the ONLY option would force the use of hugepages, otherwise the instance wouldn’t start up). Anyway, the previous behavior with hugepages was, that if Oracle was not able to allocate the entire SGA from the hugepages area, it would silently allocate the entire SGA from small pages. It was all or nothing. But to my surprise, when I set my SGA_MAX_SIZE bigger than the amount of allocated hugepages in my testing, the instance started up and the hugepages got allocated too!

It’s just that the remaining part was allocated as small pages, as mentioned in the alert log entry below (and in the latest documentation too – see the link above):

Thu Oct 24 20:58:47 2013
Thu Oct 24 20:58:54 2013
Shutting down instance (abort)
License high water mark = 19
USER (ospid: 18166): terminating the instance
Instance terminated by USER, pid = 18166
Thu Oct 24 20:58:55 2013
Instance shutdown complete
Thu Oct 24 20:59:52 2013
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 1024 MB (85%)

Large Pages used by this instance: 512 (1024 MB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 16 MB)
Large Pages configured system wide = 512 (1024 MB)
Large Page size = 2048 KB

  Total Shared Global Region size is 1202 MB. For optimal performance,
  prior to the next instance restart increase the number
  of unused Large Pages by atleast 89 2048 KB Large Pages (178 MB)
  system wide to get 100% of the Shared
  Global Region allocated with Large pages

The ipcs -m command confirmed this (multiple separate shared memory segments had been created).

Note that despite what documentation says, there’s a 4th option, called AUTO for the USE_LARGE_PAGES parameter too (in I think), which can now ask the OS to increase the number of hugepages when instance starts up – but I would always try to pre-allocate the right number of hugepages from start (ideally right after the OS reboot – via sysctl.conf), to reduce any overhead potential kernel CPU usage spikes due to search and defragmentation effort for “building” large consecutive pages.

Marting Bach has written about the AUTO option here.

Posted in Linux, Oracle | 12 Comments

Enkitec Rocks Again!!!

Yes, the title of this post is also a reference to the Machete Kills Again (…in Space) movie that I’m sure going to check out once it’s out ;-)

Enkitec won the UKOUG Engineered Systems Partner of the Year award last Thursday and I’m really proud of that. We got seriously started in Europe a bit over 2 years ago, have put in a lot of effort and by now it’s paying off. In addition to happy customers, interesting projects and now this award, we have awesome people in 4 countries in Europe already – and more to come. Also, you did know that Frits Hoogland and Martin Bach have joined us in Europe, right? I actually learn stuff from these guys! :-) Expect more awesome announcements soon! ;-)

Thank you, UKOUG team!

Also, if you didn’t follow all the Oracle OpenWorld action, we also got two major awards at OOW this year! (least year “only” one ;-)). We received two 2013 Oracle Excellence Awards for Specialized Partner of the Year (North America) for our work in both Financial Services and Energy/Utilities industries.

Thanks, Oracle folks! :)

Check out this Oracle Partner Network’s video interview with Martin Paynter where he explains where we come from and how we get things done:

Oh, by the way, Enkitec is hiring ;-)

Posted in Announcement, Oracle | 2 Comments

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