Wednesday, September 24, 2014

Top 7 Reasons Why Oracle Conferences Rock!

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

Top 7 Reasons Why Oracle Conferences Rock!


Why take the time and make the effort to attend an Oracle database conference or Oracle user group meeting? We're all busy, so there had better be some super good reasons to make the effort! For me, the benefits definitely exceed the cost.

There are many different conferences to choose from. There are professional conferences, leadership conferences, scientific conferences and business focused conferences. So why an Oracle Database conference? What's the big deal?

This is difficult for me to explain, so I've summarized why I love Oracle conferences into a "Top 7" list.

Number 7. Get free stuff from vendors

I suspect 50% of Oracle DBAs attend conferences because they love getting free stuff. If you want to maximize the good stuff, timing is everything. Get to the booth when the exhibition hall first opens, because vendors have tons of stuff to give away and they are full of energy. Also, just before the exhibition hall closes on the final day show up again. Vendors are tired and want to get rid of as much stuff as possible... and you just happen to be there!

Number 6. Become known in your industry

If you want to focus and excel in your career in a particular area, then pass on what you know. If you hold on to what you learn, no one will know you're an expert but you... and that's lonely. One of the best ways to become known and enjoy conferences is to speak at them. If this is something you want to do, please email me. I'll mentor you. I'm serious.

Number 5. Talk to others, if you want to

Want to talk with people who care about Oracle technology as much as you do? Conferences are a great way to break out of your rut and think... and if you want to talk. One of the reasons I like conferences is it gives me a chance to meet with past students.

Number 4. Learn, if you want to

There are always opportunities to learn at conferences. How many times have I heard someone say they were in a lame session. But when I ask if they read the abstract first, they say no. If you want to increase your chances of attending great sessions, read the abstract. Second, look for speakers you like. Third, sit next to the door just in case you need to make a quick exit! The worst you can do is look at the agenda and pick the sessions you want to attend. Do a little research and you'll be surprised how many good sessions there are.


Number 3. Be inspired!

How many places can you go to receive inspiration? Not many, is my answer! Because I can chill for a bit at conferences, observe what's going on in our industry and interact with a wide variety of people associated with Oracle technology, I tend to leave with a fresher and more refined view... or better said, "A Wider View." That in itself is worth the cost of any conference.

Number 2. Network with vendors, speakers, colleagues

I take it one step at a time. I always cruise the exhibition halls looking for new products and touching base with the vendors I know. Personally, I like to encourage the new vendors because it is a massive investment for them to exhibit. When I'm cruising, that's where I typically reconnect with students and other DBAs. It's a more comfortable and relaxing environment for me. If you want to speak with an expert face-to-face, a conference is a natural place to do this. If someone walks up to me with an AWR report, I'll make time for them. If they email me the week before, I'll do whatever I can to schedule some time with them.

Number 1. Be with friends

This one is personal. When I'm teaching or consulting, it's very intense and compressed. But at conferences, I get a chance to unwind, sit back, have a beer, and talk. I'm really involved with the IOUG conference and each year I look forward to reconnecting with this small group of friends. It's such a good feeling to be with "Oracle friends" without having to talk... about Oracle. I can just sit and listen without anyone wondering... What's wrong with Craig? You can do the same thing. Find an Oracle user group and volunteer.

Why Attend? Because Oracle conferences rock!

There you have it. Seven reasons why Oracle conferences and Oracle User Groups rock! If you're not planning to attend any, you're missing a great way to connect, advance your career, and have a lot of fun. See you there!!

All the best in your Oracle performance work!

Craig.




Monday, September 15, 2014

Top 7 Reasons Why Oracle Conferences Are A Waste Of Time

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

Top 7 Reasons Why Oracle Conferences Are A Waste Of Time


Want to turn a lame Oracle Database conference experience into a great one? You may not believe this, but I think Oracle conferences are a waste of time. That is, unless I take action. I've been to hundreds of Oracle conferences, so I'm kind of an expert in this field.

Here is my "Top 7" list about why Oracle conferences are time suckers AND how you can turn them into a GREAT educational, social and networking experience.

