主页 / dba / 问题 / 301405
Accepted
ggo
Asked: 2021-10-21 05:59:13 +0800 CST

Oracle 简单 count(*) SQL 查询需要“永远”

  • 772

我在 Windows Server 2019 上新安装 Oracle 12c 时遇到“问题”（至少是一种奇怪/缓慢的行为），此查询至少需要 5 分钟才能完成：

    SELECT COUNT(*) FROM SYS.ALL_VIEWS;

为什么要花这么多时间？我错过了什么？

Oracle 12c 在 SSD 上的 Win2019（在 VMWare VM 中）上运行。VM 有 5 GB 内存。对于“真正的”数据库来说，这可能还不够，但我的数据库包含一个默认的通用数据库（没有用户数据），因为我只需要为客户进行快速测试。

谢谢你。

oracle oracle-12c

2 个回答

  1. Best Answer
    SELECT COUNT(*) FROM SYS.ALL_VIEWS;

    不是一个简单的查询。ALL_VIEWS是一个非常复杂的视图，它有许多连接需要特殊的逻辑来确定调用用户实际上可以看到什么。

    您可以使用all_views查看背后的逻辑all_views，这就是它在 19c 上的样子。

    select OWNER, VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC,TYPE_TEXT_LENGTH, TYPE_TEXT,
       OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME,
       EDITIONING_VIEW, READ_ONLY,  CONTAINER_DATA, BEQUEATH,
       ORIGIN_CON_ID, DEFAULT_COLLATION, CONTAINERS_DEFAULT, CONTAINER_MAP,
       EXTENDED_DATA_LINK, EXTENDED_DATA_LINK_MAP, HAS_SENSITIVE_COLUMN
