Friday, December 19, 2008

Oracle v$ views info

A nice link that have info about the oracle v$ views. Use it as a quick refference.

http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

Rman full backup examples

Full database backup plus archivelog backup (9i,10g)

rman>
backup as compressed backupset format '/[mount_point]/[backup_name]__%Y-%M-%D-%t_s%s_p%p' database plus archivelog not backed up delete all input;

Fast incremental startegy by having the last backup full recovered every day (10g and up)

rman>
backup incremental level 1 cumulative for recover of copy with tag 'FULL_DB'device type disk filesperset = 10 tag 'INC_DB' database;
recover copy of database with tag 'FULL_DB';
backup filesperset = 25 tag 'ARCH_BACKUP' archivelog all not backed up delete all input;

Wednesday, December 17, 2008

Execute immediate and commit

Some tips for the execute immediate pl/sql command and when it dows commit.

When you use execute immediate for dml statements then you must explicity commit.

But when you use execute immediate for ddl statements then all previous dnml statements in a transaction are explicity commited.

Friday, December 12, 2008

Number Conversion Routines (By Thomas Kyte)

This set of routines is useful to convert between various 'bases' in Oracle. Once you install these functions, you will be able to perform operations such as

select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual;

Just follow the links:

for examples -> http://asktom.oracle.com/tkyte/hexdec/index.html

for the code -> http://asktom.oracle.com/tkyte/hexdec/hexdec.sql

Using the above functions you can do right shifting
e.g

select lpad( to_bin( 3123 ), 15, '0' ) bin1,
lpad( to_bin( trunc(3123/power(2,(rownum-1))) ), 15, '0' ) bin2,
rownum-1 shift
from all_users
where rownum <= 10;



Thursday, December 4, 2008

AIX 5L Recommended VMM Settings for Oracle

Oracle database servers on IBM Power@AIX can have a really performance boost when VMM tuned upon the default IBM Settings.

AIX 5.2 VMM Parameters Tunning

vmtune -c8 -s1 -r32 -R64 -p5 -P20 -t20 -f128 -F144 -W32
setting maxfree to 144
Setting minfree to 128
Setting minperm% to 5
Setting maxperm% to 20
Setting maxclient% to 20
Setting minpgahead to 32
Setting maxpgahead to 64
Setting maxrandwrt to 32
Setting numclust to 8
Setting sync_release_ilock to 1
async io servers : maxservers = 10 * CPU, minservers=maxservers/2

The above parameters are a good starting point. You can tune a little bit by monitoring performance.
e.g
If your database is an OLTP system , using lots of db_sequential_reads (index scans) you can put minpgahead to 512 and maxpgahead to 1024 on JFS and JFS2 volume groups.

For async io you can use the disks rule of thumb: maxservers=10*[total_disks_for_oracle_files],minservers=maxservers/2

On AIX 5.3 there is a slight diference in the way the VMM works so you can use this settings:
vmo -r -o lgpg_size=16777216 lgpg_regions=128
vmo -p -o minperm%=5
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o minfree=960
vmo -p -o maxfree=1088

For async io as for AIX 5.2.

Oracle Parameters for 9iR2(9.2.0.3) and up :
filesystem_options=SETALL Enables both ASYNC and DIRECT I/O or CONCURRENT I/O(JFS2)
It is advisable not to use direct I/O because degrades performance. If you have only JFS filesystems then use filesystem_options=ASYNC which is the default setting.

NOTE
There is no "built-in" support for CIO or DIO in Oracle Database 9iR2 (9.2.0) or lower, though you can force the use of CIO (JFS2) or DIO (JFS) with filesystem mount options.