Performance Monitoring

List of Common Oracle Metrics

Monitoring Methodologies

 

• Reactive
• Proactive

Reactive monitoring is not a recommend approach but at times it becomes inevitable. Oracle 10g provide tools that aid in proactively monitoring database. Server-generated alerts and Automated Database Diagnostic Monitor(AADM) are going to be discussed in another post.

 

Database and Instance Metrics

Performance measurements are commonly referred as databases metrics which is basically the rate of change of cumulative statistic that need to be monitored. Metrics are indicators of the health of various database targets. Excerpt from Oracle Database 10g New Features by Rampant TechPress

“The Metrics are the statistics derived from base statistics. They represent the delta values between the snapshot periods. Metrics are used by internal components (clients) for system health monitoring, problem detection and self-tuning. There are hundreds of different measurement points available to monitor database system.

Each metric is also associated with a metric name. You can query the view v$metricname to find the names of all the metrics.

SQL> select METRIC_NAME, METRIC_UNIT from v$metricname; "

 

Metric	Description
Event Class Metrics	Metrics collected on the wait event class level. e.g.  DB_TIME_WAITING
Event Metrics	Metrics collected on various wait events
File Metrics Long Duration	Metrics collected at the file level. e.g. AVERAGE_FILE_WRITE_TIME
Service Metrics	Metrics collected at the service level. e.g. CPU_TIME_PER_CALL
Session Metrics Short Duration	Metrics collected at the session level. e.g. BLOCKED_USERS
System Metrics Short Duration	Metrics collected at the system level
Tablespae Metrics	Metrics collected at the Tablespace level e.g. TABLESPACE_PCT_FULL

 

Viewing Metrics

The new MMON background process collects database metrics continuously and automatically saves them in the SGA for one hour.It also transfers the memory version of AWR stats to disk on regular basis(in snapshots). Performance metrics are available through

• Data Dictionary
• Dynamic Performance Views
• Optimizer Statistics

Data Dictionary Metrics

Data dictionary metrics provide information about space consumption and object status. Database indexes and stored procedures both need to be VALID to be used.

Unusable indexes always require DBA intervention to restore them to a valid state. Invalid
PL/SQL objects will normally recompile automatically the first time they are called, but
sometimes require DBA intervention in cases where the automatic recompilation fails.

Compiling PL/SQL procedures and Packages

Indexes can become unusable due to normal maintenance operations on tables. Unusable indexes can be rebuild to make them valid.

Rebuilding Indexes

 

Viewing In-Memory Metrics

All system related metrics saved in memory and they can be viewed using dynamic performance views. Most of the cumulative statistics can be viewed through following:

• V$SYSSTAT
• V$SESSSTAT
• V$SYSMETRIC
• V$SYSMETRIC_HISTORY

Example of few system metrics maintained in V$SYSMETRIC view:

• Buffer Cache Hit Ratio
• CPU Usage Per Sec
• Disk Sort Per Sec
• Host CPU Utilization (%)
• Library Cache Hit Ratio
• SQL Service Response Time
• Shared Pool Free (%)

Viewing Saved Metrics

After every 60 min, MMON places metric info from SGA to disk through AWR snapshot mechanism. The metric data that AWR collects through MMON is permanently stored in DBA_HIST_* views like

• DBA_HIST_SERVICE_NAME
• DBA_HIST_SESSMETRIC_HISTORY

Optimizer Statistics

Optimizer statistics for tables and indexes are stored in the data dictionary. These statistics are not intended to provide real-time data. They provide the optimizer a statistically correct snapshot of data storage and distribution which the optimizer uses to make decisions on how to access data.

Metrics collected include:

• Size of the table or index in database blocks
• Number of rows
• Average row size and chain count (tables only)
• Height and number of deleted leaf rows (indexes only)

As data is inserted, deleted, and modified these values change. The performance impact of
maintaining real-time data distribution statistics would be prohibitive, so these statistics are updated by periodically gathering statistics on tables and indexes.

