dborasol

One of OBIEE’s many great strengths is aggregate navigation; the ability to choose from a list of possible tables the one which will probably give the optimal performance for a given user query. Users are blissfully unaware of which particular table their query is being satisfied from, since aggregate navigation happens on the BI Server once the user’s request comes through from an Analysis or Dashboard.

This seamless nature of aggregate navigation means that testing specific aggregates are working can be fiddly. We want to ensure that the aggregates we’ve built are (i) being used when appropriate and (ii) showing the correct data. This is the particularly the case in Exalytics when aggregates are put into in-memory (TimesTen) by the Summary Advisor and we need to validate them.

Whilst the log file nqquery.log (or Usage Tracking table S_NQ_DB_ACCT) tells us pretty easily which table a query used, it is nice to be able to switch a query easily between possible aggregate sources to be able to compare the data. This blog demonstrates how we can use the INACTIVE_SCHEMAS variable (as described in my previous blog on loading Exalytics incrementally) to do this.

INACTIVE_SCHEMAS is a Logical SQL variable that tells the BI Server to exclude the specified physical schema(s) from consideration for resolving an inbound query. Normally, the BI Server will parse each incoming query through the RPD, and where a Logical Table has multiple Logical Table Sources it will evaluate each one to determine if it (a) can satisfy the query and (b) whether it will be the most efficient one to use. By using INACTIVE_SCHEMAS we can force the BI Server to ignore certain Logical Table Sources (those associated with the physical schema specified), ensuring that it just queries the source(s) we want it to.

In the following example, the data exists on both Oracle database, and TimesTen (in-memory). Whilst the example here is based on an Exalytics architecture, the principle should be exactly the same regardless of where the aggregates reside. This is how the RPD is set up for the Fact table in my example:

The GCBC_SALES schema on Oracle holds the unaggregated sales data, whilst the EXALYTICS schema on TimesTen has an aggregate of this data in it. The very simple report pictured here shows sales by month, and additionally uses a Logical SQL view to show the contents of the query being sent to the BI Server:

Looking at nqquery.log we can see the query by default hits the TimesTen source:

