Creating a database is slow on Windows XP

[Dim St Thomas]

I am a developer working on a database client program. I am testing this
program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk)
This machine has Oracle 9.2.0.1.0 and RedBrick database software installed.
I am testing the software by creating small test databases.
If I create an Oracle database using the Database Configuration Assistant,
it takes forever just to create the database. If I choose to create a
new database and deselect all the options (Example Schemas, Data Mining,
OLAP, JVM, Intermedia etc) it still takes 10 minutes to create the
database. With RedBrick I can create a database in less than 10 seconds.
Once the database has been created, loading and accessing the data
takes about the same time with Oracle and RedBrick.

So here are my questions:
1. Why does Oracle take so long just to create an empty database/
what is it doing during database creation?
2. Oracle includes tuning tools, but I think these are only for use
on existing databases. Is there any Oracle settings I can change to
speed up database creation?
3. If I installed more memory on my machine would it significantly
speed up database creation?

Thanks

[Jim Kennedy]

"Dim St Thomas" <di*********@yahoo.com> wrote in message
news:1e**************************@posting.google.c om...

I am a developer working on a database client program. I am testing this
program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk)
This machine has Oracle 9.2.0.1.0 and RedBrick database software installed. I am testing the software by creating small test databases.
If I create an Oracle database using the Database Configuration Assistant,
it takes forever just to create the database. If I choose to create a
new database and deselect all the options (Example Schemas, Data Mining,
OLAP, JVM, Intermedia etc) it still takes 10 minutes to create the
database. With RedBrick I can create a database in less than 10 seconds.
Once the database has been created, loading and accessing the data
takes about the same time with Oracle and RedBrick.

So here are my questions:
1. Why does Oracle take so long just to create an empty database/
what is it doing during database creation?
2. Oracle includes tuning tools, but I think these are only for use
on existing databases. Is there any Oracle settings I can change to
speed up database creation?
3. If I installed more memory on my machine would it significantly
speed up database creation?

Thanks

Why are you creating databases again and again? I don't know anything about
Redbrick, but in Oracle you are creating a lot of structures etc. Usually,
one creates one database and then creates multiple schemas.(which don't take
long at all, seconds, tops)
Jim

[Hans Forbrich]

Dim St Thomas wrote:

I am a developer working on a database client program. I am testing this

I have a strong suspicion that you might be creating databases where you
really should be creating schemas.

With all due respect, the way you phrase your question implies you do not
understand enough of the Oracle internals to program efficiently toward the
way Oracle implements the database constructs. All SQL is NOT created
(actually implemented) equally.

If you have not done so already, PLEASE get and read a copy of Thomas Kyte's
Expert One-on-one Oracle.

/Hans

[Adam]

10 minutes, whow that's fast :)

You can create a new database from sqlplus using 'create database' SQL
statement.
That's fast too.
DCA creates the db and runs a complete set of scripts to load all
PL/SQL API's. On my server this makes over 200 MB of data.

To speed up creation, you can tell DCA NOT to generate the DB but to build
a creation script for you. In this script you can comment out subscripts
creating obsolete parts of system schema (like oracle 7 compatible
replication views or other junk)...
On 12 May 2004 20:11:23 -0700, Dim St Thomas <di*********@yahoo.com> wrote:

I am a developer working on a database client program. I am testing this
program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb
disk)
This machine has Oracle 9.2.0.1.0 and RedBrick database software
installed.
I am testing the software by creating small test databases.
If I create an Oracle database using the Database Configuration
Assistant,
it takes forever just to create the database. If I choose to create a
new database and deselect all the options (Example Schemas, Data Mining,
OLAP, JVM, Intermedia etc) it still takes 10 minutes to create the
database. With RedBrick I can create a database in less than 10 seconds.
Once the database has been created, loading and accessing the data
takes about the same time with Oracle and RedBrick.

So here are my questions:
1. Why does Oracle take so long just to create an empty database/
what is it doing during database creation?
2. Oracle includes tuning tools, but I think these are only for use
on existing databases. Is there any Oracle settings I can change to
speed up database creation?
3. If I installed more memory on my machine would it significantly
speed up database creation?

