Search

OakieTags

Who's online

There are currently 0 users and 33 guests online.

Recent comments

Oakies Blog Aggregator

Data Guard and Oracle Restart, aka relink always !!!

During my early years with PC I love to play "Leisure Suit Larry" (especially part 1 to 3 which has running on CGA or Hercules graphics card). Author of this games, Al Lowe had a good sense of humor and every time you failed your mission or stuck in a place - there was an pop up on the screen "Al says: save early, save often". After today fight with Oracle I should remember a next saying - "Relink after, relink always".

Let me explain what what happen today.

There used to be old Oracle bug in 11.2g which I described here. Basically Oracle Restart was not aware of role changes and could open a database in wrong mode after role switch. I created a workaround for it but I was really waiting for a patch. During a UKOUG 2014 I was speaking with Oracle Data Guard Product Manager - Larry Carpenter - and I learned that patch is ready. I asked @UweHesse for details and I got my numbers back - patch 15986647 and it is included in latest PSU 11.2.0.4.4.

After I got this information I was happy - I just applied this PSU last week, so let's test it.

The first run looked OK - after switchover Oracle Restart displayed proper role and start-up option on new primary
agent/ohasd/oraagent_grid/oraagent_grid.log
2014-12-22 09:02:36.511: [ AGFW][2057]{0:0:44372} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:02:36.513: [ AGFW][9207]{0:0:44372} Command: res_attr_modified for resource: ora.apppre11.db 1 1 completed with status: SUCCESS
2014-12-22 09:02:36.516: [ AGFW][2057]{0:0:44372} Attribute: ROLE for resource ora.apppre11.db modified to: PRIMARY
2014-12-22 09:02:36.516: [ AGFW][2057]{0:0:44372} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre11.db modified to: open
2014-12-22 09:02:36.516: [ AGFW][2057]{0:0:44372} config version updated to : 69 for ora.apppre11.db 1 1
2014-12-22 09:02:36.516: [ AGFW][2057]{0:0:44372} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.663: [ AGFW][2057]{0:0:44374} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.apppre_adf.svc 1 1] ID 4355:617914

but only proper role on the new standby.

agent/ohasd/oraagent_grid/oraagent_grid.log

2014-12-22 09:03:10.420: [ AGFW][2057]{0:0:11369} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787
2014-12-22 09:03:10.423: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:03:10.424: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:03:10.424: [ AGFW][2412]{0:0:11369} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 09:03:10.424: [ AGFW][2057]{0:0:11369} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 09:03:10.424: [ AGFW][2057]{0:0:11369} config version updated to : 142 for ora.apppre21.db 1 1
2014-12-22 09:03:10.424: [ AGFW][2057]{0:0:11369} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787

Start up option was still open instead of mount. Hmmm, I was sure that both nodes are same but again I reconfigure Data Guard Broker and Oracle Restart - no change - issue was resolved on the one server only.  Then I decided to double check if I have this PSU installed, and I found out that everything was in order in terms of PSU.

-bash-3.2$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-12-22_15-08-59PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-12-22_15-08-59PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (3) :

Patch 18235390 : applied on Mon Dec 22 14:54:02 GMT 2014
Unique Patch ID: 18349043
Created on 4 Dec 2014, 18:50:09 hrs PST8PDT
Bugs fixed:
18235390

Patch 19121549 : applied on Mon Nov 17 14:06:54 GMT 2014
Unique Patch ID: 18060349
Patch description: "OCW Patch Set Update : 11.2.0.4.4 (19121549)"
Created on 7 Oct 2014, 03:38:04 hrs PST8PDT
Bugs fixed:
18328800, 18691572, 14525998, 18187697, 18348155, 17516024, 17387214
17750548, 17617807, 17551223, 14671408, 14207615, 18272135, 18180541
17292250, 17378618, 17500165, 18875012, 18464784, 17065496, 18848125
13991403, 17955615, 14693336, 17273020, 17238586, 17089344, 17405605
17531342, 17155238, 17159489, 18053580, 16543190, 17039197, 16317771
17947785, 10052729, 16281493, 18346135, 17481314, 18199185, 18399991
18024089, 18428146, 18352845, 18352846, 17391726, 18414137, 17001914
17927970, 14378120, 16346413, 17305100, 15832129, 15986647, 16901346
18068871, 17985714, 18536826, 16206997, 18752378, 16876500, 16429265
18343490, 18336452, 16613232, 17273003, 19276791, 12928658, 18226143
17172091, 18229842, 18053631, 16867761, 18231837, 15869775, 17483479
18729166, 17405302, 15920201, 18709496

Patch 19121551 : applied on Mon Nov 17 14:05:34 GMT 2014
Unique Patch ID: 17971200
Patch description: "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Created on 9 Oct 2014, 13:06:55 hrs PST8PDT
Sub-patch 18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Bugs fixed:
16929165, 16220077, 17235750, 17468141, 18191164, 17006183, 16315398
17501491, 13955826, 17288409, 12905058, 17446237, 17375354, 16992075
16855292, 17050888, 17546973, 18554871, 17726838, 17614134, 9756271
18673325, 17227277, 17232014, 13853126, 17545847, 17390160, 18096714
12747740, 17016369, 17786518, 19271443, 10136473, 16785708, 17311728
18018515, 16268425, 17610798, 18280813, 17082359, 17783588, 14245531
18094246, 13866822, 17477958, 16943711, 18673304, 18031668, 19463897
19463893, 19211724, 13498382, 16450169, 17397545, 17786278, 17767676
14458214, 19289642, 17622427, 17824637, 17716305, 16399083, 18744139
14852021, 19727057, 17242746, 17174582, 18277454, 13645875, 14084247
17551709, 17393683, 17614227, 17705023, 17883081, 16042673, 18996843
16285691, 17393915, 16228604, 17655634, 17596908, 17600719, 16180763
17754782, 17323222, 18264060, 17325413, 17343514, 17865671, 16613964
17811447, 16069901, 17390431, 16494615, 16043574, 17006570, 17027426
14602788, 17080436, 18673342, 17186905, 17011832, 17394950, 13944971
17752121, 17284817, 17811456, 17238511, 17239687, 17042658, 14764829
17602269, 17672719, 17891946, 17205719, 18262334, 16721594, 17071721
14565184, 17265217, 17389192, 17761775, 16360112, 17982555, 17842825
19121551, 16837842, 18139690, 17313525, 18203837, 18203838, 18203835
18436307, 17546761, 17721717, 17344412, 17787259, 16472716, 17346671
17588480, 13364795, 14657740, 11733603, 17811438, 19466309, 17040527
17037130, 17088068, 17612828, 18180390, 17449815, 19458377, 19554106
18973907, 18230522, 19544839, 17811429, 16863422, 17237521, 17951233
17752995, 16392068, 17437634, 14338435, 13609098, 17332800, 18199537
17465741, 17441661, 18522509, 18061914, 14133975, 17570240, 16692232
18247991, 18328509, 16956380, 17587063, 19049453, 16618694, 17586955
18009564, 14285317, 16542886, 17341326, 17571306, 17036973, 18641419
16524926, 17297939, 17478145, 17040764, 17299889, 16912439, 15861775
14054676, 13951456, 17267114, 16850630, 17648596, 14010183, 17296856
17478514, 16875449, 18681862, 14829250, 17385178, 17443671, 16731148
16314254, 17165204, 15979965, 19584068, 17381384, 17892268, 16198143



