不得不说的Oracle性能调整与优化
今天，小编的数据库学习内容是关于Oracle性能的调整与优化。内容详细，大家千万不要错过咯！Oracle性能调整是一个范围比较广且有点复杂的主题，我们应该如何调整与优化呢？
Oracle的调整方法
对那些已经取得了Oracle 8i性能调整认证考试的人而言，有一个试验区域实验Oracle的调整方法，当Oracle 9i发布时Oracle强调了这个方法有所改动，这个方法从8i到9i/10g发生一些变化，两个方法都不完全一样，它们都有各自的优势和缺点，在Oracle 8i中，由下面的步骤组成：
1、调整业务规则
2、调整数据设计
3、调整应用程序设计
4、调整数据库逻辑结构
5、调整数据库操作
6、调整访问路径
7、调整内存分配
8、调整I/O和物理结构
9、调整资源连接
10、调整基础平台
Oracle 9i的基本原则性方法，原则即优先级顺序：
优先级描述
第一 清楚地定义问题，然后指定一个调整目标
第二 检查主机系统和搜集Oracle统计信息
第三 将识别的问题与Oracle 9i提供的通用数据库性能方法（版本1）/数据库性能计划（版本2）进行对比
第四 使用第二步搜集的统计信息获得一个在系统上可能发生了什么的大概设想
第五 标识所做的改动，然后实施那些改动
第六 确定是否符合第一步中确定的目标，如何符合就停止调整，如果还不符合就重复第五和第六步直到符
合调整目标
Oracle 10g R2性能调整指导认同所有Oracle性能改善方法的处理过程，步骤已经扩展了，但是总体来说还是保留了一些相同的地方。
1、执行下面的初始化标准检查：
a.从用户那里获取真实的反馈，确定性能设计范围和性能目标，以及将来的性能目标，这个过程对未来容量的规划很关键。
b.在性能好和差的时候，都要获取全套操作系统，数据库和从系统获取应用程序统计信息，如果这些不可用，就获取任何有用的信息，丢失统计信息与丢失犯罪证据类似：使得侦探工作更困难并且更浪费时间。
c.对所有涉及用户性能的机器进行操作系统健全检查，通过对操作系统的健全检查，你可以查看被完全利用的硬件和操作系统资源，列出所有过度使用的资源作为后面分析的症状，此外，检查所有硬件错误或进行诊断。
2、检查前十个Oracle常见错误，并确定这些是否可能成为真正的问题，把它们列出来作为后面分析的症状，因为它们代表了大多数可能的问题，ADDM自动检查并报告前十个问题中的九个。查看第6章“自动性能诊断”【http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/diagnsis.htm#g41683】和“Oracle系统中发现的前十个错误”【http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/technique.htm#i11221】
3、建立一个在系统上发生了什么的概念模型，使用症状作为提示理解是什么导致了性能问题，查看“一个性能概念模型的简单决策过程”【http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/technique.htm#i11199】
4、计划一系列补救动作和预先考虑系统的工作情况，然后按顺序应用将使应用程序最大受益，ADDM产生的每个建议都包括了预期的受益分析，不幸的是，系统停机时可能会阻止这样一个严格的调查方法，如果同时应用了多个修改，那么要尝试确保它们是被隔离的，以便可以独立证明每个修改的影响。
5、确认所做的修改是否达到了预期的作用，并了解用户对性能的感受是否有所改善，另外，寻找更多的瓶颈，继续改进概念模型直到你对应用程序的理解更准确。
6、重复后面的三步直到达到性能目标或由于其他约束而变得不可能。
变化是问题的一部分
从一个自顶向下的构造方法到一个以原则为基础的“使它停止伤害”的方法是问题的一部分，搜集统计数据显得很重要因为你如何知道你已经对问题做了改进（或使问题更糟）？对其他方法，你仍然要保留两个最初的问题：我要寻找什么以及我如何使它变得更好？如果结构化方法使你头疼，那原则性方法只会添加混乱。
能够帮助初学者（不承诺：我远远还不够专家的资格）评估（配置、终端和调整）的调整项目清单：
·调整缓冲区高速缓存（Buffer Cache）
·调整重做日志缓冲区（Redo Log Buffer）
·调整共享池内存（Shared Pool Memory）
·优化数据存储器
·优化表空间
·调整Undo段
·检测锁争用
·SQL调整
这些调整措施使得Oracle RDBMS和实例从上到下都更漂亮了，本文剩下的部分将集中在SQL调整或更精密地预防慢速SQL被执行，这些不是同一件事吗？大概是吧，但是在开发方面一个通用的方法是编写执行得够好够块的语句，每条语句不用是最佳的，但是某些思想必须体现在代码中，你没有时间优化成百甚至上千的SQL语句，但同时你可以遵循一些指导方针，以避免常见的错误和不良的编码习惯。
有效避免问题查询的17条提示
这17条提示来源于Hassan Afyouni编写的“Oracle9i性能调整：优化数据库生产率”，这些提示为两个结果打下了坚固的基础：使SQL语句执行效率更佳和确定在这一点上无事可做（如：你已经为SQL语句做了你力所能及的努力，该继续另一个调整项目了）。
这17条提示就是：
1、避免笛卡尔结果
2、避免在大表上全表扫描
3、使用SQL标准和规范减少解析
4、缺少包含在where子句中列的索引
5、避免连接太多的表
6、监视V$session_longops检测长时间运行的操作
7、使用适当的提示
8、使用shared_cursor参数
9、使用基于规则的优化器（如果它比基于成本的优化器要更好的话）
10、避免不必要的排序
11、监视索引变暗（定期删除，必要时重建）
12、小心使用混合索引（不要重复列）
13、监视查询统计情况
14、为表和索引使用不同的表空间（作为一个通用规则，这虽然有点守旧，但主要目的是为了减少I/O争用）
15、在适当的时候使用分区表（和本地索引）（分区是一个额外的成本特性）
16、在where子句中使用直接量（使用绑定变量）
17、保存统计数据时常更新
这是一个相当全面、彻底、准确的列表，步骤9参考了基于规则的优化器的使用，可能引起一个Oracle已经认定作为一个将来的项目特性的依赖或相关性被反对，最后你不得不使用CBO来解决这个问题，因此现在你可能还要开始忘掉CBO，步骤14应该按照减少I/O争用的目标做一些改动而不只停留在它当前描述的为表和索引分配单独的表空间。
Quest Software许多DBA和开发者使用一个名叫Toad的工具，它出自Quest Software公司，按照Quest Software网站上说法，Toad用户社区大约有500，000数量的用户，Toad的一个特色就是它有能力优化SQL查询，换句话说，Oralce公司还没有占领调整顾问类型工具的市场。
关于顾问工具你有多种选择，理解它们是做什么的，如果你没有工作在生产或开发环境中你使用它们做什么？可能仅仅是喜欢，即使你处于一个开发环境，但你可能也没有使用到非常大的数据，本文的焦点集中在如何产生大量的数据，我们将介绍Quest Software的另一个产品：DataFactory for Oracle。
DataFactory
DataFactory的用途是为多种数据库平台快速创建有意义的测试数据，包括Oracle、DB2、Sybase以及ODBC兼容的数据库，正常情况下，每服务器零售价为595美元，在Quest Software的网站有一个可免费使用30天的版本可下载。
要获得这个软件（目前是5.5.0版本），你必须用真实的电子邮件地址注册，hotmail和gmail会被拒绝的，一旦你注册成功了，你将收到一封包含密钥的邮件，这个密钥用于解锁应用程序，开始30天的试用。
安装过程是相当简单的，如果你运行了微软的AntiSpyware，你可能会收到一个或多个错误，禁用掉实时保护后重新安装。
创建辅助对象
了解应用程序较好的方法是使用它的辅助对象，一般的过程有：
1、创建一个项目
2、在方案中创建表
3、运行脚本载入数据
不幸的是，在禁用了系统名参考的约束上获取刷新最好的方法是使用内置的辅助对象，使用一个反复的处理方法，可以禁用掉所有约束直到载入脚本运行不出现错误为止，然而，当Quest运行时修复了这个bug时，我可以标识和禁用这个约束。
启动DataFactory后，你可以选择启动自学教材，关于如何载入自学教材对象的指令（也就是所有的帮助）都在HTML文件中。
帮助系统中的指令规定了使用哪些表，但那是不准确的，下一步我会提到要用到哪些表（总共15个，都是以DF_前缀开头的）。
要创建辅助对象，安装下列步骤做：
1、选择工具（Tools）创建辅助对象（Create TutoRIAl Objects），显示辅助对象创建向导。
2、从选择连接方法的下拉列表中选择合适的c类型。
3、点击下一步（Next）。
4、输入连接参数。
5、点击下一步（Next），显示完成页面。
6、点击完成（Finish），DataFactory辅助表就创建好了。
在创建表之前，你应该在数据库中先创建一个单独的方案对象，使用Oracle 10g，我创建了一个quest用户对象/方案（授予它connect和resource权限即可）。
点击Tools Create Tutorial Objects
辅助设置向导显示它自己的版本和oracle的logo
在完成页显示一列表名
DataFactory告诉你创建成功了
在左边框架中显示了项目文件夹
在主菜单上点击运行按钮（Run），立即报出ORA-02291错误，这是违反了完整性约束的错误提示（某些表上还不止一次），因为在一个外键关联的列上载入的数据没有同时往主表插入对应的数据，几乎所有的约束命名都采取SYS_Cxxxxxx命名结构，意味着它们不是明确的名字。
为了解决完整性约束问题，你可以禁用掉约束（只要你知道要修改哪个表），下面的查询和ALTER TABLE命令显示识别并禁用有问题的约束：
SQL> select owner, constraint_name, table_name, column_name 2 from all_cons_columns 3 where constraint_name like '%9814%'; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ----- ------------------------------ -------------------- ------------ QUEST SYS_C009814 DF_ORDERS CUSTID SQL> alter table df_orders 2 disable constraint sys_c009814; Table altered.结果（Results）窗口显示你的脚本完全成功执行了，意味着你可以看看究竟创建了些什么。 |
不是一次分析一个表，使用内置的DBMS_STATS（它是Oracle推荐的分析工具包），如果你使用的是Oracle 10g，你应该添加一个WHERE dropped='NO'条件阻止在显示查询结果时显示被删掉的表。
SQL> execute dbms_stats.gather_schema_stats('QUEST');
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows
2 from user_tables
3 where dropped='NO';
TABLE_NAME NUM_ROWS
-------------------- ----------
DF_TITLES 100
DF_MOVIE_CUSTOMER 1100
DF_MOVIE_EMPLOYEE 900
DF_DUMMY 1100
DF_AUTHORS_TITLES 1100
DF_MOVIE_RENTAL 700
DF_PRODUCTS 100
DF_MOVIE_TAPE 400
DF_CUSTOMERS 1100
DF_AUTHORS 1100
DF_MOVIE_DISTRICT 1100
DF_ORDERS 101
DF_MOVIE_MOVIE 900
DF_ORDERDETAILS 200
DF_MOVIE_STORE 500
15 rows selected.
|
返回项目或列表，在列表中选择一个表，将会显示它的列和列的数据类型，你可能需要在结果（Results）和子段（Children）之间使用固定按钮功能。
使用DF_MOVIE_CUSTOMER作为一个例子，如何查看它的数据？随机字符串选项刚好准确地用于这里。
辅助表上更多的内容
在外键列上有索引吗？
SQL> select index_name, table_name, column_name, column_position 2 from user_ind_columns; INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION ------------ -------------------- -------------------- --------------- SYS_C009823 DF_MOVIE_DISTRICT DISTRICTID 1 SYS_C009827 DF_MOVIE_STORE STOREID 1 SYS_C009830 DF_MOVIE_EMPLOYEE EMPID 1 SYS_C009837 DF_MOVIE_CUSTOMER CUSTID 1 SYS_C009841 DF_MOVIE_MOVIE MOVIEID 1 SYS_C009845 DF_MOVIE_TAPE TAPEID 1 SYS_C009850 DF_MOVIE_RENTAL TAPEID 1 SYS_C009850 DF_MOVIE_RENTAL CUSTID 2 SYS_C009850 DF_MOVIE_RENTAL RENTDATE 3 SYS_C009810 DF_CUSTOMERS CUSTID 1 SYS_C009813 DF_ORDERS ORDERID 1 SYS_C009816 DF_PRODUCTS PRODUCTID 1 SYS_C009819 DF_ORDERDETAILS ORDERID 1 SYS_C009819 DF_ORDERDETAILS PRODUCTID 2 14 rows selected. |
输出内容意味着什么？你可以立即判断出这样一个事实，不是所有表上都有主键，有15个表，但这里只返回了14行（或如果使用distinct的话，只返回11行），为什么我们会知道这个结果？因为创建主键时会自动创建一个索引，如果你在载入脚本中禁用了所有的引用完整性约束，你还会怀疑什么呢？
Oracle推荐将索引列作为经常访问的关联列，常见的规则是索引列用于where子句，缺少索引，你应该怀疑“create table”部分没有创建外键索引列。
下面的查询显示了带外键的表名/列名：
SQL> select a.constraint_name, b.constraint_type, 2 a.table_name, a.column_name 3 from user_cons_columns a, all_constraints b 4 where a.constraint_name=b.constraint_name 5 and constraint_type = 'R'; CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME ---------------- - -------------------- ------------- SYS_C009831 R DF_MOVIE_EMPLOYEE SUPERVISORID SYS_C009828 R DF_MOVIE_STORE DISTRICTID SYS_C009821 R DF_ORDERDETAILS PRODUCTID SYS_C009820 R DF_ORDERDETAILS ORDERID DFMOVIESTOREFK2 R DF_MOVIE_STORE MANAGERID SYS_C009852 R DF_MOVIE_RENTAL TAPEID SYS_C009851 R DF_MOVIE_RENTAL CUSTID SYS_C009838 R DF_MOVIE_CUSTOMER STOREID SYS_C009814 R DF_ORDERS CUSTID SYS_C009846 R DF_MOVIE_TAPE MOVIEID DFMOVIEEMPFK2 R DF_MOVIE_EMPLOYEE STOREID 11 rows selected. |
这是最终结果吗？怀疑被证实了，外键没有被索引。
从管理和维护角度来看，为什么只有两个约束是明确命名的而剩下的都是系统命名的？实际上是这个方案总共有51个约束，这里只是发生了两个命名的约束。
从这次探索来看，工具如DataFactory或你自己编写的脚本最关键的地方是能够产生百万计的测试样本数据，如果不能保证引用完整性，或在数据建模方面的最佳实践，它有什么好处？如果你打算在应用程序上调试查询，测试数据需要反应应用程序如何使用它。如果你依赖于数据完整性，你的测试数据需要支持并遵从父表/子表关联关系。
从设计的立场来看，有两个最佳实践容易被违背，其一是外键列无索引，其二是3个主要项目（主键、外键和索引）没有明确的名字。第三个可能是在表上无主键，难道在每个表上都需要主键吗？不是！但大多数情况下，每个表都需要主键，即使没有主键，你也要知道为什么没有设置主键，换句话说，不规范的表应该是有意识的决定，而不是失败。
更多详情，可登录课课家官方网了解数据库教程视频，一定会让你有所收获的。
