Jay, though migrating to locally managed tablespaces is the better long term
solution I would think you probably could have solved the problem by increasing
the number of freelists available on the problem objects.
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jay.Miller@xxxxxxxxxxxxxxxx <Jay.Miller@xxxxxxxxxxxxxxxx>
Sent: Friday, January 26, 2018 4:02:50 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: In case anyone else hits this - dictionary managed tablespaces can
have much worse performance in 12c
This one took a while to diagnose so I thought I’d share it in case anyone else
encounters the issue.
We recently migrated an old 10g database (legacy from another firm that we
acquired years back) to our brand new 12c environment. App performance dropped
through the basement so I was called in to look at it.
Checked the obvious things, execution plans hadn’t changed (we still had the
old db to compare it with) but INSERTs were much slower. In fact logon inserts
to the sys.aud$ table were now showing up in the top 10 SQL statements.
We checked with storage but they assured us that writes were, if anything,
faster.
Looking more closely at the audit table inserts I saw that we were waiting on
buffer busy waits. “That’s strange,” I thought, “haven’t seen that sort of
thing since Oracle switched to locally managed tablespaces 100 years ago. Huh,
I don’t suppose…”
Sure enough, the migration had been done via rman and applying logs so all
tablespace settings were the same as they were in the old 10g database which
apparently had never switched their older tablespaces to locally managed. This
included a tablespace which had been created specially to house sys.aud$. We
moved everything to locally managed and suddenly the app performance was a bit
better than it was in 10g as opposed to about 4x worse.
So moral of the story seems to be that 12c really doesn’t like dictionary
managed tablespaces. At least on RedHat 6.
HTH someone.
Jay Miller
Sr. Oracle DBA