--------------------------------------------------------------------------------

OPatch succeeded.
 

But on one server I was testing patch for OEM (18235390) to allow it to monitor tablespaces without ORA-1000 errors.Is it possible that my OEM patch add anything special ?

I was thinking about that for a while but I was not sure. So I did simple test - installed this patch on standby server and ... yes - Oracle Restart issue has been fixed. 
 
Strange, very strange - what this patch did that Data Guard was fixed ? Well it did one important thing - it relinked oracle binaries again.
 
Lucky enough I got one more pair with latest PSU ready for test.I run test with PSU installed and bug was still there. OK - so let's relink - and it was it
 
2014-12-22 14:33:40.942: [    AGFW][2057]{0:0:12827} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223
2014-12-22 14:33:40.944: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify {
2014-12-22 14:33:40.945: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify }
2014-12-22 14:33:40.945: [ AGFW][9494]{0:0:12827} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 14:33:40.946: [ AGFW][2057]{0:0:12827} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 14:33:40.946: [ AGFW][2057]{0:0:12827} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre21.db modified to: mount
2014-12-22 14:33:40.946: [ AGFW][2057]{0:0:12827} config version updated to : 153 for ora.apppre21.db 1 1
2014-12-22 14:33:40.946: [ AGFW][2057]{0:0:12827} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223

 

This is quite strange and a little bit scary. I have installed all PSU using opatch auto from Grid Infrastructure Home and there was no errors but relinking Oracle Home actually fix a problem which was included in the patch. 
 
So remember "Relink after (patch), relink always"
 
regards,
Marcin


Learning

I received an email a few weeks ago asking me if I would look at a series of three posts on adaptive dynamic sampling in 12c – (part 1, part 2, part 3). I took a note of the topic and URLs, and read through them fairly rapidly, and they seemed to be perfectly reasonable articles describing the authors thoughts, tests, and observations. Inevitably, though, several questions ran through my mind as I read – typically along the lines of “what would happen if …”, “did you restart the instance before …”, “did you flush the shared pool between …”. It’s very hard to create, run, and report a set of tests that allow you to make solid inferences about how Oracle and the optimizer behave – and whatever you do someone else will find a way of asking some questions that push the envelope a little further.

I got a follow-up email a week or so later, asking if I’d had time to look at the article because the author wanted to present the topic at a local (Azerbaijan) user group event – and the follow-up email prompted me to write this blog note.

Given that it’s taken about three weeks for me to get around to writing this note you might appreciate that I don’t have a lot of time to spare on a topic that I’m not actively pursuing for a good reason. I accumulate a lot of information from around the internet, from books, and from presentations, and I’ll invariably attach lots of questions and conjectures to that information but I won’t necessarily be able to have any confidence in its utility or correctness. When I have to know an answer I may go back to a source I’ve noted and use it as a basis for doing some further goal-oriented investigations – but until I need to (or unless I’m particularly curious) I don’t have the time to do arbitrary research. This means, of course, that I don’t have time to get into a dialogue with people about the work they have done and the information they have presented.

On the other hand, of course, I thoroughly approve of anyone who takes the time to do the experiment and write up the results. And I heartily approve of anyone who is prepared to stand up in front of a user group and share their observations and want to encourage people to do this; so this is the reply I sent which, I hope, is suitable positive:

There are a number of questions that I would probably want answered if I were to examine the posts in detail, and I would want to repeat the tests that you have done to check that I got the same results and to see if there were other observations I could make that might lead me to different conclusions. This is something that takes far too much time to do properly, and I have no inside information that would allow me to say very quickly whether your comments are right, wrong, or simply incomplete.

The important point, though, is that you have set up some tests, documented the results, and offered some conclusions. That is sufficient for your presentation to the user group.
  • This is what I did
  • This is what I observed
  • This is what I concluded
 At worst someone will ask some questions like: “Did you check …”, or “What happens if …”
You may need to answer “I don’t know”, or “I didn’t think of doing that”, or “That’s a good idea, I’ll try it”; but whatever happens everyone learns something, and everyone has the opportunity to learn more based on what you have given them.
In passing, at the very first presentation I did for the UK Oracle User Group about 25 years ago I got about half way through some comments on an odd performance pattern I had observed when modifying the SQL*Plus arraysize when someone stuck up their hand and asked: “Did you check the tcp packet size?” The only response I could make, after a few seconds pause to think, was: “I should have thought of that. No.”

New Version Of XPLAN_ASH Utility

A new version 4.2 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

There were no too significant changes in this release, mainly some new sections related to I/O figures were added.

One thing to note is that some of the sections in recent releases may require a linesize larger than 700, so the script's settings have been changed to 800. If you use corresponding settings for CMD.EXE under Windows for example you might have to adjust accordingly to prevent ugly line wrapping.

Here are the notes from the change log:

- New sections "Concurrent activity I/O Summary based on ASH" and "Concurrent activity I/O Summary per Instance based on ASH" to see the I/O activity summary for concurrent activity

- Many averages and medians now also have accompanying minimum and maximum values shown. This isn't as good as having histograms but gives a better idea of the range of values, and how potentially outliers influence the average and deserve further investigations

- Bug fixed: When using MONITOR as source for searching for the most recent SQL_ID executed by a given SID due to some filtering on date no SQL_ID was found. This is now fixed

- Bug fixed: In RAC GV$ASH_INFO should be used to determine available samples

- The "Parallel Execution Skew ASH" indicator is now weighted - so far any activity level per plan line and sample below the actual DOP counted as one, and the same if the activity level was above
The sum of the "ones" was then set relative to the total number of samples the plan line was active to determine the "skewness" indicator

Now the actual difference between the activity level and the actual DOP is calculated and compared to the number of total samples active times the actual DOP
This should give a better picture of the actual impact the skew has on the overall execution

- Most queries now use a NO_STATEMENT_QUEUING hint for environments where AUTO DOP is enabled and the XPLAN_ASH queries could get queued otherwise

