Who's online

There are currently 0 users and 32 guests online.

Recent comments

Oakies Blog Aggregator

Parallel Projection

A recent case at a client reminded me of something that isn't really new but not so well known - Oracle by default performs evaluation at the latest possible point in the execution plan.So if you happen to have expressions in the projection of a simple SQL statement that runs parallel it might be counter-intuitive that by default Oracle won't evaluate the projection in the Parallel Slaves but in the Query Coordinator - even if it was technically possible - because the latest possible point is the SELECT operation with the ID = 0 of the plan, which is always performed by the Query Coordinator.Of course, if you make use of expressions that can't be evaluated in parallel or aren't implemented for parallel evaluation, then there is no other choice than doing this in the Query Coordinator.The specific case in question was a generic export functionality that allowed exporting report results to some CSV or Excel like format, and some of these reports had a lot of rows and complex - in that case CPU intensive - expressions in their projection clause.When looking at the run time profile of such an export query it became obvious that although it was a (very simple) parallel plan, all of the time was spent in the Query Coordinator, effectively turning this at runtime into a serial execution.This effect can be reproduced very easily:

create table t_1
select /*+ use_nl(a b) */
rownum as id
, rpad('x', 100) as filler
(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_1', method_opt=>'for all columns size 1')

alter table t_1 parallel cache;

-- Run some CPU intensive expressions in the projection
-- of a simple parallel Full Table Scan
set echo on timing on time on

set autotrace traceonly statistics

set arraysize 500

regexp_replace(filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as some_cpu_intensive_exp1
, regexp_replace(filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') as some_cpu_intensive_exp2
, regexp_replace(filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') as some_cpu_intensive_exp3
from t_1

-- The plan is clearly parallel
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | 2000K| 192M| 221 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2000K| 192M| 221 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 2000K| 192M| 221 (1)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T_1 | 2000K| 192M| 221 (1)| 00:00:01 | Q1,00 | PCWP | |

-- But the runtime profile looks more serial
-- although the Parallel Slaves get used to run the Full Table Scan
-- All time spent in the operation ID = 0
| Id | Pid | Operation | Name | Execs | A-Rows| ReadB | ReadReq | Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH | Parallel Execution Skew ASH | Activity Graph ASH | Top 5 Activity ASH |
| 0 | | SELECT STATEMENT | | 5 | 2000K | | | 3 | 136 | 120 | #################### | 1:sqlplus.exe(120)[2000K],P008(0)[0],P009(0)[0],P00A(0)[0],P00B(0)[0] | ################################ | @@@@@@@@@@@@@@@@@@@ ( 98%) | ON CPU(120) |
| 1 | 0 | PX COORDINATOR | | 5 | 2000K | | | 119 | 1 | 1 | # | 1:sqlplus.exe(1)[2000K],P008(0)[0],P009(0)[0],P00A(0)[0],P00B(0)[0] | | ( .8%) | ON CPU(1) |
| 2 | 1 | PX SEND QC (RANDOM)| :TQ10000 | 4 | 2000K | | | 66 | 11 | 2 | ## | 2:P00B(1)[508K],P00A(1)[490K],P008(0)[505K],P009(0)[497K],sqlplus.exe(0)[0] | | (1.6%) | PX qref latch(2) |
| 3 | 2 | PX BLOCK ITERATOR | | 4 | 2000K | | | | | | | 0:P00B(0)[508K],P008(0)[505K],P009(0)[497K],P00A(0)[490K],sqlplus.exe(0)[0] | | | |
|* 4 | 3 | TABLE ACCESS FULL| T_1 | 52 | 2000K | 23M | 74 | | | | | 0:P00B(0)[508K],P008(0)[505K],P009(0)[497K],P00A(0)[490K],sqlplus.exe(0)[0] | | | |

Fortunately there is a simple and straightforward way to make use of the Parallel Slaves for evaluation of projection expressions that can be evaluated in parallel - simply add a suitable NO_MERGE hint for the query block that you want the projection to be evaluated for in the Parallel Slaves.If you don't want to have side effects on the overall plan shape by not merging views you could always wrap the original query in an outer SELECT and not merging the now inner query block. There seems to be a rule that the projection of a view always get evaluated at the VIEW operator, and if we check the execution plan we can see that the VIEW operator is marked parallel:

set echo on timing on time on

set autotrace traceonly statistics

set arraysize 500

select /*+ no_merge(x) */ * from (
regexp_replace(filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as some_cpu_intensive_exp1
, regexp_replace(filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') as some_cpu_intensive_exp2
, regexp_replace(filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'm') as some_cpu_intensive_exp3
from t_1
) x

-- View operator is marked parallel
-- This is were the projection clause of the VIEW will be evaluated
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
| 0 | SELECT STATEMENT | | 2000K| 11G| 221 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 2000K| 11G| 221 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | VIEW | | 2000K| 11G| 221 (1)| 00:00:01 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 2000K| 192M| 221 (1)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T_1 | 2000K| 192M| 221 (1)| 00:00:01 | Q1,00 | PCWP | |

-- Runtime profile now shows effective usage of Parallel Slaves
-- for doing the CPU intensive work
| Id | Pid | Operation | Name | Execs | A-Rows| Start | Dur(T)| Dur(A)| Time Active Graph | Parallel Distribution ASH | Parallel Execution Skew ASH| Activity Graph ASH | Top 5 Activity ASH |
| 0 | | SELECT STATEMENT | | 5 | 2000K | | | | | 0:sqlplus.exe(0)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0] | | | |
| 1 | 0 | PX COORDINATOR | | 5 | 2000K | 17 | 63 | 10 | # ## # #### | 1:sqlplus.exe(10)[2000K],P000(0)[0],P001(0)[0],P002(0)[0],P003(0)[0] | #### | * (5.6%) | resmgr:cpu quantum(10) |
| 2 | 1 | PX SEND QC (RANDOM) | :TQ10000 | 4 | 2000K | 5 | 61 | 10 | ## # ## ## ## # | 3:P002(5)[544K],P001(4)[487K],P000(1)[535K],P003(0)[434K],sqlplus.exe(0)[0] | # | (5.6%) | ON CPU(7),resmgr:cpu quantum(3) |
| 3 | 2 | VIEW | | 4 | 2000K | 2 | 82 | 69 | #################### | 4:P003(42)[434K],P001(35)[487K],P000(26)[535K],P002(22)[544K],sqlplus.exe(0)[0] | ############ | @@@@@@@@@@@@@@@@@@@ ( 70%) | ON CPU(125) |
| 4 | 3 | PX BLOCK ITERATOR | | 4 | 2000K | | | | | 0:P002(0)[544K],P000(0)[535K],P001(0)[487K],P003(0)[434K],sqlplus.exe(0)[0] | | | |
|* 5 | 4 | TABLE ACCESS FULL| T_1 | 52 | 2000K | 3 | 78 | 29 | ###### ####### # ### | 4:P000(11)[535K],P002(8)[544K],P001(8)[487K],P003(7)[434K],sqlplus.exe(0)[0] | ### | ***** ( 19%) | resmgr:cpu quantum(30),ON CPU(4) |

At runtime the duration of the query now gets reduced significantly and we can see the Parallel Slaves getting used when the VIEW operator gets evaluated. Although the overall CPU time used is similar to the previous example, the duration of the query execution is less since this CPU time is now spent in parallel in the slaves instead in the Query Coordinator.


By default Oracle performs evaluation at the latest possible point of the execution plan. Sometimes you can improve runtime by actively influencing when the projection will be evaluated by preventing view merging and introducing a VIEW operator that will be used to evaluate the projection clause.The optimizer so far doesn't seem to incorporate such possibilities in its evaluations of possible plan shapes, so this is something you need to do manually up to and including Oracle 12c (version as of time of writing this).

Cusco to Lima

It was a 3:30 start, which after broken sleep and the events of the day before had me a little worried. We got a taxi to the airport in Cusco, which is the coldest airport I have ever experienced. After checking in, we headed to the departure gate, which was also freezing. The departure gate was interesting. The lady brought her own laptop, microphone and speaker to make the announcements. :)

We got on to the coldest plane I’ve ever been on. I don’t remember seeing people on a plane in coats and woolly hats before. :) After a quick flight we got to Lima airport, where I said goodbye to Debra, who is flying back to Northern Ireland, via Miami and London.

Having a 14 hour layover in Lima, I decided to check in to a hotel at the airport and sleep for a while. I also upgraded my flight home to a business class flight. The combination of the Machu Picchu trip, airport hotel and business class flight home have added up to quite a lot of money, but if I get home in a reasonable state, it will be worth it. :)



Cusco to Lima was first posted on August 15, 2015 at 9:40 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Machu Picchu

At about 04:00 we were queuing for the bus ride to the base of Machu Picchu. I started to feel a bit ill again. A little after 05:00 we were on the bus driving up to the base of Machu Picchu. It took about 30 mins to get there, most of which I spent trying not to puke.

I was very dissapointed with the entrance to Machu Picchu. It felt like the entrance to a theme park. There was even Machu Picchu WiFi. We were there to witness wonder and spectacle, but seemed to be getting Disneyland. After being on the verge all morning, I puked and felt much better.

When we eventually got through the turnstyles, we started to walk up the hill. The stairs are quite steep, but nothing I would be worried about if it weren’t for the altitude. It makes it feel like hard work, so you have to take it slow. I used the turns to my advantage and mostly hid the fact I was repeatedly throwing up. After a few minutes we got to area that overlooks the former residential area of Machu Picchu. If you’ve ever seen a picture of Machu Picchu, chances are you’ve seen the one taken from this spot. A few levels up and we were at the guardhouse, which gives an even better view. I puked there too. :) For me, this was all I wanted to do as far as Machu Picchu was concerned. I wanted to stand there and see this for myself. Everything else was a bonus. People visit several times and spend days there. This was really all I wanted. :)

