oracle學(xué)習(xí)筆記.doc

1、 sqlload導(dǎo)入數(shù)據(jù)1.1、sql loader 的特點(diǎn)oracle自己帶了很多的工具可以用來(lái)進(jìn)行數(shù)據(jù)的遷移、備份和恢復(fù)等工作。但是每個(gè)工具都有自己的特點(diǎn)。比如說(shuō)exp和imp可以對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行導(dǎo)出和導(dǎo)出的工作，是一種很好的數(shù)據(jù)庫(kù)備份和恢復(fù)的工具，因此主要用在數(shù)據(jù)庫(kù)的熱備份和恢復(fù)方面。有著速度快，使用簡(jiǎn)單，快捷的優(yōu)點(diǎn)；同時(shí)也有一些缺點(diǎn)，比如在不同版本數(shù)據(jù)庫(kù)之間的導(dǎo)出、導(dǎo)入的過(guò)程之中，總會(huì)出現(xiàn)這樣或者那樣的問(wèn)題，這個(gè)也許是oracle公司自己產(chǎn)品的兼容性的問(wèn)題吧。sql loader 工具卻沒(méi)有這方面的問(wèn)題，它可以把一些以文本格式存放的數(shù)據(jù)順利的導(dǎo)入到oracle數(shù)據(jù)庫(kù)中，是一種在不同數(shù)據(jù)庫(kù)之間進(jìn)行數(shù)據(jù)遷移的非常方便而且通用的工具。缺點(diǎn)就速度比較慢，另外對(duì)blob等類(lèi)型的數(shù)據(jù)就有點(diǎn)麻煩了。1.2、 sql loader使用例子創(chuàng)建一個(gè)假日表create table SOURCE_JRB( JR DATE, DQDH VARCHAR2(2), HBZL VARCHAR2(2), SFJR VARCHAR2(1), JLZT VARCHAR2(1), BUSI_DATA_DATE DATE)1.3、控件文件jrb.ctlUNRECOVERABLE -關(guān)閉日志，不可恢復(fù)Load data -控制文件標(biāo)識(shí)INFILE * -要輸入的數(shù)據(jù)文件名為這里在命令中指定，所以用*代替fix 15 -指定一行記錄的字符數(shù)，包括換行符INTO TABLE source_jrb -指定表名APPEND -向表中追加記錄(JR position(1:8) DATE YYYY-MM-DD , -定長(zhǎng)指定1-8位字符為列jr,類(lèi)型為dateDQDH position(9:10),HBZL position(11:12),SFJR position(13:13),JLZT position(14:14),BUSI_DATA_DATE position(1:2) to_date(20110428) ,YYYY-MM-DD)a、insert，為缺省方式，在數(shù)據(jù)裝載開(kāi)始時(shí)要求表為空 b、append，在表中追加新記錄 c、replace，刪除舊記錄，替換成新裝載的記錄 d、truncate，同上Data文件:jrb.dat2003010100001120080207000011200802080000112008021100001120080212000011200804040000112008050400000120080501000011200805020000112008060900001120080915000011200809270000011.4、執(zhí)行命令將jrb.ctl文件和jrb0.dat文件放在e:tem目錄下sqlldr arms/armsarmsdb control=e:temjrb.ctl data=e:temJRB0.dat log=e:temJRB.log bad=e:temJRB.bad direct=truearms/armsarmsdb這里數(shù)據(jù)庫(kù)用戶(hù)名：arms；密碼:arms；數(shù)據(jù)庫(kù)：armsdb；2、oracle對(duì)應(yīng)用程序性能分析有的時(shí)候我們發(fā)現(xiàn)一個(gè)應(yīng)用系統(tǒng)運(yùn)行很慢，運(yùn)行很慢主要是由于跟數(shù)據(jù)庫(kù)有關(guān)，致使數(shù)據(jù)很長(zhǎng)時(shí)間沒(méi)有返回?cái)?shù)據(jù)，這個(gè)時(shí)候我們可能去查oracle到底哪里有問(wèn)題，是什么原因?qū)е滤\(yùn)行非常慢。其實(shí)很多時(shí)間都是由于程序人員寫(xiě)的sql執(zhí)行效率非常低或者某個(gè)時(shí)間段訪問(wèn)量忽然猛增或者其他原因，那么我們?nèi)绾味ㄎ坏绞悄囊粋€(gè)SQL或是哪一個(gè)時(shí)間段有問(wèn)題呢？因?yàn)槿绻覀兌ㄎ坏侥硞€(gè)時(shí)間段比其他時(shí)間段cpu，內(nèi)存都相當(dāng)耗時(shí)，我們就可以查看我們應(yīng)用系統(tǒng)在這個(gè)時(shí)間段做什么，從而分析出系統(tǒng)運(yùn)行慢的原因；我們也可以分析某個(gè)sql的運(yùn)行時(shí)間，有時(shí)一段sql跑上幾個(gè)小時(shí)都沒(méi)跑出來(lái)。如果我們能定位到這些sql，對(duì)我們改善系統(tǒng)性能也是有很大幫助。我們這里通個(gè)分析addmrt和awrrpt來(lái)粗略分析數(shù)據(jù)庫(kù)性能。在awrrpt這個(gè)里面，我們可以得到耗時(shí)前五的事件。調(diào)取addmrt和awrrpt文件登錄oracle服務(wù)器進(jìn)行以下目錄：cd $ORACLE_HOMEcd rdbms/admin找到awrrpt.sqlls awrrpt.sql以數(shù)據(jù)庫(kù)管理員進(jìn)入sqlplussqlplus / as sysdba執(zhí)行wrrpt.sql/database/product/10gR2/db_1/rdbms/admin/awrrpt.sql在上圖輸入格式類(lèi)型，我們就把這些報(bào)告保存為txt格式的，默認(rèn)保存為html格式。輸完后回車(chē)輸入報(bào)告天數(shù)，我們輸入1，輸入1表示他將把最近1天的報(bào)告展示出來(lái)，如果是2那就是近兩天的。這里輸入開(kāi)始的一個(gè)snap id，在這里我們可以看到他是每隔一小時(shí)記一次快照，我們可以修改這個(gè)快照間隔?；剀?chē)后，文件名我們這里不輸，默認(rèn)就可以了?；剀?chē)后，在當(dāng)前執(zhí)行sql的目錄中就生成了一個(gè)awrrpt_1_14078-14079.txt的文件，我們通過(guò)ftp把文件下載到本地(這里oracle服務(wù)器在AIX機(jī)器上)選擇要下載到本地的目錄(我這里e:tem)連接FTPftp 90輸入用戶(hù)名密碼。登到文件所在路徑：cd /database/product/10gR2/db_1/rdbms/admin下載文件get awrrpt_1_14078_14079.txt awrrpt_1_14078_14079.txt3、oracle執(zhí)行計(jì)劃通過(guò)oracle執(zhí)行計(jì)劃我們可以查詢(xún)oracle數(shù)據(jù)庫(kù)在執(zhí)行一個(gè)sql時(shí)所花費(fèi)的代價(jià)，它詳細(xì)地列出了一個(gè)sql執(zhí)行各部分時(shí)所耗費(fèi)的代價(jià)，根據(jù)這些信息，可以為我們優(yōu)化sql提供幫助。3.1、相關(guān)的概念Driving Table驅(qū)動(dòng)表：又稱(chēng)為外層表OUTER TABLE。這個(gè)概念用于嵌套與HASH連接中。如果該row source返回較多的行數(shù)據(jù)，則對(duì)所有的后續(xù)操作有負(fù)面影響。如果一個(gè)大表在WHERE條件有限制條件，則該大表作為驅(qū)動(dòng)表也是合適的，所以并不是只有小表可以作為驅(qū)動(dòng)表，正確說(shuō)法應(yīng)該為應(yīng)用查詢(xún)的限制條件后，返回較少行源的表作為驅(qū)動(dòng)表。在執(zhí)行計(jì)劃中，應(yīng)該為靠上的那個(gè)row source，在后面描述中，一般將該表稱(chēng)為連接操作的row source 1。Probed Table(被探查表)：該表又稱(chēng)為內(nèi)層表INNER TABLE。在我們從驅(qū)動(dòng)表中得到具體一行的數(shù)據(jù)后，在該表中尋找符合連接條件的行。所以該表應(yīng)當(dāng)為大表實(shí)際上應(yīng)該為返回較大row source的表 且相應(yīng)列上應(yīng)該有索引。在后面的描述中，一般將該表稱(chēng)為連接操作的row source 2.組合索引(concatenated index)：由多個(gè)列構(gòu)成的索引，如create index idx_emp on emp(col1,col2)。在組合索引中有一個(gè)重要的概念：引導(dǎo)列(leading column)，在上面的例子中，col1列為引導(dǎo)列。當(dāng)我們進(jìn)行查詢(xún)時(shí)可以使用where col1 = ?，也可以使用where col1 = ? and col2 = ?，都會(huì)使用索引，但是where col2 = ?查詢(xún)就不會(huì)使用該索引。所以限制條件中包含引導(dǎo)列時(shí)，該限制條件才會(huì)使用該組合索引?？蛇x擇性(selectivity)：比較一下列中唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷該列的可選擇性。如果該列的“唯一鍵的數(shù)量/表中的行數(shù)”的比值越接近1，則該列的可選擇性越高，該列就越適合創(chuàng)建索引，同樣索引的可選擇性也越高。在可選擇性高的列上進(jìn)行查詢(xún)時(shí)，返回的數(shù)據(jù)就較少，比較適合使用索引查詢(xún)。3.2、oracle訪問(wèn)數(shù)據(jù)的存取方法1）全表掃描（Full Table Scans， FTS）為實(shí)現(xiàn)全表掃描，Oracle讀取表中所有的行，并檢查每一行是否滿(mǎn)足語(yǔ)句的WHERE限制條件.一個(gè)多塊讀操作可以使一次I/O能讀取多塊數(shù)據(jù)塊，而不是只讀取一個(gè)數(shù)據(jù)塊，這極大的減少了I/O總次數(shù)，提高了系統(tǒng)的吞吐量，所以利用多塊讀的方法可以十分高效地實(shí)現(xiàn)全表掃描，而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問(wèn)模式下，每個(gè)數(shù)據(jù)塊只被讀一次。使用FTS的前提條件：在較大的表上不建議使用全表掃描，除非取出數(shù)據(jù)的比較多，超過(guò)總量的5%-10%，或你想使用并行查詢(xún)功能時(shí)。3.3、索引掃描（Index Scan）我們先通過(guò)index查找到數(shù)據(jù)對(duì)應(yīng)的rowid值(對(duì)于非唯一索引可能返回多個(gè)rowid值)，然后根據(jù)rowid直接從表中得到具體的數(shù)據(jù)，這種查找方式稱(chēng)為索引掃描或索引查找(index lookup)。一個(gè)rowid唯一的表示一行數(shù)據(jù)，該行對(duì)應(yīng)的數(shù)據(jù)塊是通過(guò)一次i/o得到的，在此情況下該次i/o只會(huì)讀取一個(gè)數(shù)據(jù)庫(kù)塊。索引掃描由2步組成：1 掃描索引得到對(duì)應(yīng)的rowid值。 2 通過(guò)找到的rowid從表中讀出具體數(shù)據(jù)。每步都是單獨(dú)的一次I/O，但是對(duì)于索引，由于經(jīng)常使用，絕大多數(shù)都已經(jīng)CACHE到內(nèi)存中，所以第1步的I/O經(jīng)常是邏輯I/O，即數(shù)據(jù)可以從內(nèi)存中得到。但是對(duì)于第2步來(lái)說(shuō)，如果表比較大，則其數(shù)據(jù)不可能全在內(nèi)存中，所以其I/O很有可能是物理I/O，這是一個(gè)機(jī)械操作，相對(duì)邏輯I/O來(lái)說(shuō)，是極其費(fèi)時(shí)間的。所以如果對(duì)大表進(jìn)行索引掃描，取出的數(shù)據(jù)如果大于總量的5%-10%，使用索引掃描會(huì)效率下降很多。4、表之間的連接4.1、嵌套循環(huán)(Nested Loops)這個(gè)連接方法有驅(qū)動(dòng)表(外部表)的概念。其實(shí)，該連接過(guò)程就是一個(gè)2層嵌套循環(huán)，所以外層循環(huán)的次數(shù)越少越好，這也就是我們?yōu)槭裁磳⑿”砘蚍祷剌^小 row source的表作為驅(qū)動(dòng)表(用于外層循環(huán))的理論依據(jù)。如果使用這種方法，決定使用哪個(gè)表作為驅(qū)動(dòng)表很重要。有時(shí)如果驅(qū)動(dòng)表選擇不正確，將會(huì)導(dǎo)致語(yǔ)句的性能很差、很差。從內(nèi)部連接過(guò)程來(lái)看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此時(shí)保持row source1盡可能的小與高效的訪問(wèn)row source2(一般通過(guò)索引實(shí)現(xiàn))是影響這個(gè)連接效率的關(guān)鍵問(wèn)題。這只是理論指導(dǎo)原則，目的是使整個(gè)連接操作產(chǎn)生最少的物理I/O次數(shù)。在上面的連接過(guò)程中，我們稱(chēng)Row source1為驅(qū)動(dòng)表或外部表。Row Source2被稱(chēng)為被探查表或內(nèi)部表。在NESTED LOOPS連接中，Oracle讀取row source1中的每一行，然后在row sourc2中檢查是否有匹配的行，所有被匹配的行都被放到結(jié)果集中，然后處理row source1中的下一行。這個(gè)過(guò)程一直繼續(xù)，直到row source1中的所有行都被處理。這是從連接操作中可以得到第一個(gè)匹配行的最快的方法之一，這種類(lèi)型的連接可以用在需要快速響應(yīng)的語(yǔ)句中，以響應(yīng)速度為主要目標(biāo)。如果driving row source(外部表)比較小，并且在inner row source(內(nèi)部表)上有唯一索引，或有高選擇性非唯一索引時(shí)，使用這種方法可以得到較好的效率。NESTED LOOPS有其它連接方法沒(méi)有的的一個(gè)優(yōu)點(diǎn)是：可以先返回已經(jīng)連接的行，而不必等待所有的連接操作處理完才返回?cái)?shù)據(jù)，這可以實(shí)現(xiàn)快速的響應(yīng)時(shí)間。如果不使用并行操作，最好的驅(qū)動(dòng)表是那些應(yīng)用了where 限制條件后，可以返回較少行數(shù)據(jù)的的表，所以大表也可能稱(chēng)為驅(qū)動(dòng)表，關(guān)鍵看限制條件。對(duì)于并行查詢(xún)，我們經(jīng)常選擇大表作為驅(qū)動(dòng)表，因?yàn)榇蟊砜梢猿浞掷貌⑿泄δ?。?dāng)然，有時(shí)對(duì)查詢(xún)使用并行操作并不一定會(huì)比查詢(xún)不使用并行操作效率高，因?yàn)樽詈罂赡苊總€(gè)表只有很少的行符合限制條件，而且還要看你的硬件配置是否可以支持并行(如是否有多個(gè)CPU，多個(gè)硬盤(pán)控制器)，所以要具體問(wèn)題具體對(duì)待。5、如何查看oracle執(zhí)行計(jì)劃我們一般都用plsql工具來(lái)查看oracle的執(zhí)行計(jì)劃，在pl/sql的SQL窗口中寫(xiě)上我們要分析的SQL語(yǔ)句，按F5可以查看他的執(zhí)行計(jì)劃。SQL語(yǔ)句：select a.dqdh | a.jgdh,a.khrq ,a.khrq, a.dqdh, a.jgdh, a.zhye, a.xzxh, a.zhdh from SOURCE_DXZDJB a join ARMS_CFG_GZTS b on a.dqdh = b.dqdh and b.tyrq=to_Date(3000/12/31,yyyy/mm/dd) join ARMS_ORGAN_ENTRYID e on a.dqdh=an_no and e.entry_id=10033 where a.jlzt = 1 and a.zhye 0 and a.xzrq =to_date(2010-01-01,yyyy-mm-dd)解釋計(jì)劃：這里為了好解釋?zhuān)竺娴募t色字體加了一個(gè)行號(hào)。對(duì)于這個(gè)解釋計(jì)劃如何看，哪一步先執(zhí)行哪一步后執(zhí)行，原則是最右邊最上面的先執(zhí)行。上面這個(gè)圖最右邊是第8行，對(duì)象名稱(chēng)是idx_dqdh_dxzdjb，描述是index range scan。意思是索引掃描，再看他的上面7行，對(duì)象名稱(chēng)是source_dxzdjb，這兩行合起就是查source_dxzdjb這張