[...]
------------- Sending query to database named TimesTen aggregates
WITH
SAWITH0 AS (select distinct T1528.Sale_Amoun000000AD as c1,
     T1514.Month_YYYY000000D0 as c2
from
     SA_Month0000011E T1514,
     ag_sales_month T1528
[...]

Now, for thoroughness, let’s compare this to what’s in the TimesTen database, using a Direct Database Request:

OK, all looks good. But, is what we’ve aggregated into TimesTen matching what we’ve got in the source data on Oracle? Here was can use INACTIVE_SCHEMAS to force the BI Server to ignore TimesTen entirely. We can see from the nqquery.log that OBI has now gone back to the Oracle source of the data:

[...]
------------- Sending query to database named orcl
WITH
SAWITH0 AS (select sum(T117.FCAST_SAL_AMT) as c1,
     T127.MONTH_YYYYMM as c2
from
     GCBC_SALES.TIMES T127 /* Dim_TIMES */ ,
     GCBC_SALES.SALES T117 /* Fact_SALES */
[...]

and the report shows that actually we have a problem in our data, since what’s on the source doesn’t match the aggregate:

A Direct Database Request against Oracle confirms the data we’re seeing – we have a mismatch between our source and our aggregate:

This is the kind of testing that it is crucial to perform. Without proper testing, problems may only come to light in specific reports or scenarios, because by the very nature of aggregate navigation working silently and hidden from the user.

So this is the feature we can use to perform the testing, but below I demonstrate a much more flexible way that having to build multiple reports.

Implementing INACTIVE_SCHEMAS

Using INACTIVE_SCHEMAS in your report is very simple, and doesn’t require modification to your reports. Simply use a Variable Prompt to populate INACTIVE_SCHEMAS as a Request Variable. Disable the Apply button for instantaneous switching when the value is changed.

A Request Variable will be prepended it to any logical SQL sent to the BI Server. Save this prompt in your web catalog, and add it to any dashboard on which you want to test the aggregate:

Even better, if you set the security on the dashboard prompt such that only your admins have access to it, then you could put it on all of your dashboards as a diagnostic tool and only those users with the correct privilege will even see it:

Displaying the aggregate source name in the report

So far this is all negative , in that we are specifying the data source not to use. We can examine nqquery.log etc to confirm which source was used, but it’s hardly convenient to wade through log files each time we execute the report. Ripped off from Inspired by SampleApp is this trick:

1. Add a logical column to the fact table
2. Hard code the expression for the column in each Logical Table Source
3. Bring the column through to the relevant subject area
4. Incorporate it in reports as required, for example using a Narrative View.

Bringing it all together gives us this type of diagnostic view of our reports:

Summary

There’s a variety of ways to write bespoke test reports in OBI, but what I’ve demonstrated here is a very minimal way of overlaying a test capability on top of all existing dashboards. Simply create the Request Variable dashboard prompt, set the security so only admins etc can see it, and then add it in to each dashboard page as required.

In addition, the use of a ‘data source’ logical column in a fact table tied to each LTS can help indicate further where the data seen is coming from.

As many know, I joke about being a strange, ADHD kid.  I didn’t really fit in with the other kids and was really, really small for my age.  I was bullied and very much a loner.  Growing up, my closest friend was my middle sister, Kristi who is 2 1/2 years younger.  She was the golden child-  blond hair, blue eyes and very, very sweet.  She was so easy-going that she was often frustrated by other kids, so my easy-going, but, hyper personality was preferable to them and we were happy to hang out together.

We grew up in a very rural area of upper Michigan and spent our days playing make believe in the fields and forests around our house on Lake Michigan.  When we did move to higher population areas, first in Oregon and then in Virginia, we still hung out with each other than our school mates, even though we attended different schools.

I was the maid of honor at Kristi’s wedding to her husband, Jim and we’d visit when I’d come out to Oregon to visit about once a year.  We’d pick up like we’d never been apart, which is common for sisters.  I was happy to see that she and our youngest sister, Kimberly, became very close as they had grown up and they have continued with that relationship.  It’s hard not to like Kristi, she is simply “likeable”.

She turned 43 this year and as she’s always been a bit of a health-nut, appeared to be in the prime of her life.  She had no clear “cancer indicators” when she started having symptoms in January and it wasn’t until February that the doctor’s discovered she had what appeared to be colon cancer.  Within weeks, scans came back showing she not only had colon cancer, it had spread to her liver.

Both my parents have had their challenges with cancer.  My Mother had a battle with uterine cancer and my father, a version of leukemia, but both bounced back rather quickly with treatment.  We respected Kristi’s desire to be private and expected her to make a full recovery.

This hasn’t happened.  She is now, just a couple months later, stage 4 colon cancer.  No treatment has hindered the progress of the cancer’s spread through her body and she is in her final battle.  The ravages of the disease are not just physical, they have brought a horrible financial burden as her family has struggled to save her.

The feeling of helplessness is difficult.  I, as many in my field can understand the need to “fix things” or at least make things better and there is so little I can do.  My sister’s friend’s have put together a contribution site to assist in their financial struggle to help with the cost of her treatments.  It makes the pain a little less knowing I can help ease the financial burden even if I can’t do anything to help save my sister.

If you would like to help Kristi’s and our family, click here.

This post is a brief discussion about the advantages of activating parallelism by altering the session environment instead of using the alternative ways (hints, DDL). The latter ways are the most popular in my experience, but I have noticed that their popularity is actually due, quite frequently, more to imperfect understanding rather than informed decision - and that's a pity since "alter session force parallel query" can really save everyone a lot of tedious work and improve maintainability a great deal.

We will also check that issuing

alter session force parallel query parallel N;

is the same as specifying the hints

/*+ parallel (t,N)  */
/*+ parallel_index (t, t_idx, N) */

for all tables referenced in the query, and for all indexes defined on them (the former is quite obvious, the latter not that much).

Side note: it is worth remembering that hinting the table for parallelism does not cascade automatically to its indexes as well - you must explicitly specify the indexes that you want to be accessed in parallel by using the separate parallel_index hint (maybe specifying "all indexes" by using the two-parameter variant "parallel_index(t,N)"). The same holds for "alter table parallel N" and "alter index parallel N", of course.

the power of "force parallel query"

I've rarely found any reason for avoiding index parallel operations nowadays - usually both the tables and their indexes are stored on disks with the same performance figures (if not the same set of disks altogether), and the cost of the initial segment checkpoint is not generally different. At the opposite, using an index can offer terrific opportunities for speeding up queries, especially when a full table scan can be substituted by a fast full scan on a (perhaps much) smaller index.

Thus, I almost always let the CBO consider index parallelism as well. Three methods can be used: - statement hints (the most popular option) - alter table/index parallel N - "force parallel query".

I rather hate injecting parallel hints everywhere in my statements since it is very risky. It is far too easy to forget to specify a table or index (or simply misspell them), not to mention to forget new potentially good indexes added after the statement had been finalized. Also, you must change the statement as well even if you simply want to change the degree of parallelism, perhaps just because you are moving from an underequipped, humble and cheap test environment to a mighty production server. At the opposite, "force parallel query" is simple and elegant - just a quick command and you're done, and with a single place to touch in order to change the parallel degree.

"alter table/index parallel N" is another weak technique as well in my opinion, mainly for two reasons. The first one is that it is a permanent modification to the database objects, and after the query has finished, it is far too easy to fail to revert the objects back to their original degree setting (because of failure or coding bug). The second one is the risk of two concurrent sessions colliding on the same object that they both want to read, but with different degrees of parallelism. Both the two problems above do not hold only when you always want to run with a fixed degree for all statements; but even in this case, I would consider issuing "force parallel query" (maybe inside a logon trigger) instead of having to set/change the degree for all tables/indexes accessed by the application.

I have noticed that many people are afraid of "force parallel query" because of the word "force", believing that it switches every statement into parallel mode. But this is not the case: as Tanel Poder recently illustrated, the phrase "force parallel query" is misleading; a better one would be something like "consider parallel query", since it is perfectly equivalent to hinting the statement for parallelism as far as I can tell (see below). And hinting itself tells the CBO to consider parallelism in addition to serial execution; the CBO is perfectly free to choose a serial execution plan if it estimates that it will cost less - as demonstrated by Jonathan Lewis years ago. Hence there's no reason to be afraid, for example, that a nice Index Range Scan that selects just one row might turn into a massively inefficient Full Table Scan (or index Fast Full Scan) of a one million row table/index. That is true besides bugs and CBO limitations, obviously; but in these hopefully rare circumstances, one can always use the no_parallel and no_parallel_index to fix the issue.

"force parallel query" and hinting: test case

Let's show that altering the session is equivalent to hinting. I will illustrate the simplest case only - a single-table statement that can be resolved either by a full table scan or an index fast full scan (check script force_parallel_main.sql in the test case), but in the test case zip two other scenarios (a join and a subquery) are tested as well. Note: I have only checked 9.2.0.8 and 11.2.0.3 (but I would be surprised if the test case could not reproduce in 10g as well).

Table "t" has an index t_idx on column x, and hence the statement

select sum(x) from t;

can be calculated by either scanning the table or the index. In serial, the CBO chooses to scan the smaller index (costs are from 11.2.0.3):

select /* serial */ sum(x) from t;
--------------------------------------
|Id|Operation             |Name |Cost|
--------------------------------------
| 0|SELECT STATEMENT      |     | 502|
| 1| SORT AGGREGATE       |     |    |
| 2|  INDEX FAST FULL SCAN|T_IDX| 502|
--------------------------------------
 

If we now activate parallelism for the table, but not for the index, the CBO chooses to scan the table:

select /*+ parallel(t,20) */ sum(x) from t
------------------------------------------
|Id|Operation              |Name    |Cost|
------------------------------------------
| 0|SELECT STATEMENT       |        | 229|
| 1| SORT AGGREGATE        |        |    |
| 2|  PX COORDINATOR       |        |    |
| 3|   PX SEND QC (RANDOM) |:TQ10000|    |
| 4|    SORT AGGREGATE     |        |    |
| 5|     PX BLOCK ITERATOR |        | 229|
| 6|      TABLE ACCESS FULL|T       | 229|
------------------------------------------

since the cost for the parallel table access is now down from the serial cost of 4135 (check the test case logs) to the parallel cost 4135 / (0.9 * 20) = 229, thus less than the cost (502) of the serial index access.

Hinting the index as well makes the CBO apply the same scaling factor (0.9*20) to the index as well, and hence we are back to index access:

select /*+ parallel_index(t, t_idx, 20) parallel(t,20) */ sum(x) from t
---------------------------------------------
|Id|Operation                 |Name    |Cost|
---------------------------------------------
| 0|SELECT STATEMENT          |        |  28|
| 1| SORT AGGREGATE           |        |    |
| 2|  PX COORDINATOR          |        |    |
| 3|   PX SEND QC (RANDOM)    |:TQ10000|    |
| 4|    SORT AGGREGATE        |        |    |
| 5|     PX BLOCK ITERATOR    |        |  28|
| 6|      INDEX FAST FULL SCAN|T_IDX   |  28|
---------------------------------------------

Note that the cost computation is 28 = 502 / (0.9 * 20), less than the previous one (229).

"Forcing" parallel query:

alter session force parallel query parallel 20;

select /* force parallel query  */ sum(x) as from t
---------------------------------------------
|Id|Operation                 |Name    |Cost|
---------------------------------------------
| 0|SELECT STATEMENT          |        |  28|
| 1| SORT AGGREGATE           |        |    |
| 2|  PX COORDINATOR          |        |    |
| 3|   PX SEND QC (RANDOM)    |:TQ10000|    |
| 4|    SORT AGGREGATE        |        |    |
| 5|     PX BLOCK ITERATOR    |        |  28|
| 6|      INDEX FAST FULL SCAN|T_IDX   |  28|
---------------------------------------------

Note that the plan is the same (including costs), as predicted.

Side note: let's verify, just for fun, that the statement can run serially even if the session is "forced" as parallel (note that I have changed the statement since the original always benefits from parallelism):

alter session force parallel query parallel 20;

select /* force parallel query (with no parallel execution) */ sum(x) from t
WHERE X < 0
----------------------------------
|Id|Operation         |Name |Cost|
----------------------------------
| 0|SELECT STATEMENT  |     |   3|
| 1| SORT AGGREGATE   |     |    |
| 2|  INDEX RANGE SCAN|T_IDX|   3|
----------------------------------

Side note 2: activation of parallelism for all referenced objects can be obtained, in 11.2.0.3, using the new statement-level parallel hint (check this note by Randolf Geist for details):

select /*+ parallel(20) */ sum(x) from t
---------------------------------------------------
|Id|Operation                 |Name    |Table|Cost|
---------------------------------------------------
| 0|SELECT STATEMENT          |        |     |  28|
| 1| SORT AGGREGATE           |        |     |    |
| 2|  PX COORDINATOR          |        |     |    |
| 3|   PX SEND QC (RANDOM)    |:TQ10000|     |    |
| 4|    SORT AGGREGATE        |        |     |    |
| 5|     PX BLOCK ITERATOR    |        |     |  28|
| 6|      INDEX FAST FULL SCAN|T_IDX   |T    |  28|
---------------------------------------------------

This greatly simplifies hinting, but of course you must still edit the statement if you need to change the parallel degree.

I often use "ssvnc" to connect my servers to install or do something about X. Anyway, I must to start VNC before by using "vncserver".

[surachart@oralearning ~]$ vncserver New 'oralearning:1 (surachart)' desktop is oralearning:1 Starting applications specified in /home/surachart/.vnc/xstartup Log file is /home/surachart/.vnc/oralearning:1.log [surachart@oralearning ~]$

I ended up find nothing when I connected VNC as below picture. So, I checked "/home/surachart/.vnc/oralearning:1.log" file to investigate the issue.

Fri May 17 15:46:12 2013  vncext:      VNC extension running!  vncext:      Listening for VNC connections on all interface(s), port 5901  vncext:      created VNC server for screen 0 /home/surachart/.vnc/xstartup: line 27: xsetroot: command not found /home/surachart/.vnc/xstartup: line 29: twm: command not found /home/surachart/.vnc/xstartup: line 28: xterm: command not found

As messages in log file. I installed some packages, killed VNC and started VNC again.

[root@oralearning ~]# yum install xorg-x11-twm xorg-x11-server-utils xterm Loaded plugins: security Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package xorg-x11-server-utils.x86_64 0:7.5-13.el6 will be installed --> Processing Dependency: mcpp for package: xorg-x11-server-utils-7.5-13.el6.x86_64 ---> Package xorg-x11-twm.x86_64 1:1.0.3-5.1.el6 will be installed ---> Package xterm.x86_64 0:253-1.el6 will be installed --> Running transaction check ---> Package mcpp.x86_64 0:2.7.2-4.1.el6 will be installed --> Processing Dependency: libmcpp.so.0()(64bit) for package: mcpp-2.7.2-4.1.el6.x86_64 --> Running transaction check ---> Package libmcpp.x86_64 0:2.7.2-4.1.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================  Package                                   Arch                       Version                              Repository                      Size ================================================================================================================================================ Installing:  xorg-x11-server-utils                     x86_64                     7.5-13.el6                           ol6_latest                     158 k  xorg-x11-twm                              x86_64                     1:1.0.3-5.1.el6                      ol6_latest                     100 k  xterm                                     x86_64                     253-1.el6                            ol6_latest                     357 k Installing for dependencies:  libmcpp                                   x86_64                     2.7.2-4.1.el6                        ol6_latest                      68 k  mcpp                                      x86_64                     2.7.2-4.1.el6                        ol6_latest                      23 k Transaction Summary ================================================================================================================================================ Install       5 Package(s) Total download size: 706 k Installed size: 1.6 M Is this ok [y/N]: y Downloading Packages: (1/5): libmcpp-2.7.2-4.1.el6.x86_64.rpm                                                                                  |  68 kB     00:01 (2/5): mcpp-2.7.2-4.1.el6.x86_64.rpm                                                                                     |  23 kB     00:00 (3/5): xorg-x11-server-utils-7.5-13.el6.x86_64.rpm                                                                       | 158 kB     00:00 (4/5): xorg-x11-twm-1.0.3-5.1.el6.x86_64.rpm                                                                             | 100 kB     00:00 (5/5): xterm-253-1.el6.x86_64.rpm                                                                                        | 357 kB     00:01 ------------------------------------------------------------------------------------------------------------------------------------------------ Total                                                                                                            73 kB/s | 706 kB     00:09 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction   Installing : libmcpp-2.7.2-4.1.el6.x86_64                                                                                                 1/5   Installing : mcpp-2.7.2-4.1.el6.x86_64                                                                                                    2/5   Installing : xorg-x11-server-utils-7.5-13.el6.x86_64                                                                                      3/5   Installing : 1:xorg-x11-twm-1.0.3-5.1.el6.x86_64                                                                                          4/5   Installing : xterm-253-1.el6.x86_64                                                                                                       5/5   Verifying  : libmcpp-2.7.2-4.1.el6.x86_64                                                                                                 1/5   Verifying  : xorg-x11-server-utils-7.5-13.el6.x86_64                                                                                      2/5   Verifying  : mcpp-2.7.2-4.1.el6.x86_64                                                                                                    3/5   Verifying  : xterm-253-1.el6.x86_64                                                                                                       4/5   Verifying  : 1:xorg-x11-twm-1.0.3-5.1.el6.x86_64                                                                                          5/5 Installed:   xorg-x11-server-utils.x86_64 0:7.5-13.el6              xorg-x11-twm.x86_64 1:1.0.3-5.1.el6              xterm.x86_64 0:253-1.el6 Dependency Installed:   libmcpp.x86_64 0:2.7.2-4.1.el6                                           mcpp.x86_64 0:2.7.2-4.1.el6 Complete! [root@oralearning ~]# exit logout [surachart@oralearning ~]$  vncserver  -kill  :1 Killing Xvnc process ID 1647 [surachart@oralearning ~]$ [surachart@oralearning ~]$ vnc vncconfig  vncpasswd  vncserver [surachart@oralearning ~]$ vncserver New 'oralearning:1 (surachart)' desktop is oralearning:1 Starting applications specified in /home/surachart/.vnc/xstartup Log file is /home/surachart/.vnc/oralearning:1.log [surachart@oralearning ~]$ less /home/surachart/.vnc/oralearning:1.log

Connected to server again. It was all right for me.

In my last post about large pages in 11.2.0.3 I promised a little more background information on how large pages and NUMA are related.

Background and some history about processor architecture

For quite some time now the CPUs you get from AMD and Intel both are NUMA, or better: cache coherent NUMA CPUs. They all have their own “local” memory directly attached to them, in other words the memory distribution is not uniform across all CPUs. This isn’t really new, Sequent has pioneered this concept on x86 a long time ago but that’s in a different context. You really should read Scaling Oracle 8i by James Morle which has a lot of excellent content related to NUMA in it, with contributions from Kevin Closson. It doesn’t matter that it reads “8i” most of it is as relevant today as it was then.

So what is the big deal about NUMA architecture anyway? To explain NUMA and why it is important to all of us a little more background information is on order.

Some time ago processor designers and architects of industry standard hardware could no longer ignore the fact that a front side bus (FSB) proved to be a bottleneck. There were two reasons for this: it was a) too slow and b) too much data had to go over it. As one direct consequence DRAM memory has been directly attached to the CPUs. AMD has done this first with it’s Opteron processors in its AMD64 micro architecture, followed by Intel’s Nehalem micro architecture. By removing the requirement of data retrieved from DRAM to travel across a slow bus latencies could be removed.

