Search

Top 60 Oracle Blogs

Recent comments

June 2010

Jonathan Lewis / Kyle Hailey Webinar Replay

Thanks to everyone who joined for the webinar with Jonathan Lewis last Thursday. I've been getting a number of inquiries asking if the webinar was recorded. The webinar was recorded and you can view it at
http://www.embarcadero.com/master-sql-tuners-oracle-lewis-hailey
Here is a snippet from my part using DB Optimizer

It's a bit fuzzy on youtube. The real presentation is high quality.
Here is a clear image from DB Optimizer:

Life Cycle of a Process Request

Oracle's Flashback Query facility lets you query a past version of a row by using the information in the undo segment.  The VERSIONS option lets you seen all the versions that are available. Thus, it is possible to write a simple query to retrieve the all values that changed on a process request record through its life cycle.

The Oracle parameter undo_retention determines how long that data remains in the undo segment. In my example, it is set to 900 seconds, so I can only query versions in the last 15 minutes. If I attempt to go back further than this I will get an error.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
column prcsinstance heading 'P.I.' format 9999999

Its that time again…

Not doing a lot on this site the last two weeks, not that I am that busy, but just can’t get around to it. This year is a lot about conferences (Hotsos, an EMEA Masterclass, Miracle Open World, ODTUG Kaleidoscoop, Oracle Open World, The Michigan OakTable Symposium, an upcoming Planboard Developer Symposium here in Holland and maybe UKOUG, my overall favorite, if one of my abstracts is selected), helping Kurt van Meerbeek in maintaining the new OakTable website, organizing things like (“Cuddly toys not included”) / “An Evening with Doug Burns” (among others an internal training for my fellow DBA’s at AMIS and an “on invite only” basis customer peers), the normal stuff at work (troubleshooting, performance, remote administration) and helping others via this website, OTN or via beta testing some new Oracle stuff. So I am in total a bit more “out there” this year than usual.

The National Soccer Team of Holland

Quiz Night

If you want to be good at trouble-shooting one of the most important skills you have to learn is how to examine the evidence. Here’s a section of a trace file that was published recently on the OTN database forum. There’s an interesting detail that you might pick out – how long will it take you [...]

Life Cycle of a Process Request

Oracle's Flashback Query facility lets you query a past version of a row by using the information in the undo segment.  The VERSIONS option lets you seen all the versions that are available. Thus, it is possible to write a simple query to retrieve the all values that changed on a process request record through its life cycle.

The Oracle parameter undo_retention determines how long that data remains in the undo segment. In my example, it is set to 900 seconds, so I can only query versions in the last 15 minutes. If I attempt to go back further than this I will get an error.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
column prcsinstance heading 'P.I.' format 9999999

Related-Combine Operation „UNION ALL (RECURSIVE WITH)“

To make easier the interpretation of execution plans, in chapter 6 of TOP I defined three types of operations: standalone operations, unrelated-combine operations, and related-combine operations. For combine operations I also added a list of all operations of each type. Since in 11.2 a new related-combine operation is available, I decided to write this short [...]

What I learned about deadlines...

I learned that I am not the only one :) Seth's blog is one of the ones I read every time. They are short, to the point and almost always meaningful to me. Deadlines are the greatest motivator for me - if I do not have a deadline for something, I can pretty much guarantee you I will not finish it. I set my own little deadlines for things just to get finished. Whenever someone asks me to do something for them - write a foreword, make a recommendation, whatever - I typically say "sure and what is the drop dead date". If they know me, they'll give me a date before the true 'drop dead' just to have it in a timely fashion (because the odds they see it before then are slim to none).

Speaking of deadlines, I just finished the 2nd edition of Expert Oracle Database Architecture. Right now, this minute. Just have to dot I's and cross T's now - a few final copy edits and it'll be done. This will be the blurb on the back of the book (which you can expect to see soon)

Expert Oracle Database Architecture

Dear Reader,
I have a simple philosophy when it comes to the Oracle database: you can treat it as a black box and just stick data into it, or you can understand how it works and exploit it fully. If you choose the former, you will, at best, waste money and miss the potential of your IT environment. At worst, you will create nonscalable and incorrectly implemented applications—ones that damage your data integrity and, in short, give incorrect information. If you choose to understand exactly how the Oracle database platform should be used, then you will find that there are few information management problems that you cannot solve quickly and elegantly.

