Search

Top 60 Oracle Blogs

Recent comments

June 2010

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 [...]

Do It Yourself Exadata Performance! Really? Part II.

Yes, shamelessly, I’m still groveling for more votes. My Suggest-A-Session vote count is at a modest 45! Come on folks, I know we can do better than that. If you don’t get there and vote I fear there is no way for me to get a podium for this topic unless you all collectively petition [...]

Open Storage S7000 with Exadata… a good fit ETL/ELT operations.

I have worked on Exadata V2 performance projects with Kevin Closson for nearly a year now and have had the opportunity to evaluate several methods of loading data into a data warehouse. The most common, and by far the fastest method, involves the use of “External Tables”. External tables allow the user to define a table object made up of text files that live on a file system.   Using External Tables allows for standard SQL parallel query operations to be used to load data into permanent database tables.

Upcoming Index Seminar Dates (The Tourist)

I’ve just finalised some European dates for my Oracle Index Internals and Best Practices Seminar for later in the year. Location and dates are as follows: Turkey (Istanbul): 18-19 October 2010 Germany (Munich): 21-22 October 2010 Czech Republic (Prague) : 25-26 October 2010 As usual, always a good idea to book early to avoid disappointment. [...]

Advert: See you in Utrecht next week

I'm excited to be teaching at Amis for one day next week and the more informal event the previous evening. Registration form is here. Showing my beloved pictures with beer in hand? Sounds perfect although perhaps I should make sure the demos are working correctly before opening the bottle ;-)

Lots more technical posts to come in the near future too, I'm just getting over jet-lag on top of a week in Las Vegas!

Oracle Exadata Storage Server: No More SQL Tuning Required? Hooray!

I sometimes wonder if folks think I prefer poorly-tuned SQL. After all, I like hardware and everyone knows how effective it is to throw hardware at a poor query plan. I think everyone in the world has already blogged it but I can’t resist the me-too excitement. On June 10 my fellow Oak Table Network [...]

Evolution of a SQL Plan Baseline Based on a DELETE Statement

During an evolution the database engine compares the performance of two execution plans. The aim is to find out which one provides the better performance. For that purpose it has to run the SQL statement on which the SQL plan baseline is based and compare some execution statistics. The following output of the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function [...]

an unexpected visitor ...

I got up early so I could plant a few things in the garden before I settled down to some writing this afternoon. I was on my way out the door, returned to my room to get my socks and noticed something long and black on the floor near my bed. Then I realize the thing was alive and flicking it's tongue at me:Interesting start to a Sunday morning: a 3 foot long black snake on my bedroom floor.

Oracle Internals and X$ tables

I try to be active on the OTN Forums, particularly the Database – General forum. Very often, I’ll see people asking about Oracle internals and X$ tables and where they can learn more. The answer is generally, that you can’t. It’s not possible to read up on stuff that’s largely undocumented. Further, you shouldn’t really care that much. While internals can be interesting, they rarely add a great deal of real, practical value.

This recently came up again in an OTN forum posting. I replied to the thread. That answer is reproduced here: