Troubleshooting Oracle performance : [methodically identify and
Contents
1. eese 438 EPS GE ct EE NL NS iced NN EEE Ch Ta M a 439 A EE 442 Pas 443 DP ee bau RNET 445 EE MENE AE NS 446 ON To Ghaptar 18 een ente 447 BW Chapter 13 Optimizing Data ACCESS sreranranesnrannnnvrnvrnrnavnnnvnvenvnsnevvennnnsnvernnnnnvnnvnsvanenneveser Q4G Identifying Suboptimal Access Paths rsrarsvenvnnnrvvrnnenesvnrnnnnnnnvrnvennnnsvevevnsrsesvnrnvnsesssnenenssvvensere 449 Ed E 449 O AA an te c deb nS 452 CAUSES NON 454 NON 454 CONTENTS SQL Statements with Weak Selectivity eene nnne 459 ABETI EE T 459 WDIEEIaTE ET 461 Range Partitioning BARRIER 461 Hash and IG All TT 474 Composite Partitioning eene neret tnnt ttnrnnennn nentes ntn tentn natat to rint tenerte teens 475 Design Considerations ener nennen rte nrentnnnnnnentnntnnntnn tnt tentn nentes ttnt tutte tetra neut tiere ra oo 477 Full Index ECHTEN 479 SQL Statements with Strong Selectivity eene 482 Rowid ACCESS RRRRRRRRRRERRRRRRRER 482 Index ACCESS RRRRRRRRRRRRRRRRRRR 484 Single table Hash Cluster ACCeSS m memnrrveneererrerarsnvsnsserenssssenerseseesseseeennsereessvresserevnverersessesnrsenvesersesssenssevnreneer 526 On to Chapter 14 r nersesvarravresvsensnnennrannenennnnenennnnevsnsarannennsessenennsnvsenesensvnrnesennnsssnsnsvnserssnssssnseneanne 528 Chapter 14 Optimizing Joins essears2. eere rennen nennen nnne irte rra rones 174 Or NEE EE 175 LM nun m 176 Select List UI LUC RRRRRRRRRRR 177 Predicate TE E 178 Predicate Move Around srssesererssersverersrsevevsversesnresesnnnenneveveverensssevenevevssensesesenesnsessversersvenesssnversesesevesesessvsnsnersne 180 A NN 181 Ne RS il EE 181 Group by Placement A E EE E T E 181 Order By Elmi ati oiiire enr a a 182 CONTENTS Subquery Unnesting BERTERRERRRTR 183 Subquery AENEID nro 183 Subquery Removal Using Window Functions eeeeeeeeeeeenee enne nnne tenente nnne tete tinte tenens 184 A O 185 A ONO 185 Outer Jointo Inner Join iiri ederent treten eth ana rnc o catre OE 186 FuliOuter Join RE T HM 187 Table NIE EE 187 ORITUR TETUER 188 EXCITET UELUT INSERIRE 189 Query Rewrite with Materialized Views eeeeee eene nnne nnn nnne turn tornano 189 On bee ea 190 E Chapter 7 System Statistics smmesnsrsvnervevsvsravnnnnvnesnsnnnnnnnnsnsvresvvnnannersnsenns EN 191 Th dbms Stats Package scere radi brain ita dd iul d a da 191 What System Statistics Are Available sesvrnnannnrnnrnvnnnennesvennnranevenverenneresneversnnesseseserersvnsansrserne 192 Gathering System Sas usa fati ne 194 Noworkload St tistiesa 0 een sanieren 194 A RN 195 Choosing Between Noworkload Statistics and Workload Statistics eee
3. Si TIET A xviii
4. Configuring the Query Optimizer sunuonsunennunnsnnonnsnnennennnnnuunuonnunnnunnennne 278 To Configure or Not to CONNAUTe Egeter 273 Configuration Road Map renuit rye uie Rene ctn Ra v pra dicia ne 274 Sat the Right ParameterLuuanaersusa eren 276 Query Optimizer E CC 276 PGA Management PERDE 291 On to Chapter 1O EE 297 EiChapter 10 Execution PlanS mm rrsnnsernnnananansvnnnnnnvnnunnvnnnennnnnnnensnsnnnevennvevnnevnnrennevennennsnens ZOO Obtaining Execution PIANOS cocinan o 299 The EXPLAIN PLAN Statement niniin aaia tene testen reason arenas anar tertia tasa 299 Dynamic Performance Views eese nene noai titi tte toties ente tiit tinta ritenute ted 303 Automatic Workload Repository and Statspack eeeeeeeeeeee eene nnne sara ar arar 305 Tracing Facilities A LATET UR LORI TREE ALIS 307 The dbms xplan Package iii A dpi 311 A OA 311 ERNEST E 316 The display cursor Function sescenti teniente te tinte tenetis tint toto titan 322 The display awr Function EE 323 Interpreting Execution Plans n S 325 Parent Child Relationship tme ne beue tera Era Se Po cnn Pe ERE ine e UR CERCARE UR rn 325 Types of Operations i NO 328 Stand Alone Dperationg e NN 328 iterative Operations sesscsiccssacsissscscicccocosssssscsisseniereisssiessscescessbocsenedousociseveuoausosuessussecsetecuctesdsevaeuesediusadisdeesucneaden 331 Unrelated
5. 198 Restoring System Statistics nana idas 199 Working with a Backup Table escsieni niente nnan ventorum bonnet r vant bensensdinsssnedsnensebednienpekaner 200 Logging of Management Operations cocmononconnnnnnenacrannnnranecocnnoronnnnnn ono rana nnne nnn nen 200 impact on the Query Optimizer een 202 A LE ee re 206 Chapter 8 Object Statistics c ccm di Qe l Tne dbms stats Package nun einen 207 What Object Statistics Are Available eeeeeeenenennennnnnnene nnns 209 Table Statistics E 210 Column Statistics 0 een A AEEA O aiaciobes 211 UE le 213 xi CONTENTS Extended Statistics RR nara 226 Index Statistics c sccscccsssecsssscsssscsssssscssscsrscseesecsssessensssecseessesseansessscssssesserneeeeeeseasseassestenseensenseasecasensesnarenseansenss 230 Statistics for Partitioned ObjectS esrvereensrerserernerersrrnvsenenrrrenvrrnsnsenvesessenensneenererennnnersenerseeessssesesnessverevenrerser 232 Gathering Object StatisticS urs mserrvererervvrvrvvererenvnrevesnvnerserenevsverensnannnvaversensserevenesesnssesevenene 233 Target Objects eene tentent tto RRE 235 Gathering Options RR 239 Backup Table ense nnne nnne nnne tenore ntn tnnt rto ntn tentnn toten toten tentat rasero car rare rasero 244 Configuring the dbms stats Package ener nnns 245 The Legacy Way BER ETE 245 The Contempor
6. Combine Operations saseververvevrarernvererrerensnevnssvnesenesnnssvrsearversverssseressssssssernsenesnnerssersesssrsenrnsssssesnen 331 Related Combine Operations enne tntnnntnn nennen tiro ritenere terno 333 Divide and Conquer 342 A OOOO 345 Adaptive Execution Plans PRERRTT 348 xiii CONTENTS Recognizing Inefficient Execution Plans rennen nennen nnne 353 Wrong Estimations uversreeereesverevverserversvesersvessnsessensessensensersessesserseesesrseseeseessessesvavensenserssaneesessessssernsererener 353 Restriction Not Recognized eene nnne tnn ntnntnnente errores testante nentes rosana 355 ORL TO A la RENE EP ER 356 Part IV Optimization erre rrr nenne nnnm ennnnnnnnons GOL i Chapter 11 SQL Optimization Techniques serssessvaresensersvenvennnnevnsvnsensenvensnnsenuvesvnsnnseneeer SIG Altering the Access Structures ernst nnne nennen nente nnne 360 HOW It UD CREER EQ 360 When to Use A LER 361 Pitfalls and Fallacies eren ennt tnn nnne nonien totu to note teen tn thinner ron arar rosas 361 Altering the SQL Statement nennen eene entere nnne nenne teretes 361 HOW It IDE SERERE E 361 When to Use TEE 363 NIESEN 363 A O 363 DIOE AIR 363 When to Use TEE 370 d EIER 370 Altering the Execution Environm
7. Troubleshooting Oracle Performance Christian Antognini 10UG Apress Contents Foreword by Jonathan Lewis rrvrvsenennnvnnnnnnnnnnnnnnvnnnennnrunnnsnanennevnnnennnnsnnnennnnsnnvennnennsenennvenna XIX Foreword by Cary MillSap usevasvasvunnnnnnnnvnnnsnnunnnnnnunnnnnnnnnnnnnnennnnnnnnnnnnnnnnnnannnnenenenvnnnnvnnsvunnnre XX Foreword from the First Edition raamasserasnnnnonnavnunnvnnnnnnnsennenenennnnvnnnnnnnnnnenennnnunnnnnnnnnanenennens XXII About the Author srranannunnnnnnnnnnnennnannnnnnnnnnannnnnnnnunnnnnnnnnsennsennennsennensnnnvsennnnnnnnnnnnsnnnnsannnnnnene XXV About the Technical Reviewers esssssesessenvesrnvenessvnrvsnnveennassenseneosenrenenssnnaresenverensenravesene XXVI Acknowledgments aussen O O NN Acknowledgments from the First Edition nesesrsansvennnnnvvennnnnnnvnnnnnonvnnnevnennnnnennnnnnnnnnsnssnener XXXI nn A E Part I Foundations c eM m EDI UN EES E Chapter 1 Performance Problems annenneennnnnnnnnsunnnnnnnnnvnnennnnnnnensnnnannnenneennnnnnnnavnsnennnnnnsnser I Do You Need to Plan Performance iaa 3 Requirements E UE H Analysis and Design E 5 Coding and UnitTestirig iaa 6 Integration and Acceptance Testing esent rear 7 Designing for PELTON Le 8 Lack of Logical Database Design rissservevererseevnnvnnnvrveesnnnsnensvesversvensensreesverseseversveverevenerennensersvsvesensasersenvevensveverne 8 Implementing Generic Tables sserservervrnvsvsrerevnrernvers
8. ary WAy reete nennen entente nnntntonto ten tento ntn to ttn ton tentata rara tette tenter enia 246 Working with Global Temporary Tables eene nnns 248 Working with Pending Object Statistics eere 250 Working with Partitioned Objects eere nnne nans 251 IE TID CR oo arroces 251 incremental CET 254 Copying Statistics eee nennen nennen rre 256 Scheduling Object Statistics Gathering eene nnne 257 The 10g Way se sssessssesseressrssssnesenesstessvonsssrnrnuenerennnernsttesnteessesnsennsntutiunedensdtousiuennenteundtearenennerantentsrestreeteernesarennt 257 The 11g and BEATEUMR 259 Restoring Object Statistics sessssreseesresrssnrsnssererenrevennnsnnnentnsnnnrenresnnnnnanstnurosessnoneonruennrenenene 261 Locking Object Statistics aiii io nie 262 Comparing Object Statistics iss scscecesssaiceccacecsenscadsosussssossrooccausvanvessnssssvecaussesersnduaseisoniainenosevesvas 265 Deleting Object EE 267 Exporting Importing Getting and Setting Object Statistics see 268 Logging of Management Operations eene nnne nnne nnne nnnnntnnnns 269 Strategies for Keeping Object Statistics Up to Date eee 270 Orto Chapter B eon side soa teta fct ba vere petet SN AR 271 CONTENTS Chapter 9
9. coi EEGEN 562 Parallel JONS a NA 563 PArHON WISE JOINS Gi da 563 Full Partition I I M 563 Partial Partition wise JOINS encre ene eene tun ense n aia 566 Star TANSTORMAUON ai aa 568 Oto Chapter ee eene 574 EiChapter 15 Beyond Data Access and Join Optimization 2 075 Mrs n 575 Howilt pce it ada 576 When to USO c 596 Pittallssand Fallaties en A ae aee eT Rot E RE 596 R s lt CACHING RUPEE 597 HOW IE Work iii aia Ai iaa ivi 597 When to Use NEE 604 ARA dai leere rera ien oporteat oe oaa ti Poe oen vna eov reet eei a rra b Ka eo Pe e ratae 604 Parallel PROCESSING co 22 ART 605 We eoo irte enger adn ce av Funda EV Qna edler 605 E A 636 Pitfalls and TIET 636 Direct Path MSer EEN 641 How It Works eere t 641 When to eC A NTE 644 NEIER TEE 644 CONTENTS Row Prefetching eene rnnt nnne recrear rre nera 645 TE AE 645 When to Use It TEE 650 Pitfalls and FallaCies eecrne nennen nnne tenente nennen tnnt sinon tein ta tato tati tents inte rete tenere enirn 650 Array Interface ener retener nnne reee recen 650 HOW It WOIKS TEE 650 When to Use TEE 654 ht EILER 654 On to Chapter 16 1 seen rte nnn nnntnnnntnnnnnn tente tente tonta tense ttn se nente tnn ten internen 654 Chapter 16 Optimizing
10. ent nennen rennen nnne nennen 372 HOW It WOrkS msssreseerensvnsvevsennnrnenuavssvsnenesnenesrenennussnnensvenenesnsnnevenennnnsneennssvaresssnsnnennnervevesennevenensensnssvssersernnnsrenesee 372 EREN 375 Pitfalls and Fallacies rmseseresvererarvrnevsvsverssrsrsenesevenenesrseressneesensnenvesvssssvrseresnvenevsvsvsvasseseneneneserensennsereneneravssensrer 375 Stored DIME utut EVE PR EYE A dia e 375 ON 375 When to Use It O Pc ET 385 Pitfalls TEE 385 SQL Profiles cvcsscssccssesscscsesseustsnsdicvncecsssacorsesscnenverscsecnenseussadsaasisaiussansavouspuesesssasesasssnanarscnesseaeerssions 387 DHA EEN 387 When to Use EH 401 Pitfalls and IER 402 xiv CONTENTS SQL Plan Management herein einer 402 How Lei ut nee LU RRR 403 MIB H 417 urs ER nn acier CC 417 Onto Chapter V2 oes RTT 418 amp Chapter 12 PAV DEE AE identifying Parsing Problems u nee nude hehe 419 QUICK EE 420 M IPIE E 425 Solving Parsing Problems seriei torre Seege 427 ludice IE DES 427 AA TEIL PENDENTES 433 Working Around Parsing Problems c M 433 MUCH EID E M 434 Server Side Statement Caching eat iccirco rette feres id 436 Using Application Programming Interfaces
11. evnvensssnssnsnsnsssennenenerevnnne 151 Automatic Workload Repository eene nnnm nnne nennen enn nnnntn nennt 152 Performing Configuration eese ener tnter tnter toten tntnten tutos ntes tn toten te tentent tentata tene 152 Taking Snapshots RR 154 ITT 154 StatSDack nini O A ERRTELTLEUEEFRLEERFELEHELFEUENEROVETLFERHERFEREEFFEFRRFELTCN RT 156 Performing Installation eeeeeeeenn tenente tnn nnns nnt ntntne tnt nte tete teniente teinte te irent tenent 157 Configuring the Repository eene carac ntn rentem nnne tnter tetti tente tinae tenere enin 157 Taking and Purging Snapshots seceseesvervversvessvrrversersesersensrsreseesessessensasersersnsessenseeneeverstesessesersessnsersesensenessesne 158 TT 160 Analysis With Diagnostics Pack eese nennen nnns 160 Analysis Without Diagnostics Pack eese nenne nennen tnter nnns 161 Otto Part Bi west ve cosi ad rand eua de 166 Part Ill Query Optimizer ssrsrsvsrsresereanavavnveveserennvnnnnevesenransvornsnsesesenenennnn 167 Chapter 6 Introducing the Query Optimizer ener rennen nnns 109 Fondamentale aa EAR 169 dreien lr 172 Query Transformaliofis 5 scsieccsesscscessstessesasasassnottsccnnisessetgssicsnsascsnsavsoaddncnecssasasateanasncssanbsnednesdends 173 Count Transformation EE 174 Common Sub Expression Elimination
12. ng the Problems 14 Working the Problems 14 On do Chapter Zion AS A 17 ii Chapter 2 Key Concepts r nrsnrsrsrrnraernnavnnvavnnvnnvanansnnnenennenervsnenenannnnvenennronsnnnnnenvenerevnenesnere 1 9 Selectivity and Cardin cion ii 19 What IS a A A digo bx LC EA CE Sri ta on RR eia aad 21 ute Cycle of a CUFSOE ai ac 21 How Parsing Wo Sic tds une ee 24 Shareable GUISOS iii A cr rte E e SEENEN 25 Bind MEET 29 Reading ard Writing BIOGKS voii rios 40 gel E 42 Application E 43 Database Calls ii odas 44 Of ti Pat Zi iia iS 48 8 Part Il IdentifiCatiOn erreur nnnm nn nnn nnne 49 S Chapter 3 Analysis of Reproducible Problems revanrannanennrnnvnnnnnvnnnennennvnnennnnnennennunuvnnennens 1 Tracing Database Calls u aan 51 SQL Trace nennen euere 52 Structure of the Trace Files cccsccsscsseseveoscersessestdvncssonsescaterecctrusddbescsessoancerandceunsesaseusoutebavalatvordssessseavacotueusasuvesaiants 65 Using TRESESS a anne 67 viii CONTENTS gl 67 PENG IR RO Earn osa 68 Using TOS tee AAA 79 Profiling PL SOL COUE A uni M H 89 df INN 89 Using DBMS PROFILER NA 96 Iuba Me nic GE ENEdNEeg 101 Orto Chapter Ang err ia 101 Chapter 4 Real Time Analysis of Irreproducible Problems rranvunvnnvnsennrerrnnnennvavenvnnnerr 103 Analysis Roadmap E 104 Dynamic Performance VIBWS uuouoccssuin iaciunt task inne rh is 105 OS Stati
13. nrnnveneveveerseersversesveesvevsvevevsveervrsteessesssevsvesvenvesevrsvsveressvesseesvene 8 Not Using Constraints to Enforce Data Integrity eene nnne 9 Lack of Physical Database Design rernsevevensvrnenennrnenernnesvenervensersvnvsnnnevevevnvrenusvnvnvevsvesvansnesensenvevsennernvnnesnsenennseene 9 Not Choosing the Right E CU TEEN 9 Not Using Bind Variables CorTectly teen irte tee utc etenim tte nonae EE 10 Not Using Advanced Database Features csssssssssssssssscsssessssssssssesssocescssscssessossasssccesonsesssonsssssssesssessonsosssnsanes 10 CONTENTS Not Using PL SQL for Data Centric Processing nennen nennen nennen nennen tenter nnne nne ttn tnnt 11 Performing Unnecessary Commis eene nenne ntenennntnnttntota ntn rre errar 11 Steadily Opening and Closing Database Connections eere nnne nnns 11 Do You Have Performance Problems eeeeeeeeee eene nnne nennen nennen nennen enn 11 System Monitoring BARRE Bn1 12 Response Time Monitoring eere reete nente coronar tn tente toritate tente toten toten rare earn 12 Compulsive Tuning Disorder eene tnter nentes tnnt re nnn tente ttn ten tnt en tenente tite tenes 12 How Do You Approach Performance Problems eene nnn 13 Business Perspective vs System Perspective eene nennen tnn nennen tenens 13 Catalogi
14. stics E 106 Time EE E 107 Wait Classes and Wait Events eese nennen tenente nnne rre nera 109 System and ETC DE 113 dE 114 C rrent Sessions TE 115 Active Session HiStOFY ERR 117 SQL Statement Statistics cccssscsrsrecsssscscsssssessssserssosscesesenensrsensessssncoedeseasoeessocenstesnsesnsorsevacunsensarsusasesuendaevesnes 126 Real time Monitoring eren ntn nente ette tnt NISAAR Es terrena EE EAA LATAA A tore rient 126 Analysis With Diagnostics and Tuning Pack resesvorsovneennervvresevversverevesererervenennsrrsnsnsensesssesesvnsne 131 Database Server Load EE 131 System Level E O AE A 132 Session Level Amalysis ssssscssssssscssssecssscccessssessossesssnrsonsrsassecssenssucesconenseosssceveareseassacesnssenceseasenersenseacesssorssensanes 137 SQL Statement Information csccsssscessesesescsessessesssseassssssassssvsevssssesesssessesseseseenersessescsseneassuteasesseseesetentensees 139 Analysis Without Diagnostics Pack eere nennen nnns 142 Database Server Load EE 142 Syst m Level Analysis ne aan 143 Session Level AnS a A A A AA AO 147 SQL Statement Information utt On 147 OIL to NADIE sacra Gcr AS i a UR o IATER 149 CONTENTS Chapter 5 Postmortem Analysis of Irreproducible Problems 151 Repositories s sesessarsesrersvavsvenrvessvrssnenennensennsenanensnenensansnanererenvenesensneneun
15. the Physical Design H D Optimal Column Order 655 Optimal Datatype eer 657 Pitfalls in Datatype Selection tenente nnennentnnnenn entere tentent tnter entente eras 657 Best Practices in Datatype SelectiOn eene nennen nnn nnetnntnnnnnnn tnr enne nnn nnne 660 Row Migration and Row Chaining eren nnnnnnnnnnnnnennnnnnnnnn 662 Migration Oe EI tettetett ttet t rrtt ttt EEEEHEENAEREPENEEEEEEeEtePEEEEE 662 Problem Description eerte ercer 664 Problem Identification erret tnnt tenete tnn nntntn tene tenent ntn teinte toto tinte teneor tentent 664 SOWUtIONS ccccccecescssssccssssecssecssssssvscssssecarscsescnscccecseccsccssessesscsusecssecsesnssauscsesenenesanesneeencensesssunsseassnnasenenarsnsesenesenenes 665 Block Contention sersvvnrnvnaronnnessvsvsnnnveneerenesnnsenennesnesnersnnnnnnnnvnnesnennenennnsennsvssensennnnavnnssvensevesvene 665 Problem Description ENEE 666 Problem Identification A eene nnnnnnnnnntnnnnene nnn nnne tnnt rotntn nen tnis ntn en ete sans asen rene re aras tenent 666 SOMU IONS ARR 670 Data Compression eeeeneerne nennen enne nnnentnnnn ente nnnnnnnta nennen nennt tenu territa tenen en tnter 673 COP errar 673 TTT 675 unn RRRRRRRRERMMuuuuU Q 675 APart V Appendix mesesesesrevesenvnvovesenvarvnssesvnvvsvsenvnsesennnensenenvoresnnnnseneneneeserens O79
16. vvavrenersernvnvsnsnnsernnnnsvenennrnnnennenennennnennnsernvnnnernereverseverer O29 BL TE EN EN EE 529 NUI CPC LESER CES 529 ITUR NR 534 Restrictions vs Join Conditions eese nennen tenete tentent tte tenes tnie tertiae inteso nes se tete tn aser 537 Nested Loops Jons inscio pr Reo aida 538 A M 538 TWo Table Join ILES 539 A AO 541 Buffer Cache Prefetches cccccssssscsssssssrssssesssssesssssscersessesseessessenseessesencececssessnsceserseneceensesecsnscsecansuseaasanesstens 542 Merde SOUS ised cca aiti cies caspases Ha pactis inr ri nih ial aa 544 Concept A O UN 544 Two Table JOIMN cscssssssssssssscsssscsssssssscsssssscsnssssssessssssesseecsesseesssssesesussusesausussesoensnsensasscenesesessarsnsssscaneseaseneseasesees 545 A eines aa ENA a aaia aiai 548 Work Areas aiii cara 549 Hash J NS ua een 555 o incor rdv ceci eve reb oed eve ERR EAE STI B ER EXTARE NN 555 Two table TEE 556 xvi CONTENTS OI EIE EE 557 oS CLE 559 SD DITE 560 UTC 561 Choosing the Join Method auos eiiis eter EEN 562 First Rows Optimization i merevenesnvseeseseseesesersereeiersvesserserersepnenversrsereererersrrersrsvesvesesseversereree 562 All Rows OPtimIZAbiON scissssesscerecesorsesssnuaveenvnceposavesoraavscusbicdevaadaverbovevevevasournsiedovesuayodsveveeavoasdcatbeu seeasseiseaissaaguayens 562 Supported Join Methods
Download Pdf Manuals
Related Search