Oakies Blog Aggregator

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

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.

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.

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:

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

Roles that Attendees Hold

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%.

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.

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.

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.

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:  Conferences, oracle, RMOUG

Del.icio.us

Facebook

TweetThis

Digg

StumbleUpon

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.

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 R_T. This value R_T 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.