Search

OakieTags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

Tuning Blog Entries

Organizing  some of my past blog entries on Oracle database performance tuning and analysis. I’ll add more notes as well as a separate section on SQL tuning in particular.

ASH

AWR

Redo

Etc

SQL

VST – vistual sql tunning

 

 

 

Why Are My Indexes Still Valid Solution (A Second Face)

I’ve been so busy lately, I just haven’t had any spare time to post. For now, the quick answer to the last quiz is that the second table was indeed an Index Organized Table (IOT). One of the nice benefits of an IOT is that when re-organised, unlike a Heap Table, all indexes remain valid, [...]

APAC OTN Tour: Beijing, China

My flights to China were rather uneventful. The Birmingham to Dubai leg was delayed by an hour due to fog in Dubai. I had a 4 hour connection in Dubai originally, so the delay was no big deal.

Arriving in Beijing was a little unnerving. I misplaced the Chinese version of the hotel address, but had the English version. Finding someone to translate it proved very difficult and as it turned out they translated it incorrectly. Fortunately I found a cached version of the address on my iPad, so that saved by bacon. The second hitch was that I couldn’t get cash with by debit cards. Just a flat-out refusal from any ATM in China. Arrrggghhh! Fortunately, I was able to get cash advances using my credit card. I’m going to pay through the nose for it, but at least I can survive.

Today is the first day of the conference and I had a morning slot. For the English speakers, we had one screen showing our slides in English and one showing the Chinese translation. I was asked to speak more slowly than usual (kinda difficult for me) and as a result I had to reduce the content somewhat. I did a run through last night to make sure my timing was OK with this adjustment.

The conference has a single track, so you get a room full of people from different technical areas. This is always a little unnerving as you worry about the relevance of your material to audience. Here are a couple of photos of the audience I took while I was setting up.

 

Everything seemed to go OK. I hope they understood my accent. :)

With a bit of luck I will get to see some of Beijing over the next couple of days. I’m behing the Great Firewall of China, so some sites (Facebook and Twitter) are blocked completely and many other sites (like Gmail and any other Google related services) seem to come and go. For the time being my blog seems active. If I lose access to it I will update things once I get to New Zealand.

Cheers

Tim…




Little Things Doth Crabby Make – Part XV. Oracle SPARC SuperCluster Is Much Faster Than IBM Power Systems! No Squinting Allowed!

Since I missed Oracle Openworld 2011 I wasn’t able to attend the keynotes. I have, however, taken the time to view each of them in playback from video archives. After viewing the keynote delivered by Oracle Corporation’s CEO Larry Ellison, I felt compelled to read some additional literature relevant to the IBM-smashing claims made by Mr. Ellison during his segment focused on Oracle SPARC SuperCluster. A simple Google search brought me to www.oracle.com/us/corporate/features/sun-beats-ibm-501074.html where I see the following graphic:

 

 

 

 

 

 

 

 

 

 

 

 

 

It has been a long time since my last installment in the Little Things Doth Crabby Make series so it’s high time I do so.  Here is what I see at the “sun-beats-ibm” webpage:

 

 

 

 

 

 

 

 

 

In case the fine-print disclaimer is too small, here’s what I see (bold font added by me):

Sources for Comparison of Systems:

Systems cost based on server, software and comparable storage list prices (without discounts), as well as third party research. Performance comparison based on Oracle internal testing together with publicly available information about IBM Power 795 TurboCore system with highest processor speed commercially available (4.25 GHz) as of Sept 28, 2011

 

That makes me crabby. I shouldn’t have to squint, should I?

Filed under: oracle

Want to Know More about Oracle’s Core?

I had a real treat this summer during my “time off” in that I got to review Jonathan Lewis’s up-coming new book. I think it’s going to be a great book. If you want to know how Oracle actually holds it’s data in memory, how it finds records already in the cache and how it manages to control everything so that all that committing and read consistency really works, it will be the book for you.

