Re: oracle database-startup time
John Hurley
> I wanted to know what oracle database startup time depends on? Does
> size of database matter or it is constant on the same machine?
Are you talking about on a clean startup or where recovery is
involved?
Have you done any testing on systems you have access to?
CPU cycles are involved along with disk reads ( control files,
tablespace files ) and some disk writes ( log files etc ). The size
of the database should not be directly a factor unless the number of
supporting files increases drastically.
Obviously when startup has to do recovery the time involved can vary
considerably.
gazzag
> Hi,
>
> I wanted to know what oracle database startup time depends on? Does
> size of database matter or it is constant on the same machine?
What do you mean by "database startup" time? There is a field called
STARTUP_TIME in the V$INSTANCE view. The documentation states,
somewhat unhelpfully:
"Time when the instance was started."
However, if the database requires crash-recovery it may be some time
before you see the "Database open" message. You'd be able to get that
information from the alert log.
HTH
-g
Mark D Powell
> Hi,
>
> I wanted to know what oracle database startup time depends on? Does
> size of database matter or it is constant on the same machine?
Size of the database matters only in the number of data files
allocated to house the database data may increase with size and on
startup Oracle has to read every file header block and compare the SCN
information in it to the information in the Control file to verify the
database is ready to be opened and does not require recovery. So
dealing with 64 files should be faster than 512 files however when you
can to do 2000 - 25000 IO per second depending on hardware you may not
really notice.
The size of the online redo log files and the undo tablespaces also
may have some bearing in that bigger logs could require more time to
read to reapply activity in the event of crash recovery. Larger undo
tablespaces would imply large transaction support (or heavy user load)
that in turn could require more time to rollback incomplete work as
part of the crash recovery processing (apply redo then undo incomplete
transactions).
The size of the SGA matters in that allocating and loading control
information into a large SGA may take longer than allocating a more
moderate size SGA though this is usually not significant.
I would hazard to say that Startup time is mostly depending on the
quality of the hardware.
HTH -- Mark D Powell --
Noons
>>> I wanted to know what oracle database startup time depends on? Does
>>> size of database matter or it is constant on the same machine?
>>
>> What do you mean by "database startup" time?
>
> Time of executing command 'startup' (when database is shut down)
If you have a very large SGA (tens of Gigabytes) and you are not using hugepages
- or its equivalent in your OS - then it will take some time to startup, no
matter what. In our Power6 IBm it takes 90 seconds to allocate all page tables
for an SGA of 35GB. If I use hugepages, it takes 2 seconds. That's how much
difference it makes.
If the startup follows a shutdown abort then it will also be slow, depending on
how many redo log files you have and how busy with changes the database was at
the time of the shutdown. Other than shutdown abort, the size of redo logs has
almost no impact on startup time. We use 1GB redos and it makes no difference
whatsoever.
Size of database is mostly immaterial. Other than size of undo and redos in the
case above.
joel garry
Missed the OP, but perhaps googling DBA_HIST_INSTANCE_RECOVERY may
come up with some explanations of the various settings that may be
used to limit startup time.
jg
--
@home.com is bogus.
http://www.cio.com/article/639363/Oracle_Awarded_1.3_Billion_in_SAP_Lawsuit