并行 parallel 受PROFILE 的影响各个版本不同

TEST CASE:

========

conn sys as sysdba

alter system set resource_limit=true;
CREATE PROFILE support_profile LIMIT SESSIONS_PER_USER 6;
alter user <USERNAME>  profile support_profile;

conn <USERNAME>/<PASSWORD>

SQL> alter session set "_px_trace"="high","compilation","high","execution","messaging","time";

Session altered.

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 10';

Session altered.

SQL> select /*+ PARALLEL(8) monitor*/ count(*) from <TABLE NAME>;

COUNT(*)
----------
918843

SQL> SET LONG 1000000
SQL> SET LONGCHUNKSIZE 1000000
SQL> SET LINESIZE 1000
SQL> SET PAGESIZE 0
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => NULL, type => 'TEXT',report_level=>'ALL') AS report FROM dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(8) monitor*/ count(*) from sales

Parallel Execution Details (DOP=8 , Servers Allocated=8)

------------

 SET LONG 1000000
 SET LONGCHUNKSIZE 1000000
  SET LINESIZE 1000
 SET PAGESIZE 0
  SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => NULL, type => 'TEXT',report_level=>'ALL') AS report FROM dual;

Database profile is used to set SESSION_PER_USER to 6. As a result you cannot have more than 6 sessions. If you try to open 7 sessions you get and ORA-02391 error.

This also causes that an SQL may not get the requested number of parallel processes.

CHANGES

CAUSE

Expected behaviour since Bug 5910422 is fixed.

SOLUTION

This is the expected behaviour in 10.2.0.4 and above (where Bug 5910422 is fixed). The sessions_per_user limit is enforced when the parallel workers are starting in the session.

The following demonstrates this behaviour on one session. Obviously if more than one sessions are open and parallel statement is running, its DOP will be reduced accordingly.

The example demonstrates the following steps on a query that has parallel plan with 1 worker group having 1 worker set. The query is requested to run with dop=8 by statement level parallel hint.

  • No profile limit set, and the query runs with DOP 8, having 8 parallel workers.
  • Then profile LIMIT SESSIONS_PER_USER is set to 6. Oracle wants to create 8 parallel workers, before the 8 parallel workers would join the QC, it recognizes SESSIONS_PER_USER is set to 6, so it releases 3 workers, and end up with 5 workers + the QC.

This demonstration was done on single instance. It may apply to RAC but the meaning of SESSIONS_PER_USER profile limit in multi instance environment is out of the scope of this article.

STEPS


conn sh
select /*+ PARALLEL(8) monitor*/ count(*) from T1;
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => NULL, type => 'TEXT',report_level=>'ALL') AS report FROM dual;

-- enable resource plan and resource limit
conn sys as sysdba

alter system set resource_limit=true;
CREATE PROFILE support_profile LIMIT SESSIONS_PER_USER 6;
alter user sh profile support_profile;

conn sh
alter session set "_px_trace"="high","compilation","high","execution","messaging","time";
ALTER SESSION SET EVENTS '10053 trace name context forever, level 10';
select /*+ PARALLEL(8) monitor*/ count(*) from T1;
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => NULL, type => 'TEXT',report_level=>'ALL') AS report FROM dual;


-- clean up
conn sys as sysdba
drop PROFILE support_profile cascade;
alter system set resource_limit=false;



ACTUAL RESULT
 

SH@dw23> select /*+ PARALLEL(8) monitor*/ count(*) from T1;

  COUNT(*)
----------
    783327

SH@dw23> SET LONG 1000000
SH@dw23> SET LONGCHUNKSIZE 1000000
SH@dw23> SET LINESIZE 1000
SH@dw23> SET PAGESIZE 0
SH@dw23> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => NULL, type => 'TEXT',report_level=>'ALL') AS report FROM dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(8) monitor*/ count(*) from T1

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SH (11:3833)
 SQL ID              :  0md7m3v2sh21s
 SQL Execution ID    :  16777216
 Execution Started   :  02/20/2014 11:53:14
 First Refresh Time  :  02/20/2014 11:53:14
 Last Refresh Time   :  02/20/2014 11:53:15
 Duration            :  1s
 Module/Action       :  SQL*Plus/-
 Service             :  ABCD.xx.xyz.com
 Program             :  sqlplus.exe
 Fetch Calls         :  1