{Update, Jonathan has confirmed that, unexpected hiccups aside, Oracle Core: Essential Internals for DBAs and Developers should be available from October 24, 2011}

{Thanks to Mike Cox, who let me know it is already available to be reserved at Amazon}

Jonathan got in touch with me around mid-May to say he was working on the draft of his new book, one that would cover “how does Oracle work”, the core mechanics. Would I be willing to be one of his reviewers? Before anyone comments that there is not likely to be much about core Oracle that I know and Jonathan does not, he did point out that he had already lined up someone to be his technical reviewer, ie someone he expected to know as much as he and help spot actual errors. The technical reviewer is the most excellent Tanel Poder, who posted a little mention of it a couple of months back.

I was to act more like a typical reader – someone who knew the basics and wanted to learn more. I would be more likely to spot things he had assumed we all know but don’t, or bits that did not clearly explain the point if you did not already know the answer. ie an incomplete geek. I figured I could manage that :-) .

It was a lot harder work than I expected and I have to confess I struggled to supply back feedback as quickly as Jonathan wanted it – I was not working but I was very busy {and he maybe did not poke me with a sharp stick for feedback soon enough}. As anybody who has had to review code specifications or design documents will probably appreciate, you don’t just read stuff when you review it, you try and consider if all the information is there, can it be misunderstood and, if you find that you don’t understand a section, you need to work out if the fault is with you, with the way it is written or with what is written. When I read a technical {or scientific} document and I do not fully understand it, I usually leave it a day, re-read it and if it still seems opaque, I just move on. In this case I could not do that, I had to ensure I understood it or else tell Jonathan why I thought I did not understand it. If there are sections in the end book that people find confusing, I’ll feel I let Jonathan down.

Just as tricky, on the one hand, as I’ve been using Oracle for so long and I do know quite a lot about Oracle {although clearly not enough in the eyes of the author :-) } I had to try and “not know” stuff to be able to decide if something was missing. On the other, when I wanted to know more about something was I just being a bit too nerdy? I swung more towards the opinion that if I wanted to know more, others would too.

I have to say that I really enjoyed the experience and I learnt a lot. I think it might change how I read technical books a little. I would run through each chapter once to get the feel of it all and then re-read it properly, constantly checking things in both version 11 and 10 of Oracle as I read the drafts and would not let myself skip over anything until I felt I really understood it. As an example, I’ve never dug into internal locks, latches and mutexes much before and now that I’ve had to learn more to review the book, I have a much better appreciation of some issues I’ve seen in the wild.

Keep an eye out for the book, it should be available by the end of this year and be called something like “Oracle Core” {I’ll check with Jonathan and update this}. I won’t say it will be an easy read – though hopefully a little easier as a result of my input – as understanding things always takes some skull work. But it will certainly be a rewarding read and packed full of information and knowledge.

The Social Development Database

I’ve always been fascinated by development databases — more so sometimes than huge, heavily utilized production ones. Mainly because I’ve seen how the beginnings of a performance problem, or the start of an elegant solution takes shape within a development database. It’s one of the reasons why I love high levels of visibility through full DDL-auditing within development. I love to SEE what database developers are thinking, and how they are implementing their ideas using specific shapes of data structures.

One of the concepts I’d love to see is a “river of news” panel within development tools to see what is going on within a development database. Some of the good distributed source code control systems do this now.

Here’s a good example of what I mean:

http://github-images.s3.amazonaws.com/blog/2011/mac-screenshots/commits-full.png

Imagine if there was a panel in SQL*Developer or TOAD that looked something like this:

This wouldn’t be all that hard to implement if full DDL-auditing was turned on…

Happy Birthday Val…

It’s my mom’s 70th birthday today. Happy birthday Val.

I’m flying to China today, so I’ve set this post to publish automatically while I’m in transit. :)

Cheers

Tim…




Debugging PL/SQL and Java Stored Procedures with JPDA