Number 7. Sleeper Presentations. You forgot to read that session abstract before you sat down? You're not alone! Here my secret: Sit by the door. But when you walk out, remember that the speaker probably
knows how you feel but is doing they best they can. Out of respect for them and the poor souls who are actually enjoying the session, be quiet when you leave.

Number 6. My System Is Bigger Than Your System. How many times have we all heard someone talking about their, "50 node RAC global system with 5 million active users." Really? Is that even possible? Here are four proven options. Option 1 is to ask a question to shut them up. For example, "So how do you deal with the enqueue contention?" Another option is to simply walk away. If you feel that's rude, then Option 3 is to suddenly grab your phone to answer "that" call and walk away...never to return. If you're feeling feisty then combining Option 2 and 3 is wonderful. Ask the question "So how do you deal with upgrading 50 nodes?" AND THEN suddenly grab your phone and walk away.

Number 5. Not Another New Feature Presentation! Oracle Corporation and their marketing minions love to talk about futures. If you're depressed from hearing about new features you won't get to touch for the next five years, here's what to do. First, remember that Oracle Corporation user group sponsorship is important to user group conferences. Without the help from Oracle, most conferences would not occur. Second, the more Oracle sponsors an event, the more they influence the content. The money source always drives the content. The solution is to look at number of presentations given by non-Oracle employees versus Oracle employees... before you register.

Number 4. Can't Afford It Unless Oracle Sales Pays. Yes you can! I know training and traveling budgets have been cut. What you need is a free pass, right? I figured this one out while still working for Oracle. Submit an abstract to speak. If speaking freaks you out, click HERE. If you won't speak, then volunteer. Believe me when I say, EVERY CONFERENCE NEEDS VOLUNTEERS.

Number 3. Boring Conference. I totally get that. Want to know the secret about boring conferences? Avoid them...unless you take action. My conference of choice is Collaborate/IOUG. I love that conference... because I'm really involved, have been for years and look forward to working with the conference team and seeing them each year. It's kind of like family to me.

Number 2. I Feel Like A Dork. Because I speak a lot, people don't realize that much of the time I feel like a social idiot. Strange, but put me on stage or in a mentoring situation and I'm fine. It's taken me 20 years of conferences to figure this one out. Here's what to do: Go with a colleague or volunteer so you'll always have someone to hang out with. If that doesn't work and you're a guy, look for the stupid games because that's where you'll find DBAs just like you (and me).

Number 1. Stupid Drunks. I get so tired of drunk people yelling in my face! Especially when I can feel the spit from their mouths poke me in the eye. It only took a few years to figure this one out. The solution? Step back a couple of feet. And if that doesn't work, then walk away. Don't worry about offending them, because they won't remember you anyways.

Make It A Great Conference!

There you have it, my "Top 7" list about why Oracle conferences are time suckers AND how you can turn them into a GREAT educational, social and networking experience.

See you at IOUG this April in Las Vegas!

All the best in your Oracle performance work,

Craig.

Sunday, September 7, 2014

Watch Oracle DB Elapsed Time and Wall Time With Parallel Query

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

Watch Oracle Elapsed Time and Wall Time With Parallel Query


In my recent postings I wrote that when using the Oracle Database parallel query a SQL statement's wall time should be equal to its elapsed time divided by the number of parallel query slaves plus some overhead.

That may seem correct, but is it really true? To check I ran an experiment and posted the results here. The results are both obvious and illuminating.

If you don't want to read but just sit on the couch, have a beer and watch TV you're in luck! I took a clip from my Tuning Oracle Using An AWR Report online video seminar put it on youtube.  You can watch the video clip on YouTube HERE or simply click on the movie below.


The Math, For Review Purposes


In my previous recent postings I detailed the key time parameters; DB Time, DB CPU, non-idle wait time, elapsed time, parallelism and effective parallelism. To save you some clicking, the key parameters and their relationships are shown below.

DB Time = DB CPU + NIWT

Elapsed Time = Sum of DB Time