Global Stats
=========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes |
=========================================================================================
|    5.40 |    0.16 |     3.73 |        0.21 |     1.30 |     1 |   2065 |  318 |  11MB |
=========================================================================================

Parallel Execution Details (DOP=8 , Servers Allocated=8)
==================================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  |         Wait Events         |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes |         (sample #)          |
==================================================================================================================================================
| PX Coordinator | QC    |         |    0.79 |    0.01 |     0.24 |        0.21 |     0.33 |     58 |   18 | 144KB | db file parallel read (1)   |
| p000           | Set 1 |       1 |    0.59 |    0.02 |     0.46 |             |     0.11 |    281 |   33 |   2MB | db file sequential read (1) |
| p001           | Set 1 |       2 |    0.59 |    0.02 |     0.42 |             |     0.14 |    258 |   54 |   2MB | direct path read (1)        |
| p002           | Set 1 |       3 |    0.58 |    0.02 |     0.41 |             |     0.15 |    418 |   63 |   1MB | direct path read (1)        |
| p003           | Set 1 |       4 |    0.56 |    0.01 |     0.42 |             |     0.13 |    204 |   28 | 968KB | direct path read (1)        |
| p004           | Set 1 |       5 |    0.56 |    0.04 |     0.39 |             |     0.14 |    286 |   38 |   1MB | db file sequential read (1) |
| p005           | Set 1 |       6 |    0.58 |    0.02 |     0.46 |             |     0.10 |    181 |   26 |   1MB | direct path read (1)        |
| p006           | Set 1 |       7 |    0.56 |    0.02 |     0.48 |             |     0.07 |    203 |   32 |   1MB | direct path read (1)        |
| p007           | Set 1 |       8 |    0.58 |    0.01 |     0.45 |             |     0.12 |    176 |   26 |   1MB | db file sequential read (1) |
==================================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1311612494)
===============================================================================================================================================================
| Id |         Operation          |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |       Activity Detail       |
|    |                            |          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |         (# samples)         |
===============================================================================================================================================================
|  0 | SELECT STATEMENT           |          |         |      |         1 |     +1 |     1 |        1 |      |       |          |                             |
|  1 |   SORT AGGREGATE           |          |       1 |      |         1 |     +1 |     1 |        1 |      |       |          |                             |
|  2 |    PX COORDINATOR          |          |         |      |         2 |     +0 |     9 |        8 |   18 | 144KB |    11.11 | db file parallel read (1)   |
|  3 |     PX SEND QC (RANDOM)    | :TQ10000 |       1 |      |         1 |     +1 |     8 |        8 |      |       |          |                             |
|  4 |      SORT AGGREGATE        |          |       1 |      |         1 |     +1 |     8 |        8 |      |       |          |                             |
|  5 |       VIEW                 | VW_TE_2  |    611K |   62 |         1 |     +1 |     8 |     783K |      |       |          |                             |
|  6 |        UNION-ALL           |          |         |      |         1 |     +1 |     8 |     783K |      |       |          |                             |
|  7 |         PX BLOCK ITERATOR  |          |    501K |   54 |         1 |     +1 |     8 |     672K |      |       |          |                             |
|  8 |          TABLE ACCESS FULL | T1|    501K |   54 |         1 |     +1 |   103 |     672K |  103 |   9MB |    55.56 | direct path read (5)        |
|  9 |         PX BLOCK ITERATOR  |          |    109K |    9 |         1 |     +1 |     8 |     111K |      |       |          |                             |
| 10 |          TABLE ACCESS FULL | T1|    109K |    9 |         1 |     +1 |   197 |     111K |  197 |   2MB |    33.33 | db file sequential read (3) |
===============================================================================================================================================================


SH@ABCD> conn sys/manager1@ABCD as sysdba
Connected.

SYS@ABCD> alter system set resource_limit=true;

System altered.

SYS@ABCD> CREATE PROFILE support_profile LIMIT SESSIONS_PER_USER 6;

Profile created.

SYS@ABCD> alter user sh profile  support_profile;

User altered.

SYS@ABCD> conn sh/sh@ABCD
Connected.
SH@ABCD> alter session set "_px_trace"="high","compilation","high","execution","messaging","time";

Session altered.

SH@ABCD> ALTER SESSION SET EVENTS '10053 trace name context forever, level 10';

Session altered.

SH@dw23> select /*+ PARALLEL(8) monitor*/ count(*) from sales;

  COUNT(*)
----------
    783327

SH@ABCD> SET LONG 1000000
SH@ABCD> SET LONGCHUNKSIZE 1000000
SH@ABCD> SET LINESIZE 1000
SH@ABCD> SET PAGESIZE 0
SH@ABCD> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => NULL, type => 'TEXT',report_level=>'ALL') AS report FROM dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(8) monitor*/ count(*) from T1

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SH (11:3841)
 SQL ID              :  0md7m3v2sh21s
 SQL Execution ID    :  16777218
 Execution Started   :  02/20/2014 12:04:31
 First Refresh Time  :  02/20/2014 12:04:31
 Last Refresh Time   :  02/20/2014 12:04:32
 Duration            :  1s
 Module/Action       :  SQL*Plus/-
 Service             :  ABCD.xx.xyz.com
 Program             :  sqlplus.exe
 DOP Downgrade       :  38%
 Fetch Calls         :  1