Now imagine that every processor has a number of memory channels to which DDR3 (DDR4 could arrive soon!) SDRAM is attached to. In a dual socket system, each socket is responsible for half the memory of the system. To allow the other socket to access the corresponding other half of memory some kind of interconnect between processors is needed. Intel has opted for the Quick Path Interconnect, AMD (and IBM for p-Series) use Hyper Transport. This is (comparatively) simple when you have few sockets, up to 4 each socket can directly connect to every other without any tricks. For 8 sockets it becomes more difficult. If every socket can directly communicate with its peers the system is said to be glue-less which is beneficial. The last production glue-less system Intel released was based on the Westmere architecture. Sandy Bridge (current until approximately Q3/2013) didn’t have an eight-way glue-less variant, and this is exactly why you get Westmere-EX in the X3-8, and not Sandy Bridge as in the X3-2.

Anyway, your system will have local and remote memory. For most of us, we are not going to notice this at all since there is little point in enabling NUMA on systems with two sockets. Oracle still recommends that you only enable NUMA on 8 way systems, and this is probably the reason the oracle-validated and preinstall RPMs add “numa=off” to the kernel command line in your GRUB boot loader.

Booting with NUMA enabled

The easiest way to boot with NUMA enabled is to get to your ILOM and boot the server. As soon as the GRUB line (“booting … in x seconds”) appears, hit a key. You will be dropped into the GRUB menu. It should highlight the default boot entry (Oracle Linux Server (2.6.39.400…x86-64). Hit the “e” key to edit the directives. You should see something like this now:

root (hd0,0)
kernel /vmlinuz-2.6.39-400.xxx ....
initrd /initramfs-2.6.39-400.xxx

Move the cursor to the line starting with kernel, then hit “e” again. The cursor will move to the end of the line, where you will find the numa=off directive. Hit the backspace key to remove numa=off, then hit return (it will bring you back to the previous 3 directions), then “b” to boot this configuration.

This is useful because it doesn’t involve editing the grub menu file, and if something should break you can simply restart and are back in a known good configuration.

Now when you log in as root you will notice that NUMA is turned on!

Signs of NUMA

My lab server is an AMD 6238 dual socket workstation with 32GB of RAM. To see the effect of NUMA, you can make use of the numactl tool:

[root@ol62 ~]# numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3 4 5
node 0 size: 8190 MB
node 0 free: 1637 MB
node 1 cpus: 6 7 8 9 10 11
node 1 size: 8192 MB
node 1 free: 1732 MB
node 2 cpus: 12 13 14 15 16 17
node 2 size: 8192 MB
node 2 free: 1800 MB
node 3 cpus: 18 19 20 21 22 23
node 3 size: 8176 MB
node 3 free: 1745 MB
node distances:
node   0   1   2   3
  0:  10  16  16  16
  1:  16  10  16  16
  2:  16  16  10  16
  3:  16  16  16  10

You need to know that Opteron reports twice the number of NUMA nodes than there are sockets since their 6100 series. These processors are multi-module chips on the same die. Each of the sockets has 12 cores or better: modules. AMD’s processors are somewhere between HyperThreads and cores, to which extent I can’t tell. The server reports 24 CPUs in any case.

My configuration has allocated 12295 large pages at boot time or roughly 24 GB out of 32GB available. You can see how many pages have been allocated per CPU node in the first half of the output. Luckily the memory has been requested evenly across all NUMA nodes.

The second part of the numactl output gives you the node distances in a matrix. The numbers are provided by the Operating System at boot time in form of the System Locality Table (SLIT) and cannot be changed. They indicate the cost of accessing remote memory. 10 seems to be the base value for this parameter for local access. Higher values indicate more overhead.

NUMA in SYSFS

The SYS pseudo file system is set to replace the venerable /proc file system. The SYSFS exports more information than /proc does, which is apparent when it comes to memory allocation per NUMA node. Per node NUMA statistics are in /sys/devices/system/node*

Two files are out of interest, numastat and meminfo. I won’t go into detail for numastat (yet another post will follow), but meminfo is interesting.

[root@ol62 node0]# cat meminfo
Node 0 MemTotal:        8386572 kB
Node 0 MemFree:         1685988 kB
Node 0 MemUsed:         6700584 kB
Node 0 Active:            10516 kB
Node 0 Inactive:          12704 kB
Node 0 Active(anon):       2656 kB
Node 0 Inactive(anon):        0 kB
Node 0 Active(file):       7860 kB
Node 0 Inactive(file):    12704 kB
Node 0 Unevictable:        1172 kB
Node 0 Mlocked:            1172 kB
Node 0 Dirty:                 0 kB
Node 0 Writeback:             0 kB
Node 0 FilePages:         21276 kB
Node 0 Mapped:             2960 kB
Node 0 AnonPages:          3156 kB
Node 0 Shmem:               116 kB
Node 0 KernelStack:        1384 kB
Node 0 PageTables:          528 kB
Node 0 NFS_Unstable:          0 kB
Node 0 Bounce:                0 kB
Node 0 WritebackTmp:          0 kB
Node 0 Slab:              23788 kB
Node 0 SReclaimable:       5652 kB
Node 0 SUnreclaim:        18136 kB
Node 0 AnonHugePages:         0 kB
Node 0 HugePages_Total:  3074
Node 0 HugePages_Free:   3074
Node 0 HugePages_Surp:      0

This file is similar to /proc/meminfo but only relevant for node0, i.e. the first 6 “cores” on my system. Here you can see the large page allocation on this node.

Why does this matter

When you are consolidating lots of environments to your system with lots of sockets, you should try and stick to memory locality. Keep instances on a socket if possible, today’s servers can take a lot of memory and you shouldn’t have to use remote memory this avoiding latency. I personally would use control groups to ensure my instances stay where I want them to stay. There are other ways to control memory distribution (see some of the SLOB examples) but cgroups are by far the most elegant.

Using NUMA on your system and leaving it to chance how memory is distributed will lead to difficult-to-predict performance. You might even run out of memory on a local node causing unexpected problems. As with everything, understanding and tuning a configuration is the way to go! I will run a few benchmarks next to demonstrate the difference between local and remote memory access. Unfortunately I don’t have a 4-way system available for these tests-normally you wouldn’t really worry about NUMA settings on less than four cores.

Warning

Don’t go and rush your systems to NUMA! Like I said, there is little to be gained in about 80% of all servers out there on dual-socket systems. Four-way servers might be candidates for NUMA, 8 way are candidates. By saying candidates I mean if you understand NUMA and how it can affect your application, and have really load tested it and only if it provided to be predictable, stable performance, then I would think of enabling NUMA for a production workload. There is nothing like thorough testing that can tell you how your application will perform. I guess all I want to say is that turning on NUMA can have negative performance impact as well, or even crash your Oracle instance if the memory on a NUMA node is depleted. Search MOS for NUMA to get more information.

Reference

• http://www.hardwaresecrets.com/article/324
• http://www.hardwaresecrets.com/article/19
• http://ark.intel.com – search for “products by code name”

Demystifying WebLogic and Fusion Middleware Management --- by Glen Hawkins, Senior Director, Product Management

By R A Sanyal on Apr 16, 2013

The purpose of this post is mainly to highlight the performance degradation due to dictionary index fragmentation. It is something that oracle not widely announce but it came from the physical structure of the database.

Oracle databases have the AGE and the age mainly came from the number of DDL operations done on the database. The DDL operations modify the dictionary and introduce fragmentation to the indexes and tables.

I have made the small test case

-- CREATE TABLE
DROP TABLE list_customers
/
CREATE TABLE list_customers
   ( customer_id             NUMBER(6)
   , cust_first_name         VARCHAR2(20)
   , cust_last_name          VARCHAR2(20)
   , nls_territory           VARCHAR2(30)
   , cust_email              VARCHAR2(30))
   PARTITION BY LIST (nls_territory) (
   PARTITION asia VALUES ('CHINA', 'THAILAND'),
   PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
   PARTITION west VALUES ('AMERICA'),
   PARTITION east VALUES ('INDIA'))
/
-- ADD partitions
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4'
/
ALTER TABLE list_customers ADD PARTITION south VALUES ('ANTARCTICA')
/
EXIT
-- DROP partition
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4'
/
ALTER TABLE list_customers DROP PARTITION south
/
EXIT

It is oversimplified method without dependancies to the object and object statistics. But it already create two massive traces. In summary during the INSERT command we insert to tables OBJ$, DEFERRED_STG$ and TABPART$ During delete operation we remove rows from this tables As you all know insert-delete tables have high level of fragmentation on the indexed columns

We run the standard report for indexes on 3 mentioned tables and it shows that estimated number of leaf blocks for some of them dramatically smaller then the actual one.

INDEX	Estimated Size	Actual Size
SYS.TABPART$.I_TABPART_OBJ$	47	279
SYS.OBJ$.I_OBJ3	4	20
SYS.OBJ$.I_OBJ4	422	1475
SYS.OBJ$.I_OBJ1	422	969
SYS.TABPART$.I_TABPART_BOPART$	68	125
SYS.DEFERRED_STG$.I_DEFERRED_STG1	30	53
SYS.OBJ$.I_OBJ5	1269	1728
SYS.OBJ$.I_OBJ2	1269	1726

In case you would try to rebuild this indexes in usual way, you would get the oracle error

ORA-00701: object necessary for warmstarting database cannot be altered

that actually block all attempts to fix the fragmentation.

Index fragmentation primarelly affect index FULL and RANGE scans operation but not UNIQUE index scan. UNIQUE scan would be affected only when INDEX would grow for additional level.

The number in a table does not show something dramatic but it looks like we already have mensurable performance impact on common database operations, like name resolution.

In long term I think every database with high number of structure modifications has to go through process of APPLICATION DATA migration regularly once in 5-15 years.

Thanks to everyone that submitted abstracts for our upcoming E4 conference. Unfortunately, there were more quality submissions than we had room for. Maybe next year we should expand the event to 3 days. :) But in the meantime, we have assembled what I believe is an excellent line up of speakers. I’ll just mention a few highlights here:

