September 5, 2011 I often look for easier ways to accomplish time consuming tasks, and I suspect that is part of the reason why I buy so many computer books. In Recipe 2-5 (page 60) of the “Oracle Database 11g Performance Tuning Recipes” book there is a SQL statement to determine which database columns (owned [...]
It’s Father’s Day here today in Australia and because I’ve naturally been a really really good Dad all year, my family have given me a real treat for my present this year, the Immersion Box Set of the Pink Floyd classic, The Dark Side Of The Moon (although unfortunately, I have to wait a couple of weeks for [...]
…then calmly close the door and get back to work! They’ll be exceedingly happy!
The rate at which new applications pour forth from corporate IT is astounding. Nimble businesses, new and old, react to bright ideas quickly and doing so often requires a new application. Sure, the backbone ERP system is critical to the business and without it there would be no need for any other application in the enterprise. This I know. However…
When an application developer is done white-boarding a high-level design to respond to a bright idea in the enterprise it’s off to the DBA Team to get the train rolling for a database to back-end the new application. I’d like to tell the DBA Team what to tell the application developer. Are you ready? The response should be:
Go do it yourself! Leave me alone. I’m busy with the ERP system
You see, the DBA Team can say that and still be a good corporate citizen because this hypothetical DBA Team works in a 21st century IT shop where Database As A Service is not just something they read about in the same blog I’ve been following for several years, namely Steve Bobrowski’s blog Database As A Service.
Steve’s blog contains a list of some of the pioneers in this technology space. I’m hoping that my trackback to his blog will entice him to include a joint VMware/EMC product on the list. I’d like to introduce readers of this blog to a very exciting technology that I think goes a long way towards realizing the best of what cloud database infrastructure can offer:
I encourage readers to view this demo of vFabric Data Director and read the datasheet because this technology is not just chest-thumping IdeaWare™. I am convinced this is the technology that will allow those in the DBA community to tell their application developers to “go do it yourself” and make their company benefit from IT even more by doing so.
What Can This Post Possibly Have To Do With Oracle Exadata?
Folks who read this blog know I can’t resist injecting trivial pursuit.
The architect and lead developer of vFabric Data Director technology is one of the three concept inventors of Oracle Exadata or, as it was soon to be called within Oracle, Storage Appliance for Grid Environments (SAGE). One of the others of that “team of three” was a crazy-bright engineer with whom I spent time scrutinizing the affect of NUMA on spinlocks (latches) in Oracle Database in the Oracle8i time frame.
It is a small world and, don’t forget, if a gifted application developer approaches your desk for a timely, urgent request for database provisioning just tell him/her to go do it yourself! They’ll be glad you did!
A question that comes up from time to time on the OTN forums is the one about how much undo do you generate when you insert or update a row. It’s not a question that has an immediate answer – it depends on what you’re actually doing, the amount of change you introduce, and the number of indexes affected. However, there are a few guidelines that will point you in the right direction – and the key is to keep an eye on the used_urec and used_ublk colummns from v$transactions.
Consider the example of inserting data: when you insert a row, you typically generate one undo record (used_urec) for the row, but when you insert many rows using an array insert Oracle has an optimisation mechanism on undo record creation that allows it to create one used_urec to cover all the changes you have made simultaneously to an individual block - so used_urec could be much smaller than the number of rows processed.
However, if you have indexes in place and are doing normal index maintenance on import, then each table row would require each index to be updated, so you would go back to one used_urec per table row plus one used_urec per index maintained per table row.
So, when you look at the “big picture” there’s no obvious correlation between rows inserted and undo generated — until you look at the fine detail of exactly what you’re doing, and whether any optimisations apply. (The details of the optimisation strategies available vary with the chosen insert mechanisms and with version of Oracle)
Just for reference, here’s a link to a little note I wrote some months ago about monitoring undo – it generated a surprising number of comments.
September 1, 2011 I located another interesting statement in the “Oracle Database 11g Performance Tuning Recipes“ book, so I thought that it was time for another blog article that takes a look at a statement from the book. Chapter 4 of the book is significantly better written (much more accurate) than chapter 3. To set the stage, [...]
To the first 10 that signed up for the Real World Performance Day using the "referred by Tom's blog" moniker - the books and shirts are in the mail:
I just sent them out today - you should get them soon. Looking forward to meeting:
Venky from Illinois
Kevin from Toronto
Tom from Toronto
Heather from Toronto
Stephan from Maryland
Daniel from North Carolina
Aaron from Maryland
Joe from Massachusetts
Steve from Wisconsin
Ted from Pennsylvania
I was going to leave it for a few days but there have already been so many comments and discussions on all this, I thought I better write something up. In case anyone was wondering, yes I probably am driving my colleagues at work mad with my “Question of the Day” !! Unfortunately, some might [...]
I really don’t have time to do any blogging or do anything on the forums at present – book in progress, too many people people needing a couple of days here and there, and Open World with its ACE Director thing is about to take a big chunk out of my life – but I thought I’d just mention that 2012 is beginning to shape up.
I’ve submitted three abstracts for RMOUG – it’s a three-day event, and a progression of one presentation per day seems appropriate (if they get accepted):
Single Table Access Paths — with two indexes on a table I can find 89 different options for the most efficient way of querying the data (depending on the index definitions, column definitions, and query, or course). (The optimizer can do about 23 of them)
How to Optimise a two-table join — there’s a surprising number of things to think about
Beating the Oracle Optimizer — general principles for, and examples of, manual query transformations (i.e. rewrites) that the optimizer can’t do automatically.
I’m planning to bring my wife to Denver a few days ahead of the event this time – and we’ll be bringing some walking boots. Somehow I don’t think the mountains will be as easy as the Lake District, for walking though.
I’ve added Hotsos 2012 (the 10th anniversary) to my schedule. March 4th – 8th. I’ll be hosting the Training Day at the end of the symposium, and I’ve submitted an abstract for my slide-free presentation on “The Beginner’s Guide to becoming an Expert”.
RLWrap is a great too to enhance the user experience with SQL*Plus by allowing it to make use of the GNU readline library. Search the Internet for RLWrap and sqlplus and you should get plenty of hits explaining how awesome that combination is.
Why am I writing this? I am currently in the process of upgrading my lab reference database server to Oracle Linux 6.1, and in the process I wanted to install the rlwrap tool to get read line support with SQLPlus. It’s actually quite simple, all I did after installing the operating system with the “database server” package is described in the few steps that follow this introduction.
First of all I was surprised that gcc wasn’t installed with the package group, and some other bits were missing too. This can easily be rectified by allowing access to the Oracle Public YUM server. I’m behind a gateway so have to use a proxy server:
[root@rhel61ref rlwrap-0.37]# export http_proxy=http://192.168.xx.10:3128 [root@rhel61ref rlwrap-0.37]# export https_proxy=https://192.168.xx.10:3128
This is a very basic SQUID setup without authentication. It’s also behind a firewall and cannot be accessed from the outside world.
Now with this set, go to /etc/yum.repos.d/ and use wget to get the repository configuration:
[root@rhel61ref rlwrap-0.37]# wget http://public-yum.oracle.com/public-yum-ol6.repo
This file needs to be edited-set “enabled” to 1 for the repository called [ol6_u1_base]. A quick “yum update” as root updates the repository information. I needed the following packages (including dependencies) to be installed as well:
[root@rhel61ref rlwrap-0.37]# yum install gcc [root@rhel61ref rlwrap-0.37]# yum install readline-devel
Now it’s time to get RLWrap itself, at the time of this writing it was version 0.37:
[root@rhel61ref rlwrap-0.37]# wget http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.37.tar.gz
I downloaded it to my home directory. There was a small problem related to the configure process:
[root@rhel61ref rlwrap-0.37]# ./configure checking build system type... Invalid configuration `x86_64-unknown-linux-': machine `x86_64-unknown-linux' not recognized configure: error: /bin/sh tools/config.sub x86_64-unknown-linux- failed [root@rhel61ref rlwrap-0.37]#
To work around this, you can use the “–build” flag:
[root@rhel61ref rlwrap-0.37]# ./configure --build=x86_64
This did the trick. After the configure command completed, all I needed to do was to “make” the application and “make install”. Now all I need to do is wait for Oracle to release 220.127.116.11 hopefully around OpenWorld and install it to my system. Until then I’ll have to work on understanding upstart…
The alternative is to grab a RPM from a compatible source, such as EPEL (http://fedoraproject.org/wiki/EPEL), but that depends if you are willing to mix RPMs from different sources which may not be possible due to political reasons.