- The physical I/O bytes on execution plan line level taken from "Real-Time SQL Monitoring" has now the more appropriate heading "ReadB" and "WriteB", I never liked the former misleading "Reads"/"Writes" heading

The BBC has “Stolen” my Interesting Shortest Day Facts

Today, the 21st December 2014, is the “shortest day” in the Western Hemisphere this year, the day in which the period of daylight is shortest (it’s the 22nd if the last Leap Year is more than 2 years ago).

I’ve blogged before about how the evenings start drawing out BEFORE the shortest day and, despite it not being an Oracle technical post and was also one of my first posts {when almost no one came by}, it gets a modest but interesting number of hits. If you look at the below graphs (sorry, it’s not as clear as it could be unless you click on the image), you will see there is a burst of hits at the end of the year and a smaller rise in interest at the middle of the year.

Evenings_post_stats

These hits are all via search engines, mostly on the phrase “Evenings drawing out”. Obviously there is a correlation with people both in the Northern (for the December hits) and Southern (for the June hits) hemispheres getting sick of the longer periods of dark and googling about when it will start to change. And finding this strangely relevant post on what is otherwise a nerdy IT site.

{Isn’t this an example of what all the IT blather about Big Data and the cloud is about? Finding patterns in search engine data etc? My blog is not exactly Big Data though :-) }

Well, The BBC is probably going to steal my thunder, this year at least, as they have done an article on the phenomena, though concentrating more on the mornings continuing to get darker after the “shortest day”. It’s not a very good article in one respect, though, as it has the phrase “perceived a curious development” as though this mismatch between the shortest day and mornings/evenings getting later is a recent change. I’m pretty sure that the tilt of our planet and it’s orbit around the sun has not changed enough in my lifetime to alter this situation! In fact, I checked – evenings started drawing out on around the 13th December 50 years ago, exactly the same as this year and exactly the same as is expected in 50 years too. IT does describe well how it is out shifting clock (due to the longer day length around now).

Another little oddity about shortest day and our planet is over when you think the earth is furthest from the sun. Most of us in the Northern hemisphere assume it is on the shortest day, because it is colder and darker. But it is not. we are actually nearest to the sun, at “perihelion”, on the 3rd Jan. So not even “shortest day” but just after :-).

Exadata Patching Introduction

These I consider the most important points about Exadata Patching:

Where is the most recent information?

MOS Note 888828.1 is your first read whenever you think about Exadata Patching

What is to patch with which utility?

Exadata Patching

Expect quarterly bundle patches for the storage servers and the compute nodes. The other components (Infiniband switches, Cisco Ethernet Switch, PDUs) are less frequently patched and not on the picture therefore.

The storage servers have their software image (which includes Firmware, OS and Exadata Software)  exchanged completely with the new one using patchmgr. The compute nodes get OS (and Firmware) updates with dbnodeupdate.sh, a tool that accesses an Exadata yum repository. Bundle patches for the Grid Infrastructure and for the Database Software are being applied with opatch.

Rolling or non-rolling?

This the sensitive part! Technically, you can always apply the patches for the storage servers and the patches for compute node OS and Grid Infrastructure rolling, taking down only one server at a time. The RAC databases running on the Database Machine will be available during the patching. Should you do that?

Let’s focus on the storage servers first: Rolling patches are recommended only if you have ASM diskgroups with high redundancy or if you have a standby site to failover to in case. In other words: If you have a quarter rack without a standby site, don’t use rolling patches! That is because the DBFS_DG diskgroup that contains the voting disks cannot have high redundancy in a quarter rack with just three storage servers.

Okay, so you have a half rack or bigger. Expect one storage server patch to take about two hours. That summarizes to 14 hours (for seven storage servers) patching time with the rolling method. Make sure that management is aware about that before they decide about the strategy.

Now to the compute nodes: If the patch is RAC rolling applicable, you can do that regardless of the ASM diskgroup redundancy. If a compute node gets damaged during the rolling upgrade, no data loss will happen. On a quarter rack without a standby site, you put availability at risk because only two compute nodes are there and one could fail while the other is just down.

Why you will want to have a Data Guard Standby Site

Apart from the obvious reason for Data Guard – Disaster Recovery – there are several benefits associated to the patching strategy:

You can afford to do rolling patches with ASM diskgroups using normal redundancy and with RAC clusters that have only two nodes.

You can apply the patches on the standby site first and test it there – using the snapshot standby database functionality (and using Database Replay if you licensed Real Application Testing)

A patch set can be applied on the standby first and the downtime for end users can be reduced to the time it takes to do a switchover

A release upgrade can be done with a (Transient) Logical Standby, reducing again the downtime to the time it takes to do a switchover

I suppose this will be my last posting in 2014, so Happy Holidays and a Happy New Year to all of you :-)

Tagged: exadata

Reading Oracle memory dumps

Every DBA working with the Oracle database must have seen memory dumps in tracefiles. It is present in ORA-600 (internal error) ORA-7445 (operating system error), system state dumps, process state dumps and a lot of other dumps.

This is how it looks likes:

Dump of memory from 0x00007F06BF9A9E00 to 0x00007F06BF9ADE00
7F06BF9A9E00 0000C215 0000001F 00000CC1 0401FFFF  [................]
7F06BF9A9E10 000032F3 00010003 00000002 442B0000  [.2............+D]
7F06BF9A9E20 2F415441 31323156 4F2F3230 4E494C4E  [ATA/V12102/ONLIN]
7F06BF9A9E30 474F4C45 6F72672F 315F7075 3735322E  [ELOG/group_1.257]
7F06BF9A9E40 3336382E 36313435 00003338 00000000  [.863541683......]
7F06BF9A9E50 00000000 00000000 00000000 00000000  [................]

The first column is the memory location in hexadecimal.
The second to fifth columns represent the actual memory values in hexadecimal.
The sixth column shows an ASCII representation of the memory contents. If a position does not represent an ASCII character, a dot (“.”) is printed.

Actually, the values in the second to fifth column are grouped in four columns. This is how the values in a column look like:
{hex val}{hex val}{hex val}{hex val}, for example: 00010203 means: 0, 1, 2, 3.

In the ASCII representation (sixth column) the spaces after every four values are not put in.

However, look at the following line:

7F06BF9A9E10 000032F3 00010003 00000002 442B0000  [.2............+D]

And focus on the last four characters:
“..+D” (two non-printables, plus, D)
Now look at the corresponding memory contents from the dump:
“442B0000″ This is: “44 2B 00 00″, which should correspond to “. . + D”.
There is something the matter here: the plus and the D seem to be represented by “00”. That’s not correct.

Let’s see what “442B0000″ actually represents in ASCI:

$ echo -e "\x44\x2B\x00\x00"
D+

