怎么提高oracle速度,oracle 提高insert速度
如何提高oracle的查询速度
几个简单的步骤大幅提高Oracle性能--我优化数据库的三板斧。
成都创新互联公司 - 服务器托管,四川服务器租用,成都服务器租用,四川网通托管,绵阳服务器托管,德阳服务器托管,遂宁服务器托管,绵阳服务器托管,四川云主机,成都云主机,西南云主机,服务器托管,西南服务器托管,四川/成都大带宽,机柜大带宽,四川老牌IDC服务商
数据库优化的讨论可以说是一个永恒的主题。资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack,贴出数据库配置等等。还有的人认为要抓出执行最慢的语句来进行优化。但实际情况是,提出疑问的人很可能根本不懂执行计划,更不要说statspack了。而我认为,数据库优化,应该首先从大的方面考虑:网络、服务器硬件配置、操作系统配置、Oracle服务器配置、数据结构组织、然后才是具体的调整。实际上网络、硬件等往往无法决定更换,应用程序一般也无法修改,因此应该着重从数据库配置、数据结构上来下手,首先让数据库有一个良好的配置,然后再考虑具体优化某些过慢的语句。我在给我的用户系统进行优化的过程中,总结了一些基本的,简单易行的办法来优化数据库,算是我的三板斧,呵呵。不过请注意,这些不一定普遍使用,甚至有的会有副作用,但是对OLTP系统、基于成本的数据库往往行之有效,不妨试试。(注:附件是Burleson写的用来报告数据库性能等信息的脚本,本文用到)
一.设置合适的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
SQLSET 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来对比一下排序查询时间变化。注意,这个参数的最大值与平台有关系。
如何提高oracle模糊查询的性能?
1、使用两边加‘%’号的查询,Oracle是不通过索引的,所以查询效率很低。
例如:select count(*) from lui_user_base t where t.user_name like '%cs%';
2、like '...%'和 like'%...'虽然走了索引,但是效率依然很低。
3、有人说使用如下sql,他的效率提高了10倍,但是数据量小的时候
select count(*) from lui_user_base where rowid in (select rowid from lui_user_base t where t.user_name like '%cs%')
我拿100w跳数据做了测试,效果一般,依然很慢,原因:
select rowid from lui_user_base t where t.user_name like '%cs%' 这条sql执行很快,那是相当的快,但是放到select count(*) from lui_user_base where rowid in()里后,效率就会变的很慢了。
4、select count(*) from lui_user_base t where instr(t.user_name,'cs') 0
这种查询效果很好,速度很快,推荐使用这种。因为我对oracle内部机制不是很懂,只是对结果做了一个说明。
5、有人说了用全文索引,我看了,步骤挺麻烦,但是是个不错的方法,留着备用:
对cmng_custominfo 表中的address字段做全文检索:
1,在oracle9201中需要创建一个分词的东西:
BEGIN
ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');
--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用
end;
2,创建全文检索:
CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');
3,查询时候,使用:
select * from cmng_custominfo where contains (address, '金色新城')1;
4,需要定期进行同步和优化:
同步:根据新增记录的文本内容更新全文搜索的索引。
begin
ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');
end;
优化:根据被删除记录清除全文搜索索引中的垃圾
begin
ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');
end;
5,采用job做步骤4中的工作:
1)该功能需要利用oracle的JOB功能来完成
因为oracle9I默认不启用JOB功能,所以首先需要增加ORACLE数据库实例的JOB配置参数:
job_queue_processes=5
重新启动oracle数据库服务和listener服务。
2)同步 和 优化
--同步 sync:
variable jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');',
SYSDATE, 'SYSDATE + (1/24/4)');
commit;
END;
--优化
variable jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');
commit;
END;
其中, 第一个job的SYSDATE + (1/24/4)是指每隔15分钟同步一次,第二个job的SYSDATE + 1是每隔1天做一次全优化。具体的时间间隔,可以根据应用的需要而定。
6,索引重建
重建索引会删除原来的索引,重新生成索引,需要较长的时间。
重建索引语法如下:
ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;
据网上一些用家的体会,oracle重建索引的速度也是比较快的,有一用家这样描述:
Oracle 的全文检索建立和维护索引要比ms sql server都要快得多,笔者的65万记录的一个表建立索引只需要20分钟,同步一次只需要1分钟。
因此,也可以考虑用job的办法定期重建索引。
oracle速度变慢,怎样解决
这个 速度慢 是指 使用数据库过程中 查询数据缓慢 还是 数据库运行过程中各项功能缓慢? 如果是查询数据缓慢的话,基本上是因为 与 查询语句逻辑、表结构、执行计划相关,可以根据数据库具体执行计划优化数据库查询,比如 要查询的表没有对应的索引,可以添加索引提高查询速度;或者 要查询的表有过大量的删除与更新,则需要更新表的统计信息以提高查询速度。如果是数据库本身各项功能运行缓慢,那大多是数据库系统各项参数设置不合理,与服务器本身的系统的硬件和系统有关,可以更改数据库相关的系统参数,调整数据库运行所需要的参数以提高数据库运行质量。
如何提高oracle视图的查询速度?
1、可以缩小到5张表,因为很多都是从一张表里取出来的数据;
2、不能子查询因为是要显示数据子查询只是查询条件;
3不能建立索引,因为这样会影响表的增删改,它里面都是导入进去的一次增加上千条都有可能;
4、定期结转是什么意思,表示没看懂。时间发的太长的话就算了;
5、定期结转的意思就是,将你要建立视图的几种表数据“转移”到一张新表里面去,不用视图查询。数据库全文检索是RDBMS自带的扩展功能,可以实现高速查询。全文检索建议搜索下关键字,什么lucene之类的就出来了。
oracle数据库的性能优化有哪些方法?
你最好买一本专门讲ORACLE性能优化的书,好好看看\x0d\x0a1、调整数据库服务器的性能\x0d\x0aOracle数据库服务器是整个系统的核心,它的性能高低直接影响整个系统的性能,为了调整Oracle数据库服务器的性能,主要从以下几个方面考虑: \x0d\x0a1.1、调整操作系统以适合Oracle数据库服务器运行\x0d\x0aOracle数据库服务器很大程度上依赖于运行服务器的操作系统,如果操作系统不能提供最好性能,那么无论如何调整,Oracle数据库服务器也无法发挥其应有的性能。 \x0d\x0a1.1.1、为Oracle数据库服务器规划系统资源 \x0d\x0a据已有计算机可用资源, 规划分配给Oracle服务器资源原则是:尽可能使Oracle服务器使用资源最大化,特别在Client/Server中尽量让服务器上所有资源都来运行Oracle服务。 \x0d\x0a1.1.2、调整计算机系统中的内存配置 \x0d\x0a多数操作系统都用虚存来模拟计算机上更大的内存,它实际上是硬盘上的一定的磁盘空间。当实际的内存空间不能满足应用软件的要求时,操作系统就将用这部分的磁盘空间对内存中的信息进行页面替换,这将引起大量的磁盘I/O操作,使整个服务器的性能下降。为了避免过多地使用虚存,应加大计算机的内存。 \x0d\x0a1.1.3、为Oracle数据库服务器设置操作系统进程优先级 \x0d\x0a不要在操作系统中调整Oracle进程的优先级,因为在Oracle数据库系统中,所有的后台和前台数据库服务器进程执行的是同等重要的工作,需要同等的优先级。所以在安装时,让所有的数据库服务器进程都使用缺省的优先级运行。 \x0d\x0a1.2、调整内存分配\x0d\x0aOracle数据库服务器保留3个基本的内存高速缓存,分别对应3种不同类型的数据:库高速缓存,字典高速缓存和缓冲区高速缓存。库高速缓存和字典高速缓存一起构成共享池,共享池再加上缓冲区高速缓存便构成了系统全程区(SGA)。SGA是对数据库数据进行快速访问的一个系统全程区,若SGA本身需要频繁地进行释放、分配,则不能达到快速访问数据的目的,因此应把SGA放在主存中,不要放在虚拟内存中。内存的调整主要是指调整组成SGA的内存结构的大小来提高系统性能,由于Oracle数据库服务器的内存结构需求与应用密切相关,所以内存结构的调整应在磁盘I/O调整之前进行。 \x0d\x0a1.2.1、库缓冲区的调整 \x0d\x0a库缓冲区中包含私用和共享SQL和PL/SQL区,通过比较库缓冲区的命中率决定它的大小。要调整库缓冲区,必须首先了解该库缓冲区的活动情况,库缓冲区的活动统计信息保留在动态性能表v$librarycache数据字典中,可通过查询该表来了解其活动情况,以决定如何调整。 \x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache; \x0d\x0a \x0d\x0aPins列给出SQL语句,PL/SQL块及被访问对象定义的总次数;Reloads列给出SQL 和PL/SQL块的隐式分析或对象定义重装载时在库程序缓冲区中发生的错误。如果sum(pins)/sum(reloads) ≈0,则库缓冲区的命中率合适;若sum(pins)/sum(reloads)1, 则需调整初始化参数 shared_pool_size来重新调整分配给共享池的内存量。 \x0d\x0a1.2.2、数据字典缓冲区的调整 \x0d\x0a数据字典缓冲区包含了有关数据库的结构、用户、实体信息。数据字典的命中率,对系统性能影响极大。数据字典缓冲区的使用情况记录在动态性能表v$librarycache中,可通过查询该表来了解其活动情况,以决定如何调整。 \x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache; \x0d\x0a \x0d\x0aGets列是对相应项请求次数的统计;Getmisses 列是引起缓冲区出错的数据的请求次数。对于频繁访问的数据字典缓冲区,sum(getmisses)/sum(gets)10%~15%。若大于此百分数,则应考虑增加数据字典缓冲区的容量,即需调整初始化参数shared_pool_size来重新调整分配给共享池的内存量。 \x0d\x0a1.2.3、缓冲区高速缓存的调整 \x0d\x0a用户进程所存取的所有数据都是经过缓冲区高速缓存来存取,所以该部分的命中率,对性能至关重要。缓冲区高速缓存的使用情况记录在动态性能表v$sysstat中,可通过查询该表来了解其活动情况,以决定如何调整。 \x0d\x0a \x0d\x0aSelect name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads'); \x0d\x0a \x0d\x0adbblock gets和consistent gets的值是请求数据缓冲区中读的总次数。physical reads的值是请求数据时引起从盘中读文件的次数。从缓冲区高速缓存中读的可能性的高低称为缓冲区的命中率,计算公式: \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets+consistent gets)) \x0d\x0a \x0d\x0a如果Hit Ratio60%~70%,则应增大db_block_buffers的参数值。db_block_buffers可以调整分配给缓冲区高速缓存的内存量,即db_block_buffers可设置分配缓冲区高速缓存的数据块的个数。缓冲区高速缓存的总字节数=db_block_buffers的值*db_block_size的值。db_block_size 的值表示数据块大小的字节数,可查询 v$parameter 表: \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size'; \x0d\x0a \x0d\x0a在修改了上述数据库的初始化参数以后,必须先关闭数据库,在重新启动数据库后才能使新的设置起作用。
oracle 对于多个大表关联操作如何优化速度?
1、首先要建立适当的索引。sql在索引字段不要加函数,保证索引起效。如果是复合索引注意在sql的顺序。如果已经存在索引,建议你先重建索引先,因为大数据表的索引维护到了一个阶段就是乱的,一般建议重建。建立好的一般可以获得几十倍的速度提升。
2、最大数据量的表放在最前,最小的表放在最后面。sql是从最后面开始反向解析的。
3、其次是要把最有效缩小范围的条件放到sql末尾去。尤其是主键或者索引字段的条件。
4、保证你sql的算法合理性。保证复杂度和空间度的合理性。
5、必要时候使用存储过程。提升30%-40%的速度
6、建议你分页读取不要一下读完所有的数据。(使用rownum),一下子数据太多会使得内存不够用的。
如果这些都做了还不满意的话,可以考虑建立几个表空间,然后按照一个算法将各个表的数据,平均的放在各个表空间内(分表分区),在select的时候数据库就会使用多线程到各个表空间索引数据,这个一般不是上千万级的表是不用的。
也不是所有人都会用。
网站标题:怎么提高oracle速度,oracle 提高insert速度
标题路径:http://myzitong.com/article/heeepg.html