星期四, 9月 29, 2016

FW: 如何在Redhat Linux 5 上面安裝Oracle 9i Database


Oracle 9.2.0.4 real installation guide for RHEL-5 64-BIT
1. Make sure Java libraries are up to date.. (sysad side..)

2. Do not start installation from the 9204 base installation- Oracle Universal Installer has problem

2.1 Install first the OUI "only" from the 9206 patchset (p3948480_9206_Linux-x86-64.zip)
using the Oracle_Home youve just decided

2.2 In ../Disk1/install/oraparam.ini edit the Linux value under [Certified Versions]
Linux=redhat-2.1AS,redhat-2.1,redhat-3,redhat-4,redhat-5,UnitedLinux-1.0,SuSE-9,SuSE-
* value must include redhat-5 inorder to suppress the error message for OS version incompat.

2.3 After completion of OUI install, edit again ../product/9204/oui/oraparam.ini as instructed
in step 2.2

2.4 Now install the 9204 base using the OUI from 9206. Run ../product/9204/oui/runInstaller BUT
this time, select the "products.jar" from 9204 base installation set.
(ex: /u01/app/oracle/Disk1/stage/products.jar)

2.5 From here u should be able to install the SOFTWARE ONLY option. DO NOT create a database yet.
 
3. Patch the 9204 base with the 9207 patchset (p4163445_92070_Linux-x86-64.zip)

3.1 After decompressing contents, repeat step 2.2 again to suppress OS version incompat.

3.2 Now proceed with the patch application
 
4. You can now create a database using the "dbca" utility.

-------
Real Tests
ref:
Please Ref : http://road-to-ocm.blogspot.tw/2012/07/oracle-9204-real-installation-guide-for.html
and
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/5/pdf/Tuning_and_Optimizing_Red_Hat_Enterprise_Linux_for_Oracle_9i_and_10g_Databases/Red_Hat_Enterprise_Linux-5-Tuning_and_Optimizing_Red_Hat_Enterprise_Linux_for_Oracle_9i_and_10g_Databases-en-US.pdf


vi /etc/grub.conf

# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/VolGroup00/LogVol00
#          initrd /initrd-version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Red Hat Enterprise Linux Server (2.6.18-398.el5)
        root (hd0,0)
        kernel /vmlinuz-2.6.18-398.el5 ro root=/dev/VolGroup00/LogVol00 rhgb quiet elevator=deadline
        initrd /initrd-2.6.18-398.el5.img


Tuning the Page Cache
echo "vm.pagecache=40" >> /etc/sysctl.conf
echo "vm.min_free_kbytes=1024" >> /etc/sysctl.conf
echo "vm.swappiness=10" >> /etc/sysctl.conf
echo "kernel.shmmni=4096" >> /etc/sysctl.conf
echo "kernel.shmall=2097152" >> /etc/sysctl.conf
echo "kernel.sem=250 32000 100 128" >> /etc/sysctl.conf
echo "fs.file-max=65536" >> /etc/sysctl.conf
echo "net.core.rmem_default=262144" >> /etc/sysctl.conf
echo "net.core.wmem_default=262144" >> /etc/sysctl.conf
echo "net.core.rmem_max=262144" >> /etc/sysctl.conf
echo "net.core.wmem_max=262144" >> /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range=1024 65000" >> /etc/sysctl.conf

groupadd dba -g 501
mkdir -p /home/oracle
mkdir -p /u01
mkdir -p /u02
useradd -c "Oracle Software Owner" -g dba -u 502 -m -d /home/oracle -s /bin/bash oracle
chown -R oracle:dba /oracle /u01 
chmod -R 755 /u01

vi /home/oracle/.bash_profile

ORACLE_BASE=/u01; export ORACLE_BASE
ORACLE_HOME=/u01/db920 ;export ORACLE_HOME
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
ORACLE_TERM=xterm ;export ORACLE_TERM
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$HOME/bin; export PATH
ORACLE_SID=orcl; export ORACLE_SID

