Oracle内部优化.

1內部優化2優化手段SQL語句優化啟用Index語句數據庫內部優化34567數據庫內部優化步驟設置應用程序和操作系統的所有的初始參數優化內存的分配消除I/O瓶頸優化資源的競爭優化排序/自由表以及檢查點123458內部優化--使用工具1.STATSPACK支持ORACLE8.1.6以上2.UTLBSTAT3.UTLESTAT9總結重點:1234優化內存的分配•SQL以及PL/SQL方面•共享池•數據庫的緩沖區緩存優化輸入/輸出•磁盤輸入/輸出•空間分配•動態擴展優化資源競爭•回滚段•重做日誌•多線程服務程序其他的優化方面•排序•空閉列表•檢查點10I/O和资源竞争11使用分布I/O减少磁盘竞争将数据文件和redolog文件分开Striping表数据分开表和索引减少与Oracle无关的磁盘I/O避免动态空间管理在创建如表或回滚段的数据库实体时，在数据库中会为这些数据分配空间，该空间被称为段。如果数据库操作引起数据增加并超出了分配的表空间，oracle会扩展该段，动态扩展会降低系统性能。12•调整排序，内存中排序使用动态表V$SYSSTAT的信息反映排序SELECTNAME,VALUEFROMV$SYSSTATWHERENAMEIN(‘SORTS(MEMORY)’,’SORTS(DISK)’);SORTS(MEMORY)-不需要使用I/O操作而完全在内存完成的排序数;SORTS(DISK)-需要使用I/O操作与磁盘临时段才能完成数据的排序数目。增大SORT_AREA_SIZE以避免磁盘排序•使用NOSORT创建非排序的索引CREATEINDEXINDEX_NAMEONTABLETABLE_NAME(COLUMN_NAME)NOSORT;13•调整Checkpoints一个checkpoint是oracle自动执行的一种操作，当检查点操作时，数据库中的所有缓冲区会写回磁盘，所有数据库的控制文件被更新。Checkpoint频繁发生会加快数据库的恢复，但是增加了I/O次数，会降低系统的性能。•调整LGWR和DBWnI/O•调整LGWRI/O每次I/O写的大小依赖于LOG缓冲区的大小，该大小由LOGBUFFER所设置，缓冲区太大会延迟写操作，太小可能导致频繁的小的I/O操作。如果I/O操作的平均大小很大，那么LOG文件就会成为瓶颈，可以使用STRIPEREDOLOG文件避免这个问题。•调整DBWNI/O使用初始参数DB_WRITER_PROCESSES，可以创建多个数据库写进程。14•调整竞争由多个进程同时请求使用相同的资源时，就产生了竞争。•确定竞争问题视图V$RESOURCE_LIMIT提供了一些系统资源的使用限制。如果系统存在无反应的现象，检查V$SYSTEM_EVENT，检查最大平均等待时间的事件;如果存在过量的缓冲区等待，检查V$WAITSTAT，确定哪个类型的块有最多的等待次数和最长的等待时间，再查询V$SESSION_WAIT得到每个缓冲区的等待时间。•减少回滚段的竞争通过检查V$WAITSTAT可以确定回滚段的竞争：SELECTCLASS,COUNTFROMV$WAITSTATWHERECLASSIN(‘SYSTEMUODOHEADER’,’SYSTEMUODOBLOCK’,’UODOHEADER’,’UODOBLOCK’);•减少调度进程的竞争检查调度进程的busy率SELECTNETWORK”PROTOCOL”,SUM(BUSY)/(SUM(BUSY)+SUM(IDLE))“TOTALBUSYRATE”FROMV$DISPATCHERGROUPBYNETWORK;如果指定协议的调度进程忙的时间超过50%的有效工作时间，那么，增加调度进程可以提高使用该协议连接到oracle的性能。15•检查调度进程相应队列的等待时间SELECTNETWORK“PROTOCOL”DECODE(SUM(TOTALQ),0.’NORESPONSES’,SUM(WAIT)/SUM(TOTALQ)||’HUNDREDTHSOFSECONDS’)“AVERAGEWAITTIMEPERRESPONSE”FROMV$QUEUEQ,V$DISPATCHERDWHEREQ.TYPE=’DISPATCHER’ANDQ.PADDR=D.PADDRGROUPBYNETWORK;增加调度进程：使用MTS_DISPATCHERS参数和ALTER_SYSTEM命令可以增加调度进程16•减少redolog缓冲区latches竞争在LGWR进程将redo入口从redolog缓冲区写入redolog文件后，该入口就会被新入口覆盖，供其他log的使用。V$SYSSTAT中redobufferallocationretries反映用户进程等待redolog空间的次数：Selectname,valuefromv$sysstatwherename=’redobufferallocationretries’;redobufferallocationretries的值应该接近0，如果该值持续增加，那么，说明进程需要等待缓冲区的空间。增大参数LOG_BUFFER的值可以增大redolog的大小。•确定redolog缓冲区latches竞争redo分配latch;redo复制latches。一次只能有一个用户分配缓冲区中的空间，在分配了redo入口的空间后，用户进程将入口复制到缓冲区，其最大大小是由LOG_SMALL_ENTRY_MAX_SIZE指定。Redo复制latches的数目由参数LOG_SIMULTANEOUS_COPIES指定。•检查redolog活动对redolog缓冲区的频繁访问可能导致redolog缓冲区latches竞争，降低系统性能。Oracle在动态表V$LATCH中收集了所有LATCH的统计信息。其中：表v$latch反映willing-to-wait请求的列gets-成功的willing-to-wait请求数;misses-初始不成功的willing-to-wait请求数;sleeps-请求不成功的等待时间;表v$latch反映immediate请求的列：immediategets-成功的immediate请求数immediatemisses-不成功的immediate请求数使用如下查询：selectln.name,gets,misses,immediate_gets,immediate_missesfromv$latchl,v$latchnamelnwhereln.namein(‘redoallocation’,’redocopy’)andln.latch#=l.latch#;可以计算出各类请求的等待率。17系統提高效率手法一．建立有效索引二．SQL優化,使用索引三．及時释放系统资源,如1.減少使用Session,application對象2.多使用COMMIT.3.盡早關閉db.connection四．多使用數據庫存儲過程.18其它•交叉表•Function•Package•View192021