Tom Kyte will be doing the keynote. Enough said!

Maria Colgan and Roger MacNicol will be doing a 3 hour combined session on smart scans. Maria will attack the topic from the top down (optimizer) point of view (since she is the product manager for the optimizer) and Roger will be attacking it from the bottom up (since he is the lead developer for the smart scan code). This should be an awesome session and Tanel Poder has already said he was going to line up the night before.

Ferhat Sgonul will be talking about Turkcell’s usage of Exadata. Turkcell is one of the earliest adopters of Exadata and has had great success with it over the last several years, so this should be a very interesting case study.

Karl Arao and Tyler Muth will do a joint presentation on visualization techniques for performance data from Exadata environments. The plan is for them to compare and contrast their approaches using the same data set. Tyler usually uses R and Karl likes Tableu – may the best violin chart win.

Tyler Muth will also be doing a deep dive presentation on bloom filters and how they can be offloaded with smart scans. This is a topic about which there is little information, so it should be quite interesting.

Frits Hoogland will be doing a deep dive on how Oracle does multi-block i/o. This is of special interest with regard to Exadata because the direct path mechanism for doing multi-block i/o is a requirement for enabling smarts scans. So understanding how it works is one of the keys to getting the most out the platform.

Sue Lee (product manager for resource manager) will be doing a session on how to deal with mixed workloads. I’m really interested in this session as IORM and DBRM are critical for managing Exadata, particularly when it is used as a consolidation platform.

