Search

OakieTags

Who's online

There are currently 0 users and 30 guests online.

Recent comments

Oakies Blog Aggregator

Oracle University Instructors on the Cruise Ship

Oracle User Group Norway Annual ConferenceI’m really looking forward to speak at the Oracle User Group Norway Spring Seminar 2015, together with my dear colleague Joel Goodman! For sure it’s one of the highlights this year in terms of Oracle Events.

Joel will present about Oracle Automatic Parallel Execution on MAR-12, 6pm and about Oracle 12c Automatic Data Optimization and Heat Map on MAR-13, 9:30am

Yours sincerely will talk about The Data Guard Broker – Why it is recommended on MAR-12, 6pm and about The Recovery Area – Why it is recommended on MAR-13, 8:30am

The OUGN board has again gathered an amazing lineup of top-notch speakers for this event, so I will gladly take the opportunity to improve my knowledge :-)

Tagged: #ougn2015

Undocumented Application Engine Parameter: EnableAEMonitoring

Oracle Support Document 1640355.1: E-AE: Performance Degradation When Using Do Loop Action to Process Large Amount of Data on PT 8.52 & PT 8.53 describes a performance problem in Application Engine when a small but frequently executed loop.  Application Engine calls DBMS_APPLICATION_INFO to set MODULE and ACTION on v$session each time it goes round the loop.

However, Oracle Bug 10130415  Latch contention on "resmgr group change latch" acknowledges a problem in some versions of Oracle, but it is resolved in 11.2.0.3 and 12c.

A new parameter was introduced in PT8.52.23 and PT8.53.13 in the Database Options section of the process scheduler configuration file (psprcs.cfg).  EnableAEMonitor controls whether Application Engine calls DBMS_APPLICATION_INFO.  The default value for this parameter is 0.

The PeopleSoft support document does not reference the database bug report, but it seems reasonable to infer that the new parameter was introduced to work around the database bug.

This new parameter is not described in PeopleBooks.  It does appear in the delivered configuration files on at least 8.53.13.  However, it is not present in at least 8.54.05. Therefore, by default, Application Engine will not set the module and Action unless you add it to the configuration file.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">[Database Options]
;=========================================================================
; Database-specific configuration options
;=========================================================================

;DMK - added to enable DBMS_APPLICATION_INFO instrumentation
EnableAEMonitoring=1

Then the behaviour is that as it has been since 8.52, described in PeopleTools 8.52 Application Engine sets MODULE and ACTION.

My Recommendation 

I certainly think that you should add this parameter to all process scheduler configuration files at relevant PeopleTools version.  Unless you specifically have the problem described in the support note, I recommend that you also set the parameter to 1 as shown above. I have never seen the problem in affected database versions, and it is fixed in the terminal release of 11g.

Without setting the parameter, you will loose the ability to relate Enterprise Manager and ASH data to specific application engine steps.  If you need to make a code change to achieve a performance improvement you will have to go through the manual process of finding the SQL in an application engine trace.

In Memory XML Performance (XVM)

I wouldn’t believe the bad XMLType performance statement given stated in Martin Preiss’ blog post,…

Database Provisioning in Minutes: Using Enterprise Manager 12c DBaaS Snap Clone and NetApp Storage

Last week, I was letting you know about an opportunity to hear all about the Snap Clone functionality with EMC storage. This week, there’s a similar presentation on Wednesday, March 4 at 8AM PT (San Francisco), 11AM ET (New York), 4PM GMT (London) and 5PM CET (Paris), only this time we’re discussing Snap Clone with NetApp storage instead.

To register, go to this link.

RMOUG 2015 Training Days Review

I’m at HotSos Symposium 2015 speaking this week, so thought I would blog about the results of the conference I’m the director of and that finished up just two weeks ago.  I’m not admitting to being overwhelmed by OEM questions here, as I’m rather enjoying it.  I love seeing folks so into Enterprise Manager and look forward to more!  Keep the ideas for more blog posts coming!  I’ll write on all these great topics in upcoming posts.

Rocky Mountain Oracle User Group, (RMOUG) Training Days 2015 is over for another year, but the conference is a task that encompasses approximately 10-11 months of planning and anywhere from 120-200 hrs per year of volunteer work from my as the Training Days Director.  This in no way includes the countless hours that are contributed by Team YCC, our conference connection who helps us manage the conference or the invaluable volunteers from our membership that assist us with registration, driving speakers to and from the airport, as well as being ambassadors for our 120+  technical sessions.

Post each conference I am director for, I compile a ton of data that assists me in planning for the next year’s conference.  This starts immediately after the current year’s conference and comprises not only the feedback that is offered to me in the conference evaluations, but spoken and email feedback that attendees and speakers are kind enough to share with me.  I find this data crucial to my planning in the next year, but there is an important set of rules that are utilized to ensure I get the most from the data.  I manage the conference very similar to the way I manage database environments and data is king.  I’m going to share just a small bit of this data with you today, but it will give you an idea of the detail I get into when identifying the wins, the opportunities and the challenges for next year’s conference.

One of the major changes I had implemented to the evaluations was based off a conversation with many of the Oak Table members on values offered.  When given the opportunity to grade a venue, speaker or event on the following:

  • Very Satisfied
  • Satisfied
  • Unsatisfied

Reviewers were more inclined to choose “Satisfied” of the three options.  It was easy, didn’t demand a lot of thought into their choice and a choice of 1-10 values would result in more valuable data in my evaluations.  Being the glutton for punishment I am and finding logic in the conversation, I chose to update our evaluations to the 1-10 vs. the above choices or 1-5.

It’s been a very “interesting” and positive change.  Not only did it bring up our scoring from “Satisfied”, which was an average rating, to higher marks overall, but we received more constructive feedback that can be used to make the conference even better next year.

Colorado Convention Center

Although we are continually searching for the best venue for the conference, we receive positive feedback on the Colorado Convention Center.  Our attendees appreciate the central location, the opportunities to enjoy all the restaurants, entertainment and such in the downtown area.  The Colorado Convention Center offers us the world-class venue that a conference of our size deserves.  Our speakers find little challenge to gaining funding to travel and speak because of the location, too.

