• 1. oracle性能调优
  • 2. 我们一般会想到的oracle优化的方式1、建索引 2、分区 3、调参数 4、调连接数 5、存储过程 6、碎片 7、优化硬件设备 8、优化sql 9、文件分开存放
  • 3. 我们一般会想到的oracle优化的方式1、建索引——本质是为了减少io 2、分区——本质也是为了减少io 3、调参数——调整软硬件之间平衡 4、调连接数——调进程、内存消耗 5、存储过程——调应用 6、整理碎片——i/o 7、优化硬件设备——调cpu,内存,硬盘转速等硬件性能 8、优化sql——优化sql语句使用的资源 9、文件分开存放——分散i/o
  • 4. 优化的实质优化的实质是调整资源:cpu、内存、i/o等 比如: 1、资源不够,增加资源。 2、尽可能的使软、硬件之间达到平衡,减少热点盘,使用多进程等。 3、尽量减少资源的使用,比如能用一条sql的,不用两条sql 4、有时也需要限制资源的使用。 比如:用户量达到300时软硬件之间达到了平衡,过了这个点性能就会下降。 再比如:300个用户中的一个在那做报表呢,占用了90%的cpu等资源
  • 5. oracle体系结构
  • 6. oracle体系结构pga专用服务器连接模式。每一个用户连接都会产生一个server进程,消耗内存占用cpu等资源。这种模式下uga(用户全局域)存在于pga(进程全局域)内。就是上图画的那样。 共享服务器连接模式。但是有个资源调度的过程,性能会受影响。 连接池和共享服务器的连接模式是类似的。 oracle9i之后pga默认为oracle自动管理,pag的内存可以动态的扩大和回收。 手动管理pga:一般用不上。
  • 7. oracle最重要的内存区SGA区SGA:是用于存储数据库信息的内存区,该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它使Oracle服务器所驻留的实际内存得以分配,如果实际内存不够再往虚拟内存中写。 SGA主要由三部分构成:共享池、数据缓冲区、日志缓冲区。 共享池主要存放两部分内容: 1、最近使用的sql、PL/SQL语句, 2、数据字典 查看存放sql语句: select sql_text from v$sqlarea where sql_text like '%select * from sunls_menu%';
  • 8. oracle最重要的内存区SGA区 select * from v$sqlarea where sql_text like '%select * from sunls_menu%'; 执行这个语句得到sql语句的缓存情况 他会反映某个特定Sql语句的执行信息。 下面的语句反应sql的读取情况 select name,value from v$sysstat where name in('physical reads','db block gets','consistent gets');
  • 9. oracle最重要的内存区SGA区先用下面的公式计算高速缓存区的命中率: 1-(physical reads/(db block gets+consisten gets)); select (1-(sum(decode(name, 'physical reads',value,0))/(sum(decode(name, 'db block gets',value,0)) +sum(decode(name,'consistent gets',value,0))))) * 100 "Hit Ratio" from v$sysstat; 如果命中率是0.90或更高,是好的。如果过低就要增加db_block_buffers的值,因为说明共享池不够大了,新的sql语句把老的总是挤出去,或者就是你的程序有问题,sql共用性太低。 共享池就先说这么多吧,还有很多比如设置共享池大小,为大的PL/SQL语句留空间等,希望大家有时间自己研究一下;
  • 10. 内存使用情况分析(整理)ORACLE 数据库的初始化参数文件通常存放在$ORACLE_HOME/dbs路径下,其文件名为init+数据库sid、后缀为.ora,如initorcl.ora。其中,影响数据库内存大小的参数主要有: DB_BLOCK_SIZE:每个数据库块的字节数,在数据库建立时已确定,为1024的整数倍,如2048、4096; DB_BLOCK_BUFFERS:数据库数据缓存区的数据块数; SHARE_POOL_SIZE:共享存储区的字节数; SORT_AREA_SIZE:数据排序区的字节数; 当剩余内存(free memory)过少、内存击中率过低,当library cache 的击中率或数据字典的内存击中率低于0.95时,考虑调整SHARED_POOL_SIZE,DB_BLOCK_BUFFER的大小。
  • 11. oracle最重要的内存区SGA区数据库缓冲区: 存放最近查出来的数据,和操作的数据(比如改变后未提交的在回退段里)。 它的优化涉及到数据的存取问题,主要影响还是在I/O,和缓冲区的大小上。 oracle数据存取的最小单位是blok(段),如果查出来的数据量很大,内存不够了,插入数据量大了都会对它产生影响。另外为了保证数据的一致性,同一时刻一个block只能被一个进程pin住进行存取,因此当一个进程需要存取buffer cache中一个被其他进程使用的block的时候,这个进程就会产生对该block的buffer busy waits事件 主要优化的方式有: 1、使用多个缓冲区,并为对象指明缓冲区 2、调整其大小 3、对于访问频繁的对象使用缓存 4、分析减少Buffer busy waits
  • 12. oracle最重要的内存区SGA区Large Pool的大小是通过参数LARGE_POOL_SIZE来决定的。它所存储的信息有用户全局区域(UGA),备份和恢复选项(Backup and restore operations)和并行查询信息(Parallel query messaging)Oracle共享进程和Oracle XA接口。 日志缓冲区: Oracle日志缓冲区的争用将完全抑制数据库的性能,因此要设法减少它。日志空间请求反映了用户进程等待Oracle日志缓冲区空间的次数。所以可通过查问动态性能表V$SYSSTAT(需有SELECT ANY TABLE特权)来确定是否争用Oracle日志缓冲区空间。该查询语句的格式如下: SELECT name, value FROM V$SYSSTAT WHERE name='redo log space requests'; 日志空间的请求值应接近于0,否则需增加初始化参数LOG_BUFFER的值,以增加空间、减少争用。
  • 13. oracle最重要的内存区SGA区了解是否发生争用 Select name,gets,misses,sleeps,wait_time,spin_gets,immediate_gets,immediate_misses from v$latch where name like '%shared pool%' or name like '%library cache' or name like '%cache buffers lru chain%' or name like '%cache buffers chains%' or name like '%redo allocation%' or name like '%redo copy%'; 要减少日志分配闩锁的争用就需使单个进程持有闩锁的时间最短,要减少此时间又应减少在日志闩锁上的拷贝,即减少参数LOG_SMALL_ENTRY_MAX_SIZE的值。要减少日志拷贝闩锁的争用,一是观察其争用情况,增加闩锁,即增加LOG_SIMULTANEOUS_COPIES的值;二是减少持有闩锁的时间,即在获得日志拷贝闩锁之前,就迫使Oracle用户进程事先建立日志项。事先建立的所有日志项的大小都要小于参数 LOG_ENTRY_PREBUILD_THRESHOLD,要事先建立日志项就要增加参数LOG_ENTRY_PREBUILD_THRESSHOLD 的值。
  • 14. oracle最重要的内存区SGA区排序是一项花销很大的操作,而且对性能的影响程度也较大,因此使大部分排序在内存中完成,而不是在磁盘上进行,这是至关重要的。其中“sorts(memory)”选项表示不需要磁盘I/O,选项“sorts(disk)” 表示需要磁盘I/O。如果用户认为在磁盘上的排序意义较大,可以增加init.ora文件SORT_AREA_SIZE参数的设置值。以上介绍Oracle日志缓冲区。
  • 15. 索引对待索引的建立要特别谨慎,因为索引要占用磁盘空间。如果索引同表本身的大小差不多,那就不能从索引得到多少好处。索引还要对表的更新、插入和删除的性能产生影响。除了这几点之外,索引是改进查询性能的最简单、最有效的方法,尤其是当您总是对几个关键字字段进行搜索时,这种方法最合适。 频发的插入删除操作会产生碎片,此时需要重建索引。 建索引查出比例占表的内容越少越好。 索引最后都是通过rowid查找到记录的,rouid存的是:数据对象号+文件号+blok号+行号。
  • 16. 内存使用情况分析(整理)剩余内存(free memory) select * from v$sgastat where name=’free memory’; library cache 的击中率 select sum(pins-reloads)/sum(pins) from v$librarycache; 数据字典的内存击中率 select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache; 数据共享区的击中率 select name,value from v$sysstat where name in (‘db block gets’,’consistent gets’,’physical reads’); 击中率=1-(physical reads)/(db block gets + consistent gets) 注意:以上几个指标均应在系统运行足够长时间后进行检查。 并且显示的是从运行到现在的情况。
  • 17. 内存使用情况分析(整理)此外,我们可以根据下面的算法,估算SHARED_POOL_SIZE 的大小: SELECT SUM(value) FROM v$sesstat,v$statname WHERE name='session uga memory' AND v$sesstat.statistic#=v$statname.statistic#; 该查询返回的是目前所有用户进程所占用的SHARED_POOL 字节数; SELECT SUM(value) FROM v$sesstat,v$statname WHERE name='session uga memory max' AND v$sesstat.statistic#=v$statname.statistic#; 该查询返回的是目前所有用户进程所需占用的SHARED_POOL 最大字节数; 我们可据此确定SHARED_POOL_SIZE 的初始大小。
  • 18. sql执行过程当我们提交一条sql语句时,oracle会做哪些操作呢? Oracle会为每个用户进程分配一个服务器进程:service proces(实际情况应该区分专用服务器和共享服务器),当service process接收到用户进程提交的sql语句时,服务器进程会对sql语句进行语法和词法分析。 检查通过后,服务器进程会将sql语句转变为ascii码,并通过一个hash函数将ascii码生成出一个hash值,服务器进程会到share pool中查询此hash是否存在,如果存在,服务器进程会从sharepool中读取已经解析好的语句来执行;如果不存在,则需要做以下步骤:生成执行 计划和生成执行编码(请理解何为执行计划)。解析完成后,Oracle会将sql语句本身代码、hash值、编译代码、执行计划和所有与该语句相关的统计 数据存放到sharepool中。
  • 19. sql执行过程select语句在生成编译代码后,service process会试图从db_buffer中读取是否存在相关的缓存数据。如有直接读取,如果没有,则从物理文件中读取至数据库缓冲区并读取。 对SELECT 语句需要返回结果的语句,先看是否需要排序,需要,则排序后返回给用户,然后根据内存的大小不同,可以一次取出一行数据,也可以一次取一组数据。 如果用户执行的是INSERT、DELETE、UPDATE等操作,系统将为其分配redo log buffer和回退段,用于记录数据的变更情况,当redo log buffer中无free buffer时触发LGWR进程,将redo log buffer中的一些信息写如数据库的LOG FILE 中。 一个事物只能放在一个回退段里。
  • 20. sql语句注意的地方1.用EXISTS代替DISTINCT,消除排序 2.如果在GROUP BY中过滤数据,在WHERE从句中指定条件比在HAVING从句中有更好的性能,因为在GROUP之前已经过滤掉数据,因此更少的行被汇总 3.UNION会对两个SELECT语句的结果集执行一个SORT,并消除重复行,成本会昂贵,而UNION ALL则不会。因此如果应用能够处理重复,或者确信没有重复记录,那么考虑使用UNION ALL代替UNION,能不用UNION就不要用它
  • 21. 数据/日志存放的问题数据存放和日志存放都是会影响i/o的 1、存取分离 2、建表的时候指定表空间 3、日志分盘存放,分散i/o 4、文件的大小个数存放位置都需要考虑。 5、日志必须放在读写速度最快的设备上。 6、对于不必要的日志最好指定nologing
  • 22. 拓展1.检查占用CPU时间比较长的sql语句: select sql_text,cpu_time from v$sql where cpu_time >1e7 order by cpu_time     2.执行效率最差的10条sql语句 SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS SORTS, COMMAND_TYPE, DISK_READS, sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<10 ; 3.利用V_$SQLAREA视图提供了执行的细节。(执行、读取磁盘和读取缓冲区的次数) select SQL_TEXT,EXECUTIONS,DISK_READS,COMMAND_TYPE,OPTIMIZER_MODE, SHARABLE_MEM,BUFFER_GETS from v$sqlarea
  • 23. 拓展EXECUTIONS:执行次数 DISK_READS:读盘次数 COMMAND_TYPE:命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元) OPTIMIZER_MODE:优化方式 SQL_TEXT:Sql语句 SHARABLE_MEM:占用shared pool的内存多少 BUFFER_GETS:读取缓冲区的次数 1、帮忙找出性能较差的SQL语句 2、帮忙找出最高频率的SQL 3、帮忙分析是否需要索引或改善联接 4. 监控当前Oracle的session,如出现时钟的标志,表示此进程中的sql运行时间较长。