#export LD_ASSUME_KERNEL=2.4.19
umask 022

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib64:/lib:/usr/lib64:/usr/lib:/usr/local/lib64:/usr/local/lib:/usr/bin; export LD_LIBRARY_PATH
unset USERNAME

vi /etc/security/limits.conf
oracle           soft    nproc   2047
oracle           hard    nproc   16384
oracle           soft    nofile  1024
oracle           hard    nofile  65536

-Add or edit the following line in the /etc/pam.d/login file            
session     required     pam_limits.so 

-Add the following lines to /etc/profile:
 if [ $USER = "oracle" ]; then
       if [ $SHELL = "/bin/ksh" ]; then
             ulimit -p 16384
             ulimit -n 65536
             else
             ulimit -u 16384 -n 65536
       fi
 fi

[root@rhel5 install]# mount /dev/cdrom /mnt

[root@rhel5 install]# head -n1 /mnt/.discinfo
1409145026.642170

vi /etc/yum.repos.d/rhel5.repo
[rhel5-Server] 
mediaid=1409145026.642170
name=RHEL5-Server
baseurl=file:///mnt/Server
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release 
enabled=1 
gpgcheck=1

rpm -qa |egrep 'binutils|compat-db|compat-libstdc++|control-center|gcc|glibc|glibc-common|glibc-devel|ksh|libaio|libgcc|libgnome|libgnomeui|libgomp|libstdc++|libstdc++-devel|libXp|make|sysstat'
|sort

yum install -y binutilsbinutils
yum install -y compat-db
yum install -y compat-libstdc++-33
yum install -y control-center
yum install -y gcc
yum install -y glibc
yum install -y glibc-common
yum install -y glibc-devel
yum install -y ksh
yum install -y libaio
yum install -y libgcc
yum install -y libgnome

yum install -y libgnomeui
yum install -y libgomp
yum install -y libstdc++
yum install -y libstdc++-devel
yum install -y libXp
yum install -y make
yum install -y sysstat

另外需安裝 
libXp-1.0.0-8.i386.rpm  (java plugin required)
sysstat-7.0.2-1.el5.i386.rpm

xhost +

vi /u01/source/Disk1/install/oraparam.ini
Linux=redhat-2.1AS,redhat-2.1,redhat-3,UnitedLinux-1.0,SuSE-9,SuSE-8
->
Linux=redhat-2.1AS,redhat-2.1,redhat-3,redhat-4,redhat-5,UnitedLinux-1.0,SuSE-9,SuSE-8

su - oracle
/u01/source/Disk1/runInstaller  --> Install OUI only

After completion of OUI install, edit again /u01/db920/oui/oraparam.ini as instructed
Linux=redhat-2.1AS,redhat-2.1,redhat-3,UnitedLinux-1.0,SuSE-9,SuSE-8
->
Linux=redhat-2.1AS,redhat-2.1,redhat-3,redhat-4,redhat-5,UnitedLinux-1.0,SuSE-9,SuSE-8


cd /u01/source/Oracle_9i_R2_92040_Database_Linux_x86_64bit
gunzip amd64_db_9204_Disk1.cpio.gz
gunzip amd64_db_9204_Disk2.cpio.gz
gunzip amd64_db_9204_Disk3.cpio.gz

cpio -idcmv < amd64_db_9204_Disk1.cpio
cpio -idcmv < amd64_db_9204_Disk2.cpio
cpio -idcmv < amd64_db_9204_Disk3.cpio

Now install the 9204 base using the OUI from 9206. Run /u01/db920/oui/bin/runInstaller BUT
this time, select the "products.jar" from 9204 base installation set. 
(ex: /u01/source/Oracle_9i_R2_92040_Database_Linux_x86_64bit/Disk1/stage/products.jar)

From here u should be able to install the SOFTWARE ONLY option. DO NOT create a database yet.

