深入解析Oracle-数据库架构设计与性能优化实践.pptx

恩墨科技 成就所托/ www.eNMOU.com深入解析Oracle -数据库架构设计与性能优化实践盖国强 (eygle) 北京恩墨科技MobileSN: mailto:eygle@eygle@Site : /Mail: mailto:eygle@eygle@ Who am I10+ 年 Oracle数据库经验北京恩墨科技有限公司 创始人ITPUB论坛超级版主Oracle ACE 总监博客站点: ../../../CUSTOM/厦门建发 公司站点: / 成长于网络、回馈于网络 / 200720042005200620082009企业面临的数据现状海量的数据累积不断增长的存储与IO压力统计与运算的性能衰减扩展能力的瓶颈（一）充分了解你的数据架构设计:了解数据访问频度高频表的存储与优化（二）制定数据缓存与归档机制缓存为王:Default / Keep CacheAuto-tunednK BuffercacheRecycleKeepDefaultFEWorking set 2Working set 1BEDACCFF…BCADDALRUCKPTsLRUCKPTsBuffercache缓存为王:Default Cache（三）学习Oracle的设计理念架构设计:拆分与分割Oracle的内存管理演进架构设计：分表、分区、分库Oracle11g:Result CacheShared PoolServer Result CacheSQL select /*+ result_cache */ count(*) from eygle; COUNT(*)---------- 15993Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 420 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedLibraryCacheData DictionaryCacheResult Cache又可以分为Server Result Cache Client Result Cache（四）在瓶颈之处寻找突破Cache为王：Flash Cache 支持4. User Process reads blocks from SGA (copied from Flash Cache if not in SGA)Extended Buffer CacheHot DataWarm Data16 GB SGA Memory120 GB Flash Cache3. Clean blocks moved to Flash Cache based on LRU*1. Blocks read into buffer cache2. Dirty blocks flushed to diskCold Data360 GBMagnetic Disks* Headers for Flash Cached blocks kept in SGABusinessApplicationsBusinessApplicationsCached tablesCached tablesOracle In Memory Database CacheOffload Data processing to Middle Tier resourcesData cached in application memorySynchronized with Oracle DatabaseFast, consistent response timesHigh transaction throughputScale out with In-Memory cached GridStandard Oracle InterfacesSQL, PL/SQL, OCI（五）精心设计每一个资源消耗数据库的使命：读、写与展示数据库的使命：读、写与展示矛与盾的抉择：灵活性与性能架构设计：排序与翻页架构设计：Scale UP / OUT水平扩展构架体系Scale out的解决方案对业务基本透明可动态扩展支持任何数据库未来支持多主结构坏掉任何一个主库，不影响业务未来支持压力