rmoug1

Notice that we don’t have any scores from 1-10 that are under 8!.  Our average score was 9.57, so pretty impressive.  That was also the overall average with how often they let us down on coffee/tea and other refreshments between sessions,(yeah, I’ll be talking to them about this, as I feel it’s very important to have during those breaks.)

Project O.W.L.

This was part of the new marketing initiative I put together this year for RMOUG.  New additions at conferences are always unnerving.  We had RAC Attack last year, but to create a new exhibitor area, new sponsorships and attendee participation opportunities, you hope every group will get what they need out of the initiative.  We added Rep Attack, (Replication from DBVisit), Clone Attack,(From Delphix) a hardware display from Oracle and a Stump the Expert panel from OTN, who also sponsored our RAC Attack area.

We did pretty well with Project O.W.L., (which stands for Oracle Without Limits) but we learned from our evaluations that our attendees really wanted all those “attack” opportunities on the first ½ day, during our deep dives and hands on labs.

rmoug2

The reviewers didn’t complain about any of the “Attack” sessions or hardware displays, but gave lower scores, (down to 6, on a scale of 1-10) due to scheduling changes they really wanted to see for this new event offering.

Length of Conference

We have offered a 2 ½ day conference for the last couple years, after having a 2 day conference and an additional hands on lab ½ day previously.  Last year we started offering a single day pass for those few that were unable to join us for the entire event. This offered us additional flexibility for our attendees and we noticed that only about 35 people take advantage of this, but it removed the challenge we had for those sharing badges, which impacted our “true attendance count” when working with the Colorado Convention Center on our next year’s contract and it also increased the amount of folks that asked for a longer conference:

rmoug3

Currently, 65% of our attendees who’ve filled out their evaluations, would like to extend the conference to a full three days.  I’ve also seen some benefits of separating the Hands on Labs/deep dive sessions by development vs. Database focused to get the most out of the three days.  This would mean that during the DBA sessions, we would have the development centric deep dives and HOL and vice-versa for the DBA deep dives/HOL.   This scheduling would allow us to add another track, which our current comments list an interest in DevOPS and VMWare or Hardware.

Amount of Sessions

We consistently have comments saying, “Not enough DB12c” and the next might say, “Too much DB12c”.  Below that is a comment asking for more development sessions, followed by someone asking for less development.  This is expected and actually tells me when I’m in my “sweet spot” of session scheduling.  Our tracks closely match our attendance designated roles, so we know we are doing well with our schedule.

Percentage of Sessions for Each Track

rmoug4

Roles that Attendees Hold

rmoug5

If you mix and match the session percentages that are in our conference vs. the roles that our attendees hold, you will see that we have an excellent balance of sessions that match the amount of those that will be interested in it.

Women Attending Training Days

The reason I started Women in Tech at RMOUG was that I did a count, (we don’t collect information on the gender of our attendees, but I can either tell by name or by knowing the person, which allows me to count about 97% of our attendees.) and was aware that we only had 7% attendance by women.  With the introduction of the WIT sessions, we have now increased our attendance to over 22%.

rmoug6

New Attendance

We do ask our attendees how many Training Days folks have attended.   I noted a number of folks that felt the people they’d always seen at the conference were no longer attending and we’d noticed that, as with many Oracle User Group conferences, the attendees are “aging out”.  Gaining new attendees through new Oracle customers, startups and new technologies is essential.  Knowing if you are successful is important, too.

rmoug7

Currently, 40% of our attendance have attended four or less Training Days, which tells us we are making head way in introducing our conference and RMOUG to the area.  We still are retaining 59% of our long-term attendees, (we do have some who have attended most of our 26 conferences, too!)  Keeping both groups satisfied are also a big challenge, so again, this data shows us that we are doing a very good job.

rmoug8

We had an average of 8.85 rating on session quality on a scale of 1-10 and most of the complaints were when anyone and I do mean anyone thought they could get away with marketing in their sessions.  No matter how often we let people know that marketing is very frowned upon by our attendees,  abstract reviewers offering low scores for any abstract that appears to have any marketing in their session, someone still tries to push the marketing card.  The session level evaluations won’t be out for a little while, but I already fear for those that were called out at the conference level for marketing or sales in their technical session and those were the ones that created a majority of the percentage of 7 scores.

Session Quality

We couldn’t  have the great speakers, topics and quality of sessions without our great abstract reviewers and committee.  We have around 50 reviewers, made up of local attendees,  ACEs and Oak Table Members.  This provides us with the best over all scoring.  We ask people to only review those tracks that they are knowledgeable in and to never review their own abstracts or those that may be considered a conflict of interest.  Even my own abstracts are submitted for review and then I pull all mine, knowing that I’ll be onsite and if I need a last minute replacement, it comes in handy to slip one of mine or Tim Gorman’s in, as we have a few that have been approved.  I’m commonly quite busy and prefer to give as many speakers an opportunity to speak, so I have no problem pulling mine from the schedule unless absolutely required.

rmoug9

We achieved an average scored of 9.57 on session quality out of a score 1-10, so this tells you just how effective our abstract review and selection process is.  I applaud and recognize our abstract reviewers and thank them for making my job so easy when it comes to, not only choosing our abstracts for our conference, but if someone asks why they weren’t selected, the scores and comments, (sans the reviewer names, those remain between the committee and myself) offer feedback to assist the speaker in how they might change their abstract submission in the future for a better chance of getting accepted.  We do receive over 300 abstracts per year and can only accept around 100, so we are forced to say no to 2/3 of our abstracts submitted.

Overall, our registration count was up for paying attendees, which is a rare thing for user group conferences.  Our number of volunteers also increased, (which is crucial to our conference success.) RMOUG is a non-profit that relies on the power of our great volunteer base.  These volunteers drive many of our speakers from and to the airport, register attendees and serve as ambassadors to each and every session.  Our exhibitor area was 40% larger than its been in previous years, which brings additional revenue that RMOUG depends on for Quarterly Education Workshops, Special interest Groups, the RMOUG Newsletter, SQL>Update and other yearly expenditures.  RMOUG  couldn’t survive without the contributions of so many different groups, community participation and sponsorship.  This user group is powerful because of its community and the support  deserves a round of applause for making another Training Days conference a success!



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [RMOUG 2015 Training Days Review], All Right Reserved. 2015.