Ah! That looks backwards! Let’s take a full line and see what that gives:
(This is the line with memory address 0x7F06BF9A9E20)

$ echo -e "\x2F\x41\x54\x41 \x31\x32\x31\x56 \x4F\x2F\x32\x30 \x4E\x49\x4C\x4E"
/ATA 121V O/20 NILN

So if you want to look at the actual memory contents, you need to start with the column on the left side, read the values from right to left, then go the next column, etc.

Endianness
Actual, I asked my friend Philippe Fierens for a trace file from a SPARC (big endian) platform, to see if the endianness of the platform was causing this. I test my stuff on Linux, which is little endian.

Here’s a little snippet:

Dump of memory from 0xFFFFFFFF7D977E00 to 0xFFFFFFFF7D97BE00
FFFFFFFF7D977E00 15C20000 00000001 00000000 00000104  [................]
FFFFFFFF7D977E10 F4250000 00000000 0B200400 E2EB8A3D  [.%....... .....=]
FFFFFFFF7D977E20 44475445 53540000 32F6D98B 00000590  [DGTEST..2.......]
FFFFFFFF7D977E30 00004000 00000001 00000000 00000000  [..@.............]
FFFFFFFF7D977E40 00000000 00000000 00000000 00000000  [................]

Let’s test the line from address 0xFFFFFFFF7D977E20:

[oracle@bigmachine [v12102] trace]$ echo -e "\x44\x47\x54\x45 \x53\x54\x00\x00 \x32\xF6\xD9\x8B \x00\x00\x05\x90"
DGTE ST 2� �

So, the endianness determines how the raw memory contents should be read.

Tagged: dump, hex, linux, memory, oracle, read memory, understanding how it works

Friday Philosophy – my Funniest “PC Support” Call in Years

Those of us who work in IT often find ourselves being called by friends and relatives to help with issues they have with their home computer. No matter what branch of IT we work in, it’s IT {they figure} so of course we can fix their PC problems. It’s like any scientist can probably explain what the Higgs Boson really is and reverse the polarity of the neutron flow.

Mothers and other elderly relatives are probably the most baffling when it comes to such requests, but this week I had a wonderful call-out from one of my neighbours.

The neighour in question is a slightly dotty, jolly posh but well meaning Lady (we live in the cheap house around here, she lives at the other end of things).

“Oh Martin, it’s AWE-full! My computer is full of p3nises and other horrible things!!! Please help me get rid of all the p3nises!!!”

Ahhh… I wonder what sites she’s been looking at…must be a virus or something. “It’s OK Tabitha, I’ll pop down now and have a look for you” I say. “Oh, that is so decent of you, your such a helpful young man!” {Young?!?}

So I tell my wife I’ve off over to Tabitha’s to help her with p3nises – and leave her sniggering on the sofa. When I arrive Tabitha opens the door and exclaims, quite loudly “oh thank you for coming to help me with these p3nises and things!”. Thanks, Tabitha, I’m sure John and Elaine could hear that.

It takes ages to get to the bottom of what her issue is as she is talking non-stop, wandering off track and saying how they must think she’s a man as they want her to buy certain drugs and she keeps describing the p3nises and naked people “doing very rude things” she has seen – and each time I try and do anything on her tablet she’ll suddenly lean over and tap on any icons or links. It’s like a Pavlovian response. This could be the root of all her IT woes…

It turns out her virus scanner is running (I set her up with that), up to date and there seems to be no infection. The problem is simply the spam email she (and we all) get. She has her mail application set to preview and also download any images in the email. And she checks each spam email before deleting each one. I suspect sometimes she checks in detail…

We then have a long and sometimes surreal conversation about why they think she should want to buy viagra, enhancement creams or meet Tanya who is local, fun, vivacious and wants some company. {“Well she sounds like she just wants some friends to me” – “DON’T CLICK ON THAT LINK!!!!”}. At least she knows to never respond to emails about bills, banks, missed messages or vast sums of money – we had that conversation when I helped her sort out the Laptop a year or two back. I did not bring up sex as, well, you don’t talk to nice ladies like Tabitha about sex… Tabitha did ask if she should respond to ask them to just stop sending her these pictures but I assured her it would only get worse if we did. These are not nice people we are dealing with.

We could not sort out better filtering at her mail server end for various reasons so in the end I showed her how to turn off the preview and delete all spam with one click. That mollified her and I was allowed to leave.

There was one small knock-on effect. Now apparently, according to her (other neighbours tell me she has said this) I “am wonderful with p3nises”.

Nice to know.

Thank you for letting me share that one…

If anyone has other tails of enforced PC support which might amuse, please share – T’is the season to be Jolly after all. But please changes names! This is a public blog, Tabitha may never come across my posts but at least she will know she is not called Tabitha.

EM12c and the Optimizer Statistics Console

Today we’re going to review another great feature in the EM12c that you may not have realized was available.  Once logged into a database target, click on the Performance menu and navigate to the Optimizer Statistics Console:

opt1

Optimizer Statistics Console Page

The new console page is clean, easy to navigate and has great access points to manage and monitor optimizer statistics for the given database target.

opt2

We’ll actually start at the bottom of the page with the Statistics Status and go up into the links.  Viewing the graph, you get a quick and clear idea of the status of your statistics for the database target you are logged into.  You can easily see if there are any stale stats that may be impacting performance and if there are any missing stats. You are shown how many objects are involved in the status category and can then move your way up into the links to review and manage your database statistics configuration.

Operations

opt4

View

We’re going to go through the Operations by order of logic and not by order in the console, so we’ll start with View.

This link will take you to a great little report console that will display information about statistics in the database.  Even though our example will display results for Stale statistics, note the other great filters for the report:

opt13

As we want to see everything, we’re not going to choose any other filters for our report until we get to the bottom and have the options of Current, Pending or All for our Scope We’re going to change it to All considering the version of database is 11.2.0.4 and we could have pending statistics waiting to be implemented.

opt14

The report quickly showed that both data dictionary and fixed objects were stale, (schemas are up to date!) so we could multi-select objects on the left of the report and gather stats, (along with other options) or we could use the next section we’ll be covering to gather those stats in an EM job and address the stale statistics issue in what I feel, is a more user friendly interface.

Gather

Back in the Optimizer Statistics Console, we can click on the Gather link, you will be taken directly to the Gather Statistics Wizard:

opt5

There is a clear warning at the top letting you know that as of DB11g, automated maintenance tasks should be enabled to gather nightly statistics.  This is turned on by default in most databases, so this warning is a nice addition to this page for those that may not be aware.

Below this warning, you are able to choose what level of statistics gathering you wish to perform, (database, schema, objects, fixed objects or data dictionary…)

