Hi,
1. Currently we have 111 concurrent connections to our production
database. This may triple at high usage. We have not yet used multiple
listeners (e.g. Multi-threaded server) on this system. You can set up
multiple database writers and parallel servers to handle high load. Our
database server is a Sun Fire V880 mid range server, and has 4 CPU's and
8 GB memory. Top shows CPU states as 36% idle, 45% user, rest kernel.
Our Application Server has a similar specification. Memory shows 8.0G
real, 3.8G free, 3.5G swap in use, 18.5G swap free. The disks are Ultra
SCSI on a fibre optic RAID array.
2. The way to limit the size and length of SQL Query runs is by the
undo_retention time of the UNDO segments. If queries are inefficient and
take too long they will time out. You then have 2 options - try to tune
SQL by altering code or providing appropriate indexes and/or increase
undo_retention time. You can tune SQL as far as it will, then up the
size of pga_aggregate_target and db_cache_size to reduce swapping and
disk access. If updates and data loads seem slow, check the alert file
for redo log usage. Redo log switch times should ideally not be more
than one every 5 minutes, definitely not more than one every minute so
size accordingly, with about 5 redo log groups.
3. If you have multiple database sources which need to be merged into a
single instance, look at Hummingbird's Genio tool which provides a means
to code data conversions and transfer from multiple sources to a
centralised database. This will help you build your data warehouse.
Lloyd
-----Original Message-----
From: Dawn Syverson [mailto:Dawn.[Email address protected]
Sent: 28 July 2005 19:26
To: Ruskin Lloyd
Subject: Re: FW: where to begin
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
thank you for your response - do you use connection manager and multiple
listeners to handle load? I know I can set up resource management but
can I also limit the size and length of query runs (i.e. killing any
runs over 1 hour run time or something like that?) Are you using
multithreaded server and parallel query?
It is unclear at this point how many concurrent users there will be (I
can find that # out though based on the current system) and you are
correct there is no application running against this system (just adhoc
reports and queries). Standardizing the sql will be difficult as
everything is adhoc.
>>> "Lloyd Ruskin " [Email address protected] 07/28/05 12:45 PM >>>
Dawn,
Oracle databases are built to be scalable to handle high transaction
loads. If common SQL is used, memory usage is reduced.
The storage parameters allow for multiple freelists and multiple
transactions on each database block in each segment of each object.
Undo segment areas and temporary segment areas can be sized to handle
read consistent views and pending transactions.
Using an Application Server approach, with UNIX servers, user software
could be hosted on a 4 CPU (or more) server with 32GB memory and the
database hosted on another server of a similar specification. Your
hardware suppliers would probably suggest RAID disks, etc.
This is the approach used for the Student Information System I
administer in my UK-based university for many direct and web users. The
application has a basic 2 server approach for application software and
database server. A separate product uses another web server to provide
access.
Query software is being developed using Hummingbird BIQuery which is web
enabled on another server. You don't appear to have any application
software, or you haven't mentioned it, so you are back to 2 or maybe 3
servers to support your data, but only one database server.
You don't mention how many concurrent active users you expect to use the
data. I suspect we are talking in hundreds, rather than thousands.
Hope this helps,
Lloyd
-----Original Message-----
From: Dawn Syverson
Sent: 28 July 2005 17:35
To: Ruskin Lloyd
Subject: RE: where to begin
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
would there be performance issues that could be worked out if thousands
of queries were running against the data in each schema at any give
time? Also, is there some breaking point at which you lose benefits
from having them all in one schema (i.e. perhaps I could put 5 each in
one instance and have 6 instances total - each with their own schema?)
>>> "Lloyd Ruskin " [Email address protected] 07/28/05 10:51 AM >>>
Hi,
You said:
"we have 30 schools each with their own instance. "
If they all use schema with similar table structures, could you consider
merging the tables using VPD? Then you could segregate the data and
reduce the number of instances/schemas to manage considerably!
Lloyd
-----Original Message-----
From: Dawn Syverson
[mailto:oracledba-ezmlmshield-x51571283.[Email address protected]
Sent: 28 July 2005 16:43
To: LazyDBA Discussion
Subject: RE: where to begin
Thank you this is great. FYI: all instances are used for reporting and
ad hoc queries - we have 30 schools each with their own instance. None
are testing or development and none can be eliminated.
The path I would follow are :
1) Eliminate instances you do not need or perhaps you may be able to
combine use of instances?
2) From the list of remaining instances, take out those used as
development, uat, performace etc.. These non-production instances
should be housed in a separate server/domain.
Now you essentially have 2 main options.
1) Have one large machine capable of running all these instances..( ie.
SUnE15K for example ) . Have one database instance and use different
schemas for separation. However, you will need to segregate machine and
I/O resource by use of Resource Manager.
2) Go for Oracle RAC, and one database instance. Still use separate
schema to achieve the 'data' separation you may require. However, the
server resources are now handled via the RAC nodes.
3) use of row level security, streams etc , I would suggest, are not
ideal for the type of consolidation you want.
I've been through the same exercise and hence my suggestions.
Harish.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page