Wall Time = ( Elapsed Time / Parallelism ) + Parallelism Overhead

Wall Time = Elapsed Time / Effective Parallelism


Test Results: When Oracle Parallel Query was NOT involved.


If you want to see my notes, snippets, etc. they can be found in this text file HERE.

Here is the non-parallel SQL statement.

select /*+  FULL(big2) NOPARALLEL (big2) */ count(*)
into   i_var
from   big2 
where  rownum < 9000000

When the SQL statement was running, I was monitoring the session using my Realtime Session Sampler OSM tool, rss.sql. Since I knew the server process session ID and wanted to sample every second and wanted to see everything just for this session, this is the rss.sql syntax:
SQL>@rss.sql 16 16 827 827 % % 1
For details on any OSM tool syntax, run the OSM menu script, osmi.sql. You can download my OSM Toolkit HERE.

The rss.sql tool output is written to a text file, which I was doing a "tail -f" on. Here is a very small snippet of the output. The columns are sample number, sample time, session SID, session serial#, Oracle username, CPU or WAIT, SQL_ID, OraPub wait category, wait event, [p1,p2,p3].


We can see the session is consuming CPU and waiting. When waiting, the wait event is "direct path read", which is asynchronous (we hope) block read requests to the IO subsystem that will NOT be buffered in the Oracle buffer cache.

Now for the timing results, which are shown in the below table. I took five samples.  It's VERY important to know that the wait time (WAIT_TIME_S), DB CPU (DB_CPU_S), and DB Time (DB_TIME_S) values are related to ONLY server process SID 16. In blazing contrast, the wall time (WALL_S), elapsed time (EL_VSQL_S), and SQL statement CPU consumption (CPU_VSQL_S) is related the entire SQL_ID statement execution.

Here are the "no parallel" experimental results.
SQL> select * from op_results;

 SAMPLE_NO     WALL_S  EL_VSQL_S CPU_VSQL_S WAIT_TIME_S   DB_CPU_S  DB_TIME_S
---------- ---------- ---------- ---------- ----------- ---------- ----------
         1  35.480252  35.470015   9.764407       24.97   9.428506  34.152294
         2  35.670021  35.659748   9.778554       25.15   9.774984  35.541861
         3  35.749926  35.739473   9.774375       25.12    9.31266  34.126285
         4  35.868076  35.857752   9.772321       25.32   9.345398  34.273479
         5  36.193062   36.18378   9.712962       25.46   9.548465  35.499693
Let's check the math. For simplicity and clarity, please allow me to round and use only sample 5.
DB_TIME_S = DB_CPU_S + WAIT_TIME_S
     35.5 =   9.5    +    25.5  = 35.0
The DB Time is pretty close (35.5 vs 35.0). Close enough to demonstrate the time statistic relationships.
Elapsed Time (EL_VSQL_S) = DB_TIME_S
                    35.5 =    34.2
The Elapsed Time is off by around 4% (35.5 vs 34.2), but still closely to demonstrate the time statistic relationships.
Wall Time (WALL_S) = Elapsed Time (EL_VSQL_S) / Effective Parallelism
             35.5  =       35.5               /         1
Nice! The Wall Time results matched perfectly. (35.5 vs 35.5)

To summarize in a non parallel query (i.e., single server process) situation, the time math results are what we expected! (and hoped for)

Test Results: When Oracle Parallel Query WAS involved.