By default, Oracle’s guidelines for statistic collection options will be chosen, but you can change this to customize if you wish to work outside of Oracle’s recommendations.  You can view the default values before deciding and if for some reason, you wish to use manual configuration options:

opt6

The wizard won’t ask you to set the manual configurations until later into the setup steps and if you change your mind, you can still choose the defaults.

At the bottom of the wizard, you also have the opportunity to use the Validate with the SQL Performance Analyzer,  but as noted, the changes won’t be published and you’ll have to do that manually post the statistics collection run.

The next page will take you through the customizes options you want to use instead of GATHER AUTO, (although, like I said, you could just leave it as is and have it just perform the default anyway! :))

opt7

Then you get to schedule it via the EM Job Service and would monitor and manage this job via the EM12c Job Activity console.

opt8

This means that this is not an automated maintenance task in the Database Job Scheduler and if you are not aware of how to view jobs via the DBMS_JOB_SCHEDULER, then you could have two stats jobs running for a database or even worse, simultaneously, so BE AWARE.

Lock/Unlock/Delete

As the Lock, Unlock and Delete links take you to similar wizards that do just the opposite action, we’ll group them together in one section.  Using the Unlock statistics wizard in our example, you can click on the link and choose to unlock a schema or specific tables:

opt9

If you decide to unlock just a few or even just one object, the wizard makes it quite easy to search and choose:

opt10

In the example above, I clicked on the magnifying glass next to the box for the Schema and then chose the DBSNMP schema.  I can use a wild card search in the object name box or leave it blank and all tables in the schema are returned and a simple click in the box to the left of the object name will select it to lock, delete or unlock it, (depending which wizard you’ve chosen…)  You also can view information on IF the object is locked or unlocked already, along with partitioning information, as you may have partitions that are locked while the table may not be.

Restore

The restore option is a great feature for those that may not be on top of their “restore statistics syntax on the top of their head” game.  Now, I have to admit, some of the options in this wizard makes me very nervous.  The idea that someone would dial back database level statistics vs. locating the one or two offenders that changed just seems like throwing the baby out with the bath water, but it is an option in the restore statistics command, so here it is in the wizard, as well.

opt11

You have the option to override locked objects and force a restore, too.  Like with locking and unlocking objects, the next screen in the wizard will allow you to choose a schema and object(s) that you wish to restore from and then once chosen, you will be asked when to restore to, including the earliest restore timestamp available:

opt12

Post these choices, you then schedule the EM Job to run the task and you’re set.

 Manage Optimizer Statistics

opt3

You must be granted the Create Job and Create Any Job privileges to take advantage of these features and will be warned if you haven’t been granted one or both.

Operations links include the ability to Gather Optimizer Statistics, which includes database and schema level, along with distinct object level.  Secondary links to restore, lock, unlock and delete statistics for each statistics gathering type is available as well.

Related Links

The Related Links section includes links for research and configuration settings, such as current object statistics, global statistic gathering options, the job scheduler to view current intervals for jobs involving statistics as well as automated maintenance tasks which inform you of any clean up and maintenance jobs that are part of the overall Cost Based Optimizer world.

Configure

opt15

These links will configure the Automated Maintenance Tasks, allowing you to update schedules of execution, disable/enable and work with SPA results, (SQL Performance Analyzer.)

opt16

If you haven’t used SPA yet, it has some pretty cool features allowing you to simulate and analyze different performance changes before you make them.  Nothing like being able to see in the future!

Working with some of these features may require a few management packs, (tuning, real application testing, etc.) but if you’re curious if you’re wandering into a new management pack land, it’s easy to locate from any EM12c console page:

opt17

You will receive information about any management packs involved with the features you are using in the EM12c console for the page you’re on:

opt18

So embrace the power with optimizer statistics in EM12c Cloud Control and if you want to know more about managing Optimizer Statistics click here for the Oracle documentation or this whitepaper for more info.



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [EM12c and the Optimizer Statistics Console], All Right Reserved. 2015.

RAC One Node 12c-revisited on ODA 12.1.2.1.0 featuring database on ACFS

Thanks to Andy Colvin (@acolvin) and the Enkitec lab I have been able to get my hands on an ODA X4-2. And since that’s a lot quieter than turning on my own lab server, and also a lot faster I used the environment to test RAC One Node in 12.1.0.2.1. I recently had a question from a customer about the usefulness of the solution and what it brings to the HA table. Well here you go.

There is just a slight caveat: to make use of the high availability features in RAC One Node you need to have an application that has been written to take advantage of them. Using Fast Connection Failover or Application Continuity is the way to go. Although I have written sample code to exploit the functionality of FCF/FAN and Application Continuity it has recently come to my attention that the very popular swingbench benchmark can do the same. I saw the connection pool tab in my demos but it didn’t “click”. Now I would like to show you what happens with your application during common scenarios in RAC One Node.

Scenario 1: Database relocation

For this blog post I’d like to try a database live migration from the first to the second host in the cluster. The database has been created using oakcli, primarily because I wanted to get more familiar with it. This is ODA release 12.1.2.1.0, the latest and greatest at the time of writing. I also love the fact that the ODA comes with quite a few of these: Intel(R) Xeon(R) CPU E5-2697 v2 @ 2.70GHz. To be precise, each node has 2s24c48t of Ivy Bridge Xeons. Nice.

[oracle@server1 ~]$ /opt/oracle/oak/bin/oakcli show version
Version
-------
12.1.2.1.0

As root (indeed!) I invoked oakcli, the primary ODA command line tool. The first time around I was bitten by not having read the documentation: if you specify the -version flag oakcli will create a new RDBMS home in both cluster nodes. That was _not_ what I had in mind. On the other hand I am now familiar with checking the environment (various permutations of oakcli show…) and deleting an Oracle home. This command created the datbase in the end:

[root@server1 ~]# oakcli create database -db RON -oh OraDb12102_home1

The command will prompt you for the datbase type (single instance, RAC One, clustered) – good stuff, simple to use, and more importantly: it worked first time.

Databases in ACFS

Now remember that from ODA release 12.1.2 onwards 11.2.0.4 and 12.1.x databases will be created on ACFS. In my case that looks slightly odd, I guess I have to get used to it first:

SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/datafile/o1_mf_system_b93xdj44_.dbf
/u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/datafile/o1_mf_sysaux_b93xdmhk_.dbf
/u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/datafile/o1_mf_undotbs1_b93xdosz_.dbf
/u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/datafile/o1_mf_undotbs2_b93xdypp_.dbf
/u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/datafile/o1_mf_users_b93xdzjj_.dbf
/u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/datafile/o1_mf_soe_tbs_b93yl62m_.dbf

6 rows selected.

SQL> select name from v$tempfile;

