Oracle 9i 数据库性能调优技术-les16
ORACLE 性能优化
ORACLE 数据库性能优化参考书目：《ORACLE 9i Database Performance Tuning Guide and Reference》《ORACLE 9i Database Reference》《ORACLE 9i SQL Reference》《ORACLE 9i Database Administrator’s Guide》一、数据库实例创建过程参数确定在创建数据库实例过程中，需要确定以下几个参数：1. 数据块大小（DB_BLOCK_SIZE）该参数指明了ORACLE所处理的数据存贮于数据文档以及SGA内存中的数据块大小。
该参数的可选择的范围为:4k,8k,16k,32k,64k。
对于OLTP系统而言，取值可以为4K或8K，对于DSS系统而言，则可以取较大的数据，如32K或64K 建议统一取8K（即8192）说明DB_BLOCK_SIZE的大小将影响创建表时的EXTENT的大小。
例如指定db_block_size=16K,某表空间的EXTENT MANAGEMENT 为local autoallocate，则其系统将extent的大小最小指定为1M.所以将可能导致空间的浪费。
2. 字符集(Character set)该参数确定数据库以何种字符集来存贮CHAR以及V ARCHAR、V ARCHAR2等字符类型的值。
对于ORACLE数据字典中的字符（如表及字段的COMMENT内容）具有同样的作用。
因此需要考虑如字符集的使用。
对于国际项目，因为数据库中的comment内容（包括表及字符、存贮过程中的中文字符等内容）可能性需要以中文存贮，而用户业务数据使用的字符可能性是使用本地的语言，基于此，该参数需要选择支持UNICODE的字符编码的字符集。
目前ORACLE9i支持以下二种UNICODE字符集：⏹UTF8⏹AL32UTF8建议统一取AL32UTF83. 扩展段管理(EXTENT MANAGEMENT)该参数指明表空间中的扩展段的管理方式。
Oracle 9i 和 10g 高性能调优
Oracle 9i 和10g 高性能调优介绍让我们从安装所需要的检查的事物开始来讨论oracle的优化。
调优环境调优的环境是什么？是一个你的调优努力能起作用的一种环境。
调整oralce数据库的所必需的东西⏹好多软件工具⏹训练有素的人⏹分段的测试环境⏹生产环境的一个副本○真实的和期望的生产环境。
在成长快速或者需求改变时，这些环境是经常不同的。
○经可能的使得数据库的大小/内容一致。
在不能完全满足这样的要求时，至少开发和测试数据库应该同生产数据库成比例○统计表是一样的吗？统计表可以拷贝，或者同生产数据库使用同样的时间间隔执行可用工具调整和监控数据库的优秀软件很多。
OEM有很多非常有用的小组件。
Spotlight擅长于对繁忙系统的视频和信息进行实时监控。
两者对调整数据库物理和SQL代码的性能分析都是非常有用的。
也有许多其他的工具可用。
在调整过程中最重要的工具是developer和administrators。
那是为什么你读这本书的原因。
最好的软件工具也意味着是最昂贵的，当然这也不是说较为便宜的工具是无用的了。
通常，越贵的软件为你所做的事情越多。
然而，有时候自动为你做了某些工作，你不理解其内部机制。
你的工具集未必比经过良好训练，经验丰富的数据库管理员和开发人员作得更好。
训练有素的人好的训练有其恰当的地方。
作为系统管理员或者开发者，数据库管理员趋向于有root权限。
每一种训练都有其优缺点。
开发者除了编码SQL，创建数据模块之外，所了解的知识越来越多。
系统管理员对unix之类的操作系统知识具有广泛的了解，关注的是oracle数据库的物理存储的调优。
开发者关注的是数据库模型和创建更为高效的SQL代码方面的优化。
幸的是，事情不总是如此。
有时候开发者趋向于把调优SQL代码和数据模型负担看着是数据库管理的负责范围。
这样就会导致混乱。
分段的环境你需要尽可能多的实验环境。
作为DBA，你不能期望在一个在线的生产数据库上进行调优工作。
oracle数据库性能调整与优化
Oracle9i数据库性能调整与优化V1.0在数据库成熟应用的时代，数据库的性能优化已经演变为一项相当严密的系统工程。
作为企业IT基础设施的核心部件之一，数据库并不是孤立的系统，它与网络、操作系统、存储等硬件系统紧密相连，这种与其他IT部件的多重连接特性决定了数据库性能优化是一门综合技术。
数据库性能优化的实现路径和IT系统管理架构越来越密不可分。
Oracle9i数据库在内部特性方面有着非常大增强，其中一个最令Oracle DBA兴奋莫过于可以动态设置全部Oracle SGA控制参数。
与8i不同是，原来都将初始化参数放到一个文本文件中，并且在数据库启动时候读取，Oracle9i却可以通过ALTER DATABASE 和ALTER SYSTEM命令复位全部Oracle参数。
在9i前，如果想对Oracle数据库处理模式作一些改变话，Oracle管理员必须关闭数据库并且重新设置INIT.ORA文件中参数，然后重新启动数据库。
对于白天使用OLTP 模式运作，晚上切换到数据仓库模式Oracle数据库来说，这种重新设置是经常做。
对于需要停止和重新启动Oracle数据库来修改参数来说，Oracle9i在这方面有明显加强，它令实现数据库连续可用目标变得更加简单。
定义：SGA：（System Global Area）是Oracle Instance的基本组成部分，在实例启动时分配。
是一组包含一个Oracle实例的数据和控制信息的共享内存结构。
主要是用于存储数据库信息的内存区，该信息为数据库进程所共享（PGA不能共享的）。
它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。
理论上SGA可占OS系统物理内存的1/2——1/3。
SGA几个很重要的特性：1、SGA的构成——数据和控制信息，我们下面会详细介绍；2、SGA是共享的，即当有多个用户同时登录了这个实例，SGA中的信息可以被它们同时访问（当涉及到互斥的问题时，由latch和enquence控制）；3、一个SGA只服务于一个实例，也就是说，当一台机器上有多个实例运行时，每个实例都有一个自己的SGA尽管SGA来自于OS的共享内存区，但实例之间不能相互访问对方的SGA区。
调整优化Oracle 9i数据库的性能
调整优化Oracle 9i数据库的性能
何月顺;丁秋林
【期刊名称】《计算机应用与软件》
【年(卷),期】2004(021)006
【摘要】介绍调整和优化Sun SPARC Solaris系统平台上的Oracle 9i数据库服务器的一些相关命令和方法.
【总页数】3页(P10-11,40)
【作者】何月顺;丁秋林
【作者单位】南京航空航天大学,南京,210016;东华理工学院,抚州,344000;南京航空航天大学,南京,210016
【正文语种】中文
【中图分类】TP311.13
【相关文献】
1.Oracle 9i数据库的性能调整和优化 [J], 尹成国
2.基于Oracle 9i数据库的查询优化 [J], 廖建华
3.Oracle 9i数据库性能优化 [J], 姚树春;朱艳琴
4.索引调整优化Oracle 9i工作性能的研究 [J], 谷小秋;李德昌
5.浅议如何调整优化Oracle 9i数据库的性能 [J], 胡铁峰
因版权原因，仅展示原文概要，查看原文内容请购买。
ORACLE数据库性能优化
（三）（三）优化 I/O 操作 I/O 优化被安排在内存优化之后，通过内存的优化，可以是 I/O 冲突减少，在此情况下， 可以通过一些调整以使 I/O 性能进一步提高。 对于新系统，应自顶向下分析 I/O 需求，确定所需要的资源。而对于已存在的系统应采 用自底向上的方法： 1．1．了解系统的磁盘数量。 2．2．了解 ORACLE 使用的磁盘数量。 3．3．了解应用系统的 I/O 类型。 4．4．了解 I/O 操作是针对文件系统还是原始设备。 5．5．了解对象在磁盘上的分布。 可以通过如下方法检查 I/O 问题： 检查系统 I/O 的使用：可以使用操作系统提供的工具来监视整个系统对磁盘 文件的访问，可以将大量访问磁盘的应用与 ORACLE 的相关文件分别存放。在 UNIX 系统中可以通过 sar –d 来获得有关数据。在 WINDOWS NT 中 可 通 过 性 能监视器查看。 检查 ORACLE 的 I/O 的使用：对于 ORACLE ，可以通过下列视图来获得相 关的信息： File Type Where to Find Statistics Database Files V$FILESTAT Log Files V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_EVENT Archive Files V$SYSTEM_EVENT, V$SESSION_EVENT Control Files V$SYSTEM_EVENT, V$SESSION_EVENT 可以通过如下的方法来解决 I/O 问题： 减少磁盘竞争： 磁盘竞争：当多个进程同时访问同一个磁盘时就会产生磁盘竞争。要减 少高负荷磁盘的访问，可以将高访问量的文件移到低负荷的磁盘上。 分离 Redo 日志文件和数据文件：ORACLE 总是经常的访问 Redo 日志 文件和数据文件，将二者放在一起，可能会增加磁盘冲突。 条带化表数据：条带化，就是将一个大表的数据分布到不同磁盘的不同 数据文件中，这样也可以减少磁盘冲突。 分离表和索引：这并不是必须的，由于索引和表的读取是串行的，也可 以做到将表和索引放在一起而不发生磁盘冲突。 磁盘条带化：就是将一个大表的数据分布到不同磁盘的不同数据文件中，条 带化允许不同的进程同时访问一个表的不同部分。 这尤其对随机访问一个表的多行 很有帮助。条带化可以是磁盘的 I/O 负载平衡。有两种条带化方法。 手动方法：利用表空间以及分区表的方式。
Oracle+9i+整体性能优化
Oracle 9i 整体性能优化概述草稿之一:调整争用2.1 优化维护 42.2 诊断LATCH竞争 42.2.1 概念 42.2.2 是否存在latch争用 52.2.3 检查Latch是否主要竞争 52.2.4 DBA关注的latch内容 52.3 诊断FREE LIST竞争 62.3.1 概念 62.3.2 是否存在free list争用 62.3.3 确定free list 争用的段 72.3.4 优化free list争用 72.4 诊断LOCK竞争 82.4.1 概念 82.4.2 可能引起lock contention的原因 82.4.3 锁解决办法 92.4.4 死锁 92 调整争用争用：每当一个Oracle 进程试图访问一个Oracle结构，但由于该结构正由另一个进程结构使用而未能成功访问到它时，就发生对Oracle资源的争用。
常见的有latch、Free List 、lock争用。
主要维护的争用有：Latch（锁存器）：可作为内存性能的指标，说明内存需要调整。
λFreeλ List：会导致繁忙表上的DML操作性能很差。
Lock：会遇到彻底的暂停，产生巨大的性能影响。
λ2.1优化维护维护时，主要通过检查，判断存在的latch和free list争用是否合理，不合理，则启动相应的优化工作。
而lock，在遇到问题的时候，可作为维护参考，平时不进行太多的维护（或从应用上考虑优化）。
（除了定期去$ORACLE_HOME/admin/$ORACLE_SID/udump查看死锁情况外）2.2诊断latch竞争2.2.1概念Latch是简单的、低层次的序列化技术，用以保护SGA中的共享数据结构，比如并发用户列表和buffer cache里的blocks信息。
一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch，在完成以后释放latch。
不必对latch本身进行优化，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用。
oracle数据库性能调整和优化涉及到那些问题
Oracle 数据库性能调整和优化涉及到那些问题数据库应用的类型是复杂的，有大量用户同时更新数据库的联机事务处理应用（如银行储蓄系统）、对海量数据进行查询并生成报告的数据仓库应用（如分析商场销售数据的决策支持系统）、在互联网上大量用户同时查询和更新数据的联机事务处理应用（如网上银行应用）等等。
为了满足与适应各种各样的商业应用，使各种不同的应用在不同的环境下都能达到最优的状态，Oracle 数据库系统提供了大量的非常灵活的可调节内容。
为了保证Oracle数据库运行在最佳的性能状态下，在信息系统开发之前就应该考虑数据库的优化策略。
优化策略一般包括服务器操作系统参数调整、数据库参数调整、网络性能调整、应用程序SQL语句分析及设计等几个方面，其中应用程序的分析与设计是在信息系统开发之前完成的。
分析评价Oracle数据库性能主要有数据库吞吐量、数据库用户响应时间两项指标。
数据库用户响应时间又可以分为系统服务时间和用户等待时间两项，即“数据库用户响应时间=系统服务时间＋用户等待时间”。
因此，获得满意的用户响应时间有两个途径：一是减少系统服务时间，即提高数据库的吞吐量；二是减少用户等待时间，即减少用户访问同一数据库资源的冲突率。
数据库性能优化包括如下几个部分：∙调整数据结构的设计这一部分在开发信息系统之前完成，程序员需要考虑是否使用Oracle数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。
∙调整应用程序结构设计这一部分也是在开发信息系统之前完成的。
程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。
不同的应用程序体系结构要求的数据库资源是不同的。
∙调整数据库SQL语句应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL 语句的执行效率最终决定了Oracle数据库的性能。
Oracle公司推荐使用Oracle语句优化器（Oracle Optimizer）和行锁管理器（Row-Level Manager）来调整优化SQL语句。
Oracle数据库的优化与调整
0
TRIGGER 1852 1844 0 0
Library cache命中率计算
• Library Cache Hit Ratio = sum(pinhits) / sum(pins)
• 上例中结果为.999466248
使用V$SHARED_POOL_ADVICE视图判 定共享池的调整量
视图主要各列意义：
• 例如：SET TRANSACTION USE ROLLBACK SEGMENT oltp_13
• Oracle对于大规模SQL语句自动扩展回 滚段，但这样显然会影响性能，正确 的做法是实现分配好适宜的回滚段。
CPU的调整
• 效劳器良好的工作状态表现为在工作顶峰时 CPU的使用率高于90％。如果空闲时间CPU使 用率就在90％以上，说明效劳器缺乏CPU资源
日志缓冲区
LGWR进程在下述情况下将日志缓冲区 写入日志文件： 日志缓冲区1/3的时候 用户commit或者rollback的时候 DBWR进展写数据文件的时候
日志缓冲区的调整方法1
• 查看V$SYSSTAT视图： SELECT name, value FROM v$sysstat WHERE name = 'redo buffer allocation retries';
reads’,’physical reads’,’physical reads direct’,’physical reads direct (lob)’)
• 查询结果为 NAME VALUE ------------ ---------session logical reads 464905358 physical reads 10380487 physical reads direct 86850 physical reads direct (lob) 0
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
16-4
Copyright © Oracle Corporation, 2002. All rights reserved.
Refreshing Materialized Views
The required parameters are: • A comma-delimited list of materialized views to refresh • The refresh method: F-Fast, ?-Force, C-Complete • Refresh after errors
– True: allows the process to continue after an error – False: refresh will stop with errors (default value)
• •
For warehouse refresh, set them to False, 0,0,0. Atomic refresh
SQL> select operation, object_name 2 from v$sql_plan 3 where object_name like 'SALES%'; OPERATION NAME ---------------------- ----------------SELECT STATEMENT TABLE ACCESS SALES_SUMMARY
16-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Materialized Views
• • •
Instantiations of a SQL query May be used for query rewrites Refresh types:
16-5 Copyright © Oracle Corporation, 2002. All rights reserved.
Materialized Views: Manual Refreshing
•
Refresh specific materialized views:
dbms_mview.refresh (’CUST_SALES’, parallelism => 10);
dbms_mview.refresh_all_mviews;
16-7
Copyright © Oracle Corporation, 2002. All rights reserved.
Nested Materialized Views
TOTAL_SALES
Level 2
PROD_MV
SALES_CUST_MV
– Ignore alphabetic case – Recognize equivalent joins – Compare the defining text of a named view
16-14
Copyright © Oracle Corporation, 2002. 3 4
SELECT p.operation, p.object_name FROM v$sql_plan p, v$sql s WHERE p.address = s.address AND sql_text LIKE 'SELECT /*+ NO%';
16-20
Copyright © Oracle Corporation, 2002. All rights reserved.
•
Refresh materialized views based on one or more base tables:
dbms_mview.refresh_dependent (’SALES’);
•
Refresh all materialized views that are due to be refreshed:
16-17 Copyright © Oracle Corporation, 2002. All rights reserved.
Enabling and Controlling Query Rewrites
• Initialization parameters: – OPTIMIZER_MODE – QUERY_REWRITE_ENABLED – QUERY_REWRITE_INTEGRITY Dynamic and session-level parameters: – QUERY_REWRITE_ENABLED – QUERY_REWRITE_INTEGRITY Hints: REWRITE and NOREWRITE Dimensions
Creating Materialized Views
SQL> 2 3 4 5 6
CREATE MATERIALIZED VIEW depart_sal_sum AS SELECT d.department_name, SUM(e.salary) FROM hr.departments d, hr.employees e WHERE d.department_id = e.department_id GROUP BY d.department_name;
– Complete or Fast – Force or Never
•
Refresh modes:
– Manual – Automated (synchronous or asynchronous)
16-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Using Materialized Views
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following: • Create materialized views • Refresh materialized views • Create nested materialized views • Create UNION ALL materialized views • • Explain the use of query rewrites Enable and control query rewrites
Union All Materialized Views
16-12
Copyright © Oracle Corporation, 2002. All rights reserved.
Query Rewrite Overview
• • • •
To use materialized views instead of the base tables, a query must be rewritten. Query rewrites are transparent and do not require any special privileges on the materialized view. Materialized views can be enabled or disabled for query rewrites. Query rewrites can:
– True: All refreshes are done in one transaction – False: Each refresh is a separate transaction
SQL> EXEC dbms_mview.refresh ('SALES_MV', 2 'F', '', TRUE, FALSE, 0,0,0, FALSE);
Union All Query Rewrite
CREATE MATERIALIZED VIEW sales_cube_mv ENABLE QUERY REWRITE AS SELECT ... GROUPING_ID(calendar_year,……) gid, GROUPING(calendar_year) grp_y, ... GROUPING(cust_city) grp_c, FROM sales s, times t, customers c WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id GROUP BY GROUPING SETS( (calendar_year, cust_city), (calendar_year,..., cust_state_province), (calendar_year,..., cust_city));
16-16
Copyright © Oracle Corporation, 2002. All rights reserved.
Materialized Views and Query Rewrites: Example
SQL> 2 3 4 5 SELECT p.prod_name,SUM (s.quantity_sold), SUM (s.amount_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;
Query Rewrites
• • •
The QUERY_REWRITE_ENABLED initialization parameter must be set to True. The QUERY REWRITE privilege allows users to enable materialized views. The Summary Advisor of the dbms_olap package has options to use materialized views.