Increasing Recovery Area Size

To verify this run the following query. It will show the size of the recovery area and how full it is:

set lines 100
col name format a60
select	name
,	floor(space_limit / 1024 / 1024) "Size MB"
,	ceil(space_used  / 1024 / 1024) "Used MB"
from	v$recovery_file_dest
order by name
/

To fix the problem, you need to either make the flash recovery area larger, or remove some files from it.

If you have the disk space available, make the recovery area larger:

alter system set db_recovery_file_dest_size=<size> scope=both
/

To remove files you must use RMAN. Manually moving or deleting files will have no effect as oracle will be unaware. The obvious choice is to backup and remove some archive log files. However, if you usually write your RMAN backups to disk, this could prove tricky. RMAN will attempt to write the backup to the flash recovery area…which is full. You could try sending the backup elsewhere using a command such as this:

rman target / catalog user/pass@rmancat

run {
allocate channel t1 type disk;
backup archivelog all delete input format '/<temp backup location>/arch_%d_%u_%s';
release channel t1;
}

This will backup all archive log files to a location of your choice and then remove them.

http://www.shutdownabort.com/errors/ORA-19809.php

SQL Server 2008 Backup

Backup Types

SQL Server 2008 allows you to create four different types of backups:

• Full Backups
• Differential Backups
• Transaction log Backups
• Filegroup Backups

Full Backups

A full backup captures all pages within a database that contain data. Pages that do not

contain data are not included in the backup.

Because it is more common to back up a database than to restore one, the backup engine

is optimized for the backup process. When a backup is initiated, the backup engine grabs

pages from the data files as quickly as possible, without regard to the order of pages. Because

the backup process is not concerned with the ordering of pages, multiple threads can be used

to write pages to your backup device.

Because changes canbe made to the database while a backup is running, SQL Server needs to be able to accommodate the changes while also ensuring that backups are consistent for restore purposes. To ensure both concurrent access and backup consistency, SQL Server performs the steps of the backup procedure as follows:

1. Locks the database, blocking all transactions

2. Places a mark in the transaction log

3. Releases the database lock

4. Extracts all pages in the data fi les and writes them to the backup device

5. Locks the database, blocking all transactions

6. Places a mark in the transaction log

7. Releases the database lock

8. Extracts the portion of the log between the marks and appends it to the backup

The only operations that are not allowed during a full backup are

• Adding or removing a database file
• Shrinking a database

The only two parameters required for a backup are the name of the database and the

backup device. When you specify a disk backup device, a directory and a fi le name can

be specifi ed. If a directory is not specifi ed, SQL Server performs a backup to disk and writes

the file to the default backup directory configured for the instance. Although most backups

are written to a single disk file or a single tape device, you can specify up to 64 backup

devices. When you specify more than one backup device, SQL Server stripes the backup

across each of the devices specified.

Related articles by Zemanta

• SQL Server 2008 Backup (mohibalvi.wordpress.com)
• Get the SQL Server 2008 R2 August Community Technology Preview (msdn.microsoft.com)

How to debug a Shell Script on Unix or Lunix

How to debug a Shell Script

Use -x option to debug a shell script

Run a shell script with -x option. This is valid for bash shell scripts as well.

$ ksh -x tesh.sh
$ ksh -v test.sh

Use of set builtin command

korn shell offers debugging options which can be turn on or off using set command.

=> set -x : Display commands and their arguments as they are executed.

=> set -v : Display shell input lines as they are read.

You can use above two command in shell script itself:

<span style="color: rgb(0, 0, 153); font-weight: bold;">#!/bin/ksh</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">clear</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;"># turn on debug mode</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">set -x</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">for f in *</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">do</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">   file $f</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">done</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;"># turn OFF debug mode</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">set +x</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">ls</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;"># more commands</span>

You can replace standard

#!/bin/ksh