NAME 
----------------------------------------------------------------------------------------
/u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/datafile/o1_mf_temp_b93xdpl2_.tmp

SQL> select member from v$logfile

MEMBER
-----------------------------------------------------------------------------
/u01/app/oracle/oradata/datastore/RON/RON/onlinelog/o1_mf_1_b93xd3tb_.log
/u01/app/oracle/oradata/datastore/RON/RON/onlinelog/o1_mf_2_b93xd9m5_.log
/u01/app/oracle/oradata/datastore/RON/RON/onlinelog/o1_mf_3_b93y0hrq_.log
/u01/app/oracle/oradata/datastore/RON/RON/onlinelog/o1_mf_4_b93y0pf9_.log

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/datast
                                                 ore/RON/RON/controlfile/o1_mf_
                                                 b93xd3ll_.ctl
SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area/datastore/RON
db_recovery_file_dest_size           big integer 465G

The funny bit is that /u02/app/oracle/oradata/datastore contains snapshots… This is visible in the data file location where you find an .ACFS/snaps component. I believe the naming convention is to use .ACFS (invisible) followed by the snapshot name. You can view the snapshot detail using acfsutil:

[grid@server1 ~]$ acfsutil snap info RON /u02/app/oracle/oradata/datastore/
snapshot name:               RON
snapshot location:           /u02/app/oracle/oradata/datastore/.ACFS/snaps/RON
RO snapshot or RW snapshot:  RW
parent name:                 /u02/app/oracle/oradata/datastore/
snapshot creation time:      Wed Dec 17 15:26:24 2014

Interestingly the /u01 data store does not have a snapshot:

[grid@server1 ~]$ acfsutil snap info /u01/app/oracle/oradata/datastore
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )

But I digress…

Database setup and configuration

After the database has been created, it is defined in Clusterware as follows:

[oracle@server1 ~]$ srvctl config database -d ron
Database unique name: RON
Database name: RON
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: /u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/spfileRON.ora
Password file: /u02/app/oracle/oradata/datastore/.ACFS/snaps/RON/RON/orapwRON
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: 
Mount point paths: /u01/app/oracle/fast_recovery_area/datastore,/u01/app/oracle/oradata/datastore,/u02/app/oracle/oradata/datastore
Services: RON_racone
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: RON
Candidate servers: server1,server2
OSDBA group: dba
OSOPER group: racoper
Database instances: 
Database is administrator managed
[oracle@server1 ~]$ 

The mandatory service is defined like this:

[oracle@server1 ~]$ srvctl config service -d ron
Service name: RON_racone
Server pool: 
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type: 
Failover method: 
TAF failover retries: 
TAF failover delay: 
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition: 
Pluggable database name: 
Maximum lag time: ANY
SQL Translation Profile: 
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency: 
GSM Flags: 0
Service is enabled
Preferred instances: RON_2
Available instances: 
[oracle@server1 ~]$ 

With RAC One Node it is even more important to connect to the service, and not to the instance.

Getting ready for the testing

The situation before the test is shown here:

[oracle@server1 ~]$ srvctl status database -d ron
Instance RON_2 is running on node server2
Online relocation: INACTIVE
[oracle@server1 ~]$ srvctl status service -d ron
Service RON_racone is running on instance(s) RON_2

