小白也能学会的Oracle优化教程-主打零基础

徐sir（徐慧阳）

已于 2024-02-10 08:20:03 修改

阅读量969

点赞数 15

文章标签： oracle 数据库运维 linux

于 2024-02-09 13:00:52 首次发布

本文链接：https://blog.csdn.net/xxddxhyz/article/details/136082050

版权

没错，这一篇不是标题党，真的是小白也能学会的Oracle 优化教程，学会了能解决很大一部分优化问题！！

1、这篇文章适用于哪些人

尤其适用于我这样的数据库小白、被赶鸭子上架来做优化的系统工程师、不懂SQL优化的部分开发人员。

即使对于专业的DBA来说，优化SQL是个系统、复杂的工程，这个过程涉及到系统参数调整、数据库相关参数调整、数据存储优化、业务逻辑正确性、SQL写法，有的时候DBA还要参与到整个业务系统的流程设计等等。。。。以确保数据库能最合理、快速的运行（当然专业DBA有专业的EMCC或其它监控工具，这些都要花钱，我没有~）。

看这篇文章你不需要会复杂的SQL写法、不需要懂业务逻辑，只需要工具鼠标点点、配合简单的基础知识，就能够优化大多数Oracle常见性能问题。

做为菜鸡的我优化只会3板斧：改参数、建索引、收集统计信息，这个工具都可以极大简化我的工作。我通过这个工具己经给很多客户解决过因为统计信息不准确、索引创建不正确导致的性能问题。SQL索引这类问题最为典型，好多生产都会因为开发写出烂SQL+瞎创建索引导致有这样的问题。

下面开始我们的文章。

2、获取需要用到的工具

这里就要说到一个Oracle的官方工具大杀器：Oracle SQL Developer

官网的介绍如下：

Oracle SQL Developer 是一个免费的集成开发环境，可在传统部署和云端部署中简化 Oracle Database 的开发和管理。SQL Developer 提供 PL/SQL 应用的完整端到端开发、用于运行查询和脚本的工作表、用于管理数据库的 DBA 控制台、报告接口、完整的数据建模解决方案，以及用于将第三方数据库迁移到 Oracle 的迁移平台。

是不是很强大！而且还免费，你要用plsql/toad也可以，但是要花钱（盗版不提倡🤫）。很多DBA的工作都可以通过这个工具来完成，甚至是数据迁移，数据一致性比对。我之前写过一篇通过SQLDEVELOPER导出LOB文件就是使用这个工具完成：《记一次ORACLE中BLOB文件直接导出的方法测试》。

这个工具在Oracle 11g以后的版本，默认都存在Oracle_home目录下，但是默认携带的版本太低，有很多功能都没有。

打开官方链接：SQL Developer | Oracle 中国

点击“下载Sql Developer”

如果你是和我一样使用Windows的小白用户，请按下图选择，下载 windows 64-bit with jdk 11 included，工具是基于JAVA开发的，唯一的缺点就是JAVA的通病，对CPU要求有些高，你电脑CPU要是太拉跨，运行起来可能会卡，有的时候可能会程序无响应。

这里面包含了要运行sqldeveloper的JAVA环境，不再需要你额外配置。解压即用。

同时还有官方文档的连接，想要详细学习这个工具用途，可以点击下图中的documentation进行查看

这里需要你注册一个oracle的账号，然后同意许可就能下载了。

3、创建数据库连接

下载下来的压缩包，直接右键解压到C盘根目录（你可以根据自己的情况选择安装位置）

打开解压目录，运行sqldeveloper.exe开始

如果你安装过历史版本，运行新版本的时候会自动提示你导入之前版本的用户数据。避免了升级安装的麻烦。

登录到界面后，如下图所示，

点击+号，选择“新建数据库连接”

添加对应的数据库信息，保存

下一步在导航栏，点击“查看”

选择"DBA"

点击加号，选择刚才保存的数据库连接

这里就是DBA的一个小型工具集，里面可以监控数据库状态、实时SQL进行情况监控、表空间管理、查看AWR等等一系列操作。