The only difference in the "non parallel" SQL statement above and the SQL statement below is the parallel hint. Below is the "parallel" SQL statement.
select /*+  FULL(big2) PARALLEL(big2,3)  */ count(*) into i_var from big2 where rownum<9000000>
When the "parallel" SQL statement was running, because Oracle parallel query was involved resulting in multiple related Oracle sessions, when monitoring using my rss.sql tool, I need to open the session ID (and serial#) to include all sessions. I still sampled every second. Here is the rss.sql syntax:
SQL>@rss.sql 0 9999 0 9999 % % 1
The tool output is written to a text file, which I was doing a "tail -f" on. Here is a very small snippet of the output. I manually inserted the blank lines to make it easier to see the different sample periods.


There is only one SQL statement being run on this idle test system. And because there is no DML involved, we don't see much background process activity. If you look closely above, sessions 168 (see third column) must be a log write process because the wait event is "log file parallel write". I checked and session 6 is a background process as well.

It's no surprise to typically see only four session involved. One session is the parallel query coordinator and the three parallel query slaves! Interestingly, the main server process session that I executed the query from is session number 16. It never showed up in any of my samples! I suspect it was "waiting" on an idle wait event and I'm only showing processes consuming CPU or waiting on a non-idle wait event. Very cool.

Now for the timing results. I took five samples.  Again, it's VERY important to know that the wait time (WAIT_TIME_S), DB CPU (DB_CPU_S), and DB Time (DB_TIME_S) values are related to ONLY calling server process, which in this case is session 16. In blazing contrast, the wall time (WALL_S), elapsed time (EL_VSQL_S), and SQL statement CPU consumption (CPU_VSQL_S) is related the entire SQL statement execution.

Here are the "parallel" experimental results.
 SQL>  select * from op_results;

 SAMPLE_NO     WALL_S  EL_VSQL_S CPU_VSQL_S WAIT_TIME_S   DB_CPU_S  DB_TIME_S
---------- ---------- ---------- ---------- ----------- ---------- ----------
         1  46.305951 132.174453   19.53818         .01   4.069579   4.664083
         2  46.982111 132.797536  19.371063         .02   3.809439   4.959602
         3   47.79761 134.338069  19.739735         .02   4.170921   4.555491
         4   45.97324 131.809249  19.397557         .01   3.790226   4.159572
         5  46.053922 131.765983  19.754143         .01   4.062703   4.461175
Let's check the math. So simplicity and clarity, please allow me to round and use sample 5.
DB_TIME_S = DB_CPU_S + WAIT_TIME_S
     4.5  =    4.1   +     0   
The DB Time shown above is kind of close... 10% off. (4.5 vs 4.1) But there is for sure timing error in my collection sript. I take the position, this is close enough to demonstrate the time statistic relationships. Now look below.
Elapsed Time (EL_VSQL_S)  = DB_TIME_S
                   131.7 !=    4.5
Woah! What happened here? (131.7 vs 4.5) Actually, everything is OK (so far aways) because the DB Time is related to the session (Session ID 16), whereas the elapsed time is ALL the DB Time for ALL the processes involved in the SQL statement. Since parallel query is involved, resulting in four additional sessions (1 coordinator, 3 slaves) we would expect the elapsed time to be greater than the DB Time. Now let's look at the wall time.
Wall Time (WALL_S) = ( Elapsed Time (EL_VSQL_S) / Parallelism ) + overhead
             46.1  = (          131.8           /     3       ) + 2.2
Nice! Clearly the effective parallelism is greater than 3 because there is some overhead (2.2). But the numbers makes sense because:

1. The wall time is less than the elapsed time because parallel query is involved.

2. The wall time is close to the elapsed time divided by the parallelism. And we can even see the parallelism overhead.

So it looks like our time math is correct!


Reality And The AWR Or Statspack Report


This is really important. In the SQL Statement section of any AWR or Statspack Report, you will see the total elapsed time over the snapshot interval and perhaps the average SQL ID elapsed time per execution. So what is the wall time? What are users experiencing? The short answer is, we do NOT have enough information.

To know the wall time, we need to know the parallelism situation. If you are NOT using parallel query, than based on the time math demonstrated above, the elapsed time per execution will be close to what the user experiencing (unless there is an issue outside of Oracle). However, if parallelism is involved, you can expect the wall time (i.e, user's experience) to be much less than the elapsed time per execution shown in the AWR or Statspack report.

Another way of looking at this is: If a user is reporting a query is taking 10 seconds, but the average elapsed time is showing as as 60 seconds, parallel query is probably involved. Also, as I mentioned above, never forget the average value is not always the typical value. (More? Check out my video seminar entitled, Using Skewed Data To Your Advantage HERE.)

Thanks for reading!

Craig.