Oracle 11g dba_free_space视图查询慢解决方案——hint调整执行计划

最新推荐文章于 2023-03-16 11:04:36 发布

清秋_lwl

最新推荐文章于 2023-03-16 11:04:36 发布

阅读量1.8k

点赞数

分类专栏： SQL优化 Oracle数据库文章标签： oracle sql

本文链接：https://blog.csdn.net/weixin_41429228/article/details/114228054

版权

ora11g中查询表空间的使用情况时需要通过dba_free_space视图来查看表空间的剩余量，但有时可能由于回收站的对象较多等原因，导致该视图查询速度较慢，本文将从其执行计划的角度分析其原因并提出一种相对实用且无风险的解决方式。

摘要由CSDN通过智能技术生成

现象描述：

在Oracle 11g中执行如下SQL进行表空间使用情况的日常基线采集时发现，部分数据库会采集不到数据，经过程序方面的排查发现，由于该SQL的执行时间过长（15秒左右），超出了程序设定的响应阈值，故而导致数据采集不到的现象。

SELECT t.tablespace_name AS "tablespace",
       substr(t.contents, 1, 4) AS "type",
       trunc ((t.tbs_size - nvl (t.free_space, 0)) / 1024 / 1024) AS "used_mb",
       trunc (t.tbs_size / 1024 / 1024) AS "total_mb",
       trunc (nvl (t.free_space, 0) / 1024 / 1024) AS "free_mb",
       trunc ((t.tbs_size - nvl (t.free_space, 0)) * 100 / t.tbs_size) AS "used_perc_%"
FROM
  (SELECT a.tablespace_name,
          b.tbs_size,
          a.free_space,
          tbs.contents
   FROM
     (SELECT tablespace_name,
             sum(bytes) free_space
      FROM dba_free_space
      GROUP BY tablespace_name) a,
     (SELECT tablespace_name,
             sum(bytes) tbs_size
      FROM dba_data_files
      GROUP BY tablespace_name) b,
                                dba_tablespaces tbs
   WHERE a.tablespace_name = b.tablespace_name
     AND a.tablespace_name = tbs.tablespace_name) t

查看执行计划：

通过对上述SQL的分解，可以发现执行缓慢主要是由于FROM dba_free_space部分，而从查阅到的资料来看，有的建议清理回收站的，有的建议通过加入hint重建dba_free_space视图的别名视图，但感觉需要做出的改动都较大。我们直接来看一下该SQL的执行计划：

SQL> alter session set statistics_level=ALL;

Session altered.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  akbmdv5gfs26k, child number 1
-------------------------------------
SELECT t.tablespace_name AS "tablespace",        substr(t.contents, 1,
4) AS "type",        trunc ((t.tbs_size - nvl (t.free_space, 0)) / 1024
/ 1024) AS "used_mb",        trunc (t.tbs_size / 1024 / 1024) AS
"total_mb",        trunc (nvl (t.free_space, 0) / 1024 / 1024) AS
"free_mb",        trunc ((t.tbs_size - nvl (t.free_space, 0)) * 100 /
t.tbs_size) AS "used_perc_%" FROM   (SELECT a.tablespace_name,
 b.tbs_size,           a.free_space,           tbs.contents    FROM
 (SELECT tablespace_name,              sum(bytes) free_space       FROM
dba_free_space       GROUP BY tablespace_name) a,      (SELECT
tablespace_name,              sum(bytes) tbs_size       FROM
dba_data_files       GROUP BY tablespace_name) b,
          dba_tablespaces tbs    WHERE a.tablespace_name =
b.tablespace_name      AND a.tablespace_name = tbs.tablespace_name) t