准备工作完毕，接下来说说小白都会的数据库优化。

4、使用工具自动生成优化SQL建议

进入正题。主要是通过这个DBA工具中的“实时SQL监视”，找到慢SQL，然后通过内置的优化建议生成工具，生成优化建议，按建议进行配置，完成优化。

这里会弹出如下的提示，点击“是”（里面的内容你可以仔细阅读，是否符合你的生产环境）

在这个界面里会列出执行时间较长、IO较高的SQL，当你的数据库出现性能问题时，你后这里会出现很多。找到执行最多、时间最长的SQL进行优化。

比如下图这个，一看执行了19S就很有问题，点击改行，会自动显示执行计划，整个SQL的执行计划一下就可以看到走了全表扫描，一看就是没走索引导致的。

我们右键这个SQL，选择Xplan

好家伙，这条SQL还没有写绑定变量，直接就写值了！！！

接下来我们复制这段SQL，然后把它粘贴到SQL工作表

右键选择刚才创建的连接，选择“打开SQL工作表”

粘贴SQL，然后选中SQL，右键，选择"SQL优化指导“

之后他就会自动运行，分析SQL

主要是通过以下几个方面进行优化指导：

统计信息、SQL概要文件、索引、重新构建SQL建议

这个时间根据SQL的复杂程度，可能会运行比较长的时间，一般建议是在业务不繁忙的时候进行！！！

好的，经过40秒左右，我们的优化任务完成了。看提示，它己经发现了索引的问题。

我们点击上图的详细信息，就能看到他的详细优化建议，这里给了2条，我列了出来，详细的我贴在最后，有兴趣的可以翻看：

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。

  Recommendation (estimated benefit: 99.12%)
  ------------------------------------------
  - 考虑接受建议的 SQL 概要文件, 以便对此语句使用并行执行。
    execute dbms_sqltune.accept_sql_profile(task_name => 'staName61121',
            task_owner => 'SYS', replace => TRUE, profile_type =>
            DBMS_SQLTUNE.PX_PROFILE);
 与 DOP 128 并行执行此查询会使原始计划上的响应时间缩短 99.13%。但是, 启用并行执行时要付出一些代价。它将增加语句的资源消耗 (预计为
  11.75%), 这会导致系统吞吐量降低。此外, 由于在非常短的持续时间内消耗了这些资源, 因此如果没有足够可用的硬件容量,
  并发语句的响应时间将受到负面影响。
2- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。

  Recommendation (estimated benefit: 62.36%)
  ------------------------------------------
  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index OUTPADM.IDX$$_361660001 on OUTPADM.CLINIC_MASTER_DAY(TO_CHAR("VISIT_DATE",'yyyymmdd')||'_'||"CLINIC_LABEL"||'_'||"VISIT_TIME_DESC");

  Rationale
  ---------
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

我的建议是忽略他给他的部分建议，如：创建SQL概要文件、并行，通常给的这两个建议都是加上会更慢，重点看有没有让你收集统计信息、索引该如何创建。

创建1个好的索引（在不能更改业务SQL的情况下），基本能解决很大的问题，我以前也把这个方法告诉一些开发人员，让他们来适当的优化一下有问题的SQL。

生成的建议就有创建语句，但是没有online，生产上要建索引还是加上online，不然锁表可要了命了！

5、一些其他的辅助用法

1、导出SQL报告

如果你觉得他给的建议不靠谱，还想找高人指点一下，也可以把这个SQL导出为报告

选中这个SQL，点击上方的保存按钮，save as active report

会保存为1个HTML，这个HTML需要联网调用一些ORACLE的组件才能打开，打开后是这个样子

2、实时查看数据库的情况

对于没有别的专业监控数据库的情况下，可以使用”数据库状态“--”实例查看器“来进行简单的监控，这个有点类似于EM管理器的部分功能。

除了上面的功能，还有很多的常用功能。大家感兴趣可以看B站上的”翰高软件“学院出过几期讲解视频

Sqldeveloper高效使用第一节_哔哩哔哩_bilibili