There are many other well known speakers including Martin Bach, Andy Colvin, Gwen Shapira, Mark Rittman, Tim Fox and Tanel Poder.

Here’s a link to see the complete line up of E4 speakers.

While we’re on the subject, I should mention that there will be several talks on hadoop related topics and the increasingly expanding role it is playing in our industry. The idea of pushing the work to the storage is not unique to Exadata. It is also the main driver behind hadoop. So I’m extremely pleased to announce that Doug Cutting will be speaking at E4 as well.

So that’s all for the marketing related stuff on E4. I hope you can join us in Dallas.

 Weds May 29th, 2013  Central Europe: 17:00 UK: 16:00 East USA: 11.00 Central USA: 10.00 Pacific USA: 08.00

Register now

End-to-end metrics bridge the gap between the developer and the DBA, the Developer and Oracle, and the DBA and the application.

Most applications today are n-tiers using a connection pool on the application tier, which makes them very hard to trace. With a little bit of collaboration between developers and DBAs, tracing can be turned on very easily for your specific application module, a specific user, IP-address, customer, or any other metric of your choice.

End-to-end metrics can also be helpful in your development process, and in the day-to-day monitoring of your application.

A live Q&A session with Lasse Jenssen will follow the presentation.

 Weds May 22nd, 2013  Central Europe: 17:00 UK: 16:00 East USA: 11.00 Central USA: 10.00 Pacific USA: 08.00

