Hệ quản trị cơ sở dữ liệu Oracle 9i (Tập 2): Phần 1

Chia sẻ: _ _ | Ngày: | Loại File: PDF | Số trang:122

Thêm vào BST

Báo xấu

15
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Mục đích của cuốn sách "Thành thạo Oracle 9i - Quản trị cơ sở dữ liệu (Tập 2): Phần 1" nhằm hướng dẫn bạn cách nạp, sao lưu và chuyển đổi dữ liệu, sử dụng tiện ích Expor và Import thực hiện hiệu chỉnh hiệu suất thực thi và xử lý sự cố cho CSDL thực... Mời các bạn cùng tham khảo nội dung chi tiết phần 1 cuốn sách tại đây!

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Hệ quản trị cơ sở dữ liệu Oracle 9i (Tập 2): Phần 1

OfcACUS
IBnj Nguyen Thien BSng ~ Nguyen Ngoc Minh lM »»U K Hoang Dtfc Hai Hieu dinh: P h u W ig Lan Thanh thao OfcACU 9i Quart trj co sd dit lieu (Tap 2) Nha xuat ban Thong Ke
L dl TI/A LCJl Tl/A L&m cdch n&o d£ trd th&nh nh& qu in tri C S D L Oracle? Ban c6 the p h ii tham gia cdc k h 6a hoc v§ Oracle, tim hieu tren cdc W eb site, c&c t&i li
LCfl NGO LCJl NGO K in h thifa quy Ban doc gan xa, Ban xuat bdn M K .P U B trtfdc het xin b&y to lon g b iet on va niem vinh hanh trUdc n h i?t tin h cua dong dao Ban doc doi vdi tu s&ch M K .P U B tron g thdi gian qua. Khau hieu cua chung toi Ik: * Lao dong khoa hoc n gh iem tuc. * Chat lirong vk n gay cang chat luong hon. * T a t ca v i Ban doc. R at nhieu Ban doc da guTi m a il cho chung toi ddng gop nhieu y kien quy bau cho tu sdch. Ban xuat ban M K .P U B xin duoc kinh mcfi quy Ban doc tham gia cung nang cao chat lirong tu s&ch cua chung ta. T ro n g qud trinh doc, xin cac Ban ghi chu lai ckc sai sot (du nho, ldn) cua cuon s&ch hoac ckc nhan x et cua rie n g Ban. Sau do xin gui ve dia chi: E -m ail: m k.book@m inhkhai.com .vn - m k.pub@ m inhkhai.com .vn Hoac gift ve: N h a sach M inh K h ai 249 N g u y in T h i M inh K hai, Q.I, Tp. H o Chi' M inh Neu Ban ghi chu true tiep len cuon sach, roi gui cuon sach do cho chung toi thi chung toi se xin hoan lai cudc phi buu dien va gui lai cho Ban cuon sach khac. Chung toi xin gift tang mot cuon sach cua tu sach M K .P U B tuy chon lira cua Ban theo m ot danh muc thich hop se duoc gui tdi Ban. V di muc dich ngay cang nang cao chat luong cua tu sach M K .P U B , chung toi rat m ong nhan duoc sU hop tac cua quy Ban doc g^n xa. " M K .P U B va Ban doc cung la m ! " M K .P U B
Myc Lye MUC LUC LCfl t U a ........................................................................................ 3 LC*I N G O ....................................................................................... 4 MUC L U C ......................................................................................5 PHAN IV: NAP, SAO LUU VA KHOI PHUC D0 LIE U ....................27 CHUONG 11: NAP VA CHUYEN DOI D0 LIEU ............................. 29 1. Gidri thieu viec nap dCf lieu va chuyen doi duf lie u .................................. 29 1.1. N a p dU lieu bdng E T L ....................................................................30 1.2. N a p dU lie u bdng O racle Warehouse B u ild e r.............................. 32 2. Tien ich S q l*L o a d e r.................................................................................. 32 2.1. Cac p h ua n g p lia p nap di2 lieu khac n h a u .................................... 33 2.2. Cac fd e dieu klxien cua S q l* L o a d e r .............................................. 35 2.3. Cac menh de lien quan den viec nap dtf lie u .............................. 36 2.4. Cac menh de lien quan den fde dU l i e u .......................................37 2.5. K ha i bao file dU lie u ........................................................................ 37 2.6. Cac rccord vat ly va lo g ic ............................................................... 37 2.7. D in k dang record ............................................................................. 39 2.8. Cac menh de anli xa bdng - F ie ld ................................................ 39 3. Tao ra duf lieu trong qua trinh n a p .......................................................... 46 4. Khcti dong S ql*L oa d er............................................................................... 46 5. Cai gi nkm trong file log cua trinh Load er............................................. 47 5.1. S it d u n g ma lenh trd v e ..................................................................49 5.2. S ii d u n g tuy chon D ire ct L o a d ....................................................... 49 5.3. T h ie t lap cac tuy chon cho viec nap true tic p .............................. 51 5.4. N a p tri/c ticp va quan ly cac C onstraint I t r ig g e r ........................52 5.5. N h iln g nico vat cho viec si2 dung S q l* L o a d e r ............................. 53 6 . Su dung External Tables de nap duf lie u ................................................. 54 6 . 1 . S ii d u n g E x tern a l Tables cho vicc nap dU l i e u ........................... 55
MVCLVC 6.2. Cac th on g so truy c&p quan tro n g doi vdi E x te rn a l T a b le .........59 6.3. S it dung S q l*L o a d e r de too ra cac phat bi£u tao E xtern a l T a b le ...................................................................................60 7. Chuyen doi dCf li§ u ..................................................................................... 63 7.1. Lay dU lieu t il cac bdng co s d n ........................................................64 7.2. S it du ng P L / S Q L de chuyen d oi dU lie u ...................................... 65 7.3. S it d u n g cac ham cua bdng cho viec chuyen d o i d < lie u ............ 70 2 7.4. Si2 du ng m ot ham cua bdng............................................................. 72 7.5. SU dung ham bdng de khai thac dU lieu cua Web S e rv ice .........76 8 . M ot so ky thuat nap dOf lieu hOtu l'ch cua S q l*L o a d e r............................. 77 8.1. S it du ng m enh de W hen tro n g qua trin h nap dU lie u ................ 77 8.2. N a p ten ngudi d u n g vao tro n g m o t b d n g ...................................... 78 8.3. N a p cac F ie ld dU lieu lan vao tro n g m o t b d n g ............................ 78 8.4. N a p m ot so cua Sequence vao tro n g m o t b d n g ............................ 79 8.5. N a p dQ lie u t il m ot bdng vao tro n g m o t file A S C I I .....................79 8 . 6 . X a c d in h va g o bd dU lieu tr iin g ......................................................80 8.7. X oa cac c h i m uc trilac khi nap dU lie u h a n g lo a t........................ 80 8 .8 . N a p d d lieu vao n h ieu b d n g ............................................................ 80 8.9. Bay lo i ma le n li tit S q l* L o a d e r........................................................ 81 8.10. N a p dU lieu X M L vao tron g co so dU lieu X M L cua O r a c le .... 81 9. Tom tat....................................................................................81 CHl/dNG 12: SLf DUNG TIEN ICH EXPORT, IM PO R T.................. 83 1. Nhap va xuat dCf lieu .................................................................................. 84 2 . Suf dung tien ich E x p o r t............................................................................ 85 2.1. Cac th on g so cua E x p o r t...................................................................85 2.2. Cac tuy chon x u a t.............................................................................. 88 2.3. Cac th on g so lien quan den F la s h b a ck .......................................... 9 / 2.4. K ha i dong tien ich E x p o rt................................................................ 02 2.5. S it d u ng ddng lenli de thuc lxien viec x u a t................................... 04 2.6. Cac th on g bao va cac ma lo i cua E x p o r t...................................... 05
M yc L y c 7 W 3. SCf dung ti§n ich Im p o rt............................................................................97 3.1. T h il tu nh& p......................................................................................97 3.2. Cac th on g so I m p o r t ........................................................................97 3.3. Cac thdng so li&n quan diin f i l e .................................................... 98 3.4. C h i d in h cac d oi tuang duoc nh&p................................................ 98 3.5. Cac th on g so cho che do Im p o r t.................................................. 100 3.6. Cac tuy chon nh&p......................................................................... 101 3.7. T o i Uu cac con so th on g k c ........................................................... 105 3.8. Cac th on g so lien quan den viec ticp tuc thuc th i l a i .............. 106 3.9. K hdi d on g tien ich nh&p................................................................107 3.10. Cac cau th on g bao va ma lo i tro n g trin h I m p o r t ................... 109 4. Chuyen doi khong gian bang.................................................................. 109 4.1. S d d u n g chuyen d oi khong gian b d n g ........................................ 110 4.2. Chuyen d o i m ot k h ong g ia n b d n g ............................................... 110 4.3. Tao tap k h on g gia n bdng co the chuyen doi d u oc.................... I l l 4.4. X u dt th on g tin til dien dU lieu cho cac khong gia n b d n g ....... 112 4.5. Sao chep cac file xuat va cac file khong gian bdng toi co so dtf lieu d ic h ........................................................................................... 113 4.6. T h u c h icn nhap kh ong gian bdng............................................... 114 5. M ot so ky thuat nhap vk xu at................................................................ 115 5.1. T ra n h nhUng I 6 i nli& p/xuat thong tliU ong................................ 116 5.2. Si? d u n g th on g so th on g kc th ich h o p ......................................... 117 5.3. T im hieu cac g io i han cua cac tien ich nhap I x u a t.................. 117 5.4. T liU c h icn viec d i chuyen co so dU lieu co hieu qua................. 117 5.5. K h d i p h u c m ot co so d d lieu bang cach sU du ng cac tien ich xuat va n h a p ............................................................................................ 118 5.6. X u a t ca nhap co so dU lieu I o n .................................................... 119 5.7. D am bao tin h chat hop Ic cua viec x u a t.....................................119 5.8. T o i Uu xu at va n h a p ...................................................................... 120 6 . Tom t i t ......................................................................................................121
MUC LUC C H U O N G 13: S A O L U U C O s 6 D Lf L I E U ______________ ___ ______ ____ 123 1 . Sao luu C SD L O ra cle...............................................................................124 1.1. Cac thu&t ngU sao lu u .....................................................................124 1.2. C hien lilac sao lu u ........................................................................... 126 1.3. Sao luu cac tap tin k h a c .................................................................128 1.4. Cac phuang phdp sao lu u ................................................................129 2. Tien Ich R M A N cua O racle..................................................................... 129 2.1. L o i ich cua R M A N .......................................................................... 130 2.2. K ien true R M A N ..............................................................................131 2.3. Ket noi tcri R M A N ........................................................................... 132 2.4. Tao script bdng R M A N ................................................................... 134 2.5. Cac tliuat ngCt quan tron g cua R M A N .......................................... 136 2.6. Cac lenli R M A N ...............................................................................137 2.7. Cau h in h R M A N ..............................................................................143 2.8. Tao recovery C ata log....................................................................... 151 2.9. M o t so v i du sao luu khac d u ng R M A N .......................................158 2.10. ThUc hien sao lUu tang (ch i sao lilu phdn thay d o i) ............... 162 2.11. D u n g R M A N thuc hien sao luu true tuyen................................ 165 3. SCf dung lcrp quan ly phuefng tien vcti R M A N ....................................... 166 3.1. Si2 dung Legato S in g le S erver Version vdi R M A N ....................167 3.2. K ien true L S S V ................................................................................ j q j 3.3. Cai dat L S S V ................................................................................... i ^9 3.4. SU dung L S S V ................................................................................. j ~q 3.5. L S S V va R M A N ...............................................................................j j q 4. Sao luu bang cac lenh he dieu h a n h ...................................................... 175 4.1. Sao luu toan phdn C S D L ............................................................... j 75 4.2. Sao luu tting phdn C S D L .............................................................. jc q 4.3. G iam sat cac sao luu true tuyen do ngudi du ng quan ly ..... ;5 7 5. N ang cao kha nang bao ve duf lieu de khoi phuc................................. 152 5.1. O racle Data G uard va C S D L d u p h o n g ..................................... 75-3
M yc L y c 5.2. C S D L d u ph dng lo gic va vat ly ................................................... 184 5.3. Che do bao ve.................................................................................. 185 6 . Sai lac C SD L vk kiem tra ckc sao luu................................................... 185 6.1. Sai I clc khdi dti. lieu O racle........................................................... 186 6.2. D o tim khdi dU. lieu O racle sai la c .............................................. 186 6.3. K iem tra cac sao luu cua R M A N ................................................. 188 6.4. K hdi tao H a rd cua O r a c le ............................................................ 190 7. K y thuat vk xuf ly su co............................................................................ 190 7.1. G ia m sat R M A N ............................................................................. 190 7.2. L d i O R A _00 2 57 ............................................................................... 191 8 . K et chucfng................................................................................................ 191 CHl/CfNG 14: P H U C H O I C 0 s 6 D 0 L I E U ............................................193 1. M ot so kieu hu hong C S D L .................................................................... 194 1.1. H u h on g he th o n g .......................................................................... 194 1.2. K hdi dong n lia n li ph uc lio i l o i ..................................................... 195 1.3. Cac tham lioa d tru n g tarn di2 lie u .............................................. 196 1.4. L d i do con n g u d i............................................................................ 196 1.5. H u h on g phUcmg tien luu tri2 ....................................................... 197 2. Cac kieu phuc hoi C S D L ......................................................................... 198 2.1. K hdi p h u c va p h u c h oi C S D L ...................................................... 199 2.2. T h a i gia n can th iet de ph uc h o i................................................... 199 2.3. P h u c h oi hoan toan va khong hoan to a n ................................... 199 2.4. P h u c h oi d on g va m d ..................................................................... 200 3. Giam kha nkng t 6n thucmg cua he th o n g ............................................. 200 4. Thuc hien phuc hoi bang R M A N ........................................................... 201 4.1. T a i sao R M A N la cong cu ph uc hoi tot n h a t............................. 202 4.2. P h u c h oi C S D L bdng tay...............................................................204 5. M ot so kich ban phuc hoi phucfng tien tieu bieu.................................. 205 5.1. P h u c hoi hoan toan m ot C S D L ....................................................205 5.2. P h u c h oi do ngudi d u n g quan ly ..................................................207
B P 10____________________________________________________ MUC LUC 5.3. P h u c h oi k h ong g ia n b d n g ............................................................. 208 5.4. P h u c hSi do nguai d u n g quan l y .................................................. 209 5.5. P h u c hSi tap tin dQ lie u ..................................................................209 5.6. P h u c h oi do nguai d u n g quan l y .................................................. 210 5.7. P h u c h oi khong hoan to a n ............................................................. 210 5.8. P h u c hdi khong hoan toan do ngudi d u n g quan l y ...................212 5.9. P h u c h oi t&p tin dieu k h ie n ........................................................... 213 5.10. P h u c h oi do nguai d u n g quan l y ................................................ 214 5.11. P h u c h oi tap tin dU lieu ma kh ong co ban sao lu u ................. 219 5.12. D u n g p h u c h oi do ngUcri d u n g quan ly ...................................... 220 6 . Tao ban sao C S D L ................................................................................... 220 6.1. D u n g R M A N ......................................................................................220 6.2. Tao ban sao C S D L bdng tay.......................................................... 221 7. Cac ky thuat cho Granular R eco very..................................................... 222 7.1. P h u c h oi khong gian bdng den m ot d iem th oi g ia n ..................222 7.2. G h i bo s u n g .......................................................................................226 7.3. T ric h til dien dU lie u ....................................................................... 226 7.4. P h ie n lam viec cua L o g m in e r ........................................................ 227 7.5. S d du ng L o g m in e r de phan ticli R edo L o g ................................. 228 8 . SCra chOfa sai lac dCr lieu va phuc hoi thu................................................ 232 8.1. P h u c lidi khoi dU lie u ...................................................................... 232 8.2. P h u c hoi thvC..................................................................................... 233 9. Xtf ly sU co khi phuc h o i.......................................................................... 234 9.1. Ld i O R A -O l 194................................................................................. 234 9.2. Ld i O R A -O l 152................................................................................. 235 9.3. Ld i O R A -0 0 37 6................................................................................. 236 10. K et chucmg.............................................................................................. 237
M yc Lyc PHAN V: QUAN LY QUA TRINH HOAT DONG CUA CCf SCf D lf LIEU ORACLE 239 CHUGNG 15: QUAN LY QUA TRINH HOAT DONG CUA CO S6 Dtf LIEU 241 1 . C£c ti'nh n&ng quan ly tu dong cua O racle............................................242 1 . 1 . Quan ly hoan toan khong g ia n bdng cuc b o .............................. 242 1.2. T u d on g n oi ron g vi t r i lUu t r d .................................................... 243 1.3. T u don g quan ly kh ong gia n cua S eg m en t.................................244 1.4. Quan ly tu dong viec U n d o ...........................................................245 2. Tam ngiing cap phat khong g ia n ............................................................250 2.1. Cac loai phdt bieu R esu m a ble...................................................... 254 2.2. Ld i O U T -O F -S P A C E ..................................................................... 255 2.3. Cac ldi lien quan den khong gia n cap phdt cua ngudi d u n g ... 256 2.4. Cac qua trin h hoat dong R esum able............................................256 3. Tu dong hoa vk theo doi thfri gian khoi phuc........................................256 4. Quan ly file d l dang bang O M F ..............................................................259 4.1. L o i ich cua viec sd dung O M F ..................................................... 260 4.2. Tao cac file do O racle quan l y ..................................................... 260 5. Kha nang true tuyen cua Oracle 9 i.........................................................267 5.1. To chdc la i dd lieu trUc tuyen...................................................... 267 5.2. S d d u ng O E M de thuc hien viec to chdc la i true tu yen...........267 5.3. K iem tra tinh hop le cua phdn tich trUc tuyen.......................... 269 5.4. Xay dung la i ch i m uc trUc tuyen.................................................. 270 5.5. Dinlx nghia la i bdng trUc tu y e n ................................................... 270 6 . Viec dinh nghia lai true tuyen co the lam duoc nhCmg gl?................... 270 6.1. M o t v i du d in h nghia bdng trUc tu y e n ....................................... 271 6.2. K iem tra tinh thich hop cua bdng................................................ 272 6.3. Tao bdng ta m ...................................................................................272 6.4. Tao cac ra n g buoc va cac trig g e r tren bdng ta m .......................276 6.5. D on g bo bdng tam va bdng n g u o n ...............................................276
M ]® 12 MUC LUC 6 .6 . H oan tat qua trin h d in h n gh ia la i b d n g ..................................... 277 6.7. Quan ly ngudn tai nguyen d o n g ................................................... 278 6. 8 . Chuyen d oi cac gia o tac chay la u ................................................. 279 6.9. Gicri han so luang giao tac dai bdng vicc xcp hang qua trin h hoat d o n g ......................................................................................... 280 6.10. G iai han tliai gia n thuc th i Ion nhat cho m ot gia o ta c ........... 280 6.11. S d dung m ax_estim atcd_excc_tim e Resource P la n D ire c tiv e .280 6.12. S d dung U ndo P o o l Resource P la n D ire c tiv e ........................... 281 6.13. Thay doi Block ca so dtf lieu true tuyen .................................... 281 6.14. Cau h in h lai bo nha d o n g ............................................................ 283 6.15. Quan ly true tuyen viec si2 dung co so dQ lieu am tlia m ........288 6.16. Tam ngUng co so dtf l i e u ............................................................. 289 7. Quan ly cac Redo Log true tuyen............................................................ 290 7.1. Hardware M ir r o r in g khac vdi O racle M u ltip le x in g ...................291 7.2. Tao cac n liom Redo L o g trUc tu yen .............................................. 291 7.3. Th em nhom Redo L o g ..................................................................... 291 7.4. Dat lai ten cac file Redo L o g ......................................................... 292 7.5. Xoa cac Redo L o g trUc tu y e n ......................................................... 293 7.6. Viec h u hong Redo L o g true tu y e n ............................................... 293 7.7. Theo doi cac R edo L o g .................................................................... 293 7.8. Quan ly cac nhat ky da duoc lu u .................................................. 294 7.9. Quan ly m ot ket noi co so dtf l i e u ................................................ 295 7.10. Tao m ot lien ket co so dtf lieu p riv a te ........................................295 7.11. Tao m ot lien ket co so dU lieu p u b lic .........................................296 8 . Anh xa cac file cua Oracle tdi cac thiet bi vat ly .................................. 297 8.1. K ien true cua anh xa f i l e ................................................................298 8.2. T h ie t lap viec anh xa f i le ................................................................299 8.3. Cung cap th u vien anh x a .............................................................. 259 8.4. Khcri dong viec anh xa f i l e ............................................................. 259 9. Cac meo vat va xii ly cac sy co................................................................ 300
M yc L y c 9.1. Cac van d i tron g khi chuyen m ot ca so d d lieu sang d ie do “im Id n g” ................................................................................................ 300 9.2. D in h nghia la i bdng trUc tuyen tlid t b a i ....................................300 9.3. Cac van de tro n g khi xay du ng la i cac c h i m uc trUc tu y e n .... 301 10. Tom t i t ................................................................................................... 301 CHl/CfNG 16: SLf DUNG TRINH ENTERPRISE MANAGER CUA ORACLE 9i .................................................... 303 1 . Oracle Enterprise M an ager.................................................................... 304 1.1. L o i ich cua viec sd d u ng O E M de quan tr i co so dd lie u .........305 1.2. K ha nang bien d oi tron g quan ly .................................................307 1.3. H op nhat viec quan tr i co so dd l i e u .......................................... 307 1.4. Tap tru n g ve m ot d iem quan ly tren m an h in h O E M ............. 308 1.5. Quan tr i he th on g ticli h op ............................................................308 1.6. Cac he th on g khac nhau cua O E M ..............................................309 1.7. K ien true cua O E M va cac thanh p h d n ...................................... 310 2. Cau hinh O E M ......................................................................................... 312 2.1. Cau hinh In te llig e n t A g e n t ...........................................................312 2.2. Cau hinh M anagem ent S e r v e r ..................................................... 313 2.3. Cau h in h cho man h in h O E M ...................................................... 321 2.4. Cau h in h viec bao c a o ................................................................... 324 2.5. Cau h in h Web S erver cho bao cao E n terp rise M a n a g e r..........326 2.6. Chay Console td m ot trin h duyet W eb........................................ 327 3. Su dung man hinh O E M ..........................................................................328 3.1. N h a n dien cac dich v u .................................................................. 328 3.2. F ile services.ora .............................................................................. 329 4. Quan ly ccf sa duf lieu thong qua O E M .................................................... 329 4.1. Quan ly Instance cua co so dd li e u ..............................................331 4.2. Quan ly so do co so dd li e u ........................................................... 332 4.3. Quan ly van de bao mat co so dd l i e u ........................................ 332 4.4. Quan ly viec luu trd co so d d lie u ................................................ 333
W 14 M y c LUC 4.5. Quan ly co sd d d lieu phdn t a n .................................................... 334 4.6. Quan ly kho dU l i e u ........................................................................335 4 .7. Quan ly O la p ....................................................................................335 4.8. Sum m ary M a n a g e m en t.................................................................. 335 4.9. Quan ly W orkspace.......................................................................... 336 4.10. Quan ly co sd dQ lieu X M L ..........................................................336 5. Sit dung O EM W iza rd ..............................................................................337 5.1. T rin h E x p o rt and Im p o rt W iza rd ................................................. 338 5.2. T rin h A nalyze W iza rd .................................................................... 339 5.3. T rin h Backup W iz a rd .................................................................... 341 6 . Suf dung ckc tinh n&ng bko cko cua O E M ..............................................341 6.1. Cac bao cao duqc xdy d iin g trUdc va tuy b ie n ............................ 342 6.2. T ru y cdp Web S ite bao c a o ............................................................343 7. Quan ly Jobs bkng O E M .........................................................................345 7.1. X em ch i tiet J o b .............................................................................. 345 7.2. Tao m ot J o b ..................................................................................... 346 7.3. Chon co so dU. lieu va dat ten cho J o b ........................................ 346 7.4. Chon m ot tac v u .............................................................................. 347 7.5. Ldp lich va xem xet J o b ................................................................. 348 8 . Quan ly ckc sir kien bkng O E M ..............................................................349 8.1. Tao cac sU k ie n ................................................................................ 349 8.2. Chon dieu kien th it n g liie m ...........................................................350 8.3. La p lich cho cac sU k ien ................................................................. 350 9. Cau hinh e-mail vk thong bao qua may nhan t i n ................................351 9.1. Cau h in h th on g bao qua may nhdn t i n ....................................... 351 9.2. K hdi don g dich vu nhdn t i n ......................................................... 351 9.3. T h em may chu nhdn tin vao O E M ...............................................352 9.4. T h ie t ldp quyen Uu tien th on g b a o ...............................................352 9.5. Cdu h in h th on g bao e -m a il............................................................353 10. Oracle M anagem ent P a c k s .................................................................. 353
MVC LVC 10.1. O racle D ia g n otics P a ck ............................................................... 353 10.2. O racle T u n in g P a c k .....................................................................356 10.3. O racle Change M anagem ent P a c k ............................................ 357 11. Xtf ly sir co trong O E M .......................................................................... 357 12. T o m t i t ................................................................................................... 357 PHAN VI: DIEU CHINH HIEU QUA VA XLf LY SU CO VIEC TAO CO Sd DLf LIE U 359 CHUONG 17: c A l THIEN HIEU QUA CO Sd D 0 LIEU: TOI LfU CAU LENH TRUY VAN SQL 361 1 . M ot giai phap dieu chinh hieu qua cua O racle..................................... 362 1.1. M o t he tlio n g pliUcmg phap thuc h icn viec dieu cliln h hieu q u a ........................................................................................... 362 1.2. Tac d on g cua viec dieu ch in h hieu q u a ......................................363 1.3. T h u c han li ma lioa cau len li S Q L ............................................... 364 2. T oi uu quy trinh xuf ly truy van cua O racle........................................... 364 2.1. P h a n tic h ..........................................................................................365 2.2. T o i U u ..............................................................................................366 2.3. T h u c h ien truy v a n ........................................................................ 370 3. T oi uu truy van va toi uu dira tren chi p h i............................................ 371 3.1. C lion lua pliUcm g p lia p toi U u ......................................................371 3.2. C ung cap th on g tin tlio n g ke ve cac doi tuqng cho trin h toi U u ............................................................................................... 372 3.3. T h ie t lap che do toi U u ..................................................................373 3.4. T h ie t lap cap do trin h toi U u ....................................................... 374 3.5. T rin h toi Uu thuc h ien n liU ng g i ? ............................................... 374 3.6. Chuyen d o i cau le n li S Q L ............................................................ 375 3.7. C lion con dudng truy c a p ............................................................. 375 3.8. Cac han che cua C B O .................................................................... 377 3.9. C ung cap th on g tin th on g ke cho C B O .......................................378
SP 16 MUC L y e 3.10. S d d u n g dbm s_stats de tap hap th on g tin th on g k c ............... 379 3.11. Tao m ot bdng th on g k e ................................................................ 379 3.12. Tap hap thong tin th on g ke cua so dd........................................379 3.13. T h u th&p tlio n g tin th on g ke cua he th o n g ...............................380 3.14. S d dung cau lenh A n a ly z e ........................................................... 381 4. V iet cau lenh SQ L hieu qua.................................................................... 385 4.1. M enh de W here hieu q u a ............................................................... 385 4.2. S d dung cac ham S Q L ....................................................................387 4.3. Sd dung ket n oi p h a i (R ig h t J o i n ) ............................................... 387 4.4. S d dung phdt bieu Case..................................................................388 4.5. Th uc hien truy van con hieu q u a ................................................. 388 4.6. S d dung W here thay vi H a v in g .....................................................388 4.7. G iam thieu viec tim kiem b d n g .....................................................388 4.8. S d dung cdc H in t de tac dong den sa do thuc t h i .....................388 4.9. C lion lua pliUcmg phap ket noi tot n h a t...................................... 389 4.10. Cdc vdng L o op lo n g n h a u ............................................................ 390 4.11. SU d u ng cdc ch i m uc ket n oi B itm a p .........................................390 4.12. Chon lua th d tu ket noi tot n h a t ................................................ 392 4.13. Chien luqc lap ch i m u c .................................................................392 4.14. K h i nao tao ch i m u c ...................................................................... 393 4.15. Cai g i lam c lil m u c ........................................................................ 393 4.16. S d d u n g cdc loa i ch i m uc th ich lia p ...........................................394 4.17. Cdc bdng duoc to chdc ch i m u c ...................................................395 4.18. Cdc ch i m uc ra ng buoc vdi n h a u ................................................ 395 4.19. Cdc ch i m uc dua tren h a m .......................................................... 396 4.20. Cdc ch i m uc khoa d a o ...................................................................396 4.21. C liien luqc lap ch i m uc chia c d t ................................................. 397 4.22. S d d u ng trin h In d e x T u n in g W izard cua O E M .......................397 4.23. Theo doi viec sd d u n g ch i m u c .....................................................3j 9 4.24. G o bo cdc ch i m uc k lion g can th ie t.............................................3 j 3 4.25. S d d u ng cdc ph d t bieu S Q L g io n g n h a u ................................... .353
MtyC LVC 4.26. G ia m tSng ch i p h i S Q L thdng qua cdc ham I n lin e ................ 399 4.27. S d d u n g cdc b icn ra n g b u o c.......................................................402 4.28. T ra n h vide sd d u n g cdc View khdng th ich h a p ...................... 402 4.29. T ra n li nhU ng tliao tac quet toan bdng khong cdn th ie t.........402 5. D B A c6 th£ giup c&i thien qu& trinh xfr ly S Q L nhu the n&o..............402 5.1. S d d u ng cdc bdng chia c d t ........................................................... 403 5.2. S d d u ng cdc ky thuat n e n ............................................................ 403 5.3. S d d u n g cdc View cdn th ie t.......................................................... 404 5.4. S d d u ng cdc S tored O u tlin e de on dinh C B O ........................... 405 5.5. K h i nao sd d u n g cdc O u tlin e ........................................................ 406 5.6. T h u c th i cdc ke hoach on d in h .....................................................406 5.7. Tao cdc O u tlin e ............................................................................... 407 5.8. Tao cdc O u tlin e d cap In s ta n ce ....................................................408 5.9. Tao cdc O u tlin e cho cdc phdt bieu cu th e .................................. 408 5.10. S d d u n g cdc S to red O u tlin e ....................................................... 409 5.11. S d d u n g O u tlin e M a n a ger cua O E M ........................................409 5.12. S d d u n g vice thuc th i song s o n g ............................................... 410 5.13. Cdc tac vu D B A k h a c...................................................................411 5.14. L a m tUcri thudng xuyen cdc con so th on g k c ............................411 5.15. S d d u n g cdc bieu d d .................................................................... 411 5.16. Xay d u n g la i cdc bdng va cdc ch i m uc thuang xuyen.............411 5.17. L u u cdc bdng nhd vao tron g bo nhd.......................................... 412 5.18. Cdc con g cu dieu ch in h hieu qua cau lenh S Q L .................... 412 5.19. S d d u n g E x p la in P la n ................................................................. 413 5.20. D ieu kien tien quyet doi vdi viec sd d u n g E x p la in P la n ....... 413 5.21. Tao m o t E x p la in P l a n ................................................................. 414 5.22. T rin h didn E x p la in P la n .............................................................414 5.23. P liie n dich ket qua kc't xuat cua E xpla in P la n ........................415 5.24. T h em m ot so vi du ve E x p la in P l a n ......................................... 416 5.25. S d d u n g A u to tr a c c ........................................................................419 5.26. S d d u n g S Q L Trace va T k p ro f...................................................426
HP 18 MUC L y c 5.27. T h ie t ldp cdc th on g so ban ddu cho viec theo d o i....................427 5.28. La m cho S Q L Trace co hieu l u c ................................................. 429 5.29. P h ien dich cdc file Trace bdng T k p ro f...................................... 430 5.30. X em xet viec d in h dang file k it xu d t..........................................431 5.31. S d d u n g V $ S Q L A R E A de tim hieu qua cau le n li S Q L ..........435 5.32. T im ki&'m cdc p h d t bieu S Q L dang sd d u n g hdu het cdc nguon tai nguyen cua b a n ......................................................................... 436 5.33. S d du ng cdc View tic dien khac tro n g viec di&u c liln h cau lenh S Q L ................................................................................................... 438 5.34. S d dung cdc cong cu G U I tro n g vide didu c h in h .....................439 5.35. S d dung cong cu T u n in g Pack cua O E M .................................. 440 5.36. lin g dung S Q L A n a ly ze ................................................................440 5.37. Oracle E x p e rt..................................................................................442 6 . M ot giai phap dcm gian de dieu chinh cac phat bieu S Q L .................. 444 6.1. N h a n biet cdc p h d t bieu co van d e ............................................... 444 6.2. T im kiem nguon goc cua viec kliong hieu qua.................................. 444 6.3. D ieu ch in h phdt b ie u ...................................................................... 445 6.4. So sanh hieu qua..............................................................................445 7. Tom tit.................................................................................. 446 CHUCfNG 18: DIEU CHiNH HIEU NANG CUA CCf SC D 0 LIEU ....447 J 1. Dieu chinh bo nhof Oracle........................................................................ 447 1.1. D i iu ch in h Shared p o o l.................................................................. 449 1 .2 . Bo nhd dem th u v ie n ....................................................................... 450 1.3. Phdn tich cdng va phdn tich m e m ............................................... 451 1.4. T o i Uu bo dem th u v ien ................................................................... 456 1.5. S d d u ng th on g so cu rs o r_ s lia rin g ..................................................456 1 . 6 . S d dung cu rsor_s p a ce_for_tim e.....................................................458 1.7. Bo nhd dem cua td dien d d lie u ....................................................458 1.8. D in h kich thudc viin g Shared P o o l .............................................. 459 1.9. Co d in li cdc doi tUqng tron g Shared P o o l................................... 461 1.10. S d dung Shared P o o l A d v is o r .....................................................4^4
M y c LVC 1.11. D i 6 u ch in h B u ffer C a ch e............................................................ 464 1.12. D in h kich tliuac B u ffer Cache n liU the n a o .............................464 1.13. S d d u ng M u lti P o o l cho bo nhd d e m ........................................465 1.14. D ieu ch in h bo nhd P G A .............................................................. 468 1.15. T u dong quan ly bo nhd P g a ......................................................468 1.16. S d d u n g th on g so Pga_a ggregate_taget................................... 469 1.17. S d d u n g view V $ p g a _ta rg et_a d vice.......................................... 469 2. \Jdc luong hieu qua cua he th o n g........................................................... 469 2.1. H ieu qua C P U ................................................................................. 469 2.2. Chieu dai cua h ang d oi chda chuang trin h ch a y ..................... 470 2.3. T im kiem nguai sd d u n g cpu nhieu n h a t.................................. 471 2.4. S d d u n g O E M de theo d oi viec sd d u n g c p u ............................. 472 2.5. T h a i gia n C P U sd d u n g cho dieu g i? .......................................... 472 2.6. C P U sd d u n g cho phdn t i c h ......................................................... 474 2.7. G ia m thcri gia n C P U sd d u ng cho phdn t i c h ............................. 475 2.8. S d d u ng C P U de q u y ..................................................................... 476 3. Do hieu qua cua I/O ................................................................................. 476 3.1. Viec phdn bo I/ O co toi Uu k h o n g .............................................. 477 3.2. G iam sU xu n g dot d ia ....................................................................479 4. Thu thap cac con so thong ke ve hieu qua bang Statspack.................479 4.1. Cai dat S ta ts p a ck .......................................................................... 480 4.2. S d d u n g Statspack......................................................................... 482 4.3. Cdc mdc Snapshot va cdc d iem bdt ddu S Q L ........................... 482 4.4. Tap hop d d lieu S ta tsp a ck ........................................................... 483 4.5. Xoa d d lieu Sta tspa ck.................................................................... 485 4.6. Th u thap cdc bao cao cua S ta tspa ck ........................................... 485 4.7. S d d u n g O E M D iagn ostics Pack de theo doi hoat d o n g ..........492 4.8. P erfo rm a n ce M a n a ge r.................................................................... 492 4.9. Capacity P la n n e r ............................................................................493 5. Tom t i t ......................................................................................................494
MUC LUC CHUdNG 19: Tl/ DIEN D 0 LIEU CUA ORACLE VA CAC VIEW D O NG ................. _495 1. Ti/ dien duf lieu O ra cle ............................................................................. 495 2. Sir dung cac V iew tinh trong ti/ difen duf li£u......................................... 497 2.1. Cdc View ch u n g ................................................................................498 2.2. Cdc View lien quan den quan tri nguai d u n g .............................504 2.3. Cdc View lien quan den kiem s o a t............................................... 514 2.4. Cdc View lien quan den luu trd .....................................................517 2.5. Cdc View theo doi cdc gia o t a c ......................................................524 2.6. Cdc View lien quan den ra n g buoc va c h i m u c.......................... 526 2.7. Cdc View sd du ng cho viec quan ly cdc d oi tuqng tro n g co so dd l i e u .................................................................................................... 532 2.8. Cdc View sd du ng cho viec quan ly cdc bdng d d lie u va View. 532 3. Su dung cac bang v§ qua trinh thuc hien dpng..................................... 538 3.1. Cdc View lien quan den bo n h d .....................................................539 3.2. Cdc View lien quan den sao lu u ....................................................544 3.3. Cdc View lien quan den nguai d u n g va S e s s io n ........................ 547 3.4. Cdc View lien quan den R edo L o g va A rc h iv e L o g ....................551 3.5. Cdc View lien quan den k lioi p h u c ............................................... 555 3.6. Cdc View theo doi viec thuc h ie n ..................................................556 3.7. Cdc View lien quan den S Q L ........................................................ 558 3.8. Cdc View lien quan den hieu qua thuc h ie n ...............................558 3.9. Cdc View th on g th u a n g ................................................................... 563 3.10. Cdc View lien quan den file va luu t r d ..................................... 569 4. Tom tat 571 CHUONG 20: Slf DUNG CAC PACKAGE PIVSQL CUA ORACLE... 573 1. Tong quan ve cac Package Do Oracle cung c a p ......... 574 1.1. A i tao cdc Package cua O ra c le ? .......................... 574 1.2. S d d u n g cdc Package cua O racle n liU the nao? 574 2. Dbms j o b ........................................................................ o