Memory Max Target

To use memory_max_target on 11g R1 must have a shared memory mounted

mount -t tmpfs ramfs -o size=1024m /dev/shm

From metalink note 749851.1

Please also note that ramfs (instead of tmpfs mount over /dev/shm) is not supported for AMM at all. With AMM the Oracle database needs to grow and reduce the size of SGA dynamically. This is not possible with ramfs where it possible and supported with tmpfs (which is the default for the OS installation).
Therefore above command which will work for a 11g instance may not be suited anymore

In 11g R2 this can be done when creating the database with DBCA

metalink note 465048.1

mount -t tmpfs shmfs -o size=7g /dev/shm

Also, to make this change persistent across system restarts, add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=7g 0

NOTE: You should check with your OS Administrator and/or alert.log for information on "best" size fo shm.

Redo Block Size

OS specific and coded into oracle code.

select max(lebsz) from sys.x$kccle;

Solaris, AIX, Windows NT/2000, Linux, Irix, DG/UX, OpenVMS, NetWare, UnixWare, DYNIX/ptx :- 512 bytes

HP-UX, Tru64 Unix :- 1024 bytes

SCO Unix, Reliant Unix :- 2048 bytes

MVS, MPE/ix :- 4096 bytes

Useful SQL Plus formats

set echo off -- suppress showing sql in result set
set feedback off -- eliminate row count message
set linesize 100 -- make line long enough to hold data
set pagesize 0 -- suppress headings and page breaks
set sqlprompt '' -- eliminate SQL*Plus prompt from output
set trimspool on -- eliminate trailing blanks

SQL Loader with Null columns

TRAILING NULLCOLS tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

load data
infile data.csv
replace
into table test_table
TRAILING NULLCOLS
(
col1 TERMINATED BY ',',
col2 TERMINATED BY ',',
col3 TERMINATED BY WHITESPACE
)


Loading string terminated by new line

control file construct

load data
infile product.csv "str '\n'"
append into table feature_8k
fields terminated by "," optionally enclosed by "'"
(col1,col2,col3,col4,col6)

data file in the format of

12503,'HABQ5FAI1','ROFA','28','N'
12503,'HABQ5FAI1','ROFA','29','N'
12503,'HABQ5FAI1','ROFA','30','N'
12503,'HABQ5FAI1','ROFA','31','Y'
12503,'HABQ5FAI1','ROFA','32','Y'

load with command

sqlldr asanga/asa@db1 control=controlp.ctl data=product.csv

Clustering Factor of Indexes

The clustering factor is a number which represent the degree to which data is randomly distributed in a table.It is the number of “block switches” while reading a table using an index.
It is used by Oracle's optimizer to help determine the cost associated with index range scans in comparison to full table scans.

To calculate the clustering factor of an index during the gathering of index statistics, Oracle does the following.
For each entry in the index Oracle compares the entry's table rowid block with the block of the previous index entry.
If the block is different, Oracle increments the clustering factor by 1.
The minimum possible clustering factor is equal to the number of blocks identified through the index's list of rowid's -- for an index on a column or columns conatining no nulls, this will be equal to the number of blocks in the table that contain data. The maximum clustering factor is the number of entries in the index.

create table abc as select object_id,object_name from all_objects;

create index aidx on abc(object_id) compute statistics;

select clustering_factor From user_indexes where index_name ='AIDX';

CLUSTERING_FACTOR
-----------------
279

select count(*) from abc;

COUNT(*)
----------
54540


select blocks from user_segments where segment_name='ABC';

BLOCKS
----------
384

In the above example index has to jump 279 times to give all the data if a full table scan is performed using the index.

A good clustering factor is equal (or near) to the values of number of blocks of table.

A bad clustering factor is equal (or near) to the number of rows of table.

Rebuilding index does not improve the clustering factor. To improve the clustering factor table must be rebuild and reordered.

Gather Schema Stats

Gathering schema stats.

exec dbms_stats.gather_schema_stats (
ownname => 'SCHEMA NAME',
degree => dbms_stats.default_degree,
cascade => dbms_stats.auto_cascade,
options => 'GATHER AUTO'
);

OR

exec dbms_stats.gather_schema_stats (
ownname => 'SCHEMA NAME',
options => 'GATHER AUTO'
);

• ownname : - Schema to analyze (NULL means current schema)
• degree : - Degree of parallelism. The default for degree is NULL. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.
• cascade :- Gather statistics on the indexes as well.Index statistics gathering is not parallelized. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not.
• options :- Further specification of which objects to gather statistics for: GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER
  AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored.
  

Finding OS Disk path from ASMLib Disk group

Run querydisk command on oracleasm look up major and minor versions from ls -la /dev

or use

export ORACLE_SID=+ASM
export ORAENV_ASK=NO
. oraenv

$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" << EOS 2>&1 |grep [A-Z] > asmdisks.txt
set head off feed off
select a.group_number||' '||b.disk_number||' '||a.name||' '||b.label
from v\$asm_diskgroup a, v\$asm_disk b
where a.group_number = b.group_number;
exit
EOS

printf "%-9s %-30s %-3s %-10s %-3s\n" "ASM Disk" "Device Path [MAJ,MIN]" "GRP" "Disk Group" "DSK"
/etc/init.d/oracleasm querydisk `/etc/init.d/oracleasm listdisks` | cut -f2,10,11 -d" " | perl -pe 's/"(.*)".*\[(.*), *(.*)\]/$1 $2 $3/g;' | while read v_asmdisk v_minor v_major
do
v_device=`ls -la /dev | grep " $v_minor, *$v_major " | awk '{print $10}'`
grp=`grep $v_asmdisk asmdisks.txt|cut -f1 -d" "`
dsk=`grep $v_asmdisk asmdisks.txt|cut -f2 -d" "`
diskgroup=`grep $v_asmdisk asmdisks.txt|cut -f3 -d" "`

printf "%-9s /dev/%-25s %-3s %-10s %-3s\n" $v_asmdisk "$v_device [$v_minor, $v_major]" $grp $diskgroup $dsk
done