Creating UKOUG Tech15 – The View from the Inside

At the end of last week I was contacted by the UKOUG who asked me if I would agree to be on the planning committee for the annual technical conference this year – Tech15. Not only that but I was privileged to be asked to repeat my role from Tech14 and be the Lead for the Database area. I am of course happy to do so.

UKOUG_Tech15 Banner
Why do I mention this? Well, this year I intend to share what is involved in helping to organise the content for an event like this, to give a view from the inside. This will mostly be by postings to my blog but also on twitter (@mdwidlake – see the little “twittering” section on the right margin of this page).

Most of the logistical work required to run the conference is done by the team at UKOUG. The UK Oracle user group is large enough that it has a small, dedicated team of paid staff – it needs to, pure voluntary efforts by people with day jobs simply could not run something that is the size of a small company. The office team, helped by the board-level volunteers like Debra Lilley and Fiona Martin, decide on and book the venue (I’m pleased to say that 2015 we are returning to the ICC in Birmingham after 2 years “holiday” in Manchester and Liverpool, and will be on the 7th-9th December – see the Tech15 announcement here) and deal with the hundreds of issues there, including catering. They of course run the registration system, the negotiations with sponsors and vendors wishing to participate, promotion of the event and all the other tasks that go with running any conference, be it I.T., politics, businesses or science fiction. We volunteers do not get involved with any of that, the office staff are highly proficient at such things. Also, that side of it is probably not of much interest to you lot so I won’t say much about it. I’ve helped with the logistical side for smaller events (Tech & Life Science conferences and, yes, a science fiction convention) and most of it is dull and very job-like.

We volunteers do the part that the office staff would struggle with, which is decide on the content. I’ll describe the process in a later blog or blogs but as we volunteers work with the technology we know the subject matter, what is current and coming and, between us what the audience is likely to be interested in. We also have input into decisions about how content is delivered and the things that surround it – the social events, the timing of the talks, any pre-event activities. Basically, aspects that will impact the attendee enjoyment are generally passed by us.

That is the part I’ll mostly try and share with you as we go through the process. For now, I’ll just mention that my friend Anthony Macey did this nice piece about being a volunteer for Tech 2014

Usually when I blog it is very obviously my opinion and no one else’s. I don’t feel the need to have that usual weaselly disclaimer to absolve others of any association with what I say. However, for postings about preparing UKOUG Tech15 I will be in some ways talking about the efforts and actions of others and could be seen as representing the UKOUG. I am not – all opinions and errors should be heaped on my shoulders alone. I did take the step of asking the UKOUG if they were happy for me to run this series of blogs and if they wanted oversight of the postings – they were good enough to say that they were happy for me to do it and that they would not require oversight. So I have their blessing but am a free agent.

If anyone has any questions about the conference and how it is organised, please feel free to get in touch. I can’t answer questions on everything, but if there is some aspect of how it is organised that your are curious about then please ask (so long as it is notwhy did my talk on blargh get turned down“).

12c Parallel Execution New Features: Concurrent UNION ALL - Part 1

12c introduces the concurrent UNION ALL operator that allows multiple branches below the UNION ALL to become active concurrently - which is a significant change. Before the introduction of this feature Oracle never executed multiple branches of an execution plan concurrently (in terms of Parallel Execution) - the parallelism so far was about executing the same operations of a particular branch of the execution plan by multiple processes / sessions. When we later look at the actual runtime behaviour of the new feature we'll notice that it's actually not that much different from previous behaviour than it sounds here.By default the feature gets automatically used when there is at least one branch of the UNION ALL making use of Parallel Execution features, like a parallel full table scan for example.If the UNION ALL operator consists of serial branches only the concurrent execution of those serial branches can be explicitly activated by using the PQ_CONCURRENT_UNION hint. To suppress the feature usage, a corresponding NO_PQ_CONCURRENT_UNION hint can be used. Both hints are officially documented.The concurrent execution of serial branches in my opinion could be particularly useful to overcome the still existing limitation of parallelism and operations via database links: If you want to copy larger segments of data via database links you can use Parallel Execution on the source to speed up data retrieval and you can use Parallel Execution on the target to speed up the DML process, but the data will always have to go through the query coordinator process on both sides, so a copy process is always limited by the CPU and network bandwidth / latency of a single process. If you need to copy multiple objects of similar size you could simply speed up the copy process via spawning multiple jobs, but if the objects are of significantly different size you would still have to wait for the single process to cope with the largest segment.Before 12c the only way to overcome this limitation was to employ "do-it-yourself" parallelism per (large) segment, more recently supported via DBMS_PARALLEL_EXECUTE. This allows spreading such an operation across several processes / sessions concurrently and therefore allowing to overcome the described limitation. But this approach makes the overall handling more complex (chunking, error handling etc., although DBMS_PARALLEL_EXECUTE provides a standardized interface for such tasks) and isn't a simple SQL based solution any longer.With the concurrent UNION ALL feature in principle a SQL solution should be possible where multiple branches of a concurrent UNION ALL query (different chunks) of the same remote segment concurrently. Of course this approach still requires some manual preparation since ideally the different branches should only query disjunct parts of the segment, so for non-partitioned segments ROWID ranges should be prepared and used inside the different remote queries, similar to the DBMS_PARALLEL_EXECUTE approach. If you don't care about the duplicated work you could of course simply use something like a ORA_HASH or MOD based filter on a column expression that comes up with a reasonably equal (hash) value distribution (like a primary key).However, due to the way the concurrent UNION ALL is implemented internally at present, this approach might not work very well, at least in 12.1.0.1 and 12.1.0.2, as I'll show in a later part.Some other questions I would like to address regarding this feature are the following:- How is the work distributed at runtime if you end up with a mixture of parallel and serial branches?- If you request concurrent execution with serial branches only and no other parallel execution in the plan, how is the parallel degree to be used determined?In this part of the blog post I want to start with the latter question first: What parallel degree gets used if a UNION ALL consists only of serial branches?And one follow-up question could be: What happens if a more complex execution plan mixes parts with Parallel Execution outside a UNION ALL with a concurrent UNION ALL that consists only of serial branches?Here is the table setup I'm going to use for all my tests:


