在一个Oracle 19c（19.16.0.0.0）实例下使用下面脚本统计表空间的大小时，发现SQL执行非常慢。
set linesize 860 pages 100
col con_id for 9999
col con_name for a8
col tb_name for a12
col tb_size for 99,999.99 head "TB_SIZE|GB"
col tb_used_size for 99,999.99 head "TB_USED_SIZE|GB"
col tb_free_size for 99,999.99 head "TB_FREE_SIZE|GB"
col tb_extensible_size for 99,999.99 head "TB_EXTE_SIZE|GB"
col tb_used_rate for 99.99 head "TB_USED|_RATE%"
col tb_free_rate for 99.99 head "TB_FREE|_RATE%"
col tb_max_size for 999,999.99 head "TB_MAX|_SIZE|GB"
col max_used_rate for 999.99 head "MAX_USED_RATE|%"
col max_free_rate for 999.99 head "MAX_FREE_RATE|%"
BREAK ON CON_ID ON con_name
--
with cfs as
(
select cfs.con_id
, cfs.tablespace_name
, round(sum(cfs.bytes)/1024/1024/1024,3) as tb_free_size
from cdb_free_space cfs
group by cfs.con_id, cfs.tablespace_name
)
,cdf as
(
select cdf.con_id
, cdf.tablespace_name
, round(sum(cdf.bytes)/1024/1024/1024,3) tb_size
, round(sum(decode(cdf.maxbytes,0,cdf.bytes, cdf.maxbytes))/1024/1024/1024,2) as max_size
from cdb_data_files cdf
group by cdf.con_id,cdf.tablespace_name
)
,ctf as
(
select ctf.con_id
, ctf.tablespace_name
, round(sum(ctf.bytes)/1024/1024/1024,3) tb_size
, round(sum(decode(ctf.maxbytes,0, ctf.bytes, ctf.maxbytes))/1024/1024/1024,3) as max_size
from cdb_temp_files ctf
group by ctf.con_id,ctf.tablespace_name
)
,ctfs as
(
select ctfs.con_id
, ctfs.tablespace_name
, round(sum(ctfs.free_space)/1024/1024/1024,3) as tb_free_size
, round(sum(ctfs.tablespace_size)/1024/1024/1024,3) as tablespace_size
, round(sum(ctfs.allocated_space)/1024/1024/1024,3) as allocated_space
from cdb_temp_free_space ctfs
group by ctfs.con_id, ctfs.tablespace_name
)
select cdf.con_id as con_id
, c.name as con_name
, cdf.tablespace_name as tb_name
, cdf.tb_size as tb_size
, (cdf.tb_size - cfs.tb_free_size) as tb_used_size
, cfs.tb_free_size as tb_free_size
, (cdf.tb_size - cfs.tb_free_size)/cdf.tb_size*100 as tb_used_rate
, cfs.tb_free_size/cdf.tb_size*100 as tb_free_rate
, (cdf.max_size - cdf.tb_size ) as tb_extensible_size
, case
when cdf.max_size = 0 then 'No' else 'Yes'
end as autoextfile
, cdf.max_size as tb_max_size
, (cdf.tb_size - cfs.tb_free_size)/cdf.max_size*100
as max_used_rate
, (cdf.max_size - cdf.tb_size + cfs.tb_free_size)/cdf.max_size*100
as max_free_rate
from cfs, cdf, v$containers c
where cfs.con_id = cdf.con_id
and cfs.tablespace_name = cdf.tablespace_name
and c.con_id = cdf.con_id
union
select ctf.con_id as con_id
, c.name as con_name
, ctf.tablespace_name as tb_name
, ctfs.tablespace_size as tb_size
, (ctfs.tablespace_size - ctfs.tb_free_size) as tb_used_size
, ctfs.tb_free_size as tb_free_size
, (ctfs.tablespace_size - ctfs.tb_free_size)/ctfs.tablespace_size*100
as tb_used_rate
, ctfs.tb_free_size/ ctfs.tablespace_size*100 as tb_free_rate
, (ctf.max_size - ctfs.tablespace_size) as extensible_size
, case
when ctf.max_size = 0 then 'No' else 'Yes'
end as autoextfile
, ctf.max_size as tb_max_size
, round((ctfs.tablespace_size - ctfs.tb_free_size)/ctf.max_size*100,2)
as max_used_rate
, round((ctf.max_size -ctfs.tablespace_size+ctfs.tb_free_size)/ctf.max_size*100,2)
as max_free_rate
from ctf ,ctfs ,v$containers c
where ctf.con_id = ctfs.con_id
and ctf.con_id = c.con_id
order by con_id, con_name,max_free_rate;
分析定位后确认查询cdb_free_space这个视图时比较慢，查看了一下这个视图的定义，其数据来源于sys.dba_free_space这个视图
select text from dba_views where view_name=upper('cdb_free_space');
SELECT
k."TABLESPACE_NAME",
k."FILE_ID",
k."BLOCK_ID",
k."BYTES",
k."BLOCKS",
k."RELATIVE_FNO",
k."CON_ID",
k.con$name,
k.cdb$name,
k.con$errnum,
k.con$errmsg
FROM
CONTAINERS ( "SYS"."DBA_FREE_SPACE" ) k
#注意：sys用户下text有值，system用户下面text为null值，脚本需要改为查询text_vc字段。
那么肯定是dba_free_space这个视图导致查询变慢的，刚好之前看到过相关案例，主要是因为回收站（recyclebin）有大量对象导致查询dba_free_space时变慢，对应的metalink为Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)[1]
简单概述一下，从Oracle 10g开始，视图dba_free_space定义被修改了，它需要访问sys.recyclebin$。如果回收站中有大量的对象/记录的话，它会导致SQL查询变慢，这个是一个正常现象。
SQL> select text from dba_views where view_name='DBA_FREE_SPACE';
TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
and bitand(ts.flags, 4503599627370496) <> 4503599627370496
union all
select
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and u.ktfbuefno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
and bitand(ts.flags, 4503599627370496) <> 4503599627370496
union all
select ts.name, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0
union all
select
ts.name, fi.file#, f.extent_start,
(f.extent_length_blocks_2K /(ts.blocksize/2048)) * ts.blocksize,
(f.extent_length_blocks_2K / (ts.blocksize/2048)), fi.relfile#
from sys.ts$ ts, sys.new_lost_write_extents$ f, sys.file$ fi
where ts.ts# = f.extent_datafile_tsid
and f.extent_datafile_tsid = fi.ts#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
and bitand(ts.flags, 4503599627370496) = 4503599627370496
解决办法也非常简单，将回收站（recyclebin）中的对象清除后（当然，清理前要确认是否可以真的清除），清理回收站后（回收站总计有一千多个对象），上面SQL不到1秒就OK了。
另外，还有场景是因为查询sys.x$ktfbue这个固定表导致，因为sys.x$ktfbue会返回已经使用的扩展数据块信息。查询sys.x$ktfbue这个fixed table是一个开销很大的任务，而且由于优化器不知道其代价，生成的执行计划可能会选择重复扫描此表，为了阻止优化器生成这些次优执行计划，可以使用命令显示收集sys.x$ktfbue固定表的统计信息。
另外搜索metalink时，发现在多租户环境中，大量的PDB数据库也会导致一些视图变慢，例如cdb_free_space, cdb_data_files。对应的metalink为Space Related Dictionary View Performance Slow in Multitenant Database with Large Number of Pluggable Databases (Doc ID 2053791.1)[2]，具体内容如下所示：
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
In a multitenant database where large number of PDB (pluggable database) reside (for example, over 200), the following dictionary tables are reported to have notably bad performance:
cdb_free_space, cdb_data_files, cdb_temp_files, cdb_free_space, gv$sort_segment, cdb_undo_extents
Also Enterprise Manager Cloud Control 12c refers to several of these views with UNION clause in Administration -> Storage -> Tablespace page which may cause slow loading of the page
CHANGES
CAUSE
Certain space-related dictionary views which require IO to datafiles may consume time and cpu because large number of PDBs usually contains large number of data files.
SOLUTION
There are several potential workarounds when encountering performance issues when querying these views.
Potential Solution 1: Change the number of query worker processes
When a query to CDB_xxx dictionary view is executed (for example, CDB_FREE_SPACE), parallel worker processes are spawned to query each PDB with the corresponding DBA_xxx dictionary views (for example, DBA_FREE_SPACE). When a multitenant database contains large number of PDBs, querying CDB_xxx dictionary view may cause large number of workers to be spawned, and large number of DBA_xxx queries be executed in individual PDBs.
The number of worker processes to be spawned to iterate through individual PDBs for CDB_xxx dictionary queries could be controlled by hidden parameter _cdb_view_parallel_degree.
To avoid overwhelming the cpu, DBA may set _cdb_view_parallel_degree to limit the number of worker processes. Setting this value to small numbers (like 2 or 3) may prevent sudden CPU load, but causes the query to run longer because each PDB will be iterated by only 2 or 3 workers. In such case, try different _cdb_view_parallel_degree values, like 5, 10, 20, 50, 100, etc, and check the query speed and whether cpu load could be tolerated. If the performance of the CDB_xxx query was affected by limited number of parallel worker processes, then enlarging _cdb_view_parallel_degree may help improve performance.
Potential Solution 2: Gather table statistics
Some of the space related dictionary views refers to X$KTFBUE fixed table, which returns used extent information. Querying this fixed table is an expensive task, but optimizer may not be aware of that cost, and may choose an execution plan which scans this table repeatedly. To prevent such suboptimal plan to be chosen, explicitly gather statistics for X$KTFBUE fixed table with following command:
exec dbms_stats.gather_table_stats('SYS','X$KTFBUE');
Potential Solution 3: Change optimizer plan
Sometimes, changing the execution plan through following parameter helps improve performance.
_optimizer_adaptive_plans=false
Refer to following document for information on this parameter.
Adaptive Query Optimization Document 2031605.1
REFERENCES
NOTE:2031605.1 - Adaptive Query Optimization
BUG:21489714 - 250 PDBS: POOR PERFORMANCE OF CDB QUERY FROM CLOUD CONTROL
参考资料
Doc ID 271169.1: https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=1auooq1lfx_4&_afrLoop=356104159315000
[2]
Doc ID 2053791.1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=356314387428095&id=2053791.1&_adf.ctrl-state=1auooq1lfx_148
原文始发于微信公众号（DBA闲思杂想录）：Oracle 19c查询cdb_free_space很慢总结
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/228053.html