#do the following and choose to continue if prompt error
-bash-3.2$  mkdir -p /u01/db920/Apache/Apache/conf
-bash-3.2$ cp /u01/db920/oracle.apache.apache/conf/oracle_apache.conf /u01/db920/Apache/Apache/conf/.
-bash-3.2$ cp /u01/db920/oracle.apache.apache/ports.ini /u01/db920/Apache/Apache/ports.ini
-bash-3.2$ echo "s_jservPort = 7775" >> /u01/db920/oracle.apache.apache/ports.ini 
-bash-3.2$ cp /u01/db920/oracle.apache.apache/ports.ini /u01/db920/oracle.apache/.


Patch the 9204 base with the 9208 patchset

After decompressing contents, repeat edit /u01/source/Disk1/install/oraparam.ini again to suppress OS version incompat.
Linux=redhat-2.1AS,redhat-2.1,redhat-3,UnitedLinux-1.0,SuSE-9,SuSE-8
->
Linux=redhat-2.1AS,redhat-2.1,redhat-3,redhat-4,redhat-5,UnitedLinux-1.0,SuSE-9,SuSE-8

You can now create a database using the "dbca" utility

星期四, 7月 07, 2016

informix Enterprise replication

 

Informix 內建之enterprise replication 可以針對表格-表格同步, 或是當作DR備援使用

也可挑選表格所需要的部分欄位, 可設定多個thread來加速同步速度

例如

Select A , B , D from source.tab…..    (ignore C欄位)

如果要做到資料彙整、數值比對、計算之類的事情, 還是要仰賴ETL工具來實現

 

Ref:

http://iiug2015.org/files/IIUG2015Petr_PomykacekMigr_ER_Petr_Pomykacek-final.pdf

http://www.oninit.com/manual/informix/100/25122791.pdf

 

星期五, 3月 25, 2016

RE: 設定xtrabackup for MariaDB/ MySQL

Test OK