Sqldeveloper高效使用第二讲_哔哩哔哩_bilibili

Sqldeveloper高效使用第三节_哔哩哔哩_bilibili

Sqldeveloper高效使用第四节_哔哩哔哩_bilibili

附件：优化指导详细信息

重要的地方我就遮挡一下

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : staName61121
Tuning Task Owner  : SYS
Tuning Task ID     : 221542
Workload Type      : Single SQL Statement
Execution Count    : 1
Current Execution  : EXEC_223779
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 02/08/2024 18:32:12
Completed at       : 02/08/2024 18:32:51

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : fpmu1s95v08c8
SQL Text   : SELECT
                 a.ghhx,
                 a.hxzt,
                 a.kssj,
                 a.jssj,
                 a.hxsjd,
                 a.hxlx,
                 a.memo
             FROM
                 nljk.getyykshx a
             WHERE
                 a.pbxh = '20240209_XXX文本'

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。

  Recommendation (estimated benefit: 99.12%)
  ------------------------------------------
  - 考虑接受建议的 SQL 概要文件, 以便对此语句使用并行执行。
    execute dbms_sqltune.accept_sql_profile(task_name => 'staName61121',
            task_owner => 'SYS', replace => TRUE, profile_type =>
            DBMS_SQLTUNE.PX_PROFILE);

  与 DOP 128 并行执行此查询会使原始计划上的响应时间缩短 99.13%。但是, 启用并行执行时要付出一些代价。它将增加语句的资源消耗 (预计为
  11.75%), 这会导致系统吞吐量降低。此外, 由于在非常短的持续时间内消耗了这些资源, 因此如果没有足够可用的硬件容量,
  并发语句的响应时间将受到负面影响。

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

                                 Past week sampled statistics for this SQL
                                 -----------------------------------------
  Number of executions                                                   0 
  Percent of total activity                                              0 
  Percent of samples with #Active Sessions > 2*CPU                       0 
  Weekly DB time (in sec)                                                0 

                              Projected statistics with Parallel Execution
                              --------------------------------------------
  Weekly DB time (in sec)                                                0 

2- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。

  Recommendation (estimated benefit: 62.36%)
  ------------------------------------------
  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index OUTPADM.IDX$$_361660001 on
    OUTPADM.CLINIC_MASTER_DAY(TO_CHAR("VISIT_DATE",'yyyymmdd')||'_'||"CLINIC_LA
    BEL"||'_'||"VISIT_TIME_DESC");

  Rationale
  ---------
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2395942895

 
----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |   210K|    11M| 32512   (5)| 00:06:31 |
|*  1 |  HASH JOIN         |                   |   210K|    11M| 32512   (5)| 00:06:31 |
|   2 |   TABLE ACCESS FULL| FSD_TIMES_DETIAL  |    90 |  2340 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| CLINIC_MASTER_DAY |   222K|  6966K| 32508   (5)| 00:06:31 |
----------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / B@SEL$2
   3 - SEL$F5BB74E1 / A@SEL$2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."TIME_DESC_NO"="B"."SERIAL_NO" AND 
              "A"."VISIT_TIME_DESC"="B"."TIME_INTERVAL_NAME")
   3 - filter(TO_CHAR(INTERNAL_FUNCTION("A"."VISIT_DATE"),'yyyymmdd')||'_'||"A".
              "CLINIC_LABEL"||'_'||"A"."VISIT_TIME_DESC"='20240209_XXX文本')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=2) "B"."BEGIN_TIME"[VARCHAR2,8], "B"."END_TIME"[VARCHAR2,8], 
       "VISIT_NO"[NUMBER,22], "A"."SERIAL_NO"[NUMBER,22]
   2 - "B"."TIME_INTERVAL_NAME"[VARCHAR2,8], "B"."SERIAL_NO"[NUMBER,22], 
       "B"."BEGIN_TIME"[VARCHAR2,8], "B"."END_TIME"[VARCHAR2,8]
   3 - "VISIT_NO"[NUMBER,22], "A"."VISIT_TIME_DESC"[VARCHAR2,8], 
       "A"."SERIAL_NO"[NUMBER,22], "A"."TIME_DESC_NO"[NUMBER,22]

