Oracles statistics for CBO ( Gathering statistics )

Optimizer statistics for use by the Cost Based Optimizer (CBO)

To achieve a quick delete and recreate of the statistics on an individual table and it's indexes
(adding column statistics for any skewed columns)

for delete

exec dbms_stats.delete_table_stats(ownname=>'user_name',tabname=>'table_name',cascade_indexes=>true);


exec dbms_stats.gather_table_stats(ownname=>'user_name',-
tabname=>'table_name',-
estimate_percent => 100,-
cascade=>true,-
method_opt=>'for all columns size skewonly');

generate statistics with as much statistical accuracy as possible,100% sample sizes are suggested since any reduction in sample size is always a concession to accuracy

100% samples are potentially time consuming and consideration needs to be made to fit the statistics gathering activities within the existing maintenance window

exec dbms_stats.gather_table_stats( -
ownname => ' Schema_name ', -
tabname => ' Table_name ', -
estimate_percent => 100, -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE 1' );

method_opt

ESTIMATE_PERCENT: defaults:

9i : "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
11g : DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)


For cases where column data is known to be skewed and column statistics are known to be beneficial, Replace:
method_opt => 'FOR ALL COLUMNS SIZE 1'
with
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
or with
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY'

Startup Oracle 10g and 11g Database In Archivelog Mode and flashback mode

Startup Oracle 10g and 11g Database In Archivelog Mode and flashback mode.

In this case "orcl " is the instance name .

-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 7 09:45:59 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SYS AS SYSDBA>!echo $ORACLE_SID
orcl

sql> create pfile='/tmp/init_orcl.txt' from spfile ;

sql> shutdown immediate;

Then edit this file vi /tmp/init_orcl.txt

==============================================

*.log_archive_format="log%r_%t_%s.arc"
-- <<<<< Oracle 10g and above this parameter must with %r %t %s these value

*.log_archive_dest_1='LOCATION="/opt/oracle/archive'

*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'

*.db_recovery_file_dest_size="2G"

*.db_flashback_retention_target="1440"


===============================================

LOG_ARCHIVE_FORMAT must be in the format: %s,%t,%r.

%s log sequence number

%S log sequence number, zero filled

%t Thread number

%T Thread number, zero filled

%a activation ID

%d database ID


%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database



SQL> startup mount pfile='/tmp/init_orcl.txt' ;
ORACLE instance started.

Total System Global Area 2634022912 bytes
Fixed Size 2042912 bytes
Variable Size 385881056 bytes
Database Buffers 2231369728 bytes
Redo Buffers 14729216 bytes
Database mounted.

sql> alter database archivelog;
Database altered.

sql>alter database flashback on;
Database altered.

sql> alter database open;
Database altered.

SYS AS SYSDBA>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archive
Oldest online log sequence 205
Next log sequence to archive 207
Current log sequence 207

sql> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.


bash-3.00$ cd /opt/oracle/archive/
bash-3.00$ ls -lt
-rw-r----- 1 oracle dba 1024 Oct 7 09:47 log699206679_1_206.arc
-rw-r----- 1 oracle dba 138240 Oct 7 09:47 log699206679_1_205.arc

to check flash back file

db_recovery_file_dest='/opt/oracle/flash_recovery_area
go to

cd /opt/oracle/flash_recovery_area/

then you will see ORCL dir then flashback dir
$cd ORCL
then
$cd flashback

finally

$pwd

/opt/oracle/flash_recovery_area/ORCL/flashback

-bash-3.00$ ls -lt
total 31152
-rw-r----- 1 oracle dba 15941632 Oct 7 09:49 o1_mf_5drnyvfc_.flb

then create spfile

sql> create spfile from pfile='/tmp/init_orcl.txt' ;

File created.

oracle 11g x86_64 bit Installation on SUSE 10.3

Installation oracle 11g on Suse 10.3
OR
Installation oracle 10g Release 2 on Suse 10.2



imp site to download rpm

http://rpm.pbone.net/
www.rpmseek.com
www.fr.rpmfind.net
http://download.opensuse.org/distribution/10.3/repo/oss/suse/x86_64/

Cause The following 32-bit OS packages have not been installed -

to load rpm

rpm -ivh (rpm name)
query about

rpm -q -a |grep -i (Rpm Name)

When you do a $ rpm -qa | grep glibc-devel,
you should see 2 rows in the output, one for the 32 bit and one for the 64 bit.

$ rpm -qa | grep glibc-devel

glibc-devel-2.3.3-98.28 --> 32bit version
glibc-devel-2.3.3-98.28 --> 64bit version


64-bit SLES 10
----------------
(These outputs are from a 64-bit SLES 10 SP0 (base release) "default-RPMs" installation.
Exact RPM version numbers may be slightly higher on your system.)

$ rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep gcc
libgcc-4.1.0-28.4 (x86_64)

$ rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" |
grep libaio
libaio-0.3.104-14.2 (x86_64)
libaio-32bit-0.3.104-14.2 (x86_64)