Register now

While the primary focus of both Oracle and SQL Server is on relational database management, there are a number of tools that come with each software package that are focused around monitoring and investigating the performance of the servers. These monitoring tools vary wildly between the two platforms, yet still have a lot in common because of shared fundamentals.

In their latest live ‘Oracle vs. SQL Server’ discussion, Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will look at built-in performance monitoring mechanisms and tools.

As before, this will be a live discussion with no supporting slides, and will conclude with a Q+A session with Jonathan and Grant. Be prepared for a lively exchange that will not only entertain, but will teach you key concepts on Oracle and SQL Server.

The below content is based on a David Peake‘s presentation at OOW 2012 and APEX World 2013. As the Oracle Database 12c is not out yet at the time of writing, note that things might be different in the final product.

The Oracle Database 12c is a major release in Oracle history, as it contains a complete redesign of the underlying architecture. Two things will come back a lot when you read about Oracle DB 12c; the Container Database (CDB) and the Pluggable Database (PDB).

The below image shows how it works; you have a container database which the memory and processes are attached at, and then you can have multiple other databases that you can just plugin.

You find more information about this architecture here.

So what does Oracle DB 12c mean for APEX? Find out on my blog post.

[root@rhel59dra ~]# /sbin/scsi_id -g -u -s /block/sdc

