Performance is a very typical word associated with every other concepts, things, workforce, technology that drives the very business.
For Oracle database also no exception .You will find thousands of articles where cmplicated thing are represented simple.And many other articles where simple thing get twisted with authr's imagination.
To me perfomance is someting in some good enough form to attain the result.
Oracle perofrmance often termed as High perofmance depends on very basic components it is made of. We can divide oracle performance in to following category.
1. Instance/Memory Tuning Tuning
2. Network Tuning
3. SQL/ Application Tuning
4. Database Tuning
We will try to limit our scope of this discussion in its broad perpescetive for understanding purpose.I will try to give more detail explanations using a case study for better understanding in my next blog.
Tuesday, May 6, 2008
The Basics of Oracle Performance Tuning
Automatic Storage Management (ASM) in Oracle 10g
Oracle ASM (Automatic Storage Management) is a new feature of Oracle 10g to streamline storage management and provisioning.ASM is a powerful and easy to use volume manager and filesystem for Oracle 10g and 10g RAC.ASM eliminates the need for the DBA to directly manage the thousands of Oracle database files that could be present in a modern Oracle instance. ASM does this by enabling ASM disk groups, which are logical units comprised of disks and the files that reside on them. Using ASM, the management of thousands of Oracle files is reduced to managing a small number of disk groups.To turn on ASM you must create a ASM instance before you start your database instances.The ASM functionality is controlled by ASM instance.This is not a full database instance, just the memory structures and as such is very small and lightweightThe main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.ASM is the logical extension of the power of Oracle-managed files (OMF).
The level of redundancy and the granularity of the striping can be controlled using templates. Default templates are provided for each file typestored by ASM, but additional templates can be defined as needed.Failure groups are defined withina disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.
V$ Views (All these views are accessible from the ASM instance)
The ASM configuration can be viewed using the V$ASM_% views, which often contain different information depending on whether they are queried from the ASM instance, or a dependant database instance.
V$ASM_DISKGROUP - Performs disk discovery and lists diskgroups
V$ASM_DISKGROUP_STAT - Lists disk groups
V$ASM_DISK - Performs disk discovery and listdisks+usage metrics
V$ASM_DISK_STAT - List disks +usage metrics
V$ASM_FILE - List ASM files
V$ASM_ALIAS - list ASM aliases
V$ASM_CLIENT - lists instances DB instances connected to ASM
V$OPERATION - extent mapping table for ASM files
V$ASM_TEMPLATE - Displays a row for each template present in each disk group mounted by the ASM instance. Displays a row for each template present in each disk group mounted by the ASM instance with which the database instance communicates
In summary ASM provides the following functionality:
1.Manages groups of disks, called disk groups.
2.Manages disk redundancy within a disk group.
3.Provides near-optimal I/O balancing without any manual tuning.
4.Enables management of database objects without specifying mount points and filenames. 5.Supports large files.
6.Initialization Parameters and ASM Instance Creation
Initialization Parameters and ASM Instance Creation
The initialization parameters that are of specific interest for an ASM instance are:
INSTANCE_TYPE
Defines the instance as an ASM instance. This is the only required parameter to identify an ASM instance; the remainder can be left at their defaults.Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME
Defines the service provider name for which this ASM instance manages disk groups. +ASM is the default value, and should not be modified unless multiple ASM instances are on the same node
ASM_POWER_LIMIT
Controls rebalance operation speed. Values range from 1 to 11, with 11 being the fastest. If omitted, this value defaults to 1. The number of slaves is derived from the parallelization level specified in a manual rebalance command (POWER), or by the ASM_POWER_LIMIT parameter
ASM_DISKSTRING
An operating system dependent value; used by ASM to limit the set of disks considered for discovery
ASM_DISK_GROUPS
Describes the list of ASM disk group names to be mounted by an ASM instance at startup, or whenever the ALTER DISKGROUP ALL MOUNT command is used
LARGE_POOL_SIZE
The LARGE POOL size. This must be set to a minimum of 8MB, but Oracle recommends setting this to 16MB
Incorrect usage of parameters in ASM or RDBMS instances result in ORA-15021 errors.
To create an ASM instance first create a file called init+ASM.ora in the /tmp directory containing the following information.
INSTANCE_TYPE=ASM
Next, using SQL*Plus connect to the ide instance.
export ORACLE_SID=+ASM
sqlplus / as sysdba
Create an spfile using the contents of the init+ASM.ora file.
SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
File created.
Finally, start the instance with the NOMOUNT option.
SQL> startup nomount
ASM instance started
Total System Global Area 125829120 bytes
Fixed Size 1301456 bytes
Variable Size 124527664 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
The ASM instance is now ready to use for creating and mounting disk groups.
To shutdown the ASM instance issue the following command.
SQL> shutdown
ASM instance shutdown
Once an ASM instance is present disk groups can be used for the following parameters in database instances
(INSTANCE_TYPE=RDBMS) to allow ASM file creation:
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
CONTROL_FILES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
STANDBY_ARCHIVE_DEST
Startup and Shutdown of ASM InstancesASM instance are started and stopped in a similar way to normal database instances.
The options for the STARTUP command are:
FORCE - Performs a SHUTDOWN ABORT before restarting the ASM instance.
MOUNT - Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
NOMOUNT - Starts the ASM instance without mounting any disk groups.
OPEN - This is not a valid option for an ASM instance.
The options for the SHUTDOWN command are:
NORMAL - The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
IMMEDIATE - The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.
TRANSACTIONAL - Same as IMMEDIATE.
ABORT - The ASM instance shuts down instantly.
Administering ASM :
CREATE DISKGROUP statement - for creating Disk groups
This statement allows you to specify the level of redundancy:
A.NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
B. HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
C.EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID. In addition failure groups and preferred names for disks can be defined. If the NAME clause is omitted the disks are given a system generated name like "disk_group_1_0001". The FORCE option can be used to move a disk from another disk group into this one.
Create Disk Group Comand Esample:
CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1,
'/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1,
'/devices/diskb2' NAME diskb2;
Disk groups can be deleted using the DROP DISKGROUP statement.
DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;
Disks can be added or removed from disk groups using the ALTER DISKGROUP statement. Remember that the wildcard "*" can be used to reference disks so long as the resulting string does not match a disk already used by an existing disk group.
Add disks.
ALTER DISKGROUP disk_group_1 ADD DISK
'/devices/disk*3',
'/devices/disk*4';
Drop a disk
ALTER DISKGROUP disk_group_1 DROP DISK disk2;
Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement. The statement can be used to resize individual disks, all disks in a failure group or all disks in the disk group. If the SIZE clause is omitted the disks are resized to the size of the disk returned by the OS.
Resize a specific disk
ALTER DISKGROUP disk_group_1
RESIZE DISK diska1 SIZE 100G;
Resize all disks in a failure group.
ALTER DISKGROUP disk_group_1
RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;
Resize all disks in a disk group
ALTER DISKGROUP disk_group_1
RESIZE ALL SIZE 100G;
The UNDROP DISKS clause of the ALTER DISKGROUP statement allows pending disk drops to be undone. It will not revert drops that have completed, or disk drops associated with the dropping of a disk group.
ALTER DISKGROUP disk_group_1 UNDROP DISKS;
Disk groups can be rebalanced manually using the REBALANCE clause of the ALTER DISKGROUP statement. If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.
ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;
Disk groups are mounted at ASM instance startup and unmounted at ASM instance shutdown. Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as seen below.
ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;
Template
Templates are named groups of attributes that can be applied to the files within a disk group. The following example show how templates can be created, altered and dropped.
Create a new template
ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);
Modify template
ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);
Drop template
ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;
Available attributes include:
UNPROTECTED - No mirroring or striping regardless of the redundancy setting.
MIRROR - Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
COARSE - Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
FINE - Specifies higher granularity for striping. This attribute cannot be set for external redundancy.
Directories
A directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing.
The following examples show how ASM directories can be created, modified and deleted.--
Create a directory
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';
Rename a directory
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';
Delete a directory and all its contents
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;
Aliases
Aliases allow you to reference ASM files using user-friendly names, rather than the fully qualified ASM filenames.
Create an alias using the fully qualified filename
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'FOR '+disk_group_1/mydb/datafile/my_ts.342.3';
Create an alias using the numeric form filename
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
FOR '+disk_group_1.342.3';
Rename an alias
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'TO '+disk_group_1/my_dir/my_file2.dbf';
Delete an alias
ALTER DISKGROUP disk_group_1 DELETE ALIAS '+disk_group_1/my_dir/my_file.dbf';
Attempting to drop a system alias results in an error.
Files
Files are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.
Drop file using an alias
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';
Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';
Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE disk_group_1/mydb/datafile/my_ts.342.3';
Checking Metadata
The internal consistency of disk group metadata can be checked in a number of ways using the CHECK clause of the ALTER DISKGROUP statement.
Check metadata for a specific file.ALTER DISKGROUP disk_group_1 CHECK FILE '+disk_group_1/my_dir/my_file.dbf'
Check metadata for a specific failure group in the disk group.
ALTER DISKGROUP disk_group_1 CHECK FAILGROUP failure_group_1;
Check metadata for a specific disk in the disk group. ALTER DISKGROUP disk_group_1 CHECK DISK diska1;
Check metadata for all disks in the disk group.
ALTER DISKGROUP disk_group_1 CHECK ALL;
ASM Filenames
There are several ways to reference ASM file. Some forms are used during creation and some for referencing ASM files. The forms for file creation are incomplete, relying on ASM to create the fully qualified name, which can be retrieved from the supporting views. The forms of the ASM filenames are summarised below.
Filename Type Format
Fully Qualified ASM Filename +dgroup/dbname/file_type/file_type_tag.file.incarnation Numeric ASM Filename +dgroup.file.incarnation
Alias ASM Filenames +dgroup/directory/filename
Alias ASM Filename with Template +dgroup(template)/alias
Incomplete ASM Filename +dgroup
Incomplete ASM Filename with Template +dgroup(template)
SQL and ASM
ASM filenames can be used in place of conventional filenames for most Oracle file types, including controlfiles, datafiles, logfiles etc.
For example, the following command creates a new tablespace with a datafile in the disk_group_1 disk group.
CREATE TABLESPACE my_ts DATAFILE '+disk_group_1' SIZE 100M AUTOEXTEND ON;
Migrating to ASM Using RMAN
The following method shows how a primary database can be migrated to ASM from a disk based backup:
Disable change tracking (only available in Enterprise Edition) if it is currently being used.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Shutdown the database.
SQL> SHUTDOWN IMMEDIATE
Modify the parameter file of the target database as follows:
Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
Start the database in nomount mode.
RMAN> STARTUP NOMOUNT
Restore the controlfile into the new location from the old location.
RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
Mount the database.
RMAN> ALTER DATABASE MOUNT;
Copy the database into the ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
Switch all datafile to the new ASM location.
RMAN> SWITCH DATABASE TO COPY;
Open the database.
RMAN> ALTER DATABASE OPEN;
Create new redo logs in ASM and delete the old ones.Enable change tracking if it was being used.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
