New local temporary tablespace in 12.2 defaults to SYSTEM for some users after upgrade

2018-03-22 update: Mike Dietrich blogged on this, with a kind tip o’ the hat. Also updated bug and planned fix info.

Note: big thanks to the invaluable Mike Dietrich for the help with this one.  And a dozen other upgrade issues that we learned about from his blog.  As well as to my awesome colleague Tom Nicholson who identified the issue.

While upgrading databases to 12.2.0.1, we ran into an issue with the new feature called Local Temporary Tablespaces. While a good concept, it has some problems with how it sets itself for users during upgrade, and it seems as if everyone should be checking this post-upgrade and fixing it if necessary.

To quote the Oracle doc on Local Temporary Tablespaces: “Starting with Oracle Database 12c Release 2 (12.2), local temporary tablespaces are available. A local temporary tablespace stores separate, non-shared temp files for every database instance. A local temporary tablespace is used only for spilling temporary results of SQL statements, such as queries that involve sorts, hash aggregations, and joins. These results are only accessible within an instance.”  Also see the Concepts doc version of the explanation. You do not have to create this tablespace on shared disk, even with RAC, as it is intended to be local instance only.

There is a new column in DBA_USERS, LOCAL_TEMP_TABLESPACE, that shows the default local temporary tablespace.  This functions just like the default temporary tablespace, in that it’s a default.  There is also a new database-wide parameter, again just like the default temporary tablespace, which defines what to assign a user if you don’t specify it in the create user command.  See below for some poking around with the new database property.

Now for the problem … while testing some existing code that uses dbms_metadata.get_ddl to generate user create statements, my colleague Tom saw it was now generating a second ALTER for each CREATE USER, which set the default local temporary tablespace.  And then he noticed that for a subset of the users, the alter was trying to set local temp as SYSTEM, which would fail with ORA-12911: permanent tablespace cannot be temporary tablespace.  After some research on the new feature and looking at LOCAL_TEMP_TABLESPACE in dba_users we saw that a significant portion our existing users had their default local temporary tablespace set to the SYSTEM tablespace after upgrade.  All users should have been left NULL for local_temp_tablespace, as is the expected behavior, which would cause them to default to using their default temporary tablespace just as they would have in 12.1.  But that’s not what happened.

select    local_temporary_tablespace  -- 12.2 only
        , temporary_tablespace
        , count(*)
FROM dba_users
group by local_temporary_tablespace, temporary_tablespace
order by 1;

LOCAL_TEMP_TABLESPACE TEMPORARY_TABLESPACE   COUNT(*)
--------------------- -------------------- ----------
SYSTEM                TEMP                         49
TEMP                  TEMP                         12
                      TEMP                         66

Out of 127 users, 66 had the expected behavior of being set to NULL, and therefore defaulting to their default TEMP tablespace. 12 were set to TEMP. And 49 were set to SYSTEM. Which, of course, is not a TEMP tablespace at all.

After some conversation with Mike Dietrich, he noticed that the SPARE9 field found in sys.user$ was set to 0 for all of users that were assigned the SYSTEM tablespace as their local temporary tablespace. It is consistent across multiple different databases that we have upgraded. This seems to be the thing that triggers the incorrect assignment. We opened an SR and found out that there is a (non-visible) bug open with Development on this, Bug 23715518. There is no fix planned at the moment. [Through Mike Dietrich’s blog, learned the full bug name: BUG 23715518 – APPSST12201::SYSTEM TABLESPACE IS MARKED AS LOCAL_TEMP_TABLESPACE AFTER UPGRADE and that it will be fixed in Oracle Database 18c.] The impact of this is not clear but the idea of some types of temp operations (sorts, hash aggregations, and joins) occurring in the SYSTEM tablespace doesn’t seem like a good thing … if this indeed happens, not confirmed by testing at this time.

We decided for now to run a script to change the local temp to match the user’s temp and Oracle agreed with this workaround. It’s a pretty trivial piece of SQL to generate the required script but here it is for convenience …