Expert Oracle Database Architecture is a book that explores and defines the Oracle database. In this book I’ve selected what I consider to be the most important Oracle architecture features, and I teach them in a proof-by-example manner, explaining not only what each feature is, but also how it works, how to implement software using it, and the common pitfalls associated with it. In this second edition, I’ve added new material reflecting the way that Oracle Database 11g Release 2 works, updated stories about implementation pitfalls, and new capabilities in the current release of the database. The number of changes between the first and second editions of this book might surprise you. Many times as I was updating the material – I myself was surprised to discover changes in the way Oracle worked that I was not yet aware of. In addition to updating the material to reflect the manner in which Oracle Database 11g Release 2 works – I’ve added an entirely new chapter on data encryption. Oracle Database 10g Release 2 added a key new capability – transparent column encryption – and Oracle Database 11g Release 1 introduced transparent tablespace encryption. This new chapter takes a look at the implementation details of these two key features as well as manual approaches to data encryption.

This book is a reflection of what I do every day. The material within covers topics and questions that I see people continually struggling with, and I cover these issues from a perspective of "When I use this, I do it this way." This book is the culmination of many years’ experience using the Oracle database, in myriad situations. Ultimately, its goal is to help DBAs and developers work together to build correct, high-performance, and scalable Oracle applications.

Thanks and enjoy!

Cursor_sharing : a picture is worth a 1000 words

Anyone who has been around Oracle performance over the years knows the grief that hard parsing SQL queries can cause on highly concurrent applications. The number one reason for hard parsing has been applications that don't use bind variables. Without bind variables queries that would otherwise be shared get recompiled because their text is different and Oracle treats them as different queries. Oracle addressed this issue with a parameter called cursor_sharing. The parameter cursor_sharing has three values
exact - the default
similar - replace literals with bind variables, if a histogram keep literal in place
force - replace literals with bind variables and use existing plan if it exists
Here is what the load looks like going from the default, exact, to the value force on a load of the same query but a query that doesn't use bind variables:

More VST Notation

Just got DB Optimizer 2.5 out the door two weeks ago and now work is going fast on 2.5.1 slated for beginning of August. Already added EXISTS and NOT EXISTS notation into the diagrams:

SELECT
cs.customerid,
cs.firstname,
cs.lastname,
mr.rentalid,
mr.duedate,
mr.totalcharge,
ri.itemnumber
FROM
MOVIES.customer cs,
MOVIES.movierental mr,
MOVIES.rentalitem ri
WHERE
LENGTH (cs.lastname) = 5 AND
cs.zip > 75062 AND
1 < cs.customerid + 2 AND
cs.phone BETWEEN 9625569900 AND 9999569900 AND
ROUND (ri.rentalid) > 10 AND
TRUNC (ri.itemnumber) > 1 AND
mr.totalcharge > (SELECT AVG (totalcharge)
FROM MOVIES.movierental) AND
ri.moviecopyid NOT IN (SELECT mc.moviecopyid
FROM MOVIES.moviecopy mc
WHERE
mc.copyformat = 'vhs' AND
mc.copycondition = 'old' AND
mc.movieid IN (SELECT mt.movieid
FROM MOVIES.movietitle mt
WHERE
mt.year < 1990 AND
mt.rating IN ('pg', 'r') AND
mt.categoryid IN (SELECT mc.categoryid
FROM MOVIES.moviecategory mc
WHERE mc.rentalprice = (SELECT MAX (rentalprice)
FROM MOVIES.moviecategory
WHERE categoryid = mc.categoryid)))) AND
mr.CUSTOMERID = cs.CUSTOMERID AND
ri.RENTALID = mr.RENTALID

Continued Rows

Do you think you know how Oracle records access to migrated or chained rows ? You may need to check your theories. Here’s a little demonstration that may amuse you. It uses an 8KB block size, a locally managed tablespaces (LMT) with uniform extent sizes of 1MB, and freelist management rather than automatic segment space [...]