Optimize the database
Add ASM disks
For example, the database has one 50 GB Grid disk, one 100 GB log disk, and four 500 GB data disks.
To add three 200 GB log disks, create three ASM disks, open ASM Configuration Assistant: Disk Groups, select the target disk group, and right-click to add the ASM disks to the disk group.
Figure-1 Add disks to a disk group
Expand log groups
View log group information.
select * from v$log;
|
|
This example adds four disk groups to both thread 1 and thread 2. |
Add disk groups.
alter database add logfile thread 1 group 13 ('+LOG/orcl/onlinelog/redo03.dbf') size 10240M reuse;
alter database add logfile thread 2 group 18 ('+LOG/orcl/onlinelog/redo08.dbf') size 10240M reuse;
Table-1 Parameters
|
Parameter |
Description |
|
thread 1 |
Thread number. |
|
group 13 |
Log group name. You must specify the log group name when deleting logs. |
|
LOG |
Log disk group name. |
|
Orcl |
Database instance name. |
|
status |
Log group status:
|
Delete the existing log table.
If a log group to delete is in use, execute the alter system switch logfile; command to switch log groups.
When the target log group changes to inactive state, use the alter database drop logfile group group number; command to delete it.
The database uses at least two log groups. You must create a log group before you delete one.
Expand the temporary table space
Execute the following command to create a new temporary table space.
create bigfile temporary tablespace user_temp tempfile '+DATA/orcl/tmp1.dbf' size 300g autoextend on extent management local;
The parameters are as follows:
DATA—Disk group name.
orcl—Instance name.
Replace the old space with the new one.
alter database default temporary tablespace user_temp;
drop tablespace temp including contents and datafiles;// Make sure log groups are enough.
alter tablespace user_temp rename to temp
select name from v$tempfile //View the temporary table space.
Expand the undo space
CREATE BIGFILE UNDO TABLESPACE "UNDOTBS4" DATAFILE '+DATA/orcl/undo4.dbf' SIZE 102400m autoextend on;
alter system set undo_tablespace=UNDOTBS4 scope=both;
Set disk parameters
You can set the disk scheduling mode and queue depth. If you modify a disk parameter, modify it on all disks in a log, Grid, or data disk group and modify the following disk settings on the hosts resident with the disks.
/sys/block/vdb/queue/max_hw_sectors_kb—Use the default value, which is larger than 1024.
echo 1024 > /sys/block/vdb/queue/hw_sector_size—Use the default value 512.
echo deadline > /sys/block/vdb/queue/scheduler.
/etc/iscsi/iscsid.conf—Set the value of node.session.cmds_max and node.session.queue_depth to 1024.
After you modify the disk settings, log out and relog in.
iscsiadm -m node –u