For swingbench to benefit from FCF it needs to be configured so that it uses a connection pool. I have used the swingbench FAQ (http://www.dominicgiles.com/swingbenchfaq.html) to do so. My connection is defined as follows:

   
      soe
      soe
      //cluster-scan/RON_racone
      Oracle jdbc Driver
        
            50
            25
            100
            50
            45
            10
            120
        
       
         50
         true
         nodes=server1:6200,server2:6200
         true
         20
      
      

This part and the rest of the configuration is based largely on this post: Application Continuity in Oracle Database 12c (12.1.0.2).

Now – finally – for the test!

Starting charbench will trigger the creation of 100 sessions on the currently active instance (I set NumberOfUsers to 100) and they will be chugging along nicely. Here is the output after a couple of minutes:

[oracle@server1 bin]$ ./charbench -c oda_soe.xml 
Author  :        Dominic Giles
Version :        2.5.0.952

Results will be written to results.xml.
Hit Return to Terminate Run...

Time            Users   TPM     TPS

7:24:28 AM      100     17242   291                                            

I saw about 290 TPS for a total of around 18,000 TPM over some time. Let’s start the live migration. But first, I’d like to use the glass bowl to see what might happen (new in 12.1.0.2):

[oracle@server1 ~]$ srvctl predict database -db RON -verbose
Database ron will be started on node server1
Database ron will be stopped on node server2
Service ron_racone will be started on node server1

Well I guess that might be correct, but let’s try:

[oracle@server1 ~]$ srvctl relocate database -d ron -verbose -node server1
Configuration updated to two instances
Instance RON_1 started
Services relocated
Waiting for up to 30 minutes for instance RON_2 to stop ...
Instance RON_2 stopped
Configuration updated to one instance
[oracle@server1 ~]$ 

While this command executed I didn’t lose a single connection – 100 SOE connections were always established. It also takes a few second for the cache to warm up, during which the transaction rate dips a little bit:

[oracle@server1 bin]$ ./charbench -c oda_soe.xml
Author  :        Dominic Giles
Version :        2.5.0.952

Results will be written to results.xml.
Hit Return to Terminate Run...

Time            Users   TPM     TPS

7:29:11 AM      100     13135   191                                            

One minute after this the throughput is back to normal.

[oracle@server1 bin]$ ./charbench -c oda_soe.xml
Author  :        Dominic Giles
Version :        2.5.0.952

Results will be written to results.xml.
Hit Return to Terminate Run...

Time            Users   TPM     TPS

7:30:50 AM      100     16800   295          

Online relocation with RAC One node is certainly a possibility and works nicely if your application is ready for connection pools and the new way of connecting to the database.

Preview of the next article in the series

In the next article I’d like to add a couple of things I haven’t had time to test yet: 12.1.0.x extends the concept of the database resident connection pool to Java applications (and other middle tier) which would allow me to scale even further. I’d also like to show you what happens if the current RAC One Node instance fails. Stay tuned!

Oracle database operating system memory allocation management for PGA – part 4: Oracle 11.2.0.4 and AMM

This is the 4th post in a series of posts on PGA behaviour of Oracle. Earlier posts are: here (PGA limiting for Oracle 12), here (PGA limiting for Oracle 11.2) and the quiz on using PGA with AMM, into which this blogpost dives deeper.

As laid out in the quiz blogpost, I have a database with the following specifics:
-Oracle Linux x86_64 6u6.
-Oracle database 11.2.0.4 PSU 4
-Oracle database (single instance) with the following parameter set: memory_target=1G. No other memory related parameters set.

In this setup, I run the pga_filler script (source code here), which creates a collection until the session statistic ‘session pga memory’ exceeds the grow_until variable, which for this case I set to 2100000000 (approximately 2.1G).

So: the instance is set to have AMM (memory_target) with a size of 1GB, which is supposed to be the total amount memory which this instance uses, and a session runs a PL/SQL procedure which only stops if it has allocated 2.1GB, which is clearly more than configured with the memory_target parameter. Please mind a collection, which the anonymous procedure uses to allocate memory, is outside of the memory areas for which Oracle can move data to the assigned temporary tablespace (sort, hash and bitmap memory areas).

After startup of the instance with only memory_target set to 1G, the memory partitioning looks like this:

SYS@v11204 AS SYSDBA> select component, current_size/power(1024,2), last_oper_type from v$memory_dynamic_components where current_size != 0;

COMPONENT							 CURRENT_SIZE/POWER(1024,2) LAST_OPER_TYP
---------------------------------------------------------------- -------------------------- -------------
shared pool										168 STATIC
large pool										  4 STATIC
java pool										  4 STATIC
SGA Target										612 STATIC
DEFAULT buffer cache									424 INITIALIZING
PGA Target										412 STATIC

This is how v$pgastat looks like:

SYS@v11204 AS SYSDBA> select * from v$pgastat;

NAME								      VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter					  432013312 bytes
aggregate PGA auto target					  318200832 bytes
global memory bound						   86402048 bytes
total PGA inuse 						   78572544 bytes
total PGA allocated						   90871808 bytes
maximum PGA allocated						   93495296 bytes
total freeable PGA memory					    2818048 bytes
process count								 57
max processes count							 58
PGA memory freed back to OS					    3211264 bytes
total PGA used for auto workareas					  0 bytes
maximum PGA used for auto workareas					  0 bytes
total PGA used for manual workareas					  0 bytes
maximum PGA used for manual workareas					  0 bytes
over allocation count							  0
bytes processed 						    8479744 bytes
extra bytes read/written						  0 bytes
cache hit percentage							100 percent
recompute count (total) 						 18

SYS@v11204 AS SYSDBA> show parameter pga

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target		     big integer 0

Okay, so far so good. v$memory_dynamic_components shows the PGA Target being 412M, and v$pgastat shows the aggregate PGA target setting being 412M too. I haven’t set pga_aggregate_target (as shown with ‘show parameter pga’), because I am using memory_target/AMM for the argument I hear the most in favour of it: one knob to tune.

Next up, I start the pga_filler script, which means the session starts to allocate PGA.

I keep a close watch using v$pgastat:

SYS@v11204 AS SYSDBA> select * from v$pgastat;

NAME								      VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter					  432013312 bytes
aggregate PGA auto target					  124443648 bytes
global memory bound						   86402048 bytes
total PGA inuse 						  296896512 bytes
total PGA allocated						  313212928 bytes
maximum PGA allocated						  313212928 bytes

This shows the pga_filler script in progress by looking at v$pgastat from another session. The total amount of PGA allocated has grown to 313212928 (298M) here.

A little while later, the amount of PGA taken has grown beyond the PGA target (only relevant rows):

total PGA inuse 						  628974592 bytes
total PGA allocated						  645480448 bytes
maximum PGA allocated						  645480448 bytes

However, when looking at the memory components using v$memory_dynamic_components, it gives the impression PGA memory is still 412M:

SYS@v11204 AS SYSDBA> select component, current_size/power(1024,2), last_oper_type from v$memory_dynamic_components where current_size != 0;

COMPONENT							 CURRENT_SIZE/POWER(1024,2) LAST_OPER_TYP
---------------------------------------------------------------- -------------------------- -------------
shared pool										168 STATIC
large pool										  4 STATIC
java pool										  4 STATIC
SGA Target										612 STATIC
DEFAULT buffer cache									424 INITIALIZING
PGA Target										412 STATIC

You could argue PGA is explicitly mentioned as ‘PGA Target’, but then: the total of the memory area’s (PGA Target+SGA Target) do show a size that roughly sums up to be equal to the memory_target.

A little while later, this is what v$pgastat is showing:

total PGA inuse 						  991568896 bytes
total PGA allocated						 1008303104 bytes
maximum PGA allocated						 1008303104 bytes

Another glimpse at v$memory_dynamic_components shows the same output as above, PGA Target at 412M. This is the point where it get’s a bit weird: the total amount of PGA memory (according to v$pgastat) shows it’s almost 1G, memory_target is set at 1G, and yet v$memory_dynamic_components show no change at all.

Again a little further in time:

total PGA inuse 						 1325501440 bytes
total PGA allocated						 1342077952 bytes
maximum PGA allocated						 1342077952 bytes

Okay, here it get’s really strange: there’s more memory allocated for PGA memory alone than has been set with memory_target for both PGA and SGA memory structures. Also, v$memory_dynamic_components shows no change in SGA memory structures or exchange of memory from SGA to PGA memory.

If v$pgastat is correct, and memory_target actively limits the total amount of both SGA and PGA, then the session must allocate memory out of thin air! But I guess you already came to the conclusion too that either v$pgastat is incorrect, or memory_target does not limit memory allocations (as at least I think it would do).

Let’s dump the PGA heap of the active process to see the real memory allocations of this process:

SYS@v11204 AS SYSDBA> oradebug setospid 9041
Oracle pid: 58, Unix process pid: 9041, image: oracle@bigmachine.local (TNS V1-V3)
SYS@v11204 AS SYSDBA> oradebug unlimit
Statement processed.
SYS@v11204 AS SYSDBA> oradebug dump heapdump 1
Statement processed.

(9041 is the PID of the process running PL/SQL)

Now look into (the relevant) data of the PGA heap dump:

[oracle@bigmachine [v11204] trace]$ grep Total\ heap\ size v11204_ora_9041.trc
Total heap size    =1494712248
Total heap size    =    65512
Total heap size    =  1638184

Okay, this is clear: the process actually took 1494712248 (=1425M) plus a little more memory. So, memory_target isn’t that much of a hard setting after all.

But where does this memory come from? There ought to be a sort of combined memory effort together with the SGA for memory, right? That was the memory_target promise!

Let’s take a look at the actual memory allocations of a new foreground process in /proc/PID/maps:

[oracle@bigmachine [v11204] trace]$ less /proc/11405/maps
00400000-0bcf3000 r-xp 00000000 fc:02 405855559                          /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
0bef2000-0c0eb000 rw-p 0b8f2000 fc:02 405855559                          /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
0c0eb000-0c142000 rw-p 00000000 00:00 0
0c962000-0c9c6000 rw-p 00000000 00:00 0                                  [heap]
60000000-60001000 r--s 00000000 00:10 351997                             /dev/shm/ora_v11204_232652803_0
60001000-60400000 rw-s 00001000 00:10 351997                             /dev/shm/ora_v11204_232652803_0
...
9fc00000-a0000000 rw-s 00000000 00:10 352255                             /dev/shm/ora_v11204_232685572_252
a0000000-a0400000 rw-s 00000000 00:10 354306                             /dev/shm/ora_v11204_232718341_0
3bb3000000-3bb3020000 r-xp 00000000 fc:00 134595                         /lib64/ld-2.12.so
3bb321f000-3bb3220000 r--p 0001f000 fc:00 134595                         /lib64/ld-2.12.so
3bb3220000-3bb3221000 rw-p 00020000 fc:00 134595                         /lib64/ld-2.12.so
3bb3221000-3bb3222000 rw-p 00000000 00:00 0
3bb3400000-3bb3401000 r-xp 00000000 fc:00 146311                         /lib64/libaio.so.1.0.1
...
3bb5e16000-3bb5e17000 rw-p 00016000 fc:00 150740                         /lib64/libnsl-2.12.so
3bb5e17000-3bb5e19000 rw-p 00000000 00:00 0
7f018415a000-7f018416a000 rw-p 00000000 00:05 1030                       /dev/zero
7f018416a000-7f018417a000 rw-p 00000000 00:05 1030                       /dev/zero
7f018417a000-7f018418a000 rw-p 00000000 00:05 1030                       /dev/zero
7f018418a000-7f018419a000 rw-p 00000000 00:05 1030                       /dev/zero
7f018419a000-7f01841aa000 rw-p 00000000 00:05 1030                       /dev/zero
7f01841aa000-7f01841ba000 rw-p 00000000 00:05 1030                       /dev/zero
7f01841ba000-7f01841ca000 rw-p 00000000 00:05 1030                       /dev/zero
7f01841ca000-7f01841da000 rw-p 00000000 00:05 1030                       /dev/zero
7f01841da000-7f01841ea000 rw-p 00000000 00:05 1030                       /dev/zero
7f01841ea000-7f01841fa000 rw-p 00000000 00:05 1030                       /dev/zero
7f01841fa000-7f018420a000 rw-p 00000000 00:05 1030                       /dev/zero
7f018420a000-7f018421a000 rw-p 00000000 00:05 1030                       /dev/zero
7f018421a000-7f018422a000 rw-p 00000000 00:05 1030                       /dev/zero
7f68d497b000-7f68d4985000 r-xp 00000000 fc:02 268585089                  /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnque11.so
...

When I run the pga_filler anonymous PL/SQL block, and strace (system call trace) utility, I see (snippet):

mmap(0x7f0194f7a000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194f7a000
mmap(0x7f0194f8a000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194f8a000
mmap(0x7f0194f9a000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194f9a000
mmap(0x7f0194faa000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194faa000
mmap(0x7f0194fba000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194fba000
mmap(0x7f0194fca000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194fca000
mmap(0x7f0194fda000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194fda000
mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_NORESERVE, 6, 0xea000) = 0x7f0194e6a000
mmap(0x7f0194e6a000, 65536, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194e6a000
mmap(0x7f0194e7a000, 131072, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194e7a000
mmap(0x7f0194e9a000, 131072, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194e9a000
mmap(0x7f0194eba000, 131072, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 6, 0) = 0x7f0194eba000

So, when looking back, it’s very easy to spot the SGA memory, which resides in /dev/shm in my case, and looks like ‘/dev/shm/ora_v11204_232652803_0′ in the above /proc/PID/maps snippet.
This means that the mmap() calls are simply, as anyone would have guessed by now, the PGA memory allocations. In the maps snippet these are visible as being mapped to /dev/zero.
When looking at the mmap() call, at the 5th argument, which is the number 6, we look at a file descriptor. In /proc/PID/fd the file descriptors can be seen, and file descriptor 6 is /dev/zero, as you probably suspected. This way the allocated memory is initial set to zero.

By now, the pga_filler script finishes:

TS@v11204 > @pga_filler
begin pga size : 3908792
last  pga size : 2100012216
begin uga size : 1607440
last  uga size : 2000368
parameter pat  : 0

Taking the entire 2.1G I made the collection to grow to. With memory_target set to 1G.

Conclusion
The first conclusion I made is that PGA memory is very much different than SGA/shared memory. Anyone with a background in Oracle operating-system troubleshooting will find this quite logical. However, the “promise” AMM/memory_target made, in my interpretation, is that the memory would be used seamless. This is simply not the case. Shared memory is in /dev/shm, and PGA is mmaped/allocated as private memory.

Still, this wouldn’t be that much of an issue if memory_target would limit memory in a rigid way, and memory could, and actually would, very easily float between PGA and SGA. It simply doesn’t.

Why don’t we see Oracle trying to reallocate memory? This is the point where I can only guess.

– Probably, Oracle would try to grow the shared pool if it has problems allocating memory for SQL, library cache, etc. This probably hasn’t happened in my test.
– Probably, Oracle would try to grow the buffer cache if it can calculate a certain benefit from enlarging it. This probably hasn’t happened in my test.
– The other SGA area’s (large and java pool) probably are grown if these are used, and need more space for allocations. This probably didn’t happen in my test.
– For the PGA, a wild guess is the memory manager calculates using the workarea sizes (sort, hash and bitmap areas), which are not noticeably used in my test.

Another conclusion and opinion is AMM/memory_target is not a set once and forget option. In fact, it isn’t that much of a difference from using ASMM from a DBA perspective: you carefully need to understand the SGA size, and you carefully need to (try to) manage the PGA memory. Or reasoned the other way around: the only way you can sensibly set memory_target is if you know the correct SGA size and the PGA usage. Also having Oracle manage the memory area’s automatically is not unique to AMM: Oracle will reallocate (inside the SGA) if it finds it necessary, with AMM, ASMM and even manual set memory area’s. But the big dis-advantage of AMM (at least on linux, not sure about other operating systems) is that huge pages can’t be used, which has a severe impact on “real life” databases, in my experience. (Solaris CAN use huge pages with AMM(!)).

A final word: of course I tested a very specific situation. In most real-life cases there will be multiple sessions, and the PGA manageable memory areas will be used. However, the point I try to make is memory_target is simply not a way to very easily make your database be hard limited to the value set. Probably, in real life, the real amount of memory used by the instance will in the area of the value set with memory_target, but this will be subject to what memory areas you are exactly using. Of course it can differ in a spectaculair way if collections or alike structures are used by a large number of sessions.

Tagged: amm, memory, mmap, oracle, private memory, shared memory, shm