ANBOB™

最近一客户的Oracle 19c环境在使用expdp导出分区变慢任务积压很严重，对于这个客户每月几万分区的EXPDP备份无法忍受，几M小空分区都要6分钟以上，导出速度和导出需求一样不科学☺。对datapump进程可以做sql trace跟踪，同时从导出时间段的AWR的TOP SQL看，这库似乎也没啥正常业务负载， TOP 1 SQL是DATA pump worker在查询v$open_cursor,文本如下：

SELECT COUNT(*) FROM sys.v_$open_cursor WHERE sid = SYS_CONTEXT('USERENV',  'SID') AND cursor_type = 'OPEN_PLSQL'

AWR

Note:
注意TOP 1就是该SQL,单次执行需要7秒多。 其它SQL多数是expdp相关和几个查询表空间使用率的。

在MOS中不难查到这是oracle在解决一个内部bug时从Oracle V12.2引入了这个查询，也是一个Bug 28771564 – Datapump Export Invoked By A Privilege User Is Slow Due To Query To V$open_cursor (Doc ID 28771564.8), 到20C后才修复， 或expdp导出的用户不带下面这些角色如DBA, EXP_FULL_DATABASE.

Datapump export invoked by a user who has a privilege role such as DBA,EXP_FULL_DATABASE, IMP_FULL_DATABASE, DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE executes queries for v$open_cursor.
This causes performance degradation.

解决办法

1, 不使用上面的角色，如果expdp导出自己的表需要哪些权限呢？

SQL> create user uexp identified by uexp;
User created.

SQL> grant read,write on directory DATAPUMP to uexp;
Grant succeeded.

SQL> grant create session to uexp;
Grant succeeded.

# 需要创建队列表

SQL> grant create table to uexp;
Grant succeeded.

SQL> alter user uexp quota 100m on users;
User altered.

SQL> create table uexp.t1 as select * from dba_objects;

导出 自己的表

oracle@oel7db1 ~]$ expdp uexp/uexp@cdb1pdb1 directory=datapump tables=t1 dumpfile=t1_new.dump

Export: Release 19.0.0.0.0 - Production on Thu Jun 22 18:58:39 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "UEXP"."SYS_EXPORT_TABLE_01": uexp/********@cdb1pdb1 directory=datapump tables=t1 dumpfile=t1_new.dump
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "UEXP"."T1" 10.10 MB 76546 rows
Master table "UEXP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for UEXP.SYS_EXPORT_TABLE_01 is:
/home/oracle/t1_new.dump
Job "UEXP"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jun 22 18:59:15 2023 elapsed 0 00:00:33

导出其它schema的表

[oracle@oel7db1 ~]$ expdp uexp/uexp@cdb1pdb1 directory=datapump tables=anbob.test1 dumpfile=t1_new.dump

Export: Release 19.0.0.0.0 - Production on Thu Jun 22 19:45:18 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas

通常我们是给以下ROLE， 除了这些role没有找到好办法，你如果有兴趣可以尝试。
EXP_FULL_DATABASE >> DATAPUMP_EXP_FULL_DATABASE >> DBA

2， 优化SQL
因为导出查询的是sys.v_$open_cursor关于SQL游标打开，FV源头是X$KGLLK， X$是Oracle内部结构表有5个对象可以研究X$

x$kqfta – headline information about the x$ structures – name, size, column count.
x$kqfvi – a list of the names of the dynamic performance views (mostly pairs of v$/gv$)
x$kqfvt – a list of the select statements that define the views in x$kqfvi
x$kqfdt – a list of “derived tables”: a cross between synonyms and views of a few of the structures in x$kqfta
x$kqfco – a list of the “columns” in each of the tables in x$kqfta (but not x$kqfvi and x$kqfdt)

X$在实例的内存结构，之前我blog记录过一个数据库长期未重启，同样是expdp导致1个实例的X$记录过多的问题， 同样这个X$KGLLK是在实例2导出慢，但记录数据数实例2比实例1少，实例1导出相同分区小于1分钟，比实例2快5分钟，所以临时解决可以尝试先从实例1导出， 或重启实例2.

对于是否X$KGLLK内存结构碎片化或过大，您用兴趣可以研究一下。JL大师有2篇不错的blog可以参考Excavating X$ 和X$ row sizes

打赏

微信扫一扫，打赏作者吧～