Skip to content

数据技术

DBMS_JOB和DBMS_SCHEDULER之间存在内部事务机制,将对DBMS_JOB_MAP表中的重复名称进行内部检查。并且此检查可能导致进程挂起。通

过在一个会话中使用DBMS_JOB创建一个作业,但是不提交,可以非常轻松地重现此内容。然后在第二个会话中创建另一个作业,该作业将挂起。引起其他进程的阻塞

 

id:2645984.1 –在SYS.SCHEDULER $ _DBMSJOB_MAP上删除引起行锁,将补丁30835853应用于Oracle 19c主目录,目前19c linux平台的RU并没有解决此问题,可以提单独的SR申请补丁

 

 

To BottomTo Bottom

In this Document

Symptoms
Changes
Cause
Solution
References

APPLIES TO:

Oracle Database – Enterprise Edition – Version 19.4.0.0.0 and later
Information in this document applies to any platform.
Upgrade done from 12.1.0.2 to 19c

New dictionary table SYS.SCHEDULER$_DBMSJOB_MAP is created in 19c.

SYMPTOMS

Application jobs are slow with “enq: TX – row lock contention” event on the below recursive SQL:

DELETE FROM SYS.SCHEDULER$_DBMSJOB_MAP WHERE JOB_NAME NOT IN (SELECT NAME FROM SYS.OBJ$ WHERE NAME LIKE ‘DBMS_JOB$_%’)

CHANGES

Upgrade to 19c

CAUSE

The issue is due to the below bug:

Bug 30835853 – DBMS_JOB.SUBMIT PROCEDURE BEHAVIOR CHANGE IN 19C

After upgrade database to 19c, the new dictionary table SYS.SCHEDULER$_DBMSJOB_MAP is introduced.

Application jobs may be slow due to below recursive SQL statement causing “enq: TX – row lock contention”.

DELETE FROM SYS.SCHEDULER$_DBMSJOB_MAP WHERE JOB_NAME NOT IN (SELECT NAME FROM SYS.OBJ$ WHERE NAME LIKE ‘DBMS_JOB$_%’)

SOLUTION

Apply the Patch 30835853 for your version

Oracle 19.3.0 升级到 19.7.0需要DBMS_JOB额外补丁以解决hang的问题

日前oracle 19c通过了ol8和红帽8的认证,但是现在还不建议直接上生产

 

Oracle Database 19c is certified on OL8 and RHEL8

Oracle Database 19c is certified on OL8 and RHEL8

 

Oracle Linux 8 版本对19c数据库的一些基本要求

 

Minimum RU: 19.7 (or 19.6 with patches, refer to aforementioned certification notes)

Minimum kernel versions:

    • Oracle Linux 8.1 with the Unbreakable Enterprise Kernel 6: 5.4.17-2011.0.7.el8uek.x86_64 or later,
      or
    • Oracle Linux 8.0 with the Red Hat Compatible kernel: 4.18.0-80.el8.x86_64 or later

ACFS: Please refer to ACFS Supported on OS Platforms for Oracle ACFS certification details (MOS Note: 1369107.1)

 

Red Hat Enterprise Linux 8 版本对19c数据库的一些基本要求

 

Minimum RU: 19.7 (or 19.6 with patches, refer to aforementioned certification notes)

Minimum kernel versions:

    • Oracle Linux 8.0 with the Red Hat Compatible kernel: 4.18.0-80.el8.x86_64 or later

ACFS: Please refer to ACFS Supported on OS Platforms for Oracle ACFS certification details (MOS Note: 1369107.1)

具体参考如下:

Oracle Database 19c已通过OL8和RHEL8认证

新装的oracle 19c grid集群启动报错,日志信息如下:

[root@luda bin]# crsctl start crs
CRS-41053: checking Oracle Grid Infrastructure for file permission issues
PRVH-0112 : Path “/etc/oracle/maps” with permissions “rwxrwxr-x” does not have write permissions for others on node “rac1”.
PRVH-0100 : Restricted deletion flag is not set for path “/etc/oracle/maps” on node “rac1”.
PRVH-0100 : Restricted deletion flag is not set for path “/oracle/19c/grid/crsdata/rac1/shm” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/jssu” on node “rac1”.
PRVH-0147 : Set group ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/extproc” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/extjob” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/oracle” on node “rac1”.
PRVH-0147 : Set group ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/oracle” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/oracle/19c/19.3.0/grid/bin/oradism” on node “rac1”.
CRS-4124: Oracle High Availability Services startup failed.
CRS-4000: Command Start failed, or completed with errors.

 