Global Stats
=========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes |
=========================================================================================
|    0.91 |    0.40 |     0.00 |        0.23 |     0.28 |     1 |   1618 |   62 |   9MB |
=========================================================================================

Parallel Execution Details (DOP=5 , Servers Requested=8 , Servers Allocated=5)
==================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  | Wait Events |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | (sample #)  |
==================================================================================================================================
| PX Coordinator | QC    |         |    0.35 |    0.06 |          |        0.23 |     0.06 |     58 |      |     . |             |
| p000           | Set 1 |       1 |    0.10 |    0.06 |     0.00 |             |     0.04 |    395 |   16 |   2MB |             |
| p001           | Set 1 |       2 |    0.10 |    0.10 |     0.00 |             |     0.01 |    409 |   15 |   2MB |             |
| p002           | Set 1 |       3 |    0.14 |    0.06 |     0.00 |             |     0.07 |    281 |   10 |   2MB |             |
| p003           | Set 1 |       4 |    0.12 |    0.09 |     0.00 |             |     0.03 |    329 |   15 |   2MB |             |
| p005           | Set 1 |       5 |    0.10 |    0.03 |     0.00 |             |     0.07 |    146 |    6 | 872KB |             |
==================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1311612494)
===================================================================================================================================================
| Id |         Operation          |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                            |          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
===================================================================================================================================================
|  0 | SELECT STATEMENT           |          |         |      |         1 |     +1 |     1 |        1 |      |       |          |                 |
|  1 |   SORT AGGREGATE           |          |       1 |      |         1 |     +1 |     1 |        1 |      |       |          |                 |
|  2 |    PX COORDINATOR          |          |         |      |         1 |     +1 |     6 |        5 |      |       |          |                 |
|  3 |     PX SEND QC (RANDOM)    | :TQ10000 |       1 |      |         1 |     +1 |     5 |        5 |      |       |          |                 |
|  4 |      SORT AGGREGATE        |          |       1 |      |         1 |     +1 |     5 |        5 |      |       |          |                 |
|  5 |       VIEW                 | VW_TE_2  |    611K |   62 |         1 |     +1 |     5 |     783K |      |       |          |                 |
|  6 |        UNION-ALL           |          |         |      |         1 |     +1 |     5 |     783K |      |       |          |                 |
|  7 |         PX BLOCK ITERATOR  |          |    501K |   54 |         1 |     +1 |     5 |     672K |      |       |          |                 |
|  8 |          TABLE ACCESS FULL | T1|    501K |   54 |         1 |     +1 |    62 |     672K |   62 |   9MB |          |                 |
|  9 |         PX BLOCK ITERATOR  |          |    109K |    9 |         1 |     +1 |     5 |     111K |      |       |          |                 |
| 10 |          TABLE ACCESS FULL | T1|    109K |    9 |         1 |     +1 |    50 |     111K |      |       |          |                 |
===================================================================================================================================================


