FAQ
I've read the tahiti doc of (nearly) the same name [0], but it really
didn't do too much. I'm pretty sure I've posted about this but it's a
high priority for me now.

Sad story, short: my level 0 database backups take over 7 hours.
People have mentioned to me that they have databases of comparable
size (or larger) backing up in 1-2 hours. I'm equally, if not more,
interested in reducing the restore/recovery (duplication) time, which
has been around 8-10 hours.

Here's the details of this past weekend's level 0 database backup,
from v$rman_backup_job_details:

TIME_TAKEN_DISPLAY: 7:30:47

INPUT_BYTES_DISPLAY: 1.03T

OUTPUT_BYTES_DISPLAY: 172.91G

INPUT_BYTES_PER_SEC_DISPLAY: 39.80M

OUTPUT_BYTES_PER_SEC_DISPLAY: 6.55M

Here's my configuration:
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/rman/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO

'/u00/app/oracle/product/10.2.0/db_1/dbs/snapcf_xcede.f'; # default

The command script I use for backups is:

SQL "alter session set optimizer_mode=RULE";
RUN {

BACKUP INCREMENTAL LEVEL 0 DATABASE;

BACKUP ARCHIVELOG ALL NOT BACKED UP DELETE INPUT;

}

Points of order:
1. I'm aware than I can backup the archive logs along with the
database in one single command.
2. I set the optimizer_mode based on this blog post.
http://www.pythian.com/blogs/363/slow-rman-backups-check-this-out .
As you can see from my overly-enthusiastic comments, my tests were
very favorable. However that didn't seem to translate into either
faster backups or duplications.
3. The destination for all backup files is /rman, which is a Veritas
vxfs partition. I'll have to get the physical details tomorrow.

[0] http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtunin.htm#sthref1057

