RE: Choosing data file size for a multi TB database?
- From: Zoran Martic <zoran_martic@xxxxxxxxx>
- To: matthewp@xxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 6 Sep 2005 14:25:31 -0700 (PDT)
Hi Matthew,
I agree that CKPT may become the bottleneck soner then
I thought.
It looks obvious that CKPT breaking point is when DBWR
is faster doing I/O in parallel while CKPT needs to do
things in serial and suddenly becomes the bottleneck
in doing the database checkpoint.
I agree that the CKPT can be slow because it needs to
write all things to the control file, read and write
to the datafile header (direct path read and write),
....
But I am curious out of these 15 minutes what is
taking the majority of time? Which waits?
If you have 4000 files you will probably need at least
4000 + (the number of control files) * 4000 writes and
at least 4000 reads + not sure how many control file
sequential reads.
We all know that just doing 20000 I/O operations are
taking some time :)
Is this the case here?
Regards,
Zoran
--- "Parker, Matthew" <matthewp@xxxxxxxxxx> wrote:
> In Oracle a large number of datafiles do affect you
> administratively and in recovery.
>
> In a 22TB database that has +4500 files. A simple
> query such as
> select tablespace_name,sum(bytes)/1024/1024 from
> dba_segments group by tablespace_name;
>
> takes 5 minutes and that is not indicative of a
> large number of objects, it is indicative of the
> freespace bitmap walk across the datafiles (locally
> managed tablespaces). Under some circumstances or
> patch versions it can take much longer.
>
> Also in examination of this database even under low
> load the time to check point is 15 minutes and many
> times overlays the next log switch. Someday Oracle
> will parallelize the CKPT process, similar to how it
> is done on recovery.
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