After that we walked down towards the residential area. At that point, I really felt like I was done for. I told Debra to carry on and I walked down to the entrance to look for medical attention. I finally got to see the medic, and puke in her bin a few times. She injected me with a concoction of anti-nausea and electrolytes and left me to sleep for a while. By the time Debra returned I was feeling much better. Interestingly, it was nothing to do with the altitude. My blood O2 was fine. It was pretty similar to what happened to me in India. I’m starting to think it’s nausea caused by a type of migrane, induced by lack of sleep.

Anyway, after my rather brief visit to Machu Picchu, we were heading down the mountain in the bus. We got some food and chilled out before boarding the train to take us back to Cusco and the rest of our luggage.

The train journey back took about 3.5 hours. Lot’s of great sights, only marred by some intensely annoying children, who were complaining about being bored. Why do adults drag children along to this stuff? They don’t enjoy it and ruin it for everyone else!

Back at Cusco, it was a quick taxi ride to the hotel, where I puked and went to bed. We were hoping to have a brief look at Cusco, but it gets dark so early in Peru, there really wasn’t time.

I would like to say I got a good night’s sleep, but the hotel we stayed at was so noisey. I woke several times in the night because of fireworks, music and general noise in the town, which made the 03:30 start the next day even harder to cope with.

Now I know this all sounds really negative and bad, but it was worth it. Machu Picchu is one of those places I always hoped to see before I died. The fact it nearly killed me in the process is besides the point. :) I’m pretty sure if I hadn’t been so beaten up by two weeks of travelling and presenting it would have been a breeze. Part of me thinks it would be nice to go back and see again, but part of me thinks I’ve done all I wanted to do. It is a very expensive experience, but worth it in my opinion.