select 'alter user '||username||' LOCAL TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';'
from dba_users where username not in ('XS$NULL'); 

You could also choose to only do this for users who have their local temp set to SYSTEM and leave the rest alone.  We opted for consistency.

Unrelated to the problem, here’s little bit of futzing with the overall database default for local temp tablespace.

-- the default is null after 12.2 upgrade

> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME like 'DEFAULT_%_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE
---------------------------------------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE             USERS
DEFAULT_TEMP_TABLESPACE                  TEMP

-- you can't just set it to TEMP if it's currently null and 
-- TEMP is your default, since it defaults to that anyway

> ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE TEMP;
ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE TEMP
*
ERROR at line 1:
ORA-12907: tablespace TEMP is already the default temporary tablespace

-- there's no reason to do this, but it's interesting to try ...
-- we happen to have second temporary tablespace in this database

> ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE PSTEMP;

Database altered.

-- and now it will let you set it to TEMP

> ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE TEMP;

Database altered.

> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME like 'DEFAULT_%_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE
---------------------------------------- ----------------------------------------
DEFAULT_LOCAL_TEMP_TABLESPACE            TEMP
DEFAULT_PERMANENT_TABLESPACE             USERS
DEFAULT_TEMP_TABLESPACE                  TEMP
Advertisements

Peoplesoft and 12c Adaptive Features

2016-11-29 EDIT: Oracle has announced that in 12.2 the features that drove this overuse of the result cache (dynamic stats triggered by SPDs) will default to off, and provided patches to apply to 12.1 as well.  More info here.

I had been collecting examples and mentally writing something about this but the king of Peoplesoft DBAs, David Kurtz, beat me to it.  So I’ll just link to his analysis: Peoplesoft on Oracle 12c.  Read on for some of my own experience tuning around the issues with 12c Adaptive Features.  Apologies that it isn’t complete or full of examples, I wanted to at least get something out here before more months pass me by.  I may improve it over time.  Also, the experiences that led to this post were on 12.1.0.2.0.  We have patched to 160119, the January bundle patch, but there hasn’t been enough time to assess if any of the fixes to SQL Plan Management and Adaptive features apply to this situation.

The short story is that Peoplesoft has a lot of trouble with the 12c adaptive features.  Peoplesoft still has a lot of queries that do not use binds.  Especially with Peoplesoft jobs that run thousands of these queries that are normally < 1 second, there can be relatively massive increases in runtime when you move to 12c.  If you look at trace files (10046 and 10053), you see that what is happening is that it is spending a ton of time in the parse phase, doing “test” queries.  This is of course poor application design, but it is a vendor app and it certainly isn’t the only one like it.

Unfortunately the issues with applications that are not perfectly designed for Oracle were not apparent until we spent some quality time with our Peoplesoft databases running on 12c in production.  So at this point it would take a pretty large performance testing effort to turn off adaptive features at the database level.  So far, I have been working with individual performance issues by:

  • Having the developers add session variable statements to turn off adaptive features at the beginning of the job;
  • If that’s not possible, or we only want to fix one query, having them add an OPT_PARAM(‘OPTIMIZER_ADAPTIVE_FEATURES’,’false’) hint;
  • If they can’t/won’t change the code (delivered code, for instance) then I create a one-hint SQL Profile with force matching on.

I’ve seen a number of similar-but-different issues with performance and 12c adaptive features, but one recurring pattern that is resolvable in the same way each time goes as follows.

  • Cursor pin s wait on x waits
  • Also library cache pin
  • Spending inordinate time in parse

In 11g, the “fix prod fast” solution for an execution plan gone rogue was to look at the history of various plans, find a good one, and create a SQL Plan Baseline (we’ll ignore differing plans for differing binds for now for the sake of brevity).  Then you can go back and take some time to look at stats.  One of the most annoying things about 12c Adaptive Features is that SQL Plan Directives cause the Optimizer to ignore your SQL Plan Baselines.  It thinks it knows better, or at least it wants to try and find out.  More aspects of this particular situation:

  • SQL Plan Baseline(s) are ignored/not used, even if Accepted, even if Fixed (maddening).  Either a baseline you create to try to fix the problem, or one that was already there – same result.  You can see them being considered and discarded in an Optimizer trace.  Even if the plan was used by the same query moments ago, the Optimizer says they fail to validate as workable plans.
  • There are tons of SQL Plan Baselines, sometimes 100+.
  • Often, at least one of the tables involved in the query has an inordinate number of SQL Plan Directives, even after running stats.

