RE: In case anyone else hits this - dictionary managed tablespaces can have much worse performance in 12c
Date: Tue, 30 Jan 2018 15:32:58 +0000
Message-ID: <0D8F4CAC0F9D3C4AACC63F50FD9957F7547907F8_at_PRDCTWPEMLMB31.prod-am.ameritrade.com>
Thanks Mark. Yes, that's what I used to do back in the good old days and I briefly considered it here. But given the small number of objects involved it seemed better to simply migrate everything to locally managed tablespaces.
Jay Miller
Sr. Oracle DBA
201.369.8355
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark
Sent: Monday, January 29, 2018 12:26 PM
To: oracle-l_at_freelists.org
Subject: Re: In case anyone else hits this - dictionary managed tablespaces can have much worse performance in 12c
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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Jay.Miller_at_tdameritrade.com<mailto:Jay.Miller_at_tdameritrade.com> <Jay.Miller_at_tdameritrade.com<mailto:Jay.Miller_at_tdameritrade.com>> Sent: Friday, January 26, 2018 4:02:50 PM To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 30 2018 - 16:32:58 CET