I wasn’t really in a fit state to take photos, but fortunately Debra was and she let me have a copy of them, which you can see here. I especially like the ones of me looking like dreadful. :)



Update 1: I think it is great how much work they are doing to preserve the Machu Picchu site, but the amount of rebuilding is a bit of a concern. At the moment, about 30% of the site has been rebuilt and the work is continuing. If too much is done, it ceases to be an ancient site and becomes a modern site in the style of an ancient one. They need to tread very carefully, or risk taking the final step and completing the transition to Disneyland!

Update 2: At no point did I see Pikachu! Apparently, Machu Picchu and Pikachu are not the same thing. Who’da thunk it?

Machu Picchu was first posted on August 15, 2015 at 9:03 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Lima to Cusco to Machu Picchu

With the tour over, Debra and I had arranged to spend a couple of days visiting Machu Picchu, before heading home.

We woke up early on Friday to get a flight from Lima to Cusco. We arrived at the airport in plenty of time, got to our gate and saw a list of delayed and cancelled flights to Cusco. The weather was too bad in Cusco for flights to take off and land. Luckily, after a while the weather apparently cleared in Cusco, allowing us to take a flight which arrived about 1 hour late.

We had arranged to drop our luggage off at a hotel in Cusco a day early, then continue on to Machu Picchu. The taxi ride to the hotel was interesting. Cusco has some very narrow streets that are barely wide enough for get a car through. It was quite hairy at times. We eventually got there, dropped our bags off and continued in the taxi to Ollantaytambo, which took about 90 mins. This allowed us to briefly see some of the sacred valley up close. During the drive I had a funny turn, which I put down to the high altitude. Debra said I looked green. By the time we got to Ollantaytambo and got some food I was feeling better.