[root@rhel59dra ~]# ls -l /dev/ASM*

Oracle 12c is around the corner and due that I have received many questions from fellow DBAs about what was new about Backup and Recovery at 11g I have decided to write a small white paper about it.

Hope you will enjoy reading it as much I enjoyed writing it.

What_is_new_at_11g

Cheers,

Francisco Munoz Alvarez

I delivered my two sessions at the Great Lakes Oracle Users Group today. It was a great experience! I had the honor to fill to capacity both sessions, and both were scheduled in the largest room out of 5 concurrent tracks! I estimate that in each session I had more than 50% of the total audience. It is very rewarding been able to share some knowledge with such a great crowd. I was asked if I would do a half -day pre-conference workshop next year. I would certainly do if I can.

Anyways, with the conference behind, I am sharing here both presentations. For the one in Adaptive Cursor Sharing, if you want to perform the labs we did today, please post your request here and I will find a way to share those demo scripts.

1. SQL Tuning made much easier with SQLTXPLAIN (SQLT)
2. Using Adaptive Cursor Sharing (ACS) to produce multiple Optimal Plans

The other day I was installing Oracle Enterprise Manager 12c Cloud Control and ran into a few problems.  I had to scrub the install and start from scratch due to a naming issue in the /etc/hosts file (story for another time).  In order to start over, I had to remove the SYSMAN and associate schemas from the repository database.  When I went to drop the schema, the database said the schema was still connected and active.  Besides being frustrated at this point, I had to identify and kill the sessions that were still active for the SYSMAN schema.  

Any time when needing to find active sessions within the database, we can use the V$SESSION view.  The only thing we need to grab from this view is the SID and SERIAL#.  The SQL I used to get this is below:

select sid, serial#, username from v$session where username like ‘SYSMAN%’ and status = ‘ACTIVE’;

This will pull all the sessions that are tied SYSMAN.  Being that this was an Oracle Enterprise Manager 12c repository, there are a few different SYSMAN schemas we need to account for.

Once we have all the SID and SERIAL#; we need to disconnect the session from the database.  Yes, we can do an ‘alter system kill session‘, but this would abandon the transaction for the session, if any.  To cleanly disconnect the session, we can use the ‘alter system disconnect session‘ command.  This command has been around since 8i, and does a good job of disconnecting a session cleanly.  The syntax for the command is listed below:

alter system disconnect session ‘sid,serial#’ immediate;

or

alter system disconnect session ‘sid,serial#’ post_transaction;

Now that the sessions that were hanging me up have been disconnected, I can clean up the OEM repository and prepare to restart the installer.

 

Filed under: Database, OEM

Last week we held the UKOUG Database SIG in Leeds as I described in a recent post. It was a well attended event in a  central location with excellent refreshments available. – Thank you to UKOUG’s  Anna Crellin for organising the day so well.

