orace性能优化高级培训

null自我介绍自我介绍谢永生 网名：warehouse Itpub新技术区斑竹 Oracle独立技术顾问 ***特约讲师Oracle高性能调整Oracle高性能调整数据库物理结构和实例级别的调整 数据库逻辑结构设计和应用级别的调整 数据库物理结构设计和实例级别的调整数据库物理结构设计和实例级别的调整多年的工作经验告诉我工作需要经历的4个阶段多年的工作经验告诉我工作需要经历的4个阶段完成工作 高效完成工作 高效、安全完成工作 安全、高效完成工作Oracle性能因素Oracle性能因素Oracle性能优化方法论 Oracle性能优化方法论 Why tunes？ Who tunes？ What to tune？ How to tune？ When to tune？为什么（why）要优化为什么（why）要优化系统慢了？ 其实慢只是表象 距离找到慢的原因可能路还很长… 优化什么（what）（需要找到慢的原因） 优化什么（what）（需要找到慢的原因） 是系统的问题？ 是数据库的问题？ 是应用设计的问题？ 是代码编写的问题？ 是架构的问题？怎样（how）优化 怎样（how）优化 根据what中明确的问题，需要制定出调整策略 这个过程可能需要借助很多工具，如是系统的问题，可能需要借助topas、vmstat、iostat等；如果是db的问题则可能需要通过awr、ash、addm等谁（who）来优化 谁（who）来优化 系统架构师（系统架构的问题，麻烦大了） 系统管理员（os、storage问题） 数据库管理员（db问题） 应用程序设计人员（应用设计问题） 应用程序开发人员（代码书写问题） 什么时候（when）优化 什么时候（when）优化 7*24 高可用性系统 是否需要停应用 允许停机的时间 在可以停机的时间内是否能够顺利完成调整 调整前的准备工作(是否需要备份db以及oracle_home)oracle是如何工作的 oracle是如何工作的 Oracle数据库的物理结构Oracle数据库的物理结构Spfile（pfile） Controlfile Datafile Redo logfile Password file Archivelog Oracle数据库的逻辑结构Oracle数据库的逻辑结构Database Tablespace Segment Extent Block Table Index逻辑结构之间的关系逻辑结构之间的关系逻辑结构和物理结构的关系逻辑结构和物理结构的关系什么是oracle实例什么是oracle实例Oracle实例的重要组成部分Oracle实例的重要组成部分后台进程（background process） select * from v$bgprocess 共享内存（sga：system global area） select * from v$sga_dynamic_componentsCheckpoint和实例恢复调整Checkpoint和实例恢复调整理解checkpoint在oracle db中的重要意义 理解dbwr的写机制 理解lgwr的写机制 理解实例恢复的过程 理解redo的大小对性能的影响 合理使用计算机资源 合理使用计算机资源 不论何种应用，计算机和计算机交互无非是process和process之间充分使用下面4种资源交互: File (disk io) Memory (为什么计算机要使用内存？减少物理io) Network CpuIo调整Io调整IO调整的原则 a.最小化io的访问（要贯穿整个应用的设计） b.均衡各类文件的分布(ctl,dbf,redo,archivedlog,tempfile) c.合理使用temporary tablespace（group） Io调整Io调整存储级调整 a．存储不合理，导致io过于集中 b．寻找证据 1）iostat 2）Topas 3）Select * from v$filestat 4）Select * from v$tempstat c．Sa(system administrator)或者厂商协助调整明白存储级别上和io性能相关的重要概念明白存储级别上和io性能相关的重要概念Raid(redundant array of independent disks ) Mirror Strip Array Lun(logical unit number) 理解存储级别上的lun和os级别上的物理卷的对应关系理解存储级别上的lun和os级别上的物理卷的对应关系Lun是存储上的逻辑概念 物理卷是主机上的物理概念 Lun和物理卷和disk相比都是逻辑概念 Lun和物理卷都不能决定磁盘的io性能 决定磁盘io性能的是array里真真包含的disk的数量，数量越多，性能越好在存储级别上决定io性能的关键因素在存储级别上决定io性能的关键因素了解常用raid的数据冗余特性 明白创建array时为什么包含的disk数量越多约好不同oracle文件类型的I/O统计不同oracle文件类型的I/O统计Io调整Io调整Db级调整 a.了解数据库的物理结构 b.合理规划和io相关的文件分布 c.均衡（分散）io d.oracle物理结构中文件分布的原则 null1)controlfile单独存放，mirror不要过多，2份足以 2)redo单独存放，不要放在raid 5上，member不要过多，2份足以 3)undo单独存放 4)datafile尽可能的要分散，尤其是分区表和分区索引所存储的数据文件 5)tempfile文件单独存放，不同的用户使用不同的临时表空间，最好能使用temporary tablespace group 6)index 和data分离，对io影响不大，便于管理（index的原理rowid） 7)archivelog和所有其他文件分离，要务必和redo绝对分离Os memory调整Os memory调整明确主机现有的物理内存 明确主机现有的free物理内存Os memory调整Os memory调整明确主机的swap使用情况Db memory 调整Db memory 调整明确oracle使用内存的主件 理解sga使用内存的原理 理解pga使用内存的原理了解和Sga相关的参数了解和Sga相关的参数 理解lock_sga的作用，注意windows下不起作用 理解sga_max_size的作用(从9.2版本引入) 理解sga_target的作用(从10.1版本引入) 理解参数pre_page_sga的作用Sga的组成Sga的组成Shared_pool Data buffer Redo log buffer Large pool Java pool Stream poolSga自动管理Sga自动管理Sga自动管理的优缺点 如何设置sga自动管理 Sga自动管理之后老参数的作用 如何正确使用sga自动管理 如何通过sga advisor来给sga设定合理的值 使用sga advisor的条件 参数statistics_level的作用Sga advisor的数据来源Sga advisor的数据来源数据源于下面查询 SELECT sga_size, (1-estd_db_time_factor)*100 FROM v$sga_target_advice order by 1 如何读懂advisor曲线 根据advisor判断sga设置是否合理Sga advisor的曲线分析Sga advisor的曲线分析根据曲线指示正确设置sga_target的值，防止设置过大浪费物理内存、设置过小影响系统性能Data buffer调整Data buffer调整Data buffer的作用就如同memory对计算机的作用是同样的道理，没有memory的计算机我们无法想想哪的慢到什么程度 Sga自动管理下db_cache_size的作用 正确使用 buffer cache advisor Buffer cache advisor的数据来源 select size_for_estimate,a.estd_physical_read_factor from v$db_cache_advice a Buffer cache advisor的意义Buffer cache advisor的意义Shard pool的作用Shard pool的作用Oracle引入shard pool的意图 我们能正确领会oracle的意图吗 在某种程度上shared pool的大小不能直接决定系统的性能 什么是绑定变量 在oltp系统中不使用绑定变量的危害是什么 Olap系统为什么不建议使用绑定变量 Shard pool 调整Shard pool 调整正确使用shared pool advisor Shared pool advisor的数据来源 select a.SHARED_POOL_SIZE_FOR_ESTIMATE,a.ESTD_LC_TIME_SAVED_FACTOR from v$shared_pool_advice a shared pool advisor曲线的意义shared pool advisor曲线的意义根据曲线的含义正确设定shared_pool_size的值Pga 调整Pga 调整pga_aggregate_target的作用 Pga的内存在何时分配 Pga的内存是源于os还是oracle 什么是工作区 workarea_size_policy的意义 在pga自动管理的情况下，sort_area_size和hash_area_size在什么情况下还可以发挥作用Pga advisor的使用Pga advisor的使用Pga advisor的数据来源 select * from v$pga_target_advice 如何读懂advisor曲线 根据advisor曲线判断pga设置是否合理 Pga advisor曲线的意义Pga advisor曲线的意义select pga_target_for_estimate/1024/1024 ,a.ESTD_PGA_CACHE_HIT_PERCENTAGE,a.ESTD_OVERALLOC_COUNT from v$pga_target_advice a 和cpu资源相关的调整和cpu资源相关的调整过度消耗cpu资源的sql诊断和调整 v$sqlarea ASH 减少和latch相关的等待 Cpu 100%模拟、诊断和调整Oracle里和process相关的调整Oracle里和process相关的调整进程的种类 a）background b）server c）user（client） 明确系统的在线用户最大数量 select * from v$resource_limit where resource_name in ('processes','sessions') 明确系统的并发用户最大数量 select * from v$session where status='ACTIVE' DEDICATED连接模式的特点DEDICATED连接模式的特点响应及时、速度快 消耗的系统资源多 什么是session 进程对session是一对一的服务 判断session使用的是专用还是共享模式连接模式 什么样的系统该使用专用模式shared连接模式的特点shared连接模式的特点响应可能不够及时、有延时 省系统资源 如何判断db是否支持shared连接模式 select count(*) from v$dispatcher shared count(*) from v$shared_server alert日志… dbca… 参数shared_servers>0 Shared连接模式的结构Shared连接模式的结构Shared连接模式下一个client端发出一个处理请求的全过程共享连接模式处理一个请求的过程共享连接模式处理一个请求的过程Processing a Request When a user connects through the Shared Server architecture submits a database request: 1. The user process forwards the request to its dispatcher. 2. The dispatcher places the request into the common request queue in the SGA. 3. The next available shared server picks up the request from the request queue and processes the request. 4. The shared server places the response on the calling dispatcher’s response queue. Each dispatcher has its own response queue. 5. The dispatcher retrieves the response from its response queue. 6. The dispatcher returns the response to the user.Unix中process和windows中thread的区别Unix中process和windows中thread的区别如何理解windows下oracle.exe Oracle.exe中包含了哪些thread Oracle.exe中的一个thread导致windows db server cpu 100%如何诊断 Windows下如何查找一个占用cpu很高的thread和session Windows下如何kill一个占用cpu很高的thread block空间使用调整 block空间使用调整 Dmt和lmt比较 Dmt空间管理引起对uet$,fet$的操作在大量extent的情况下严重影响性能 空间管理锁类型为v$lock.type = ‘ST’ Lmt不再使用字典表(sys.uet$,sys.fet$)来记录extent分配和使用信息 使用文件头的bitmap来记录extent信息，一个bit表示相应的位置的extent 使用统一分配的extent大小（不主张使用自动分配） 大大提高空间的分配和释放的性能数据块(block)数据块(block)最小的I/O单元 由一个或多个操作系统块组成 在数据库创建时设定 DB_BLOCK_SIZE是默认的块大小 9i开始支持最多5种不同大小的block(2k,4k,8k,16k,32k) 32k的block仅在一些平台上支持，windows平台不被支持Pctfree和pctused的作用以及相互制约关系 Pctfree和pctused的作用以及相互制约关系 Hwm(high water mark)Hwm(high water mark)什么是segment的hwm 为什么delete大量数据之后空间一点也不释放 为什么查询一个具有少量数据的表性能如此的差 Delete和truncate有何差别 如何降低hwm 通过move降低hwm之后index失效需要rebuildlocklockLock的type：exclusive和share Oracle里在行上只能加exclusive类型的锁 有独立的undo做保证在oracle里select操作无需在表上以及行上加任何类型的锁 为什么操作数据时需要在表上加锁 Oracle在表上支持的lock type： RS: row share RX: row exclusive S: share SRX: share row exclusive X: exclusive表上lock type的兼容性表上lock type的兼容性阻塞(block)阻塞(block)引起block的原因 发生在表上引起的block 发生在行上引起的block 如何诊断block 如何解除blockBlock模拟及诊断分析Block模拟及诊断分析Session 1： SQL> update t set id=1; 1 row updated. Session 2： SQL> update t set name='b'; -----发生阻塞 阻塞的原因是session无法在数据id=1上获得行级锁deadlock(死锁)deadlock(死锁)数据库里的死锁其实就是程序里的死循环 死锁并不可怕，oracle会自动解除死锁 但是频繁的死锁会影响应用程序的稳定性 Alert中会详细记录引起死锁的session以及sql信息死锁场景模拟死锁场景模拟LatchLatchLatch是oracle里最难翻译的词汇 很多人翻译成闩锁，我通常不原意翻译它 Lock是保护数据库中对象的 Latch是保护实例级别的对象，准确的说是保护sga里内存结构的 Latch是一种串行化的、力度非常小的锁 Latch等待通常会消耗cpu资源几种重要的latch等待模拟几种重要的latch等待模拟cache buffers chains Library cache pin Library cache lockWait eventWait event什么是等待事件（v$event_name） oracle通过等待事件来反应系统的性能问题 通过等待事件诊断系统性能问题Wait_classWait_class10g引入的wait_class使我们更容易定位系统的问题所在 通过等待类大致锁定系统瓶颈通过等待类大致锁定系统瓶颈select wait_class,sum(total_waits) sum_total_waits from v$system_event group by wait_class order by sum_total_waits desc(目前系统的突出等待类)statistics statistics 什么是统计信息?它的作用是什么？ 实例级的统计信息v$sysstat Session级别的统计信息v$sesstat Optimizer statistics（在应用调整里介绍） Optimizer statistics对oracle生成执行计划产生重要影响 系统统计信息System statistics相关的视图及关系System statistics相关的视图及关系Session statistics相关的视图及关系Session statistics相关的视图及关系Materic(度量、指标)Materic(度量、指标)Metric是指累计的统计信息的变化率，Metric比statistics粒度更细，更容易说明问题，Metric的引入主要为了实现proactive（积极、主动）维护数据库，通过给metric设定一定的threshold，如果超过了这个threshold则系统自动报警，我们通过监视这些警告从而可以提前预防和解决问题Metric的种类Metric的种类Oracle从衡量系统性能的不同角度把metric分成了5类： 和instance有关的metric：v$sysmetric 和session有关的metric：v$sessmetric 和service(服务名)有关的metric： v$servicemetric 和file相关的metric：v$filemetric 和wait event相关的metirc：v$eventmetirc和metirc相关的视图和metirc相关的视图Metric的使用Metric的使用给度量设定值 SQL> create tablespace test datafile 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test .dbf' size 10m ; SQL> create table t1 tablespace test as select * from dba_objects; 表已创建。 SQL> insert into t1 select * from t1; 已创建11655行。 ………. SQL> commit; SQL> select bytes/1024/1024 m from dba_segments where segment_name='T1'; M ---------- 9 SQL> 查看告警 Statspack(工欲善其事，必先利其器 )Statspack(工欲善其事，必先利其器 )Statspack的思想 Statspack的安装 通过statspack搜集snapshot 生成statspack report 阅读statspack report 得出系统性能问题的 有针对性的调整系统Awr(auto workload repository)Awr(auto workload repository)Awr比statspack搜集了更多、更详细的信息 Awr不需要安装了，oracle把awr做为oracle 内核的一部分集成在安装软件里了 Awr搜集了os信息而statspack没有搜集 Awr搜集的snapshot oracle自动维护通常无需dba干预 Awr包含了10g引入的time modle数据库逻辑结构设计和应用级别的调整 数据库逻辑结构设计和应用级别的调整 设计表的原则设计表的原则遵循NF范式 根据业务尽可能的把相对独立的实体抽象出来 尽可能的减小实体的大小从而减少访问数据时不必要的开销 减小实体有助于较少锁冲突 根据业务特点选择适当的表类型减小实体减少开销案例减小实体减少开销案例具体参考脚本：减小实体较少锁冲突案例减小实体较少锁冲突案例减小实体减少行连接案例减小实体减少行连接案例了解oracle支持的table type了解oracle支持的table typeHeap Iot Partition Cluster temporary合理使用分区表合理使用分区表分区表的使用不能绝对的提高性能 合理的使用分区表可以方便管理 是否使用分区表很多时候是由业务的特点决定的，而不是由数据量决定的，当然数据量不大的表分区本身意义不大indexindex逻辑上 单列或组合索引 唯一非唯一索引 物理上 分区或非分区 B 树 正常或反向键 位图深入理解rowid深入理解rowidB-tree index的结构B-tree index的结构Bitmap index的结构Bitmap index的结构B-tree和bitmap index的比较B-tree和bitmap index的比较Index的scan方式Index的scan方式Unique scan Range scan Desc scan Index full scan Fast full scan Index skip scan 合理使用view 合理使用view View可以增加程序的可读性、控制数据的安全访问，也是面向对象特性的一个体现，但是view的使用在某种程度上对sql性能没有任何提高 合理使用trigger 合理使用trigger 在大型的、复杂的应用里尽可能的少使用trigger，使用它方便的同时可能不仅会使应用的灵活性受到限制同时可能会对性能产生影响 如果trigger实现的功能很复杂，那么最好把复杂的业务逻辑通过procedure或者function来实现之后再在trigger里调用process或者function 多使用procedure、function或者package 多使用procedure、function或者package procedure、function，package进行了预编译 procedure、function，package中sql自动使用绑定变量尽可能的多使用synonym尽可能的多使用synonymSynonym的引入是oracle面向对象思想的又一重要体现，在procedure、function，package里尽可能的多使用Synonym以减少由于对象（如表）名字改变而大量修改引用该对象的procedure、function，package；同时synonym的使用可以封装如:schema.object_namne@dblink这种远程对象的使用，使用户更加安全的透明访问对象 少使用sequence少使用sequence使用sequence有时候确实很方便，不过sequence不能保证绝对的连续，而且如果对sequence的一些特性（如cache）控制不好会对性能产生影响合理使用外键 合理使用外键 在大型的、复杂的应用里尽可能的少使用外键，外键的使用会使数据的完整性、一致性得到有力保障，但是它的使用有时会使应用的灵活性受到很大限制，如果不使用它，那么数据的完整性和一致性完全需要我们应用开发人员自己来控制，这对应用设计和开发人员都提出了很高的要求，这个到底是否使用根据自己的应用特点和团队技术实力自行选择 在外键上创建index可以大大提高效率同时防止死锁的出现在外键上创建index可以大大提高效率同时防止死锁的出现绑定变量 绑定变量 在以oltp为主的应用系统中多使用绑定变量，因为在oltp系统里都是一些小的事务对应的sql，sql的解析可能消耗的资源往往大于执行消耗的资源，因此我们尽可能的要减少解消耗的资源对系统性能的影响 在以olap为主的应用系统里通常不需要使用绑定变量，因为在olap应用系统里往往都是一些大的事务对应的长时间执行的sql，sql的执行时间远远大于sql的解析时间，因此使用绑定变量没有太多意义Oracle optimizer(优化器)介绍Oracle optimizer(优化器)介绍Rbo(Rule-Based Optimization ) Cbo(Cost-Based Optimization) Cbo下optimizer mode CHOOSE ALL_ROWS FIRST_ROWS FIRST_ROWS_1 FIRST_ROWS_10 FIRST_ROWS_100 FIRST_ROWS_1000Access Paths for the RBO Access Paths for the RBO RBO Path 1: Single Row by Rowid RBO Path 2: Single Row by Cluster Join RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key RBO Path 4: Single Row by Unique or Primary Key RBO Path 5: Clustered Join RBO Path 6: Hash Cluster Key RBO Path 7: Indexed Cluster Key RBO Path 8: Composite Index RBO Path 9: Single-Column Indexes RBO Path 10: Bounded Range Search on Indexed Columns RBO Path 11: Unbounded Range Search on Indexed Columns RBO Path 12: Sort Merge Join RBO Path 13: MAX or MIN of Indexed Column RBO Path 14: ORDER BY on Indexed Column RBO Path 15: Full Table ScanExecution planExecution plan什么是执行计划 如何查看sql的执行计划 在sqlplus里设置set autotrace on 在plsql developer里通过F5 通过查询select * from v$sql_plan 通过explain plan for … 如何看懂执行计划如何看懂执行计划The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first 阅读执行计划案例分析nullThe execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first. Optimizer statistics Optimizer statistics 什么Optimizer statistics Optimizer statistics的作用 如何搜集Optimizer statistics analyze table… dbms_stats 什么是histogram Histogram的作用和种类Table join method Table join method loop nested join sort merge join hash join 各种连接方式的原理和成本比较 各种连接方式的适用范围如何控制Table access sort 如何控制Table access sort Ordered Leading use_hash use_nl use_merge Optimizer hint Optimizer hint Optimizer hint 的作用 使用Optimizer hint的限制(只适合cbo) 常用hint介绍 index no_index full ruleSql trace和tkprof工具简要介绍Sql trace和tkprof工具简要介绍诊断sql最强有力的工具sql trace介绍 处理sql trace跟踪文件tkprof工具介绍Sql调整指导公式T=S/VSql调整指导公式T=S/VT代表sql执行所消耗的时间 S代表sql执行所消耗的资源 V代表sql执行时的速度 调整sql的目标就是减小T 调整sql的过程就是较少对S的消耗同时尽可能增大V的过程Sql调整指导公式T=S/V案例介绍Sql调整指导公式T=S/V案例介绍A&Q Thanks A&Q Thanks null warehouse的联系方式： mobile：13840879243 msn:xys_666888@hotmail.com qq：9020630 qq群：122642876 blog：http://warehouse.itpub.net 公司网址：www.feirui.net