Oracle 11g 性能调优 Lec01

Oracle11g性能调优xujyOracle11g性能调优性能调优Oracle11g性能调优•WhoTunes•Systemarchitecture•Systemdesigners•Systemdevelopers•DatabaseadministratorsOracle11g性能调优•TuningPhases(HowTune)•Applicationdesignandprogramming•Databaseconfiguration•Addinganewapplication•TroubleshootingandtuningOracle11g性能调优•TuningGoals•Reducingoreliminatingwaits•Accessingtheleastnumberofblocks•Cachingblocksinmemory•Responsetime(响应时间)•Throughput(吞吐量)•Load(负载)•Recoverytime(恢复时间)Oracle11g性能调优•ExamplesofTuning•Improvedresponsetime•Improveddatabaseavailability•Improveddatabasehitpercentages•Improvedmemoryutilization•FewerwaitsOracle11g性能调优•CommonTuningProblems•Badsessionmanagement(usuallyrelatedtomiddleware)•Badcursormanagement(usuallyresultingfromprogrammererror)•Badrelationaldesigns(usuallyresultingfromovernormalization)Oracle11g性能调优•开发过程中的性能调优•Tunethedesign.•Tunetheapplication.•Tunememory.•TuneI/O.•Tunecontention.•Tunetheoperatingsystem.Oracle11g性能调优•生产系统的调优•Locatethebottleneckbyusingtools.•Determinethereasonforthebottleneck.•Resolvethecause.•Checkthatthebottleneckhasbeenresolved.Oracle11g性能调优Oracle11g性能调优•Chap1－2数据对象管理调优(表/索引)•Chap3内存结构配置调优(SGA)•Chap4性能数据搜集与监控•Chap5系统资源争夺•Chap6操作系统调优•Chap7数据库问题•Chap8-11SQL调优及相关问题•Chap12执行计划•Chap13优化器配置•Chap14实现查询提示•Chap15并行SQLStartHereOracle11g性能调优•评价标准–平时评价(50%)•考勤(10%)•学习表现(10%)•作业或测试(30%)–期末评价(50%)•随堂开卷笔试(第16周星期一，2015年6月8日)Lec01创建高效的SQL•Chap8(Book)1.SQL回顾攻略8-01至8-112.高效SQL技巧攻略8-12至8-17SQL回顾(0)•1.结构化查询语言StructuredQueryLanguage1.ISO标准SQL语法2.传统的Oracle语法SQL回顾(1)•1.投影(Projectπ)SQLSELECT*FROMemployees;–投影运算实现查询结果列的选择–由SELECT子句指定选择的列注:示例数据库为hr附:OracleApex访问•附:OracleApex访问•点选SQL工具箱附:OracleApex访问•输入SQL代码,点击附:OracleApex访问•点选SQL命令SQL回顾(1)•1.投影(Projectπ)SQLSELECTlast_name,first_nameFROMemployees;LAST_NAMEFIRST_NAME---------------------------------------------AbelEllenBaerHermannCabrioAnthonyDillyJenniferErnstBruceSQL回顾(1)•1.投影(Projectπ)SQLSELECTlast_name||','||first_nameASEmployeeNameFROMemployees;EmployeeName-------------------------------Abel,EllenBaer,HermannCabrio,AnthonyDilly,JenniferErnst,BruceSQL回顾(2)•2.选择(SELECTIONσ)SQLSELECT*FROMemployeesWHEREdepartment_id=20;–选择运算实现查询结果行的选择–由WHERE子句指定选择条件SQL回顾(2)•2.选择(SELECTIONσ)•SQLSELECTlast_name,first_name,salary,emailFROMemployeesWHEREdepartment_idIN(20,30)ANDcommission_pct0;SQL回顾(3)•3.连接(JOIN)•SQLSELECTd.location_id,department_name,cityFROMdepartmentsd,locationslWHEREd.location_id=l.location_id;SELECTd.location_id,department_name,cityFROMdepartmentsdJOINlocationslONd.location_id=l.location_id;