While we were waiting for the train, I noticed the arrivals/departures screen on the wall had a session of TOAD running, doing some queries. By the time we had cameras ready, it was gone and the announcements screen was back. Debra went on the hunt and found a lady in an office that confirmed they (PeruRail) were using Oracle. :) We got on the Vistadome train, which has lots of extra windows, including in the roof, which is essential if you want a good view of the mountains around you. The train has a rather narrow gauge, which is a little disconcerting at first. The train takes you to Aguas Calientes, now known as Machu Picchu Pueblo, which is the best place to stay if you plan an early visit to Machu Picchu.

Just a quick word of warning, I did not like Machu Picchu Pueblo at all. It is a great setting in the mountains with the river running through, but it is one giant tourist centre, full to the brim with restaurants, markets and tourist shops. Many of the write-ups about Machu Picchu talk about it being ruined by tourists. This town proves the point! We bought our bus tickets for the next day, grabbed some food and headed to bed for an early start.



Update: Here is a quick montage of the journey to Machu Picchu.

Lima to Cusco to Machu Picchu was first posted on August 15, 2015 at 8:21 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Mr DISTINCT might not be your friend

Whenever you have the need to use the DISTINCT keyword, its worth just pausing for a second, and making sure that you are not hiding just a larger issue. It actually might represent either incorrect use of SQL or incorrect assumptions from the data model.

Consider the following example

FROM   emp e, dept d
WHERE  e.ename = 'SMITH'
AND    e.deptno = d.deptno

The query is certainly valid, but when I see "distinct" I ask myself the following questions:

Has the DISTINCT has been added in an attempt to only return a single row ?, ie, is someone working under the assumption being that an employee name can only refer to a single department ? Unless there is a unique constraint on the ENAME column, then we can still just as easily get multiple rows back (even with the DISTINCT), so the SQL will be a "sleeping problem" in the application until the data causes it to fail.

Because the DISTINCT keyword here:

  • doesn’t give you any guarantee that you’ll only get one row, and
  • might be making the database do excessive work (get all rows, sort them and remove duplicates), every single time you run this SQL

Alternatively, perhaps there is meant to be a single department for an ENAME, ie, ENAME is unique. If that is the case, then we should change the database model/design to reflect it and not use DISTINCT to "fake" it.

I’m not saying DISTINCT is always a bug – but it’s always worth just asking yourself if you were correct to be using it.

Oracle Database Developer Choice Awards

Hey, do you know a developer who should be recognized as one of the worlds best at: SQL, PL/SQL, Oracle REST Data Services, Oracle Application Express, or Database Design?

Oracle Technology Network (OTN) is sponsoring the Oracle Database Developer Choice Awards to help recognize your superstars as they deserve.

Please go to to learn more about the awards and how to nominate people.

VirtualBox 5.0.2

VirtualBox 5.0.2 has been released. It’s the first maintenance release for the 5.0 version.

Downloads and changelog in the usual places.



VirtualBox 5.0.2 was first posted on August 14, 2015 at 6:29 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Friday Philosophy – Building for the Future