The talks were all very good and I think the event was enjoyed by all. Neil Johnson wrote a blog entry about his experience of the day and of presenting for the first time. He has now got the bit between his teeth and wants to present a 2 hour masterclass at the December conference in Manchester – (perhaps I am exaggerating slightly)

I ran the ‘BitsandPieces’ session where audience participation was the key idea and this was achieved. The theme was small pieces of Oracle functionality that are not well known or used.

We discussed :-

The dbms_workload_repository.add_colored_sql procedure which allows you to capture specific pices of sql that individually would not appear as a top resource in AWR data. Using the package to add a sql_id and the data does then appear and performance can be fully explored. A good introductory  blog entry is available from Dion Cho at http://dioncho.wordpress.com/tag/colored-sql/

• The benefits of using restore points and  flashback database  were covered. Examples were given of flashing back through a 10g to 11g upgrade (if you might want to), using a restore point before making a major table or code change that might need to be rolled back (better than rolling forward from a previous backup) and using the same restore point repeatedly as part of performance testing. The point was made that anything that might include an OS command (such as dropping or resizing a datafile probably would not be recovered through so be careful with the command. My advice is that I consider it as the belt that goes with the braces of a full backup.
• Invisible indexes – how easy they were to use and how they allowed the data_dictionary to be aware of the index but the optimizer ignores it. There was also a initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES  that allows the index to become visible at both a session and system level to allow full testing. Beware that if you rebuild an index it then becomes visible again.
• One tip that came out was that in pre-packaged environments such as EBS when an index is not wanted, rather than drop it, mark it as invisible. Then when any upgrades or patches are applied the  index still exists in the dictionary and is not recreated as it might be if it had been dropped.
• Another idea was the use of the opatch auto command. This came out in 11GR2 but not until PSU2. It manages a full GI and RDBMS patchset application and can stop all dependant databases, stop crs resources, stop crs, patch and restart everything. It can save a serious amount of time and is well worth investigating.

I did pick up another few interesting thoughts which I want to do a bit of research on and I will blog about those shortly.

So overall it  as a good day with a lot of community interaction, networking and a good laugh afterwards when about 15 of us went for a few beers.

Thanks to everyone who attended my webinar on using hints for Oracle testing and performance tuning. As usual, it was a great event and I appreciate the comments and questions. Downloads: Presentation PDF Related scripts Webinar recording (coming soon) I'll be back in the saddle again in July so keep your eyes open for the announcement of that event. Thanks again and hope to see you then!

Oracle supports two kind of bind variable placeholder syntax in Java PreparedStatments. A bind variable placeholder could be denoted with a colon and a number ":1" or with a question mark "?". However there are situation where developers may use a syntax such as question mark followed by a number "?1". Though this syntax is not supported in Oracle, when using JDBC drivers 10.2 and 11.1 the Java code would execute without an error and returns results as expected. But with 11.2 JDBC driver this will give the error ORA-00933: SQL command not properly ended and java code would not execute as before. So as part of database upgrade if JDBC driver is also upgraded and if java code has the syntax similar to "?1" etc then code that previously worked would not work anymore. The problem here lies in using the unsupported bind variable placeholders. According to Oracle the fact that it works with previous version of JDBC driver is a merer accident and not the expected behavior. If it's not possible to modify the code the easiest option to remedy to the situation is to downgrade the JDBC driver to 11.1. As the issue comes as a result of the JDBC driver not because of the database Java code given at the end of the post could be used to test the unsupported syntax against various databases and JDBC drivers. Following table list summary of findings from running the test code with 10.2/11.1/11.2 JDBC drivers and databases.

JDBC Driver Version	Database Version	"?1" Works
10.2.0.5	10.2.0.5	YES
10.2.0.5	11.1.0.7	YES
10.2.0.5	11.2.0.3	YES
11.1.0.7	10.2.0.5	YES
11.1.0.7	11.1.0.7	YES
11.1.0.7	11.2.0.3	YES
11.2.0.3	10.2.0.5	NO
11.2.0.3	11.1.0.7	NO
11.2.0.3	11.2.0.3	NO

Create and populate table used in the test code

SQL> create table x (a varchar2(10), b number);

Table created.

SQL> begin
  2  for i in 1 .. 20
  3  loop
  4  insert into x values('abc'||i,i);
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

Test code

   
        OracleDataSource ds = new OracleDataSource();
        ds.setUser("asanga");
        ds.setPassword("asa");
        ds.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");

        Connection con = ds.getConnection();
        DatabaseMetaData meta = con.getMetaData();

        System.out.println("Driver Name " + meta.getDriverName());
        System.out.println("Driver Version " + meta.getDriverVersion());
        System.out.println("Driver Major Version " + meta.getDriverMajorVersion());
        System.out.println("Driver Minor Version " + meta.getDriverMinorVersion());
        System.out.println("Database Major Version " + meta.getDatabaseMajorVersion());
        System.out.println("Database Minor Version " + meta.getDatabaseMinorVersion());
        System.out.println("Database Product Name " + meta.getDatabaseProductName());
        System.out.println("Database Product Version " + meta.getDatabaseProductVersion());

        String SQL = "select * from x where b = ?1"; // works on 10gR2, 11gR1 jdbc driver but not on 11gR2 jdbc driver
//        String SQL = "select * from x where b = ?";  // works on all drivers

        PreparedStatement pr = con.prepareStatement(SQL);
        pr.setInt(1, 10);
        ResultSet rs = pr.executeQuery();

        while(rs.next()){

            System.out.println(rs.getString("A")+" "+rs.getInt("B"));
        }
              
        rs.close();
        pr.close();        con.close();

Useful metalink notes ORA-00933 When Using Bind Variables in JDBC 11.2 [ID 1304235.1]