[root@RepDB2 tmp]# ./backup_mysql.sh Sunday oracle
mv: target `/source/mysql-backups/last_week' is not a directory
xtrabackup version 2.0.8 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 587)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 1073741824
>> log scanned up to (136644115)
160325 17:17:36  InnoDB: Warning: allocated tablespace 22, old maximum was 9
[01] Copying ./ibdata1 to /source/mysql-backups/this_week/Sunday/ibdata1
>> log scanned up to (136650500)
>> log scanned up to (136652382)
>> log scanned up to (136652382)
>> log scanned up to (136652382)
>> log scanned up to (136652382)
[01]        ...done
[01] Copying ./test/t2#P#d2.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d2.ibd
[01]        ...done
[01] Copying ./test/authors.ibd to /source/mysql-backups/this_week/Sunday/test/authors.ibd
[01]        ...done
[01] Copying ./test/tt.ibd to /source/mysql-backups/this_week/Sunday/test/tt.ibd
[01]        ...done
[01] Copying ./test/t2#P#d7.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d7.ibd
[01]        ...done
[01] Copying ./test/t2#P#d0.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d0.ibd
[01]        ...done
[01] Copying ./test/t2#P#d4.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d4.ibd
[01]        ...done
[01] Copying ./test/t2#P#d6.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d6.ibd
>> log scanned up to (136652382)
[01]        ...done
[01] Copying ./test/task_table2.ibd to /source/mysql-backups/this_week/Sunday/test/task_table2.ibd
[01]        ...done
[01] Copying ./test/t2#P#d1.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d1.ibd
[01]        ...done
[01] Copying ./test/t2#P#d5.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d5.ibd
[01]        ...done
[01] Copying ./test/t2#P#d3.ibd to /source/mysql-backups/this_week/Sunday/test/t2#P#d3.ibd
[01]        ...done
[01] Copying ./mysql/gtid_slave_pos.ibd to /source/mysql-backups/this_week/Sunday/mysql/gtid_slave_pos.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mydb/authors.ibd to /source/mysql-backups/this_week/Sunday/mydb/authors.ibd
[01]        ...done
[01] Copying ./mydb/t3.ibd to /source/mysql-backups/this_week/Sunday/mydb/t3.ibd
[01]        ...done
[01] Copying ./mydb/t2.ibd to /source/mysql-backups/this_week/Sunday/mydb/t2.ibd
[01]        ...done
[01] Copying ./mydb/task_table1.ibd to /source/mysql-backups/this_week/Sunday/mydb/task_table1.ibd
[01]        ...done
[01] Copying ./mydb/t1.ibd to /source/mysql-backups/this_week/Sunday/mydb/t1.ibd
[01]        ...done
xtrabackup: The latest check point (for incremental): '136652382'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (136652382)

xtrabackup: Transaction log of lsn (136644115) to (136652382) was copied.
[root@RepDB2 tmp]# ls -l    /source/mysql-backups/this_week/Sunday
total 77852
-rw-r----- 1 root root 79691776 Mar 25 17:17 ibdata1
drwx------ 2 root root     4096 Mar 25 17:17 mydb
drwx------ 2 root root     4096 Mar 25 17:17 mysql
drwx------ 2 root root     4096 Mar 25 17:17 test
-rw-r----- 1 root root       81 Mar 25 17:17 xtrabackup_checkpoints
-rw-r----- 1 root root    10752 Mar 25 17:17 xtrabackup_logfile
[root@RepDB2 tmp]#

Subject: 設定xtrabackup for MariaDB/ MySQL

#設定xtrabackup policy
# percona-xtrabackup-2.2.12-1.el7.x86_64.rpm 是從 https://mariadb.com/my_portal/download/ 下載的

#Prerequisite for perxona-xtrabackup
[root@db1 mysql]#
yum install perl-DBD-MySQL.x86_64
yum install perl-Digest-MD5.x86_64

[root@db1 mysql]#
rpm -ivh /source/percona-xtrabackup-2.2.12-1.el7.x86_64.rpm
warning: /source/percona-xtrabackup-2.2.12-1.el7.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID e3c94f49: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-xtrabackup-2.2.12-1.el7  ################################# [100%]
[root@db1 mysql]#

[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Monday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Tuesday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Wednesday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Thursday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Friday
[root@db1 source]# mkdir -p /source/mysql-backups/this_week/Saturday
[root@db1 source]# mkdir -p /source/mysql-backups/last_week
[root@db1 source]#

[root@db1 source]# cat backup_mysql.sh
#!/bin/bash
##
#####################################################################################
# "THE BEER-WARE LICENSE" (Revision 42):
# <blagovest@petrovs.info> wrote this file.  As long as you retain this notice you
# can do whatever you want with this stuff. If we meet some day, and you think
# this stuff is worth it, you can buy me a beer in return.   Blagovest Petrov
#####################################################################################
##

DAY=$1
MYSQL_PASS=$2
MYSQL_USER="root"

BACKUPS_DIR=/source/mysql-backups
THIS_WEEK=$BACKUPS_DIR/this_week
LAST_WEEK=$BACKUPS_DIR/last_week

function do_full_backup() {

  if [ -d $LAST_WEEK ]; then
    rm -rf $LAST_WEEK
    mkdir $LAST_WEEK
  fi

  if [ -d $THIS_WEEK ]; then
    mv $THIS_WEEK/* $LAST_WEEK
    mkdir -p $THIS_WEEK/Sunday
  fi

  xtrabackup --backup --target-dir=$THIS_WEEK/Sunday \
  --datadir=/var/lib/mysql \
  --user=$MYSQL_USER --password=$MYSQL_PASS
}

function do_incremental_backup() {

  if [ -d $THIS_WEEK/$DAY ]; then
    rm -rf $THIS_WEEK/$DAY
    mkdir $THIS_WEEK/$DAY
  fi


  xtrabackup --backup --target-dir=$THIS_WEEK/$DAY \
  --incremental-basedir=$THIS_WEEK/Sunday \
  --datadir=/var/lib/mysql \
  --user=$MYSQL_USER --password=$MYSQL_PASS
}

case $1 in
  Sunday)
    do_full_backup
    ;;
  *)
    do_incremental_backup
esac

手動測試
#記得要reset logfile size innodb_log_file_size = 50331648 才能備份
#MySQL Server depending on version has different default value for innodb_log_file_size: 5242880 (MySQL < 5.6.8), 50331648 (MySQL >= 5.6.8) http://dev.mysql.com/doc/refman/5.6/..._log_file_size

[root@db1 source]# ./backup_mysql.sh Sunday root123
xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (2060136)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /source/mysql-backups/this_week/Sunday/ibdata1
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /source/mysql-backups/this_week/Sunday/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./mysql/gtid_slave_pos.ibd to /source/mysql-backups/this_week/Sunday/mysql/gtid_slave_pos.ibd
[01]        ...done
[01] Copying ./test/tab1.ibd to /source/mysql-backups/this_week/Sunday/test/tab1.ibd
[01]        ...done
>> log scanned up to (2060136)
xtrabackup: The latest check point (for incremental): '2060136'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (2060136)


#Crontab
0 2 * * 0 /usr/local/bin/backup_mysql.sh Sunday root123 >/dev/null 2>&1
0 2 * * 1 /usr/local/bin/backup_mysql.sh Monday 12345 >/dev/null 2>&1
0 2 * * 2 /usr/local/bin/backup_mysql.sh Tuesday 12345 >/dev/null 2>&1
0 2 * * 3 /usr/local/bin/backup_mysql.sh Wednesday 12345 >/dev/null 2>&1
0 2 * * 4 /usr/local/bin/backup_mysql.sh Thursday 12345 >/dev/null 2>&1
0 2 * * 5 /usr/local/bin/backup_mysql.sh Friday 12345 >/dev/null 2>&1
0 2 * * 6 /usr/local/bin/backup_mysql.sh Saturday 12345 >/dev/null 2>&1



星期三, 2月 03, 2016

[Oracle] 如何從cursor import sql 至baseline.

P.S. 從cursor import sql , 進來之後, SQL的狀態就會是enabled , accepted狀態了

SQL> select /* test_spm */ count(*) from admin.objekti;
  COUNT(*)
----------
     76378
SQL> set lines 200
SQL> col sql_text for a50 wrapped
SQL> select sql_id, sql_text from v$sql
  2  where sql_text like '%test_spm%';
SQL_ID        SQL_TEXT
------------- --------------------------------------------------
cpzgdw9swdvzk select /* test_spm */ count(*) from admin.objekti
ap9td7vafq26n select sql_id, sql_text from v$sql where sql_text
              like '%test_spm%'

select *
from table(dbms_xplan.display_cursor(sql_id => 'ap9td7vafq26n'));


variable cnt number;
execute :cnt :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'ap9td7vafq26n');

select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
from dba_sql_plan_baselines
WHERE sql_text like 'select * from t%';
SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO


SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
    attribute_name  => 'ENABLED',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

[轉載]Oracle 11g 如何手動Capture SQL至sql baseline

Ref:

https://avdeo.com/tag/load_plan_from_cursor_cache/

 

1.

select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED

from dba_sql_plan_baselines

WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

------------------------------ ------------------------------ --- --- ---

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO

 

SQL handle : 語法相同的SQL

Plan_Name : 不同執行計畫路徑就會有不同的plan name

 

2.

select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

SQL handle: SYS_SQL_1447ba3a1d83920f

SQL text: select * from t where col1 = 1

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

Plan name: SYS_SQL_PLAN_1d83920fae82cf72

Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE

--------------------------------------------------------------------------------

 

Plan hash value: 470836197

 

-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("COL1"=1)

 

25 rows selected.

 

SQL>

 

3.

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1447ba3a1d83920f') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_1447BA3A1D83920F')

--------------------------------------------------------------------------------

 

-------------------------------------------------------------------------------

                        Evolve SQL Plan Baseline Report

-------------------------------------------------------------------------------

 

Inputs:

-------

  SQL_HANDLE = SYS_SQL_1447ba3a1d83920f

  PLAN_NAME  =

  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

  VERIFY     = YES

  COMMIT     = YES

 

Plan: SYS_SQL_PLAN_1d83920fae82cf72

-----------------------------------

  Plan was verified: Time used .01 seconds.

  Passed performance criterion: Compound improvement ratio >= 7.33

  Plan was changed to an accepted plan.

 

                      Baseline Plan      Test Plan     Improv. Ratio

                      -------------      ---------     -------------

  Execution Status:        COMPLETE       COMPLETE

  Rows Processed:                 1              1

  Elapsed Time(ms):               0              0

  CPU Time(ms):                   0              0

  Buffer Gets:                   22              3              7.33

  Disk Reads:                     0              0

  Direct Writes:                  0              0

  Fetches:                        0              0

  Executions:                     1              1

 

-------------------------------------------------------------------------------

                                 Report Summary

-------------------------------------------------------------------------------

Number of SQL plan baselines verified: 1.

Number of SQL plan baselines evolved: 1.

 

4.

SET SERVEROUTPUT ON

DECLARE

  l_plans_altered  PLS_INTEGER;

BEGIN

  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',

    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',

    attribute_name  => 'ENABLED',

    attribute_value => 'YES');

 

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

 

Once you evolve the plan, you can see that plan is ACCEPTED now.

 

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED

  2  from dba_sql_plan_baselines

  3  WHERE sql_text like 'select * from t%';

 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

------------------------------ ------------------------------ --- --- ---

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO

 

 

5.Verify SQL是否已經進入到sql baseline

SQL> explain plan for

  2  select * from t where col1 = 1;

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 470836197

 

-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("COL1"=1)

 

Note

-----

   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

 

18 rows selected.

 

SQL>

星期六, 1月 30, 2016

Oracle 11g 如何簡單的測試Storage Array 效能

Prerequisites for I/O Calibration:
Before running I/O calibration, ensure that the following requirements are met:
The user must be granted the SYSDBA privilege
timed_statistics must be set to TRUE(default)
Asynchronous I/O must be enabled

Oracle 11g 如何簡單的測試Storage Array 效能
1.檢查是否有開啟async IO
SQL>
COL NAME FORMAT A50
SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE  F.FILE#=I.FILE_NO
AND    FILETYPE_NAME='Data File';
SQL>
+DATA/xxx/DATAFILE/tbs2.295.902228267
ASYNC_OFF

2.修改filesystemio_options=setAll

3.重新啟動資料庫instance (filesystemio_options是靜態參數, 須重啟才會生效)

4. 檢查是否有開啟async IO
SQL>
COL NAME FORMAT A50
SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE  F.FILE#=I.FILE_NO
AND    FILETYPE_NAME='Data File';
SQL>
+DATA/xxx/DATAFILE/tbs2.295.902228267
ASYNC_ON

5.開始進行測試
set serveroutput on

declare
v_max_iops number;
v_max_mbps number;
v_actual_latency number;
begin
dbms_resource_manager.calibrate_io(num_physical_disks=>2,max_latency=>10,max_iops=>v_max_iops,max_mbps=>v_max_mbps,actual_latency=>v_actual_latency); 
 dbms_output.put_line('IOPS:'||v_max_iops);
dbms_output.put_line('MBPS:'||v_max_mbps);
dbms_output.put_line('ACTUAL_LATENCY:'||v_actual_latency);
end;
/
IOPS:99     --OLTP要求此數字越高越好
MBPS:38     --DW要求此數字越高越好
ACTUAL_LATENCY:9  --OLTP要求此數字越小越好

PL/SQL procedure successfully completed.

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...