I started my Oracle working life as a builder – a Forms & Reports Builder (briefly on SQL*Forms V2.3 but thankfully within a month or two we moved up to SQL*Forms V3, SQL*reportwriter V1.1 and SQL*Menu 5 – who remembers SQL*Menu?). Why were we called Builders? I guess as you could get a long way with those tools by drawing screens, utilising the (pretty much new) RI in the underlying Oracle V7 to enforce simple business rules and adding very simple triggers – theoretically not writing much in the way of code. It was deemed to be more like constructing stuff out of bits I guess. But SQL*Forms V3 had PL/SQL V1 built in and on that project we used it a *lot*.

I had been an “Analyst Programmer” for 3 years before then and I’ve continued to be a developer/programmer/constructor-of-code on and off over the intervening couple of decades. I’m still a developer at times. But sometimes I still think of it as being a “builder” as, if you do it write {sorry, little word-play joke there} you are using bits of existing stuff and code designs/patterns you know work well and constructing your system. The novel part, the bit or bits that have never been done before (at least by me), the “architecting” of those units into something interestingly different or the use of improved programming features or techniques vary from almost-none to a few percent. That is the part which I have always considered true “Software Development”.

So am I by implication denigrating the fine and long-standing occupation of traditional builders? You know, men and women who know what a piece of two-by-four is and put up houses that stay put up? No. Look at the below.

This is part of my neighbour Paul’s house. He is a builder and the black part in the centre with the peaked roof is an extension he added a few years back, by knocking his garage down. The garage was one of three, my two were where the garage doors you can see are and to the left. So he added in his two-story extension, with kitchen below and a very nice en-suite bedroom above, between his house and my ratty, asbestos-riddle garages. Pretty neat. A few years later he knocked down my garages and built me a new one with a study on top (without the asbestos!) and it all looks like it was built with his extension. Good eh? But wait, there is more. You will have noticed the red highlight. What is that white thing?

Closer in - did he forget some plumbing?

Closer in – did he forget some plumbing?

This pipe goes clean through the house

This pipe goes clean through the house

When I noticed that white bit after Paul had finished his extension I figured he had planned more plumbing than he put in. I kept quiet. Then, when he had built my new garage and study, I could not help ask him about the odd plumbing outlet. So he opened it. And it goes through the dividing wall all the way through to the other side of the house. Why?

“Well Martin, putting in cables and pipes and s**t into an existing house that go from one side to the other, especially when there is another building next door, as a real pain in the a**e. It does my ‘ead in. So when a build something that is not detached, I put in a pipe all the way through. Now if I need to run a cable from one side of the house to the other, I have my pipe and I know it is straight, clean, and sloping every so slightly downwards”. Why downwards? “Water Martin. You don’t want water sitting in that pipe!”.

I’ve noticed this about builders. When I’ve had work done that is good, there is at least one person on the team who thinks not just about how to erect or do what needs to be done today, they do indeed think about what you will need after the build is done, or in a few years. Such as hanging doors so they do not smack into the cupboards you will put in next… *sigh*. Paul is the thinking guy in his little team. I suspect one of the others is pretty smart too.

But isn’t this what the architect is for? To think about living with the building? Well, despite the 7 years plus needed to become a true architect (as that term really means, not as some stolen label for software designers with too much ego) I’ve had builders spot the pragmatic needs a couple of times that the architect missed.

And as I think we would all agree, a good software developer always has an eye on future maintenance and modification of the software they develop. And they want to create something that fits in the existing system and looks right. So just like my builder neighbour does.

I’m not a software architect. I’m a code builder. And I’m proud of it.

OTN Tour of Latin America 2015 : PEOUG, Peru – Day 1

A quick taxi ride got us to the conference hotel really quickly, so we were nice and early for the PEOUG event.

After the introductions by Miguel Palacios, it was time for the first sessions of the event. Of the English speakers, first up were Debra Lilley and Dana Singleterry. Debra had some problems with her laptop, so she did her presentation using mine and all went well. Dana did his session over the net, so I sent a few Tweets to let him know how things looked and sounded from our end. I figured a bit of feedback would help reassure him there weren’t any technical issues.

My first session of the day came next. I had a good sized audience and some of the people were brave enough to ask questions at the end. :) I had some in English and some in Spanish using the translation service to help me. :)

