暂无图片
同一SQL语句，某日执行耗时43000秒。终止后，再次执行120秒完成。
我来答
返回问答列表
分享
yclegend
2022-05-12
同一SQL语句，某日执行耗时43000秒。终止后，再次执行120秒完成。
暂无图片 10M
sql

同一SQL语句，某日执行耗时43000秒。终止后，再次执行120秒完成。

sql语句中无绑定变量，执行计划中全为HASH JOIN全表扫描。该现象3年中只出现一次，请帮忙分析一下可能性。

我来答
添加附件
收藏
分享
问题补充
5条回答
默认
最新
薛晓刚

可能是由于之前有其他锁的因素导致，比如其他事务未提交，或者数据库在进行备份等因素导致未能按照真实情况执行。

暂无图片 评论
暂无图片 有用 0
暂无图片
yclegend
升级问题到: 潜在风险
暂无图片 评论
暂无图片 有用 0
yclegend
| Id  | Operation                                     | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                             |       |       |       |   162K(100)|          |
|   1 |  VIEW                                         |                             | 34084 |  2463K|       |    33   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                           | SYS_TEMP_0FD9FD07B_1BF164B3 | 34084 |   832K|       |    33   (0)| 00:00:01 |
|   3 |   VIEW                                        |                             | 34084 |  2463K|       |    33   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL                          | SYS_TEMP_0FD9FD07B_1BF164B3 | 34084 |   832K|       |    33   (0)| 00:00:01 |
|   5 |    VIEW                                       |                             | 34084 |  2463K|       |    33   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL                         | SYS_TEMP_0FD9FD07B_1BF164B3 | 34084 |   832K|       |    33   (0)| 00:00:01 |
|   7 |  TABLE ACCESS FULL                            | CBBBBCONVERSION            |     1 |    14 |       |     7   (0)| 00:00:01 |
|   8 |  TEMP TABLE TRANSFORMATION                    |                             |       |       |       |            |          |
|   9 |   LOAD AS SELECT                              |                             |       |       |       |            |          |
|  10 |    HASH UNIQUE                                |                             | 34084 |   832K|  1216K|  2161   (1)| 00:00:26 |
|  11 |     VIEW                                      | BBBBSPECIALCONDITIONS      | 34084 |   832K|       |  1912   (1)| 00:00:23 |
|  12 |      TABLE ACCESS FULL                        | BBBBSPECIALCONDITIONS      | 34084 |   832K|       |  1912   (1)| 00:00:23 |
|  13 |   LOAD AS SELECT                              |                             |       |       |       |            |          |
|  14 |    VIEW                                       |                             |   160K|  5008K|       | 34122   (1)| 00:06:50 |
|  15 |     HASH JOIN                                 |                             |   160K|  7043K|  6888K| 34122   (1)| 00:06:50 |
|  16 |      VIEW                                     | WINDCUSTOMCODE              |   160K|  5008K|       | 26573   (1)| 00:05:19 |
|  17 |       TABLE ACCESS FULL                       | WINDCUSTOMCODE              |   160K|  5008K|       | 26573   (1)| 00:05:19 |
|  18 |      VIEW                                     | BBBBDESCRIPTION            |   380K|  4826K|       |  6763   (1)| 00:01:22 |
|  19 |       TABLE ACCESS FULL                       | BBBBDESCRIPTION            |   380K|  4826K|       |  6763   (1)| 00:01:22 |
|  20 |   LOAD AS SELECT                              |                             |       |       |       |            |          |
|  21 |    HASH GROUP BY                              |                             |  1230 |   145K|       |  2819   (1)| 00:00:34 |
|  22 |     VIEW                                      |                             |   109K|    12M|       |  2815   (1)| 00:00:34 |
|  23 |      HASH JOIN RIGHT OUTER                    |                             |   109K|    15M|    10M|  2815   (1)| 00:00:34 |
|  24 |       VIEW                                    |                             |   160K|  8922K|       |   191   (1)| 00:00:03 |
|  25 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9FD07C_1BF164B3 |   160K|  5008K|       |   191   (1)| 00:00:03 |
|  26 |       VIEW                                    |                             |   109K|     9M|       |  1543   (1)| 00:00:19 |
|  27 |        HASH JOIN OUTER                        |                             |   109K|    11M|  6760K|  1543   (1)| 00:00:19 |
|  28 |         VIEW                                  |                             |   109K|  5465K|       |   500   (1)| 00:00:06 |
|  29 |          HASH JOIN                            |                             |   109K|  6859K|       |   500   (1)| 00:00:06 |
|  30 |           VIEW                                | RALATEDSECURITIESCODE       | 26791 |   758K|       |   308   (1)| 00:00:04 |
|  31 |            TABLE ACCESS FULL                  | RALATEDSECURITIESCODE       | 26791 |   837K|       |   308   (1)| 00:00:04 |
|  32 |           VIEW                                |                             |   160K|  5478K|       |   191   (1)| 00:00:03 |
|  33 |            TABLE ACCESS FULL                  | SYS_TEMP_0FD9FD07C_1BF164B3 |   160K|  5008K|       |   191   (1)| 00:00:03 |
|  34 |         VIEW                                  |                             |   160K|  8922K|       |   191   (1)| 00:00:03 |
|  35 |          TABLE ACCESS FULL                    | SYS_TEMP_0FD9FD07C_1BF164B3 |   160K|  5008K|       |   191   (1)| 00:00:03 |
|  36 |   HASH JOIN RIGHT OUTER                       |                             |   334K|   209M|       |   122K  (1)| 00:24:36 |
|  37 |    TABLE ACCESS FULL                          | BBBBFLOATINGRATE           |  6041 |   147K|       |   238   (0)| 00:00:03 |
|  38 |    HASH JOIN RIGHT OUTER                      |                             |   334K|   202M|       |   122K  (1)| 00:24:33 |
|  39 |     VIEW                                      |                             |  3690 |   317K|       |    21   (0)| 00:00:01 |
|  40 |      UNION-ALL                                |                             |       |       |       |            |          |
|  41 |       VIEW                                    |                             |  1230 |   105K|       |     7   (0)| 00:00:01 |
|  42 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9FD07D_1BF164B3 |  1230 |   145K|       |     7   (0)| 00:00:01 |
|  43 |       VIEW                                    |                             |  1230 |   105K|       |     7   (0)| 00:00:01 |
|  44 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9FD07D_1BF164B3 |  1230 |   145K|       |     7   (0)| 00:00:01 |
|  45 |       VIEW                                    |                             |  1230 |   105K|       |     7   (0)| 00:00:01 |
|  46 |        TABLE ACCESS FULL                      | SYS_TEMP_0FD9FD07D_1BF164B3 |  1230 |   145K|       |     7   (0)| 00:00:01 |
|  47 |     HASH JOIN RIGHT OUTER                     |                             |   334K|   173M|    13M|   122K  (1)| 00:24:33 |
|  48 |      TABLE ACCESS FULL                        | BBBBINDUSTRYWIND           |   356K|  9753K|       |  1300   (1)| 00:00:16 |
|  49 |      HASH JOIN RIGHT OUTER                    |                             |   334K|   165M|  9992K|   112K  (1)| 00:22:29 |
|  50 |       VIEW                                    |                             |   341K|  5994K|       | 10004   (1)| 00:02:01 |
|  51 |        HASH GROUP BY                          |                             |   341K|  6993K|    39M| 10004   (1)| 00:02:01 |
|  52 |         TABLE ACCESS FULL                     | BBBBCF                     |  1282K|    25M|       |  5651   (1)| 00:01:08 |
|  53 |       HASH JOIN RIGHT OUTER                   |                             |   334K|   159M|  2208K| 93778   (1)| 00:18:46 |
|  54 |        VIEW                                   |                             | 17926 |  1995K|       | 17884   (1)| 00:03:35 |
|  55 |         HASH JOIN SEMI                        |                             | 17926 |   927K|       | 17884   (1)| 00:03:35 |
|  56 |          TABLE ACCESS FULL                    | BBBBAGENCY                 | 17926 |   700K|       | 11135   (1)| 00:02:14 |
|  57 |          TABLE ACCESS FULL                    | BBBBDESCRIPTION            |   380K|  4826K|       |  6748   (1)| 00:01:21 |
|  58 |        HASH JOIN RIGHT OUTER                  |                             |   334K|   122M|       | 69499   (1)| 00:13:54 |
|  59 |         VIEW                                  |                             |  2770 | 83100 |       | 17333   (1)| 00:03:28 |
|  60 |          HASH GROUP BY                        |                             |  2770 | 49860 |       | 17333   (1)| 00:03:28 |
|  61 |           VIEW                                |                             |  2770 | 49860 |       | 17333   (1)| 00:03:28 |
|  62 |            WINDOW SORT                        |                             |  2770 |   162K|       | 17333   (1)| 00:03:28 |
|  63 |             HASH JOIN                         |                             |  2770 |   162K|       | 17332   (1)| 00:03:28 |
|  64 |              VIEW                             |                             |  2770 |   102K|       | 10580   (1)| 00:02:07 |
|  65 |               CONNECT BY WITHOUT FILTERING    |                             |       |       |       |            |          |
|  66 |                VIEW                           |                             |  2770 |  5448K|       | 10580   (1)| 00:02:07 |
|  67 |                 UNION-ALL                     |                             |       |       |       |            |          |
|  68 |                  FILTER                       |                             |       |       |       |            |          |
|  69 |                   TABLE ACCESS FULL           | BBBBSPECIALCONDITIONS      |  5304 |   155K|       |  1913   (1)| 00:00:23 |
|  70 |                   TABLE ACCESS FULL           | BBBBSPECIALCONDITIONS      |     1 |    25 |       |  1912   (1)| 00:00:23 |
|  71 |                  SORT GROUP BY                |                             |  1385 | 84485 |       |  8667   (1)| 00:01:45 |
|  72 |                   CONNECT BY WITHOUT FILTERING|                             |       |       |       |            |          |
|  73 |                    FILTER                     |                             |       |       |       |            |          |
|  74 |                     HASH JOIN                 |                             |  5304 |   315K|       |  8666   (1)| 00:01:44 |
|  75 |                      TABLE ACCESS FULL        | BBBBSPECIALCONDITIONS      |  5304 |   155K|       |  1913   (1)| 00:00:23 |
|  76 |                      TABLE ACCESS FULL        | BBBBDESCRIPTION            |   380K|    11M|       |  6751   (1)| 00:01:22 |
|  77 |                     TABLE ACCESS FULL         | BBBBSPECIALCONDITIONS      |     1 |    25 |       |  1912   (1)| 00:00:23 |
|  78 |              TABLE ACCESS FULL                | BBBBDESCRIPTION            |   380K|  8168K|       |  6751   (1)| 00:01:22 |
|  79 |         HASH JOIN RIGHT OUTER                 |                             |   334K|   113M|       | 52165   (1)| 00:10:26 |
|  80 |          TABLE ACCESS FULL                    | BBBBINTRODUCTION           | 19262 |   206K|       |   546   (1)| 00:00:07 |
|  81 |          HASH JOIN RIGHT OUTER                |                             |   334K|   109M|  9248K| 51619   (1)| 00:10:20 |
|  82 |           TABLE ACCESS FULL                   | BBBBISSUER                 |   143K|  7564K|       |  3848   (1)| 00:00:47 |
|  83 |           HASH JOIN                           |                             |   334K|    92M|    78M| 42534   (1)| 00:08:31 |
|  84 |            TABLE ACCESS FULL                  | BBBBDESCRIPTION            |   334K|    74M|       |  6762   (1)| 00:01:22 |
|  85 |            TABLE ACCESS FULL                  | WINDCUSTOMCODE              |  1645K|    87M|       | 26569   (1)| 00:05:19 |
暂无图片 评论
暂无图片 有用 0
yclegend

数据库环境：11.2.0.4 RAC

暂无图片 评论
暂无图片 有用 0
Uncopyrightable

在awr报告中找到相应的sql_id，然后用awrsqrpt.sql生成下该sql的计划任务看下资源消耗情况；上面有很多table access full的表，得需要根据表大小和where条件进一步调整，降低全表扫描的情况；

暂无图片 评论
暂无图片 有用 0
回答交流
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
暂无图片墨值悬赏