Re: Single Instance 4 Database - System is Slow?

Comments embedded.
captain_2010_at_yahoo.com wrote:
> HI,
>
> I have oracle 8i installed on WindowsXp and
> running one instance with 4 Oracle Database.
>

Impossible. One instance - one database, period. This is Oracle, not SQL Server.

> Problem
> -------
> The system is running slow and I do not understand why it is slow ?
> Have tuned all parameters in init.ora have even increase blocked size
> still no improvement.

Apparently you haven't run any 10046 traces at level 8 or level 12 to poll the Oracle Wait interface. Also, you say nothing of the total memory installed on this server, or how you have your files located on the disk or disks. 'Tuning' init.ora parameters won't help you with poorly located files, nor will it help with network issues creating communication problems. And it won't help with poorly coded applications and improperly designed schemas. And I'm not certain what speed-related goal you were attempting to achieve with recreating your database with a larger block size, and, it's obvious that methodology failed, else you wouldn't be asking here.

>
> Planning for below action
> -------------------------
> Now planning to change mode from dedicated to share mode.
> Before changing to share mode wanted to know the effects of changing
> it.
>
> Information gathered on dedicated and share mode
> ------------------------------------------------
> Though below are the few points that have been gathered
> 1) A dedicated server only serves one session.
> A shared server can serve many sessions.
> If there are going to be a large number of concurrent sessions, use
> shared server.
>

Wrong. Dedicated and shared modes both serve many sessions.

> 2) Shared Server does two things:
> 1. It allows you to use some functionality that would not otherwise be
> available (like Connection Manager and connection pooling)

Connection pooling, yes. Connection Manager has nothing to do with Dedicated or Shared server mode.

> 2. It gets around resource shortfalls by having fewer processes at the
> back end.

Not necessarily. Cconnection pooling enables the database server to timeout an idle session and use the connection to service an active session. The idle logical session remains open (the user is not disconnected), and the physical connection is automatically re-established when the next request from that session is received, so it appears to the idle user he's never lost connectivity. With connection pooling you can also limit the number of active sessions a database server can have.

>
> 3) For long queries and transactions, dedicated servers will be more
> appropriate.
>

Only because long queries can time out.

> Captain

You seriously need to consider submitting your resignation as you know nothing of Oracle, but possibly plenty about SQL Server. Poking around hoping to hit upon a solution is not the accepted method of application/database tuning, except, possibly to you.

Before you create any more problems hire a competent Oracle DBA to manage this system. You appear to be exceptionally unqualified.

David Fitzjarrell Received on Wed Jul 13 2005 - 16:30:48 CDT