A Minor Parallel Execution Problem

We have a problem on one of the data warehouse databases at work. There is an overnight resource profile in place that is designed to stop any users from running long-running queries while other activities are happening. The main limit is on logical_reads_per_call, for example (and this is just a simple example I’ve been playing with at home)

SQL> alter profile default limit logical_reads_per_call 10000;

Profile altered.

Now I’ll connect to the testuser account and look at the table I’m going to test this with – DUDE (why it’s called that should become clearer when I blog about DUDE soon)

SQL> select table_name, blocks from user_tables where table_name = 'DUDE';

TABLE_NAME                         BLOCKS
------------------------------ ----------
DUDE                                25431

So you can see that the table DUDE consists of 25431 blocks. Therefore, if I try to perform a full table scan of it, I’ll hit the limit.

SQL> set autotrace trace statistics
SQL> select count(*) from dude;
select count(*) from dude
*
ERROR at line 1:
ORA-02395: exceeded call limit on IO usage

But, if I try the same query using parallel execution, it doesn’t exceed the limit.

SQL> select /*+ parallel(dude, 2) */ count(*) from dude;

Statistics
----------------------------------------------------------
       27  recursive calls
        3  db block gets
    25537  consistent gets
    25403  physical reads
      672  redo size
      414  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        1  sorts (memory)
        0  sorts (disk)
        1  rows processed

And even if I reduce the limit to 500 :-

SQL> connect / as sysdba
Connected.
SQL> alter profile default limit logical_reads_per_call 500;

Profile altered.

SQL> connect testuser/testuser
Connected.
SQL> set autotrace trace statistics
SQL> select /*+ parallel(dude, 2) */ count(*) from dude;

Statistics
----------------------------------------------------------
        6  recursive calls
        0  db block gets
    25536  consistent gets
    25403  physical reads
        0  redo size
      414  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        1  sorts (memory)
        0  sorts (disk)
        1  rows processed

There are only two slaves in use, each of which is clearly reading more than 500 blocks, but I assume that because they’re background processes, their statistics aren’t included.

I’m still digging around with this, but I’m interested in hearing anyone else’s experiences because this is causing us a problem because just when we want to limit system load, some of the most resource-hungry SQL statements are being executed and not limited, because they’re using PX. I can think of several solutions to this, but it would appear that the existing approach isn’t going to work.

Additional info
I missed this off the original post. At work I was using x$ksuru to try to work out what was going on. These are the results I’m getting locally, which show that the original slaves are exceeding the limit that’s still in place. (Note that this is on a bigger table, though, to give me more chance to monitor the changes)

select * from x$ksuru
where addr IN (select saddr from v$px_session where qcsid = &parent)
and ksurind = 4

SQL> /
Enter value for parent: 1073
old   2: where addr IN (select saddr from v$px_session where qcsid = &parent)
new   2: where addr IN (select saddr from v$px_session where qcsid = 1073)

ADDR           INDX    INST_ID   KSUSEIDL    KSURIND    KSURUSE
-------- ---------- ---------- ---------- ---------- ----------
7D0532C4       1069          1          1          4      50090
7D057D94       1073          1          1          4         26
7D05DB18       1078          1          1          4      49302

SQL> /
Enter value for parent: 1073
old   2: where addr IN (select saddr from v$px_session where qcsid = &parent)
new   2: where addr IN (select saddr from v$px_session where qcsid = 1073)

ADDR           INDX    INST_ID   KSUSEIDL    KSURIND    KSURUSE
-------- ---------- ---------- ---------- ---------- ----------
7D0532C4       1069          1          1          4      55499
7D057D94       1073          1          1          4         26
7D05DB18       1078          1          1          4      54508