2- Using New Indices
--------------------
Plan hash value: 3076509382

 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   | 27424 |  1553K| 12237   (1)| 00:02:27 |
|*  1 |  HASH JOIN                   |                   | 27424 |  1553K| 12237   (1)| 00:02:27 |
|   2 |   TABLE ACCESS FULL          | FSD_TIMES_DETIAL  |    90 |  2340 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| CLINIC_MASTER_DAY | 29101 |   909K| 12234   (1)| 00:02:27 |
|*  4 |    INDEX RANGE SCAN          | IDX$$_361660001   | 29101 |       |   138   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / B@SEL$2
   3 - SEL$F5BB74E1 / A@SEL$2
   4 - SEL$F5BB74E1 / A@SEL$2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."TIME_DESC_NO"="B"."SERIAL_NO" AND 
              "A"."VISIT_TIME_DESC"="B"."TIME_INTERVAL_NAME")
   4 - access("CLINIC_MASTER_DAY"."qsmmix_VCol_5001"='20240209_XXX文本')
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=2) "B"."BEGIN_TIME"[VARCHAR2,8], "B"."END_TIME"[VARCHAR2,8], 
       "VISIT_NO"[NUMBER,22], "A"."SERIAL_NO"[NUMBER,22]
   2 - "B"."TIME_INTERVAL_NAME"[VARCHAR2,8], "B"."SERIAL_NO"[NUMBER,22], 
       "B"."BEGIN_TIME"[VARCHAR2,8], "B"."END_TIME"[VARCHAR2,8]
   3 - "VISIT_NO"[NUMBER,22], "A"."VISIT_TIME_DESC"[VARCHAR2,8], 
       "A"."SERIAL_NO"[NUMBER,22], "A"."TIME_DESC_NO"[NUMBER,22]
   4 - "A".ROWID[ROWID,10]

3- Using Parallel Execution
---------------------------
Plan hash value: 261992528

 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |  2212 |   125K|   284   (4)| 00:00:04 |        |      |            |
