2012年8月4日 星期六
Oracle:查詢誰Lock住Process, sid number ?做了什麼事？
ORACLE Tips: 查詢誰Lock住Process, sid number ?
Oracle:10G
select b.object_name obj_name,
d.ctime time,
to_char(d.lmode) l,
to_char(d.request) r,
a.os_user_name os_user,
c.machine machine,
c.program program,
c.serial# s_serial,
a.oracle_username ora_user,
a.object_id obj_id,
a.process pid,
a.session_id s_id,
p.spid
from v$locked_object a, all_objects b, v$session c, v$lock d,V$process p
where a.object_id=b.object_id
and c.Paddr = p.Addr
and a.session_id=c.sid
and a.session_id=d.sid
and d.type='TX'
order by d.ctime desc, p.spid;
---------------------------------------------------------------------------------------------------------
#查詢Session History(首先要知道Session_id，通常em發送訊息過來，就會指名session_id，
這樣會比較好找出是那一個Session做什麼事):
select sample_time,session_id,sql_id,session_state,blocking_session_status,
event,wait_class,time_waited,program,module from v$active_session_history
where blocking_session_status!='NOT IN WAIT';
#記住sql_id，再到v$sqltext查詢是執行那一個sql(piece是執行順序)：
select * from v$sqltext where sql_id='f2fnfj93dwwvh' order by piece;
上面的動作都是Oracle 10G的ASH的功能：
ASH (Active Session History) 體系結構
在Oracle10g之前，當前會話記錄保存在v$session中；處於等待狀態的會話會被復制一份放在
v$session_wait中。當該連接斷開後，其原來的連接信息在v$session和v$session_wait中就
會被刪除。沒有視圖能提供有關session在歷史上的每個時間點上都在做什麼，以及在等待
什麼資源。原來的v$session及v$session_wait只是顯示當前session正在執行什麼SQL及等待
什麼資源。
從Oracle10g開始，Oracle提供了Active Session History (ASH)來解決這個問題。 每隔1秒鐘
ASH都會將當前活動的session的信息記錄在SGA的一個緩衝區(循環使用)中。在ASH中，這個過
程稱為采樣(Sampling)。ASH缺省每一秒收集一下v$session中活動會話的情況，記錄會話等待
的事件，不活動的會話不會被采樣, 間隔時間由 _ash_sampling_interval 參數確定 .
在10g中新出現了一個視圖：v$session_wait_history。這個視圖保存了每個活動session在
v$session_wait中最近10次的等待事件. 但這對於一段時期內的數據性能狀況的監測是遠遠不夠
的，為了解決這個問題，在10g中還新添加了一個視圖：v$active_session_history。這就是ASH
（active session history）。
ASH采用的策略 ---
典型的情況下,為了診斷當前數據庫的狀態，需要最近的五到十分鐘的詳細信息。然而，由於記錄
session的活動信息是很費時間和空間的，ASH采用的策略是: 保存處於等待狀態的活動session的
信息，每秒從v$session_wait及v$session中采樣一次，並將采樣信息保存在內存中(註意：ASH的
采樣數據是保存在內存中)。
ASH的工作原理 ---
對於Active Session的采樣(每秒收集相關視圖中的信息)數據存放在SGA中，SGA中分配給ASH的大小
可以從v$sgastat中查詢(Shared Pool下ASH buffers)，該空間可以循環使用，如果需要，以前的信
息可以被新的信息覆蓋。要把所有session的所有活動記錄下來是非常消耗資源的。因此ASH只能從
V$SESSION 等少數相關視圖中獲取那些活動的session的信息。ASH每隔1秒收集session信息時，不是
通過SQL語句完成的，而是采用直接訪問內存的方式，相對更高效。
因為每秒需要采樣數據，所以ASH緩存裏數據量非常大，將他們全部刷新到磁盤上的話，會非常消
耗磁盤空間，因此在將ASH緩存中的數據刷新到AWR相關表中的時候， 采取以下策略：
1. MMON 默認每隔60分鐘 (可以調整) 將ash buffers 中的數據全部flush到磁盤 。
2. MMNL 默認當ash buffers 滿66%的時候將ash buffers 中的1/10的數據寫入磁盤 (具體1/10是哪些數據，遵循FIFO原則) 。
3. MMNL 寫入的采用數據百分比 10% 表示的是寫入磁盤的數據占 ash buffers 中采樣數據量的百分比 (而不是占ash buffers 總大小的比例)
4. 為了節省空間，AWR中采集的數據默認在7天後自動清除。
http://tw.myblog.yahoo.com/shin-chuan/article?mid=536&prev=-1&next=531
2012年6月18日 星期一
OGV 影片格式轉為 MP4：使用 FFmpeg
http://darkranger.no-ip.org/content/ogv-%E5%BD%B1%E7%89%87%E6%A0%BC%E5%BC%8F%E8%BD%89%E7%82%BA-mp4%EF%BC%9A%E4%BD%BF%E7%94%A8-ffmpeg
話說今日 DR 在 Linux（Fedora 15）上使用 recordMyDesktop 錄製了一段軟體操作的影片，輸出的格式是 *.OGV，但這個格式卻沒有辦法成功上傳到 YouTube，於是打算再用 FFmpeg 轉檔，不過 FFmpeg 在沒有加參數的情況下，輸出的影片品質不是很理想，所以 DR 上網查了一下，用兩組指令轉成品質還不錯的 MP4 格式：
ffmpeg -y -i out.ogv -sameq -s 1280x1024 -aspect 4:3 -r 30000/1001 -b 2M -bt 4M -pass 1 -vcodec libx264 -threads 0 -an -f mp4 -loglevel quiet /dev/null
ffmpeg -y -i out.ogv -sameq -s 1280x1024 -aspect 4:3 -r 30000/1001 -b 2M -bt 4M -pass 2 -vcodec libx264 -threads 0 -async 1 -ac 2 -ab 160k -ar 48000 -loglevel quiet output.mp4
out.ogv 是輸入的檔案名稱，1280x1024 和 4:3 是影片的解析度和比例，output.mp4 則是輸出的檔案名稱。
話說今日 DR 在 Linux（Fedora 15）上使用 recordMyDesktop 錄製了一段軟體操作的影片，輸出的格式是 *.OGV，但這個格式卻沒有辦法成功上傳到 YouTube，於是打算再用 FFmpeg 轉檔，不過 FFmpeg 在沒有加參數的情況下，輸出的影片品質不是很理想，所以 DR 上網查了一下，用兩組指令轉成品質還不錯的 MP4 格式：
ffmpeg -y -i out.ogv -sameq -s 1280x1024 -aspect 4:3 -r 30000/1001 -b 2M -bt 4M -pass 1 -vcodec libx264 -threads 0 -an -f mp4 -loglevel quiet /dev/null
ffmpeg -y -i out.ogv -sameq -s 1280x1024 -aspect 4:3 -r 30000/1001 -b 2M -bt 4M -pass 2 -vcodec libx264 -threads 0 -async 1 -ac 2 -ab 160k -ar 48000 -loglevel quiet output.mp4
out.ogv 是輸入的檔案名稱，1280x1024 和 4:3 是影片的解析度和比例，output.mp4 則是輸出的檔案名稱。
ffmpeg轉檔
朋友說YouTube都是用ffmpeg在伺服器轉檔，因此找了一下用法：
ffmpeg -i 原始影片檔 -f flv 輸出.flv
另一個人寫
ffmpeg -i 原始影片檔 -vcodec flv -acodec copy -y 輸出.flv
ffmpeg支援的格式非常多，只要輸入 ffmpeg -formats 就可以看到支援的codec，不過有些做假:P 例如codec選wmv3會失敗，只能選wmv2。
以下是我試用過的參數：
-b bitrate set video bitrate (in kbit/s)
-r rate set frame rate (Hz value, fraction or abbreviation)
-s 調整輸入大小，例如：-s 320x240
-y overwrite output files
-t duration set the recording time
-f 輸出格式
ffmpeg效果好，速度快，想架影片站的人要好好利用呀～
把影像檔案轉換成avi格式
＃ ffmpeg -i filename.flv -f avi -vcodec libxvid -acodec libmp3lame -ar 22050 filename.avi
參數說明：
-i : 指輸入的檔案名稱，只要系統中有相對應的codec，就能使用多種格式。
-f : 強迫輸出的檔案格式。
-vcodec : 指定影像的編碼格式，這裡的libxvid指的是一般所說的xvid。
-acodec : 指定聲音的編碼格式，libmp3lame就是常用的mp3格式。
-ar : 指定聲音的取樣頻率，一般預設是44100。（因為我是用在手機上，所以設比較低。）
把影像檔案轉換成3gp格式
# ffmpeg -i filename.avi -s qcif -vcodec h263 -acodec libfaac -ac 1 -ar 8000 -r 25 -ab 32 filename.3gp
參數說明：
-i : 指輸入的檔案名稱。
-s : 畫面的解析度。qcif指的是176x144，直接輸入長和寬也可以，格式是 wxh。
-vcodec : 指定影像的編碼格式。
-acodec : 指定聲音的編碼格式，libfaac就是指aac格式。
-ac : 設定聲音的聲道數。1指是的單聲道。
-ar : 指定聲音的取樣頻率。
-r : 設定fps。
-ab : 指定聲音的bitrate。
把影像檔轉換成flv格式
# ffmpeg -i filename.mpg -ab 56 -ar 22050 -b 500 -r 15 filename.flv
參數說明：
-i : 指輸入的檔案名稱。
-ab : 指定聲音的bitrate。
-ar : 指定聲音的取樣頻率。
-b : 指定影像的bitrate
-r : 設定fps。
將聲音檔案轉成mp3
# ffmpeg -i filename.wav -acodec libmp3lame -ar 44100 -ab 128k filename.mp3
參數說明：
-i : 指輸入的檔案名稱。
-ar : 指定聲音的取樣頻率。
-ab : 指定聲音的bitrate。
-acodec : 指定聲音的編碼格式，libmp3lame就是常用的mp3格式。如果輸出的檔案有指定mp3的話，其實這裡省略沒關係。
如果覺得原本的聲音檔音量太小的話，可以再加一個 -vol 200 的參數，會讓輸出的mp3音量變成200%，就兩倍大聲。200是百分比，可以隨意調整的。
以上幾個是很常用的格式，整理下來做成筆記，也供各位需要轉檔的人參考。
話說回來，在windows下，每次要轉檔都得想看看要找什麼軟體，比較好用的大都是要花錢買的，不過如果會用ffmpeg的話，差不多快天下無敵了，ffmpeg提供的轉檔格式和設定多到難以捉摸。現在想到要轉檔時，反而想到的是找找看ffmpeg的參數怎麼下。
當然也是有人做了一些介面來操作ffmpeg，不過對於喜歡簡便又不用裝太多東西的我來說，寫成shell script就很好用了。
也可以輸入影片檔案，指定輸出為聲音檔案。ffmpeg會幫你把影片中的聲音分離出來。
http://blog.xuite.net/michaelr/linux/22143380
2012年5月9日 星期三
To completely remove all Oracle databases, instances, and software from an Oracle home directory
http://docs.oracle.com/cd/B19306_01/install.102/b15660/remove_oracle_sw.htm#sthref1036
6.1 Overview
To completely remove all Oracle databases, instances, and software from an Oracle home directory, you must:
- Identify all instances associated with the Oracle home.
- Remove database and Automatic Storage Management instances.
- Shut down processes.
- Reconfigure the Oracle Cluster Synchronization Services Daemon, if necessary.
- Remove the Oracle software.
6.2 Identifying All Instances
To identify all instances associated with the Oracle home that you want to remove, enter the following command:
$ more /etc/oratab
The output of this command contains entries similar to the following:
+ASM:/u01/app/oracle/product/10.2.0/db_1:N CUST:/u01/app/oracle/product/10.2.0/db_1:N
These entries show that the
+ASM Automatic Storage Manager instance and the CUST Oracle database instance are associated with the/u01/app/oracle/product/10.2.0/db_1 Oracle home directory.6.3 Removing an Oracle Database
To completely remove Oracle Database software, you must remove any installed databases. To remove an Oracle database:
Note:
Removing an Oracle database deletes all of the data in the database. If you want to keep this data, make sure that you back up the database before deleting it.- Log in as the
oracleuser:$ su - oracle
- Run the
oraenvorcoraenvscript to set the environment for the database that you want to remove, for example:- Bourne, Bash, or Korn shell:
$ . /usr/local/bin/oraenv
- C shell:
% source /usr/local/bin/coraenv
- At the prompt, specify the SID for the database that you want to remove.
- Start the Database Configuration Assistant:
$ dbca
The Welcome window appears. - Click Next.The Operations window appears.
- Select Delete a Database, then click Next.
- Select the database that you want to delete, then click Finish.
- In the window that appears, confirm that you want to delete the database.
- When Database Configuration Assistant removes the database, you are prompted to choose whether you want to perform another operation. Click Yesto return to the Operations screen or click No to exit from Database Configuration Assistant. If you want to remove another database, click Yes and repeat steps 6 through 8.
6.4 Removing an Automatic Storage Management Instance
To completely remove Oracle database software, you must also remove any Automatic Storage Management instances running in the Oracle home. To remove an Automatic Storage Management instance:
- If necessary, log in as the
oracleuser:$ su - oracle
- Run the
oraenvorcoraenvscript to set the environment for the Automatic Storage Management instance that you want to remove, for example:- Bourne, Bash, or Korn shell:
$ . /usr/local/bin/oraenv
- C shell:
$ source /usr/local/bin/coraenv
- At the prompt, specify the SID for the Automatic Storage Management instance that you want to remove.
- Connect to the Automatic Storage Management instance as the SYS user:
$ sqlplus "SYS/SYS_PASSWORD AS SYSDBA" - Enter the following command to determine whether any Oracle database instance is using the Automatic Storage Management instance:
SQL> SELECT INSTANCE_NAME FROM V$ASM_CLIENT;
This command lists all of the database instances that are using this Automatic Storage Management instance.Note:This command only lists database instances that are running. It is possible that other instances are associated with the Automatic Storage Management instance, but they are not currently running.If you removed a database from this Oracle home but the output from the command shows that this Automatic Storage Management instance is supporting a database instance in another Oracle home, do not remove the Automatic Storage Management instance or the Oracle home. - If there are no database instances associated with this Automatic Storage Management instance, drop the disk groups associated with this instance as follows:Note:Dropping the Automatic Storage Management disk group makes the disk device available for use with another Automatic Storage Management instance if required. However, all data in the disk group is lost. Make sure that no other database instance requires any data from this disk group before you drop it.
- Identify the disk groups associated with the Automatic Storage Management instance:
SQL> SELECT NAME FROM V$ASM_DISKGROUP;
- For each disk group that you want to delete, enter a command similar to the following:
SQL> DROP DISKGROUP name INCLUDING CONTENTS;
- Enter the following command to shut down the Automatic Storage Management instance:
SQL> SHUTDOWN
- Remove the entry for the Automatic Storage Management instance from the
/etc/oratabfile.
6.5 Reconfiguring Oracle Cluster Synchronization Services
Oracle Cluster Synchronization Services (CSS) is a daemon process that is configured by the
root.sh script when you configure an Automatic Storage Management instance. It is configured to start every time the system boots. This daemon process is required to enable synchronization between Oracle Automatic Storage Management and database instances. It must be running if an Oracle database is using Automatic Storage Management for database file storage.
Note:
On cluster systems with Oracle Real Application Clusters (RAC) installations, the CSS daemon is configured during the Oracle Clusterware installation. If the system is running Oracle CRS, refer to Oracle Database Oracle Clusterware and Oracle Real Application Clusters Installation Guide for Linux for information about removing RAC or Oracle Clusterware.
Before you remove an Oracle Database 10g Oracle home, you must determine whether the CSS daemon is running from that Oracle home and whether any other Oracle Database 10g Oracle homes exist on the system:
- If the Oracle Database 10g Oracle home that you want to remove is the only Oracle Database 10g installation on the system, you can delete the CSS daemon configuration.
- If the CSS daemon is running from the Oracle Database 10g Oracle home that you want to remove and other Oracle Database 10g installations exist on the system, you must reconfigure the CSS daemon to run from another Oracle Database 10g Oracle home.
The following sections describe how to complete these tasks:
6.5.1 Identifying Oracle Database 10g Oracle Homes
To identify all of the Oracle Database 10g Oracle home directories, enter the following command:
$ more /etc/oratab
From the output, identify any Oracle home directories where Oracle Database 10g is installed. Oracle homes that contain Oracle Database 10g typically have paths similar to the following. However, they might use different paths.
/mount_point/app/oracle/product/10.2.0/db_n
If there is only one Oracle home directory that contains Oracle Database 10g, refer to the "Deleting the Oracle CSS Daemon Configuration" section for information about deleting the Oracle CSS daemon configuration.
If you identify more than one Oracle Database 10g Oracle home directory, refer to the following section for information about reconfiguring the Oracle CSS daemon.
6.5.2 Reconfiguring the Oracle CSS Daemon
To reconfigure the Oracle CSS daemon so that it runs from an Oracle home that you are not removing, follow these steps:
- In all Oracle home directories on the system, stop all Oracle Automatic Storage Management instances and any Oracle Database instances that use Automatic Storage Management for database file storage.
- Switch user to
root. - Enter the following command to identify the Oracle home directory being used to run the CSS daemon:
# more /etc/oracle/ocr.loc
The output from this command is similar to the following:ocrconfig_loc=/u01/app/oracle/product/10.2.0/db_1/cdata/localhost/local.ocr local_only=TRUE
Theocrconfig_locparameter specifies the location of the Oracle Cluster Registry (OCR) used by the CSS daemon. The path up to thecdatadirectory is the Oracle home directory where the CSS daemon is running (/u01/app/oracle/product/10.2.0/db_1in this example).Note:If the value of thelocal_onlyparameter is FALSE, Oracle Clusterware is installed on this system.See Also:Oracle Database Oracle Clusterware and Oracle Real Application Clusters Installation Guide for Linux for information about removing Oracle Real Applications Clusters or Oracle ClusterwareIf this Oracle home directory is not the Oracle home that you want to remove, go to the "Removing Oracle Software" section. - Change directory to the Oracle home directory for an Oracle Database 10g installation that you are not removing.
- Set the
ORACLE_HOMEenvironment variable to specify the path to this Oracle home directory:- Bourne, Bash, or Korn shell:
# ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2; # export ORACLE_HOME
- C shell:
# setenv ORACLE_HOME /u01/app/oracle/product/10.2.0/db_2
- Enter the following command to reconfigure the CSS daemon to run from this Oracle home:
# $ORACLE_HOME/bin/localconfig reset $ORACLE_HOME
This command stops the Oracle CSS daemon, reconfigures it in the new Oracle home, and then restarts it. When the system boots, the CSS daemon starts automatically from the new Oracle home. - To remove the original Oracle home directory, refer to the "Removing Oracle Software" section.
6.5.3 Deleting the Oracle CSS Daemon Configuration
To delete the Oracle CSS daemon configuration, follow these steps:
Note:
Delete the CSS daemon configuration only if you are certain that no other Oracle Database 10g installation requires it.- Remove any databases or Automatic Storage Management instances associated with this Oracle home. Refer to the preceding sections for information about how to complete these tasks.
- Switch user to
root. - Change directory to the Oracle home directory that you are removing.
- Set the
ORACLE_HOMEenvironment variable to specify the path to this Oracle home directory:- Bourne, Bash, or Korn shell:
# ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 # export ORACLE_HOME
- C shell:
# setenv ORACLE_HOME /u01/app/oracle/product/10.2.0/db_1
- Enter the following command to delete the CSS daemon configuration from this Oracle home:
# $ORACLE_HOME/bin/localconfig delete
The script stops the Oracle CSS daemon, then deletes its configuration. When the system boots, the CSS daemon no longer starts.
6.6 Removing Oracle Software
The following steps describe how to use Oracle Universal Installer to remove Oracle software from an Oracle home:
Note:
Always use Oracle Universal Installer to remove Oracle software. Do not delete any Oracle home directories without first using Oracle Universal Installer to remove the software.- If necessary, log in as the
oracleuser:$ su - oracle
- Set the
ORACLE_HOMEenvironment variable to specify the path of the Oracle home directory that you want to remove:- Bourne, Bash, or Korn shell:
$ ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 $ export ORACLE_HOME
- C shell:
$ setenv ORACLE_HOME /u01/app/oracle/product/10.2.0/db_1
- Remove any databases or Automatic Storage Management instances associated with this Oracle home and delete or reconfigure the Oracle CSS daemon.Refer to the preceding sections for information about how to complete these tasks.
- Stop any processes running in this Oracle home:
Process Name Command Database Control $ORACLE_HOME/bin/emctl stop dbconsoleOracle Net listener $ORACLE_HOME/bin/lsnrctl stopiSQL*Plus $ORACLE_HOME/bin/isqlplusctl stopUltra Search $ORACLE_HOME/bin/searchctl stop - Start Oracle Universal Installer as follows:
$ $ORACLE_HOME/oui/bin/runInstaller
- In the Welcome window, click Deinstall Products.The Inventory screen appears, listing all of the Oracle homes on the system.
- In the Inventory screen, select the Oracle home and the products that you want to remove, then click Remove.Note:If you choose to remove Oracle JVM, Oracle Universal Installer removes all installed products that depend on Oracle JVM, including Oracle Database 10g.Oracle Universal Installer displays a confirmation window asking you to confirm that you want to deinstall the products and their dependent components.
- Click Yes.Oracle Universal Installer displays a progress indicator as it removes the software.
- Click Close on the Inventory screen.
- When the products have been deleted, click Cancel to exit from Oracle Universal Installer, and then click Yes.
2012年5月3日 星期四
Basic Enterprise Manager Troubleshooting
The web-based Enterprise Manager tool was introduced in Oracle 10g and has significant functionality improvements over previous versions. Even so, it seems there is not a day goes by without someone asking how to solve an Enterprise Manager configuration issue, so this article explains the first things you should look at. It's not meant to be an all encompassing guide. Just a first shot.
Make sure the "/etc/hosts" file contains at least the loopback address and preferably the machine definition also.
127.0.0.1 localhost.localdomain localhost
192.168.0.181 myserver.localdomain myserver
If you are using 11g, make sure the ORACLE_HOSTNAME and ORACLE_UNQNAME environment variables are set correctly.Check the listener is running correctly.ORACLE_HOSTNAME=myserver.localdomain; export ORACLE_HOSTNAME ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
If you get something like the following, your listener is not running and you need to start it using the "lsnrctl start" command.lsnrctl status
$ lsnrctl status
LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 13-MAR-2009 13:32:49
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel5-11g.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
$
Next, check the Enterprise Manager service is running.
$ emctl start dbconsole
Assuming the console is already running, or fails to start, the next
thing to do is to reconfigure Enterprise Manager. This is done using the
Enterprise Manager Configuration Assistant (EMCA). For a first attempt,
run the following command. Remember to add the "-cluster" option to the
end for RAC databases.
$ emca -config dbcontrol db -repos recreate
If it all goes well you would expect to see something like this.
$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Mar 13, 2009 11:32:35 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Database Control is already configured for the database DB11G
You have chosen to configure Database Control for managing the database DB11G
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_1
Local hostname ................ oel5-11g.localdomain
Listener port number ................ 1521
Database SID ................ DB11G
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 11:33:19 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_11_32_35.log.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5540 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5520 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 1158 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 3938 is already in use.
Mar 13, 2009 11:33:20 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 13, 2009 11:33:22 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 13, 2009 11:35:12 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Mar 13, 2009 11:35:12 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 13, 2009 11:40:06 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 13, 2009 11:40:11 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 13, 2009 11:41:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 13, 2009 11:41:07 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 13, 2009 11:41:07 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 13, 2009 11:41:16 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 13, 2009 11:41:16 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 13, 2009 11:41:28 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 13, 2009 11:41:28 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 13, 2009 11:42:31 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 13, 2009 11:42:34 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>> The Database Control URL is https://oel5-11g.localdomain:1158/em <<<<<<
Mar 13, 2009 11:42:48 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/oel5-11g.localdomain_DB11G/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 11:42:48 AM
$
If this fails, then de-configure and configure Enterprise Manager. Remember, you need the "-cluster" option for RAC databases.The deconfig will look something like this.emca -deconfig dbcontrol db -repos drop emca -config dbcontrol db -repos create
$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Mar 13, 2009 12:09:48 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 12:10:07 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_12_09_47.log.
Mar 13, 2009 12:10:08 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 13, 2009 12:10:31 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 13, 2009 12:11:56 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 12:12:02 PM
$
The config will look something like this.
$ emca -config dbcontrol db -repos create
STARTED EMCA at Mar 13, 2009 12:37:27 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DB11G
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.1.0/db_1
Local hostname ................ oel5-11g.localdomain
Listener port number ................ 1521
Database SID ................ DB11G
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 13, 2009 12:37:54 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/DB11G/emca_2009_03_13_12_37_27.log.
Mar 13, 2009 12:37:55 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 13, 2009 12:42:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 13, 2009 12:42:07 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 13, 2009 12:42:57 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 13, 2009 12:42:59 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 13, 2009 12:42:59 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 13, 2009 12:43:07 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 13, 2009 12:43:07 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 13, 2009 12:43:20 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 13, 2009 12:43:20 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 13, 2009 12:44:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 13, 2009 12:44:17 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>> The Database Control URL is https://oel5-11g.localdomain:5500/em <<<<<<
Mar 13, 2009 12:44:21 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.1.0/db_1/oel5-11g.localdomain_DB11G/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 13, 2009 12:44:21 PM
$
For the vast majority of cases I come across, these steps solve the problem.For more information see:
Hope this helps. Regards Tim...
oracle em 端口號
http://hi.baidu.com/james_yulei/blog/item/450ca808e800801295ca6bf2.html
Use a Web browser to access the Database Control URL:
http://host:port/em
In this example:
host is the name of the computer on which you installed Oracle Database
port is the port number reserved for the Database Control during installation
If you do not know the correct port number to use, look for the following line in the $ORACLE_HOME/install/portlist.ini file:
Enterprise Manager Console HTTP Port (db_name) = 1158
The installation reserves the first available port from the range 5500 to 5519. For example, if you installed Oracle Database on host mgmt42, and the Database Control uses port 1158, use the following URL:
http://mgmt42:1158/em
Oracle Enterprise Manager displays the Database Control login page.
Use a Web browser to access the Database Control URL:
http://host:port/em
In this example:
host is the name of the computer on which you installed Oracle Database
port is the port number reserved for the Database Control during installation
If you do not know the correct port number to use, look for the following line in the $ORACLE_HOME/install/portlist.ini file:
Enterprise Manager Console HTTP Port (db_name) = 1158
The installation reserves the first available port from the range 5500 to 5519. For example, if you installed Oracle Database on host mgmt42, and the Database Control uses port 1158, use the following URL:
http://mgmt42:1158/em
Oracle Enterprise Manager displays the Database Control login page.
2012年4月30日 星期一
Oracle 10g R2 installtion on CentOS 5.5/RHEL
http://onaxer.com/blog/blog/2010/08/25/oracle-10g-r2-installtion-on-centos-5-5rhel/
August 25th, 2010 by Pawan Kumar Leave a reply »
The main required packages for installing oracle are (installing on 32 bit architecture)
binutils.i386
compat-gcc-7.3-2.96.128.i386
compat-gcc-c++-7.3-2.96.128.i386
compat-libstdc++-7.3-2.96.128.i386
compat-libstdc++-devel-7.3-2.96.128.i386
cpp.i386
gcc.i386
gcc-c++.i386
glibc.i386
glibc-common.i386
glibc-devel.i386
glibc-headers.i386
glibc-kernheaders.i386
libstdc++.i386
libstdc++-devel.i386
libaio
libai-devel.i386
pdksh.i386
setarch.i386
sysstat.i386
Download the Oracle 10gR2 from ,
http://www.oracle.com/technology/software/products/database/oracle10g/index.html
Preinstallation Steps:
1. Edit /etc/hosts file
2. Edit /etc/sysctl.conf
#kernel.shmall = 2097152
#kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
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.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144
3. Run the following command to change the kernel parameters
/sbin/sysctl -p
4. Add the following security parameter
in /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
5. Edit /etc/pam.d/login and add following line
session required pam_limits.so
6. SElinux and firewall should be turned off
check /etc/selinux/config
SELINUX=disabled
7. Creating following users and Groups
groupadd oinstall
groupadd dba
groupadd oper
useradd -g oinstall -G dba oracle
passwd oracle
8. Oracle will be installed in the following Directories
#mkdir -p /u01/app/oracle/product/10.2.0/db_1
#chown -R oracle.oinstall /u01
9. Login as Root user on the machine in GUI console and execute
#xhost +
This will enable other users to use X console
10. Take backup of the file /etc/redhat-release to /etc/redhat-release.original
and edit /etc/redhat-release
#echo “redhat-4″> /etc/redhat-release
11. Edit the /home/oracle/.bash_profile
add the following lines
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
12. Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=:0.0; export DISPLAY
13. Copy the file 10201_database_linux32.zip into oracle user home directory ,i.e, /home/oracle
and unzip the file.
14. Now the time to start installation
when u extact the file it will create a database directory.
$cd database
$./runInstaller
This will start Oracle Universal Installer
15 . In Oracle Universal Installer
a. Select Advanced Installation
b. Here Specify Inventory Directory and Credentials
Values are : /u01/app/oracle/oraInventory
and OS group name will be onistall
c. Select Installation Type
Enterprise Edition
d. Specify Home Details
Name; db_1
Path ; /u01/app/oracle/product/10.2.0/db_1
e. Product Specific Prerequisite checks
This should come passed ,otherwise select user varified
f. Select Configuration Options
Create Database
g. Select Database configuration
General Purpose
h. Specify Database configuration options
Global Database Name: TSH1
SID : TSH1
i. Select Database management options
use database control for database management
j. Specify Database Storage Option
File System
location /u01/app/oracle/oradata
k. Specify Backup and Recovery Options
Do not enable
l. Specify Database Schema Passwords
give a password which you must remember .
m. Summery and install
when the installation finishes it will show
Database Configuration Assistant
click ok
Now it will give you 2 scripts which must be run as root
#/u01/app/oracle/oraInventory/orainstRoot.sh
#/u01/app/oracle/product/10.2.0/db_1/root.sh
This is the end of installation part.
16. Post installation Steps
Just copy back
#cp /etc/redhat-release.original /etc/redhat-release
Edit the file /etc/oratab , In last change to Y
TSH1:/u01/app/oracle/product/10.2.0/db_1:Y
17. Autostart and Stop Script , login as root and create a script
This script is required to automatically start/stop Oracle database
#vim /etc/init.d/dbora
ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
;;
esac
---------------------------------
#chmod 750 /etc/init.d/dbora
#chkconfig --level 345 dbora on
Now we have created the script which will start/stop with Operating system
#/etc/init.d/dbora stop
#/etc/init.d/dbora start
Note : you may get the listener waring message while starting oracle db
Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr
To Resolve the issue ,login as oracle user and change the file $ORACLE_HOME/bin/dbstart
Nearly 78 line
ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
Change to
ORACLE_HOME_LISTNER=$ORACLE_HOME
18. As oracle user Login in shell
$sqlplus
usename : SYSTEM
password : givenbyyou
Some of the Reference taken from
1. http://dearlinux.wordpress.com/2010/07/01/installing-oracle-database-10g-release-2-on-linux-rhel-5-part-2/
2. http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php
3. http://ivan.kartik.sk/oracle/install_ora10gR2_redhat.html
August 25th, 2010 by Pawan Kumar Leave a reply »
The main required packages for installing oracle are (installing on 32 bit architecture)
binutils.i386
compat-gcc-7.3-2.96.128.i386
compat-gcc-c++-7.3-2.96.128.i386
compat-libstdc++-7.3-2.96.128.i386
compat-libstdc++-devel-7.3-2.96.128.i386
cpp.i386
gcc.i386
gcc-c++.i386
glibc.i386
glibc-common.i386
glibc-devel.i386
glibc-headers.i386
glibc-kernheaders.i386
libstdc++.i386
libstdc++-devel.i386
libaio
libai-devel.i386
pdksh.i386
setarch.i386
sysstat.i386
Download the Oracle 10gR2 from ,
http://www.oracle.com/technology/software/products/database/oracle10g/index.html
Preinstallation Steps:
1. Edit /etc/hosts file
2. Edit /etc/sysctl.conf
#kernel.shmall = 2097152
#kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
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.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144
3. Run the following command to change the kernel parameters
/sbin/sysctl -p
4. Add the following security parameter
in /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
5. Edit /etc/pam.d/login and add following line
session required pam_limits.so
6. SElinux and firewall should be turned off
check /etc/selinux/config
SELINUX=disabled
7. Creating following users and Groups
groupadd oinstall
groupadd dba
groupadd oper
useradd -g oinstall -G dba oracle
passwd oracle
8. Oracle will be installed in the following Directories
#mkdir -p /u01/app/oracle/product/10.2.0/db_1
#chown -R oracle.oinstall /u01
9. Login as Root user on the machine in GUI console and execute
#xhost +
This will enable other users to use X console
10. Take backup of the file /etc/redhat-release to /etc/redhat-release.original
and edit /etc/redhat-release
#echo “redhat-4″> /etc/redhat-release
11. Edit the /home/oracle/.bash_profile
add the following lines
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
12. Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:
DISPLAY=:0.0; export DISPLAY
13. Copy the file 10201_database_linux32.zip into oracle user home directory ,i.e, /home/oracle
and unzip the file.
14. Now the time to start installation
when u extact the file it will create a database directory.
$cd database
$./runInstaller
This will start Oracle Universal Installer
15 . In Oracle Universal Installer
a. Select Advanced Installation
b. Here Specify Inventory Directory and Credentials
Values are : /u01/app/oracle/oraInventory
and OS group name will be onistall
c. Select Installation Type
Enterprise Edition
d. Specify Home Details
Name; db_1
Path ; /u01/app/oracle/product/10.2.0/db_1
e. Product Specific Prerequisite checks
This should come passed ,otherwise select user varified
f. Select Configuration Options
Create Database
g. Select Database configuration
General Purpose
h. Specify Database configuration options
Global Database Name: TSH1
SID : TSH1
i. Select Database management options
use database control for database management
j. Specify Database Storage Option
File System
location /u01/app/oracle/oradata
k. Specify Backup and Recovery Options
Do not enable
l. Specify Database Schema Passwords
give a password which you must remember .
m. Summery and install
when the installation finishes it will show
Database Configuration Assistant
click ok
Now it will give you 2 scripts which must be run as root
#/u01/app/oracle/oraInventory/orainstRoot.sh
#/u01/app/oracle/product/10.2.0/db_1/root.sh
This is the end of installation part.
16. Post installation Steps
Just copy back
#cp /etc/redhat-release.original /etc/redhat-release
Edit the file /etc/oratab , In last change to Y
TSH1:/u01/app/oracle/product/10.2.0/db_1:Y
17. Autostart and Stop Script , login as root and create a script
This script is required to automatically start/stop Oracle database
#vim /etc/init.d/dbora
ORA_HOME=/u01/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
;;
esac
---------------------------------
#chmod 750 /etc/init.d/dbora
#chkconfig --level 345 dbora on
Now we have created the script which will start/stop with Operating system
#/etc/init.d/dbora stop
#/etc/init.d/dbora start
Note : you may get the listener waring message while starting oracle db
Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr
To Resolve the issue ,login as oracle user and change the file $ORACLE_HOME/bin/dbstart
Nearly 78 line
ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle
Change to
ORACLE_HOME_LISTNER=$ORACLE_HOME
18. As oracle user Login in shell
$sqlplus
usename : SYSTEM
password : givenbyyou
Some of the Reference taken from
1. http://dearlinux.wordpress.com/2010/07/01/installing-oracle-database-10g-release-2-on-linux-rhel-5-part-2/
2. http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php
3. http://ivan.kartik.sk/oracle/install_ora10gR2_redhat.html
2012年3月4日 星期日
[分享] Oracle存儲過程編寫經驗和優化措施
http://forum.twbts.com/thread-5943-1-1.html
整理文件時，發現幾年前搜集到的一些文件，出處已不知了，不過內容蠻受用，分享給大家。
1 開發人員如果用到其他庫的Table或View，務必在當前庫中建立View來實現跨庫操作，最好不要直接使用“databse.dbo.table_name”，因為sp_depends不能顯示出該SP所使用的跨庫table或view，不方便校驗。
2 開發人員在提交SP前，必須已經使用set showplan on分析過查詢計畫，做過自身的查詢優化檢查。
3 高程式運行效率，優化應用程式，在SP編寫過程中應該注意以下幾點：
3.1 SQL的使用規範：
3.1.1 儘量避免大事務操作，慎用holdlock子句，提高系統併發能力。
3.1.2 儘量避免反復訪問同一張或幾張表，尤其是資料量較大的表，可以考慮先根據條件提取資料到臨時表中，然後再做連接。
3.1.3 儘量避免使用游標，因為游標的效率較差，如果游標操作的資料超過1萬行，那麼就應該改寫；如果使用了游標，就要儘量避免在游標迴圈中再進行表連接的操作。
3.1.4 注意where字句寫法，必須考慮語句順序，應該根據索引順序、範圍大小來確定條件子句的前後順序，盡可能的讓欄位順序與索引順序相一致，範圍從大到小。
3.1.5 不要在where子句中的“=”左邊進行函數、算術運算或其他運算式運算，否則系統將可能無法正確使用索引。
3.1.6 儘量使用exists代替select count(1)來判斷是否存在記錄，count函數只有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。
3.1.7 儘量使用“>=”，不要使用“>”。
3.1.8 注意一些or子句和union子句之間的替換
3.1.9 注意表之間連接的資料類型，避免不同類型資料之間的連接。
3.1.10 注意存儲過程中參數和資料類型的關係。
3.1.11 注意insert、update操作的資料量，防止與其他應用衝突。如果資料量超過200個資料頁面（400k），那麼系統將會進行鎖升級，頁級鎖會升級成表級鎖。
3.2 索引的使用規範：
3.2.1 索引的創建要與應用結合考慮，建議大的OLTP表不要超過6個索引。
3.2.2 盡可能的使用索引欄位作為查詢條件，尤其是聚簇索引，必要時可以通過index index_name來強制指定索引
3.2.3 避免對大表查詢時進行table scan，必要時考慮新建索引。
3.2.4 在使用索引欄位作為條件時，如果該索引是聯合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用。
3.2.5 要注意索引的維護，週期性重建索引，重新編譯存儲過程。
3.3 tempdb的使用規範：
3.3.1 儘量避免使用distinct、order by、group by、having、join、***pute，因為這些語句會加重tempdb的負擔。
3.3.2 避免頻繁創建和刪除臨時表，減少系統表資源的消耗。
3.3.3 在新建臨時表時，如果一次性插入資料量很大，那麼可以使用select into代替create table，避免log，提高速度；如果資料量不大，為了緩和系統表的資源，建議先create table，然後insert。
3.3.4 如果臨時表的資料量較大，需要建立索引，那麼應該將創建臨時表和建立索引的過程放在單獨一個子存儲過程中，這樣才能保證系統能夠很好的使用到該臨時表的索引。
3.3.5 如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先truncate table，然後drop table，這樣可以避免系統表的較長時間鎖定。
3.3.6 慎用大的臨時表與其他大表的連接查詢和修改，減低系統表負擔，因為這種操作會在一條語句中多次使用tempdb的系統表。
3.4 合理的演算法使用：
根據上面已提到的SQL優化技術和ASE Tuning手冊中的SQL優化內容,結合實際應用,採用多種演算法進行比較,以獲得消耗資源最少、效率最高的方法。具體可用ASE調優命令：set statistics io on, set statistics time on , set showplan on 等。
訂閱：
文章 (Atom)