检查发现权限有问题,通过增加权限解决

 

chmod a+s /oracle/19c/19.3.0/grid/bin/extjob
chmod a+s /oracle/19c/19.3.0/grid/bin/oracle
chmod a+s /oracle/19c/19.3.0/grid/bin/oradism
chmod +t /oracle/19c/grid/crsdata/rac1/shm

Oracle 19c RAC PRVG-11960 0147

在12.2之前,如果你想获得实时的数据,那么在利用query rewrite前,你必须得用on commit的刷新方式刷新物化视图。但是on commit的刷新方式有众多限制。所以,以往的方式中需要采用on command的方式来进行刷新(不管是全量刷新还是增量刷新)。

在使用on command刷新的时候,通过设置job来定时的刷新物化视图,一次job运行之后,下一次job到来之前,如果基表有数据变化是不会体现到物化视图的查询结果里的,实时物化视图就是解决这种问题的,实时获取数据而且免去频繁刷新mv。

 

下面的测试仅供参考:

 

SQL> create table t1 (x not null primary key, y not null) as
2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000;

Table created.

SQL> create materialized view log on t1 with rowid (x, y) including new values;

Materialized view log created.

SQL>
SQL> create materialized view mv_old
2 refresh fast on demand
3 enable on query computation
4 enable query rewrite
5 as
6 select y , count(*) c1
7 from t1
8 group by y;

Materialized view created.

create mv时的关键字:enable on query computation

SQL> create table t2 (x not null primary key, y not null) as
2 select rownum x, mod(rownum, 10) y from dual connect by level <= 1000000;

Table created.

SQL> create materialized view log on t2 with rowid (x, y) including new values;

Materialized view log created.

SQL>
SQL> create materialized view mv_new
2 refresh fast on demand
3 enable on query computation
4 enable query rewrite
5 as
6 select y , count(*) c1
7 from t2
8 group by y;

Materialized view created.

SQL> show parameter rewrite

NAME TYPE VALUE
———————————— ———– ——————————
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

SQL> set autotrace on explain stat

SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS O
—————————————- ——————- –
MV_OLD FRESH N
MV_NEW FRESH Y

SQL>
SQL> select count(*) from MLOG$_T1;

COUNT(*)
———-
0

SQL> select count(*) from MLOG$_T2;

COUNT(*)
———-
0

SQL>

SQL> insert into t1
2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999;

999 rows created.

SQL>
SQL> insert into t2
2 select 1000000+rownum, mod(rownum, 3) from dual connect by level <= 999;

999 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select mview_name,staleness,on_query_computation from user_mviews;

MVIEW_NAME STALENESS O
—————————————- ——————- –
MV_OLD NEEDS_COMPILE N
MV_NEW NEEDS_COMPILE Y

SQL>
SQL> select count(*) from MLOG$_T1;

COUNT(*)
———-
999

SQL> select count(*) from MLOG$_T2;

COUNT(*)
———-
999

SQL>

由于数据stale,且没有set query_rewrite_integrity=stale_tolerated,传统mv没有进行query write。
SQL> select y as y_new_parse1, count(*) from t1
2 group by y;

Y_NEW_PARSE1 COUNT(*)
———— ———-
1 100333
6 100000
2 100333
4 100000
5 100000
8 100000
3 100000
7 100000
9 100000
0 100333

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 136660032

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 10 | 30 | 515 (4)| 00:00:01 |
| 1 | HASH GROUP BY | | 10 | 30 | 515 (4)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 1000K| 2929K| 498 (1)| 00:00:01 |
—————————————————————————

Statistics
———————————————————-
1975 recursive calls
30 db block gets
4167 consistent gets
1786 physical reads
5440 redo size
754 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
131 sorts (memory)
0 sorts (disk)
10 rows processed

SQL>

real time进行了query rewrite,且查到的数据是最新实时数据

SQL> select y as y_new_parse1, count(*) from t2
2 group by y;

Y_NEW_PARSE1 COUNT(*)
———— ———-
6 100000
4 100000
5 100000
8 100000
3 100000
7 100000
9 100000
1 100333
2 100333
0 100333

10 rows selected.

Execution Plan
———————————————————-
Plan hash value: 542978159