To fix this situation I have taken the following steps:

  • Created SQL Profile disabling 12c Adaptive Features (thanks to Kerry Osborne for his single-hint sql profile script) … see hint above.
  • Deleted all SQL Baselines.  Flush the individual statement from cache if necessary.
  • At this point, parse related waits dropped off.
  • The query was now using a new plan but it was not a great plan, so it was still somewhat slow (but not waiting on parse / pin S / library cache etc).
  • Ran stats on all tables involved, a new plan was derived by the Optimizer. (Note: stats were not stale and had also been run manually per-table beforehand to attempt a fix.)
  • Created new baseline with best plan.

From that point on, performance was excellent, parse time was minimal, and the plan was stable.

Bug in tablespace size/usage monitoring in 12.1.0.2 OEM (DBA_TABLESPACE_USAGE_METRICS)

Despite rarely posting these days, this one is worth trying to get the word out.  We just ran across a 12.1.0.2 bug which is not fixed in the January or April bundle patches/PSUs.  It is also not mentioned in the 12c “you should install these one-off patches” page.  Yet it has the ability to take down your production environment.

The view DBA_TABLESPACE_USAGE_METRICS is used by OEM to drive its alerts on tablespace storage.  It turns out that due to a bug in the code behind the view, it leaves out a number of tablespaces entirely.  You can easily see the problem by comparing DBA_TABLESPACES and DBA_TABLESPACE_USAGE_METRICS.  This means that if any of the missing tablespaces runs out of space (hits the max autoextend) you won’t get any of your percentage-free alerts before it does.  We found anywhere from one to over 70 missing tablespaces, depending on the database.  Every database was missing at least one tablespace from this view.

You can easily see the problem by comparing DBA_TABLESPACES and DBA_TABLESPACE_USAGE_METRICS:

select tablespace_name from dba_tablespaces 
where tablespace_name not in (
    select tablespace_name from DBA_TABLESPACE_USAGE_METRICS
);

We thought we were smart by having another backup monitor running from cron, in case OEM missed sending a notification or we missed it.  Except we used the same view in our monitor, so it misses these tablespaces as well.  Just goes to show that it’s always tough to judge how paranoid to get when trying to eliminate dependencies in redundant systems.

I’m pretty miffed that this has not been deemed important enough issue to be included in a bundle patch.  It’s a big deal, but apparently Oracle doesn’t think so.  If they’d even just referenced it on their page about important one-offs to apply to 12c, we would have already had it in production as of our most recent patching round.

Edit: Oracle Support recommended the 12.1.0.2.4 version of the patch to apply on our 12.1.0.2.160119 Oracle home, and it worked, the missing regular tablespaces now show up in the view.  However, the Oracle doc on this says that “DBA_TABLESPACE_USAGE_METRICS describes tablespace usage metrics for all types of tablespaces, including permanent, temporary, and undo tablespaces.”

After applying the patch, all of the normal tablespaces are now included in the view:

select distinct tablespace_name from dba_data_files minus select tablespace_name from dba_tablespace_usage_metrics;

no rows selected

HOWEVER the TEMP tablespaces are still missing – is this a new bug?  Updated SR to find out, will update here when I know.

select distinct tablespace_name from dba_temp_files minus select tablespace_name from dba_tablespace_usage_metrics;

TABLESPACE_NAME
——————
PSGTT01
PSTEMP

 

 

 

 

HOTSOS!

So, yeah, kinda let this die for a while. Hope to post some recent work soon. Just a quick note that I’m at HOTSOS13 and looking forward to a few days of stuffing my brain! I’m on twitter as @tylervanvierzen if you happen to be there too.