"Defining a "default RPMs" installation of the SLES OS".
o glibc-devel-2.4-31.2.x86_64.rpm
o gcc-4.1.0-28.4.x86_64.rpm
o libstdc++-devel-4.1.0-28.4.x86_64.rpm
o gcc-c++-4.1.0-28.4.x86_64.rpm
o glibc-devel-32bit-2.4-31.2.x86_64.rpm
o libaio-devel-0.3.104-14.2.x86_64.rpm


version of following rpm should be same

gcc
glibc-32bit
glibc-devel-32bit


Create the Oracle Groups and User Account


create the Linux groups and user account that will be used to install and maintain the Oracle Database 11g software. The user account will be called oracle, and the groups will be oinstall and dba.

Execute the following commands as root:

Create the Oracle Groups and User Account and directory

# groupadd oinstall
# groupadd dba
# mkdir -p /home/anujs/oracle
---- oracle user home directory
# mkdir -p /u01/app/oracle
---- for oracle installtion directory
# chown -R oracle:oinstall /u01/app/oracle
# chmod -R 775 /u01/app/oracle
# chmod -R 775 /home/anujs/oracle

#useradd -c "Oracle Software Owner" -g oinstall -G dba -d /home/anujs/oracle oracle



The "-G" flag specifies the primary group.
The "-g" flag specifies the secondary group.
The "-d" flag specifies the default directory.
The "-m" flag creates the default directory.
The "-s" flag specifies the default shell.



==========================
useradd -m -g oinstall -G dba -d /home/oracle -s /bin/bash -c “Oracle Software Owner” oracle
===========================



# id oracle
# uid=501(oracle) gid=502(oinstall) groups=502(oinstall),503(dba)

Set the password on the oracle account:

#passwd oracle

The values given are minimums, so if your system uses a larger value, don't change it.

===========================================
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144


===========================================

# cat /proc/sys/kernel/shmmax
shmmax = 2147483648
# cat /proc/sys/kernel/shmmni
shmmni = 4096
# cat /proc/sys/kernel/shmall
shmall = 2097152
#ipcs -lm |grep "min seg size"
shmmin = 1
#cat /proc/sys/fs/file-max
file-max = 65536



-- semmsl
# awk '{print $1}' /proc/sys/kernel/sem
250
-- semmns
awk '{print $2}' /proc/sys/kernel/sem
256000
-- semopm
awk '{print $3}' /proc/sys/kernel/sem
32
-- semmni
# awk '{print $4}' /proc/sys/kernel/sem
1024


change the kernel parameters file

vi /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144



To activate these new settings into the running kernel space, run the

#sysctl –p

Run the following commands as root to verify your settings:

#sysctl -a | grep shm
#sysctl -a | grep sem
#sysctl -a | grep file-max
#sysctl -a | grep ip_local_port_range
#sysctl -a | grep rmem_default
#sysctl -a | grep rmem_max
#sysctl -a | grep wmem_default
#sysctl -a | grep wmem_max

=============
oracle@apt-amd-02:~> cat /proc/sys/kernel/sem
250 32000 100 128
SEMMSL SEMMNS SEMOPM SEMMNI

These values represent SEMMSL, SEMMNS, SEMOPM, and SEMMNI.


SEMMSL = The maximum number of sempahores that can be in one semaphore set. It should be same size as maximum number
of Oracle processes (The PROCESSES parameter in the init.ora file).

SEMMNS Parameter = SEMMSL * SEMMNI (250*128=32000)

SEMOPM = Oracle recommends to set SEMOPM to a minimum value of 100 for 9i R2 and 10g R1/R2 databases on x86 and x86-64 platforms

SEMMNI = Oracle recommends SEMMNI to be at least 128 for 9i R2 and 10g R1/R2 databases except for 9i R2 on x86 platforms where the
minimum value is lower. Since these recommendations are minimum settings, it's best to set it always to at least 128 for 9i and 10g databases on x86 and x86-64 platforms.

=============



setting limits on the number of processes and open by oracle user
vi /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536



vi /etc/pam.d/login
session required /lib/security/pam_limits.so



- Add or edit the following line in the /etc/pam.d/xdm file,

not already exist:

session required pam_limits.so

- Add or edit the following line in the /etc/pam.d/su file, if it does

not already exist:

session required pam_limits.so

go to oracle user

# su - oracle

.bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2
export ORACLE_SID=vihaan
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1

export TZ=GMT

exit from oracle user <<<<<<------

download software form http://otn.oracle.com/

linux.x64_11gR1_database.zip

copy this file into -----following dir

# cp linux.x64_11gR1_database.zip /home/anujs/oracle

# chown oracle:oinstall linux.x64_11gR1_database.zip

# su - oracle ---imp

unzip this file

go to unzip directory and run following command

oracle$> ./runInstaller -ignoreSysPrereqs







master document

Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) [ID 169706.1]