ANBOB™

最近在19C(19.8)的ORACLE数据库上遇到了ORA-00600 [qesrLoopOverSetRowP:rows]错误，解决方法中提到了禁用rowsets ，“rowsets”这是一个新的 Oracle 12c 特性, 是SQL层执行计划相关的内部优化，上周刚整理了Row source statistics执行计划的统计信息，关于rowsets的信息非常少，在Mike Dietrich’s Blog几年前描述过:

“Rowsets is a SQL execution performance enhancement introduced in Oracle RDBMS release 12.1 and further extended in a future release of the Oracle Database.

Prior to 12.1, data processing in the SQL layer were done on a row by row basis. That is, each row source in the row source tree performing a specific SQL operation (scan, join aggregation, window function, connect by etc) consume and produce one row at a time. Starting in 12.1, we introduced the concept of rowsets, where many row sources were enhanced to consume and produce rows in batches called rowsets. It is an internal performance optimization project. That is why there isn’t much documentation.

This is a fundamental change in the SQL layer row processing. The benefits of rowsets is not only instruction cache locality but it also enables other performance optimizations such as, vectorization and prefetch in row sources and expression evaluation.”

在12.1 中存在wrong result的bug,  解决办法同样是禁用rowsets或使用event 10055禁用特定的使用场景。如果在12.1中是建议禁用rowsets, 但毕竟是一个优化项，19c 版本也做了扩展建议保持启用，对于19c ORA-00600 [qesrLoopOverSetRowP:rows]解决方法是

Disable rowsets.
alter system set “_rowsets_enabled”=FALSE scope=both sid=’*’;

– OR –

A more targeted workaround that should have less/minimal performance impact is to set event 10123 to level 4.
“alter session set events ‘10123 trace name context forever, level 4’ ”

我尝试在19c中尝试一下，哪里体现rowsets。

SQL> set feedback on sql_id
SQL> set transaction read only;

Transaction set.

SQL_ID: 9hb4u1cj9x90h
SQL> set arraysize 4

SQL> select * from anbob.test1 t1 ,anbob.test1 t2 where t1.id=t2.id and  t1.id=1000;

        ID         ID
---------- ----------
      1000       1000

1 row selected.

SQL_ID: 9fz9rw2tt9zjh

SQL> select * from table(dbms_xplan.display_cursor('9fz9rw2tt9zjh',0,'-note +projection'));

PLAN_TABLE_OUTPUT
------------------------------------------------
SQL_ID  9fz9rw2tt9zjh, child number 0
-------------------------------------
select * from anbob.test1 t1 ,anbob.test1 t2 where t1.id=t2.id and
t1.id=1000

Plan hash value: 2587486242

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |    16 (100)|          |
|*  1 |  HASH JOIN         |       |     1 |     8 |    16   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TEST1 |     1 |     4 |     8   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST1 |     1 |     4 |     8   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."ID")
   2 - filter("T1"."ID"=1000)
   3 - filter("T2"."ID"=1000)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1; rowset=256) "T1"."ID"[NUMBER,22], "T2"."ID"[NUMBER,22]
   2 - (rowset=256) "T1"."ID"[NUMBER,22]
   3 - (rowset=256) "T2"."ID"[NUMBER,22]

30 rows selected.
SQL_ID: 7c27fm7ck0hmc

注意：
在Column Projection Information信息中出现了rowset的关键字。

关闭该特性在12c中有event 10055, 对应不对的级别，18c增加了更多的级别，同时增加了新的event 10123.

[oracle@oel7db1 ~]$ oerr ora 10055
10055, 00000, "Rowsets: turn off rowsets for various operations"
// *Document: NO
// *Cause:    N/A
// *Action:   Turns off rowsets for various operations
//            Level:
//            0x00000001 - turn off for table scan
//            0x00000002 - turn off for hash join consume
//            0x00000004 - turn off for hash join produce
//            0x00000008 - turn off for group by
//            0x00000010 - turn off for sort
//            0x00000020 - turn off for table-queue out
//            0x00000040 - turn off for table-queue in
//            0x00000080 - turn off for identity
//            0x00000100 - turn off for granule iterator
//            0x00000200 - turn off for EVA functions
//            0x00000400 - turn off for PL/SQL
//            0x00000800 - turn off for upgrade
//            0x00001000 - turn off for database startup
//            0x00002000 - turn off for blobs and clobs
//            0x00004000 - turn off for tracing row source
//            0x00008000 - turn off rowset information in explain plan
//            0x00010000 - disable hash join rowsets fast path
//            0x00020000 - turn off for bloom create
//            0x00040000 - turn off for bloom use
//            0x00080000 - disable prefetch for hash join
//            0x00100000 - disable prefetch for bloom
//            0x00200000 - disable semi blocking hash join
//            0x00400000 - turn off rowset for fixed table
//  12.2 adds 2 new levels
//            0x00800000 - turn off rowset for view
//            0x01000000 - turn off rowset for filter
//  18.3 moves to event 10123 and adds 7 new levels 
//            0x02000000 - disable producing rowsets in scan with one-row eva
//            0x04000000 - turn off rowsets for non-inner hash join
//            0x08000000 - turn off rowsets for update
//            0x10000000 - turn off rowsets for delete
//            0x20000000 - disable prefetch for hash aggregation
//            0x40000000 - turn off rowsets for window function
//            0x80000000 - turn off rowsets for count

[oracle@oel7db1 ~]$ oerr ora 10123
10123, 00000, "Rowsets: turn off rowsets for various operations"
// *Document: NO
// *Cause:    N/A
// *Action:   Turns off rowsets for various operations
//            Level:
//            0x00000001 - turn off for top select
//

打赏

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