oracle调简单介绍.docVIP

Oracle调优简单介绍 1、mts连接模式 1 2、SGA内存配置 2 3、系统中涉及多个schema 4 4、数据库中存在安装时附带的sample 4 5、语句执行效率低 4 6、操作系统限制每个文件最大1G 5 7、更新数据库的内部统计值 5 8、使用perfstat检查并发时刻oracle的运行状态 6 1、mts连接模式 在并发测试时发现oracle报错： ORA-04031: unable to allocate 86400 bytes of shared memory (large pool,unknown object,hash-join subh,kllcqc:kllcqslt) large_pool一般只在使用rman备份或数据库处在mts连接模式才会用到。 在没有使用rman备份的情况下，查询初始化参数发现： ??? NAME VALUE 1 mts_circuits 885 2 mts_dispatchers (PROTOCOL=TCP)?(SERVICE=oraarpXDB),?(PROTOCOL=TCP) 3 mts_listener_address ? 4 mts_max_dispatchers 5 5 mts_max_servers 20 6 mts_multiple_listeners FALSE 7 mts_servers 1 8 mts_service oraarp 9 mts_sessions 880 说明数据库运行在mts连接模式下。 在硬件环境富裕的情况下建议采用专用模式(DEDICATED) 方法： 注销初始化参数文件中的这句话： dispatchers = (PROTOCOL=TCP) (SERVICE=oraarpXDB), (PROTOCOL=TCP) 客户端连接时选择专用数据库模式或缺省模式 2、SGA内存配置 查询初始化参数发现内存配置如下 ??? POOL NAME BYTES 1 ? fixed_sga 744256 2 ? buffer_cache 201326592 3 ? log_buffer 787456 4 shared?pool enqueue 1475952 5 shared?pool DML?lock 757904 6 shared?pool KGK?heap 7000 7 shared?pool KQR?L?PO 547872 8 shared?pool KQR?M?PO 216624 9 shared?pool KQR?S?SO 4376 10 shared?pool sessions 2393040 11 shared?pool sql?area 2938520 12 shared?pool 1M?buffer 1056768 13 shared?pool KGLS?heap 1181480 14 shared?pool processes 1024000 15 shared?pool db_handles 928000 16 shared?pool parameters 4256 17 shared?pool free?memory 532403128 18 shared?pool transaction 1590776 19 shared?pool PL/SQL?DIANA 478248 20 shared?pool ksfv?subheap 4240 21 shared?pool FileOpenBlock 6028960 22 shared?pool PL/SQL?MPCODE 83328 23 shared?pool library?cache 4217608 24 shared?pool miscellaneous 25 shared?pool MTTR?advisory? 51040 26 shared?pool PLS?non-lib?hp 2088 27 shared?pool joxs?heap?init 4240 28 shared?pool sim?memory?hea 174800 29 shared?pool table?definiti 560 30 shared?pool trigger?defini 2472 31 shared?pool trigger?inform 1704 32 shared?pool trigger?source 616 33 shared?pool VIRTUAL?CIRCUITS 1802000 3