——————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————
| 0 | SELECT STATEMENT | | 12 | 312 | 22 (14)| 00:00:01 |
| 1 | VIEW | | 12 | 312 | 22 (14)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | VIEW | VW_FOJ_0 | 10 | 290 | 9 (12)| 00:00:01 |
|* 4 | HASH JOIN FULL OUTER | | 10 | 240 | 9 (12)| 00:00:01 |
| 5 | VIEW | | 1 | 7 | 6 (17)| 00:00:01 |
| 6 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
| 8 | VIEW | | 10 | 170 | 3 (0)| 00:00:01 |
| 9 | MAT_VIEW ACCESS FULL | MV_NEW | 10 | 60 | 3 (0)| 00:00:01 |
| 10 | VIEW | | 2 | 52 | 13 (16)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | FILTER | | | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 32 | 6 (17)| 00:00:01 |
| 14 | VIEW | | 1 | 26 | 6 (17)| 00:00:01 |
|* 15 | FILTER | | | | | |
| 16 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | 6 | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 35 | 7 (15)| 00:00:01 |
| 20 | VIEW | | 1 | 29 | 6 (17)| 00:00:01 |
| 21 | HASH GROUP BY | | 1 | 22 | 6 (17)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | MLOG$_T2 | 999 | 21978 | 5 (0)| 00:00:01 |
|* 23 | MAT_VIEW ACCESS BY INDEX ROWID| MV_NEW | 1 | 6 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_SNAP$_MV_NEW | 1 | | 0 (0)| 00:00:01 |
——————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

3 – filter(“AV$0″.”OJ_MARK” IS NULL)
4 – access(SYS_OP_MAP_NONNULL(“SNA$0″.”Y”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))
7 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
12 – filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL)
15 – filter(SUM(1)>0)
17 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
18 – access(“MV_NEW”.”SYS_NC00003$”(+)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))
22 – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-12 14:35:01’, ‘syyyy-mm-dd hh24:mi:ss’))
23 – filter(“MV_NEW”.”C1″+”AV$0″.”D0″>0)
24 – access(SYS_OP_MAP_NONNULL(“Y”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))

Note
—–
– dynamic statistics used: dynamic sampling (level=2)
– this is an adaptive plan

Statistics
———————————————————-
906 recursive calls
64 db block gets
1232 consistent gets
14 physical reads
10548 redo size
744 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
64 sorts (memory)
0 sorts (disk)
10 rows processed

对比结果还是比较清楚的。

Real time mv利用原来的已经stale的物化视图,结合mv log,通过计算后,帮你获取实时的数据。即能获得实时数据,又避免频繁的刷新mv。

12c以后 Real-time materialized view 实时物化视图的应用

Disabling real-time statistics gathering

Real-time Statistics
This Oracle Database 19c new feature is available on certain Oracle Database platforms. Check the Oracle Database Licensing Guide for more
information.
CHALLENGES TO MAINTAINING ACCURATE OPTIMIZER STATISTICS
As mentioned above, stale statistics can result in sub-optimal SQL execution plans and keeping them accurate in highly volatile
systems can be challenging. High-frequency statistics gathering helps to resolving this, but a more ideal solution would be to maintain
statistics as changes to the data in the database are made.

REAL-TIME STATISTICS
Real-time statistics extends statistic gathering techniques to the conventional DML operations INSERT, UPDATE and MERGE. When
these DML operations are executed on the data in the database, the most essential optimizer statistics are maintained in real time. This
applies both the individual row and bulk operations.

Real-time statistics augment those collected by the automatic statistics gathering job, high-frequency stats gathering or those gathered
manually using the DBMS_STATS API. An accurate picture of the data in the database is therefore maintained at all times, which
results in more optimal SQL execution plans.

Real-time statistics are managed automatically, and no intervention from the database administrator is required. Developers may
choose to disable online statistics gathering for individual SQL statements using the NO_GATHER_OPTIMIZER_STATISTICS hint.

Example:

SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ …

If you already have a well-established statistics gathering procedure or if for some other reason you want to disable automatic statistics
gathering for your main application schema, consider leaving it on for the dictionary tables. You can do so by changing the value of
AUTOSTATS_TARGET parameter to ORACLE instead of AUTO using DBMS_STATS.SET_GLOBAL_PREFS procedure.

 

exec dbms_stats.set_global_prefs(‘autostats_target’,‘oracle’)

 

SQL> begin
dbms_stats.set_global_prefs(‘autostats_target’,’oracle’);
end;
/

参考:
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-769E609D-0312-43A7-9581-3F3EACF10BA9
<<19c New Feature:Real-Time Statistics (文档 ID 2552657.1)>>

 

改特性建议关闭。

Oracle 19c Real-Time Statistics 关闭实时统计信息