Search Discussions

  • Krish.hariharan_at_quasardb.com at Nov 15, 2007 at 11:22 pm
    Don,

    My experience is with VLDB (30TB) rman backups going directly to tape
    (disk cache) averaging 1G/channel-min. However, I am curious about the
    following

    CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET

    I did a benchmark with CAS technology based RMAN backup and the limiting
    factor was CPU not Disk IO. My gut feel is that you may be CPU bound based
    on the ratio of bytes read per sec to bytes written per sec, due to backup
    set compression.

    Can you benchmark a smaller backup (couple of files) with and without
    backupset compression?

    The alternative if you want to use compressed backup sets, if you are
    thread constrained but not CPU constrained, and should CPU be your
    bottleneck, is to increase the number of channels to the extent your disk
    subsystem would allow, to meet your SLA target.

    If on the other hand you are disk constrained, which I suspect you are
    not, then you may be able to speed up by using ASM or direct io options
    (if not already set) and should the underlying array/disk has io head
    room.

    -Krish
  • Robert Freeman at Nov 16, 2007 at 3:41 am
    IF you are CPU constrained then compression can be a
    problem. However, IF you are NOT CPU constrained then
    Compression of the backup to disk can and will reduce
    the size of your backups.

    RF
    --- krish.hariharan_at_quasardb.com wrote:
    Don,

    My experience is with VLDB (30TB) rman backups going
    directly to tape
    (disk cache) averaging 1G/channel-min. However, I am
    curious about the
    following

    CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE
    TO COMPRESSED BACKUPSET
    I did a benchmark with CAS technology based RMAN
    backup and the limiting
    factor was CPU not Disk IO. My gut feel is that you
    may be CPU bound based
    on the ratio of bytes read per sec to bytes written
    per sec, due to backup
    set compression.

    Can you benchmark a smaller backup (couple of files)
    with and without
    backupset compression?

    The alternative if you want to use compressed backup
    sets, if you are
    thread constrained but not CPU constrained, and
    should CPU be your
    bottleneck, is to increase the number of channels to
    the extent your disk
    subsystem would allow, to meet your SLA target.

    If on the other hand you are disk constrained, which
    I suspect you are
    not, then you may be able to speed up by using ASM
    or direct io options
    (if not already set) and should the underlying
    array/disk has io head
    room.

    -Krish

    --
    http://www.freelists.org/webpage/oracle-l

    Robert G. Freeman
    Author:
    Now Available for Pre-Sales on Amazon.com!!!!

    Oracle Database 11g New Features (Oracle Press)

    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)
  • Robert Freeman at Nov 16, 2007 at 3:44 am
    Don a few questions:

    What version is this..? Are you using a block
    tracking file for your incrementals? If not you may be
    backing up way more information than you need too.

    Are you doing your parallel backups to the same
    disk? You could be running into disk contention if so.

    What is your CPU usage during the backup. As
    mentioned earlier, if you are CPU constrained then
    compression can slow things down. If you are not CPU
    constrained then compression can very much speed
    things up and make your backup images much smaller.

    Robert

    Don Seiler wrote:
    I've read the tahiti doc of (nearly) the same name
    [0], but it really
    didn't do too much. I'm pretty sure I've posted
    about this but it's a
    high priority for me now.

    Sad story, short: my level 0 database backups take
    over 7 hours.
    People have mentioned to me that they have databases
    of comparable
    size (or larger) backing up in 1-2 hours. I'm
    equally, if not more,
    interested in reducing the restore/recovery
    (duplication) time, which
    has been around 8-10 hours.

    Here's the details of this past weekend's level 0
    database backup,
    from v$rman_backup_job_details:

    TIME_TAKEN_DISPLAY: 7:30:47
    INPUT_BYTES_DISPLAY: 1.03T
    OUTPUT_BYTES_DISPLAY: 172.91G
    INPUT_BYTES_PER_SEC_DISPLAY: 39.80M
    OUTPUT_BYTES_PER_SEC_DISPLAY: 6.55M
    Here's my configuration:
    RMAN> show all;

    RMAN configuration parameters are:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7
    DAYS;
    CONFIGURE BACKUP OPTIMIZATION ON;
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
    TYPE DISK TO '/rman/%F';
    CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE
    TO COMPRESSED BACKUPSET;
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE
    DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
    DISK TO 1; # default
    CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
    '/rman/%U';
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; #
    default
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO
    >
    '/u00/app/oracle/product/10.2.0/db_1/dbs/snapcf_xcede.f';
    # default

    The command script I use for backups is:

    SQL "alter session set optimizer_mode=RULE";
    RUN {
    BACKUP INCREMENTAL LEVEL 0 DATABASE;
    BACKUP ARCHIVELOG ALL NOT BACKED UP DELETE
    INPUT;
    }

    Points of order:
    1. I'm aware than I can backup the archive logs
    along with the
    database in one single command.
    2. I set the optimizer_mode based on this blog post.
    http://www.pythian.com/blogs/363/slow-rman-backups-check-this-out
    .
    As you can see from my overly-enthusiastic comments,
    my tests were
    very favorable. However that didn't seem to
    translate into either
    faster backups or duplications.
    3. The destination for all backup files is /rman,
    which is a Veritas
    vxfs partition. I'll have to get the physical
    details tomorrow.

    [0]
    http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtunin.htm#sthref1057
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l

    Robert G. Freeman
    Author:
    Now Available for Pre-Sales on Amazon.com!!!!

    Oracle Database 11g New Features (Oracle Press)

    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)
  • Don Seiler at Nov 16, 2007 at 6:27 am
    Some quick answers to these questions before I hop into bed.
    1. What version is this..? Are you using a block
    tracking file for your incrementals? If not you may be
    backing up way more information than you need too.
    Sorry for not mentioning this straight away. This is Oracle EE
    10.2.0.2 on RHEL4. Processing is 4 dual-core 64-bit CPUs. Both RDBMS
    and OS are 64-bit.

    Yes we use BCT for incrementals. Our level 1 incrementals take
    anywhere from 30 to 90 minutes. Our schedule is to do a level 0 on
    Saturday evening, and level 1 on all other evenings during the week.
    2. Are you doing your parallel backups to the same
    disk? You could be running into disk contention if so.
    Yes we are (to /rman), and this is my first hunch. Shamefully, I'm
    not well-versed by any means with the storage side of database
    operations. If this Veritas partition were striped across multiple
    disks, does that mean anything in terms of being able to support
    parallel writes? Or should I only think of it as 1 and not try to
    play games?
    3. What is your CPU usage during the backup. As
    mentioned earlier, if you are CPU constrained then
    compression can slow things down. If you are not CPU
    constrained then compression can very much speed
    things up and make your backup images much smaller.
    The load sometimes goes over 21.00. If you're looking for a different
    metric I can try to get that as well.

    Obviously storage space was the reason we use compression here, and
    especially the reason we don't do the incremental merge scenario that
    Job mentioned. Incremental merge does an image copy of all data files
    for the base image. A normal uncompressed backup would at least skip
    whatever empty and/or unused blocks, if I recall correctly.

    I'd like to get a picture of the ideal backup-to-disk scenario with a
    goal of minimizing backup and especially restore/recovery. Should I
    have 4 partitions on physically separate spindles for the 4 parallel
    jobs? Suck it up and do the incremental merge (although I don't see
    that affecting restore/recovery time)? Just not do compression? With
    a recovery window of 7 days it might take up quite a bit more disk (is
    "disk is cheap" still the fashionable response?).
  • Greg Rahn at Nov 16, 2007 at 8:47 am
    What are the disk stats during the backups (from iostat)? Some things
    I would investigate:

    - are the target drives saturated?
    - are the I/O channels saturated?
    - do /rman and the db file luns share physical spindles? (hopefully not)

    I would suggest that you should have N luns where N is the number of
    parallel streams. This allows N I/O SCSI queues to be simultaneously
    serviced vs. one. Think of this like more check out lanes at the
    grocery store.

    Compression has the CPU overhead, but reduces the IO bandwidth and
    storage space requirement. If you are not I/O bound (or space) then
    it won't provide any visible performance gains - it will just consume
    more CPU. It's basically a trade off.

    Ultimately you want the target writes to be the bottleneck, but
    operating at optimal efficiency.
    On 11/15/07, Don Seiler wrote:
    Some quick answers to these questions before I hop into bed.
    --
    Regards,

    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Nov 16, 2007 at 4:10 pm
    See my answers inline below. Thanks again for all the help!
    On Nov 16, 2007 2:47 AM, Greg Rahn wrote:
    What are the disk stats during the backups (from iostat)? Some things
    I would investigate:
    - are the target drives saturated?
    - are the I/O channels saturated?
    I presume these things can only be known during backup times. Any
    special parameters to pass to iostat to get the specific information
    for these questions? Should I take a sample every hour during the
    level 0?
    - do /rman and the db file luns share physical spindles? (hopefully not)
    Right now they do actually. It is a temporary situation until we can
    move some more disk around in Jan/Feb 2008. Perhaps any real
    investigation is moot until we get it isolated?

    To Brandon: /rman is RAID 10.
    I would suggest that you should have N luns where N is the number of
    parallel streams. This allows N I/O SCSI queues to be simultaneously
    serviced vs. one. Think of this like more check out lanes at the
    grocery store.
    So this would be something like /rman1, /rman2, /rman3, /rman4 when I
    have parallelism=4? I presume I need to configure the individual
    channels then to each write to one of those locations. Perhaps I'm
    confusing terms.
    Compression has the CPU overhead, but reduces the IO bandwidth and
    storage space requirement. If you are not I/O bound (or space) then
    it won't provide any visible performance gains - it will just consume
    more CPU. It's basically a trade off.
    Just to confirm, last night's level 1 backup saw a load as high as
    22.43. I'll try to get I/O readings this weekend.
    Ultimately you want the target writes to be the bottleneck, but
    operating at optimal efficiency.
    I assumed as much, I just want to make sure that the bottleneck is as
    wide as can be.;)

    To Alex G:
    I'll try to get 10046 traces as well this weekend.
  • Don Seiler at Nov 16, 2007 at 10:22 pm
    Here's the "sar -u" output from Saturday night and Sunday morning of
    this past weekend when the level 0 database backup was running. I'm
    not sure if you're interested in the -d output, or if you'd rather see
    iostat output.

    root_at_foo:/var/log/sa # sar -u -f sa10 -s 22:30:00 -i 900
    Linux 2.6.9-55.0.6.ELsmp (foo.bar.com) 11/10/2007

    10:30:01 PM CPU %user %nice %system %iowait %idle
    10:45:01 PM all 29.56 0.00 0.90 0.16 69.37
    11:00:01 PM all 28.41 0.00 0.80 0.11 70.68
    11:15:01 PM all 29.75 0.00 0.89 0.11 69.25
    11:30:01 PM all 29.04 0.00 0.87 0.10 69.98
    11:45:01 PM all 31.25 0.00 0.95 0.10 67.71
    Average: all 29.60 0.00 0.88 0.12 69.40

    root_at_foo:/var/log/sa # sar -u -f sa11 -e 06:00:00 -i 900
    Linux 2.6.9-55.0.6.ELsmp (foo.bar.com) 11/11/2007

    12:00:01 AM CPU %user %nice %system %iowait %idle
    12:15:01 AM all 29.38 0.00 0.99 0.12 69.52
    12:30:01 AM all 29.57 0.00 0.99 0.24 69.20
    12:45:01 AM all 27.11 0.00 3.28 5.73 63.88
    01:00:01 AM all 33.61 0.00 3.82 5.01 57.55
    01:15:01 AM all 31.57 0.00 3.49 5.60 59.35
    01:30:01 AM all 27.54 0.00 2.50 4.16 65.80
    01:45:02 AM all 25.33 0.00 0.95 0.14 73.59
    02:00:01 AM all 24.30 0.00 0.91 0.12 74.67
    02:15:01 AM all 25.23 0.00 0.91 0.11 73.75
    02:30:01 AM all 25.19 0.00 0.94 0.13 73.74
    02:45:01 AM all 25.77 0.00 2.77 4.45 67.01
    03:00:01 AM all 26.14 0.00 3.17 5.82 64.87
    03:15:01 AM all 25.99 0.00 1.84 2.45 69.72
    03:30:01 AM all 25.67 0.00 0.97 0.13 73.23
    03:45:01 AM all 24.40 0.00 0.97 0.12 74.51
    04:00:01 AM all 25.76 0.00 0.97 0.13 73.14
    04:15:01 AM all 31.83 0.01 1.22 0.49 66.44
    04:30:01 AM all 27.24 0.00 1.70 0.24 70.82
    04:45:01 AM all 26.65 0.00 2.59 4.89 65.87
    05:00:01 AM all 27.05 0.00 3.14 5.93 63.88
    05:15:01 AM all 26.45 0.00 2.94 5.45 65.16
    05:30:01 AM all 25.99 0.00 1.05 0.13 72.83
    05:45:02 AM all 23.22 0.00 0.95 0.13 75.70
    Average: all 27.00 0.00 1.87 2.25 68.88
  • Mark Brinsmead at Nov 25, 2007 at 3:58 pm
    I may be joining this thread a little late, but oh well. Perhaps I can
    still add something to the discussion.

    Just to summarize Don's situation:

    Don is using RMAN to backup a database of about 860GB. The backups take
    more than 10 hours; less than 86GB/hr or 2.5 MB/s.

    The backup is written to disk in /rman, a Veritas filesystem.

    The RMAN backup uses 4 concurrent threads, with compression.

    Don is unsure of the underlying disk configuration (RAID-1 vs. RAID-5, how
    many spindles, etc.) but is reasonably sure that /rman shares physical
    spindles with the database.

    Don's "sar" statistics show that during the backup, the system is completely
    "busy", spending about 30% of its time in CPU, and 70% waiting on I/O.

    Okay, so it looks pretty clear that these backups are I/O bound. It is also
    highly likely from what we have been told that there is substantial I/O
    contention. There are four concurrent backup threads reading from and
    writing to the same set of disks. This might also be aggravated by the cost
    of software-based RAID-5, but we do not actually *know* whether this this
    the case.

    With 10g, RMAN compression can be either a blessing or a curse. In this
    case, where we are probably (badly) I/O bound, so the compression is *
    probably* beneficial. I think Don has done tests to confirm that, but I'm
    not certain I have seen that in this thread.

    Based on what we have seen, I would think that the very best (or at least, *
    first*) "optimization" we can apply here is to separate the back storage
    from the database storage, on separate sets of spindles. Do not use RAID-5
    for the /rman filesystem, except *maybe* with high end hardware-supported
    RAID-5 where sequential writes are recognised and optimised.

    Don already plans to re-arrange the /rman storage. This should be done
    sooner rather than later, I think.

    (Note: there are better reasons for rearranging this storage configuration
    than just performance. In the event of a storage failure, there is a
    significant risk of losing *both* the database *and* the backups. That
    would be a "bad thing (tm)".)

    While I/O contention remains the main limiting factor for backup
    performance, RMAN compression is probably going to be a net benefit; the
    few disk blocks *written* by the backup, the fewer the counter-productive
    disk seeks; this leads to less contention and faster throughput.

    There is, however, a second potential source of I/O contention -- the
    parallelism of the backup. In cases where backup parallelism is not well
    matched to the storage configuration, additional parallelism *harms*throughput.

    Don, have you tried your backups with *fewer* parallel threads? This could
    be a tough thing to balance, but you may find that at least until you
    separate the backup and database storage, the reduced I/O contention might
    actually allow you to do your backups faster...

    Back in the 90's, a typcial CPU could "gzip" data with a throughput of
    around 1.0 MB/s. Current CPUs can do much better. But your backup threads
    (unless I have botched my arithmetic) are averaging only somewhere around
    0.6 MB/s. Ignoring RMAN for the moment, how fast can you gzip a 1 GB file?
    Until your backups are achieving *at least* four times that rate, you can
    probably assume they are I/O bound.

    Anyway, these are a few thoughts on your situation; I hope they are not too
    random or disjointed. I hope even more that they are helpful. :-)

    I think someone earlier in this thread asked about methods to optimized
    disk-based backups. Aside from the observations offered above, I have only
    come across one *really* reliable way of doing this -- buy a tape drive!
    :-) There are very affordable tape drives out there that are capable of
    sustaining throughputs well in excess of 100MB/s. That's 360 GB/hr. In
    this particular situation, a $5000 tape drive *could *completely transform
    your backups. Your only challenge then will be to find a way to keep the
    tape drive "fed" -- it is common for tape-based backups to suffer
    performance-wise when data cannot be delivered as fast at the tape drive can
    take it.

    But that is a different discussion, perhaps for a different day...
    On Nov 16, 2007 3:22 PM, Don Seiler wrote:

    Here's the "sar -u" output from Saturday night and Sunday morning of
    this past weekend when the level 0 database backup was running. I'm
    not sure if you're interested in the -d output, or if you'd rather see
    iostat output.

    root_at_foo:/var/log/sa # sar -u -f sa10 -s 22:30:00 -i 900
    Linux 2.6.9-55.0.6.ELsmp (foo.bar.com) 11/10/2007

    10:30:01 PM CPU %user %nice %system %iowait %idle
    10:45:01 PM all 29.56 0.00 0.90 0.16 69.37
    11:00:01 PM all 28.41 0.00 0.80 0.11 70.68
    11:15:01 PM all 29.75 0.00 0.89 0.11 69.25
    11:30:01 PM all 29.04 0.00 0.87 0.10 69.98
    11:45:01 PM all 31.25 0.00 0.95 0.10 67.71
    Average: all 29.60 0.00 0.88 0.12 69.40

    root_at_foo:/var/log/sa # sar -u -f sa11 -e 06:00:00 -i 900
    Linux 2.6.9-55.0.6.ELsmp (foo.bar.com) 11/11/2007

    12:00:01 AM CPU %user %nice %system %iowait %idle
    12:15:01 AM all 29.38 0.00 0.99 0.12 69.52
    12:30:01 AM all 29.57 0.00 0.99 0.24 69.20
    12:45:01 AM all 27.11 0.00 3.28 5.73 63.88
    01:00:01 AM all 33.61 0.00 3.82 5.01 57.55
    01:15:01 AM all 31.57 0.00 3.49 5.60 59.35
    01:30:01 AM all 27.54 0.00 2.50 4.16 65.80
    01:45:02 AM all 25.33 0.00 0.95 0.14 73.59
    02:00:01 AM all 24.30 0.00 0.91 0.12 74.67
    02:15:01 AM all 25.23 0.00 0.91 0.11 73.75
    02:30:01 AM all 25.19 0.00 0.94 0.13 73.74
    02:45:01 AM all 25.77 0.00 2.77 4.45 67.01
    03:00:01 AM all 26.14 0.00 3.17 5.82 64.87
    03:15:01 AM all 25.99 0.00 1.84 2.45 69.72
    03:30:01 AM all 25.67 0.00 0.97 0.13 73.23
    03:45:01 AM all 24.40 0.00 0.97 0.12 74.51
    04:00:01 AM all 25.76 0.00 0.97 0.13 73.14
    04:15:01 AM all 31.83 0.01 1.22 0.49 66.44
    04:30:01 AM all 27.24 0.00 1.70 0.24 70.82
    04:45:01 AM all 26.65 0.00 2.59 4.89 65.87
    05:00:01 AM all 27.05 0.00 3.14 5.93 63.88
    05:15:01 AM all 26.45 0.00 2.94 5.45 65.16
    05:30:01 AM all 25.99 0.00 1.05 0.13 72.83
    05:45:02 AM all 23.22 0.00 0.95 0.13 75.70
    Average: all 27.00 0.00 1.87 2.25 68.88


    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Cheers,
    -- Mark Brinsmead
    Senior DBA,
    The Pythian Group
    http://www.pythian.com/blogs

    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Nov 26, 2007 at 10:49 pm
    Apologies in advance for the top-posted reply. Just to be clear,
    /rman and all other disks are RAID 10. Due to a lack of free disk, we
    had to move /rman onto the same spindles that hold our datafiles.
    This happened in September. The plan is to separate them again early

    in 2008, when we have more disk scheduled to be free. When this
    happens, should I be writing to four different paths (e.g. /rman01,
    /rman02, /rman03, /rman04)? As I've already admitted, I don't speak

    storage too fluently.

    If I might think out loud for a moment just to get the jumble out of my head:

    Each "channel" in RMAN is writing its own backupset piece, which
    (can) contain multiple datafiles.
    I want each channel to write to it's own disk (or LUN). 4 channels
    means, ideally, four disks.
    ** Showing my naivete: Should these disks be on their own spindles?
    Would doing otherwise completely defeat the purpose?
    RMAN parallelism refers to having multiple channels.

    Right now I have 4 channels, all writing to /rman. Each channel is
    writing compressed backupset pieces that contain 4 datafiles.

    Mark: our backups did take much less time. I attributed it (guessing
    at the time, of course) to the fact that RMAN also does quality checks
    on the data being written, as opposed to putting a tablespace in
    BACKUP mode and then gzipping the file to a new dir.

    Also one bit of sadder news. I performed an RMAN duplication the
    Friday before last that took over 25 hours. However I really don't
    want to even start any kind of diagnosis until we at least move RMAN
    storage to different disk.

    Don.
    On Nov 25, 2007 9:58 AM, Mark Brinsmead wrote:
    I may be joining this thread a little late, but oh well. Perhaps I can
    still add something to the discussion.

    Just to summarize Don's situation:

    -------------------------
    Don is using RMAN to backup a database of about 860GB. The backups take
    more than 10 hours; less than 86GB/hr or 2.5 MB/s.

    The backup is written to disk in /rman, a Veritas filesystem.

    The RMAN backup uses 4 concurrent threads, with compression.

    Don is unsure of the underlying disk configuration (RAID-1 vs. RAID-5, how
    many spindles, etc.) but is reasonably sure that /rman shares physical
    spindles with the database.

    Don's "sar" statistics show that during the backup, the system is completely
    "busy", spending about 30% of its time in CPU, and 70% waiting on I/O.
    --------------------------

    Okay, so it looks pretty clear that these backups are I/O bound. It is also
    highly likely from what we have been told that there is substantial I/O
    contention. There are four concurrent backup threads reading from and
    writing to the same set of disks. This might also be aggravated by the cost
    of software-based RAID-5, but we do not actually know whether this this the
    case.

    With 10g, RMAN compression can be either a blessing or a curse. In this
    case, where we are probably (badly) I/O bound, so the compression is
    probably beneficial. I think Don has done tests to confirm that, but I'm
    not certain I have seen that in this thread.

    Based on what we have seen, I would think that the very best (or at least,
    first) "optimization" we can apply here is to separate the back storage from
    the database storage, on separate sets of spindles. Do not use RAID-5 for
    the /rman filesystem, except maybe with high end hardware-supported RAID-5
    where sequential writes are recognised and optimised.

    Don already plans to re-arrange the /rman storage. This should be done
    sooner rather than later, I think.

    (Note: there are better reasons for rearranging this storage configuration
    than just performance. In the event of a storage failure, there is a
    significant risk of losing both the database and the backups. That would be
    a "bad thing (tm)".)

    While I/O contention remains the main limiting factor for backup
    performance, RMAN compression is probably going to be a net benefit; the
    few disk blocks written by the backup, the fewer the counter-productive disk
    seeks; this leads to less contention and faster throughput.

    There is, however, a second potential source of I/O contention -- the
    parallelism of the backup. In cases where backup parallelism is not well
    matched to the storage configuration, additional parallelism harms
    throughput.

    Don, have you tried your backups with fewer parallel threads? This could be
    a tough thing to balance, but you may find that at least until you separate
    the backup and database storage, the reduced I/O contention might actually
    allow you to do your backups faster...

    Back in the 90's, a typcial CPU could "gzip" data with a throughput of
    around 1.0 MB/s. Current CPUs can do much better. But your backup threads
    (unless I have botched my arithmetic) are averaging only somewhere around
    0.6 MB/s. Ignoring RMAN for the moment, how fast can you gzip a 1 GB file?
    Until your backups are achieving at least four times that rate, you can
    probably assume they are I/O bound.

    Anyway, these are a few thoughts on your situation; I hope they are not too
    random or disjointed. I hope even more that they are helpful. :-)

    I think someone earlier in this thread asked about methods to optimized
    disk-based backups. Aside from the observations offered above, I have only
    come across one really reliable way of doing this -- buy a tape drive! :-)
    There are very affordable tape drives out there that are capable of
    sustaining throughputs well in excess of 100MB/s. That's 360 GB/hr. In
    this particular situation, a $5000 tape drive could completely transform
    your backups. Your only challenge then will be to find a way to keep the
    tape drive "fed" -- it is common for tape-based backups to suffer
    performance-wise when data cannot be delivered as fast at the tape drive can
    take it.

    But that is a different discussion, perhaps for a different day...



    On Nov 16, 2007 3:22 PM, Don Seiler wrote:



    Here's the "sar -u" output from Saturday night and Sunday morning of
    this past weekend when the level 0 database backup was running. I'm
    not sure if you're interested in the -d output, or if you'd rather see
    iostat output.

    root_at_foo:/var/log/sa # sar -u -f sa10 -s 22:30:00 -i 900
    Linux 2.6.9-55.0.6.ELsmp (foo.bar.com) 11/10/2007

    10:30:01 PM CPU %user %nice %system %iowait %idle
    10:45:01 PM all 29.56 0.00 0.90 0.16 69.37
    11:00:01 PM all 28.41 0.00 0.80 0.11 70.68
    11:15:01 PM all 29.75 0.00 0.89 0.11 69.25
    11:30:01 PM all 29.04 0.00 0.87 0.10 69.98
    11:45:01 PM all 31.25 0.00 0.95 0.10 67.71
    Average: all 29.60 0.00 0.88 0.12 69.40

    root_at_foo:/var/log/sa # sar -u -f sa11 -e 06:00:00 -i 900
    Linux 2.6.9-55.0.6.ELsmp (foo.bar.com) 11/11/2007

    12:00:01 AM CPU %user %nice %system %iowait %idle
    12:15:01 AM all 29.38 0.00 0.99 0.12 69.52
    12:30:01 AM all 29.57 0.00 0.99 0.24 69.20
    12:45:01 AM all 27.11 0.00 3.28 5.73 63.88
    01:00:01 AM all 33.61 0.00 3.82 5.01 57.55
    01:15:01 AM all 31.57 0.00 3.49 5.60 59.35
    01:30:01 AM all 27.54 0.00 2.50 4.16 65.80
    01:45:02 AM all 25.33 0.00 0.95 0.14 73.59
    02:00:01 AM all 24.30 0.00 0.91 0.12 74.67
    02:15:01 AM all 25.23 0.00 0.91 0.11 73.75
    02:30:01 AM all 25.19 0.00 0.94 0.13 73.74
    02:45:01 AM all 25.77 0.00 2.77 4.45 67.01
    03:00:01 AM all 26.14 0.00 3.17 5.82 64.87
    03:15:01 AM all 25.99 0.00 1.84 2.45 69.72
    03:30:01 AM all 25.67 0.00 0.97 0.13 73.23
    03:45:01 AM all 24.40 0.00 0.97 0.12 74.51
    04:00:01 AM all 25.76 0.00 0.97 0.13 73.14
    04:15:01 AM all 31.83 0.01 1.22 0.49 66.44
    04:30:01 AM all 27.24 0.00 1.70 0.24 70.82
    04:45:01 AM all 26.65 0.00 2.59 4.89 65.87
    05:00:01 AM all 27.05 0.00 3.14 5.93 63.88
    05:15:01 AM all 26.45 0.00 2.94 5.45 65.16
    05:30:01 AM all 25.99 0.00 1.05 0.13 72.83
    05:45:02 AM all 23.22 0.00 0.95 0.13 75.70
    Average: all 27.00 0.00 1.87 2.25 68.88






    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --




    http://www.freelists.org/webpage/oracle-l



    --
    Cheers,
    -- Mark Brinsmead
    Senior DBA,
    The Pythian Group


    http://www.pythian.com/blogs
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Dec 4, 2007 at 3:19 pm

    On Nov 26, 2007 4:49 PM, Don Seiler wrote:
    Also one bit of sadder news. I performed an RMAN duplication the
    Friday before last that took over 25 hours. However I really don't
    want to even start any kind of diagnosis until we at least move RMAN
    storage to different disk.
    Well I'm in the midst of another insanely-long duplication. The /rman
    on the auxiliary server is indeed on the same spindles as the rest of
    the datafiles, but I thought I'd ask anyway.

    The recovery process is going through the "cataloged datafile copy"
    for each datafile in the instance. Messages like this:

    cataloged datafile copy
    datafile copy filename=/u10/oradata/stage/txbill_idx13.dbf recid=210
    stamp=640430010

    However, each of these take 3-4 minutes, judging by the duplication
    log updates. When I have hundreds of datafiles, 3-4 minutes each is
    an incredibly long time. Nothing is being written to the aux instance
    alert log during this period.

    It seems that since we moved production to 64-bit hardware, this
    duplications went from 9-10 hours (which I thought was way too long)
    to 25-26 hours (which is a horrible joke). I'm going to pour over
    some of the online guides and Robert Freeman's 10g RMAN book. If
    anyone has any tips or cluebats, swing away.
  • Don Seiler at Dec 10, 2007 at 3:42 pm
    Just a bit of follow-up. We have been able to move our /rman LUN to
    it's own spindles in production. Saturday night's level 0
    database+archivelog backup finished in roughtly 3.75 hours. This was
    using the same configuration as I posted in the original post, so 4
    channels. I'll cut this down to 2 for next Saturday and see how it
    times out then.

    We're working to make similar changes on our development servers in
    the hopes that duplications will finish in a somewhat reasonable time
    (e.g. far less than 2 days).
  • Crisler, Jon at Dec 10, 2007 at 4:16 pm
    I have a need to host a NFS server, and my internal customer wants to
    use their AIX 10g RAC cluster for this. Currently this cluster uses ASM
    to SAN for db storage, and some SAN storage connected as local
    filesystem (JFS2) drives.

    I am not NFS savvy at all. Is there a way to configure the RAC servers
    to host the NFS mounts, and have them failover (automatic or manual) to
    the other server? We are trying to make the NFS hosting as robust as
    possible. We also do not have GPFS (IBM's clustering file system). If
    this was Linux, we could possibly use OCFS2 and mount that, but as far
    as sharing via NFS and failover I don't know if that would work.

    Any suggestions ?
  • Krish.hariharan_at_quasardb.com at Dec 10, 2007 at 7:37 pm
    Jon,

    From discussions with a Unix architect, I understood that read-write nfs
    has some holes that our security teams did not like. We however use read
    only nfs routinely in many environments. The issues were:
    1. Security did not like us using nfs, especially read-write
    2. In our Solaris environments, in some older OS releases, stale nfs
    mounts were problematic.

    A question though: Is there a reason why you wouldn't have the nfs mounts
    on all nodes of the RAC and perhaps control access to that mount point
    through, say the services framework as opposed to failing the mount point
    to different nodes?

    -Krish
  • Pedro Espinoza at Dec 11, 2007 at 2:17 am
    There is a problem with nfs server on cluster, unless the filesystem
    is a clustered file system--like gnbd+gfs on linux.

    Think of this scenario: you export /mnt/fs1 from serverA. In the event
    of failover, you want to export that file system from Server B. To do
    this, server B should be connected to the same storage; in the event
    of failover, one should import dg, then start the volume, then mount
    that /dev/vx/dsk on /mnt/fs1, then export that file system. In the
    process, you need to up a virtual interface with serverA on serverB.
    These are the quirks you need to work out.
    On Dec 10, 2007 11:16 AM, Crisler, Jon wrote:
    I have a need to host a NFS server, and my internal customer wants to
    use their AIX 10g RAC cluster for this. Currently this cluster uses ASM
    to SAN for db storage, and some SAN storage connected as local
    filesystem (JFS2) drives.

    I am not NFS savvy at all. Is there a way to configure the RAC servers
    to host the NFS mounts, and have them failover (automatic or manual) to
    the other server? We are trying to make the NFS hosting as robust as
    possible. We also do not have GPFS (IBM's clustering file system). If
    this was Linux, we could possibly use OCFS2 and mount that, but as far
    as sharing via NFS and failover I don't know if that would work.

    Any suggestions ?
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Dec 28, 2007 at 3:30 pm
    I'm getting bit HARD by this one again. This time I'm using RMAN
    duplication to restore from production onto development, but this time
    using ASM storage. We've been at the "cataloged datafile copy" stage
    of things for quite some time. If the RMAN log is any indicator, some
    datafiles take over 30 minutes to catalog, some take 5.

    Back on our 32-bit hardware, this didn't take anywhere near this long.
    Can anyone shed some light?

    I'm trying to get detailed specifics on the lay of the storage
    landscape beneath ASM. As I've indicated in another thread, we're
    currently just using two 933gb LUNs for this disk group. These LUNs
    are carved from the SAN and composed of a number of disks under RAID
    10. I'm waiting for the admin to give me info like individual disk
    size and stripe size. IIRC, each RAID group is composed of 3 data and
    3 parity disks, the LUNs are then striped across those, and then ASM
    is striped across the (two) LUNs in the normal ASM disk group fashion.

    We are using external redundancy in the ASM disk group.

    I'll write back when I get more storage specifics.

    Don.
    cataloged datafile copy
    datafile copy filename=/u10/oradata/stage/txbill_idx13.dbf recid=210
    stamp=640430010

    However, each of these take 3-4 minutes, judging by the duplication
    log updates. When I have hundreds of datafiles, 3-4 minutes each is
    an incredibly long time. Nothing is being written to the aux instance
    alert log during this period.

    It seems that since we moved production to 64-bit hardware, this
    duplications went from 9-10 hours (which I thought was way too long)
    to 25-26 hours (which is a horrible joke). I'm going to pour over
    some of the online guides and Robert Freeman's 10g RMAN book. If
    anyone has any tips or cluebats, swing away.
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Dec 28, 2007 at 8:54 pm

    On Dec 28, 2007 9:30 AM, Don Seiler wrote:
    I'm trying to get detailed specifics on the lay of the storage
    landscape beneath ASM. As I've indicated in another thread, we're
    currently just using two 933gb LUNs for this disk group. These LUNs
    are carved from the SAN and composed of a number of disks under RAID
    10. I'm waiting for the admin to give me info like individual disk
    size and stripe size. IIRC, each RAID group is composed of 3 data and
    3 parity disks, the LUNs are then striped across those, and then ASM
    is striped across the (two) LUNs in the normal ASM disk group fashion.

    We are using external redundancy in the ASM disk group.

    I'll write back when I get more storage specifics.
    OK I was able to get these specifics about our RMAN and ASM LUNs:

    ASM disks: both are 14 disks, RAID 10, 7D+7P
    RMAN: 12 disks, RAID 10, 6D+6P
    Disks are each 73G
    Stripe size is 64k

    The D+P means data and parity for mirroring.

    Re: ASM. It seems to me like we're shooting ourselves in the foot if
    we give ASM two big striped-and-mirrored LUNs rather than at least
    letting ASM handle the striping across 7 disks rather than 2. I'd
    like to know what people's thoughts who've had more experience with
    such things.

    Although I digress from the immediate problem of the RMAN post-restore
    catalog task taking forever.

    Don.
  • Don Seiler at Feb 11, 2008 at 9:55 pm
    Just wanted to follow-up here as well. I finally decided to file an
    SR, and Oracle came back pretty quickly with Note 462879.1 [0], fixed
    in 10.2.0.4 and 11g. It seems that, in this bug, queries against
    v$rman_status table can take an extremely long time, and that table is
    queried for every datafile switch during RMAN DUPLICATEs

    Workarounds are to either set sesiion-level optimization to RULE
    based, or to gather fixed object statistics via
    dbms_stats.gather_fixed_objects_stats();.

    I've done the latter, but haven't had an opportunity to perform
    another restore as our development instances are needed until the next
    release in two weeks. I'll report back with results when I do.

    [0] https://metalink.oracle.com/metalink/plsql/f?p=130:14:6987901499320050620::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,462879.1,1,1,1,helvetica
  • Taylor, Chris David at Feb 11, 2008 at 11:09 pm
    You mean you weren't using dbms_stats.gather_fixed_objects_stats()??

    I thought EVERYONE was using that by now...

    KIDDING!! Only kidding!

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Don Seiler
    Sent: Monday, February 11, 2008 3:55 PM
    To: oracle-l
    Subject: Re: Tuning RMAN backup and recovery

    Just wanted to follow-up here as well. I finally decided to file an
    SR, and Oracle came back pretty quickly with Note 462879.1 [0], fixed
    in 10.2.0.4 and 11g. It seems that, in this bug, queries against
    v$rman_status table can take an extremely long time, and that table is
    queried for every datafile switch during RMAN DUPLICATEs

    Workarounds are to either set sesiion-level optimization to RULE
    based, or to gather fixed object statistics via
    dbms_stats.gather_fixed_objects_stats();.

    I've done the latter, but haven't had an opportunity to perform
    another restore as our development instances are needed until the next
    release in two weeks. I'll report back with results when I do.

    [0]
    https://metalink.oracle.com/metalink/plsql/f?p=130:14:698790149932005062
    0::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_f
    rame,p14_font:NOT,462879.1,1,1,1,helvetica

    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Mar 3, 2008 at 3:39 pm
    I did have some follow-up questions regarding
    DBMS_STATS.GATHER_FIXED_OBJECT_STATS().

    We had noticed that, since gathering the fixed object stats, the
    time needed to perform a level 0 incremental backup dropped by around
    40%. Is this not a coincidence?

    Is there any schedule or milestone upon which one should re-gather
    fixed object statistics?

    Don.
    On Mon, Feb 11, 2008 at 3:55 PM, Don Seiler wrote:
    Just wanted to follow-up here as well. I finally decided to file an
    SR, and Oracle came back pretty quickly with Note 462879.1 [0], fixed
    in 10.2.0.4 and 11g. It seems that, in this bug, queries against
    v$rman_status table can take an extremely long time, and that table is
    queried for every datafile switch during RMAN DUPLICATEs

    Workarounds are to either set sesiion-level optimization to RULE
    based, or to gather fixed object statistics via
    dbms_stats.gather_fixed_objects_stats();.

    I've done the latter, but haven't had an opportunity to perform
    another restore as our development instances are needed until the next
    release in two weeks. I'll report back with results when I do.

    [0] https://metalink.oracle.com/metalink/plsql/f?p=130:14:6987901499320050620::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,462879.1,1,1,1,helvetica


    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Mar 3, 2008 at 5:21 pm
    I've read a couple blog posts that say to gather fixed object stats
    weekly, during normal business hours. Another page [0] also suggested
    gathering dictionary stats as part of the same weekly process.

    Anyone have any thoughts?

    Don.

    [0] http://www.toadworld.com/Community/ExpertsBlog/tabid/67/EntryID/135/Default.aspx
    On Mon, Mar 3, 2008 at 9:39 AM, Don Seiler wrote:
    I did have some follow-up questions regarding
    DBMS_STATS.GATHER_FIXED_OBJECT_STATS().
    1. We had noticed that, since gathering the fixed object stats, the
    time needed to perform a level 0 incremental backup dropped by around
    40%. Is this not a coincidence?

    2. Is there any schedule or milestone upon which one should re-gather
    fixed object statistics?
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Mar 3, 2008 at 7:05 pm

    On 3/3/08, Don Seiler wrote:
    I've read a couple blog posts that say to gather fixed object stats
    weekly, during normal business hours. Another page [0] also suggested
    gathering dictionary stats as part of the same weekly process.

    Anyone have any thoughts?

    Don.

    [0] http://www.toadworld.com/Community/ExpertsBlog/tabid/67/EntryID/135/Default.aspx

    On Mon, Mar 3, 2008 at 9:39 AM, Don Seiler wrote:
    I did have some follow-up questions regarding
    DBMS_STATS.GATHER_FIXED_OBJECT_STATS().
    1. We had noticed that, since gathering the fixed object stats, the
    time needed to perform a level 0 incremental backup dropped by around
    40%. Is this not a coincidence?

    2. Is there any schedule or milestone upon which one should re-gather
    fixed object statistics?
    I would recommend that you check out
    Upgrading from Oracle Database 9i to 10g: What to expect from the
    Optimizer
    http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimizer_10gr2_0208.pdf
    This is discussed on page 7 & 8 (Statistics on Dictionary Tables &
    Statistics on Fixed Objects)
  • Don Seiler at Mar 4, 2008 at 2:32 pm
    Taking away the following juicy nuggest:

    "Statistics on the dictionary tables will be maintained via the
    automatic statistics
    gathering job run during the nightly maintance window."

    "You only need to gather fixed objects statistics once for a
    representative workload and they are not updated by the automatic statistics
    gathering job."

    Answers both my questions.

    Thanks,
    Don.
    On Mon, Mar 3, 2008 at 1:05 PM, Greg Rahn wrote:
    On 3/3/08, Don Seiler wrote:
    I've read a couple blog posts that say to gather fixed object stats
    weekly, during normal business hours. Another page [0] also suggested
    gathering dictionary stats as part of the same weekly process.

    Anyone have any thoughts?

    Don.

    [0] http://www.toadworld.com/Community/ExpertsBlog/tabid/67/EntryID/135/Default.aspx

    On Mon, Mar 3, 2008 at 9:39 AM, Don Seiler wrote:
    I did have some follow-up questions regarding
    DBMS_STATS.GATHER_FIXED_OBJECT_STATS().
    1. We had noticed that, since gathering the fixed object stats, the
    time needed to perform a level 0 incremental backup dropped by around
    40%. Is this not a coincidence?

    2. Is there any schedule or milestone upon which one should re-gather
    fixed object statistics?
    I would recommend that you check out
    Upgrading from Oracle Database 9i to 10g: What to expect from the
    Optimizer
    http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimizer_10gr2_0208.pdf
    This is discussed on page 7 & 8 (Statistics on Dictionary Tables &
    Statistics on Fixed Objects)



    --
    Regards,

    Greg Rahn
    http://structureddata.org
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l
  • Limin Guo at Nov 16, 2007 at 2:09 pm
    Don,

    I guess that it is a known issue of compression performance with RMAN
    in 10.1 and 10.2. That is probably why Oracle 11g has a new
    compression algorithm, with which the performance of RMAN compression
    is noticeably faster than in previous releases.

    I did a benchmark testing last year in both 10.1 and 10.2 databases in
    a Solaris box with 16 processors. When compression is enabled (no
    parallelism), RMAN backup takes about 5 times longer than the one
    without compression, and the backupsets size is about 5 times smaller.
    That was the reason I thought the compression option in RMAN in 10.1
    and 10.2 is not practical, especially for restore and recovery.

    Hope this helps,

    Limin.
    On Nov 16, 2007 1:27 AM, Don Seiler wrote:
    Some quick answers to these questions before I hop into bed.
    1. What version is this..? Are you using a block
    tracking file for your incrementals? If not you may be
    backing up way more information than you need too.
    Sorry for not mentioning this straight away. This is Oracle EE
    10.2.0.2 on RHEL4. Processing is 4 dual-core 64-bit CPUs. Both RDBMS
    and OS are 64-bit.

    Yes we use BCT for incrementals. Our level 1 incrementals take
    anywhere from 30 to 90 minutes. Our schedule is to do a level 0 on
    Saturday evening, and level 1 on all other evenings during the week.
    2. Are you doing your parallel backups to the same
    disk? You could be running into disk contention if so.
    Yes we are (to /rman), and this is my first hunch. Shamefully, I'm
    not well-versed by any means with the storage side of database
    operations. If this Veritas partition were striped across multiple
    disks, does that mean anything in terms of being able to support
    parallel writes? Or should I only think of it as 1 and not try to
    play games?
    3. What is your CPU usage during the backup. As
    mentioned earlier, if you are CPU constrained then
    compression can slow things down. If you are not CPU
    constrained then compression can very much speed
    things up and make your backup images much smaller.
    The load sometimes goes over 21.00. If you're looking for a different
    metric I can try to get that as well.

    Obviously storage space was the reason we use compression here, and
    especially the reason we don't do the incremental merge scenario that
    Job mentioned. Incremental merge does an image copy of all data files
    for the base image. A normal uncompressed backup would at least skip
    whatever empty and/or unused blocks, if I recall correctly.

    I'd like to get a picture of the ideal backup-to-disk scenario with a
    goal of minimizing backup and especially restore/recovery. Should I
    have 4 partitions on physically separate spindles for the 4 parallel
    jobs? Suck it up and do the incremental merge (although I don't see
    that affecting restore/recovery time)? Just not do compression? With
    a recovery window of 7 days it might take up quite a bit more disk (is
    "disk is cheap" still the fashionable response?).


    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Regards,

    Limin Guo.
    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Nov 16, 2007 at 3:36 pm
    That has not been my finding with 10.2.0.2 on AIX 5.3.

    My backups to Netbackup (disk staging pool, not straight to tape) are
    actually *faster* with compression on. I'm backing up about 250GB worth
    of datafiles and without compression, the backup is only slightly
    smaller than the database itself and runs in about 4.5 hours, but with
    compression the backup only 36GB and completes in only 2.5 hours. I've
    seen this result consistently for a few months now after switching back
    and forth between compressed and non-compressed. I still have to do
    more testing to do on the restore side, but so far it looks like
    restores of the compressed backupsets may take signifcantly longer than
    restores of the non-compressed backupsets - maybe twice as long.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Limin Guo

    I guess that it is a known issue of compression performance with RMAN in
    10.1 and 10.2.

    When compression is enabled (no parallelism), RMAN backup takes about 5
    times longer

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • Limin Guo at Nov 16, 2007 at 10:43 pm
    Brandon and Robert:

    Very interesting to know that it actually save time for you to use
    RMAN compression than not use it.

    Do you use different CONFIGURE when using compression? like channels,
    parallelization, etc.

    I have just run another test in a Solaris test box (Solaris 8 with 4
    processors, Oracle 10.2.0.3). The results are consistent with my last
    post. The database is 15g, the regular backupsets are 14g and
    compressed backupsets are 2.4g. The backup time with compression on
    takes over twice as long.

    The backupsets are written to a RAID5 volume with ADA disks in
    Clariion storage. All Oracle datafiles sit on RAID5 volume with
    fiber-channel disks in the same storage, but different tray. So there
    should be no competing I/O between them, I hope.

    The backup script performs a level 0 backup and then restore the
    backupsets for validating purpose. Here are the time results:

    without compression with compression
    backup 13 min 33min
    restore validate 13 min 26min

    During both tests, I monitored machine with sar (interval of 5
    seconds), there are no noticeable differences between compressed one
    and non-compressed one. On average:

    user 25 - 40%
    sys single digit
    wio 10 - 25%

    If I am CPU bounced, I should see high user% during compressed rman
    backup, shouldn't I? but I did not. however I do see little high user%
    in restore validate process in non-compressed mode. If I am I/O
    bounced, I should see the backup without compression takes longer
    time, am I right?

    The rman commands used for the testing:

    set $ENABLE_ENCRYPTION;
    crosscheck archivelog all;
    delete noprompt obsolete;
    backup $AS_COMPRESSED $BKUP_LEVEL database;
    delete noprompt obsolete;
    set $ENABLE_DECRYPTION;
    restore validate database;
    restore validate spfile to 'restore_spfile.ora';
    restore validate controlfile to 'restore_control01.ctl';
    report unrecoverable;

    All variables are substituted during run time. BKUP_LEVEL is 0.

    The CONFIGURE in the database.

    RMAN configuration parameters are:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

    CONFIGURE BACKUP OPTIMIZATION ON;

    CONFIGURE DEFAULT DEVICE TYPE TO DISK;

    CONFIGURE CONTROLFILE AUTOBACKUP ON;

    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

    CONFIGURE MAXSETSIZE TO UNLIMITED;

    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO

    '/vw01/app/oracle/product/10.2.0/Db_1/dbs/snapcf_NSTPS.f'; # default

    The hardware information:

    System Configuration: Sun Microsystems sun4u Sun Fire V440
    System clock frequency: 183 MHZ
    Memory size: 20GB

    CPUs ====================================
    E$ CPU CPU Temperature Fan
    CPU Freq Size Impl. Mask Die Ambient Speed Unit

    -------- ---------- ------ ---- -------- -------- ----- ----
    0 1281 MHz 1MB US-IIIi 2.4 - -
    1 1281 MHz 1MB US-IIIi 2.4 - -
    2 1281 MHz 1MB US-IIIi 2.4 - -
    3 1281 MHz 1MB US-IIIi 2.4 - -

    IO Devices =================================

    I am going to dig into it more and see why I don't see what you guys have seen.

    Thank you for sharing your information.

    Limin.
    On Nov 16, 2007 10:36 AM, Allen, Brandon wrote:
    That has not been my finding with 10.2.0.2 on AIX 5.3.

    My backups to Netbackup (disk staging pool, not straight to tape) are
    actually *faster* with compression on. I'm backing up about 250GB worth
    of datafiles and without compression, the backup is only slightly
    smaller than the database itself and runs in about 4.5 hours, but with
    compression the backup only 36GB and completes in only 2.5 hours. I've
    seen this result consistently for a few months now after switching back
    and forth between compressed and non-compressed. I still have to do
    more testing to do on the restore side, but so far it looks like
    restores of the compressed backupsets may take signifcantly longer than
    restores of the non-compressed backupsets - maybe twice as long.


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Limin Guo


    I guess that it is a known issue of compression performance with RMAN in
    10.1 and 10.2.

    When compression is enabled (no parallelism), RMAN backup takes about 5
    times longer

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
    --
    Regards,

    Limin Guo.
    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Nov 16, 2007 at 11:01 pm
    I set out to specifically to test compression performance a few months
    ago so I changed absolutely nothing other than adding "AS COMPRESSED
    BACKUPSET" to my BACKUP DATABASE command. I'm backing up to sbt_tape
    via Netbackup 6.0, but I believe the backups actually go to a disk
    staging pool rather than directly to a tape drive. I've only tested
    compression on this one system, so I don't have anything else to compare
    it to and I'm not sure what would explain why we're seeing different
    results. It seems to me that the most likely explanation is that my
    bottleneck is the speed of writes to sbt_tape and those are drastically
    reduced by the compression so that's where I save the time, whereas you
    probably are not bottlenecked by the speed of your backup writes to disk
    so you experience an increase in time due to the extra CPU overhead.
    Here are my rman configurations:

    RMAN> show all;

    RMAN configuration parameters are:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
    CONFIGURE BACKUP OPTIMIZATION ON;

    CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';

    CONFIGURE CONTROLFILE AUTOBACKUP ON;

    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO

    '%F'; # default
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; #

    default
    CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 3 BACKUP TYPE TO BACKUPSET;

    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; #

    default
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; #

    default
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; #

    default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' MAXOPENFILES 1 PARMS

    'ENV=(NB_ORA_SCHED=User)';
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO

    '/opt/oracle/product/10.2/db_1/dbs/snapcf_baandev.f'; # default

    -----Original Message-----
    From: Limin Guo

    Brandon and Robert:

    Very interesting to know that it actually save time for you to use RMAN
    compression than not use it.

    Do you use different CONFIGURE when using compression? like channels,
    parallelization, etc.

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Nov 16, 2007 at 3:56 pm
    Your Sun tests are in direct opposition to my own real life production
    experiences. Of course, many things might impact such testing, but in *many*
    cases I've found compression in 10g RMAN to make for much faster backups.
    Not always, but often.

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    BLOG: http://robertgfreeman.blogspot.com/
    Sig V1.2

    -----Original Message-----
    From: Limin Guo
    Sent: Friday, November 16, 2007 7:10 AM
    To: don_at_seiler.us
    Cc: Robert Freeman; Oracle-L Freelists
    Subject: Re: Tuning RMAN backup and recovery

    Don,

    I guess that it is a known issue of compression performance with RMAN
    in 10.1 and 10.2. That is probably why Oracle 11g has a new
    compression algorithm, with which the performance of RMAN compression
    is noticeably faster than in previous releases.

    I did a benchmark testing last year in both 10.1 and 10.2 databases in
    a Solaris box with 16 processors. When compression is enabled (no
    parallelism), RMAN backup takes about 5 times longer than the one
    without compression, and the backupsets size is about 5 times smaller.
    That was the reason I thought the compression option in RMAN in 10.1
    and 10.2 is not practical, especially for restore and recovery.

    Hope this helps,

    Limin.
    On Nov 16, 2007 1:27 AM, Don Seiler wrote:
    Some quick answers to these questions before I hop into bed.
    1. What version is this..? Are you using a block
    tracking file for your incrementals? If not you may be
    backing up way more information than you need too.
    Sorry for not mentioning this straight away. This is Oracle EE
    10.2.0.2 on RHEL4. Processing is 4 dual-core 64-bit CPUs. Both RDBMS
    and OS are 64-bit.

    Yes we use BCT for incrementals. Our level 1 incrementals take
    anywhere from 30 to 90 minutes. Our schedule is to do a level 0 on
    Saturday evening, and level 1 on all other evenings during the week.
    2. Are you doing your parallel backups to the same
    disk? You could be running into disk contention if so.
    Yes we are (to /rman), and this is my first hunch. Shamefully, I'm
    not well-versed by any means with the storage side of database
    operations. If this Veritas partition were striped across multiple
    disks, does that mean anything in terms of being able to support
    parallel writes? Or should I only think of it as 1 and not try to
    play games?
    3. What is your CPU usage during the backup. As
    mentioned earlier, if you are CPU constrained then
    compression can slow things down. If you are not CPU
    constrained then compression can very much speed
    things up and make your backup images much smaller.
    The load sometimes goes over 21.00. If you're looking for a different
    metric I can try to get that as well.

    Obviously storage space was the reason we use compression here, and
    especially the reason we don't do the incremental merge scenario that
    Job mentioned. Incremental merge does an image copy of all data files
    for the base image. A normal uncompressed backup would at least skip
    whatever empty and/or unused blocks, if I recall correctly.

    I'd like to get a picture of the ideal backup-to-disk scenario with a
    goal of minimizing backup and especially restore/recovery. Should I
    have 4 partitions on physically separate spindles for the 4 parallel
    jobs? Suck it up and do the incremental merge (although I don't see
    that affecting restore/recovery time)? Just not do compression? With
    a recovery window of 7 days it might take up quite a bit more disk (is
    "disk is cheap" still the fashionable response?).


    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Regards,

    Limin Guo.

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Nov 16, 2007 at 5:37 pm

    On Nov 15, 2007 2:53 PM, Don Seiler wrote:
    I've read the tahiti doc of (nearly) the same name [0], but it really
    didn't do too much. I'm pretty sure I've posted about this but it's a
    high priority for me now.
    Aside from compression, there are other things to consider.

    Blocksize - this is used to set the buffer size in allocate channel
    maxfilesopen - going to disk the default (16 IIRC) is too high. Scale
    it down and test.
    tape_io_slaves - the name may not be right, no db access at the
    moment. using tape IO slaves will cause the buffers to come from PGA
    rather than shared_pool (working from memory here, I believe that is
    accurate)
    tape_async_io - yes, tapes are synchronous, but async is supported
    (see the docs)

    Hotsos presentation coming up! :)

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Nov 16, 2007 at 8:20 pm
    If anyone lives in the Ontario (Canada) area I'd like
    to ask you a question offline please.

    Robert

    Robert G. Freeman
    Author:
    Now Available for Pre-Sales on Amazon.com!!!!

    Oracle Database 11g New Features (Oracle Press)

    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)
  • Allen, Brandon at Nov 16, 2007 at 8:21 pm
    If you're talking about blksize, it has been deprecated in 10.1+ :

    http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/obs_comm.
    htm#RCMRF910

    But it is still recommended in this best practices guide (p.10):

    http://www.oracle.com/technology/deploy/availability/pdf/S942_Chien.doc.
    pdf

    So, I'm not sure if it's still a good idea or not. Have you actually
    tested with it in 10g to see if it works?

    Here's another white paper on best practices for 10g specifically with
    Netbackup, from HP, that also suggests using BLKSIZE. I haven't read it
    yet, but looks interesting:

    http://h71028.www7.hp.com/ERC/downloads/4AA0-8102ENW.pdf

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still

    Blocksize - this is used to set the buffer size in allocate channel
    maxfilesopen - going to disk the default (16 IIRC) is too high. Scale it
    down and test.

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Nov 16, 2007 at 9:26 pm
    Blksize: still in 10gR2 docs, though for tape only.

    I'll check the HP paper later - no access right now.

    (Via blackberry)
    On 11/16/07, Allen, Brandon wrote:
    If you're talking about blksize, it has been deprecated in 10.1+ :

    http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/obs_comm.
    htm#RCMRF910

    But it is still recommended in this best practices guide (p.10):

    http://www.oracle.com/technology/deploy/availability/pdf/S942_Chien.doc.
    pdf

    So, I'm not sure if it's still a good idea or not. Have you actually
    tested with it in 10g to see if it works?

    Here's another white paper on best practices for 10g specifically with
    Netbackup, from HP, that also suggests using BLKSIZE. I haven't read it
    yet, but looks interesting:

    http://h71028.www7.hp.com/ERC/downloads/4AA0-8102ENW.pdf


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Jared Still


    Blocksize - this is used to set the buffer size in allocate channel
    maxfilesopen - going to disk the default (16 IIRC) is too high. Scale it
    down and test.

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do not
    consent to Internet email for messages of this kind. Opinions, conclusions
    and other information in this message that do not relate to the official
    business of this company shall be understood as neither given nor endorsed
    by it.
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Nov 16, 2007 at 9:32 pm
    You're right, I found it here:

    http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtunin0
    01.htm#sthref1065

    So it looks like BLKSIZE is still supported with the ALLOCATE and SEND
    commands, just not as part of the CONFIGURE CHANNEL command.

    Thanks,
    Brandon

    -----Original Message-----
    From: Jared Still

    Blksize: still in 10gR2 docs, though for tape only.
    On 11/16/07, Allen, Brandon wrote:
    If you're talking about blksize, it has been deprecated in 10.1+ :
    http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/obs_comm.
    htm#RCMRF910
    >

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Jared Still at Nov 20, 2007 at 6:50 am

    On 11/16/07, Allen, Brandon wrote:
    You're right, I found it here:

    http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtunin0
    01.htm#sthref1065

    So it looks like BLKSIZE is still supported with the ALLOCATE and SEND
    commands, just not as part of the CONFIGURE CHANNEL command.
    The fact that BLKSIZE is still supported with ALLOCATE and SEND, but not
    CONFIGURE CHANNEL seems rather strange.

    Though I have not yet tested it, I am guessing (sorry Alex :) that using SEND
    as the first command in a RUN block would work if automatic channels
    are configured. I'll test it later.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Alex Gorbachev at Nov 26, 2007 at 3:09 pm
    See inline...
    On Nov 20, 2007 5:50 PM, Jared Still wrote:
    ... I am guessing (sorry Alex :) ...
    No excuses...;-) unless the following is done:
    ... I'll test it later.
    You asked for it! We are waiting for results.;-)

    Cheers,
    Alex

    --
    Alex Gorbachev, Oracle DBA Brewer, The Pythian Group
    http://www.pythian.com/blogs/author/alex http://www.oracloid.com
    BAAG party - www.BattleAgainstAnyGuess.com
    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Nov 16, 2007 at 9:23 pm
    Correction:
    Tape IO slaves: set large pool, otherwise buffers come from shared pool.

    No IO slaves: buffers in PGA

    (Via blackberry)
    On 11/16/07, Jared Still wrote:
    On Nov 15, 2007 2:53 PM, Don Seiler wrote:
    I've read the tahiti doc of (nearly) the same name [0], but it really
    didn't do too much. I'm pretty sure I've posted about this but it's a
    high priority for me now.
    Aside from compression, there are other things to consider.

    Blocksize - this is used to set the buffer size in allocate channel
    maxfilesopen - going to disk the default (16 IIRC) is too high. Scale
    it down and test.
    tape_io_slaves - the name may not be right, no db access at the
    moment. using tape IO slaves will cause the buffers to come from PGA
    rather than shared_pool (working from memory here, I believe that is
    accurate)
    tape_async_io - yes, tapes are synchronous, but async is supported
    (see the docs)

    Hotsos presentation coming up! :)



    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Mark Brinsmead at Nov 29, 2007 at 1:28 am
    Krish,

    You are quite correct. I misread the 'sar' output. I went back and
    looked again at the "original". My mail reader had rendered it in a
    variable width font, misaligning the columns. Had I read more carefully, I
    would have seen that the systems do, indeed report 70% idle, not 70% wio.

    Sorry all. But then, I stated my assumptions for a reason...

    Anyway, since this was the primary basis for my prior comments, I am
    going to have to withdraw them. While not necessarily *wrong*, there is
    little reason to say that they are right either. :-(

    Don did post a follow-up reporting that something (compressed backups, I
    think, although it did not seem clear) had increased his backup throughput.
    With 10g, RMAN compressed backups are quite CPU intensive. That enabling
    compression has *improved* throughput does suggest that either I/O bandwidth
    (e.g., backups across a slow network or written to a slow device) or I/O
    contention *could be* a major contributor to backup time. On the other
    hand, your (Krish's) observation there there are probably unused CPU cycles
    available can also explain the same observations.

    Don, to better understand the issue of I/O contention, remember how a
    disk works. It has a spinning platter, and a (one!) moving arm. (Yes,
    multi-actuator disk drives exist. But I haven't actually seen one for
    decades; they are too costly to manufacture and most IT managers are so
    fixated on metrics like $/GB that foolish matters like "throughput" and
    response time are irrelevant. At least until well after the purchasing
    decision has been made.)

    To read a given block of data, the disk drive need to move the arm to the
    right track, and then wait for the required data to rotate beneath the
    head. Sometimes it is a large movement ("seek"), and sometimes it is
    small. Sometimes, the required data comes up right under the head, others
    you need to wait for a full rotation.

    Simple first-year college math (which I confess to having mostly
    forgotten nearly 20 years ago) shows pretty easily that for *random* I/Os,
    you will need -- on average -- to move the arm half way across the disk, and
    spin the platter for one half of a rotation. Once the required datablock is
    beneath the read head, the data is read; usually in much less than a
    millisecond.

    Reading the spec sheets for almost any disk drive, you will find
    published numbers for "average seek" time. Rotation time is easily computed
    from the RPMs. Because the actual time to *read* (or write) the data is
    almost negligible, these two numbers effectively determine the number of
    Random IOs Per Second (RIOPS) your disk drive can do.

    Now, that is roughly *half* the story.

    The other half is that depending on how you actually *use* your disk your
    actual experience can be much better -- or much worse -- than the "average"
    values used to compute RIOPS. Basically, when you do very large sequential
    reads or writes (the kind you might do during a backup or a full table scan)
    the disk head hardly needs to move, and the data can (usually) be read with
    next to *no* cost for seek time or rotational latency.

    This is how you do much *better* than the average. Large sequential disk
    I/Os can reach -- and sustain -- throughputs of about 50MB/s on most modern
    disks.

    But, as I said, you can also do much *worse* than average. Backups or
    file copies are the classic example of how this happens. Imagine you have
    two partitions on a single disk, one on the inner edge, and one on the outer
    edge, and that you need to copy data from one area to the other. (Does this
    sound familiar? It should; this is probably what *your* backups are doing,
    because your database and your backup volume occupy separate portions of the
    same set of disks.) In this scenario, a *simplistic* copy procedure will
    read one block of data from the first area, and then write it to the other.
    This forces the disk to make a very large "seek" (head movement) for *every*I/O.

    It is many years since I actually benchmarked something like this (why
    would I do it twice?). At the time, rather than copying from one partition
    to another on the same disk, I benchmarked two concurrent backups (to tape),
    one reading from a partition at the inner edge of the disk, and one reading
    from the outer edge, In this particular situation, it was easy to
    demonstrate that running the two backups *concurently* was about 12x *slower
    * than running them one at a time.

    That was done with 1990's vintage disks. I would expect current disks to
    show an even *larger* discrepancy on this particular test, but I won't go
    into the unnecessary details of why I expect that.

    This is what *I/O contention* is all about. Two operations (in your
    case, backup-reads and backup-writes) fight over the placement of the disk
    heads, forcing very frequent (and potentially *large*) head movements and
    resulting in much-less-than-optimal throughput.

    Note that the scenario I described (copying data from one edge of a disk
    to the other) is *one* way to cause I/O contention. Another way is the one
    that I actually *tested* in the 90's -- multiple concurrent theads trying to
    do I/O against the same device. Since you are using RAID-10, the latter is
    less likely to affect you, although it could. (This is one reason I
    suggested you try *reducing* the number of backup channels you use, and see
    whether that increases or decreases your throughput.)

    Note, however, that my last tests were done in the 1990's, when "smart"
    disk arrays were a novelty, and stuff like non-volatile RAM cache was next
    to non-existent. Smart disk hardware with caching and clever scheduling
    algorithms *can*, at least theoretically, make the effects of I/O contention
    much less pronounced.

    *Whew*. That was a lot of typing!

    Okay, so, in summary: I cannot say that I/O contention *is* the cause
    of Don's performance issue. But it *could* very well be. Certainly, Don's
    situation -- data and backups on common devices, and (possibly) excessive
    concurrency -- fit the profile of systems that are *likely* to suffer from
    I/O contention, and hopefully I have helped Don (and others) understand why.

    By the way... I did mention before that Don has another reason to move
    his backups to separate physical spindles, but I may have failed to say what
    it was. Even RAID-10 disk sets sometimes go "poof". It would be *very*
    embarassing indeed to have this happen, and simultaneously lose both your
    database and your backups! That is the make-sure-your-resume-is-up-to-date
    kind of "embarassing"...

    Anyway, I hope this has been helpful...
    On Nov 27, 2007 10:56 AM, wrote:

    *Don's "sar" statistics show that during the backup, the system is
    completely "busy", spending about 30% of its time in CPU, and 70% waiting on
    I/O.*



    Perhaps I missed an email in this thread. The sar statistics do not show
    70% wait. The time spent here is in user CPU and therefore the disk is not
    the bottleneck in this case. This is a problem with a single channel/thread
    going as fast as it can given the CPU capability. Making the rman disk
    targets into multiple disk targets may (at best) get at most 5% back. All
    other things being equal, in my view, you can't speed this up, but you can
    scale this up to the extent you have excess cpu capacity (that said a
    benchmark might be to create an uncompressed backup of say 4 data files and
    then follow it up with compress/gzip and see what the resource utilization
    and elapsed times are).


    ...
    --
    Cheers,
    -- Mark Brinsmead
    Senior DBA,
    The Pythian Group
    http://www.pythian.com/blogs

    --
    http://www.freelists.org/webpage/oracle-l
  • Mark Brinsmead at Dec 3, 2007 at 12:55 am
    Thank you, Ranko.

    Of course, I was simplifying for the purpose of explanation.

    As it happens, the platters all spin synchronously, and -- as you point out
    yourself -- the heads all move synchronously. That there are multiples of
    each is actually completely irrelevant with respect to understanding basic
    disk drive physics, or the nature of "I/O contention". In terms of
    "geometry", the disk effectively still *logically *has one head and one
    platter. The actual number of heads and platters generally affects only one
    thing: transfer rate. And transfer rate itself is almost meaningless in the
    context of single-block random reads -- at least as long as
    (average-seek-time + rotational-latency) exceeds single block transfer time
    by orders of magnitude.

    When I referred to disks with "multiple heads" no longer existing, I was
    actually referring to "multiple heads *per platter*". Such things *used* to
    exist (N independent heads per platter increases random I/Os per second by a
    factor of *at least* N, and with a good scheduling algorithm, much better
    than that) but I have not seen such a device in a very long time.

    The references you have linked to do look quite good. I have not read them
    in detail, but these do look like good resources for somebody who wants to
    better understand how disk drives work. And a good basic understanding of
    disk drive behaviour is very important indeed to a DBA trying to tune I/O
    performance.
    On Dec 2, 2007 7:03 AM, Ranko Mosic wrote:


    Disk has more than one platter.
    http://www.storagereview.com/guide2000/ref/hdd/op/index.html
    http://www.storagereview.com/guide2000/ref/hdd/op/act.html
    http://www.storagereview.com/guide2000/ref/hdd/op/over.html
    Each platter has two heads, one on the top of the platter and one on
    the bottom, so a hard disk with three platters (normally) has six
    surfaces and six total heads

    This means that when the actuator moves, all of the heads move
    together in a synchronized fashion. Heads cannot be individually sent
    to different track numbers

    On 11/28/07, Mark Brinsmead wrote:
    ...
    Don, to better understand the issue of I/O contention, remember how a
    disk works. It has a spinning platter, and a (one!) moving arm. (Yes,
    multi-actuator disk drives exist. But I haven't actually seen one for
    decades; they are too costly to manufacture and most IT managers are so
    fixated on metrics like $/GB that foolish matters like "throughput" and
    response time are irrelevant. At least until well after the purchasing
    decision has been made.)

    To read a given block of data, the disk drive need to move the arm to the
    right track, and then wait for the required data to rotate beneath the head.
    Sometimes it is a large movement ("seek"), and sometimes it is small.
    Sometimes, the required data comes up right under the head, others you need
    to wait for a full rotation.

    Simple first-year college math (which I confess to having mostly
    forgotten nearly 20 years ago) shows pretty easily that for random I/Os, you
    will need -- on average -- to move the arm half way across the disk, and
    spin the platter for one half of a rotation. Once the required
    datablock is
    beneath the read head, the data is read; usually in much less than a
    millisecond.
    ... <http://www.pythian.com/blogs>


    --
    Regards,
    Ranko Mosic
    Consultant Senior Oracle DBA
    B. Eng, Oracle 10g, 9i Certified Database Professional
    Phone: 416-450-2785
    email: mosicr_at_rogers.com

    http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
    --
    Cheers,
    -- Mark Brinsmead
    Senior DBA,
    The Pythian Group
    http://www.pythian.com/blogs

    --
    http://www.freelists.org/webpage/oracle-l
  • Don Seiler at Dec 3, 2007 at 4:24 pm
    So if I have just the one /rman LUN, does it even make sense to have
    more than 1 channel, even if /rman is on its own spindles? What about
    the fact that the LUN is made up multiple disks sliced and diced (e.g.
    a 3D+3P raid group)?
  • Jared Still at Dec 3, 2007 at 11:09 pm

    On Dec 3, 2007 8:24 AM, Don Seiler wrote:
    So if I have just the one /rman LUN, does it even make sense to have
    more than 1 channel, even if /rman is on its own spindles? What about
    the fact that the LUN is made up multiple disks sliced and diced (e.g.
    a 3D+3P raid group)?
    I believe the tuning guide suggests one channel per spindle.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    http://www.freelists.org/webpage/oracle-l
  • Krish.hariharan_at_quasardb.com at Dec 4, 2007 at 12:21 am
    Don,

    Sorry, I did not realize that you had sent this to me and not to the
    group. Two things. In your case I think you are constrained by speed and
    you can try to overcome by using more channels to increase your
    throughput.

    The channel processes are not going to be writing all the time there are
    going to be bottlenecks. Therefore having more channels may be ok to the
    extent that you don't compete wiht yourself.

    You should use the metrics associated with io such as service time and the
    time it takes to execute your backup as a measure for how to design this.
    As was discussed in this thread you are going to be constrained by the
    theoretical maximum of the choice you make (raid 0+1 vs raid 5 for
    example)

    In your case however, the disk optimizations are not likely to help since
    you are not io constrained.

    Regards,
    -Krish
    So if I have just the one /rman LUN, does it even make sense to have
    more than 1 channel, even if /rman is on its own spindles? What about
    the fact that the LUN is made up multiple disks sliced and diced (e.g.
    a 3D+3P raid group)?

    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Dan Norris at Dec 10, 2007 at 9:59 pm
    I'm not a fan of putting Oracle DBs on NFS. Furthermore, as this list shows: http://www.oracle.com/technology/products/database/clustering/certify/tech_generic_unix_new.html, only a relatively small handful of specialized NFS appliances/software are supported for RAC. That is, you can't take the typical UNIX NFS server implementation and use it to run RAC in a supported way. If you don't care about support and just want to build a sandbox, it may work fine--I've used OpenFiler for sandboxes and it worked well for my functional (not load) testing.

    Dan

    Original Message ----
    From: "krish.hariharan_at_quasardb.com"
    To: Jon.Crisler_at_usi.com
    Cc: Oracle-L Freelists
    Sent: Monday, December 10, 2007 1:37:46 PM
    Subject: Re: NFS on a 10g RAC cluster

    Jon,

    From discussions with a Unix architect, I understood that read-write
    nfs
    has some holes that our security teams did not like. We however use
    read
    only nfs routinely in many environments. The issues were:
    1. Security did not like us using nfs, especially read-write
    2. In our Solaris environments, in some older OS releases, stale nfs
    mounts were problematic.

    A question though: Is there a reason why you wouldn't have the nfs
    mounts
    on all nodes of the RAC and perhaps control access to that mount point
    through, say the services framework as opposed to failing the mount
    point
    to different nodes?

    -Krish
  • Crisler, Jon at Dec 10, 2007 at 10:03 pm
    I think I may have stated the problem a bit ambiguously - the intent was
    not to store database files on NFS, but rather to host NFS on the same
    platform, and let CRS manage and failover the NFS daemons. CRS can be
    extended to manage non-Oracle applications. The database is already
    built and running via ASM storage to a SAN.



    From: Dan Norris
    Sent: Monday, December 10, 2007 5:00 PM
    To: krish.hariharan_at_quasardb.com; Crisler, Jon
    Cc: Oracle-L Freelists
    Subject: Re: NFS on a 10g RAC cluster



    I'm not a fan of putting Oracle DBs on NFS. Furthermore, as this list
    shows:
    http://www.oracle.com/technology/products/database/clustering/certify/te
    ch_generic_unix_new.html, only a relatively small handful of specialized
    NFS appliances/software are supported for RAC. That is, you can't take
    the typical UNIX NFS server implementation and use it to run RAC in a
    supported way. If you don't care about support and just want to build a
    sandbox, it may work fine--I've used OpenFiler for sandboxes and it
    worked well for my functional (not load) testing.

    Dan

    Original Message ----
    From: "krish.hariharan_at_quasardb.com"
    To: Jon.Crisler_at_usi.com
    Cc: Oracle-L Freelists
    Sent: Monday, December 10, 2007 1:37:46 PM
    Subject: Re: NFS on a 10g RAC cluster

    Jon,

    From discussions with a Unix architect, I understood that read-write nfs
    has some holes that our security teams did not like. We however use read
    only nfs routinely in many environments. The issues were:
    1. Security did not like us using nfs, especially read-write
    2. In our Solaris environments, in some older OS releases, stale nfs
    mounts were problematic.

    A question though: Is there a reason why you wouldn't have the nfs
    mounts
    on all nodes of the RAC and perhaps control access to that mount point
    through, say the services framework as opposed to failing the mount
    point
    to different nodes?

    -Krish
  • Dan Norris at Dec 10, 2007 at 11:16 pm
    Hi Jon,

    I misinterpreted the question and took an opportunity to get on my soapbox--sorry about that.

    Yes, I believe that you should be able to set up NFS Server as a resource (or group of resources) under management of Oracle Clusterware. Pretty much any process that you can start, stop, and check is eligible for Clusterware management. If you want to see some examples, check out these links:

    http://www.oracle.com/technology/products/database/clusterware/pdf/TWP-Oracle-Clusterware-3rd-party.pdf
    http://www.oracle.com/technology/products/database/clusterware/pdf/SI_DB_Failover_11g.pdf (as an example--good scripts here)
    http://www.oracle.com/technology/sample_code/products/rac/zip/sifo_action_scripts.zip
    http://www.oracle.com/technology/sample_code/products/rac/zip/crs_asagent.zip

    In general, you'd want to have some VIP address that clients of the NFS service use to access the NFS mounts. I'm not an NFS expert, but I expect there are some NFS server daemons that you may need to restart (possibly to bind to the new IP) and you'll certainly want to run some exportfs commands or equivalent. You might also need to fail over some of the underlying storage if you're not using a CFS. This is essentially rebuilding what has already been done in a commercial offering by Polyserve (now HP) with their Enterprise File Services Clustered Gateway (EFS-CG) product (though they use customized NFS server code--not stock Linux NFS server code so I'm told). I've never used it personally--just read about it, so this isn't an endorsement, just awareness: http://h18006.www1.hp.com/products/storageworks/efs/index.html

    Dan

    ----- Original Message ----
    From: "Crisler, Jon"
    To: Dan Norris; krish.hariharan@quasardb.com
    Cc: Oracle-L Freelists
    Sent: Monday, December 10, 2007 4:03:29 PM
    Subject: RE: NFS on a 10g RAC cluster

    I think I may have stated the problem a
    bit ambiguously � the intent was not to store database files on NFS, but rather
    to host NFS on the same platform, and let CRS manage and failover the NFS
    daemons. CRS can be extended to manage non-Oracle applications. The database
    is already built and running via ASM storage to a SAN.

    From: Dan Norris


    Sent: Monday, December 10, 2007
    5:00 PM

    To: krish.hariharan@quasardb.com;
    Crisler, Jon

    Cc: Oracle-L Freelists

    Subject: Re: NFS on a 10g RAC
    cluster

    I'm not a fan of putting Oracle DBs
    on NFS. Furthermore, as this list shows: http://www.oracle.com/technology/products/database/clustering/certify/tech_generic_unix_new.html,
    only a relatively small handful of specialized NFS appliances/software are
    supported for RAC. That is, you can't take the typical UNIX NFS server
    implementation and use it to run RAC in a supported way. If you don't care
    about support and just want to build a sandbox, it may work fine--I've used
    OpenFiler for sandboxes and it worked well for my functional (not load)
    testing.

    Dan

    ----- Original Message
    ----

    From: "krish.hariharan@quasardb.com"

    To: Jon.Crisler@usi.com

    Cc: Oracle-L Freelists

    Sent: Monday, December 10, 2007 1:37:46 PM

    Subject: Re: NFS on a 10g RAC cluster

    Jon,

    From discussions with a Unix architect, I understood that read-write nfs

    has some holes that our security teams did not like. We however use read

    only nfs routinely in many environments. The issues were:

    1. Security did not like us using nfs, especially read-write

    2. In our Solaris environments, in some older OS releases, stale nfs

    mounts were problematic.

    A question though: Is there a reason why you wouldn't have the nfs mounts

    on all nodes of the RAC and perhaps control access to that mount point

    through, say the services framework as opposed to failing the mount point

    to different nodes?

    -Krish

    --

    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Dan Norris at Dec 10, 2007 at 11:21 pm
    Well, for every good experience, there's an equally compelling bad experience lurking somewhere I suspect. While I've known many successful deployments of NFS supporting Oracle DBs (apparently, you're one of them), I haven't been so lucky. I have seen many problems and have avoided it whenever possible in the last several years. So, my information may be a little dated and maybe I should take another look. I've never found a compelling argument FOR using NFS for Oracle and it's always seemed to be near equal in cost to the iSCSI solutions that I've had good luck with in the past few years. So, when there has been a decision to make, I've usually suggested using iSCSI instead of NFS and that's worked well in my experiences.

    So, I wouldn't refuse to work on a system using NFS, it would just be my 2nd choice when there are other options (and there usually are).

    Dan

    Original Message ----
    From: D'Hooge Freek
    To: "dannorris_at_dannorris.com"; "krish.hariharan_at_quasardb.com"; "Jon.Crisler_at_usi.com"
    Cc: Oracle-L Freelists
    Sent: Monday, December 10, 2007 4:08:39 PM
    Subject: RE: NFS on a 10g RAC cluster

    Dan,

    If I understood Jon's original question correctly, he does not want to
    place his datafiles on nfs, but instead setup an nfs server on one of
    the rac nodes and he wanted to know if there was a way to let the nfs
    server failover from one rac node to the other.

    BTW, Is there any particular reason why you are not a fan of putting
    Oracle databases on NFS? I must say I have rather good experiences with
    it.

    regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    e-mail: freek.dhooge_at_uptime.be
    tel. +32 (0)3 451 23 82
    http://www.uptime.be
    disclaimer

    From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On
    Behalf Of Dan Norris [dannorris_at_dannorris.com]
    Sent: 10 December 2007 22:59
    To: krish.hariharan_at_quasardb.com; Jon.Crisler_at_usi.com
    Cc: Oracle-L Freelists
    Subject: Re: NFS on a 10g RAC cluster

    I'm not a fan of putting Oracle DBs on NFS. Furthermore, as this list
    shows:
    http://www.oracle.com/technology/products/database/clustering/certify/tech_generic_unix_new.html,
    only a relatively small handful of specialized NFS appliances/software
    are supported for RAC. That is, you can't take the typical UNIX NFS
    server implementation and use it to run RAC in a supported way. If you
    don't care about support and just want to build a sandbox, it may work
    fine--I've used OpenFiler for sandboxes and it worked well for my
    functional (not load) testing.

    Dan

    Original Message ----
    From: "krish.hariharan_at_quasardb.com"
    To: Jon.Crisler_at_usi.com
    Cc: Oracle-L Freelists
    Sent: Monday, December 10, 2007 1:37:46 PM
    Subject: Re: NFS on a 10g RAC cluster

    Jon,

    From discussions with a Unix architect, I understood that read-write
    nfs
    has some holes that our security teams did not like. We however use
    read
    only nfs routinely in many environments. The issues were:
    1. Security did not like us using nfs, especially read-write
    2. In our Solaris environments, in some older OS releases, stale nfs
    mounts were problematic.

    A question though: Is there a reason why you wouldn't have the nfs
    mounts
    on all nodes of the RAC and perhaps control access to that mount point
    through, say the services framework as opposed to failing the mount
    point
    to different nodes?

    -Krish
  • Robert Freeman at Feb 11, 2008 at 11:51 pm
    They are...

    Quickly followed by a call to dbms_stats.delete_fixed_object_stats();

    :-D

    RF


    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)

    Original Message ----
    From: "Taylor, Chris David"
    To: don_at_seiler.us; oracle-l
    Sent: Monday, February 11, 2008 4:09:37 PM
    Subject: RE: Tuning RMAN backup and recovery

    You
    mean
    you
    weren't
    using
    dbms_stats.gather_fixed_objects_stats()??

    I
    thought
    EVERYONE

    was
    using
    that
    by
    now...

    KIDDING!!

    Only
    kidding!

    Chris
    Taylor
    Sr.
    Oracle
    DBA

    Ingram
    Barge
    Company
    Nashville,
    TN
    37205
    Office:
    615-517-3355
    Cell:
    615-354-4799
    Email:
    chris.taylor_at_ingrambarge.com

    -----Original
    Message-----
    From:
    oracle-l-bounce_at_freelists.org

    On
    Behalf
    Of
    Don
    Seiler
    Sent:
    Monday,
    February
    11,
    2008
    3:55
    PM
    To:
    oracle-l
    Subject:
    Re:
    Tuning
    RMAN

    backup
    and
    recovery

    Just
    wanted
    to
    follow-up
    here
    as
    well.
    I
    finally
    decided
    to
    file
    an
    SR,
    and
    Oracle
    came
    back
    pretty
    quickly
    with
    Note
    462879.1
    [0],
    fixed
    in
    10.2.0.4
    and
    11g.
    It
    seems
    that,
    in
    this
    bug,
    queries
    against
    v$rman_status
    table
    can
    take
    an
    extremely
    long
    time,
    and
    that
    table
    is
    queried
    for
    every
    datafile
    switch
    during
    RMAN

    DUPLICATEs

    Workarounds
    are
    to
    either
    set
    sesiion-level
    optimization
    to
    RULE

    based,
    or
    to
    gather
    fixed
    object
    statistics
    via
    dbms_stats.gather_fixed_objects_stats();.

    I've
    done
    the
    latter,
    but
    haven't
    had
    an
    opportunity
    to
    perform
    another
    restore
    as
    our
    development
    instances
    are
    needed
    until
    the
    next
    release
    in
    two
    weeks.
    I'll
    report
    back
    with
    results
    when
    I
    do.

    [0]
    https://metalink.oracle.com/metalink/plsql/f?p=130:14:698790149932005062
    0::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_f
    rame,p14_font:NOT,462879.1,1,1,1,helvetica
  • Don Seiler at Feb 12, 2008 at 12:43 am
    Is there something heinous about gathering fixed object status that
    I'm not aware of? Or is the humor going over my head?

    Don.
    On Feb 11, 2008 5:51 PM, Robert Freeman wrote:
    They are...

    Quickly followed by a call to dbms_stats.delete_fixed_object_stats();
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us
    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Feb 12, 2008 at 1:44 am
    Oh, just an attempt at humor.... Just seems like anytime I go to use one of
    the new statistics features on existing databases that at least one major
    execution plan goes into the toilet....

    :-)

    RF

    Robert G. Freeman
    Oracle Consultant/DBA/Author
    Principal Engineer/Team Manager
    The Church of Jesus Christ of Latter-Day Saints
    Father of Five, Husband of One,
    Author of various geeky computer titles
    from Osborne/McGraw Hill (Oracle Press)
    Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
    BLOG: http://robertgfreeman.blogspot.com/
    Sig V1.2

    -----Original Message-----
    From: dtseiler_at_gmail.com On Behalf Of Don
    Seiler
    Sent: Monday, February 11, 2008 5:43 PM
    To: Robert Freeman
    Cc: Chris.Taylor_at_ingrambarge.com; oracle-l
    Subject: Re: Tuning RMAN backup and recovery

    Is there something heinous about gathering fixed object status that
    I'm not aware of? Or is the humor going over my head?

    Don.
    On Feb 11, 2008 5:51 PM, Robert Freeman wrote:
    They are...

    Quickly followed by a call to dbms_stats.delete_fixed_object_stats();
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us

    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Feb 12, 2008 at 1:21 pm
    Mine was a sad attempt at humor (apparently);)

    I don't know about Robert's comment though.

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com

    -----Original Message-----
    From: dtseiler_at_gmail.com On Behalf Of Don
    Seiler
    Sent: Monday, February 11, 2008 6:43 PM
    To: Robert Freeman
    Cc: Taylor, Chris David; oracle-l
    Subject: Re: Tuning RMAN backup and recovery

    Is there something heinous about gathering fixed object status that
    I'm not aware of? Or is the humor going over my head?

    Don.

    On Feb 11, 2008 5:51 PM, Robert Freeman
    wrote:
    They are...

    Quickly followed by a call to dbms_stats.delete_fixed_object_stats();
    --
    Don Seiler
    http://seilerwerks.wordpress.com
    ultimate: http://www.mufc.us

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 15, '07 at 10:53p
activeMar 4, '08 at 2:32p
posts49
users13
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase