监控查询表空间非常慢

原创

食品安全辛吉飞 2023-07-16 20:49:55 博主文章分类：Oracle ©著作权

文章标签 表空间 x$ktfbue 统计信息回收站 文章分类 Oracle 数据库 私藏项目实操分享

©著作权归作者所有：来自51CTO博客作者食品安全辛吉飞的原创作品，请联系作者获取转载授权，否则将追究法律责任

问题概述

查询oracle表空间相关信息，返回时间特别长，查询语句为

SELECT a.tablespace_name,
ROUND (a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
ROUND (NVL (b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
ROUND ((a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024, 2 ) megs_used,
ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_free,
100 - ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) * 100, 2) pct_used,
ROUND (maxbytes / 1048576, 2) MAX
FROM
(SELECT f.tablespace_name,
SUM (f.BYTES) bytes_alloc,
SUM (DECODE (f.autoextensible, ‘YES’, f.maxbytes, ‘NO’, f.BYTES ) ) maxbytes
FROM dba_data_files f
GROUP BY tablespace_name
) a,
(SELECT f.tablespace_name,
SUM (f.BYTES) bytes_free
FROM dba_free_space f
GROUP BY tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
SELECT h.tablespace_name,
ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
ROUND ( SUM ((h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0)) / 1048576, 2 ) megs_free,
ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576, 2) megs_used,
ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) / SUM (h.bytes_used + h.bytes_free) ) * 100, 2 ) pct_free,
100 - ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) / SUM (h.bytes_used + h.bytes_free) ) * 100, 2 ) pct_used,
ROUND (SUM (f.maxbytes) / 1048576, 2) MAX
FROM SYS.v_$temp_space_header h,
SYS.v_$temp_extent_pool p,
dba_temp_files f
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
AND f.file_id = h.file_id
AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY 1 ;

问题原因

1.回收站数据特别多

select count(*) from recyclebin;

2.X$KTFBUE统计信息为空

当分析ORACLE存储空间分配情况时，常用到三个视图DBA_SEGMENTS,DBA_EXTENTS。

DBA_FREE_SPACE,DBA_SEGMENTS（段信息）基于段信息数据字典seg; 另外常用的两个DBA_EXTENTS（已经分配区信息）, DBA_FREE_SPACE(可用区信息）有所不同，底层会基于X表。X$表没有实体表，当读取时实际是执行了ORACLE相应功能代码。

X$KTFBUE ==== DBA_EXTENTS

查询会读取段头、位图块,当数据库比较大，SEGMENT比较多时，读取会非常慢。当同时指定KTFBUESEGTSN，KTFBUESEGFNO，KTFBUESEGBNO 条件进行查询时，可以用到X$上的特殊索引，加快查询。

SQL> @desc X$KTFBUE
Name Null? Type
------------------------------- -------- ----------------------------
1 ADDR RAW(8)
2 INDX NUMBER
3 INST_ID NUMBER
4 CON_ID NUMBER
5 KTFBUESEGTSN NUMBER
6 KTFBUESEGFNO NUMBER
7 KTFBUESEGBNO NUMBER
8 KTFBUEEXTNO NUMBER
9 KTFBUEFNO NUMBER
10 KTFBUEBNO NUMBER
11 KTFBUEBLKS NUMBER
12 KTFBUECTM NUMBER
13 KTFBUESTT VARCHAR2(20)
14 KTFBUESTA NUMBER

X$KTFBFE ==== DBA_FREE_SPACE
查询会读取文件头、位图块

SQL> @desc X$KTFBFE
Name Null? Type
------------------------------- -------- ----------------------------
1 ADDR RAW(8)
2 INDX NUMBER
3 INST_ID NUMBER
4 CON_ID NUMBER
5 KTFBFETSN NUMBER
6 KTFBFEFNO NUMBER
7 KTFBFEBNO NUMBER
8 KTFBFEBLKS NUMBER

解决方案

connect / as sysdba
purge recyclebin;
column owner format a6；
column table_name format a10；
column last_anal format a10；
SELECT owner, table_name, last_analyzed
FROM dba_tab_statistics
WHERE table_name=‘X$KTFBUE’;OWNER TABLE_NAME LAST_ANAL
SYS X$KTFBUE
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>‘SYS’, TABNAME => ‘X$KTFBUE’,ESTIMATE_PERCENT=>100);

