`

数据库优化

 
阅读更多

转:http://www.iteye.com/topic/1118860

 


  数据库优化的讨论可以说是一个永恒的主题。资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack,贴出数据库配置等等。 还有的人认为要抓出执行最慢的语句来进行优化。但实际情况是,提出疑问的人很可能根本不懂执行计划,更不要说statspack了。而我认为,数据库优 化,应该首先从大的方面考虑:网络、服务器硬件配置、操作系统配置、Oracle服务器配置、数据结构组织、然后才是具体的调整。实际上网络、硬件等往往 无法决定更换,应用程序一般也无法修改,因此应该着重从数据库配置、数据结构上来下手,首先让数据库有一个良好的配置,然后再考虑具体优化某些过慢的语 句。

    我在给我的用户系统进行优化的过程中,总结了一些基本的,简单易行的办法来优化数据库,算是我的三板斧,呵呵。不过请注意,这些不一定普遍使用,甚至有的 会有副作用,但是对OLTP系统、基于成本的数据库往往行之有效,不妨试试。(注:附件是Burleson写的用来报告数据库性能等信息的脚本,本文用 到)

相关文章:

1.oracle的表空间、分区表、以及索引的总结
      2.oracle表结构设计浅谈
      3.oracle函数大全

    一.设置合适的SGA

    常常有人抱怨服务器硬件很好,但是Oracle就是很慢。很可能是内存分 配不合理造成的。(1)假设内存有512M,这通常是小型应用。建议Oracle的SGA大约240M,其中:共享池 (SHARED_POOL_SIZE)可以设置60M到80M,根据实际的用户数、查询等来定。数据块缓冲区可以大致分配120M-150M,8i下需要 设置DB_BLOCK_BUFFERS,DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于数据块缓冲区大小。9i 下的数据缓冲区可以用db_cache_size来直接分配。

    (2)假设内存有1G,Oracle 的SGA可以考虑分配500M:共享池分配100M到150M,数据缓冲区分配300M到400M。

    (3)内存2G,SGA可以考虑分配1.2G,共享池300M到500M,剩下的给数据块缓冲区。

    (4)内存2G以上:共享池300M到500M就足够啦,再多也没有太大帮助;(Biti_rainy有专述)数据缓冲区是尽可能的大,但是一定要注意两 个问题:一是要给操作系统和其他应用留够内存,二是对于32位的操作系统,Oracle的SGA有1.75G的限制。有的32位操作系统上可以突破这个限 制,方法还请看Biti的大作吧。

  二.分析表和索引,更改优化模式

    Oracle默认优化模式是CHOOSE,在这种情况下,如果表没有经过分析,经常导致查询使用全表扫描,而不使用索引。这通常导致磁盘I/O太多,而导 致查询很慢。如果没有使用执行计划稳定性,则应该把表和索引都分析一下,这样可能直接会使查询速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。对于少于100万的表,可以考虑分析整个表,对于很大的表,可以按百分比来分析,但是百分比不能过低,否则生成的统计信息可能不准确。可 以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间,索引可以通过DBA_INDEXES的 LAST_ANALYZED列。

    下面通过例子来说明分析前后的速度对比。(表CASE_GA_AJZLZ大约有35万数据,有主键)首先在SQLPLUS中打开自动查询执行计划功能。(第一次要执行\RDBMS\ADMIN\utlxplan.sql来创建PLAN_TABLE这个表)

    SQL> SET AUTOTRACE ON
    SQL>SET TIMING ON

    通过SET AUTOTRACE ON 来查看语句的执行计划,通过SET TIMING ON 来查看语句运行时间。

    SQL> select count(*) from CASE_GA_AJZLZ;
    COUNT(*)
    ----------
    346639
    已用时间: 00: 00: 21.38
    Execution Plan
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (FULL) OF 'CASE_GA_AJZLZ'
    ……………………

    请注意上面分析中的TABLE ACCESS(FULL),这说明该语句执行了全表扫描。而且查询使用了21.38秒。这时表还没有经过分析。下面我们来对该表进行分析:

    SQL> analyze table CASE_GA_AJZLZ compute statistics;

    表已分析。已用时间: 00: 05: 357.63。然后再来查询:

    SQL> select count(*) from CASE_GA_AJZLZ;
    COUNT(*)
    ----------
    346639
    已用时间: 00: 00: 00.71
    Execution Plan
   
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=351 Card=1)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FAST FULL SCAN) OF 'PK_AJZLZ' (UNIQUE) (Cost=351
    Card=346351)
    …………………………

    请注意,这次时间仅仅用了0.71秒!这要归功于INDEX(FAST FULL SCAN)。通过分析表,查询使用了PK_AJZLZ索引,磁盘I/O大幅减少,速度也大幅提升!下面的实用语句可以用来生成分析某个用户的所有表和索 引,假设用户是GAXZUSR:

    SQL> set pagesize 0
    SQL> spool d:\analyze_tables.sql;
    SQL> select 'analyze table '||owner||'.'||table_name||' 
    compute statistics;' from dba_tables where owner='GAXZUSR';
    SQL> spool off
    SQL> spool spool d:\analyze_indexes.sql;
    SQL> select 'analyze index '||owner||'.'||index_name||' 
    compute statistics;' from dba_indexes where owner='GAXZUSR';
    SQL> spool off
    SQL> @d:\analyze_tables.sql
    SQL> @d:\analyze_indexes.sql

    解释:上面的语句生成了两个sql文件,分别分析全部的GAXZUSR的表和索引。如果需要按照百分比来分析表,可以修改一下脚本。通过上面的步骤,我们就完成了对表和索引的分析,可以测试一下速度的改进啦。建议定期运行上面的语句,尤其是数据经过大量更新。

    当然,也可以通过dbms_stats来分析表和索引,更方便一些。但是我仍然习惯上面的方法,因为成功与否会直接提示出来

    另外,我们可以将优化模式进行修改。optimizer_mode值可以是RULE、CHOOSE、FIRST_ROWS和ALL_ROWS。对于 OLTP系统,可以改成FIRST_ROWS,来要求查询尽快返回结果。这样即使不用分析,在一般情况下也可以提高查询性能。但是表和索引经过分析后有助 于找到最合适的执行计划。

    三.设置cursor_sharing=FORCE 或SIMILAR

    这种方法是8i才开始有的,oracle805不支持。通过设置该参数,可以强制共享只有文字不同的语句解释计划。例如下面两条语句可以共享:

    SQL> SELECT * FROM MYTABLE WHERE NAME='tom'
    SQL> SELECT * FROM MYTABLE WHERE NAME='turner'

    这个方法可以大幅降低缓冲区利用率低的问题,避免语句重新解释。通过这个功能,可以很大程度上解决硬解析带来的性能下降的问题。个人感觉可根据系统的实际 情况,决定是否将该参数改成FORCE。该参数默认是exact。不过一定要注意,修改之前,必须先给ORACLE打补丁,否则改之后oracle会占用 100%的CPU,无法使用。对于ORACLE9i,可以设置成SIMILAR,这个设置综合了FORCE和EXACT的优点。不过请慎用这个功能,这个 参数也可能带来很大的负面影响!

    四.将常用的小表、索引钉在数据缓存KEEP池中

    内存上数据读取速度远远比硬盘中 读取要快,据称,内存中数据读的速度是硬盘的14000倍!如果资源比较丰富,把常用的小的、而且经常进行全表扫描的表给钉内存中,当然是在好不过了。可 以简单的通过ALTER TABLE tablename CACHE来实现,在ORACLE8i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP)。一般来说,可以考虑把200数据块之内的表放在keep池中,当然要根据内存大小等因素来定。关于如何查出那些表或索引符合条件,可以使用本 文提供的access.sql和access_report.sql。这两个脚本是著名的Oracle专家 Burleson写的,你也可以在读懂了情况下根据实际情况调整一下脚本。对于索引,可以通过ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)来钉在KEEP池中。

    将表定在KEEP池中需要做一些准备工作。对于ORACLE9i 需要设置DB_KEEP_CACHE_SIZE,对于8i,需要设置buffer_pool_keep。在8i中,还要修改 db_block_lru_latches,该参数默认是1,无法使用buffer_pool_keep。该参数应该比2*3*CPU数量少,但是要大于 1,才能设置DB_KEEP_CACHE_BUFFER。buffer_pool_keep从db_block_buffers中分配,因此也要小于 db_block_buffers。设置好这些参数后,就可以把常用对象永久钉在内存里。

    五.设置optimizer_max_permutations

    对于多表连接查询,如果采用基于成本优化(CBO),ORACLE会计算出很多种运行方案,从中选择出最优方案。这个参数就是设置oracle究竟从多少 种方案来选择最优。如果设置太大,那么计算最优方案过程也是时间比较长的。Oracle805和8i默认是80000,8建议改成2000。对于9i,已 经默认是2000了。

    六.调整排序参数

    (1) SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是32K,通常显得有点小,一般可以考虑设置成1M(1048576)。这个参数不能设置过大,因为每个连接都要分配同样的排序内存。

    (2) SORT_MULTIBLOCK_READ_COUNT:增大这个参数可以提高临时表空间排序性能,该参数默认是2,可以改成32来对比一下排序查询时间变化。注意,这个参数的最大值与平台有关系.

=================

update mzyzqd a set a.nid=(select b.mtid from mx_temp b where a.lsh=b.lsh and a.no=b.no and a.sj=b.sj)
where exists
(
  select 1 from mx_temp b where a.lsh=b.lsh and a.no=b.no and a.sj=b.sj
);==>

update temp_ord_app_cost c set c.csum=1
where
c.id='00141117'
and c.app='02'
and (c.lsh,c.rq)in(
                select b.lsh,b.rq
                from (
                  SELECT
                  ROW_NUMBER()OVER(PARTITION BY to_char(t.rq,'yyyymmdd') ORDER BY t.rq asc) as LEV
                  ,t.lsh
                  ,t.rq
                  ,count(0) over(PARTITION BY to_char(t.rq,'yyyymmdd')) as count               
                  FROM temp_ord_app_cost t
                  where t.id='00141117' and t.app='02'
                      ) b
                where LEV=1
               )

分享到:
评论

相关推荐

    非常好的数据库优化教程02

    【非常棒的数据库优化教程02】 在数据库管理中,优化是提高系统性能的关键环节,尤其是在处理大量数据和复杂查询时。本章节主要探讨如何利用MySQL进行有效的数据库优化,包括索引的使用、选择合适的列类型、优化SQL...

    mysql数据库优化的学习心得

    MySQL 数据库优化技巧总结 MySQL 数据库优化是数据库管理和开发者非常关心的一个问题。由于 MySQL 数据库的性能优化可以直接影响到整个应用系统的性能和用户体验,因此掌握 MySQL 数据库优化技巧对开发者和数据库...

    sql数据库优化方案

    在IT行业中,数据库优化是提升系统性能的关键环节,尤其是在处理大量数据时,SQL数据库的优化显得尤为重要。本文将深入探讨SQL数据库优化方案,包括查询优化、索引优化、存储优化和架构优化等策略,旨在帮助你提升...

    SQL数据库优化工具

    当数据库性能下降时,"SQL数据库优化工具"就显得至关重要。这些工具能够帮助我们诊断问题、提高查询效率并优化整体数据库结构,确保系统运行顺畅。 一、SQL查询优化 SQL查询优化是数据库优化的关键部分,它涉及到...

    mysql数据库优化方案(值得学习)

    简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用,合适学习mysql的读者。 简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用,合适学习...

    数据库优化大全(面试常问到)

    数据库优化是IT领域中至关重要的一个环节,尤其是在大数据量、高并发的系统中,数据库性能的优劣直接影响到系统的响应速度和用户体验。本压缩包包含了关于数据库优化的多个文档,如"SQL语句优化.doc"、"数据库优化:...

    《数据库优化与集成之实战和效果 郑保卫》PDF课件

    《数据库优化与集成之实战和效果》是由郑保卫编著的一本关于数据库管理与优化的专业教材,主要针对数据库系统在实际应用中的性能提升和不同数据库的整合策略进行深入探讨。PDF课件形式使得读者可以方便地在线学习或...

    SQL数据库优化方法

    在IT领域,数据库优化是确保系统性能的关键环节,特别是对于SQL Server这样的大型关系型数据库管理系统。本文将深入探讨SQL数据库的优化方法,包括网络优化、硬件优化、操作系统优化、数据库参数调整以及应用程序...

    Oracle数据库优化详解

    Oracle数据库优化是一门深入的技术领域,它关系到数据库性能的提升和资源的高效利用。本文针对Oracle数据库的优化提供了详尽的指导和案例分析,尤其强调了在CBO(Cost-Based Optimizer,基于成本的优化器)模式下的...

    C#源码 访问ACCESS数据库 通过反射调用命令 清理数据库 执行数据库优化 压缩操作

    数据库优化包括多个方面,如索引优化、查询优化和存储优化。在Access中,可能需要检查和调整索引以提高查询速度,分析查询性能,以及合理分配内存资源。C#可以通过执行存储过程或者自定义的SQL脚本来完成这些任务,...

    sql server 2005数据库优化方法

    在SQL Server 2005中,数据库优化是提高系统性能和效率的关键环节。本文将介绍两种主要的数据库优化工具:SQL Server Profiler和数据库引擎优化顾问(Database Engine Tuning Advisor),以及它们如何帮助DBA们更好...

    ORACLE数据库优化基础培训.zip

    进行Oracle数据库优化是提升系统性能、确保数据高效稳定运行的关键。本次基础培训将重点聚焦在SQL语句性能优化、数据库的常用管理命令以及常见问题处理上。 一、Oracle之SQL语句性能优化 SQL(Structured Query ...

    Oracle 数据库优化技术手册.pdf

    Oracle数据库优化是一项系统工程,涉及程序设计、操作系统优化、硬件配置、数据库配置以及SQL语句等多个层面。为了提高Oracle数据库的性能,需要对这些层面进行综合考虑,逐一优化。 首先,硬件优化是基础,主要...

    ( 数据库优化方面.rar )

    ( 数据库优化方面.rar ) ( 数据库优化方面.rar ) ( 数据库优化方面.rar ) ( 数据库优化方面.rar ) ( 数据库优化方面.rar ) ( 数据库优化方面.rar ) ( 数据库优化方面.rar ) ( 数据库优化方面.rar ) ( 数据库优化方面...

    数据库优化培训课件

    数据库优化培训课件,供大家一起参考学习。

    oracle 数据库优化

    Oracle数据库优化是一个复杂而关键的领域,涉及到许多层面,包括SQL查询优化、索引策略、内存结构调整、数据库架构设计以及硬件配置等。以下将详细阐述这些知识点。 首先,SQL查询优化是数据库优化的核心部分。...

    【整理】数据库面试题索引sql优化+数据库SQL优化总结之百万级数据库优化

    接下来,"数据库SQL优化总结之百万级数据库优化.pdf"可能深入到实际的优化实践。 1. **数据库架构设计**:在百万级数据量下,合理的设计能避免性能瓶颈,如垂直分割、水平分割,以及读写分离策略。 2. **缓存与...

Global site tag (gtag.js) - Google Analytics