Thanks

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

[Dim St Thomas]

Hans Forbrich <fo******@yahoo.net> wrote in message news:<w%Coc.4114$RM.3429@edtnps89>...

Thanks for all replies.

Dim St Thomas wrote:

I am a developer working on a database client program. I am testing this
I have a strong suspicion that you might be creating databases where you
really should be creating schemas.

I think you are right. That's what happens when you are brought up on
MS Access. I have looked in the online doc which gives the following
definition of a schema:

A named collection of objects, such as tables, views, clusters,
procedures, and packages, associated with one or more particular
users.

But this seems to be an arbitary collection rather than a "real"
database object. In the example schemas, a schema is equivalent to a
user. Is this an acceptable model, i.e. create a new user account for
each schema?
With all due respect the way you phrase your question implies you do not
understand enough of the Oracle internals to program efficiently toward the
way Oracle implements the database constructs.

Yes, I wouldn't claim to be anything other than an Oracle novice.
Thanks for your help (and respect - an unusual thing on usenet!).

[Jim Kennedy]

"Dim St Thomas" <di*********@yahoo.com> wrote in message
news:1e**************************@posting.google.c om...

Hans Forbrich <fo******@yahoo.net> wrote in message news:<w%Coc.4114$RM.3429@edtnps89>...
Thanks for all replies.

Dim St Thomas wrote:

I am a developer working on a database client program. I am testing

this
I have a strong suspicion that you might be creating databases where you
really should be creating schemas.

I think you are right. That's what happens when you are brought up on
MS Access. I have looked in the online doc which gives the following
definition of a schema:

A named collection of objects, such as tables, views, clusters,
procedures, and packages, associated with one or more particular
users.

But this seems to be an arbitary collection rather than a "real"
database object. In the example schemas, a schema is equivalent to a
user. Is this an acceptable model, i.e. create a new user account for
each schema?

With all due respect the way you phrase your question implies you do not
understand enough of the Oracle internals to program efficiently toward the way Oracle implements the database constructs.

Yes, I wouldn't claim to be anything other than an Oracle novice.
Thanks for your help (and respect - an unusual thing on usenet!).

Yes, use schemas. You create one database and have many schemas in it. (at
least one, but you can have many) Then create a user who is going to own
all the objects. Other users can access those objects by referring to them
as owner.object_name. So if the owner is finance and the table is invoices
users would refer to the invoices table as
select ... from finance.invoices where....

Hope that is clearer and causes you to spin your wheels less.
Jim

[Hans Forbrich]

Dim St Thomas wrote:

But this seems to be an arbitary collection rather than a "real"
database object. In the example schemas, a schema is equivalent to a
user. Is this an acceptable model, i.e. create a new user account for
each schema?

An Oracle schema is a collection as described. Generally a schema is used
to define a consistent collection ... consistency preferrably defined in
terms of a specific application. As such a schema could be considered the
set of tables, indexes, views, stored procedures, security and audit
mechanisms (and so on) that form the data core to an application.

(As an aside, note also that Oracle supports Java Stored Procedures, which I
find often overlooked but in many instances preferrable to middle tier
procedures.)

The second part of a schema is that it needs an 'owner' to take
responsibility for the definition. Historically the 'owner' translates to
a 'user'. However, this need not be the case as it is possible to have an
owner account locked out and it's objects still accessible. (As a personal
opinion, I believe this 'locked account ownership model' is frequently a
sign of a professional Oracle development environment.)

To see one specific variant of this, refer to the MDSYS owner which holds
the object types for the 'spatial' and 'locator' geometries as well as the
universal lookup tables and their contents but should be locked out. In
this model , the stored procedures can be used as required, and the type
defs are used as complex columns (objects or intelligent structures) in
your application tables.
I very strongly encourage your research starting with the Thomas Kyte
'Expert One On One Oracle' book, followed by (or concurrently studied with)
the Concepts manual for your version - found at http://docs.oracle.com and
these followed by the appropriate "Administrator's Guide" or "Developer's
Guide(s)" from the same location.

Good luck (and apologies for my [typical] wordiness)
/Hans