参考文档

Query Against Dictionary Views Extracting Tablespace Information is Slow or Appears to Hang (Doc ID 1292253.1)

优化过SQL

SET LINES 132 PAGES 100
COL con_name        FORM A15 HEAD "Container|Name"
COL tablespace_name FORM A15
COL fsm             FORM 999,999,999,999 HEAD "Free|Space Meg."
COL apm             FORM 999,999,999,999 HEAD "Alloc|Space Meg."
--
COMPUTE SUM OF fsm apm ON REPORT
BREAK ON REPORT ON con_id ON con_name ON tablespace_name
--
WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm
           FROM cdb_free_space cf1
               ,v$containers c1
           WHERE cf1.con_id = c1.con_id
           GROUP BY c1.con_id, cf1.tablespace_name),
     y AS (SELECT c2.con_id, cd.tablespace_name, SUM(cd.bytes)/1024/1024 apm
           FROM cdb_data_files cd
               ,v$containers c2
           WHERE cd.con_id = c2.con_id
           GROUP BY c2.con_id
                   ,cd.tablespace_name)
SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm
FROM x, y, v$containers v
WHERE x.con_id          = y.con_id
AND   x.tablespace_name = y.tablespace_name
AND   v.con_id          = y.con_id
UNION
SELECT vc2.con_id, vc2.name, tf.tablespace_name, null, SUM(tf.bytes)/1024/1024
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.con_id, vc2.name, tf.tablespace_name
ORDER BY 1, 2;


set pages 80
set lin 120
set echo off
set feed off
column PCT format 999.99
column tbspce format A30
col container for a30
select substr(f.tablespace_name,1,30) tbspce,
     round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)",
     round(nvl(s.segbytes,0)/(1024*1024),0) "USED(MB)",
     round((nvl(s.segbytes,0)/f.tsbytes)*100,2) PCT,
     lower(vc.name) as container
from
   (select con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by con_id,tablespace_name) f,
   (select con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by con_id,tablespace_name) s,
   v$containers vc
where f.con_id=s.con_id(+)
  and f.tablespace_name=s.tablespace_name(+)
  and f.con_id=vc.con_id
order by container, tbspce;



SET LINES 300 PAGES 100
COL con_name        FORM A15 HEAD "Container|Name"
COL files           FORM 999,999 HEAD "Num Files"
COL tablespace_name FORM A30
COL fsm             FORM 999,999,999,999 HEAD "Free|Space Meg."
COL apm             FORM 999,999,999,999 HEAD "Alloc|Space Meg."
--
COMPUTE SUM OF fsm apm files ON con_id REPORT
BREAK ON REPORT ON con_id ON con_name ON tablespace_name
--
WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm
           FROM cdb_free_space cf1
               ,v$containers c1
           WHERE cf1.con_id = c1.con_id
           GROUP BY c1.con_id, cf1.tablespace_name),
     y AS (SELECT c2.con_id, cd.tablespace_name, count(*) files,SUM(cd.bytes)/1024/1024 apm
           FROM cdb_data_files cd
               ,v$containers c2
           WHERE cd.con_id = c2.con_id
           GROUP BY c2.con_id
                   ,cd.tablespace_name)
SELECT x.con_id, v.name  con_name, x.tablespace_name,files, x.fsm, y.apm, round(1-fsm/apm,2) pct
FROM x, y, v$containers v
WHERE x.con_id          = y.con_id
AND   x.tablespace_name = y.tablespace_name
AND   v.con_id          = y.con_id
UNION All
SELECT vc2.con_id, vc2.name , tf.tablespace_name,count(*) files, null, SUM(tf.bytes)/1024/1024, null
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.con_id, vc2.name , tf.tablespace_name
ORDER BY 1, 2;