Yes, you've understood me correctly. I have not done a formal benchmark. I regard it as fairly self-evident. You've no doubt seen ST enqueue contention often enough to want to avoid it, and locally managed tablespaces do just that. They also save a little on segment extension.

You can validate the need to avoid heavy use of those DD views however by reading through catspace.sql. You'll notice that whenever you hit a locally managed tablespace, the query goes to an X$ table. Those X$ tables are not however persistent. If you trace queries on them, you'll see that they do lots of disk reads.

If I recall correctly, Jonathan Lewis discusses locally managed tablespaces in chapter 8 of his new book, and comes to basically the same conclusions.

On 26-Aug-2000, you answered a question about locally managed tablespaces.

I was wondering if you could elaborate on your feelings about locally managed tablespaces. If I understand you well, when creating a new environment now, you would create the database with all tablespaces locally managed, and avoid some data dictionary views. What benchmarks, logical processes, experience, etc. go into that recommendation? We are eager to start using them, but want to do so with our eyes wide open.