In 2003 I published a paper entitled Debugging PL/SQL and Java Stored Procedures with JPDA. Its aim was to describe how to debug PL/SQL and Java code deployed into the database with JDeveloper 9i. Two weeks ago a reader of my blog, Pradip Kumar Pathy, contacted me because he tried, without success, to do something similar with JDeveloper 11g, WebLogic 11g and Oracle Database 11g. Unfortunately I was not able to help him. The reason is quite simple, since 2004 I’m an Eclipse user…

Few days later Pradip contacted me again to let me know that, at last, he succeeded. Here you find his notes…

  1. Grant the required privileges
  2. GRANT DEBUG CONNECT SESSION to &&schema_name;
    GRANT DEBUG ANY PROCEDURE TO &&schema_name;

  3. Create a database connection under “Application Resources” and compile the stored procedure in debug modus (Figure 2a and Figure 2b)
  4. Write some Java code to let the database engine connect JDeveloper (this piece of code must be executed before calling the PL/SQL stored procedure)
  5. private static void remotePLSQLDebug(Connection con) throws SQLException
    {
      SystemProperties systemProperties = null;
      systemProperties = SystemProperties.getInstance();
      CallableStatement callState = null;
      String port = systemProperties.get("plsqldebug.port").trim();
      String storedCall = "call dbms_debug_jdwp.connect_tcp('localhost'," + port + ")";
      callState = con.prepareCall("{" + storedCall + "}");
      callState.execute();
      callState.close();
    }
  6. Configure the plsqldebug.port parameter in the application properties
  7. Edit the project properties to enable JPDA and defining the port used by the JPDA listener (Figure 5a and Figure 5b)
  8. Deploy the EAR in WebLogic and start the JPDA listener configured in the previous step
  9. Execute the Java code of step 3; as a result the database engine connects the JPDA listener and a connection process is created (Figure 7)
  10. When the PL/SQL stored procedure is executed, JDeveloper is able to debug it (open the PL/SQL stored procedure in the “Database Navigator” for that purpose)
  11. Once the debugging is over detach the connection process

Thank you Pradip to share your findings!

Returning to the Day Job.

Having the Summer off. It’s something that quite a few IT contractors and some consultants say they intend to do…one year. It’s incredibly appealing of course, to take time off from your usual work to do some other things. Asking around, though, it is not something many of us self-employed types who theoretically could do, actually have done. I think it is because, although the theory is nice, the reality is a period not earning a living – and the background worry of “if I take a break, will I be able to step straight back into gainful employment afterwards”?

Well, purely as a service to all those who wonder about it, I decided to sacrifice myself to the experiment and do it this year. I had the summer off. {I know, I picked what turned out to be a very average summer weather-wise, but you just never know in the UK}. I’d finished a job for a client that had turned from a small number of weeks to several months of full-on seven-days-a-week effort and I had a load of domestic things that desperately needed some attention and my potential next job evaporated. I also have to acknowledge that I am in a somewhat lucky position. We do not have kids so the financial worries and potential impact on the innocent does not exist and my wife has been remarkably tolerant. How did it go? In a word, “great!”. I am a lot healthier and a lot more fired up to get on with work as a result of a few months away from the coalface – but I’ll save the second half of this post for some of the boring details of what I got up to {which you may well want to skip}.

This week the break ends. As I write this I do not have a job – but today I start looking around and I start locking myself in my office for the day. I’ll be doing work, even if this consists of me simply catching up on technical reading and testing some ideas I have about Oracle internals and design. This should hopefully increase the technical content of my Blogs too. If anyone is working on a project that could do with some Oracle performance or system design work, I’d be happy to hear from you.

As you can see, the one thing I am terrible at as a self-employed person is the selling of my services. I’m sure I will not get much response from a simple “give me a job” line in a blog that, let’s face it, is being read mostly by technical-doing-people and not budget-holding-hiring-people. However, you might have noticed a green text box on the right of this page proclaiming “available for consultancy”. It’s a free thing for me to do and I intend to keep it there, swapping it for a red “unavailable” when I am busy. This might of course reveal how poorly I do in finding work – but that could be interesting of itself.