from int$dba_views
where (OWNER = SYS_CONTEXT('USERENV', 'CURRENT_USER')
       or OBJ_ID(OWNER, VIEW_NAME, 4, OBJECT_ID) in
            (select oa.obj#
             from sys.objauth$ oa
             where oa.grantee# in ( select kzsrorol
                                         from x$kzsro
                                  )
            )
        or /* user has system privileges */
           /* 4 is the type# for Views. See kgl.h for more info */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -397/* READ ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
      )

    够简单吗？好吧，int$dba_views实际上是一个视图本身，所以让我们更深入一点，这个视图定义为

    select u.name, u.user#, o.name, o.obj#, o.type#, v.textlength, v.text,
       getlong(1, v.rowid),
       t.typetextlength, t.typetext,
       t.oidtextlength, t.oidtext, t.typeowner, t.typename,
       decode(bitand(v.property, 134217728), 134217728,
              (select sv.name from superobj$ h, sys."_CURRENT_EDITION_OBJ" sv
              where h.subobj# = o.obj# and h.superobj# = sv.obj#), null),
       decode(bitand(v.property, 32), 32, 'Y', 'N'),
       decode(bitand(v.property, 16384), 16384, 'Y', 'N'),

       decode(bitand(v.property/4294967296, 134217728), 134217728, 'Y', 'N'),
       decode(bitand(o.flags,8),8,'CURRENT_USER','DEFINER'),
       case when bitand(o.flags, (65536+131072+4294967296))>0 then 1 else 0 end,
       to_number(sys_context('USERENV', 'CON_ID')),
       nls_collation_name(nvl(o.dflcollid, 16382)),
       -- CONTAINERS_DEFAULT
       decode(bitand(v.property, power(2,72)), power(2,72), 'YES', 'NO'),
       -- CONTAINER_MAP
       decode(bitand(v.property, power(2,80)), power(2,80), 'YES', 'NO'),
       -- EXTENDED_DATA_LINK
       decode(bitand(v.property, power(2,52)), power(2,52), 'YES', 'NO'),
       -- EXTENDED_DATA_LINK_MAP
       decode(bitand(v.property, power(2,79)), power(2,79), 'YES', 'NO'),
       -- HAS_SENSITIVE_COLUMN
       decode(bitand(v.property, power(2,89)), power(2,89), 'YES', 'NO')
from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u, sys.typed_view$ t
where o.obj# = v.obj#
  and o.obj# = t.obj#(+)
  and o.owner# = u.user#

    猜猜看，sys."_CURRENT_EDITION_OBJ"也是一个具有复杂过滤器的视图。我不会继续粘贴，因为您可以在自己的 12c 安装上跟踪跟踪（并且可能会略有不同）。

    但是这里要注意的重要一点是，ALL_VIEWS当您按名称查找视图时，实际使用速度很快，计算您可能能够使用的每个视图并不快（这是合理的，因为它不是您永远需要匆忙做）。

    如果您想ALL_VIEWS在某种基准测试中使用，那么您可以将其具体化为一个表格并计算它。

    create table all_views_mat as select * from all_views;

select count(*) from all_views_mat;
    • 3

  2. 在我公司的一个大小为 1 TB 的 Oracle 实例上运行相同的查询，执行时间不到一秒。

    作为 SYS AS SYSDBA 执行

    set pages 50
set lines 230
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT CURRENT_DATE FROM DUAL;
SELECT COUNT(*) FROM SYS.ALL_VIEWS;
SELECT CURRENT_DATE FROM DUAL;

    结果是：

    Session altered.

CURRENT_DATE         
---------------------
2021-11-05 15:00:26  
1 row selected.

  COUNT(*)
----------
      6785
1 row selected.

CURRENT_DATE         
---------------------
2021-11-05 15:00:26  
1 row selected.

    正如其他答案所指出的，它可能与您查询的视图的复杂性有关，但我不会把所有的钱都押在这上面。

    其他需要考虑的事情

    查询可能与您拥有的权限相关。您是否尝试将查询运行为SYS as SYSDBA？

    然后，拥有一个只有 5 GB 的 Oracle 数据库实例和一个 10 GB 大小的数据库可能会影响性能。想一想：数据还没有在缓存中。

    另一个问题与 Oracle 实例的内存配置有关。您是在配置和参数的地方运行自动内存管理(AMM)，还是在配置MEMORY_MAX_TARGET和MEMORY_TARGET参数的地方运行自动共享内存管理(ASMM) ？您是否将这些值设置为高于 1 GB，以便 Oracle 数据库实例可以受益于将一些数据库对象（数据）缓存到 RAM 的能力？sga_targetsga_max_size

    如果第二次运行查询会发生什么？你有同样糟糕的表现吗？思考：数据库（数据）缓存。

    如果您的本地系统正在运行其他虚拟机，请关闭它们并重试。它跑得更快吗？

    您是否在同一台桌面/服务器上运行任何其他内存或 CPU 密集型应用程序？关闭它们。

    防毒软件？如果关闭或按照 Oracle 的建议从扫描中排除某些文件/目录，请关闭。

    如果您遵循其中的一些建议，那么您应该在查询简单视图时观察到性能的提高。

    哦，顺便说一下，我的 Oracle 实例大小为 1 TB，并且SGA_TARGET设置为84 GB. 内存对于数据库性能至关重要。

    以 SYSTEM 身份运行查询

    Session altered.

CURRENT_DATE         
---------------------
2021-11-10 12:46:24  
1 row selected.

  COUNT(*)
----------
      6785
1 row selected.

CURRENT_DATE         
---------------------
2021-11-10 12:46:25  
1 row selected.

    以非特权用户身份运行查询

    我创建了一个非特权用户UNPRIV_USER，除此之外没有任何特权GRANT CONNECT ...，GRANT CREATE SESSION ...然后对巨大的 1TB Oracle 数据库实例重新运行相同的语句，结果如下：

    Session altered.

CURRENT_DATE         
---------------------
2021-11-10 12:56:40  
1 row selected.

  COUNT(*)
----------
      1729
1 row selected.

CURRENT_DATE         
---------------------
2021-11-10 12:56:40  
1 row selected.

    因此，虽然非特权用户无权访问某些 VIEWS，但这不会影响查询执行的持续时间。

    结论

    权限/特权可能会对简单查询的执行时间产生影响，但在我的环境中，它似乎没有那么大的影响。（+/- 1 秒）

    我将开始查看您的虚拟化和/或特定于实例的配置设置。

    例如，我的实例在专用硬件上运行（16 核 @ 2.8 GHz，768 GB RAM，84 GB SGA 用于上述实例）。

    桌面操作系统与服务器操作系统

    桌面操作系统和服务器操作系统的优先级不同。桌面操作系统将优先考虑前台应用程序，而服务器操作系统通常会配置为优先考虑后台应用程序（服务）。

    在 Windows 系统上，请按照以下快速步骤配置系统以优先处理后台应用程序：

    要提高 SQL Server / Oracle 主机实例等后台服务的性能，请执行以下步骤：

    • 单击开始，单击控制面板，然后单击系统。
      -或者：点击 Windows 键并输入sysdm.cpl然后ENTER。这应该会打开系统控制面板。
    • 单击高级选项卡，然后单击性能下的设置。
    • 单击“高级”选项卡，单击“后台服务”，然后单击“确定”两次。

    这应该会为 Windows 操作系统上的 Oracle 服务带来更好的性能。

    • 2

相关问题