August 2010

July-August 2010

THE LIGHTER SIDE
Edible Oracle!
Which part of Oracle Database is edible? The Cost-Based Optimizer (CBO) of course. As reported by Oracle ACE Director Tanel Poder, the Oracle 12g CBO is being re-engineered from the ground up and completely different from anything we’ve seen before. It goes beyond being automatic, intelligent and auto-tuning; it’s actually edible too!

Not convinced? Here’s a picture.

We couldn’t stop laughing.

STUMP THE SPECIALISTS
Advice for a Beginning DBA

This month’s question came to us from an overseas reader:

“I have been offered a DBA job but have never been a DBA before. What advice do you have for newbies like me?

We’ve got just the book for you: Become a Rock Star DBA by the eponymously named Thomas LaRock. This book is about working effectively as a DBA. It does not address technical details and does not cover Oracle at all. The book focuses on communication and collaboration from the perspective of a newly hired DBA. This perspective is fresh and insightful, and provides general lessons that can benefit even well-seasoned DBAs.

You can read a detailed review of the book in the latest issue of the NoCOUG Journal, the quarterly publication of the Northern California Oracle Users Group which is available for free download. You’ll get a lot of tips just from reading the review. For example, the reviewer paraphrases LaRock’s advice in Chapter 2 as follow: “As a new DBA, one of your first steps is to prepare a list of databases for which you are responsible. Obvious next steps include verifying that backups are running and that databases can be restored. Not so obvious is to prepare a list of “customers.” Who within your company uses which databases, and which executive managers depend upon these databases? This knowledge helps you to communicate about plans and problems. More importantly, reaching out to these people builds your relationships with them. When you detect a problem, you can let these internal customers know that you are working on it before they find out about it through complaints. This proactive outreach goes a long way toward strengthening working relationships, benefiting external customers and improving the likelihood of company success.” You can preview Chapter 2 using Google Books.

There are numerous free online resources available to help you on your journey. Refer to the May 2010 issue of the Specialist newsletter in which we answered the question: “The internet is anarchy. What are the most reliable sources of information on the internet?”
If you have the budget, we can recommend Beginning Oracle Database 11g Administration by our own Iggy Fernandez. As Iggy says (quoting Winnie the Pooh) in Chapter 15 The Big Picture and the Ten Deliverables:
“And if you don’t know Which to Do
Of all the things in front of you,
Then what you’ll have when you are through
Is just a mess without a clue …”
We hope this helps. Best of luck in your new endeavor

.

SQL CORNER
SQL For Analysis and Reporting 

Pivot tables are a common way of summarizing data. Prior to Oracle Database 11g, SQL did not offer direct support for pivot table and non-intuitive workarounds were necessary. Suppose that you have you have to produce a department-wise histogram of employees based on their date of hire. Here’s how you would create the required pivot table prior to Oracle Database 11g.
SET sqlblanklines ON
SET pagesize 1000

WITH
classifications AS
(
SELECT
department_id,
CASE
WHEN sysdate – hire_date < 365 THEN ‘NEW HIRE’
WHEN sysdate – hire_date > 5 * 365 THEN ‘VETERAN’
ELSE ‘OTHER’
END AS classification
FROM
employees
)
SELECT
department_id,
SUM(CASE WHEN classification = ‘NEW HIRE’ THEN 1 ELSE 0 END)
AS new_hires,
SUM(CASE WHEN classification = ‘VETERAN’ THEN 1 ELSE 0 END)
AS veterans,
SUM(CASE WHEN classification = ‘OTHER’ THEN 1 ELSE 0 END)
AS others
FROM
classifications
GROUP BY
department_id
ORDER BY
department_id;
But Oracle Database 11g offers a much cleaner solution as follows.
WITH

classifications AS
(
SELECT
department_id,
CASE
WHEN sysdate – hire_date < 365 THEN ‘NEW HIRE’
WHEN sysdate – hire_date > 5 * 365 THEN ‘VETERAN’
ELSE ‘OTHER’
END AS classification
FROM
employees
)
SELECT * FROM classifications
PIVOT
(
COUNT(classification)
FOR classification IN
(
‘NEW HIRE’ AS new_hires,
‘VETERAN’ AS veterans,
‘OTHER’ AS others
)
)
ORDER BY
department_id;
Both the above versions produce the following result in the HR sample schema.
DEPARTMENT_ID  NEW_HIRES   VETERANS     OTHERS
————- ———- ———- ———-
10          0          1          0
20          0          2          0
30          0          6          0
40          0          1          0
50          0         45          0
60          0          5          0
70          0          1          0
80          0         34          0
90          0          3          0
100          0          6          0
110          0          2          0
0          1          0

FAVE QUOTE
I am Sir Oracle, Let No Dog Bark! 

In the Merchant of Venice, Shakespeare writes:
“There are a sort of men whose visages
Do cream and mantle like a standing pond,
And do a wilful stillness entertain
With purpose to be dress’d in an opinion
Of wisdom, gravity, profound conceit;
As who should say, ‘I am Sir Oracle,
And when I ope my lips let no dog bark!’
O, my Antonio, I do know of these,
That therefore only are reputed wise
For saying nothing. …”
In other words, the faces of some men resemble stagnant ponds. They purposely say nothing and pretend to have great knowledge. Their faces seem to say “We are the Oracles and, if we make a pronouncement, let nobody contradict us.” These men are only considered wise because nobody has heard them speak.
Shakespeare definitely had a keen understanding of human behavior.

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.

Sincerely,
David Wolff
CEO, Database Specialists, Inc.

dwolff@dbspecialists.com
(415) 344-0500 x48

Leave a Reply

Your email address will not be published. Required fields are marked *