Plan hash value: 2040129742

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |      1 |        |   182 (100)|          |     17 |00:00:14.67 |     221K|  35475 |
|   1 |  NESTED LOOPS                         |                    |      1 |     11 |   182  (37)| 00:00:03 |     17 |00:00:14.67 |     221K|  35475 |
|   2 |   NESTED LOOPS                        |                    |      1 |      2 |   182  (37)| 00:00:03 |     17 |00:00:14.65 |     221K|  35475 |
|*  3 |    HASH JOIN                          |                    |      1 |      2 |   180  (38)| 00:00:03 |     17 |00:00:14.65 |     221K|  35475 |
|   4 |     VIEW                              |                    |      1 |      2 |     5  (20)| 00:00:01 |     17 |00:00:00.09 |     408 |      0 |
|   5 |      HASH GROUP BY                    |                    |      1 |      2 |     5  (20)| 00:00:01 |     17 |00:00:00.09 |     408 |      0 |
|   6 |       VIEW                            | DBA_DATA_FILES     |      1 |      2 |     4   (0)| 00:00:01 |     99 |00:00:00.08 |     408 |      0 |
|   7 |        UNION-ALL                      |                    |      1 |        |            |          |     99 |00:00:00.08 |     408 |      0 |
|   8 |         NESTED LOOPS                  |                    |      1 |      1 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |     103 |      0 |
|   9 |          NESTED LOOPS                 |                    |      1 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |     103 |      0 |
|  10 |           NESTED LOOPS                |                    |      1 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |     103 |      0 |
|* 11 |            FIXED TABLE FULL           | X$KCCFN            |      1 |      1 |     0   (0)|          |     99 |00:00:00.01 |       0 |      0 |
|* 12 |            TABLE ACCESS BY INDEX ROWID| FILE$              |     99 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |     103 |      0 |
|* 13 |             INDEX UNIQUE SCAN         | I_FILE1            |     99 |      1 |     0   (0)|          |     99 |00:00:00.01 |       4 |      0 |
|* 14 |           FIXED TABLE FIXED INDEX     | X$KCCFE (ind:1)    |      0 |      1 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |
|  15 |          TABLE ACCESS CLUSTER         | TS$                |      0 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|* 16 |           INDEX UNIQUE SCAN           | I_TS#              |      0 |      1 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |
|  17 |         NESTED LOOPS                  |                    |      1 |      1 |     2   (0)| 00:00:01 |     99 |00:00:00.08 |     305 |      0 |
|  18 |          NESTED LOOPS                 |                    |      1 |      1 |     1   (0)| 00:00:01 |     99 |00:00:00.08 |     202 |      0 |
|  19 |           NESTED LOOPS                |                    |      1 |      1 |     1   (0)| 00:00:01 |     99 |00:00:00.01 |     202 |      0 |
|  20 |            NESTED LOOPS               |                    |      1 |      1 |     0   (0)|          |     99 |00:00:00.01 |      99 |      0 |
|* 21 |             FIXED TABLE FULL          | X$KCCFN            |      1 |      1 |     0   (0)|          |     99 |00:00:00.01 |       0 |      0 |
|* 22 |             FIXED TABLE FIXED INDEX   | X$KTFBHC (ind:1)   |     99 |      1 |     0   (0)|          |     99 |00:00:00.01 |      99 |      0 |
|* 23 |            TABLE ACCESS BY INDEX ROWID| FILE$              |     99 |      1 |     1   (0)| 00:00:01 |     99 |00:00:00.01 |     103 |      0 |
|* 24 |             INDEX UNIQUE SCAN         | I_FILE1            |     99 |      1 |     0   (0)|          |     99 |00:00:00.01 |       4 |      0 |
|* 25 |           FIXED TABLE FIXED INDEX     | X$KCCFE (ind:1)    |     99 |      1 |     0   (0)|          |     99 |00:00:00.08 |       0 |      0 |
|  26 |          TABLE ACCESS CLUSTER         | TS$                |     99 |      1 |     1   (0)| 00:00:01 |     99 |00:00:00.01 |     103 |      0 |
|* 27 |           INDEX UNIQUE SCAN           | I_TS#              |     99 |      1 |     0   (0)|          |     99 |00:00:00.01 |       4 |      0 |
|  28 |     VIEW                              |                    |      1 |     17 |   175  (38)| 00:00:03 |     17 |00:00:14.56 |     220K|  35475 |
|  29 |      HASH GROUP BY                    |                    |      1 |     17 |   175  (38)| 00:00:03 |     17 |00:00:14.56 |     220K|  35475 |
|  30 |       VIEW                            | DBA_FREE_SPACE     |      1 |    371K|   152  (29)| 00:00:02 |  30709 |00:00:14.55 |     220K|  35475 |
|  31 |        UNION-ALL                      |                    |      1 |        |            |          |  30709

最低0.47元/天解锁文章

清秋_lwl

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
Oracle 11g dba_free_space视图查询慢解决方案——hint调整执行计划

ora11g中查询表空间的使用情况时需要通过dba_free_space视图来查看表空间的剩余量，但有时可能由于回收站的对象较多等原因，导致该视图查询速度较慢，本文将从其执行计划的角度分析其原因并提出一种相对实用且无风险的解决方式。
复制链接

扫一扫