Super-Sizing PGA Workareas in Oracle 12c

After a customer asked me for possibilities of super-sizing PGA workareas in version 12c, I took the chance to revisit the topic and perform some tests. Great material has already been posted by Alex Fatkulin (Hotsos Symposium 2014) and Norbert Debes (Secrets of the Oracle Database, Apress), but I wanted to verify, how the current release is behaving and I found some interesting things.

Super-Sizing PGA Workareas in 12c

Oracle Database Result Cache Troubleshooting

I was troubleshooting an issue where the Oracle Database Result Cache did not get used when the RESULT_CACHE hint was specified inside a subquery. As the main query has a sysdate inside, the RESULT_CACHE Hint can not be specified for the main query, but only for the subquery.

SELECT SQ.*, sysdate FROM
(SELECT * FROM tableX, ....) SQ;

When using “/*+ RESULT_CACHE */ inside of Subquery, result cache was not enabled.

SELECT SQ.*, sysdate FROM
(SELECT /*+ RESULT_CACHE */ * FROM tableX, ....) SQ;

I found out that there is a trace event which can be set for diagnostics.

ALTER SESSION SET EVENTS '43905 trace name context forever, level 1';

This generated a trace file containing a clue to the reason:

QKSRC: ViewText[ecode=942] = SELECT  /*+ RESULT_CACHE */ ......

So, it seemed that there was an internal ORA-942 somewhere that prevented result caching from being used. The solution was to qualify the objects inside the subquery. Now the result cache was used correctly:

SELECT SQ.*, sysdate FROM
(SELECT /*+ RESULT_CACHE */ * FROM ownerY.tableX, ....)




Problems with big SGAs (>200G) on Linux

I recently had an issue where a database with 240GB SGA (1 huge shared memory segment) configured with hugepages on a system with 512G RAM was suddenly becoming instable and new logons were denied with these error message:

ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2667
Additional information: 1736718
Additional information: 215016800256

This was strange because ipcs -a showed all shared memory segments and all processes existed. It turned out that there is a known issue with the Linux feature “prelink” and very big SGAs (>200g). This MOS Note gives some details:

Connected to an idle instance, while database instance is running (Process J000 died, see its trace file kkjcre1p: unable to spawn jobq slave process ) (Doc ID 1578491.1)

Obviously, exadata customers also suffered from this issue and therefore prelink is disabled beginning with exadata version Redhat is reporting the same issue in combination with Informix databases in Knowledgebase article: https://access.redhat.com/solutions/1186953

AWR Warehouse – security issue

During implementation of AWR Warehouse, I discovered that AWR warehouse is using temporary staging schemas in the AWR warehouse repository database. These schemas life approximately for the duration of a datapump import job and are then dropped again. Due to the fact that the used password is not compliant with customers password verification function, the jobs failed.

‘ DEFAULT TABLESPACE ‘ || tbsname;

The staging schemas are created with the password “SYS_GUID” in capital lettters. This looks to me like the developer tried to generate a random string as password but instead overlooked that the password is set to fixed string “SYS_GUID” instead.

Oracle support has noticed this issue and filed an enhancement request. It is a pity, that this is not filed as a bug, but an enhancement.

Well, I hope this improves in a future version together with dynamic retention and purging options as well as customizable staging directories.

Happy AWR┬┤ing.

OPatch bugs when applying Grid Infrastructure

For one of my clients, I experienced several issues with applying PSU with opatch

There were some code changes in opatchauto, which are not yet production-ready. I am looking forward to seeing a new opatch release (maybe or 11) which has these issues fixed.

    “opatchauto apply -analyze” is normally used before patching to verify if there are any conflicts. In our case, the “opatchauto apply -analyze” did a shutdown of the database instances. Not good.
    When applying GI PSU patch to RDBMS Home and database instances are already stopped, patch apply fails. The reason is that opatchauto is trying to query database instances for information before shutting down instances. If instances are already stopped, these queries fail and opatchauto aborts.
  • OPatchauto is shutting down database instance with “srvctl stop home -stopoption TRANSACTIONAL”. I was of the opinion that this is doing a “shutdown transactional”, which in turn will wait until the last session is performing a commit. This can take a long time. Support Engineer explained that the wait will be limited to 10min, after which the system is begin stopped with “shutdown abort”. This is not documented in “http://docs.oracle.com/database/121/RACAD/srvctladmin.htm#RACAD5040” and in my opinion a bug. I expect the shutdown to be “immediate” whithout delaying the patching process.

Oracle releases new Standard Edition 2 for

Customers have been waiting for a Standard Edition release for version Then, in July, a support engineer leaked that there will be a whole new type of “Edition”, called “Standard Edition 2” to replace both “Standard Edition” and “Standard Edition One”. The respective MOS Note disappeared shortly after the accidental publication. Now today the release finally became official and can be downloaded via OTN. The “SE2” is priced exactly as the previous “Standard Edition”. SE2 is limited to a single server with 2 CPU sockets, or when used with RAC to two servers with one socket each. In addition, there seems to be a limit of 16 CPU threads (e.g. 8 cores with hyperthreading) when using one host and 8 CPU threads each for both nodes when using RAC.

So beginning with, there will be no more “Standard Edition (4 CPU socket limit)” or “Stanard Edition One (2 socket limit)” releases anymore. There is no way to avoid going to “Standard Edition 2 (2 socket limit)” in the future.

This might mean for SE customers, which were using 4 CPU sockets (occupied) on a single host or a 2-node RAC Cluster with 2 CPU sockets (occupied) per host, that they have to remove half the (occupied) sockets from their installation. I am not sure what happens with already bought SE licences and how they can be “migrated” to SE2 licenses. I hope that this will not start a new wave of license-driven Oracle->PostgreSQL/MySQL/MS SQL Migrations.

Now would probably be the right time for 11gR2 Standard Edition users to start upgrade tests for 12c to gather experience. On the other hand, waiting until end of year and bugs found by “early adaptors” might safe you some troubles.