{"id":793,"date":"2006-05-03T12:00:00","date_gmt":"2006-05-03T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=793"},"modified":"2006-05-03T12:00:00","modified_gmt":"2006-05-03T12:00:00","slug":"a-minor-parallel-execution-problem","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/05\/03\/a-minor-parallel-execution-problem\/","title":{"rendered":"A Minor Parallel Execution Problem"},"content":{"rendered":"

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)<\/p>\n

SQL> alter profile default limit logical_reads_per_call 10000;
<\/p>Profile altered.<\/pre>\n
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)<\/p>\n
SQL> select table_name, blocks from user_tables where table_name = 'DUDE';
<\/p>TABLE_NAME                         BLOCKS------------------------------ ----------DUDE                                25431<\/pre>\n
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.<\/p>\n
SQL> set autotrace trace statisticsSQL> select count(*) from dude;select count(*) from dude*ERROR at line 1:ORA-02395: exceeded call limit on IO usage<\/pre>\n
But, if I try the same query using parallel execution, it doesn’t exceed the limit.<\/p>\n
SQL> select \/*+ parallel(dude, 2) *\/ count(*) from dude;
<\/p>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<\/pre>\n
And even if I reduce the limit to 500 :-<\/p>\n
SQL> connect \/ as sysdbaConnected.SQL> alter profile default limit logical_reads_per_call 500;
<\/p>Profile altered.
<\/p>SQL> connect testuser\/testuserConnected.SQL> set autotrace trace statisticsSQL> select \/*+ parallel(dude, 2) *\/ count(*) from dude;
<\/p>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<\/pre>\n
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.<\/p>\n
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.<\/p>\n
Additional info
<\/span>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)<\/p>\n
<\/p>\n
select * from x$ksuruwhere addr IN (select saddr from v$px_session where qcsid = &parent)and ksurind = 4
<\/p>SQL> \/Enter value for parent: 1073old   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)
<\/p>ADDR           INDX    INST_ID   KSUSEIDL    KSURIND    KSURUSE-------- ---------- ---------- ---------- ---------- ----------7D0532C4       1069          1          1          4      500907D057D94       1073          1          1          4         267D05DB18       1078          1          1          4      49302
<\/p>SQL> \/Enter value for parent: 1073old   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)
<\/p>ADDR           INDX    INST_ID   KSUSEIDL    KSURIND    KSURUSE-------- ---------- ---------- ---------- ---------- ----------7D0532C4       1069          1          1          4      554997D057D94       1073          1          1          4         267D05DB18       1078          1          1          4      54508<\/pre>\n","protected":false},"excerpt":{"rendered":"
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… Continue reading A Minor Parallel Execution Problem<\/span><\/a><\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-793","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1000,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/18\/saving-optimiser-stats-10g\/","url_meta":{"origin":793,"position":0},"title":"Saving Optimiser Stats – 10g","date":"June 18, 2006","format":false,"excerpt":"In my previous blog I showed how you can save your current optimiser stats into a seperate table whenever you refresh them, so that they can be restored should the new statistics lead to poor SQL execution plans. Oracle have added an improved version of this facility to 10g (which\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1017,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/13\/alter-table-move-and-table-stats\/","url_meta":{"origin":793,"position":1},"title":"alter table … move and table stats","date":"July 13, 2006","format":false,"excerpt":"Howard Rogers left a comment on my last blog, showing an example of using alter table ... move on a 10gR2 database on Linux. In his example, unlike mine, the table rebuild did not nullify the table's statistics. I admit I was surprised myself when I ran my example yesterday\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1019,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/14\/alter-table-move-and-table-stats-part-ii\/","url_meta":{"origin":793,"position":2},"title":"alter table … move and table stats (part II)","date":"July 14, 2006","format":false,"excerpt":"Following up on another comment from Howard, I took the initrans change off the alter table ... move so that it was the most basic variation and then ran it on 8i, 9i and 10g. I've trimmed lots of the output this time, but I have the log files if\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1016,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/12\/table-reorgs-and-statistics\/","url_meta":{"origin":793,"position":3},"title":"Table reorgs and statistics","date":"July 12, 2006","format":false,"excerpt":"While working on the ITL deadlock problem (which looks like it's been fixed by the initrans increase and table rebuild), the developers highlighted another table as hitting this problem in the past. When I investigated, I found that initrans had already been set to 6 so this had obviously happened\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1568,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/28\/statistics-on-partitioned-tables-part-4\/","url_meta":{"origin":793,"position":4},"title":"Statistics on Partitioned Tables – Part 4","date":"February 28, 2010","format":false,"excerpt":"In the last post I illustrated the problems you can run into when you rely on Oracle to aggregate statistics on partitions or subpartitions to generate estimated Global Statistics at higher levels of the table. Until there are statistics for all of the relevant structures then aggregation won't take place\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1570,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/28\/statistics-on-partitioned-tables-part-5\/","url_meta":{"origin":793,"position":5},"title":"Statistics on Partitioned Tables – Part 5","date":"February 28, 2010","format":false,"excerpt":"Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle. The examples used to date have been based on INSERTing new rows into subpartitions and, although that's the approach used for some of our tables and will suit some\u2026","rel":"","context":"With 18 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/793","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=793"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/793\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=793"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=793"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=793"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}