Oracle 统计表空间使用率越来越慢是怎么回事

admin 2024年9月22日14:10:34评论11 views字数 2876阅读9分35秒阅读模式

统计表空间使用率时如果使用到了 DBA_FREE_SPACE 可以会遇到执行越来越慢的情况,

比如以下语句:

-- 按最终使用比排序select UPPER(F.TABLESPACE_NAME) as "表空间名称",       ROUND(D.AVAILB_BYTES, 2) as "表空间大小(G)",       ROUND(D.MAX_BYTES, 2) as "最终表空间大小(G)",       ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) as "已使用空间(G)",       TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,                     2),               '999.99') as "使用比",       TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.MAX_BYTES * 100, 2),               '999.99') as "最终使用比",       ROUND(F.USED_BYTES, 6) as "空闲空间(G)",       ROUND(D.MAX_BYTES - D.AVAILB_BYTES, 2) as "最终空闲可用空间(G)"  from (select TABLESPACE_NAME,               ROUND(sum(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,               ROUND(max(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES          from SYS.DBA_FREE_SPACE         group by TABLESPACE_NAME) F,       (select DD.TABLESPACE_NAME,               ROUND(sum(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,               ROUND(sum(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES)) /                     (1024 * 1024 * 1024),                     6) MAX_BYTES          from SYS.DBA_DATA_FILES DD         group by DD.TABLESPACE_NAME) D where D.TABLESPACE_NAME = F.TABLESPACE_NAME order by 6 desc;

实际执行时需要12秒左右,明显在执行效率上有点儿异常。语句中用到 dba_data_files 视图 和 dba_free_space 视图。dba_data_files 视图和dba_free_space 视图都会引用许多数据库基表。

查看以上语句的执行计划,发现花费主要集中在 X$KTFBUF 基表上,并且没有走索引。 

Oracle 统计表空间使用率越来越慢是怎么回事

查看 X$KTFBUF 的统计信息发现 LAST_ANALYZED 字段为空,说明该表的统计信息没有收集。

select OWNER, TABLE_NAME, LAST_ANALYZED  from DBA_TAB_STATISTICS where TABLE_NAME = 'X$KTFBUE';

手动收集 X$KTFBUE 基表的统计信息:

begin  DBMS_STATS.GATHER_TABLE_STATS(      OWNNAME => 'SYS',      TABNAME => 'X$KTFBUE',      ESTIMATE_PERCENT => 100);end;

以上语句大概执行了85秒,收集完 X$KTFBU统计信息,重新执行表空间使用率的查询语句发现性能并没有提升。中间被打断拉去开会去了,本打算开完会再回来整,结果会一直开到下班。

第二天早上上班,重新执行表空间使用率的查询语句发现性能恢复了,之前需要12秒,现在只需要 1.153 秒。

Oracle 统计表空间使用率越来越慢是怎么回事

重新查看执行计划,发现以前走基表的,现在开始走索引:

Oracle 统计表空间使用率越来越慢是怎么回事

走索引后,消耗降低,以前的花费大头现在几乎可以忽略不计,说明收集统计信息对性能提升有用,统计信息不准或陈旧会造成执行计划误判,该走索引的可能不走索引而是全表扫描。至于为什么放了一晚才生效,可能数据库夜间执行了什么定时任务,具体执行了什么起到了优化作用不得而知。

dba_free_space 中查询到的 bytes 是 dba_data_files 中空闲空间。当数据文件全部被利用时,则 dba_free_space 中对应的 FILE_ID 会消失:

原文:If a data file does not have any free space, you will not see a row for the data file in this view.DBA_FREE_SPACEhttps://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/DBA_FREE_SPACE.html

Oracle 统计表空间使用率越来越慢是怎么回事

除了使用 dba_free_space 统计表空间使用率,也可以使用 dba_segments 统计表空间使用率。

使用 dba_segments 比较直观,所有数据库对象占用的空间就是使用量,数据文件的最大扩展空间就是总大小,总大小减去使用量就是空闲空间:

with A as (select T.TABLESPACE_NAME,         sum(DECODE(T.AUTOEXTENSIBLE,                    'NO',                    T.BYTES,                    'YES',                    32 * 1024 * 1024 * 1024)) as BYTES    from DBA_DATA_FILES T   group by T.TABLESPACE_NAME),B as (select T.TABLESPACE_NAME, sum(T.BYTES) BYTES    from DBA_SEGMENTS T   group by T.TABLESPACE_NAME),C as (select A.TABLESPACE_NAME,         B.BYTES as USED,         A.BYTES - B.BYTES as FREE,         A.BYTES as TOTAL    from A, B   where A.TABLESPACE_NAME = B.TABLESPACE_NAME)select C.TABLESPACE_NAME as TABLESPACE,       ROUND(C.FREE / 1024 / 1024 / 1024, 0) as FREE_G,       ROUND(C.USED / 1024 / 1024 / 1024, 0) as USED_G,       ROUND(C.TOTAL / 1024 / 1024 / 1024, 0) as TOTAL_G,       ROUND((C.USED / C.TOTAL) * 100, 2) USED_RATE  from C order by 4 desc;

以上语句比使用 dba_free_space 要来的直观,dba_free_space 原数据都是 block 起始编号及空闲空间大小,不太直观。

参考

# Oracle 数据库表空间使用率查询慢https://mp.weixin.qq.com/s/Ek6dYPBg5YiwWtC_uegzVQ# DBA_FREE_SPACEhttps://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/DBA_FREE_SPACE.html

全文完。

原文始发于微信公众号(生有可恋):Oracle 统计表空间使用率越来越慢是怎么回事

免责声明:文章中涉及的程序(方法)可能带有攻击性,仅供安全研究与教学之用,读者将其信息做其他用途,由读者承担全部法律及连带责任,本站不承担任何法律及连带责任;如有问题可邮件联系(建议使用企业邮箱或有效邮箱,避免邮件被拦截,联系方式见首页),望知悉。
  • 左青龙
  • 微信扫一扫
  • weinxin
  • 右白虎
  • 微信扫一扫
  • weinxin
admin
  • 本文由 发表于 2024年9月22日14:10:34
  • 转载请保留本文链接(CN-SEC中文网:感谢原作者辛苦付出):
                   Oracle 统计表空间使用率越来越慢是怎么回事https://cn-sec.com/archives/3136696.html
                  免责声明:文章中涉及的程序(方法)可能带有攻击性,仅供安全研究与教学之用,读者将其信息做其他用途,由读者承担全部法律及连带责任,本站不承担任何法律及连带责任;如有问题可邮件联系(建议使用企业邮箱或有效邮箱,避免邮件被拦截,联系方式见首页),望知悉.

发表评论

匿名网友 填写信息