How to revert to DMTS ?

#1

Hallo,

for performance reasons I need a couple of dictionary managed tablespaces, but the system
tablespace itself is already locally managed.

What options do I have?

Thanks,

Gerard

#2

"Gerard H. Pille" <ghp (AT) skynet (DOT) be> a écrit dans le message de news: 4ca4cc4e$0$14249$ba620e4c (AT) news (DOT) skynet.be...

Quote:

Hallo,

for performance reasons I need a couple of dictionary managed tablespaces, but the system
tablespace itself is already locally managed.

What options do I have?

Thanks,

Gerard

Recreate the database.

Regards
Michel

#3

On Sep 30, 10:43*am, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:

Quote:

Hallo,

for performance reasons I need a couple of dictionary managed *tablespaces, but the system
tablespace itself is already locally managed.

What options do I have?

Thanks,

Gerard

That sounds highly suspect. Why do you think the performance would be
better? Normally, large numbers of segments in the data dictionary
for a DMT is the performance issue.

I assume that you can create a tablespace in another database and
transport it, but don't really know.

jg
--
@home.com is bogus.
http://www.datacenterknowledge.com/t...ta-center-faq/

#4

Michel Cadot wrote:

Quote:

"Gerard H. Pille"<ghp (AT) skynet (DOT) be> a écrit dans le message de news: 4ca4cc4e$0$14249$ba620e4c (AT) news (DOT) skynet.be...
| Hallo,
|
| for performance reasons I need a couple of dictionary managed tablespaces, but the system
| tablespace itself is already locally managed.
|
| What options do I have?
|
| Thanks,
|
| Gerard

Recreate the database.

Regards
Michel

The users and developers would kill me.

#5

On Thu, 30 Sep 2010 19:43:41 +0200, Gerard H. Pille wrote:

Quote:

Hallo,

for performance reasons I need a couple of dictionary managed
tablespaces

What, exactly, is the performance problem that the dictionary managed
tablespaces would solve?

--
http://mgogala.byethost5.com

#6

Mladen Gogala wrote:

Quote:

On Thu, 30 Sep 2010 19:43:41 +0200, Gerard H. Pille wrote:

Hallo,

for performance reasons I need a couple of dictionary managed
tablespaces

What, exactly, is the performance problem that the dictionary managed
tablespaces would solve?

Excessive undo on intensive deletes and inserts in indexes, multiple sessions inserting (DMTS +

freelists) and slower full table scans.

#7

On Thu, 30 Sep 2010 21:21:35 +0200, Gerard H. Pille wrote:

Quote:

Excessive undo on intensive deletes and inserts in indexes, multiple
sessions inserting (DMTS + freelists) and slower full table scans.

You maybe having problems with transactions restarts for write
consistency. Second, with the dictionary managed tablespaces, you may
have locking issue, especially in the RAC environment. Worse than that,
full table scans will actually be slower because you will have to keep
defragmenting and stop living, as opposed to the title of the ground
breaking article which introduced the locally managed tablespaces. I
would rather do a serious redesign of your application system and perform
functional partitioning than delving into dictionary managed tablespaces.
Risks are too great. In any case, if you decide to go with DMTS, I will
give you a strange advice: test, test, test and then test some more.
Be aware of the risks.

--
http://mgogala.byethost5.com

#8

On Sep 30, 3:21*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:

Quote:

Excessive undo on intensive deletes and inserts in indexes, multiple sessions inserting (DMTS +
freelists) and slower full table scans.

You can use freelist management with locally managed tablespaces - you
do not need to use ASSM:
http://download.oracle.com/docs/cd/B...htm#sthref1155

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

#9

On Sep 30, 9:21*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:

Quote:

Mladen Gogala wrote:
On Thu, 30 Sep 2010 19:43:41 +0200, Gerard H. Pille wrote:

Hallo,

for performance reasons I need a couple of dictionary managed
tablespaces

What, exactly, is the performance problem that the dictionary managed
tablespaces would solve?

Excessive undo on intensive deletes and inserts in indexes, multiple sessions inserting (DMTS +
freelists) and slower full table scans.

Do you by any chance mix here ASSM vs. MSSM with LMT vs. DMT (I like
all these acronyms by the way) ? What you describe above applies to
some extent (in certain scenarios) to tablespaces using Automatic
Segment Space Management (ASSM) instead of MSSM (Manual Segment Space
Management using freelists / freelist groups instead of ASSM bitmaps).
What makes you think that using DMT instead of LMT addresses these
issues?

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

#10

On Sep 30, 12:21*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:

Quote:

Mladen Gogala wrote:
On Thu, 30 Sep 2010 19:43:41 +0200, Gerard H. Pille wrote:

Hallo,

for performance reasons I need a couple of dictionary managed
tablespaces

What, exactly, is the performance problem that the dictionary managed
tablespaces would solve?

Excessive undo on intensive deletes and inserts in indexes, multiple sessions inserting (DMTS +
freelists) and slower full table scans.

As far as undo, see Jonathan's explanation for one possibility in
http://groups.google.com/group/comp....874c5ac59448f/

If you have such a situation (or the one in the Dion link), Mladen's
comments about restarts or application design might be spot on. I
take it you were comparing different versions of Oracle running the
same app?

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...ing-san-diego/