Debra fixed her laptop by the time her next session started, but her clicker died, so she borrowed mine. Dana’s second session was at the same time as Debra’s, so I flitted between the two, sending a few feedback Tweets to Dana about his session again.

After that session, Ronald, Debra, Pelinio, Enrique and myself ducked out to get some lunch in a place down the street.

After lunch, both Ronald and I each had back-to-back sessions. I did my Cloud Database and Analytic Functions talks. I feel like they went well. I hope the crowd did too. :)

There was one more set to talks, all from Spanish speakers, including a very full web session by Edelweiss from Uruguay. After that we got together for the closing session and some prize draws. I didn’t understand what was being said, but everyone seemed really happy and in good spirits, so I think the whole day was well received. Certainly all the feedback we got was very positive!

Big thanks to MiguelEnrique and everyone at PEOUG for inviting us and making us feel welcome. Thanks to the attendees for coming to the sessions and making us feel special by asking for photos. :) Also, big thanks to the ACE Program for making this possible for us!

So that marks the end of this years OTN Tour of Latin America for me. Sorry to the countries in the northern leg. I hope I will be able to visit your folks soon!

Debra and I are going to visit Pikachu Machu Picchu over the next couple of days, then it’s back home to normal life for a while. :)

I’ll write a summary post to close off this little adventure when I get home. Once again, thank you all!



OTN Tour of Latin America 2015 : PEOUG, Peru – Day 1 was first posted on August 13, 2015 at 2:28 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

getSQLMON… mining SQLMON reports from Oracle

As an Oracle performance engineer, I often work with customers on benchmarks and diagnosis of various issues.  Now, everyone can gather an AWR report and send it my way, but that is just the tip of the iceberg.  AWR reports are great for summarizing activity at a high level and can even find a SQL that is taking longer than expected.  So, once we have identified a poor performing SQL, how do we fix it?

…that is where SQLMON comes into the picture.

gathering sqlmon reports

The power of the SQLMON report is very well documented.  It provides an insight into EXACTLY what was happening when that query was running on the system.  It documents the run time, SQL plan, CPU activity, IO activity, Offload, etc…  The report can be gathered by running the “dbms_sqltune.report_sql_monitor” utility to extract a report.  Each SQL in Oracle maps to a specific “SQL_ID” as this is really just a hash of the sql text….But, that is not all.

Even time you run a SQL, a specific execution plan must be created or reused.  This execution plan is identified by the “sql_exec_id”….  But wait, this is still not all.

You may have run the same SQL multiple times.  Maybe the first run was on a quite system and the second was during a batch window.  Each execution timestamps the “sql_exec_start” in the v$sql_monitor table.

You can quickly see how one SQL statement could have multiple runs and plans.  This can get messy and cumbersome to gather by hand.  Enterprise manager allows you to look at individual runs and save the SQLMON file, but let’s say you have 20 executions with 3 different plans?

…clearly there has got to be a better way.

the birth of the getSQLMON script

I created a simple script to gather all SQLMON reports for a given portion of the SQL text.  I basically searched the v$sql_text table for any SQLid’s that matched a specific pattern.  It then creates a directory for this particular run and a subdirectory for every run that matches a given pattern.

$ ./
Usage: ./  

$ ./ RTL19

------------- ----------- ---------- -------------------- -------------
8g97qvjxkdvq5    16777218 1439194608 select /*+ RTL19 */              8
8g97qvjxkdvq5    16777216 1439194779 select /*+ RTL19 */              3
8g97qvjxkdvq5    16777216 1439363758 select /*+ RTL19 */              2

Gather SQLMON reports for these SQLids
ALL done... Reports are in sqlmon_081215_1308

$ cd sqlmon_081215_1308

$ ls
sqlid_8g97qvjxkdvq5_epoch_1439194608 sqlid_8g97qvjxkdvq5_epoch_1439363758
sqlid_8g97qvjxkdvq5_epoch_1439194779 sqlmon_summary_081215_1308.txt

$ ls sqlid*



Once the tool has been run, the directory structure can be distributed for further analysis.  This tool has been very helpful for me to mine SQLMON reports when running benchmarks or analyzing running various systems.  I have placed the getSQLMON tool in GitHub.

Hopefully it will be of use to you as well!! 

Filed under: Exadata, Oracle