关于Oracle性能优化的六类诊断方法

数据架构

于 2023-03-10 17:49:02 发布

阅读量1.7k

点赞数 1

分类专栏： DBA 文章标签： oracle 性能优化数据库

本文链接：https://blog.csdn.net/u011868279/article/details/129447363

版权

DBA 专栏收录该内容

183 篇文章

订阅专栏

1. 动态性能视图

动态性能视图（Dynamic Performance Views，简称DPV）是Oracle数据库中的一种特殊的元数据，它们提供了有关数据库内部运行状况的实时信息。这些信息可以用于监视数据库的性能、调整配置参数、诊断故障等方面。

Oracle数据库中有许多不同的DPV，每个DPV都提供了不同的信息。以下是一些常用的DPV：

V$SESSION：显示当前会话的信息，例如会话ID、用户名、客户端IP地址、连接时间等等。
V$SQL：显示当前正在执行的SQL语句的信息，例如SQL_ID、执行次数、执行计划等等。
V$SYSTEM_EVENT：显示系统事件的信息，例如锁等待、IO等待、CPU等待等等。
V$WAITSTAT：显示等待事件的统计信息，例如闩锁等待、日志文件同步等待、数据文件IO等待等等。
V$FILESTAT：显示数据文件的统计信息，例如读取次数、写入次数、平均读取时间、平均写入时间等等。

这些DPV可以通过SQL查询来访问。例如，要查看当前会话的信息，可以运行以下SQL语句：

SELECT * FROM V$SESSION;

要查看当前正在执行的SQL语句的信息，可以运行以下SQL语句：

SELECT * FROM V$SQL;

在使用DPV时需要注意的是，它们提供的信息可能会因Oracle版本和配置而异，因此需要仔细查看Oracle官方文档或相关指南以了解每个DPV的详细信息和用途。同时，使用DPV需要一定的权限，通常只有具有DBA或SYSDBA角色的用户才能访问和使用。

2. 等待事件

等待事件（Wait Event）是Oracle数据库中的一种性能统计信息，它记录了系统等待资源时所遇到的事件和等待的时间。这些等待事件可以是硬件相关的事件，例如IO等待、锁等待等等，也可以是软件相关的事件，例如SQL解析、SQL执行等等。

等待事件对于数据库性能分析和故障排查非常重要，因为它们提供了有关系统瓶颈和性能瓶颈的重要信息。通过分析等待事件，可以找出数据库中哪些操作花费了最多的时间，从而针对性地优化系统。

Oracle数据库中有许多不同的等待事件，每个等待事件都有一个唯一的名称，例如DB FILE SEQUENTIAL READ、LOG FILE SYNC等等。这些等待事件可以通过动态性能视图（Dynamic Performance Views，DPV）来查看，例如V$SYSTEM_EVENT、V$SESSION_EVENT、V$SESSION_WAIT等等。

以下是一些常见的等待事件及其含义：

DB FILE SEQUENTIAL READ：表示在读取数据文件时等待磁盘IO完成，通常是因为数据块在磁盘上不是连续的导致的。
LOG FILE SYNC：表示在等待redo log写入磁盘后的确认信息，通常是因为redo log写入速度过慢或者磁盘IO瓶颈导致的。
ENQUEUE：表示在等待一个共享资源，例如等待获取一个表锁、行级锁等等。
SQL*Net message from client：表示等待客户端发送请求，通常是由于网络问题导致的。
CPU time：表示在等待CPU资源，通常是因为数据库负载过高导致的。

了解这些等待事件，对于数据库性能优化和故障排查非常有帮助。在诊断性能问题时，可以通过查询DPV来查看当前正在等待哪些事件，并根据等待事件来调整系统配置、SQL语句等等，以提高系统性能。

3.statspack

Statspack是一种性能分析工具，是Oracle官方提供的一种轻量级、免费的性能分析工具。它能够收集、存储和分析数据库性能相关的信息，并生成相应的报告，帮助DBA和开发人员分析系统的性能问题和瓶颈，从而进行调整和优化。

Statspack基于系统中的动态性能视图（Dynamic Performance Views）和字典（Dictionary）来收集性能统计信息，包括系统级别和实例级别的统计信息。可以通过在Statspack中配置合适的收集周期和级别，来收集相关的性能统计信息，这些信息包括：

系统资源使用情况，如CPU、内存、磁盘等；
系统负载情况，如并发连接数、SQL执行次数等；
数据库对象的使用情况，如表、索引、存储过程等；
SQL语句的执行情况，如执行次数、响应时间、IO等。

收集到这些性能统计信息后，Statspack会自动将它们存储在Oracle数据库中的指定表中，并提供了一系列查询工具和报告工具来分析这些信息。例如，可以通过Statspack提供的report工具，生成详细的性能报告，包括：

性能摘要：包括系统级别和实例级别的性能摘要信息；
时间轴图表：显示在不同时间段内各种性能指标的变化趋势；
SQL排名：列出执行次数最多、响应时间最长的SQL语句，并提供相应的优化建议；
I/O统计信息：列出执行IO最多的SQL语句，并提供相应的优化建议；
等待事件：列出等待事件排名，并提供相应的优化建议。