-- This is the Parallel table
create table t_2
compress
as
select
rownum as id
, rpad('x', 100) as filler
from
(select /*+ cardinality(1e5) */ * from dual
connect by
level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_2')

alter table t_2 parallel 8;

-- This is the serial table
create table t2
compress
as
select
(rownum * 2) + 1 as id
, mod(rownum, 2000) + 1 as id2
, rpad('x', 100) as filler
from
(select /*+ cardinality(10000) */ * from dual
connect by
level <= 10000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't2')

create database link loop connect to cbo_test identified by cbo_test using '//localhost:1521/orcl12'

The database link will become relevant in the later parts of this series where I look at the execution of remote branches.So let's start with the question about the parallel degree used in case of serial branches only. I'll use the following simple query to determine that (repeat the query block inside the UNION ALL as many times as desired, I've used 20 serial branches):


-- Can I force a completely serial UNION ALL into concurrent execution using the hint? Yes, but isn't selected by the optimizer automatically
-- In 12.1.0.2 the PQ_CONCURRENT_UNION hint works as documented as statement level hint, whereas in 12.1.0.1 there is the bug that you need to specify a query block name (Bug 15851422 : PQ_CONCURRENT_HINT DOES NOT WORK WITHOUT QUERY BLOCK SPECIFICATION)
-- What about the degree chosen in such a case, is there an upper limit?
select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
.
.
.
select regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
);

I've performed all tests on 12.1.0.2.If you do an EXPLAIN PLAN on above query without the PQ_CONCURRENT_UNION hint, then you'll see, as documented, the default behaviour, which is serial execution of one branch after the other (I've used 20 serial branches in my tests):


-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | VIEW | |
| 3 | UNION-ALL | |
|* 4 | TABLE ACCESS FULL| T2 |
|* 5 | TABLE ACCESS FULL| T2 |
|* 6 | TABLE ACCESS FULL| T2 |
|* 7 | TABLE ACCESS FULL| T2 |
|* 8 | TABLE ACCESS FULL| T2 |
|* 9 | TABLE ACCESS FULL| T2 |
|* 10 | TABLE ACCESS FULL| T2 |
|* 11 | TABLE ACCESS FULL| T2 |
|* 12 | TABLE ACCESS FULL| T2 |
|* 13 | TABLE ACCESS FULL| T2 |
|* 14 | TABLE ACCESS FULL| T2 |
|* 15 | TABLE ACCESS FULL| T2 |
|* 16 | TABLE ACCESS FULL| T2 |
|* 17 | TABLE ACCESS FULL| T2 |
|* 18 | TABLE ACCESS FULL| T2 |
|* 19 | TABLE ACCESS FULL| T2 |
|* 20 | TABLE ACCESS FULL| T2 |
|* 21 | TABLE ACCESS FULL| T2 |
|* 22 | TABLE ACCESS FULL| T2 |
|* 23 | TABLE ACCESS FULL| T2 |
-------------------------------------

With the hint in place the plan changes to this:


-------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 5 | VIEW | | Q1,00 | PCWP | |
| 6 | UNION-ALL | | Q1,00 | PCWP | |
| 7 | PX SELECTOR | | Q1,00 | PCWP | |
|* 8 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 9 | PX SELECTOR | | Q1,00 | PCWP | |
|* 10 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 11 | PX SELECTOR | | Q1,00 | PCWP | |
|* 12 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 13 | PX SELECTOR | | Q1,00 | PCWP | |
|* 14 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 15 | PX SELECTOR | | Q1,00 | PCWP | |
|* 16 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 17 | PX SELECTOR | | Q1,00 | PCWP | |
|* 18 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 19 | PX SELECTOR | | Q1,00 | PCWP | |
|* 20 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 21 | PX SELECTOR | | Q1,00 | PCWP | |
|* 22 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 23 | PX SELECTOR | | Q1,00 | PCWP | |
|* 24 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 25 | PX SELECTOR | | Q1,00 | PCWP | |
|* 26 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 27 | PX SELECTOR | | Q1,00 | PCWP | |
|* 28 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 29 | PX SELECTOR | | Q1,00 | PCWP | |
|* 30 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 31 | PX SELECTOR | | Q1,00 | PCWP | |
|* 32 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 33 | PX SELECTOR | | Q1,00 | PCWP | |
|* 34 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 35 | PX SELECTOR | | Q1,00 | PCWP | |
|* 36 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 37 | PX SELECTOR | | Q1,00 | PCWP | |
|* 38 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 39 | PX SELECTOR | | Q1,00 | PCWP | |
|* 40 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 41 | PX SELECTOR | | Q1,00 | PCWP | |
|* 42 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 43 | PX SELECTOR | | Q1,00 | PCWP | |
|* 44 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 45 | PX SELECTOR | | Q1,00 | PCWP | |
|* 46 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
-------------------------------------------------------------------------

Starting with 12c you'll usually get a plan note about the parallel degree the optimizer has determined, for both the traditional DOP and the in 11.2 introduced Auto DOP way (for Auto DOP this note showed already up in 11.2). However, in this case, no note section shows up.So what degree do we then get at runtime? (XPLAN_ASH output snippet)


Real-Time SQL Monitoring Execution Summary
-----------------------------------------------

| |PX IS|PX |PX | |PX |PX |
| |CROSS|MIN |MAX |PX |SERVERS |SERVERS |
STATUS |USERNAME |INST |DOP |DOP |INSTANCES |REQUESTED |ALLOCATED |
-------------------|------------------------------|-----|----------|----------|----------|----------|----------|
EXECUTING |CBO_TEST |N | 20| 20| 1| 20| 20|
| | | | | | | |

So that's interesting, in my case I tested this on a laptop with 2 CPUs and Resource Manager active, which means that insane degrees should be downgraded automatically (or even already limited by the optimizer), but I really got a degree of 20, which doesn't make a lot of sense in this environment. However, in 12c obviously the limits enforced by the Resource Manager have been raised. In 11.2 the highest degree the Resource Manager allowed in this particular environment was 16, in 12c the limit was 27 instead (maybe 28, because in 11.2 I sometimes got 15, sometimes 16).So pretty straightforward the number of serial branches seem to determine the degree attempted to use at runtime. This also means you need to be careful how many branches you code into the UNION ALL if you want to make use of the concurrent feature.What happens if I mix now a Parallel Execution with a UNION ALL that consists only of serial branches, for example like this:


select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select id, regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t_2
where regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
) a,
(
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
) b
where a.id = b.id;

Again, if I don't use the PQ_CONCURRENT_UNION hint, I'll get the traditional plan shape for the UNION ALL - which gets executed serially and then gets distributed as part of the remaining parallel operations:


-------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | Q1,02 | PCWP | |
| 6 | BUFFER SORT | | Q1,02 | PCWC | |
| 7 | PX RECEIVE | | Q1,02 | PCWP | |
| 8 | PX SEND HYBRID HASH | :TQ10000 | | S->P | HYBRID HASH|
| 9 | STATISTICS COLLECTOR | | | | |
| 10 | VIEW | | | | |
| 11 | UNION-ALL | | | | |
|* 12 | TABLE ACCESS FULL | T2 | | | |
|* 13 | TABLE ACCESS FULL | T2 | | | |
|* 14 | TABLE ACCESS FULL | T2 | | | |
|* 15 | TABLE ACCESS FULL | T2 | | | |
|* 16 | TABLE ACCESS FULL | T2 | | | |
|* 17 | TABLE ACCESS FULL | T2 | | | |
|* 18 | TABLE ACCESS FULL | T2 | | | |
|* 19 | TABLE ACCESS FULL | T2 | | | |
|* 20 | TABLE ACCESS FULL | T2 | | | |
|* 21 | TABLE ACCESS FULL | T2 | | | |
| 22 | PX RECEIVE | | Q1,02 | PCWP | |
| 23 | PX SEND HYBRID HASH | :TQ10001 | Q1,01 | P->P | HYBRID HASH|
| 24 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
|* 25 | TABLE ACCESS FULL | T_2 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------

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

5 - access("ID"="B"."ID")
12 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
13 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
14 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
15 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
16 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
17 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
18 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
19 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
20 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
21 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
25 - filter( REGEXP_REPLACE ("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))

Note
-----
- Degree of Parallelism is 4 because of table property

Since I marked the table T_2 with PARALLEL 8 I would expect the overall plan to use a degree of 8, but look at the "note" section, which tells me the degree would be 4 due to a table property...What happens at runtime:


Real-Time SQL Monitoring Execution Summary
-----------------------------------------------

| |PX IS|PX |PX | |PX |PX |
| |CROSS|MIN |MAX |PX |SERVERS |SERVERS |
STATUS |USERNAME |INST |DOP |DOP |INSTANCES |REQUESTED |ALLOCATED |
-------------------|------------------------------|-----|----------|----------|----------|----------|----------|
EXECUTING |CBO_TEST |N | 8| 8| 1| 16| 16|
| | | | | | | |

But at runtime I get a degree of 8, so you have to be careful with these plan notes as they quite often seem to report incorrect degrees for the traditional DOP way.If I now use the PQ_CONCURRENT_UNION hint, I get the following plan:


------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | Q1,02 | PCWP | |
| 7 | PX SEND HYBRID HASH | :TQ10000 | Q1,00 | P->P | HYBRID HASH|
| 8 | STATISTICS COLLECTOR | | Q1,00 | PCWC | |
| 9 | VIEW | | Q1,00 | PCWP | |
| 10 | UNION-ALL | | Q1,00 | PCWP | |
| 11 | PX SELECTOR | | Q1,00 | PCWP | |
|* 12 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 13 | PX SELECTOR | | Q1,00 | PCWP | |
|* 14 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 15 | PX SELECTOR | | Q1,00 | PCWP | |
|* 16 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 17 | PX SELECTOR | | Q1,00 | PCWP | |
|* 18 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 19 | PX SELECTOR | | Q1,00 | PCWP | |
|* 20 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 21 | PX SELECTOR | | Q1,00 | PCWP | |
|* 22 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 23 | PX SELECTOR | | Q1,00 | PCWP | |
|* 24 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 25 | PX SELECTOR | | Q1,00 | PCWP | |
|* 26 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 27 | PX SELECTOR | | Q1,00 | PCWP | |
|* 28 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 29 | PX SELECTOR | | Q1,00 | PCWP | |
|* 30 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 31 | PX RECEIVE | | Q1,02 | PCWP | |
| 32 | PX SEND HYBRID HASH | :TQ10001 | Q1,01 | P->P | HYBRID HASH|
| 33 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
|* 34 | TABLE ACCESS FULL | T_2 | Q1,01 | PCWP | |
------------------------------------------------------------------------------

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

5 - access("ID"="B"."ID")
12 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
14 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
16 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
18 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
20 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
22 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
24 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
26 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
28 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
30 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
34 - filter( REGEXP_REPLACE ("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))

Note
-----
- Degree of Parallelism is 4 because of table property

OK, this is the concurrent UNION ALL shape now, but the "note" section still tells me the parallel degree is 4.What happens at runtime:


Real-Time SQL Monitoring Execution Summary
-----------------------------------------------

| |PX IS|PX |PX | |PX |PX |
| |CROSS|MIN |MAX |PX |SERVERS |SERVERS |
STATUS |USERNAME |INST |DOP |DOP |INSTANCES |REQUESTED |ALLOCATED |
-------------------|------------------------------|-----|----------|----------|----------|----------|----------|
EXECUTING |CBO_TEST |N | 10| 10| 1| 20| 20|
| | | | | | | |

Oops, at runtime I now get the degree determined by the PQ_CONCURRENT_UNION part, so the overall degree is then in my case 10, and not 4 (or 8 for that matter), since my UNION ALL had 10 serial branches in this example.So it becomes obvious that using the new feature with serial branches you have to worry a bit about the parallel degree used at runtime.In the next instalment we'll look at the actual runtime behaviour of the feature when it gets automatically triggered by a mixture of parallel and serial branches.

RMOUG’s First WIT Scholarship Winner

I’ve been running the Women in Technology, (WIT) at RMOUG, which I first started planning out in 2011, which has grown to include other user groups and even countries as its grown.  The last couple years, I’ve worked to try to add a WIT scholarship to RMOUG’s, but it wasn’t always easy to convince others that we should provide one when you are asking to choose one group over another.  There’s only so much money to go around and so many initiatives that we need to address each year for the Oracle community.

For 2015, we had a unique candidate submitted from our continual education scholarship, referred to as the Stan Yellott scholarship, after the wonderful RMOUG board member and much loved mentor who passed away in 2006.  The candidate, Natalie Kalin is from Pine Creek High School, out of Colorado Springs, which has students that attend our yearly conference, Training Days, each February.  She stood out, as not only had she chosen to major in robotics in College, but she was already in AP Robotics in high school and was then taking this education and providing STEM initiatives to Middle School students in her district.

NKalin

Due to her technical skills, initiative, stellar transcripts and submission to the scholarship, it was easy for the board of directors at RMOUG to recognize her and award a second scholarship for WIT.  I want to share the announcement from her local school district, along with recognize her accomplishment and thank her for making it so easy for me to create the WIT scholarship this year!

Thank you and congratulations, Natalie!

 



Tags:  


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [RMOUG's First WIT Scholarship Winner], All Right Reserved. 2015.

What happened to “when the application is fast enough to meet users’ requirements?”

On January 5, I received an email called “Video” from my friend and former employee Guđmundur Jósepsson from Iceland. His friends call him Gummi (rhymes with “who-me”). Gummi is the guy whose name is set in the ridiculous monospace font on page xxiv of Optimizing Oracle Performance, apparently because O’Reilly’s Linotype Birka font didn’t have the letter eth (đ) in it. Gummi once modestly teased me that this is what he is best known for. But I digress...

His email looked like this:

It’s a screen shot of frame 3:12 from my November 2014 video called “Why you need a profiler for Oracle.” At frame 3:12, I am answering the question of how you can know when you’re finished optimizing a given application function. Gummi’s question is, «Oi! What happened to “when the application is fast enough to meet users’ requirements?”»

Gummi noticed (the good ones will do that) that the video says something different than the thing he had heard me say for years. It’s a fair question. Why, in the video, have I said this new thing? It was not an accident.

When are you finished optimizing?

The question in focus is, “When are you finished optimizing?” Since 2003, I have actually used three different answers:

When are you are finished optimizing?
  1. When the cost of call reduction and latency reduction exceeds the cost of the performance you’re getting today.
    Source: Optimizing Oracle Performance (2003) pages 302–304.
  2. When the application is fast enough to meet your users’ requirements.
    Source: I have taught this in various courses, conferences, and consulting calls since 1999 or so.
  3. When there are no unnecessary calls, and the calls that remain run at hardware speed.
    Source: “Why you need a profiler for Oracle” (2014) frames 2:51–3:20.

My motive behind answers A and B was the idea that optimizing beyond what your business needs can be wasteful. I created these answers to deter people from misdirecting time and money toward perfecting something when those resources might be better invested improving something else. This idea was important, and it still is.

So, then, where did C come from? I’ll begin with a picture. The following figure allows you to plot the response time for a single application function, whatever “given function” you’re looking at. You could draw a similar figure for every application function on your system (although I wouldn’t suggest it).

Somewhere on this response time axis for your given function is the function’s actual response time. I haven’t marked that response time’s location specifically, but I know it’s in the blue zone, because at the bottom of the blue zone is the special response time RT. This value RT is the function’s top speed on the hardware you own today. Your function can’t go faster than this without upgrading something.

It so happens that this top speed is the speed at which your function will run if and only if (i) it contains no unnecessary calls and (ii) the calls that remain run at hardware speed. ...Which, of course, is the idea behind this new answer C.

Where, exactly, is your “requirement”?

Answer B (“When the application is fast enough to meet your users’ requirements”) requires that you know the users’ response time requirement for your function, so, next, let’s locate that value on our response time axis.

This is where the trouble begins. Most DBAs don’t know what their users’ response time requirements really are. Don’t despair, though; most users don’t either.

At banks, airlines, hospitals, telcos, and nuclear plants, you need strict service level agreements, so those businesses investment into quantifying them. But realize: quantifying all your functions’ response time requirements isn’t about a bunch of users sitting in a room arguing over which subjective speed limits sound the best. It’s about knowing your technological speed limits and understanding how close to those values your business needs to pay to be. It’s an expensive process. At some companies, it’s worth the effort; at most companies, it’s just not.

How about using, “well, nobody complains about it,” as all the evidence you need that a given function is meeting your users’ requirement? It’s how a lot of people do it. You might get away with doing it this way if your systems weren’t growing. But systems do grow. More data, more users, more application functions: these are all forms of growth, and you can probably measure every one of them happening where you’re sitting right now. All these forms of growth put you on a collision course with failing to meet your users’ response time requirements, whether you and your users know exactly what they are, or not.

In any event, if you don’t know exactly what your users’ response time requirements are, then you won’t be able to use “meets your users’ requirement” as your finish line that tells you when to stop optimizing. This very practical problem is the demise of answer B for most people.

Knowing your top speed

Even if you do know exactly what your users’ requirements are, it’s not enough. You need to know something more.

Imagine for a minute that you do know your users’ response time requirement for a given function, and let’s say that it’s this: “95% of executions of this function must complete within 5 seconds.” Now imagine that this morning when you started looking at the function, it would typically run for 10 seconds in your Oracle SQL Developer worksheet, but now after spending an hour or so with it, you have it down to where it runs pretty much every time in just 4 seconds. So, you’ve eliminated 60% of the function’s response time. That’s a pretty good day’s work, right? The question is, are you done? Or do you keep going?

Here is the reason that answer C is so important. You cannot responsibly answer whether you’re done without knowing that function’s top speed. Even if you know how fast people want it to run, you can’t know whether you’re finished without knowing how fast it can run.

Why? Imagine that 85% of those 4 seconds are consumed by Oracle enqueue, or latch, or log file sync calls, or by hundreds of parse calls, or 3,214 network round-trips to return 3,214 rows. If any of these things is the case, then no, you’re absolutely not done yet. If you were to allow some ridiculous code path like that to survive on a production system, you’d be diminishing the whole system’s effectiveness for everybody (even people who are running functions other than the one you’re fixing).

Now, sure, if there’s something else on the system that has a higher priority than finishing the fix on this function, then you should jump to it. But you should at least leave this function on your to-do list. Your analysis of the higher priority function might even reveal that this function’s inefficiencies are causing the higher-priority functions problems. Such can be the nature of inefficient code under conditions of high load.

On the other hand, if your function is running in 4 seconds and (i) its profile shows no unnecessary calls, and (ii) the calls that remain are running at hardware speeds, then you’ve reached a milestone:

  1. if your code meets your users’ requirement, then you’re done;
  2. otherwise, either you’ll have to reimagine how to implement the function, or you’ll have to upgrade your hardware (or both).

There’s that “users’ requirement” thing again. You see why it has to be there, right?

Well, here’s what most people do. They get their functions’ response times reasonably close to their top speeds (which, with good people, isn’t usually as expensive as it sounds), and then they worry about requirements only if those requirements are so important that it’s worth a project to quantify them. A requirement is usually considered really important if it’s close to your top speed or if it’s really expensive when you violate a service level requirement.

This strategy works reasonably well.

It is interesting to note here that knowing a function’s top speed is actually more important than knowing your users’ requirements for that function. A lot of companies can work just fine not knowing their users’ requirements, but without knowing your top speeds, you really are in the dark. A second observation that I find particularly amusing is this: not only is your top speed more important to know, your top speed is actually easier to compute than your users’ requirement (…if you have a profiler, which was my point in the video).

Better and easier is a good combination.

Tomorrow is important, too

When are you are finished optimizing?

  1. When the cost of call reduction and latency reduction exceeds the cost of the performance you’re getting today.
  2. When the application is fast enough to meet your users’ requirements.
  3. When there are no unnecessary calls, and the calls that remain run at hardware speed.

Answer A is still a pretty strong answer. Notice that it actually maps closely to answer C. Answer C’s prescription for “no unnecessary calls” yields answer A’s goal of call reduction, and answer C’s prescription for “calls that remain run at hardware speed” yields answer A’s goal of latency reduction. So, in a way, C is a more action-oriented version of A, but A goes further to combat the perfectionism trap with its emphasis on the cost of action versus the cost of inaction.

One thing I’ve grown to dislike about answer A, though, is its emphasis on today in “…exceeds the cost of the performance you’re getting today.” After years of experience with the question of when optimization is complete, I think that answer A under-emphasizes the importance of tomorrow. Unplanned tomorrows can quickly become ugly todays, and as important as tomorrow is to businesses and the people who run them, it’s even more important to another community: database application developers.

Subjective goals are treacherous for developers

Many developers have no way to test, today, the true production response time behavior of their code, which they won’t learn until tomorrow. ...And perhaps only until some remote, distant tomorrow.

Imagine you’re a developer using 100-row tables on your desktop to test code that will access 100,000,000,000-row tables on your production server. Or maybe you’re testing your code’s performance only in isolation from other workload. Both of these are problems; they’re procedural mistakes, but they are everyday real-life for many developers. When this is how you develop, telling you that “your users’ response time requirement is n seconds” accidentally implies that you are finished optimizing when your query finishes in less than n seconds on your no-load system of 100-row test tables.

If you are a developer writing high-risk code—and any code that will touch huge database segments in production is high-risk code—then of course you must aim for the “no unnecessary calls” part of the top speed target. And you must aim for the “and the calls that remain run at hardware speed” part, too, but you won’t be able to measure your progress against that goal until you have access to full data volumes and full user workloads.

Notice that to do both of these things, you must have access to full data volumes and full user workloads in your development environment. To build high-performance applications, you must do full data volume testing and full user workload testing in each of your functional development iterations.

This is where agile development methods yield a huge advantage: agile methods provide a project structure that encourages full performance testing for each new product function as it is developed. Contrast this with the terrible project planning approach of putting all your performance testing at the end of your project, when it’s too late to actually fix anything (if there’s even enough budget left over by then to do any testing at all). If you want a high-performance application with great performance diagnostics, then performance instrumentation should be an important part of your feedback for each development iteration of each new function you create.

My answer

So, when are you finished optimizing?

  1. When the cost of call reduction and latency reduction exceeds the cost of the performance you’re getting today.
  2. When the application is fast enough to meet your users’ requirements.
  3. When there are no unnecessary calls and the calls that remain run at hardware speed.

There is some merit in all three answers, but as Dave Ensor taught me inside Oracle many years ago, the correct answer is C. Answer A specifically restricts your scope of concern to today, which is especially dangerous for developers. Answer B permits you to promote horrifically bad code, unhindered, into production, where it can hurt the performance of every function on the system. Answers&nnbsp;A and B both presume that you know information that you probably don’t know and that you may not need to know. Answer C is my favorite answer because it is tells you exactly when you’re done, using units you can measure and that you should be measuring.

Answer C is usually a tougher standard than answer A or B, and when it’s not, it is the best possible standard you can meet without upgrading or redesigning something. In light of this “tougher standard” kind of talk, it is still important to understand that what is optimal from a software engineering perspective is not always optimal from a business perspective. The term optimized must ultimately be judged within the constraints of what the business chooses to pay for. In the spirit of answer A, you can still make the decision not to optimize all your code to the last picosecond of its potential. How perfect you make your code should be a business decision. That decision should be informed by facts, and these facts should include knowledge of your code’s top speed.

Thank you, Guđmundur Jósepsson, of Iceland, for your question. Thank you for waiting patiently for several weeks while I struggled putting these thoughts into words.

Friday Philosophy – The Problem of Positive Discrimination?

Have you ever (or are you currently) working in an organisation with any Positive Discrimination policies? Where, for example, there is a stated aim to have 25% of the board as female or 30% of the workforce from ethnic groups that are not of the majority ethnic group in your geographic location? How do you feel about that? Is positive discrimination a good thing or a bad thing? I can’t decide.

{Big Caveat! Before anyone wants to give me the same sort of hassle as a tiny few did recently over a related post, note that I am just wondering aloud and whilst I encourage comments and feedback, I reserve the right to block or delete any comments that I feel are abusive or discriminatory or simply from the unhinged. Just saying. Also I am mostly going to reference women as the aim for positive discrimination, as the blog got really untidy when I swapped between different types of discrimination. I apologise if anyone is offended by that – it is not intended.}

I don’t think I’ve ever been comfortable with the concept of positive discrimination and if I wind back the clock to my early 20’s, back then I was quite angrily dead set against it – on the grounds that it is still discrimination. It seemed to me then that it was a simple yin/yang concept. If discrimination is wrong, it’s wrong and “positive” discrimination is in fact just discrimination against the majority. Wrong is wrong. Stealing is wrong, be it from the poor or the rich or from organisations. All those post-it notes I’ve stolen over the years? Bad Martin.

So what has changed about my opinion? Well, I think that as we all get older we tend to be able to better consider the wider picture and less black/white about most of our philosophies {my personal opinion is that those who don’t modify their opinions in light of more experience and greater thought are, well, not maturing}. I can’t but accept that the business/IT work place as a whole is male-dominated and is riddled with sexism. This does not mean *at all* that all or even most men in business/IT are sexist, but the statistics, studies and countless personal experiences make it clear that the pay, success and respect of women are impacted.
A way to counteract that is to encourage more women to work in IT (or science or whichever area they are under-represented in) and show that they are just as effective in senior positions by tipping the balance in their favor. Positive discrimination is one way of doing that. Is the small evil of this type of discrimination acceptable if it first counteracts and then helps overturn and melt the large evil of the massive inequalities we currently have? Once equality is there (or you are at least approaching it) you drop the little evil of positive discrimination? But how else do you balance the books until the issue has been addressed? My own perception is that sexism and racism at least are reduced from what they were when I first started working, maybe positive discrimination is a significant factor in that? Maybe it is more that society has shifted?

Part of me likes the Women In Technology {try search on hashtag #WIT but you get loads of things that are labelled as “witty” as well} events and discussions such as supported in the Oracle sphere by Kellyn PotVin-Gorman and Debra Lilley amongst others. I much prefer to have a balanced workforce. But when I’ve been to a talk about it or seen online discussions, there often seems to be an element of “we hate men” or “all men are out to put us down” that, frankly, insults me. In fairness I’ve also seen that element questioned or stopped by the female moderators so I know they are aware of the problem of Men Bashing. After all, for reasons I have gone into in a prior post, as a small man I empathise with some of their issues – so to be told all men are the problem is both personally an affront and also… Yes, it’s discrimination. I should not have to feel I need to justify my own non-sexism but I do – My work, hiring and promoting history demonstrates I treat both sexes as equal. If I think you are rubbish at your job, it has nothing to do with how many X chromosomes you have.

I mentioned above “the little evil of positive discrimination” and that is certainly how I see it. I think of it as wrong not just because of the yin/yang simplistic take on right and wrong but because positive discrimination can have negative effects. Forcing a percentage of the workforce or management to be from a specified group means you are potentially not hiring the best candidates or putting the less capable into those positions. If your workforce is 10% female, not at all unusual in IT, then it is unlikely the best candidates for management are 25% female. They might be, it might be that 40% of them are female as they have managed to demonstrate their capabilities and stick with the industry despite any extra challenges faced. But to have a false percentage strikes me as problematic. Another issue is that of perceived unfair advantage or protection. How would any of us feel if we did not get a job or position as someone else got it on the basis of their sex, colour or disability to fulfill a quota? People are often bad tempered enough when they fail to get what they want. Over all, I think positive discrimination leads to a level of unease or resentment in the larger group not being aided. NOTE – I mean on average. I do not mean that everyone (or even most) feels resentment. And those who do vary in how much each individual feels upset by it.

I know a few people, including myself, who have hit big problems when disciplining or even sacking someone who is not a white male. I’ve had HR say to me “we are going to have to be very careful with this as they are {not-white-male}”. I asked the direct question of would this be easier if the person was a white male? – And they said, frankly, yes. It’s hard not to let that get your back up. I’ve seen this make someone I felt was pretty liberal and balanced become quite bigoted. That is positive discrimination being a little evil and having exactly the opposite effect as intended. That HR department was, in my opinion, getting it wrong – but I’ve heard so many similar stories that I feel it is the same in most HR departments across the UK, US and maybe Europe too. I can’t speak about other places.

I know a few women who are also very uncomfortable with positive discrimination as it makes them feel that either they got something not on the basis of their own abilities or others see it that way from looking in.

I’ve occasionally seen the disparity in numbers seen as a positive – I knew a lady at college who loved the fact she was only one of 3 women out of just over a hundred people in her year doing a degree in Computer Science. I was chatting to her {at a Sci-fi society evening, where she was also markedly out-numbered by the opposite sex} about how it must be daunting. She laughed at me in scorn – It was great! She said she stuck out and so got better responses when she asked questions in lectures, she had no trouble getting help off the over-worked tutors as they were keen to be seen to not be discriminatory and, as you mostly “met people” via your course or your societies, she pretty much had her pick of a hundred+ men. That told me.

So all in all, I still do not know if I am for or against positive discrimination. I guess I just wish it was not necessary. If there really was no discrimination, we would not question how many female, black, asian, disabled, short, fat, ginger, protestant people there were doing whatever we do.

{sorry for the lack of humour this week, I just struggled to squeeze it into such a delicate topic}