with (for debugging)

#!/bin/ksh -xv

Use of intelligent DEBUG function

Add special variable _DEBUG. Set to `on’ when you need to debug a script:

_DEBUG="on"

Put the following function at the beginning of the script:

function DEBUG()<br />{<br /> [ "$_DEBUG" == "on" ] &&  $@ || :<br />}

Now wherever you need debugging simply use DEBUG function

DEBUG echo "File is $filename"

OR

DEBUG set -x

Cmd1

Cmd2

DEBUG set +x

When debugging done and before moving a script to production set _DEBUG to off

No need to delete debug lines.

_DEBUG="off" # set to anything but not to 'on'

Sample script:

<span style="color: rgb(0, 0, 153); font-weight: bold;">#!/bin/bash</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">_DEBUG="on"</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">function DEBUG()</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">{</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;"> [ "$_DEBUG" == "on" ] &&  $@ || :</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">}</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">DEBUG echo 'Reading files'</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">for i in *</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">do</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">  grep 'something' $i > /dev/null</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">  [ $? -eq 0 ] && echo "Found in $i file" || :</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">done</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">DEBUG set -x</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">a=2</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">b=3</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">c=$(( $a + $b ))</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">DEBUG set +x</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">echo "$a + $b = $c"</span><br />

Save and run the script:

$ ./script.sh

Output:

<span style="color: rgb(0, 0, 153); font-weight: bold;">Reading files</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">Found in xyz.txt file</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">+ a=2</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">+ b=3</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">+ c=5</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">+ DEBUG set +x</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">+ '[' on == on ']'</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">+ set +x</span><br style="color: rgb(0, 0, 153); font-weight: bold;" /><span style="color: rgb(0, 0, 153); font-weight: bold;">2 + 3 = 5</span><br />

Now set DEBUG to off

_DEBUG="off"

Run script:

$ ./script.sh

Output:

<span style="font-weight: bold; color: rgb(0, 0, 153);">Found in xyz.txt file</span><br style="font-weight: bold; color: rgb(0, 0, 153);" /><span style="font-weight: bold; color: rgb(0, 0, 153);">2 + 3 = 5</span><br />

You can also try to use DEBUG as an alias instead of function.

Links

Korn Shell Debugger by OReilly

Bash Debugger Project

Powered by ScribeFire.

Unicode Characterset in Oracle

Before starting this post let’s have an idea about unicode. Unicode is a Universal encoding scheme which is designed to include far more characters than the normal character set, in fact, Unicode wants to be able to list ALL characters. So, with unicode support in oracle data from any languages can be stored and retrieved from oracle.
Oracle supports unicode within many of the character sets starting from Oracle 7.
Below is the list of character sets that is used to support unicode in oracle.

 

Database Character Set Statement of Direction

A list of character sets has been compiled in Table A-4, "Recommended ASCII Database Character Sets" and Table A-5, "Recommended EBCDIC Database Character Sets" that Oracle Corporation strongly recommends for usage as the database character set. Other Oracle-supported character sets that do not appear on this list can continue to be used in Oracle Database 10g Release 2, but may be desupported in a future release. Starting with the next major functional release after Oracle Database 10g Release 2, the choice for the database character set will be limited to this list of recommended character sets for new system deployment. Customers will still be able to migrate their existing databases in the next major functional release after Oracle Database 10g Release 2 even if the character set is not on the recommended list. However, Oracle suggests that customers migrate to a recommended character set as soon as possible. At the top of the list of character sets Oracle recommends for all new system deployment is the Unicode character set AL32UTF8.

Choosing Unicode as a Database Character Set

Oracle Corporation recommends using Unicode for all new system deployments. Migrating legacy systems eventually to Unicode is also recommended. Deploying your systems today in Unicode offers many advantages in usability, compatibility, and extensibility. Oracle Database’s comprehensive support enables you to deploy high-performing systems faster and more easily while utilizing the advantages of Unicode. Even if you do not need to support multilingual data today or have any requirement for Unicode, it is still likely to be the best choice for a new system in the long run and will ultimately save you time and money as well as give you competitive advantages. See Chapter 6, "Supporting Multilingual Databases with Unicode" for more information about Unicode.

Choosing a National Character Set

A national character set is an alternate character set that enables you to store Unicode character data in a database that does not have a Unicode database character set. Other reasons for choosing a national character set are:

• The properties of a different character encoding scheme may be more desirable for extensive character processing operations.

• Programming in the national character set is easier.

SQL NCHAR, NVARCHAR2, and NCLOB datatypes have been redefined to support Unicode data only. You can use either the UTF8 or the AL 16UTF16 character set. The default is AL16UTF16.

 

Character Sets

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm#i635016

Oracle-supported character sets are listed in the following sections according to three broad categories.

• Recommended Database Character Sets

• Other Character Sets

• Client-Only Character Sets

In addition, common character set subset/superset combinations are listed. Some character sets can only be used with certain data types. For example, the AL16UTF16 character set can only be used as an NCHAR character set, and not as a database character set.

 

Can You use AL16UTF16 as NLS_CHARACTERSET?

No, AL16UTF16 can only be used as NLS_NCHAR_CHARACTERSET in 9i and above. Trying to create a database with  a AL16UTF16 NLS_CHARACTERSET will fail.

(Source of this answer is REPETTAS WORDPRESS BLOG )

 

1) AL24UTFFSS:

This character set was the first Unicode character set supported by Oracle. The AL24UTFFSS encoding scheme was based on the Unicode 1.1 standard, which is now obsolete. This unicode character set was used between oracle version 7.2 to 8.1.

 

2) UTF-8:

UTF8 was the UTF-8 encoded character set in Oracle8 and 8i. It followed the
Unicode 2.1 standard between Oracle 8.0 and 8.1.6, and was upgraded to Unicode
version 3.0 for oracle versions 8.1.7, 9i, 10g and 11g. If supplementary characters are inserted into in a UTF8 database encoded with Unicode version 3.0, then the actual data will be treated as 2 separate undefined characters, occupying 6 bytes in storage. So for fully support of supplementary characters use AL32UTF8 character set instead of UTF8.

3) UTFE:

UTFE has the same properties as UTF8 on ASCII based platforms. As of UTF8 it is used in different oracle versions.

 

4) AL32UTF8:

This is the UTF-8 encoded character set introduced in Oracle9i.
In Oracle 9.2 AL32UTF8 implemented unicode 3.1,
in 10.1 it implemented the Unicode 3.2 standard,
in Oracle 10.2 it supports the Unicode 4.01 standard and
in Oracle 11.1 it supports the Unicode 5.0.
AL32UTF8 was introduced to provide support for the newly defined supplementary characters. All supplementary characters are stored as 4 bytes in AL32UTF8. As while designed UTF-8 there was no concept of supplementary characters therefore UTF8 has a maximum of 3 bytes per character.

5) AL16UTF16: This is the first UTF-16 encoded character set in Oracle. It was introduced in Oracle9i as the default national character set (NLS_NCHAR_CHARACTERSET). It also provides support for the newly defined supplementary characters. All supplementary characters are stored as 4 bytes.
As with AL32UTF8, the plan is to keep enhancing AL16UTF16 as
necessary to support future version of the Unicode standard.
AL16UTF16 cannot be used as a database character set (NLS_CHARACTERSET), it is only used as the national character set (NLS_NCHAR_CHARACTERSET).
Like, AL32UTF8 In Oracle 9.0 AL16UTF16 implemented unicode 3.0,
in Oracle 9.2 it implemented unicode 3.1,
in 10.1 it implemented the Unicode 3.2 standard,
in Oracle 10.2 it supports the Unicode 4.01 standard and
in Oracle 11.1 it supports the Unicode 5.0.