Oracle Performance

[Jean-logbo]

Hello

Please I am actually using a new server on Linux Redhat with 32G (64 bits) .
I need some advice to increase performance since my rman takes Now 2 hours
instead of 1 hour before when I was on 16G

See below pfile details

rwddb.__db_cache_size=1577058304
rwddb.__java_pool_size=33554432
rwddb.__large_pool_size=16777216
rwddb.__shared_pool_size=335544320
rwddb.__streams_pool_size=16777216
*.audit_file_dest='/ORACLE/admin/rwddb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='NONE'
*.background_dump_dest='/ORACLE/admin/rwddb/bdump'
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=365
*.control_files='/UBSSYSTEM/rwddb_control01.ctl','/UBSUNDO/rwddb_control02.ctl','/UBSTEMP/rwddb_control03.ctl'#Restore Controlfile
*.core_dump_dest='/ORACLE/admin/rwddb/cdump'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_file_multiblock_read_count=32
*.db_keep_cache_size=369098752
*.db_name='rwddb'
*.db_writer_processes=4
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rwddbXDB)'
*.fal_client='RWDDB_stb2'
*.fal_server='RWDDB'
*.fast_start_mttr_target=250
*.instance_name='rwddb'
*.job_queue_processes=10
*.log_archive_config=''
*.log_archive_dest_1='location=/UBSARCHIVE'
*.log_archive_dest_2='service="rwddb_stb" LGWR ASYNC=20480 NOAFFIRM REOPEN=15 MAX_FAILURE=10   OPTIONAL NET_TIMEOUT=30'
*.log_archive_dest_3='service="rwddb_stb2" LGWR ASYNC=20480 NOAFFIRM REOPEN=15 MAX_FAILURE=10   OPTIONAL NET_TIMEOUT=30'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_10='DEFER'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='DEFER'
*.log_archive_dest_state_4='DEFER'
*.log_archive_dest_state_5='DEFER'
*.log_archive_dest_state_6='DEFER'
*.log_archive_dest_state_7='DEFER'
*.log_archive_dest_state_8='DEFER'
*.log_archive_dest_state_9='DEFER'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=22,22
*.log_checkpoint_timeout=0
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_index_cost_adj=30
*.pga_aggregate_target=2778726400
*.processes=450
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_cached_cursors=34
*.sessions=600
*.sga_max_size=2348810240
*.sga_target=2348810240
*.shared_pool_reserved_size=28521267
*.STANDBY_ARCHIVE_DEST='/UBSARCHIVE'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ORACLE/admin/rwddb/udump'
*.utl_file_dir='*'

[gandolf989]

I am not sure how many databases you have on your server, but assuming that this is the only database,
you should be able to give the database at least 20GB SGA. If not you should increase the SGA to at least 5GB.
The shared pool should be at least 768M and up to 1.5GB if you can, and increase the db_cache to at least 3GB
as much as 13-16GB if you grow the SGA to 20GB.

rwddb.__shared_pool_size=768M
*.sga_max_size=5G
*.sga_target=5G

I'm guessing that the shared pool is the issue. You should also post the backup script that you are using and
run "rman target=/" then run "show all;" and post the results.

[Jean-logbo]

Thanks for the response . I have only one database .
Find below the show all ;


RMAN> show all ;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/CTL_SP_BAK_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 10 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 '/BACKUP/rwddb_%D_%M_%Y_%U.bak' MAXPIECESIZE 4 G;
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 '/ORACLE/oracle/product/10.2.0/db_1/dbs/snapcf_rwddb.f'; # default

[Jean-logbo]

Find below modification done

Total System Global Area 5368709120 bytes
Fixed Size 2091528 bytes
Variable Size 1107297784 bytes
Database Buffers 4244635648 bytes
Redo Buffers 14684160 bytes
Database mounted.
Database opened.
SQL> show sga;

Total System Global Area 5368709120 bytes
Fixed Size 2091528 bytes
Variable Size 1107297784 bytes
Database Buffers 4244635648 bytes
Redo Buffers 14684160 bytes
SQL> show parameter shared_pool_size;

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
shared_pool_size big integer
1G
SQL>

[gandolf989]

You might want to try the following setting as well.

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;

Did you recently change the following parameter? 10 is a high number, depending on the size of
the server, number of cores disk storage, etc, you might get a better time with a smaller number here.
It really depends on whether or not your server is I/O bound or CPU bound. How many cores do you have?

CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO COMPRESSED BACKUPSET;

I guess you will know if this helps the next time you try a backup.