So, what have I done? I’ve kept my hand in on the Oracle front but what about non-IT things? Well, one thing was building a clay pizza oven. That took a day on a course and about 200 hours digging a hole in the garden for clay, cleaning up some old bricks, mixing up endless quantities of cobb with the clay, straw and sand, building it wrong several times and destroying a perfectly good Pizza in it.

The final product works though. We fired it up this last weekend and it got up to 300C and the pizzas we cooked in it were very good – allowing for the odd bit of ash and ember. Hint, do not use wood that spits, like pine and off-cuts from building work. If you are wondering about the big bits of wood in the entrance, that is some green oak offcuts which I popped in there after the last firing. The morning after using the oven, you can put in wood for the next firing and the residual heat helps dry it nicely.

A lot has been done around the garden, especially as I now own a chainsaw. Again, I got myself on a course to learn how to maintain the machine and keep the chain sharp. I had to modify some protective clothing to suit my diminutive frame and then about two dozen trees came down and had to be chopped up. {Monty Python’s lumberjack sketch obviously had a big impact on my development as a child}. I then took the chainsaw for an excursion to Wales and gave my brother’s garden the same treatment. I wonder if anyone local wants a load of trees cut down? :-)

A major task has been to organise and then oversee the start of some building work on the house. As anyone who has had such work done knows, it takes a lot of time to organise the work and then once it starts there are a lot of initial issues to sort out. Much like designing and building a new computer system, upfront design and project initiation can make the whole build run so much more smoothly. At least, I am hoping so!

The building work resulted in an odd little bit of computer work. One of the guys was complaining that his machine had started running slow and now it would start up only to shut down immediately. One of the other builders said “Well ask the governor, he does computers”. Of course, I utterly failed to explain that I do other types of computers and so this PC duly arrived. It was nice to do some IT that was not my normal area. It turns out this machine had a couple of nasty viruses which I had to sort out first and then protect the machine with some decent AV software. This was made difficult by the “Rapport” security software his bank had encouraged him to download, which was somehow blocking a windows update from working, as well as grinding the machine to a halt. The performance impact of the software was causing the windows update to take 10 minutes, it would fail and take 20 minutes to roll back and then force a reboot… and cycle through the update again. I temporarily fixed this by booting off a Windows CD and I could then remove the dodgy security software, despite it’s attempts to stay in place. But the machine was still terribly slow. Dixons had sold him the machine and seen fit to put in only 512MB of memory, for a Windows Vista machine. £10 got us an extra 1GB of memory and a machine that now worked. Payment for all of this effort was a couple of nice bottles of wine and a very happy builder.

The building work is still in progress and, of course, part of why my Summer off has come to an end is that the builders have used up the spare money I had from the last job. *sigh*. Mind you, the Summer off was supposed to end in September but as the weather suddenly improved I got permission for a time extension from the lone worker in the house. Has anyone noticed this blog posting is slowly becoming nothing but an excuse for me to stick up some pictures?

There have been a lot of far more mundane things to do on the domestic front but there has also been some IT stuff I have been up to – but I’ll save that for a another day and a more oracle-centric posting I think. Right, I better try and make the CV look more like I’m the best Oracle guy on the planet {OK, I’d have to change the name on the top}. It leans towards being understated honesty rather than overstretched impressive, and I know that is what potential employers like to see (I think it is better for the interview to indicate more skills than the CV, rather than the other way around) but agents seem to want you appear better than a bizzare child of Tom Kyte and Christian Antognini for them to put you in a pile other than “yet another bog-standard IT grunt”. If the agent won’t put you forward, you can’t make your case in the interview, can you?

AIOUG Webcast: Methodical Performance Tuning

A big thank you to all those you attended my session today. I sincerely hope you got something out of it. Here are the scripts I used in the demo. And, here is the slide deck, if you are interested.

Remember, this was just the beginner's session. We will have intermediate and advanced ones in near future. Stay tuned through the AIOUG site.