使用Statspack可以帮助DBA和开发人员更好地了解系统的性能瓶颈和问题，并提供相应的优化建议，从而提高系统性能和稳定性。

4.ash,awr,addm,addr

ASH（Active Session History）和 AWR（Automatic Workload Repository）是Oracle提供的两种诊断工具，用于监控和分析数据库的性能和负载情况。ADD（Automatic Database Diagnostic Monitor）和 ADDM（Automatic Database Diagnostic Monitor）则是Oracle数据库自动化诊断工具，用于自动诊断数据库性能问题并提供相应的解决方案。

ASH（Active Session History）：它是一种轻量级的性能分析工具，用于收集实时的会话活动信息，并记录在动态性能视图 V$ACTIVE_SESSION_HISTORY 中。ASH 可以帮助 DBA 分析系统中的性能瓶颈和问题，并提供详细的会话信息、等待事件信息和 SQL 语句信息等，从而更好地定位和解决性能问题。

AWR（Automatic Workload Repository）：它是一种集成性能分析工具，用于收集和存储系统级别和实例级别的性能统计信息，并生成相应的报告。AWR 可以自动采集和记录系统性能信息，比如 CPU、内存、IO 等指标，以及 SQL 语句的执行计划、等待事件、 I/O 等。AWR 可以帮助 DBA 分析系统的性能问题，并提供优化建议和方案。

ADDM（Automatic Database Diagnostic Monitor）：它是一个自动化诊断工具，可以分析 AWR 中的性能数据，并自动诊断出数据库中的性能问题和瓶颈。ADDM 可以分析数据库的整体性能，比如 CPU 使用率、等待事件、 I/O 等，并提供相应的优化建议和方案。ADDM 通过自动诊断和优化可以有效地帮助 DBA 解决数据库性能问题。

ADD（Automatic Database Diagnostic Monitor）：它是一个自动化监控工具，用于监控数据库的性能和负载情况，并根据阈值自动触发警报。ADD 可以监控系统的 CPU 使用率、内存使用情况、磁盘空间、网络连接等，同时也可以监控数据库对象的状态和性能指标。ADD 可以帮助 DBA 及时发现系统的问题，并及时采取措施以避免系统崩溃。

5.sql turing advisor/sql access advisor

SQL Tuning Advisor 和 SQL Access Advisor 是 Oracle 数据库提供的两个诊断工具，用于优化 SQL 查询的性能和访问方式。

SQL Tuning Advisor：它是一个自动化的 SQL 优化工具，可以分析 SQL 查询的执行计划和统计信息，并给出相应的优化建议和方案。SQL Tuning Advisor 可以分析 SQL 语句的执行计划、索引使用情况、查询统计信息等，并提供优化建议，比如修改 SQL 语句、创建或删除索引、更改参数设置等，以提高查询的性能。

SQL Access Advisor：它是一个自动化的 SQL 访问优化工具，可以分析 SQL 查询的访问方式和访问路径，并给出相应的优化建议和方案。SQL Access Advisor 可以分析 SQL 语句的访问方式，比如全表扫描、索引扫描、聚簇索引扫描等，并提供相应的优化建议，比如创建或删除索引、更改查询语句等，以提高查询的性能和效率。

这两个工具可以通过 Oracle SQL Developer 或 SQL*Plus 等客户端工具来调用和使用。在使用这些工具时，需要提供相应的 SQL 查询语句或 SQL 语句文件，并根据工具提示进行相应的操作和参数设置。最终工具会生成相应的优化建议和方案，需要根据具体情况进行评估和实施。

6.Hints

Hints（提示）是 Oracle 数据库提供的一种机制，可以在 SQL 查询语句中指定一些特定的优化策略和执行计划。Hints 可以被认为是一种“透明”优化策略，即它们不会修改 SQL 查询语句本身，而只会影响 Oracle 数据库的优化器来生成一个不同的执行计划。

Hints 可以在 SQL 查询语句中通过注释的方式指定，格式为 /*+ hint_name(hint_value) */。其中，hint_name 为具体的提示名称，而 hint_value 则为相应的提示值，可以是一个数字、一个字符串、一个子查询等等。

常见的一些 Hints 包括：

/*+ ALL_ROWS */：指示优化器为查询语句生成最优的执行计划，以最小化整个查询的总成本。

/*+ INDEX(table index) */：指示优化器使用指定的索引来执行查询，而不是使用其他的索引或全表扫描等方式。

/*+ FIRST_ROWS(n) */：指示优化器为查询语句生成最快的 n 行数据，以最小化响应时间。

/*+ ORDERED */：指示优化器按照 SQL 查询语句中的表关联顺序来执行查询，而不是按照自己的计划来执行。

/*+ NO_CACHE */：指示优化器不要使用缓存中的数据，而是直接从磁盘中读取数据，以避免脏数据的影响。

在使用 Hints 时，需要注意以下几点：

Hints 可能会影响 SQL 查询语句的可读性和维护性，因此应该尽量避免在 SQL 查询语句中滥用 Hints。

Hints 可能会影响 SQL 查询语句的性能和稳定性，因此需要经过充分的测试和评估才能决定是否使用。

Hints 的效果可能会随着 Oracle 数据库版本的更新而发生变化，因此需要在新版本上重新测试和评估 Hints 的效果。