{"id":826,"date":"2006-03-26T12:00:00","date_gmt":"2006-03-26T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=826"},"modified":"2006-03-26T12:00:00","modified_gmt":"2006-03-26T12:00:00","slug":"yapt","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/03\/26\/yapt\/","title":{"rendered":"YAPT"},"content":{"rendered":"

Yet Another PX Test (if only to give readers something different to comment on)<\/p>\n

Jonathan Lewis had suggested larger extents and freelist segment space management (instead of ASSM) could benefit the PX tests I’ve been running. I’m pretty sure it was Jeff Moss that suggested bigger blocks would be more appropriate for the type of Data Warehouse environment that would be most likely to use PX. I tried all three in combination on the E10K last week and was very impressed by the improvement. Instead of 8Kb blocks and 1Mb extents, I used 32Kb blocks and 32Mb extents. Let’s try a quick table this time instead of a graph 😉 <\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
DOP<\/td>\n8K \/ 1M<\/td>\n32K \/ 32M<\/td>\n%<\/td>\n<\/tr>\n
1<\/td>\n49:56.1<\/td>\n40:54.0<\/td>\n82<\/td>\n<\/tr>\n
2<\/td>\n21:34.5<\/td>\n18:31.0<\/td>\n86<\/td>\n<\/tr>\n
3<\/td>\n20:09.5<\/td>\n15:36.6<\/td>\n77<\/td>\n<\/tr>\n
4<\/td>\n16:25.6<\/td>\n12:16.7<\/td>\n75<\/td>\n<\/tr>\n
5<\/td>\n15:18.5<\/td>\n12:20.1<\/td>\n81<\/td>\n<\/tr>\n
6<\/td>\n14:33.0<\/td>\n11:31.5<\/td>\n79<\/td>\n<\/tr>\n
7<\/td>\n14:17.8<\/td>\n11:06.5<\/td>\n78<\/td>\n<\/tr>\n
8<\/td>\n14:20.7<\/td>\n11:17.3<\/td>\n79<\/td>\n<\/tr>\n
9<\/td>\n13:43.0<\/td>\n12:04.8<\/td>\n88<\/td>\n<\/tr>\n
10<\/td>\n13:23.9<\/td>\n09:53.7<\/td>\n74<\/td>\n<\/tr>\n
11<\/td>\n13:51.0<\/td>\n12:24.8<\/td>\n90<\/td>\n<\/tr>\n
12<\/td>\n13:21.3<\/td>\n11:41.2<\/td>\n88<\/td>\n<\/tr>\n
16<\/td>\n13:38.1<\/td>\n11:24.0<\/td>\n84<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Using just the Hash Join example and only testing DOPs of between 1 and 16, there’s an improvement of about 10-25%. I knew there would be some improvement but was quite surprised just how much. Of course, this is just on this particular test on this particular configuration (as I keep saying) and I should have tested each change individually but my time is pretty limited at the moment because we have a big go-live month at work. I think it does indicate how important the space allocation method is when processing large volumes of data quickly with PX – those percentage improvements would be pretty noticeable to the users.<\/p>\n","protected":false},"excerpt":{"rendered":"

Yet Another PX Test (if only to give readers something different to comment on) Jonathan Lewis had suggested larger extents and freelist segment space management (instead of ASSM) could benefit the PX tests I’ve been running. I’m pretty sure it was Jeff Moss that suggested bigger blocks would be more appropriate for the type of… Continue reading YAPT<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-826","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":951,"url":"http:\/\/orcldoug.com\/blog\/2005\/08\/19\/px-article-and-select-magazine\/","url_meta":{"origin":826,"position":0},"title":"PX article and Select magazine","date":"August 19, 2005","format":false,"excerpt":"Good news from Select (the IOUG magazine). I mentioned a couple of postings go that they\ufffdd printed an incorrect version of the PX article. Jonathan Lewis suggested that I should contact them to make sure a correction was printed. Although the next issue is already in layout (only just after\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":835,"url":"http:\/\/orcldoug.com\/blog\/2006\/03\/14\/px-and-the-magic-of-2\/","url_meta":{"origin":826,"position":1},"title":"PX and “the Magic of 2”","date":"March 14, 2006","format":false,"excerpt":"Now that I've had time to sit back and relax and absorb things a little, I thought I should fix a couple of outright bugs in the recent PX paper (Later update - link to the paper) and revisit the conclusions before moving on to testing different aspects. First, the\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1112,"url":"http:\/\/orcldoug.com\/blog\/2009\/10\/29\/10g-consolidated-trace-files-and-px\/","url_meta":{"origin":826,"position":2},"title":"10g Consolidated Trace Files and PX","date":"October 29, 2009","format":false,"excerpt":"At the end of my Tracing Parallel Execution presentation at the Scottish OUG conference, Michael M\u00f8ller of Miracle asked whether the consolidated trace files produced by the DBMS_MONITOR package and trcsess utility are in strict time order. i.e. Will I see the actions of the query coordinator, then the actions\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":949,"url":"http:\/\/orcldoug.com\/blog\/2005\/08\/21\/px-article-and-select-magazine-part-2\/","url_meta":{"origin":826,"position":3},"title":"PX article and Select magazine (part 2)","date":"August 21, 2005","format":false,"excerpt":"I've uploaded a document outlining the changes between the various online versions of the px article and the one published in Select Journal. It's available as a PDF or Word document. I've also asked the editors if they would include a link to this document in the next edition of\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":976,"url":"http:\/\/orcldoug.com\/blog\/2005\/05\/29\/px-slave-trace-files\/","url_meta":{"origin":826,"position":4},"title":"PX Slave Trace Files","date":"May 29, 2005","format":false,"excerpt":"It's sad that it takes a Bank Holiday weekend for me to get round to doing some more research on parallel execution. Anyway, I was on the verge of modifying the following line from the PX paper ...The trace files for the slave processes may be created in background_dump_dest, rather\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1319,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g\/","url_meta":{"origin":826,"position":5},"title":"Parallel Query and 11g","date":"September 23, 2007","format":false,"excerpt":"I've been playing around with 11g on and off this week, re-running some of the tests in this document (PDF file).The main edits required to the scripts were simple ones to correct the locations of trace files, for example (where $ORACLE_BASE is \/ora on the 10g system) rm \/ora\/admin\/TEST1020\/udump\/*.trc and\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/826","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"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=826"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/826\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=826"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=826"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=826"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}