| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: data dictionary with a million tables
A copy of this was sent to Andrew May <amay_at_home.com>
(if that email address didn't require changing)
On Thu, 06 May 1999 04:14:06 GMT, you wrote:
>Tell them to come up with a more realistic design. If many of the tables
>were actually referenced by SQL statements, the database would have to
>cache SQL statements referring to each table and the definition of each
>table and index. This would be a huge overhead on parsing and would
>require a lot of memory for the sql area and dictionary cache.
>
100% agreed -- shared sql would be useless in this database. this database would never perform well at all.
>Also, extent allocation is a large, single-threaded overhead in oracle.
>An ST enqueue is required for each extent (recursive SQL has to take a
>slice from the fet$ table for each extent). Try the following command
>sometime:
>
>create table fred
>storage (initial 10k next 10k pctincrease 0 minextents 1000)
>tablespace <whatever>
>As select * from sys.dual;
>
>Tell me how long it takes to create, then drop it.
>
this is why i'm following up your post tho...
In Oracle8i, release 8.1, the times would be:
SQL> create table locally_managed_table ( x int ) 2 storage ( initial 1k next 1k
3 pctincrease 0 maxextents unlimited 4 minextents 1000 )5 /
Table created.
Elapsed: 00:00:00.13
SQL>
SQL> drop table locally_managed_table
2 /
Table dropped.
Elapsed: 00:00:00.60
And in 8.0 they were:
SQL> create table dictionary_managed_table ( x int ) 2 storage ( initial 1k next 1k
3 pctincrease 0 maxextents unlimited 4 minextents 1000 )5 /
Table created.
Elapsed: 00:00:20.07
SQL>
SQL> drop table dictionary_managed_table
2 /
Table dropped.
Elapsed: 00:00:14.02
In Oracle8i, there is a concept of a locally managed tablespace whereby extents for tablespaces are managed with a bitmap in the tablespace itself. no more fet$ extent allocations, *much* higher concurrency on space management, no recursive sql, etc, etc, etc. All in all, slightly faster :)
>A table with 100 rows would only require a few blocks and you may waste
>a lot of space. It would be an impossible task to detect any tables or
>indexes that were over/underallocated and reorganize them unless you had
>a tool to do this for you.
>
>Regards,
>Andrew.
>
>Luis Londono wrote:
>>
>> I need help with a littlle problem that I have. I have a bunch of
>> developers that want to create an application using Oracle that would
>> end up having probably a million very small tables (100 - 1000 records
>> each). It is easier for them to develop it this way, but I am trying to
>> convince them that it is not a good idea. It seems to me that the
>> performance of the entire database would suffer a lot since the data
>> dictionary would be over worked. Unfortunately I do not have any solid
>> evidence or an argument more convincing than a gut feeling. Does anyone
>> have some information that would help me? Or if I am wrong, I would
>> sure like to know. Thanks.
>>
>> -Luis
>>
>> --
>> Luis Londono
>> Senior Systems Engineer 370 E 1130 S
>> Big Planet Inc. Provo, UT 84606
>> mailto: luisl_at_bigplanet.net (801) 345-7222
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Thu May 06 1999 - 09:39:38 CDT
 |
 |