|   1 |  PX COORDINATOR          |                   |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002          |  2212 |   125K|   284   (4)| 00:00:04 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED    |                   |  2212 |   125K|   284   (4)| 00:00:04 |  Q1,02 | PCWP |            |
|   4 |     JOIN FILTER CREATE   | :BF0000           |    90 |  2340 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX RECEIVE          |                   |    90 |  2340 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000          |    90 |  2340 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |                   |    90 |  2340 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| FSD_TIMES_DETIAL  |    90 |  2340 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   9 |     PX RECEIVE           |                   |  2347 | 75104 |   282   (4)| 00:00:04 |  Q1,02 | PCWP |            |
|  10 |      PX SEND HASH        | :TQ10001          |  2347 | 75104 |   282   (4)| 00:00:04 |  Q1,01 | P->P | HASH       |
|  11 |       JOIN FILTER USE    | :BF0000           |  2347 | 75104 |   282   (4)| 00:00:04 |  Q1,01 | PCWP |            |
|  12 |        PX BLOCK ITERATOR |                   |  2347 | 75104 |   282   (4)| 00:00:04 |  Q1,01 | PCWC |            |
|* 13 |         TABLE ACCESS FULL| CLINIC_MASTER_DAY |  2347 | 75104 |   282   (4)| 00:00:04 |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   8 - SEL$F5BB74E1 / B@SEL$2
  13 - SEL$F5BB74E1 / A@SEL$2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."TIME_DESC_NO"="B"."SERIAL_NO" AND "A"."VISIT_TIME_DESC"="B"."TIME_INTERVAL_NAME")
  13 - filter(TO_CHAR(INTERNAL_FUNCTION("A"."VISIT_DATE"),'yyyymmdd')||'_'||"A"."CLINIC_LABEL"||'_'||"A"."VISIT_TIM
              E_DESC"='20240209_XXX文本' AND SYS_OP_BLOOM_FILTER(:BF0000,"A"."TIME_DESC_NO","A"."VISIT_TIME_DESC"))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "B"."BEGIN_TIME"[VARCHAR2,8], "B"."END_TIME"[VARCHAR2,8], "VISIT_NO"[NUMBER,22], "A"."SERIAL_NO"[NUMBER,22]
   2 - (#keys=0) "B"."BEGIN_TIME"[VARCHAR2,8], "B"."END_TIME"[VARCHAR2,8], "VISIT_NO"[NUMBER,22], 
       "A"."SERIAL_NO"[NUMBER,22]
   3 - (#keys=2) "B"."BEGIN_TIME"[VARCHAR2,8], "B"."END_TIME"[VARCHAR2,8], "VISIT_NO"[NUMBER,22], 
       "A"."SERIAL_NO"[NUMBER,22]
   4 - "B"."SERIAL_NO"[NUMBER,22], "B"."TIME_INTERVAL_NAME"[VARCHAR2,8], "B"."BEGIN_TIME"[VARCHAR2,8], 
       "B"."END_TIME"[VARCHAR2,8]
   5 - "B"."SERIAL_NO"[NUMBER,22], "B"."TIME_INTERVAL_NAME"[VARCHAR2,8], "B"."BEGIN_TIME"[VARCHAR2,8], 
       "B"."END_TIME"[VARCHAR2,8]
   6 - (#keys=2) "B"."SERIAL_NO"[NUMBER,22], "B"."TIME_INTERVAL_NAME"[VARCHAR2,8], "B"."BEGIN_TIME"[VARCHAR2,8], 
       "B"."END_TIME"[VARCHAR2,8]
   7 - "B"."TIME_INTERVAL_NAME"[VARCHAR2,8], "B"."SERIAL_NO"[NUMBER,22], "B"."BEGIN_TIME"[VARCHAR2,8], 
       "B"."END_TIME"[VARCHAR2,8]
   8 - "B"."TIME_INTERVAL_NAME"[VARCHAR2,8], "B"."SERIAL_NO"[NUMBER,22], "B"."BEGIN_TIME"[VARCHAR2,8], 
       "B"."END_TIME"[VARCHAR2,8]
   9 - "A"."TIME_DESC_NO"[NUMBER,22], "A"."VISIT_TIME_DESC"[VARCHAR2,8], "VISIT_NO"[NUMBER,22], 
       "A"."SERIAL_NO"[NUMBER,22]
  10 - (#keys=2) "A"."TIME_DESC_NO"[NUMBER,22], "A"."VISIT_TIME_DESC"[VARCHAR2,8], "VISIT_NO"[NUMBER,22], 
       "A"."SERIAL_NO"[NUMBER,22]
  11 - "VISIT_NO"[NUMBER,22], "A"."VISIT_TIME_DESC"[VARCHAR2,8], "A"."SERIAL_NO"[NUMBER,22], 
       "A"."TIME_DESC_NO"[NUMBER,22]
  12 - "VISIT_NO"[NUMBER,22], "A"."VISIT_TIME_DESC"[VARCHAR2,8], "A"."SERIAL_NO"[NUMBER,22], 
       "A"."TIME_DESC_NO"[NUMBER,22]
  13 - "VISIT_NO"[NUMBER,22], "A"."VISIT_TIME_DESC"[VARCHAR2,8], "A"."SERIAL_NO"[NUMBER,22], 
       "A"."TIME_DESC_NO"[NUMBER,22]
 
Note
-----
   - dynamic sampling used for this statement (level=4)
   - automatic DOP: skipped because of IO calibrate statistics are missing

-------------------------------------------------------------------------------

也欢迎关注我的公众号【徐sir的IT之路】，一起学习！

————————————————————————————
公众号：徐sir的IT之路
CSDN ：徐sir（徐慧阳）-CSDN博客
墨天轮：徐sir的个人主页 - 墨天轮
PGFANS：PGFans问答社区:全球唯一的PostgreSQL中文技术交流社区

————————————————————————————