PX_TRACE FILE SHOWS THE FOLLOWING

Oracle wants to create 8 parallel workers, before the 8 parallel workers would join the QC,
it recognizes SESSIONS_PER_USER 日志中没有显示 is set to 6, so it releases 3 workers, and end up with 5 workers + the QC. 

kxfrAllocSlaves [ 70/ 0]
  DOP trace -- call kxfpgsg to get 8 workers
2014-02-20 12:04:32.414436 :PX_Messaging:kxfp.c@9813:kxfpgsg(begin):
reqthreads=8 height=0 lsize=0 alloc_flg=0x234
2014-02-20 12:04:32.414557 :PX_Messaging:kxfp.c@9886:kxfpgsg():
reqthreads=8 KXFPTHIS/KXFPLDBL/KXFPADPT/ load balancing=on adaptive=on
2014-02-20 12:04:32.414588 :PX_Messaging:kxfp.c@18418:kxfpiinfo():
inst [cpus:mxslv:#pg]
1 [4 :270 :1 ]
(default: 0) inst target is 270
number of active slaves on the instance: 0,
number of active slaves but available to use: 0
2014-02-20 12:04:32.415645 :PX_Messaging:kxfp.c@18742:kxfpclinfo():
inst(load :user:pct:fact:servtarget:queued:started:granted:active:active(free)): aff
1 (0.00:0 :100:400 :270 :0 :0 :0 :0 :0 )
defDOP=8, tpc=2
2014-02-20 12:04:32.415750 :PX_Messaging:kxfp.c@17831:kxfpLoadAdDOP():
granted dop: 8
load adapt num servers requested to = 8 (from kxfpAdaptDOP())
2014-02-20 12:04:32.415798 :PX_Messaging:kxfp.c@2149:kxfpqialo():
Allocate new q for dp=(nil)
new q=0x951b6f30 dp=(nil) pnum=65535 numa#=0 qser=0 done.
2014-02-20 12:04:32.415880 :PX_Messaging:kxfp.c@10255:kxfpgsg():
getting 1 sets of 8 threads, client parallel query execution flg=0x234
Height=8, Affinity List Size=0, inst_total=1, coord=1
Insts: 1
Threads: 8
getting 8 slaves (8 required) for q=0x951b6f30 qser=3073
...
2014-02-20 12:04:32.663551 :PX_Messaging:kxfp.c@3782:kxfpqsrls():
Release Slave q=0x951b6f30 qr=0x951b4f48 action=1 server=4 inst=1
...
2014-02-20 12:04:32.663853 :PX_Messaging:kxfp.c@3782:kxfpqsrls():
Release Slave q=0x951b6f30 qr=0x951b5758 action=1 server=6 inst=1
...
2014-02-20 12:04:32.664908 :PX_Messaging:kxfp.c@3782:kxfpqsrls():
Release Slave q=0x951b6f30 qr=0x951b4488 action=1 server=7 inst=1
...
2014-02-20 12:04:32.664939 :PX_Messaging:kxfp.c@11410:kxfpg1sg():
got 5 servers (sync), errors=0x0 returning
Acquired 5 slaves on 1 instances avg height=5 #set=1 qser=3073
P000 inst 1 spid 19450
P001 inst 1 spid 19452
P002 inst 1 spid 19454
P003 inst 1 spid 19456
P005 inst 1 spid 19458 

2024-08-25 13:53:29.465 :PX_Messaging:kxfp.c@13629:kxfpg1sg():
        got 4 servers (sync), errors=0x0 returning
2024-08-25 13:53:29.465 :PX_Messaging:kxfp.c@12144:kxfpgsg():   threads reduced from 8 to 4 due to not enough slaves
reason_code = 354, reason: DOP downgrade due to insufficient number of processes
        Acquired 4 slaves on 1 instances avg height=4 #set=1 qser=6657
                P006 set 1 inst 1 spid 22426
                P001 set 1 inst 1 spid 6258
                P004 set 1 inst 1 spid 22422
                P003 set 1 inst 1 spid 6632
2024-08-25 13:53:29.465 :PX_Messaging:kxfp.c@12430:kxfpgsg():

  • 17
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值